Writing Trino Queries

The following information can help you learn how to write Trino queries.

What follows is not a complete set of SQL statements. These are just some basic SQL functions that you might use in your queries.

You can use the SELECT statement to select data from a database. The data returned is stored in a results table, called the result-set.

SELECT … FROM … WHERE … LIMIT

Example

SELECT contact_id, last_name, first_name
FROM contacts
WHERE website = "Company_Organization.com"
ORDER BY contact_id DESC
LIMIT 5;

Use aggregate functions to perform a calculation on one or more values and return a single value.

Use the GROUP BY command to group the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG).

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

The MIN() function returns the smallest value of the selected column.

Example

SELECT MIN(columnname)
FROM table_name
WHERE condition;

The MAX() function returns the largest value of the selected column.

Example

SELECT MAX(column_name)
FROM table_name
WHERE condition;

MIN_BY takes two arguments and returns the value of the first argument for which the value of the second argument is minimized.

SELECT MIN_BY(close_date, close_value) as date_of_min_sale
FROM sales_pipeline

MAX_BY takes two arguments and returns the value of the first argument for which the value of the second argument is maximized.

APPROX_DISTINCT calculates an approximate count of the number of distinct values.

Example

SELECT APPROX_DISTINCT(close_value)
  FROM sales_pipeline

APPROX_PRECENTILE calculates the value at a given percentile of a distribution of values.

Example

SELECT APPROX_PERCENTILE(close_value, 0.75)
FROM sales_pipeline

Use TD_TIME_RANGE and TD_INTERVAL.

Use TD_TIME_FORMAT and TD_TIME_STRING.

Use TD_DATE_TRUNC

Use TD_SCHEDULED_TIME

The following SQL statements can help you create and update tables in Trino.

Create a new table containing the result of a select query. Use CREATE TABLE to create an empty table. You might want to use these optional clauses:

• IF NOT EXISTS clause causes the error to be suppressed if the table already exists.
• WITH clause can be used to set properties on the newly created table.

Example

CREATE TABLE [ IF NOT EXISTS ] table_name [ ( column_alias, ... ) ]
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]

Insert data into an existing table. The following example uses person as the name of the table.

Example

insert into person values (1, 'james', 10, '20190301');

Window functions perform calculations across rows of the query result. They run after the HAVING clause but before the ORDER BY clause. Invoking a window function requires special syntax using the OVER clause to specify the window. A window has these three components:

• Partition Specification. Separates the input rows into different partitions. This is analogous to how the GROUP BY clause separates rows into different groups for aggregate functions.
• Ordering Specification. Determines the order in which input rows are processed by the window function.
• Window Frame. Specifies a sliding window of rows to be processed by the function for a given row. If the frame is not specified, it defaults to RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This frame contains all rows from the start of the partition up to the last peer of the current row.

For example, the following query ranks orders for each clerk by price.

Example

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

Trino implicitly converts numeric and character values to the correct type if such a conversion is possible. Trino does not convert between character and numeric types. For example, a query that expects a varchar does not automatically convert a bigint value to an equivalent varchar.

When necessary, values can be explicitly cast to a particular type.

Conversion Functions

cast(value AS type) → type

Explicitly cast a value as a type. This can be used to cast a varchar to a numeric value type and vice versa.

try_cast(value AS type) → type

Like cast(), but returns null if the cast fails.

You can write string functions to extract, find like, replace, and split data.

Learn more about Trino Expression Functions.

Use JSON functions and operators to process data.

Learn more about Trino JSON Functions and Operators

There are many mathematical operators available in Trino that you can use to process data.

Here are a few you might find useful:

• ceil(x) → [same as input]

  This is an alias for ceiling().

• floor(x) → [same as input]

  Returns x rounded down to the nearest integer.

• round(x, d) → [same as input]

  Returns x rounded to d decimal places

To review all mathematical operators, see Trino Documentation: Mathematical Functions and Operators.

The WITH clause is useful for nested queries as shown in this example query:

SELECT a, b, c
FROM (
  SELECT a, MAX(b) AS b, MIN(c) AS c FROM tbl GROUP BY a
) tbl_alias

The same query can be written with the WITH clause as follows:

WITH tbl_alias AS(
  SELECT a, MAX(b) AS b, MIN(c) AS c FROM tbl GROUP BY a
)
SELECT a, b, c FROM tbl_alias

Notice that the query has been “de-nested”.

The following example shows multiple subqueries use WITH. Each subquery is delimited by a comma.

WITH tbl1 AS(
  SELECT a, MAX(b) AS b, MIN(c) AS c FROM tbl GROUP BY a
),
tbl2 AS(
  SELECT a, AVG(d) AS d FROM another_tbl GROUP BY a
)
SELECT tbl1.*, tbl2.*
FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a

If you want to quickly test Trino syntax, you can use VALUES to create a table immediately.

SELECT a, b, c
FROM (
  VALUES
    (1, 2.0, 'x'),
    (3, 4.0, 'y'),
    (5, 6.0, 'z')
) tbl(a, b, c)

The above query returns the following table:

To clean up a table before using CREATE TABLE AS or INSERT INTO statements, use multiple statements split by semi-colon.

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable AS SELECT ...;

Multiple-statement execution is not guarded by a transaction, multiple update operations in a single job are not atomic but eventual consistency is maintained. For example, assuming the following job failed after I1:

-- I1
INSERT INTO mytable SELECT ...;

-- I2
INSERT INTO mytable SELECT ...;

If the system finds a sporadic failure during the job execution, it will start the job from scratch, but operations once succeeded were skipped. So the actual execution could be I1 (success), I2 (failure), (retry from scratch), I1 (skipped) and I2 (success). The final result will be correct.

You can view query signatures from the Trino Utilization dashboard and use the query signatures to identify and group similar queries.

The Trino Utilization dashboard is available upon request for most customers.

Trino query signatures are clusters of Trino queries that share common SQL statements. Similar jobs, jobs with only a few differences (such as the date range computed), are grouped together. You can then analyze the clusters of queries and determine which queries require the most or least resources in a given month.

The Trino Query Details table, which is posted on the Trino Utilization dashboard, includes a table column: Query Names or Signature.

• Query Signature Definitions for Common Letters
• Example of Query Signatures

Query Signatures are subject to change. The following list defines common query letters that help to decipher the query that the query signature represents:

The following example shows SQL statements mapped to query signatures: