Presto SQL for Newbies

In the series of Presto SQL articles, this article explains what is Presto SQL and how to use Presto SQL for newcomers. Presto is a high performance, distributed SQL query engine for big data. Its architecture allows users to query a variety of data sources such as Hadoop, AWS S3, Alluxio, MySQL, Cassandra, Kafka, and MongoDB. One can even query data from multiple data sources within a single query.

Let's begin with what is Presto. Presto is a massively parallel programming engine that allows users to execute against any databases. If you define a database as software that stores data and processes it, Presto does not fall under the database category. Rather I prefer to call it a data or computing engine because Presto itself does not provide a storage solution. Instead, Presto focuses on how to query different data sources such as MySQL, SQLServer, Hive, Cassandra even possibly CSV files. Presto achieves such flexibility of querying anything using its plugin architecture as shown below:

In the future if you find a new database to be supported by Presto, you only need to write a new connector to connect that database with Presto. Though it looks like connectors doing the heavy lifting here, actually connectors only provide simple API to connect to the database. For example, connectors tell Presto what are the tables available in the underlying database and how to read raw data from them. Given that information, Presto decides how to process those data and respond to a user's request. The coolest thing here is that you can join a table from one database with a table in another database. For example, consider a bank has account details in MySQL database and transaction history in Hive, they don't need to migrate data from one database to another to join them. Presto supports SQL like the following query out of the box:

SELECT acc.account_no as account_no, trans.amount
FROM mysql.bank.accounts acc LEFT JOIN hive.bank.transactions trans
    ON acc.account_no = trans.account_no
WHERE trans.amount > 1000;



Connector vs Catalog

As discussed earlier, a connector is a Presto plugin that tells Presto how to connect to a given type of database. For example, the open-source Presto comes with a built-in MySQL connector. Using this connector, you can create a catalog to connect to a MySQL server. In other words, a connector is a plugin using it you can create any number of catalogs. For example, if you have two MySQL servers, you can create two property files in the etc/catalog folder as shown below to connect to those two MySQL servers.

etc/catalog/mysql1.properties
connector.name=mysql
connection-url=jdbc:mysql://localhost:3306
connection-user=root
connection-password=secret

etc/catalog/mysql2.properties
connector.name=mysql
connection-url=jdbc:mysql://localhost:3308
connection-user=root
connection-password=secret

Note that the connector name is mysql in both catalog properties. With these property files in the etc/catalogs folder, if you execute SHOW CATALOGS from the command line, you will see an output similar to this:
presto> show catalogs;
 Catalog 
---------
 system  
 mysql1   
 mysql2    
(3 rows)


High-level Components of Presto

As a distributed system, Presto has two types of servers: Coordinator and Worker. The community version is supposed to have one coordinator and one or more workers. Some companies have their version of Highly-Available coordinators and there is a GitHub issue tracking the HA deployment of community version. However, the HA scenario is out of the scope of this article. Therefore we will continue with the assumption that there is always a single coordinator and one or more workers. Of course, the same server can act as a coordinator and a worker but it is not preferred in a production environment.


Presto CLI
The command-line tool is available to download from the official website or if you build from the source code, you can find it in presto/presto-cli/target folder. You can run the presto-cli-xxx-executable.jar from the terminal and there are a whole bunch of command line parameters available to use. By default. presto-cli connects to the coordinator running at localhost:8080. If the Presto server you want to connect to is running at a different location, use the --server parameter to specify the target.

Presto JDBC Driver
Presto also provides a JDBC driver to connect to the cluster. Similar to the CLI, you can either download it from the website or get it from the built source code (presto/presto-jdbc/target).

Both Presto CLI and Presto JDBC driver are using RESTful APIs under the hood. Therefore there is always an opportunity to use a REST client to connect to the Presto server.

Presto Dashboard
Presto Dashboard is a tool to get statistics about submitted queries and the system. You can see how many workers are available in the cluster and some statistics about the query including the execution time, input size, output size, and some advanced details on how the query is compiled into a physical plan and how it is scheduled on available workers.

Unlike the CLI and JDBC driver, the dashboard is a read-only platform. There are some third-party tools like Apache Superset or Airpal that allows you to run SQL from a GUI. You can find the list of available tools on the official website.

Coordinator
The coordinator is a Presto server that receives SQL queries, compiles them, optimizes them, and schedules them on workers. Besides, it also maintains the cluster of workers and runs the dashboard. A cluster without a coordinator is not usable. In the community version of Presto, a discovery service is running along with the coordinator to which workers register themselves to form the cluster.

Worker
As the name suggests, workers execute the query. The coordinator compiles a SQL query into logical plans and sends fragmented logical plans combined with the data source information to workers (known as tasks). Workers execute those tasks and produce the output.



In-House Terms

I am working on an article completely about the internals of Presto. However, some terms are introduced here to get yourself familiar with them. You need to know nothing about these if you use Presto just to execute some SQL queries. However if you are a database engineer working with internals of database, these keywords should be already in your dictionary.

Split
The smallest unit of data a Presto operator can process. Connectors generate splits according to their standards. For example, MySQL connector may generate one split representing a full table whereas the Hive connector may create 100 splits representing one ORC file. A split object itself does not carry the data. Instead it has the information to the data location. For example, a Hive split may have the ORC file name, length and the offset to read the piece of information defined by that split.


Logical Plan
A Directed Acyclic Graph generated by compiling the SQL query. Presto uses this tree representation to optimize and analyze the SQL query.


Stage
A sub-tree (not necessary to have branches) of a logical plan after fragmentation is called a stage.


Task
A stage bundled with a split and ready for scheduling is called a task. A task informs the worker what to do and from where to get the data.

Physical Plan
A physical plan is created by workers by converting the given stage into an operator pipeline. Usually the nodes in the logical plan will be replaced by operators and some additional operators will be injected in between to connect them.


Fore more details about the internals of Presto, read the Presto: SQL on Everything paper.



Get Your Hands Dirty

The last article: Setup Presto SQL Development Environment discusses how to start Presto from IntelliJ IDEA. In this article, we will see how to setup a two-node cluster from the tar.gz file. To setup, a large Presto cluster in a production environment, consider using the presto-admin tool.

Requirements:

Step 01:
Download the latest presto-server-xxx.tar.gz file from the official website. In this article, Presto Server 333 is used. Change the following commands according to the Presto server version you have downloaded.




If you want to download to a remote server or if you simply prefer wget, use the following command:
wget https://repo1.maven.org/maven2/io/prestosql/presto-server/333/presto-server-333.tar.gz

Step 02:
Extract the tar file in the home folder.
tar -xvzf presto-server-333.tar.gz

Step 03:
Create a new folder named etc under the presto-server-333 folder.
mkdir presto-server-333/etc

Step 04:
Create a new file presto-server-333/etc/node.properties with the following content.
node.environment=test
node.id=89be00f8-b1e2-4df7-8426-c2367a87ee88
node.data-dir=/tmp/data

Use the uuidgen command-line tool generate a unique node id for the Presto server.

Step 05:
Create a new file presto-server-333/etc/jvm.config with the following content.
-server
-Xmx16G
-XX:-UseBiasedLocking
-XX:+UseG1GC
-XX:G1HeapRegionSize=32M
-XX:+ExplicitGCInvokesConcurrent
-XX:+ExitOnOutOfMemoryError
-XX:+HeapDumpOnOutOfMemoryError
-XX:ReservedCodeCacheSize=512M
-Djdk.attach.allowAttachSelf=true
-Djdk.nio.maxCachedBufferSize=2000000

Step 06:
Create a new file presto-server-333/etc/config.properties with the following content. The config.properties defines a server as a coordinator or a worker.
coordinator=true
# Following line makes this coordinator also a worker
node-scheduler.include-coordinator=true
http-server.http.port=8080
query.max-memory=6GB
query.max-memory-per-node=1GB
query.max-total-memory-per-node=2GB
discovery-server.enabled=true
discovery.uri=http://localhost:8080

Step 07:
Create a folder named catalog under the presto-server-333/etc folder and create the following catalogs.
mkdir presto-server-333/etc/catalog

7.1:
Create a new file presto-server-333/etc/catalog/tpch.properties with the following content
connector.name=tpch
TPCH connector is an on-demand connector to generate TPCH dataset. We create a catalog named tpch to test the deployment.

7.2:
Create a new file presto-server-333/etc/catalog/tpcds.properties with the following content
connector.name=tpcds
TPC-DS connector is an on-demand connector to generate TPC-DS dataset. We create a catalog named tpcds to test the deployment.

7.3:
Create a new file  presto-server-333/etc/catalog/mysql.properties with the following content
connector.name=mysql
connection-url=jdbc:mysql://localhost:3306
connection-user=root
connection-password=secret
This properties file creates a new catalog named mysql to connect MySQL server deployed in the same machine.

If you have multiple workers, the coordinator and all workers should have the same catalogs. If a worker does not have a catalog that is available in the coordinator, that specific worker will not be used to execute the tasks on the missing catalog.

Step 08:
Run Presto server using the following command and wait until you see the SERVER STARTED message.
./bin/launcher run

Step 09:
Visit http://localhost:8080/ui The latest version requires users to enter a username to access the dashboard. Enter any username and login to the dashboard.


Step 10:
Download presto-cli-333-executable.jar file from the official website.



Wget command to download the CLI is given below:
wget https://repo1.maven.org/maven2/io/prestosql/presto-cli/333/presto-cli-333-executable.jar


Step 11:
Rename the JAR and make it executable.
mv presto-cli-333-executable.jar presto-cli && chmod +x presto-cli

Step 12:
Run Presto CLI from the terminal.
./presto-cli

Step 13:
Execute SHOW CATALOGS; from the CLI and make sure that you see all three catalogs created in the Step 7.
presto> SHOW CATALOGS;
 Catalog 
---------
 mysql   
 system  
 tpcds   
 tpch    
(4 rows)

Query 20200522_024939_00000_g4pt9, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
0:01 [0 rows, 0B] [0 rows/s, 0B/s]

Execute SHOW SCHEMAS FROM mysql; It should list all databases available in the MySQL database.
presto> SHOW SCHEMAS FROM mysql;
       Schema       
--------------------
 employees             
 information_schema 
 performance_schema 
 sys                
(4 rows)

Query 20200522_025817_00005_g4pt9, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
0:00 [4 rows, 65B] [18 rows/s, 297B/s]

Similary you can show tables from tpch.tiny using the following command:
presto> SHOW TABLES FROM tpch.tiny;
  Table   
----------
 customer 
 lineitem 
 nation   
 orders   
 part     
 partsupp 
 region   
 supplier 
(8 rows)

Query 20200522_025450_00002_g4pt9, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
0:00 [8 rows, 166B] [18 rows/s, 382B/s]

The following SQL query selects 10 rows from the tpch.tiny.customer table.
presto> SELECT * FROM tpch.tiny.customer LIMIT 10;
 custkey |        name        |                address                | nationkey |      phone      | acctbal | mktsegment |                                                   comment         
---------+--------------------+---------------------------------------+-----------+-----------------+---------+------------+-------------------------------------------------------------------
     375 | Customer#000000375 | e53JADEeGvM1ikhN7aa                   |        15 | 25-575-273-9756 | 5562.22 | HOUSEHOLD  | st the pending accounts. final courts above the pending pinto bean
     376 | Customer#000000376 | 4NwsvFQU T4mSgzvU1Rx2ZtHOGyaNyhe      |        16 | 26-437-952-8986 | 4231.45 | AUTOMOBILE | gs cajole quickly. bold asymptotes wake regularly along the quickl
     377 | Customer#000000377 | PA4levhyD,Rvr0JHQ4QNOqJ9gW YXE        |        23 | 33-260-610-4079 | 1043.72 | MACHINERY  | . slyly regular ideas cajole blithely. slyly ironic foxes are care
     378 | Customer#000000378 | 133stqM9 LT,a2BSlbm49 nXreFggaZgW6P6J |        22 | 32-147-793-4825 | 5718.05 | BUILDING   | ackages haggle fluffily ironic packages.                          
     379 | Customer#000000379 | t3QzCf,q1NbshmjOIUY                   |         7 | 17-228-550-9246 | 5348.11 | AUTOMOBILE | l deposits cajole blithely blithely final deposits. express, even 
     380 | Customer#000000380 | n2w3Jd1bipwICbOVgrELzcNRexmWSklo      |        21 | 31-538-493-4229 | 2755.46 | BUILDING   | riously special accounts. slyly final accounts sleep; blithely spe
     381 | Customer#000000381 | w3zVseYDbjBbzLld                      |         5 | 15-860-208-7093 | 9931.71 | BUILDING   | t regular, bold accounts. carefully quick packages haggle. care   
     382 | Customer#000000382 | UdgAMamK5JnSykA,ZPfR5W5zRFatDUye      |         8 | 18-942-650-6657 | 6269.42 | AUTOMOBILE | . blithely express notornis according to the blithely even request
     383 | Customer#000000383 | iBIHYgXvVDpu6qq7FlqXVcAIDAzv4qs       |         2 | 12-868-920-9034 | -849.44 | MACHINERY  | slyly express ideas haggle blithely unusual dugouts. ironic pinto 
     384 | Customer#000000384 | kDDMb3ML nUSu2Sn7CUHyZVedAFUx9        |         9 | 19-271-453-8361 |  -614.3 | HOUSEHOLD  | olites. express, unusual dolphins cajole carefully about the      
(10 rows)

Query 20200522_025550_00004_g4pt9, FINISHED, 1 node
Splits: 25 total, 25 done (100.00%)
0:00 [1.5K rows, 0B] [6.61K rows/s, 0B/s]

Step 14:
Analyze the dashboard. On the dashboard, you can see currently running, queued, finished or failed queries.



Atthe first glance, you will see executed queries along with the split information, execution time and memory statistics. You can also see the number of splits used for any query in the CLI after execution.

By clicking on the link with query id (In the above screenshot: 20200522_122148_00000_gmjfs),  you can see the more details about the query. The Overview tab provides more statistics about the query and workers involved in execution.


The Live Plan tab shows the physical plan of the query. Green boxes represent stages and each stage will have one or more operators.


Clicking on a stage will take you to the next tab: Stage Performance. This tab gives an overview about the performance of each operators. You can also see the number of input and output of each operators. This is a good place to analyze the bottleneck of a SQL query.


The next tab: Splits shows the timeline of splits and the last tab: JSON provides all these information in a JSON format.

An end user using Presto to run SQL queries just to get results may never needs to check this dashboard. However, these statistics are great source of information for database engineers who are looking to improve the SQL query as well as the computing engine. I hope this article gives you a basic introduction to Presto and its tools. If you have any questions or suggestions, please comment below.
Previous
Next Post »

Contact Form

Name

Email *

Message *