Syntax

In this section, we will take a look at ClickHouse’s SQL syntax. ClickHouse uses a syntax based on SQL but offers a number of extensions and optimizations.

Query Parsing

There are two types of parsers in ClickHouse:

• A full SQL parser (a recursive descent parser).
• A data format parser (a fast stream parser).

The full SQL parser is used in all cases except for the INSERT query, which uses both parsers.

Let’s examine the query below:

INSERT INTO t VALUES (1, 'Hello, world'), (2, 'abc'), (3, 'def')

As mentioned already, the INSERT query makes use of both parsers. The INSERT INTO t VALUES fragment is parsed by the full parser, and the data (1, 'Hello, world'), (2, 'abc'), (3, 'def') is parsed by the data format parser, or fast stream parser.

The data can have any format. When a query is received, the server calculates no more than max_query_size bytes of the request in RAM (by default, 1 MB), and the rest is stream parsed. This is to allow for avoiding issues with large INSERT queries, which is the recommended way to insert your data in ClickHouse.

When using the Values format in an INSERT query, it may appear that data is parsed the same as for expressions in a SELECT query however this is not the case. The Values format is much more limited.

The rest of this section covers the full parser.

Spaces

• There may be any number of space symbols between syntactical constructions (including the beginning and end of a query).
• Space symbols include the space, tab, line feed, CR, and form feed.

Comments

ClickHouse supports both SQL-style and C-style comments:

• SQL-style comments begin with --, #! or # and continue to the end of the line. A space after -- and #! can be omitted.
• C-style comments:
  • // (or more than 2 / characters) followed by text until the end of the line. Spaces after / are not required.
  • Can span from /* to */ for multiline comments. Spaces are not required either.
  • C-style comments can be nested.

For example:

/*
 * Compute the number of days between two dates.
 * /* Returns NULL if either argument is NULL */
 */
SELECT
    dateDiff('day', toDate('2024-01-01'), toDate('2024-12-31')) AS days_in_year, -- 365
    dateDiff('day', toDate('2020-01-01'), today()) AS days_since  #! since 2020
    ///////////////////////////////////////////////////////////////////
    # TODO: add hour/minute variants

Keywords

Keywords in ClickHouse can be either case-sensitive or case-insensitive depending on the context.

Keywords are case-insensitive when they correspond to:

• SQL standard. For example, SELECT, select and SeLeCt are all valid.
• Implementation in some popular DBMS (MySQL or Postgres). For example, DateTime is the same as datetime.

In contrast to standard SQL, all other keywords (including functions names) are case-sensitive.

Furthermore, Keywords are not reserved. They are treated as such only in the corresponding context. If you use identifiers with the same name as the keywords, enclose them into double-quotes or backticks.

For example, the following query is valid if the table table_name has a column with the name "FROM":

SELECT "FROM" FROM table_name

Identifiers

Identifiers are:

• Cluster, database, table, partition, and column names.
• Functions.
• Data types.
• Expression aliases.

Identifiers can be quoted or non-quoted, although the latter is preferred.

Non-quoted identifiers must match the regex ^[a-zA-Z_][0-9a-zA-Z_]*$ and cannot be equal to keywords. See the table below for examples of valid and invalid identifiers:

If you want to use identifiers the same as keywords or you want to use other symbols in identifiers, quote it using double quotes or backticks, for example, "id", `id`.

Literals

In ClickHouse, a literal is a value which is directly represented in a query. In other words it is a fixed value which does not change during query execution.

Literals can be:

• String
• Numeric
• Compound
• NULL
• Heredocs (custom string literals)

We take a look at each of these in more detail in the sections below.

String

String literals must be enclosed in single quotes. Double quotes are not supported.

Escaping works by either:

• using a preceding single quote where the single-quote character ' (and only this character) can be escaped as '', or
• using the preceding backslash with the following supported escape sequences listed in the table below.

Numeric

Numeric literals are parsed as follows:

• If the literal is prefixed with a minus sign -, the token is skipped and the result is negated after parsing.
• The numeric literal is first parsed as a 64-bit unsigned integer, using the strtoull function.
  • If the value is prefixed with 0b or 0x/0X, the number is parsed as binary or hexadecimal, respectively.
  • If the value is negative and the absolute magnitude is greater than 2⁶³, an error is returned.
• If unsuccessful, the value is next parsed as a floating-point number using the strtod function.
• Otherwise, an error is returned.

Literal values are cast to the smallest type that the value fits in. For example:

• 1 is parsed as UInt8
• 256 is parsed as UInt16.

For more information, see Data types.

Underscores _ inside numeric literals are ignored and can be used for better readability.

The following Numeric literals are supported:

Compound

Arrays are constructed with []: [1, 2, 3]. Tuples are constructed with (): (1, 'Hello, world!', 2). Technically these are not literals, but expressions with the array creation operator and the tuple creation operator, respectively. An array must consist of at least one item, and a tuple must have at least two items.

NULL

NULL is used to indicate that a value is missing. To store NULL in a table field, it must be of the Nullable type.

Heredoc

A heredoc is a way to define a string (often multiline), while maintaining the original formatting. A heredoc is defined as a custom string literal, placed between two $ symbols.

For example:

SELECT $heredoc$SHOW CREATE VIEW my_view$heredoc$;

┌

Defining and Using Query Parameters

Query parameters allow you to write generic queries that contain abstract placeholders instead of concrete identifiers. When a query with query parameters is executed, all placeholders are resolved and replaced by the actual query parameter values.

Query parameters can be defined in several ways:

• SET param_<name>=<value> — using a SET command in a query.
• --param_<name>='<value>' — as an argument to clickhouse-client on the command line.
• param_<name>=<value> — as a URL query string parameter for the HTTP interface.

A query parameter can be referenced in a query using {<name>: <datatype>}, where <name> is the query parameter name and <datatype> is the datatype it is converted to.

SET param_a = 13;
SET param_b = 'str';
SET param_c = '2022-08-04 18:30:53';
SET param_d = {'10': [11, 12], '13': [14, 15]};

SELECT
   {a: UInt32},
   {b: String},
   {c: DateTime},
   {d: Map(String, Array(UInt8))};

13    str    2022-08-04 18:30:53    {'10':[11,12],'13':[14,15]}

clickhouse-client --param_message='hello' --query="SELECT {message: String}"

hello

SET param_mytablename = "uk_price_paid";
SELECT * FROM {mytablename:Identifier};

curl -s "http://localhost:8123/?param_message=hello" --data-binary "SELECT {message: String}"

hello

Functions

Function calls are written like an identifier with a list of arguments (possibly empty) in (). In contrast to standard SQL, the brackets are required, even for an empty argument list. For example:

now()

There are also:

• Regular functions.
• Aggregate functions.

Some aggregate functions can contain two lists of arguments in brackets. For example:

quantile (0.9)(x) 

These aggregate functions are called “parametric” functions, and the arguments in the first list are called “parameters”.

Operators

Operators are converted to their corresponding functions during query parsing, taking their priority and associativity into account.

For example, the expression

1 + 2 * 3 + 4

is transformed to

plus(plus(1, multiply(2, 3)), 4)`

Data Types and Database Table Engines

Data types and table engines in the CREATE query are written the same way as identifiers or functions. In other words, they may or may not contain an argument list in brackets.

For more information, see the sections:

• Data types
• Table engines
• CREATE.

Expressions

An expression can be any of the following:

• a function
• an identifier
• a literal
• the application of an operator
• an expression in brackets
• a subquery
• an asterisk

It can also contain an alias.

A list of expressions is one or more expressions separated by commas. Functions and operators, in turn, can have expressions as arguments.

A constant expression is an expression whose result is known during query analysis, i.e. before execution. For example, expressions over literals are constant expressions.

Expression Aliases

An alias is a user-defined name for an expression in a query.

expr AS alias

The parts of the syntax above are explained below.

Notes on Usage

• Aliases are global for a query or subquery, and you can define an alias in any part of a query for any expression. For example:

SELECT (1 AS n) + 2, n`.

• Aliases are not visible in subqueries and between subqueries. For example, while executing the following query ClickHouse generates the exception Unknown identifier: num:

`SELECT (SELECT sum(b.a) + num FROM b) - a.a AS num FROM a`

• If an alias is defined for the result columns in the SELECT clause of a subquery, these columns are visible in the outer query. For example:

SELECT n + m FROM (SELECT 1 AS n, 2 AS m)`.

• Be careful with aliases that are the same as column or table names. Let’s consider the following example:

CREATE TABLE t
(
    a Int,
    b Int
)
ENGINE = TinyLog();

SELECT
    argMax(a, b),
    sum(b) AS b
FROM t;

Received exception from server (version 18.14.17):
Code: 184. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Aggregate function sum(b) is found inside another aggregate function in query.

In the preceding example, we declared table t with column b. Then, when selecting data, we defined the sum(b) AS b alias. As aliases are global, ClickHouse substituted the literal b in the expression argMax(a, b) with the expression sum(b). This substitution caused the exception.

Asterisk

In a SELECT query, an asterisk can replace the expression. For more information, see the section SELECT.