In this chapter, we will discuss how to create and execute queries on Presto. Let us go through Presto supported basic data types.

Basic Data Types

The following table describes the basic data types of Presto.

Presto − Operators

Presto operators are listed in the following table.

As of now we were discussing running some simple basic queries on Presto. This chapter will discuss the important SQL functions.

Math Functions

Math functions operate on mathematical formulas. Following table describes the list of functions in detail.

Trigonometric Functions

Trigonometric functions arguments are represented as radians(). Following table lists out the functions.

Bitwise Functions

The following table lists out the Bitwise functions.

String Functions

Following table lists out the String functions.

Date and Time Functions

Following table lists out the Date and Time functions.

Regular Expression Functions

The following table lists out the Regular Expression functions.

JSON Functions

The following table lists out JSON functions.

URL Functions

The following table lists out the URL functions.

Aggregate Functions

The following table lists out the Aggregate functions.

Color Functions

Following table lists out the Color functions.

Array Functions

The following table lists out the Array functions.

Teradata Functions

The following table lists out Teradata functions.

The MySQL connector is used to query an external MySQL database.

Prerequisites

MySQL server installation.

Configuration Settings

Hopefully you have installed mysql server on your machine. To enable mysql properties on Presto server, you must create a file “mysql.properties” in “etc/catalog” directory. Issue the following command to create a mysql.properties file.

$ cd etc
$ cd catalog
$ vi mysql.properties

connector.name = mysql
connection-url = jdbc:mysql://localhost:3306
connection-user = root
connection-password = pwd

Save the file and quit the terminal. In the above file, you must enter your mysql password in connection-password field.

Create Database in MySQL Server

Open MySQL server and create a database using the following command.

create database tutorials

Now you have created “tutorials” database in the server. To enable database type, use the command “use tutorials” in the query window.

Create Table

Let’s create a simple table on “tutorials” database.

create table author(auth_id int not null, auth_name varchar(50),topic varchar(100))

Insert Table

After creating a table, insert three records using the following query.

insert into author values(1,''Doug Cutting'',''Hadoop'')
insert into author values(2,’James Gosling'',''java'')
insert into author values(3,''Dennis Ritchie’,''C'')

Select Records

To retrieve all the records, type the following query.

Query

select * from author

Result

auth_id    auth_name      topic
1        Doug Cutting     Hadoop
2        James Gosling    java
3        Dennis Ritchie     C

As of now, you have queried data using MySQL server. Let’s connect Mysql storage plugin to Presto server.

Connect Presto CLI

Type the following command to connect MySql plugin on Presto CLI.

./presto --server localhost:8080 --catalog mysql --schema tutorials

You will receive the following response.

presto:tutorials>

Here “tutorials” refers to schema in mysql server.

List Schemas

To list out all the schemas in mysql, type the following query in Presto server.

Query

presto:tutorials> show schemas from mysql;

Result

      Schema
--------------------
 information_schema
 performance_schema
 sys
 tutorials

From this result, we can conclude the first three schemas as predefined and the last one as created by yourself.

List Tables from Schema

Following query lists out all the tables in tutorials schema.

Query

presto:tutorials> show tables from mysql.tutorials;

Result

  Table
--------
 author

We have created only one table in this schema. If you have created multiple tables, it will list out all the tables.

Describe Table

To describe the table fields, type the following query.

Query

presto:tutorials> describe mysql.tutorials.author;

Result

  Column   |     Type     | Comment
-----------+--------------+---------
 auth_id   | integer      |
 auth_name | varchar(50)  |
 topic     | varchar(100) |

Show Columns from Table

Query

presto:tutorials> show columns from mysql.tutorials.author;

Result

 Column    |     Type     | Comment
-----------+--------------+---------
 auth_id   | integer      |
 auth_name | varchar(50)  |
 topic     | varchar(100) |

Access Table Records

To fetch all the records from mysql table, issue the following query.

Query

presto:tutorials> select * from mysql.tutorials.author;

Result

auth_id  |   auth_name    | topic
---------+----------------+--------
       1 | Doug Cutting   | Hadoop
       2 | James Gosling  | java
       3 | Dennis Ritchie | C

From this result, you can retrieve mysql server records in Presto.

Create Table Using as Command

Mysql connector doesn’t support create table query but you can create a table using as command.

Query

presto:tutorials> create table mysql.tutorials.sample as
select * from mysql.tutorials.author;

Result

CREATE TABLE: 3 rows

You can’t insert rows directly because this connector has some limitations. It cannot support the following queries −

• create
• insert
• update
• delete
• drop

To view the records in the newly created table, type the following query.

Query

presto:tutorials> select * from mysql.tutorials.sample;

Result

auth_id  |   auth_name    | topic
---------+----------------+--------
       1 | Doug Cutting   | Hadoop
       2 | James Gosling  | java
       3 | Dennis Ritchie | C

Java Management Extensions (JMX) gives information about the Java Virtual Machine and software running inside JVM. The JMX connector is used to query JMX information in Presto server.

As we have already enabled “jmx.properties” file under “etc/catalog” directory. Now connect Prest CLI to enable JMX plugin.

Presto CLI

Query

$ ./presto --server localhost:8080 --catalog jmx --schema jmx

Result

You will receive the following response.

presto:jmx>

JMX Schema

To list out all the schemas in “jmx”, type the following query.

Query

presto:jmx> show schemas from jmx;

Result

      Schema
--------------------
 information_schema
 current

Show Tables

To view the tables in the “current” schema, use the following command.

Query 1

presto:jmx> show tables from jmx.current;

Result

                                    Table
------------------------------------------------------------------------------
 com.facebook.presto.execution.scheduler:name = nodescheduler
 com.facebook.presto.execution:name = queryexecution
 com.facebook.presto.execution:name = querymanager
 com.facebook.presto.execution:name = remotetaskfactory
 com.facebook.presto.execution:name = taskexecutor
 com.facebook.presto.execution:name = taskmanager
 com.facebook.presto.execution:type = queryqueue,name = global,expansion = global
 ………………
 ……………….

Query 2

presto:jmx> select * from jmx.current.”java.lang:type = compilation";

Result

node               | compilationtimemonitoringsupported |      name   |         objectname         | totalcompilationti
--------------------------------------+------------------------------------+--------------------------------+----------------------------+-------------------
ffffffff-ffff-ffff-ffff-ffffffffffff | true | HotSpot 64-Bit Tiered Compilers | java.lang:type=Compilation |       1276

Query 3

presto:jmx> select * from jmx.current."com.facebook.presto.server:name = taskresource";

Result

 node                 | readfromoutputbuffertime.alltime.count
 | readfromoutputbuffertime.alltime.max | readfromoutputbuffertime.alltime.maxer
 --------------------------------------+---------------------------------------+--------------------------------------+---------------------------------------
 ffffffff-ffff-ffff-ffff-ffffffffffff |                                   92.0 |                          1.009106149 |

This chapter will explain how to install Presto on your machine. Let’s go through the basic requirements of Presto,

• Linux or Mac OS
• Java version 8

Now, let’s continue the following steps to install Presto on your machine.

Verifying Java installation

Hopefully, you have already installed Java version 8 on your machine right now, so you just verify it using the following command.

$ java -version

If Java is successfully installed on your machine, you could see the version of installed Java. If Java is not installed, follow the subsequent steps to install Java 8 on your machine.

Download JDK. Download the latest version of JDK by visiting the following link.

The latest version is JDK 8u 92 and the file is “jdk-8u92-linux-x64.tar.gz”. Please download the file on your machine.

After that, extract the files and move to the specific directory.

Then set Java alternatives. Finally Java will be installed on your machine.

Apache Presto Installation

Download the latest version of Presto by visiting the following link,

Now the latest version of “presto-server-0.149.tar.gz” will be downloaded on your machine.

Extract tar Files

Extract the tar file using the following command −

$ tar  -zxf  presto-server-0.149.tar.gz
$ cd presto-server-0.149

Configuration Settings

Create “data” directory

Create a data directory outside the installation directory, which will be used for storing logs, metadata, etc., so that it is to be easily preserved when upgrading Presto. It is defined using the following code −

$ cd
$ mkdir data

To view the path where it is located, use the command “pwd”. This location will be assigned in the next node.properties file.

Create “etc” directory

Create an etc directory inside Presto installation directory using the following code −

$ cd presto-server-0.149
$ mkdir etc

This directory will hold configuration files. Let’s create each file one by one.

Node Properties

Presto node properties file contains environmental configuration specific to each node. It is created inside etc directory (etc/node.properties) using the following code −

$ cd etc
$ vi node.properties

node.environment = production
node.id = ffffffff-ffff-ffff-ffff-ffffffffffff
node.data-dir = /Users/../workspace/Presto

After making all the changes, save the file, and quit the terminal. Here node.data is the location path of the above created data directory. node.id represents the unique identifier for each node.

JVM Config

Create a file “jvm.config” inside etc directory (etc/jvm.config). This file contains a list of command line options used for launching the Java Virtual Machine.

$ cd etc
$ vi jvm.config

-server
-Xmx16G
-XX:+UseG1GC
-XX:G1HeapRegionSize = 32M
-XX:+UseGCOverheadLimit
-XX:+ExplicitGCInvokesConcurrent
-XX:+HeapDumpOnOutOfMemoryError
-XX:OnOutOfMemoryError = kill -9 %p

After making all the changes, save the file, and quit the terminal.

Config Properties

Create a file “config.properties” inside etc directory(etc/config.properties). This file contains the configuration of Presto server. If you are setting up a single machine for testing, Presto server can function only as the coordination process as defined using the following code −

$ cd etc
$ vi config.properties

coordinator = true
node-scheduler.include-coordinator = true
http-server.http.port = 8080
query.max-memory = 5GB
query.max-memory-per-node = 1GB
discovery-server.enabled = true
discovery.uri = http://localhost:8080

Here,

• coordinator − master node.

• node-scheduler.include-coordinator − Allows scheduling work on the coordinator.

• http-server.http.port − Specifies the port for the HTTP server.

• query.max-memory=5GB − The maximum amount of distributed memory.

• query.max-memory-per-node=1GB − The maximum amount of memory per node.

• discovery-server.enabled − Presto uses the Discovery service to find all the nodes in the cluster.

• discovery.uri − he URI to the Discovery server.

If you are setting up multiple machine Presto server, Presto will function as both coordination and worker process. Use this configuration setting to test Presto server on multiple machines.

Configuration for Coordinator

$ cd etc
$ vi config.properties

coordinator = true
node-scheduler.include-coordinator = false
http-server.http.port = 8080
query.max-memory = 50GB
query.max-memory-per-node = 1GB
discovery-server.enabled = true
discovery.uri = http://localhost:8080

Configuration for Worker

$ cd etc
$ vi config.properties

coordinator = false
http-server.http.port = 8080
query.max-memory = 50GB
query.max-memory-per-node = 1GB
discovery.uri = http://localhost:8080

Log Properties

Create a file “log.properties” inside etc directory(etc/log.properties). This file contains minimum log level for named logger hierarchies. It is defined using the following code −

$ cd etc
$ vi log.properties
com.facebook.presto = INFO

Save the file and quit the terminal. Here, four log levels are used such as DEBUG, INFO, WARN and ERROR. Default log level is INFO.

Catalog Properties

Create a directory “catalog” inside etc directory(etc/catalog). This will be used for mounting data. For example, create etc/catalog/jmx.properties with the following contents to mount the jmx connector as the jmx catalog −

$ cd etc
$ mkdir catalog
$ cd catalog
$ vi jmx.properties
connector.name = jmx

Start Presto

Presto can be started using the following command,

$ bin/launcher start

Then you will see the response similar to this,

Started as 840

Run Presto

To launch Presto server, use the following command −

$ bin/launcher run

After successfully launching Presto server, you can find log files in “var/log” directory.

• launcher.log − This log is created by the launcher and is connected to the stdout and stderr streams of the server.

• server.log − This is the main log file used by Presto.

• http-request.log − HTTP request received by the server.

As of now, you have successfully installed Presto configuration settings on your machine. Let’s continue the steps to install Presto CLI.

Install Presto CLI

The Presto CLI provides a terminal-based interactive shell for running queries.

Download the Presto CLI by visiting the following link,

Now “presto-cli-0.149-executable.jar” will be installed on your machine.

Run CLI

After downloading the presto-cli, copy it to the location which you want to run it from. This location may be any node that has network access to the coordinator. First change the name of the Jar file to Presto. Then make it executable with chmod + x command using the following code −

$ mv presto-cli-0.149-executable.jar presto
$ chmod +x presto

Now execute CLI using the following command,

./presto --server localhost:8080 --catalog jmx --schema default
Here jmx(Java Management Extension) refers to catalog and default referes to schema.

You will see the following response,

 presto:default>

Now type “jps” command on your terminal and you will see the running daemons.

Stop Presto

After having performed all the executions, you can stop the presto server using the following command −

$ bin/launcher stop

In this chapter, we will discuss the administration tools used in Presto. Let’s start with the Web Interface of Presto.

Web Interface

Presto provides a web interface for monitoring and managing queries. It can be accessed from the port number specified in the coordinator Config Properties.

Start Presto server and Presto CLI. Then you can access the web interface from the following url − http://localhost:8080/

The output will be similar to the above screen.

Here, the main page has a list of queries along with information like unique query ID, query text, query state, percentage completed, username and source from which this query is originated. Latest queries are running first, then completed or not completed queries are displayed at the bottom.

Tuning the Performance on Presto

If Presto cluster is having any performance-related issues, change your default configuration settings to the following settings.

Config Properties

• task. info -refresh-max-wait − Reduces coordinator work load.

• task.max-worker-threads − Splits the process and assigns to each worker nodes.

• distributed-joins-enabled − Hash-based distributed joins.

• node-scheduler.network-topology − Sets network topology to scheduler.

JVM Settings

Change your default JVM settings to the following settings. This will be helpful for diagnosing garbage collection issues.

-XX:+PrintGCApplicationConcurrentTime
-XX:+PrintGCApplicationStoppedTime
-XX:+PrintGCCause
-XX:+PrintGCDateStamps
-XX:+PrintGCTimeStamps
-XX:+PrintGCDetails
-XX:+PrintReferenceGC
-XX:+PrintClassHistogramAfterFullGC
-XX:+PrintClassHistogramBeforeFullGC
-XX:PrintFLSStatistics = 2
-XX:+PrintAdaptiveSizePolicy
-XX:+PrintSafepointStatistics
-XX:PrintSafepointStatisticsCount = 1

This chapter will discuss the configuration settings for Presto.

Presto Verifier

The Presto Verifier can be used to test Presto against another database (such as MySQL), or to test two Presto clusters against each other.

Create Database in MySQL

Open MySQL server and create a database using the following command.

create database test

Now you have created “test” database in the server. Create the table and load it with the following query.

CREATE TABLE verifier_queries(
   id INT NOT NULL AUTO_INCREMENT,
   suite VARCHAR(256) NOT NULL,
   name VARCHAR(256),
   test_catalog VARCHAR(256) NOT NULL,
   test_schema VARCHAR(256) NOT NULL,
   test_prequeries TEXT,
   test_query TEXT NOT NULL,
   test_postqueries TEXT,
   test_username VARCHAR(256) NOT NULL default ''verifier-test'',
   test_password VARCHAR(256),
   control_catalog VARCHAR(256) NOT NULL,
   control_schema VARCHAR(256) NOT NULL,
   control_prequeries TEXT,
   control_query TEXT NOT NULL,
   control_postqueries TEXT,
   control_username VARCHAR(256) NOT NULL default ''verifier-test'',
   control_password VARCHAR(256),
   session_properties_json TEXT,
   PRIMARY KEY (id)
);

Add Config Settings

Create a properties file to configure the verifier −

$ vi config.properties

suite = mysuite
query-database = jdbc:mysql://localhost:3306/tutorials?user=root&password=pwd
control.gateway = jdbc:presto://localhost:8080
test.gateway = jdbc:presto://localhost:8080
thread-count = 1

Here, in the query-database field, enter the following details − mysql database name, user name, and password.

Download JAR File

Download Presto-verifier jar file by visiting the following link,

Now the version “presto-verifier-0.149-executable.jar” is downloaded on your machine.

Execute JAR

Execute the JAR file using the following command,

$ mv presto-verifier-0.149-executable.jar verifier
$ chmod+x verifier

Run Verifier

Run the verifier using the following command,

$ ./verifier config.properties

Create Table

Let’s create a simple table in “test” database using the following query.

create table product(id int not null, name varchar(50))

Insert Table

After creating a table, insert two records using the following query,

insert into product values(1,’Phone'')
insert into product values(2,’Television’)

Run Verifier Query

Execute the following sample query in the verifier terminal (./verifier config.propeties) to check the verifier result.

Sample Query

insert into verifier_queries (suite, test_catalog, test_schema, test_query,
control_catalog, control_schema, control_query) values
(''mysuite'', ''mysql'', ''default'', ''select * from mysql.test.product'',
''mysql'', ''default'', ''select * from mysql.test.product'');

Here, select * from mysql.test.product query refers to mysql catalog, test is database name and product is table name. In this way, you can access mysql connector using Presto server.

Here, two same select queries are tested against each other to see the performance. Similarly, you can run other queries to test the performance results. You can also connect two Presto clusters to check the performance results.