Introduction

Presto is targeted at analysts who expect response times ranging from sub-second to minutes. Presto alleviates the need to choose between a fast but expensive commercial solution and a slow open source solution that requires excessive hardware. Presto is designed to efficiently query vast amounts of data using distributed queries.

Facebook uses Presto for interactive queries against several internal data stores, including their 300PB data warehouse. Over 1,000 Facebook employees use Presto daily to run more than 30,000 queries that in total scan over a petabyte a day.

Schema Management

Presto allows querying data where it lives, including Accumulo distributed storage system. It provides SQL-like query language. The Presto Command Line Interface (CLI) is a terminal-based interactive shell for running queries.

In addition to supporting a large part of standard SQL commands, Presto offers a number of very powerful aggregation functions and window operations. It can be extended with custom designed functions. Please consult the current version of Presto documentation for the latest information.

At the time of writing, Accumulo presto connector fully supports the following data types:

• BIGINT
• DOUBLE
• VARCHAR

Query Examples

Below are some examples of Presto queries.

Create Table Syntax

Create table statement in the example below produces a table with five fields, all of them belong to "user" Accumulo column family. The columns user:lastname, user:firstname and user:zipcode form a composite table key, one column user:current_occupation is shard-indexed. Sharding assures even distribution of the data across Accumulo tablet servers.

CREATE TABLE TableHelloWorldUser:hashsize_3 AS select 'str' as user:lastname:rowkey, 'str' as user:firstname:rowkey, 1 as user:zipcode:rowkey, 'str' as user:current_occupation:shard_index, 'str' as user:favorite_softdrink, 1.1 as user:cups_per_day;

Valid values for table name when creating table:

• tableName
• tableName:hashsize_number (In the example TableHelloWorldUser:hashsize_3, here 3 means that the table key is composed of three parts, default value is :hashsize_1)

Valid values for columns when creating table:

• columnQualifier
• columnFamily:columnQualifier
• columnFamily:columnQualifier:index
• columnFamily:columnQualifier:rowkey_index
• columnFamily:columnQualifier:shard_index
• columnFamily:columnQualifier:rowkey_shard_index
• columnQualifier:index
• columnQualifier:shard_index
• columnQualifier:rowkey
• columnQualifier:rowkey_index
• columnQualifier:rowkey_shard_index

"rowkey" designates a composite key part. "index" designates an indexed field. "shard_index" designates an indexed field which is also sharded.

Valid values for columns when querying:

• columnQualifier (if there is no columnFamily)
• columnFamily:columnQualifier
• id (special field for the composite primary key in all queries)

The following query is based on uservisits table from AMPLab big data benchmark. Create a table from the result set of another query:

CREATE TABLE uservisits_subset AS select cf:visitdate as user:visitdate:rowkey, cf:countrycode as user:countrycode:shard_index, cf:searchword as user:searchword:shard_index from uservisits;

As you can see we select columns visitdate, countrycode and searchword, all belonging to the same column family cf from uservisits table and store them respectively as user:visitdate, user:countrycode and user:searchword in the resulting uservisits_subset table. We set visitdate as the user:rowkey of the new table. In this case we do not need to directly specify :hashsize_1 after the uservisit_subset table name to signify that we use a single rowkey. It is a default hash size. We shard index columns user:countrycode and user:searchword.

We can then query uservisits_subset like any other table. Note that we always need to specifically select columns that form resulting table. It is a good practice to avoid using 'select *' when creating a new table. Instead, it is better to list the columns explicitly. Special fields such as id and timestamp should not be included in the select query used as a part of create table statement.

Presto REST API

Presto uses REST for all communication within a Presto installation. JSON-based REST services facilitate communication between the client and the Presto coordinator as well as for communicate between a Presto coordinator and multiple Presto workers.

REST API Overview

In Presto, everything is exposed as a REST API in Presto and HTTP is the method by which all component communicate with each other.

The Presto REST API contains several, high-level resources that correspond to the components of a Presto installation.

Execute Resource

The execute resource is what the client sends queries to. It is available at the path /v1/execute, and accepts a query as a POST and returns JSON.

Query Resource

The query resource takes a SQL query. It is available at the path /v1/query and accepts several HTTP methods. Node Resource

The node resource returns information about worker nodes in a Presto installation. It is available at the path /v1/node.