CLI #
The Presto command line interface (CLI) provides a terminal-based, interactive shell for running queries and inspecting catalog structures in any SEP cluster.
Setup to use the CLI #
The CLI is distributed as an executable JAR file that you download, rename, and place in a directory in the PATH.
Requirements #
The CLI requires a java
command on the PATH from Java 8 or newer. It is
usually easiest to use the same Java 11 required by SEP itself,
described on Java runtime
environment.
Download the CLI #
To gain access to SEP archives, visit the Starburst website and click either the Get Started or Start Free buttons.
This opens a dialog that prompts for your name, email address, and location. Fill out the form using a valid email address, then click Free Download.
A few moments later, you receive email from Starburst with a link to the downloads page. The Downloads page is organized into Long-Term and Short-Term Support sections.
Select and download the latest Presto CLI executable JAR file available, which is usually in the STS section.
You can also download the CLI binary straight from the link in the reference documentation
Rename and place the CLI #
You downloaded an executable JAR file that is usable as-is. To make it practical with having to call Java, copy it to a directory in the PATH, rename it, and make it executable. For example:
cd /usr/local/bin
cp -p /home/<yourname>/Download/presto-cli-nnn-executable.jar .
mv presto-cli-nnn-executable.jar presto
chmod +x presto
where nnn is the version of the file you selected.
First steps #
Now you are ready to verify that the CLI runs. Check if you get a similar output to the following listing:
$ presto --version
Presto CLI 350
The CLI works. Now gather the connection information for your cluster, and connect to it. You can learn more about using the CLI from the following tutorial or the Command line interface section in the SEP reference documentation.
CLI tutorial #
The following sections provide a brief introduction to using the CLI.
Interactive CLI #
At the shell prompt, enter presto
with no arguments. By default, this connects
to the running SEP server at the default address and port,
localhost:8080
.
presto
If your SEP server uses a different port or is running elsewhere
on your network, specify the server’s URL with --server
. For example:
presto --server=presto.example.com:8082
This opens a Presto CLI shell, with presto>
prompt. All commands
entered here must be terminated with a semicolon.
Exit presto
’s interactive mode with quit;
or exit;
or Ctrl+D
:
presto> quit;
Usage help #
To see the available commands in interactive mode:
presto> help;
This returns:
Supported commands:
QUIT
EXPLAIN [ ( option [, ...] ) ] <query>
options: FORMAT { TEXT | GRAPHVIZ | JSON }
TYPE { LOGICAL | DISTRIBUTED | VALIDATE | IO }
DESCRIBE <table>
SHOW COLUMNS FROM <table>
SHOW FUNCTIONS
SHOW CATALOGS [LIKE <pattern>]
SHOW SCHEMAS [FROM <catalog>] [LIKE <pattern>]
SHOW TABLES [FROM <schema>] [LIKE <pattern>]
USE [<catalog>.]<schema>
Show configured resources #
Every SEP server is configured to connect to one or more data sources by means of a catalog that defines the connection type. Each catalog has at least one schema; each schema has at least one table.
To see the list of catalogs configured for the current server, run:
presto> show catalogs;
For the Starburst-provided Docker image, this returns:
Catalog
-----------
jmx
memory
system
tpcds
tpch
(5 rows)
(Each query also returns a set of performance metadata, which is not repeated here.)
Explore the tpch catalog #
Start with the tpch
catalog, which allows you to test the capabilities and
query syntax of Presto without configuring access to an external data source.
The TPCH connector is described on its documentation
page.
See the schemas provided in the TPCH connector:
presto> show schemas from tpch;
This returns:
Schema
--------------------
information_schema
sf1
sf100
sf1000
sf10000
sf100000
sf300
sf3000
sf30000
tiny
(10 rows)
To see the tables in one of these schemas:
presto> show tables from tpch.sf100;
Save typing with USE #
To avoid typing the catalog and schema name every time:
presto> use tpch.sf100;
presto:sf100>
To see the tables in sf100
:
presto:sf100> show tables;
which returns:
Table
----------
customer
lineitem
nation
orders
part
partsupp
region
supplier
(8 rows)
To see the structure of the customer
table:
presto:sf100> show columns from customer;
which returns:
Column | Type | Extra | Comment
------------+--------------+-------+---------
custkey | bigint | |
name | varchar(25) | |
address | varchar(40) | |
nationkey | bigint | |
phone | varchar(15) | |
acctbal | double | |
mktsegment | varchar(10) | |
comment | varchar(117) | |
(8 rows)
CLI with arguments #
You can submit a valid SQL script on the presto
command line:
presto --execute 'SELECT custkey, name, phone, acctbal FROM tpch.sf100.customer LIMIT 7'
You can send a SQL script file to the CLI from the command line by specifying
its name as an argument to the -f
or --file
command line options.
presto -f filename.sql
Two TPCH scripts are included with the sample files for the O’Reilly book Presto: The Definitive Guide.
To use these scripts, download the book’s samples from their GitHub
location either as a
zip file or a git clone. Let’s say you place your clone or unzip directory in
~/bookfiles
. Then use the TPCH scripts as follows:
cd ~/bookfiles/tpch
presto -f nations.sql
The second sample script requires the Black hole
connector to be configured for the
current server. This connector is designed to operate like /dev/null
and
/dev/zero
for high performance testing of Presto components.
You can configure a local Docker-hosted SEP server to use this connector by following the steps in Map a local etc directory.
If the target SEP server has the Black Hole connector, then run the second sample script:
presto --file=tpch-queries.sql
To run this second script more than once, you must stop and restart the {site.terms.sep}} server.
For a locally running tar.gz
installation, in the sep-root
directory for
your server, run:
sudo bin/launcher restart
For a locally running Docker server:
docker restart sepdock
tpch-queries
script runs.
Further study #
More information on the CLI is available in the Command line interface section in the SEP reference documentation.