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 ParsingThere are two types of parsers in ClickHouse:
The full SQL parser is used in all cases except for the INSERT query, which uses both parsers.
Let's examine the query below:
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.
You can also turn on the full parser for the data by using the input_format_values_interpret_expressions setting.
When the aforementioned setting is set to 1, ClickHouse first tries to parse values with the fast stream parser. If it fails, ClickHouse tries to use the full parser for the data, treating it like an SQL expression.
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.
Note
For more information about format parsers, see the Formats section.
SpacesClickHouse supports both SQL-style and C-style comments:
--, #! or # and continue to the end of the line. A space after -- and #! can be omitted./* to */ and can be multiline. Spaces are not required either.Keywords in ClickHouse can be either case-sensitive or case-insensitive depending on the context.
Keywords are case-insensitive when they correspond to:
SELECT, select and SeLeCt are all valid.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":
Identifiers are:
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:
xyz, _internal, Id_with_underscores_123_ 1x, [email protected], äußerst_schön
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`.
Note
The same rules that apply for escaping in quoted identifiers also apply for string literals. See String for more details.
LiteralsIn 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:
We take a look at each of these in more detail in the sections below.
StringString literals must be enclosed in single quotes. Double quotes are not supported.
Escaping works by either:
' (and only this character) can be escaped as '', orNote
The backslash loses its special meaning i.e. it is interpreted literally should it precede characters other than the ones listed below.
Supported Escape Description\xHH 8-bit character specification followed by any number of hex digits (H). \N reserved, does nothing (eg SELECT 'a\Nb' returns ab) \a alert \b backspace \e escape character \f form feed \n line feed \r carriage return \t horizontal tab \v vertical tab \0 null character \\ backslash \' (or '') single quote \" double quote ` backtick \/ forward slash \= equal sign ASCII control characters (c <= 31).
Note
In string literals, you need to escape at least ' and \ using escape codes \' (or: '') and \\.
Numeric literals are parsed as follows:
Literal values are cast to the smallest type that the value fits in. For example:
1 is parsed as UInt8256 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:
Numeric Literal Examples Integers1, 10_000_000, 18446744073709551615, 01 Decimals 0.1 Exponential notation 1e100, -1e-100 Floating point numbers 123.456, inf, nan Hex 0xc0fe SQL Standard compatible hex string x'c0fe' Binary 0b1101 SQL Standard compatible binary string b'1101'
Note
Octal literals are not supported to avoid accidental errors in interpretation.
CompoundArrays are constructed with square brackets [1, 2, 3]. Tuples are constructed with round brackets (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.
Note
There is a separate case when tuples appear in the IN clause of a SELECT query. Query results can include tuples, but tuples cannot be saved to a database (except for tables using the Memory engine).
NULL is used to indicate that a value is missing. To store NULL in a table field, it must be of the Nullable type.
Note
The following should be noted for NULL:
NULL may have a different representation. For more information, see data formats.NULL processing is nuanced. For example, if at least one of the arguments of a comparison operation is NULL, the result of this operation is also NULL. The same is true for multiplication, addition, and other operations. We recommend to read the documentation for each operation.NULL using the IS NULL and IS NOT NULL operators and the related functions isNull and isNotNull.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:
Note
Tip
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.
There are two ways to define a query parameter:
SET param_<name>=<value>--param_<name>='<value>'When using the second variant, it is passed as an argument to clickhouse-client on the command line where:
<name> is the name of the query parameter.<value> is its value.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.
For example, the following SQL defines parameters named a, b, c and d - each with a different data type:
If you are using clickhouse-client, the parameters are specified as --param_name=value. For example, the following parameter has the name message and it is retrieved as a String:
If the query parameter represents the name of a database, table, function or other identifier, use Identifier for its type. For example, the following query returns rows from a table named uk_price_paid:
Note
Query parameters are not general text substitutions which can be used in arbitrary places in arbitrary SQL queries. They are primarily designed to work in SELECT statements in place of identifiers or literals.
Function calls are written like an identifier with a list of arguments (possibly empty) in round brackets. In contrast to standard SQL, the brackets are required, even for an empty argument list. For example:
There are also:
Some aggregate functions can contain two lists of arguments in brackets. For example:
These aggregate functions are called "parametric" functions, and the arguments in the first list are called "parameters".
Note
The syntax of aggregate functions without parameters is the same as for regular functions.
OperatorsOperators are converted to their corresponding functions during query parsing, taking their priority and associativity into account.
For example, the expression
is transformed to
Data Types and Database Table EnginesData 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:
ExpressionsAn expression can be the following:
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.
Expression AliasesAn alias is a user-defined name for an expression in a query.
The parts of the syntax above are explained below.
Part of syntax Description Example NotesAS The keyword for defining aliases. You can define the alias for a table name or a column name in a SELECT clause without using the AS keyword. SELECT table_name_alias.column_name FROM table_name table_name_alias. In the CAST function, the AS keyword has another meaning. See the description of the function. expr Any expression supported by ClickHouse. SELECT column_name * 2 AS double FROM some_table alias Name for expr. Aliases should comply with the identifiers syntax. SELECT "table t".column_name FROM table_name AS "table t". Notes on Usage
Unknown identifier: num:SELECT clause of a subquery, these columns are visible in the outer query. For example: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.
In a SELECT query, an asterisk can replace the expression. For more information, see the section SELECT.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4