Alternative query languages

Besides standard SQL, ClickHouse supports various alternative query languages for querying data. The currently supported dialects are:

clickhouse: The default SQL dialect of ClickHouse
prql: Pipelined Relational Query Language (PRQL)
kusto: Kusto Query Language (KQL)
trino: Trino SQL

Which query language is used is controlled by setting dialect.

Standard SQL

Standard SQL is the default query language of ClickHouse.

SET dialect = 'clickhouse'

Pipelined relational query language (PRQL)

To enable PRQL:

SET allow_experimental_prql_dialect = 1; -- this SET statement is required only for ClickHouse versions >= v25.1
SET dialect = 'prql'

Example PRQL query:

from trips
aggregate {
    ct = count this
    total_days = sum days
}

Under the hood, ClickHouse uses transpilation from PRQL to SQL to run PRQL queries.

Kusto query language (KQL)

To enable KQL:

SET allow_experimental_kusto_dialect = 1; -- this SET statement is required only for ClickHouse versions >= 25.1
SET dialect = 'kusto'

Query

numbers(10) | project number

Response

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

Note that KQL queries may not be able to access all functions defined in ClickHouse.

Trino SQL

To enable the Trino dialect:

SET allow_experimental_trino_dialect = 1;
SET dialect = 'trino'

Trino-specific syntax is translated to ClickHouse SQL, and Trino function names are mapped to their ClickHouse equivalents:

Query

SELECT approx_distinct(x) FROM (VALUES 1, 2, 2) AS t(x)

Response

┌─uniq(x)─┐
│       2 │
└─────────┘

The translation covers, among other things, ARRAY[...] literals, TRY_CAST, UNNEST (translated to ARRAY JOIN), ROW constructors and types, VALUES tables, OFFSET before LIMIT, BETWEEN SYMMETRIC, and several hundred functions, including the ones whose names exist in both systems with different semantics (for example, Trino length counts Unicode code points and is translated to lengthUTF8). ClickHouse functions that do not collide with Trino names remain accessible, so both function families can be mixed in one query. Trino JSON values are mapped to the ClickHouse JSON type: JSON '...' literals and json_parse become casts to JSON, json_format becomes toJSONString, and the path functions (json_extract, json_extract_scalar, json_value, json_query, json_exists, json_size) are translated to their ClickHouse counterparts. Since the ClickHouse JSON type stores objects, non-object documents (top-level arrays and scalars) are rejected when they are materialized as JSON values; the path functions accept any JSON text. Some Trino semantics are not expressible in the translated query text; they are applied to the query context instead: outer joins produce NULL (join_use_nulls), set operations use the numeric supertype rather than Variant (use_variant_as_common_type), and the query analyzer is required (enable_analyzer). An explicit SETTINGS clause in the query still takes precedence over these. Semantics that cannot be fixed by a translation at the query level remain the ClickHouse ones. Most notably, the division operator / performs integer division in Trino but floating-point division in ClickHouse, and round uses banker’s rounding in ClickHouse for Float arguments. Use EXPLAIN SYNTAX to see how a query is translated.

Fundamentals

ClickHouse concepts

Best practices

Standard SQL

Pipelined relational query language (PRQL)

Kusto query language (KQL)

Trino SQL

​Standard SQL

​Pipelined relational query language (PRQL)

​Kusto query language (KQL)

​Trino SQL

Standard SQL

Pipelined relational query language (PRQL)

Kusto query language (KQL)

Trino SQL