docs/src/main/sphinx/client/cli.md
The Trino CLI provides a terminal-based, interactive shell for running queries. The CLI is a self-executing JAR file, which means it acts like a normal UNIX executable.
The CLI uses the over HTTP/HTTPS to communicate with the coordinator on the cluster.
The Trino CLI has the following requirements:
The CLI version should be identical to the version of the Trino cluster, or newer. Older versions typically work, but only a subset is regularly tested. Versions before 350 are not supported.
(cli-installation)=
Download {download_gh}cli, rename it to trino, make it executable with
chmod +x, and run it to show the version of the CLI:
./trino --version
Run the CLI with --help or -h to see all available options.
Windows users, and users unable to execute the preceding steps, can use the
equivalent java command with the -jar option to run the CLI, and show
the version:
java -jar trino-cli-*-executable.jar --version
The syntax can be used for the examples in the following sections. In addition,
using the java command allows you to add configuration options for the Java
runtime with the -D syntax. You can use this for debugging and
troubleshooting, such as when {ref}specifying additional Kerberos debug options <cli-kerberos-debug>.
The minimal command to start the CLI in interactive mode specifies the URL of the coordinator in the Trino cluster:
./trino http://trino.example.com:8080
If successful, you will get a prompt to execute commands. Use the help
command to see a list of supported commands. Use the clear command to clear
the terminal. To stop and exit the CLI, run exit or quit.:
trino> help
Supported commands:
QUIT
EXIT
CLEAR
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>
You can now run SQL statements. After processing, the CLI will show results and statistics.
trino> SELECT count(*) FROM tpch.tiny.nation;
_col0
-------
25
(1 row)
Query 20220324_213359_00007_w6hbk, FINISHED, 1 node
Splits: 13 total, 13 done (100.00%)
2.92 [25 rows, 0B] [8 rows/s, 0B/s]
As part of starting the CLI, you can set the default catalog and schema. This allows you to query tables directly without specifying catalog and schema.
./trino http://trino.example.com:8080/tpch/tiny
trino:tiny> SHOW TABLES;
Table
----------
customer
lineitem
nation
orders
part
partsupp
region
supplier
(8 rows)
You can also set the default catalog and schema with the {doc}/sql/use
statement.
trino> USE tpch.tiny;
USE
trino:tiny>
Many other options are available to further configure the CLI in interactive mode:
:::{list-table} :widths: 40, 60 :header-rows: 1
--client-info--client-request-timeout2m.--decimal-data-size--disable-auto-suggestion--disable-compression--editing-modeEMACS.--extra-credential--extra-header--http-proxy--history-file~/.trino_history.--network-loggingNONE, other options are BASIC, HEADERS, or BODY.--output-format-interactive=<format>ALIGNED.--pager=<pager>less with a carefully
selected set of options.--no-progress--path--serverhttp://localhost:8080. Can only be set if URL is not
specified.--sessionsession_property_name=value.--socks-proxy--timezoneMost of the options can also be set as parameters in the URL. This means
a JDBC URL can be used in the CLI after removing the jdbc: prefix.
However, the same parameter may not be specified using both methods.
See {doc}the JDBC driver parameter reference </client/jdbc>
to find out URL parameter names. For example:
./trino 'https://trino.example.com?SSL=true&SSLVerification=FULL&clientInfo=extra'
(cli-tls)=
Trino is typically available with an HTTPS URL. This means that all network
traffic between the CLI and Trino uses TLS. {doc}TLS configuration </security/tls> is common, since it is a requirement for {ref}any authentication <cli-authentication>.
Use the HTTPS URL to connect to the server:
./trino https://trino.example.com
The recommended TLS implementation is to use a globally trusted certificate. In this case, no other options are necessary, since the JVM running the CLI recognizes these certificates.
Use the options from the following table to further configure TLS and certificate usage:
:::{list-table} :widths: 40, 60 :header-rows: 1
--insecure--keystore-path--keystore-password--keystore-type--use-system-keystore--keystore-type.--truststore-password--truststore-path--truststore-type--use-system-truststore--truststore-type.
:::(cli-authentication)=
The Trino CLI supports many {doc}/security/authentication-types detailed in
the following sections:
(cli-username-password-auth)=
Username and password authentication is typically configured in a cluster using
the PASSWORD {doc}authentication type </security/authentication-types>,
for example with {doc}/security/ldap or {doc}/security/password-file.
The following code example connects to the server, establishes your username, and prompts the CLI for your password:
./trino https://trino.example.com --user=exampleusername --password
Alternatively, set the password as the value of the TRINO_PASSWORD
environment variable. Typically use single quotes to avoid problems with
special characters such as $:
export TRINO_PASSWORD='LongSecurePassword123!@#'
If the TRINO_PASSWORD environment variable is set, you are not prompted
to provide a password to connect with the CLI.
./trino https://trino.example.com --user=exampleusername --password
(cli-external-sso-auth)=
Use the --external-authentication option for browser-based SSO
authentication, as detailed in {doc}/security/oauth2. With this configuration,
the CLI displays a URL that you must open in a web browser for authentication.
The detailed behavior is as follows:
--external-authentication option and execute a
query.(cli-certificate-auth)=
Use the following CLI arguments to connect to a cluster that uses
{doc}certificate authentication </security/certificate>.
:::{list-table} CLI options for certificate authentication :widths: 35 65 :header-rows: 1
--keystore-password=<password>The truststore related options are independent of client certificate authentication with the CLI; instead, they control the client's trust of the server's certificate.
(cli-jwt-auth)=
To access a Trino cluster configured to use {doc}/security/jwt, use the
--access-token=<token> option to pass a JWT to the server.
(cli-kerberos-auth)=
The Trino CLI can connect to a Trino cluster that has {doc}/security/kerberos
enabled.
Invoking the CLI with Kerberos support enabled requires a number of additional
command line options. You also need the {ref}Kerberos configuration files <server-kerberos-principals> for your user on the machine running the CLI. The
simplest way to invoke the CLI is with a wrapper script:
#!/bin/bash
./trino \
--server https://trino.example.com \
--krb5-config-path /etc/krb5.conf \
--krb5-principal [email protected] \
--krb5-keytab-path /home/someuser/someuser.keytab \
--krb5-remote-service-name trino
When using Kerberos authentication, access to the Trino coordinator must be
through {doc}TLS and HTTPS </security/tls>.
The following table lists the available options for Kerberos authentication:
:::{list-table} CLI options for Kerberos authentication :widths: 40, 60 :header-rows: 1
--krb5-config-path--krb5-credential-cache-path--krb5-disable-remote-service-hostname-canonicalization--krb5-keytab-path--krb5-principal.--krb5-principal--krb5-remote-service-name--krb5-service-principal-pattern${SERVICE}@${HOST}.
:::(cli-kerberos-debug)=
You can enable additional Kerberos debugging information for the Trino CLI
process by passing -Dsun.security.krb5.debug=true,
-Dtrino.client.debugKerberos=true, and
-Djava.security.debug=gssloginconfig,configfile,configparser,logincontext
as a JVM argument when {ref}starting the CLI process <cli-installation>:
java \
-Dsun.security.krb5.debug=true \
-Djava.security.debug=gssloginconfig,configfile,configparser,logincontext \
-Dtrino.client.debugKerberos=true \
-jar trino-cli-*-executable.jar \
--server https://trino.example.com \
--krb5-config-path /etc/krb5.conf \
--krb5-principal [email protected] \
--krb5-keytab-path /home/someuser/someuser.keytab \
--krb5-remote-service-name trino
For help with interpreting Kerberos debugging messages, see {ref}additional resources <kerberos-debug>.
By default, the results of queries are paginated using the less program
which is configured with a carefully selected set of options. This behavior
can be overridden by setting the --pager option or
the TRINO_PAGER environment variable to the name of a different program
such as more or pspg,
or it can be set to an empty value to completely disable pagination.
(cli-history)=
The CLI keeps a history of your previously used commands. You can access your
history by scrolling or searching. Use the up and down arrows to scroll and
{kbd}Control+S and {kbd}Control+R to search. To execute a query again,
press {kbd}Enter.
By default, you can locate the Trino history file in ~/.trino_history.
Use the --history-file option or the TRINO_HISTORY_FILE environment variable
to change the default.
The CLI generates autocomplete suggestions based on command history.
Press {kbd}→ to accept the suggestion and replace the current command line
buffer. Press {kbd}Ctrl+→ ({kbd}Option+→ on Mac) to accept only the next
keyword. Continue typing to reject the suggestion.
The CLI can read default values for all options from a file. It uses the first file found from the ordered list of locations:
TRINO_CONFIG environment variable..trino_config in the current users home directory.$XDG_CONFIG_HOME/trino/config.For example, you could create separate configuration files with different
authentication options, like kerberos-cli.properties and ldap-cli.properties.
Assuming they're located in the current directory, you can set the
TRINO_CONFIG environment variable for a single invocation of the CLI by
adding it before the trino command:
TRINO_CONFIG=kerberos-cli.properties trino https://first-cluster.example.com:8443
TRINO_CONFIG=ldap-cli.properties trino https://second-cluster.example.com:8443
In the preceding example, the default configuration files are not used.
You can use all supported options without the -- prefix in the configuration
properties file. Options that normally don't take an argument are boolean, so
set them to either true or false. For example:
output-format-interactive=AUTO
timezone=Europe/Warsaw
user=trino-client
network-logging=BASIC
krb5-disable-remote-service-hostname-canonicalization=true
Running the Trino CLI with the --execute, --file, or passing queries to
the standard input uses the batch (non-interactive) mode. In this mode
the CLI does not report progress, and exits after processing the supplied
queries. Results are printed in CSV format by default. You can configure
other formats and redirect the output to a file.
The following options are available to further configure the CLI in batch mode:
:::{list-table} :widths: 40, 60 :header-rows: 1
--execute=<execute>-f, --file=<file>--ignore-errors--output-format=<format>CSV.--progressConsider the following command run as shown, or with the
--output-format=CSV option, which is the default for non-interactive usage:
trino --execute 'SELECT nationkey, name, regionkey FROM tpch.sf1.nation LIMIT 3'
The output is as follows:
"0","ALGERIA","0"
"1","ARGENTINA","1"
"2","BRAZIL","1"
The output with the --output-format=JSON option:
{"nationkey":0,"name":"ALGERIA","regionkey":0}
{"nationkey":1,"name":"ARGENTINA","regionkey":1}
{"nationkey":2,"name":"BRAZIL","regionkey":1}
The output with the --output-format=ALIGNED option, which is the default
for interactive usage:
nationkey | name | regionkey
----------+-----------+----------
0 | ALGERIA | 0
1 | ARGENTINA | 1
2 | BRAZIL | 1
The output with the --output-format=VERTICAL option:
-[ RECORD 1 ]--------
nationkey | 0
name | ALGERIA
regionkey | 0
-[ RECORD 2 ]--------
nationkey | 1
name | ARGENTINA
regionkey | 1
-[ RECORD 3 ]--------
nationkey | 2
name | BRAZIL
regionkey | 1
The preceding command with --output-format=NULL produces no output.
However, if you have an error in the query, such as incorrectly using
region instead of regionkey, the command has an exit status of 1
and displays an error message (which is unaffected by the output format):
Query 20200707_170726_00030_2iup9 failed: line 1:25: Column 'region' cannot be resolved
SELECT nationkey, name, region FROM tpch.sf1.nation LIMIT 3
(cli-spooling-protocol)=
The Trino CLI automatically uses the spooling protocol to improve throughput for client interactions with higher data transfer demands, if the is configured on the cluster.
Optionally use the --encoding option to configure a different desired
encoding, compared to the default on the cluster. The available values are
json+zstd (recommended) for JSON with Zstandard compression, and json+lz4
for JSON with LZ4 compression, and json for uncompressed JSON.
The CLI process must have network access to the spooling object storage.
(cli-output-format)=
The Trino CLI provides the options --output-format
and --output-format-interactive to control how the output is displayed.
The available options shown in the following table must be entered
in uppercase. The default value is ALIGNED in interactive mode,
and CSV in non-interactive mode.
:::{list-table} Output format options :widths: 25, 75 :header-rows: 1
CSVCSV_HEADERCSV_UNQUOTEDCSV_HEADER_UNQUOTEDTSVTSV_HEADERJSONALIGNEDVERTICALAUTOALIGNED if output would fit the current terminal width,
and VERTICAL otherwise.MARKDOWNNULL(cli-troubleshooting)=
If something goes wrong, you see an error message:
$ trino
trino> select count(*) from tpch.tiny.nations;
Query 20200804_201646_00003_f5f6c failed: line 1:22: Table 'tpch.tiny.nations' does not exist
select count(*) from tpch.tiny.nations
To view debug information, including the stack trace for failures, use the
--debug option:
$ trino --debug
trino> select count(*) from tpch.tiny.nations;
Query 20200804_201629_00002_f5f6c failed: line 1:22: Table 'tpch.tiny.nations' does not exist
io.trino.spi.TrinoException: line 1:22: Table 'tpch.tiny.nations' does not exist
at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:48)
at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:43)
...
at java.base/java.lang.Thread.run(Thread.java:834)
select count(*) from tpch.tiny.nations