Trino

https://trino.io/docs/current/
Admin
Trino is a distributed SQL query engine designed for high-performance analytics against various data...
Tokens:904,247
Snippets:9,149
Trust Score:9.7
Update:6 days ago
Show doc for...
Tokens:
Raw
### Helm install output example

Source: https://trino.io/docs/current/installation/kubernetes.html

Example output from the helm install command, showing deployment status and notes for accessing the application.

```text
NAME: example-trino-cluster
LAST DEPLOYED: Tue Sep 13 14:12:09 2022
NAMESPACE: default
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
Get the application URL by running these commands:
  export POD_NAME=$(kubectl get pods --namespace default --selector "app.kubernetes.io/name=trino,app.kubernetes.io/instance=example-trino-cluster,app.kubernetes.io/component=coordinator" --output name)
  echo "Visit http://127.0.0.1:8080 to use your application"
  kubectl port-forward $POD_NAME 8080:8080

```

--------------------------------

### Kubernetes resources output example

Source: https://trino.io/docs/current/installation/kubernetes.html

Example output from 'kubectl get all' showing running pods, services, deployments, and replica sets. All pods should show a ready status in the READY column.

```text
NAME                                               READY   STATUS    RESTARTS   AGE
pod/example-trino-cluster-coordinator-bfb74c98d-rnrxd   1/1     Running   0          161m
pod/example-trino-cluster-worker-76f6bf54d6-hvl8n       1/1     Running   0          161m
pod/example-trino-cluster-worker-76f6bf54d6-tcqgb       1/1     Running   0          161m

NAME                       TYPE        CLUSTER-IP    EXTERNAL-IP   PORT(S)    AGE
service/example-trino-cluster   ClusterIP   10.96.25.35   <none>        8080/TCP   161m

NAME                                           READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/example-trino-cluster-coordinator   1/1     1            1           161m
deployment.apps/example-trino-cluster-worker        2/2     2            2           161m

NAME                                                     DESIRED   CURRENT   READY   AGE
replicaset.apps/example-trino-cluster-coordinator-bfb74c98d   1         1         1       161m
replicaset.apps/example-trino-cluster-worker-76f6bf54d6       2         2         2       161m

```

--------------------------------

### START TRANSACTION examples

Source: https://trino.io/docs/current/sql/start-transaction.html

Shows various valid START TRANSACTION statements, including combinations of isolation level and read/write modes.

```sql
START TRANSACTION;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION READ WRITE;
START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;
START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE;

```

--------------------------------

### Start Trino with bin/launcher start

Source: https://trino.io/docs/current/connector/kafka-tutorial.html

Starts the Trino server after configuring the Kafka connector.

```bash
$ bin/launcher start

```

--------------------------------

### Start CLI with default catalog and schema

Source: https://trino.io/docs/current/client/cli.html

Command to start the CLI with default catalog and schema, allowing direct table queries without specifying them. Includes example SHOW TABLES output.

```bash
./trino http://trino.example.com:8080/tpch/tiny
```

```text
trino:tiny> SHOW TABLES;

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

--------------------------------

### Configure SingleStore catalog properties

Source: https://trino.io/docs/current/connector/singlestore.html

Create a catalog properties file in etc/catalog, for example example.properties, to mount the SingleStore connector as the example catalog. Replace connection properties as appropriate for your setup.

```properties
connector.name=singlestore
connection-url=jdbc:singlestore://example.net:3306
connection-user=root
connection-password=secret

```

--------------------------------

### Create BigQuery catalog properties file

Source: https://trino.io/docs/current/connector/bigquery.html

Create a catalog properties file in etc/catalog named example.properties to mount the BigQuery connector as the example catalog. Replace the connection properties as appropriate for your setup.

```properties
connector.name=bigquery
bigquery.project-id=<your Google Cloud Platform project id>

```

--------------------------------

### VARCHAR substring(start, length) method example

Source: https://trino.io/docs/current/language/types.html

Returns the specified number of code points starting at the 1-based start index. Related function: substring().

```sql
SELECT 'Trino'.substring(2, 2);
-- ri
```

--------------------------------

### Create etc/catalog/example.properties

Source: https://trino.io/docs/current/connector/prometheus.html

Configuration file to mount the Prometheus connector as the 'example' catalog. Replace properties as appropriate.

```properties
connector.name=prometheus
prometheus.uri=http://localhost:9090
prometheus.query.chunk.size.duration=1d
prometheus.max.query.range.duration=21d
prometheus.cache.ttl=30s
prometheus.bearer.token.file=/path/to/bearer/token/file
prometheus.read-timeout=10s

```

--------------------------------

### VARCHAR substring(start) method example

Source: https://trino.io/docs/current/language/types.html

Returns the part of the string from the 1-based start index to the end. Related function: substring().

```sql
SELECT 'Trino'.substring(2);
-- rino
```

--------------------------------

### Find continents not starting with 'E' using NOT LIKE

Source: https://trino.io/docs/current/functions/comparison.html

Example query that negates the LIKE condition with NOT to find all continents not starting with 'E'.

```sql
SELECT * FROM (VALUES 'America', 'Asia', 'Africa', 'Europe', 'Australia', 'Antarctica') AS t (continent)
WHERE continent NOT LIKE 'E%';

```

--------------------------------

### Configure example-http connector in etc/catalog/example.properties

Source: https://trino.io/docs/current/release/release-0.54.html

Create this properties file to mount the example-http connector as the example catalog. The metadata-uri must return a JSON document describing table metadata and CSV files.

```properties
connector.name=example-http
metadata-uri=http://s3.amazonaws.com/presto-example/v1/example-metadata.json

```

--------------------------------

### Configure Thrift connector in example.properties

Source: https://trino.io/docs/current/connector/thrift.html

Create etc/catalog/example.properties with these properties. Replace host:port with your Thrift server addresses.

```properties
connector.name=trino_thrift
trino.thrift.client.addresses=host:port,host:port

```

--------------------------------

### VARCHAR trim method example (with characters)

Source: https://trino.io/docs/current/language/types.html

Removes any of the specified characters from both ends of a string. The example removes '#' from '##hi##' to get 'hi'.

```sql
SELECT '##hi##'.trim('#');
-- hi

```

--------------------------------

### IN with NOT negation

Source: https://trino.io/docs/current/functions/comparison.html

Example using NOT IN to get all rows except those in the list.

```sql
SELECT * FROM region WHERE name NOT IN ('AMERICA', 'EUROPE');

```

--------------------------------

### Basic Oracle catalog configuration (example.properties)

Source: https://trino.io/docs/current/connector/oracle.html

Create this file in etc/catalog to configure the Oracle connector as the 'example' catalog. The connection-url syntax varies by Oracle version and configuration; this example connects to an Oracle SID named 'orcl'. connection-user and connection-password are typically required.

```properties
connector.name=oracle
# The correct syntax of the connection-url varies by Oracle version and
# configuration. The following example URL connects to an Oracle SID named
# "orcl".
connection-url=jdbc:oracle:thin:@example.net:1521:orcl
connection-user=root
connection-password=secret

```

--------------------------------

### localtimestamp(p) example

Source: https://trino.io/docs/current/functions/datetime.html

Returns the current timestamp as of the start of the query, with p digits of subsecond precision. The example shows a precision of 6.

```sql
SELECT localtimestamp(6);
-- 2020-06-10 15:55:23.383628
```

--------------------------------

### SHOW TABLES FROM example.default

Source: https://trino.io/docs/current/connector/pinot.html

Lists all tables in the default schema of the Pinot catalog.

```sql
SHOW TABLES FROM example.default;

```

--------------------------------

### bitwise_left_shift() examples

Source: https://trino.io/docs/current/functions/bitwise.html

Returns the left shifted value of value. Examples show shifting 1 by 2 bits to get 4, shifting 5 by 2 bits to get 20, shifting by 0 returns the original value, and shifting 0 always returns 0.

```sql
SELECT bitwise_left_shift(1, 2); -- 4
```

```sql
SELECT bitwise_left_shift(5, 2); -- 20
```

```sql
SELECT bitwise_left_shift(20, 0); -- 20
SELECT bitwise_left_shift(42, 0); -- 42
```

```sql
SELECT bitwise_left_shift(0, 1); -- 0
SELECT bitwise_left_shift(0, 2); -- 0
```

--------------------------------

### USE examples

Source: https://trino.io/docs/current/sql/use.html

Examples of using USE with a catalog and schema, and with only a schema.

```sql
USE hive.finance;
USE information_schema;
```

--------------------------------

### Find continents starting with 'E' using LIKE

Source: https://trino.io/docs/current/functions/comparison.html

Example query that uses LIKE with the `%` wildcard to find continents starting with 'E', returning 'Europe'.

```sql
SELECT * FROM (VALUES 'America', 'Asia', 'Africa', 'Europe', 'Australia', 'Antarctica') AS t (continent)
WHERE continent LIKE 'E%';

```

--------------------------------

### Configure Redis connector in example.properties

Source: https://trino.io/docs/current/connector/redis.html

Create etc/catalog/example.properties with these properties to configure the Redis connector. Replace host:port with the actual Redis server location.

```properties
connector.name=redis
redis.table-names=schema1.table1,schema1.table2
redis.nodes=host:port

```

--------------------------------

### Create catalog with PostgreSQL connector and environment variables

Source: https://trino.io/docs/current/sql/create-catalog.html

Creates a catalog named 'example' using the PostgreSQL connector with properties referencing environment variables. Assumes POSTGRES_USER and POSTGRES_PASSWORD are set as secrets on all nodes.

```sql
CREATE CATALOG example USING postgresql
WITH (
  "connection-url" = 'jdbc:pg:localhost:5432',
  "connection-user" = '${ENV:POSTGRES_USER}',
  "connection-password" = '${ENV:POSTGRES_PASSWORD}',
  "case-insensitive-name-matching" = 'true'
);

```

--------------------------------

### Start Trino CLI in interactive mode

Source: https://trino.io/docs/current/client/cli.html

Minimal command to start the CLI, specifying the coordinator URL. If successful, you get a prompt to execute commands.

```bash
./trino http://trino.example.com:8080
```

--------------------------------

### MATCH_RECOGNIZE example: V-shape pattern

Source: https://trino.io/docs/current/sql/match-recognize.html

Example query that detects a V-shape pattern in the totalprice column of the orders table. It partitions by custkey, orders by orderdate, and uses measures to capture starting, bottom, and top prices. The pattern matches a decrease followed by an increase past the starting point.

```sql
SELECT * FROM orders MATCH_RECOGNIZE(
     PARTITION BY custkey
     ORDER BY orderdate
     MEASURES
              A.totalprice AS starting_price,
              LAST(B.totalprice) AS bottom_price,
              LAST(U.totalprice) AS top_price
     ONE ROW PER MATCH
     AFTER MATCH SKIP PAST LAST ROW
     PATTERN (A B+ C+ D+)
     SUBSET U = (C, D)
     DEFINE
              B AS totalprice < PREV(totalprice),
              C AS totalprice > PREV(totalprice) AND totalprice <= A.totalprice,
              D AS totalprice > PREV(totalprice)
     )
```

--------------------------------

### Configure Redshift connector in example.properties

Source: https://trino.io/docs/current/connector/redshift.html

Create a catalog properties file in etc/catalog, e.g., example.properties, to mount the Redshift connector as the example catalog. Replace connection properties as appropriate. connection-user and connection-password are typically required.

```properties
connector.name=redshift
connection-url=jdbc:redshift://example.net:5439/database
connection-user=root
connection-password=secret

```

--------------------------------

### Configure TPC-DS connector in example.properties

Source: https://trino.io/docs/current/connector/tpcds.html

Create a catalog properties file at etc/catalog/example.properties with this content to enable the TPC-DS connector.

```properties
connector.name=tpcds

```

--------------------------------

### overlay with FOR length

Source: https://trino.io/docs/current/functions/string.html

Replaces a substring of specified length starting at a given position. This example replaces 4 characters starting at position 2, producing 'Thomas'.

```sql
SELECT overlay('Txxxxas' PLACING 'hom' FROM 2 FOR 4);
-- Thomas
```

--------------------------------

### EXPLAIN ANALYZE VERBOSE example query

Source: https://trino.io/docs/current/sql/explain-analyze.html

Example query using the VERBOSE option to get more detailed information. The window function operator may report additional information.

```sql
EXPLAIN ANALYZE VERBOSE SELECT count(clerk) OVER() FROM orders
WHERE orderdate > date '1995-01-01';

```

--------------------------------

### SHOW CREATE TABLE example and output

Source: https://trino.io/docs/current/sql/show-create-table.html

Example command to show the CREATE TABLE statement for the orders table in the sf1 schema, followed by its output. The output displays the CREATE TABLE statement with the table's columns and table properties.

```sql
SHOW CREATE TABLE sf1.orders;
```

```text
              Create Table
-----------------------------------------
 CREATE TABLE tpch.sf1.orders (
    orderkey bigint,
    orderstatus varchar,
    totalprice double,
    orderdate varchar
 )
 WITH (
    format = 'ORC',
    partitioned_by = ARRAY['orderdate']
 )
(1 row)
```

--------------------------------

### OVERLAPS predicate examples

Source: https://trino.io/docs/current/functions/datetime.html

Tests whether two periods share any instant. Operands are row values of start and end (or start and interval). Periods touching only at a boundary do not overlap; sharing the same start always overlaps. NULL endpoints are treated as open-ended.

```sql
SELECT (DATE '2020-01-01', DATE '2020-06-01') OVERLAPS (DATE '2020-05-01', DATE '2020-12-31');
-- true

SELECT (DATE '2020-01-01', DATE '2020-03-01') OVERLAPS (DATE '2020-05-01', DATE '2020-07-01');
-- false

SELECT (DATE '2020-01-01', INTERVAL '5' MONTH) OVERLAPS (DATE '2020-05-01', INTERVAL '7' MONTH);
-- true
```

--------------------------------

### Start Kafka server

Source: https://trino.io/docs/current/connector/kafka-tutorial.html

Command to start the Kafka server using the provided configuration file. The output shows property verification and overrides.

```bash
$ bin/kafka-server-start.sh config/server.properties
[2013-04-22 15:01:47,028] INFO Verifying properties (kafka.utils.VerifiableProperties)
[2013-04-22 15:01:47,051] INFO Property socket.send.buffer.bytes is overridden to 1048576 (kafka.utils.VerifiableProperties)
...
```

--------------------------------

### Configure blackhole catalog in etc/catalog/example.properties

Source: https://trino.io/docs/current/connector/blackhole.html

Create etc/catalog/example.properties to mount the blackhole connector as the example catalog. The only required property is connector.name=blackhole.

```properties
connector.name=blackhole

```

--------------------------------

### ITERATE in a SQL UDF example

Source: https://trino.io/docs/current/udf/sql/iterate.html

Example function that uses ITERATE to skip the increment of b when a is less than or equal to 3, demonstrating how to loop back to the start of a labeled REPEAT block.

```sql
FUNCTION count()
RETURNS bigint
BEGIN
  DECLARE a int DEFAULT 0;
  DECLARE b int DEFAULT 0;
  top: REPEAT
    SET a = a + 1;
    IF a <= 3 THEN
        ITERATE top;
    END IF;
    SET b = b + 1;
  RETURN b;
END

```

--------------------------------

### Show all available MBeans in current schema

Source: https://trino.io/docs/current/connector/jmx.html

Run SHOW TABLES FROM example.current; to list all MBeans available in the current schema.

```sql
SHOW TABLES FROM example.current;

```

--------------------------------

### Example Kafka client configuration file

Source: https://trino.io/docs/current/admin/event-listeners-kafka.html

Example contents of /etc/kafka-configuration.properties, showing SASL authentication settings for accessing a Kafka cluster.

```properties
sasl.mechanism=SCRAM-SHA-512
security.protocol=SASL_SSL
sasl.jaas.config=org.apache.kafka.common.security.scram.ScramLoginModule required \
  username="kafkaclient1" \
  password="kafkaclient1-secret";

```

--------------------------------

### CREATE FUNCTION example with default catalog and schema

Source: https://trino.io/docs/current/sql/create-function.html

Compact syntax for creating the same UDF when the default catalog and schema for UDF storage are configured.

```sql
CREATE FUNCTION meaning_of_life() RETURNS bigint RETURN 42;

```

--------------------------------

### bitwise_right_shift() examples

Source: https://trino.io/docs/current/functions/bitwise.html

Returns the logical right shifted value of value. Examples show shifting 8 by 3 bits to get 1, shifting 9 by 1 bit to get 4, shifting by 0 returns the original value, shifting by 64 or more bits results in 0, and shifting 0 always returns 0.

```sql
SELECT bitwise_right_shift(8, 3); -- 1
```

```sql
SELECT bitwise_right_shift(9, 1); -- 4
```

```sql
SELECT bitwise_right_shift(20, 0); -- 20
SELECT bitwise_right_shift(42, 0); -- 42
```

```sql
SELECT bitwise_right_shift( 12, 64); -- 0
SELECT bitwise_right_shift(-45, 64); -- 0
```

```sql
SELECT bitwise_right_shift(0, 1); -- 0
SELECT bitwise_right_shift(0, 2); -- 0
```

--------------------------------

### Start fluentbit command

Source: https://trino.io/docs/current/admin/logging.html

Command to start fluentbit with the provided config.yaml file.

```bash
fluent-bit -c config.yaml

```

--------------------------------

### VARCHAR starts_with(prefix) method example

Source: https://trino.io/docs/current/language/types.html

Returns true if the string starts with the prefix. Related function: starts_with().

```sql
SELECT 'apple'.starts_with('app');
-- true
```

--------------------------------

### Examples of ALL, ANY, and SOME quantified comparisons

Source: https://trino.io/docs/current/functions/comparison.html

Three example queries demonstrating the use of ANY, ALL, and SOME with subqueries. The comments show the expected result for each query.

```sql
SELECT 'hello' = ANY (VALUES 'hello', 'world'); -- true

SELECT 21 < ALL (VALUES 19, 20, 21); -- false

SELECT 42 >= SOME (SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43); -- true
```

--------------------------------

### Show TPC-DS schemas

Source: https://trino.io/docs/current/connector/tpcds.html

Run SHOW SCHEMAS FROM example to list the available TPC-DS schemas. The output includes information_schema and various scale factor schemas.

```sql
SHOW SCHEMAS FROM example;

```

```text
       Schema
--------------------
 information_schema
 sf1
 sf10
 sf100
 sf1000
 sf10000
 sf100000
 sf300
 sf3000
 sf30000
 tiny
(11 rows)

```

--------------------------------

### extract function example

Source: https://trino.io/docs/current/functions/datetime.html

Shows how to use the SQL-standard extract function to get the year from a timestamp. The result is 2022.

```sql
SELECT extract(YEAR FROM TIMESTAMP '2022-10-20 05:10:00');
-- 2022
```
Tokens:
Raw