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

Further study #

More information on the CLI is available in the Command line interface section in the SEP reference documentation.