CLI #

The Trino 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.

Connection information #

Get the connection information and the cluster version for the cluster you want to connect to and use to run queries.

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.

The CLI needs to be compatible with the version of the cluster you are connecting to.

Download the CLI #

You can request the CLI for a specific version from Starburst Support.

Alternatively, to gain direct 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.

Click on the link to the Downloads page in the resulting email. It is organized into Long-Term and Short-Term Support sections.

Download a specific version of the CLI, based on your cluster version:

  • Version 350, if your cluster version is 350 or older
  • Same or newer version than the cluster for versions 354 and newer

You can also download the CLI binary straight from the link in the reference documentation:

Use the version selector in the documentation for different releases, or contact |support|.

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/trino-cli-*-executable.jar .
mv trino-cli-*-executable.jar trino
chmod +x trino

First steps #

Now you are ready to verify that the CLI runs. Check if you get a similar output to the following listing:

$ trino --version
Trino CLI 354

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 trino with no arguments. By default, this connects to the running SEP server at the default address and port, localhost:8080.

trino

If your SEP server uses a different port or is running elsewhere on your network, specify the server’s URL with --server. For example:

trino --server=cluster.example.com:8082

This opens a Trino CLI shell, with trino> prompt. All commands entered here must be terminated with a semicolon.

Exit the CLI interactive mode with quit; or exit; or Ctrl+D:

trino> quit;

Usage help #

To see the available commands in interactive mode:

trino> 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:

trino> 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 SEP without configuring access to an external data source. The TPCH connector is described on its documentation page.

Use the SHOW SCHEMAS command to list schemas provided by the TPCH connector:

trino> 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:

trino> SHOW TABLES FROM tpch.sf100;

Save typing with USE #

To avoid typing the catalog and schema name every time:

trino> USE tpch.sf100;
trino:sf100>

To see the tables in sf100:

trino:sf100> SHOW TABLES;

which returns:

  Table
----------
 customer
 lineitem
 nation
 orders
 part
 partsupp
 region
 supplier
(8 rows)

To see the structure of the customer table:

trino: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 trino command line:

trino --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.

trino -f filename.sql

Two TPCH scripts are included with the sample files for the O’Reilly book Trino: 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
trino -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 performance testing and similar use cases.

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:

trino --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.