Синтаксис

Документация по синтаксису

В этом разделе мы рассмотрим синтаксис SQL в ClickHouse. ClickHouse использует синтаксис на основе SQL, но предлагает ряд расширений и оптимизаций.

​

Разбор запросов

В ClickHouse есть два типа парсеров:

• Полный SQL-парсер (парсер рекурсивного спуска).
• Парсер формата данных (быстрый потоковый парсер).

Полный SQL-парсер используется во всех случаях, кроме запроса INSERT, в котором используются оба парсера.

Давайте рассмотрим приведённый ниже запрос:

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

Как уже упоминалось, запрос INSERT использует оба парсера. Фрагмент INSERT INTO t VALUES разбирается полным парсером, а данные (1, 'Hello, world'), (2, 'abc'), (3, 'def') — парсером формата данных, или быстрым потоковым парсером.

Включение полного парсера

Вы также можете включить для данных полный парсер с помощью настройки input_format_values_interpret_expressions.

Когда указанная выше настройка имеет значение 1, ClickHouse сначала пытается разобрать значения быстрым потоковым парсером. Если это не удаётся, ClickHouse пытается использовать для данных полный парсер, рассматривая их как SQL-выражение.

Данные могут быть в любом формате. При получении запроса сервер держит в оперативной памяти не более max_query_size байт запроса (по умолчанию 1 МБ), а остальная часть разбирается потоково. Это позволяет избежать проблем с большими запросами INSERT, которые являются рекомендуемым способом вставки данных в ClickHouse.

При использовании формата Values в запросе INSERT может показаться, что данные разбираются так же, как и выражения в запросе SELECT, однако это не так. Формат Values значительно более ограничен.

Остальная часть этого раздела посвящена полному парсеру.

Дополнительные сведения о парсерах форматов см. в разделе Formats.

​

Пробелы

• Между синтаксическими конструкциями может находиться любое количество пробельных символов (в том числе в начале и в конце запроса).
• К пробельным символам относятся пробел, табуляция, перевод строки, CR и перевод страницы.

​

Комментарии

ClickHouse поддерживает комментарии как в стиле SQL, так и в стиле C:

• Комментарии в стиле SQL начинаются с --, #! или # и продолжаются до конца строки. Пробел после -- и #! можно опускать.
• Комментарии в стиле C:
  • // (или более двух символов /), после которых идёт текст до конца строки. Пробелы после / не обязательны.
  • Для многострочных комментариев могут начинаться с /* и заканчиваться */. Пробелы также не обязательны.
  • Комментарии в стиле C могут быть вложенными.

Например:

/*
 * Вычислить количество дней между двумя датами.
 * /* Возвращает NULL, если любой из аргументов равен 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: добавить варианты для часов/минут

​

Ключевые слова

Ключевые слова в ClickHouse могут быть чувствительными к регистру или регистронезависимыми — в зависимости от контекста.

Ключевые слова регистронезависимы, если они относятся к:

• стандарту SQL. Например, SELECT, select и SeLeCt — все это допустимые варианты.
• реализации в некоторых популярных СУБД (MySQL или Postgres). Например, DateTime — это то же самое, что и datetime.

Проверить, чувствительно ли имя типа данных к регистру, можно в таблице system.data_type_families.

В отличие от стандартного SQL, все остальные ключевые слова (включая имена функций) чувствительны к регистру.

Кроме того, ключевые слова не являются зарезервированными. Они считаются таковыми только в соответствующем контексте. Если вы используете идентификаторы с тем же именем, что и ключевые слова, заключайте их в двойные или обратные кавычки.

Например, следующий запрос допустим, если в таблице table_name есть столбец с именем "FROM":

SELECT "FROM" FROM table_name

​

Идентификаторы

Идентификаторы — это:

• Имена кластеров, баз данных, таблиц, партиций и столбцов.
• Функции.
• Типы данных.
• Псевдонимы выражений.

Идентификаторы могут быть заключены в кавычки или записаны без кавычек, хотя предпочтителен второй вариант.

Идентификаторы без кавычек должны соответствовать регулярному выражению ^[a-zA-Z_][0-9a-zA-Z_]*$ и не могут совпадать с ключевыми словами. В таблице ниже приведены примеры допустимых и недопустимых идентификаторов:

Допустимые идентификаторы	Недопустимые идентификаторы
xyz, _internal, Id_with_underscores_123_	1x, [e-mail], äußerst_schön

Если вы хотите использовать идентификаторы, совпадающие с ключевыми словами, или использовать в идентификаторах другие символы, заключайте их в двойные кавычки или обратные кавычки, например "id" или `id`.

Те же правила экранирования, что и для идентификаторов в кавычках, применяются и к строковым литералам. Подробнее см. в разделе String.

Избегайте использования точек в именах столбцов

Имена столбцов, содержащие точки, столбцы с общим префиксом перед точкой и столбцы с типом Array могут интерпретироваться как часть плоской структуры Nested, когда flatten_nested = 1 (по умолчанию). Это может привести к неожиданной проверке длины массивов при вставке и ограничениям на переименование.

По возможности избегайте использования точек в именах столбцов. Используйте символы подчёркивания (_) или другой разделитель вместо точек в именах столбцов, если только вам не нужна семантика Nested.

​

Литералы

В ClickHouse литерал — это значение, которое напрямую указано в запросе. Иными словами, это фиксированное значение, которое не меняется в ходе выполнения запроса.

Литералы могут быть:

• Строковыми
• Числовыми
• Составными
• NULL
• Heredocs (пользовательские строковые литералы)

Ниже мы подробнее рассмотрим каждый из этих типов в соответствующих разделах.

​

String

Строковые литералы должны быть заключены в одинарные кавычки. Двойные кавычки не поддерживаются.

Экранирование выполняется одним из двух способов:

• с помощью предшествующей одинарной кавычки: символ одинарной кавычки ' (и только он) можно экранировать как '', или
• с помощью предшествующей обратной косой черты с использованием поддерживаемых escape-последовательностей, перечисленных в таблице ниже.

Обратная косая черта теряет своё специальное значение, то есть интерпретируется буквально, если за ней следуют символы, отличные от перечисленных ниже.

Поддерживаемое экранирование	Описание
\xHH	Обозначение 8-битного символа, за которым следует любое количество шестнадцатеричных цифр (H).
\N	зарезервировано, ничего не делает (например, SELECT 'a\Nb' возвращает ab)
\a	звуковой сигнал
\b	возврат на один символ
\e	escape-символ
\f	перевод страницы
\n	перевод строки
\r	возврат каретки
\t	горизонтальная табуляция
\v	вертикальная табуляция
\0	нулевой символ
\\	обратная косая черта
\' (or '')	одинарная кавычка
\"	двойная кавычка
`	обратная кавычка
\/	прямая косая черта
\=	знак равенства
Управляющие символы ASCII (c <= 31).

В строковых литералах необходимо экранировать как минимум ' и \, используя escape-коды \' (или '') и \\.

​

Числовые литералы

Числовые литералы разбираются следующим образом:

• Если литерал начинается со знака минус -, токен пропускается, а после разбора к результату применяется отрицание.
• Сначала числовой литерал разбирается как 64-битное беззнаковое целое число с помощью функции strtoull.
  • Если значение начинается с 0b или 0x/0X, число разбирается как двоичное или шестнадцатеричное соответственно.
  • Если значение отрицательное и его абсолютная величина больше 2⁶³, возвращается ошибка.
• Если это не удалось, значение затем разбирается как число с плавающей точкой с помощью функции strtod.
• В противном случае возвращается ошибка.

Значения литералов приводятся к наименьшему типу, в который помещается значение. Например:

• 1 разбирается как UInt8
• 256 разбирается как UInt16.

Важно

Целочисленные значения разрядностью более 64 бит (UInt128, Int128, UInt256, Int256) необходимо приводить к более широкому типу, чтобы они разбирались корректно:

-170141183460469231731687303715884105728::Int128
340282366920938463463374607431768211455::UInt128
-57896044618658097711785492504343953926634992332820282019728792003956564819968::Int256
115792089237316195423570985008687907853269984665640564039457584007913129639935::UInt256

Это позволяет обойти описанный выше алгоритм и разобрать целое число с помощью процедуры, поддерживающей произвольную точность.

В противном случае литерал будет разобран как число с плавающей точкой и, следовательно, может потерять точность из-за усечения.

Дополнительные сведения см. в разделе Типы данных.

Символы подчёркивания _ внутри числовых литералов игнорируются и могут использоваться для лучшей читаемости.

Поддерживаются следующие числовые литералы:

Числовой литерал	Примеры
Целые числа	1, 10_000_000, 18446744073709551615, 01
Десятичные числа	0.1
Экспоненциальная запись	1e100, -1e-100
Числа с плавающей точкой	123.456, inf, nan
Шестнадцатеричные	0xc0fe
Шестнадцатеричная строка, совместимая со стандартом SQL	x'c0fe'
Двоичные	0b1101
Двоичная строка, совместимая со стандартом SQL	b'1101'

Восьмеричные литералы не поддерживаются во избежание случайных ошибок интерпретации.

​

Составные

Массивы создаются с помощью []: [1, 2, 3]. Кортежи создаются с помощью (): (1, 'Hello, world!', 2). Технически это не литералы, а выражения с оператором создания массива и оператором создания кортежа соответственно. Массив должен состоять как минимум из одного элемента, а кортеж — как минимум из двух.

Есть отдельный случай, когда кортежи встречаются в предложении IN запроса SELECT. Результаты запроса могут содержать кортежи, но сохранять кортежи в базу данных нельзя (кроме таблиц с движком Memory).

​

NULL

NULL используется для обозначения отсутствующего значения. Чтобы сохранить NULL в поле таблицы, оно должно иметь тип Nullable.

Для NULL следует учитывать следующее:

• В зависимости от формата данных (входного или выходного) NULL может иметь разное представление. Подробнее см. в разделе форматы данных.
• Обработка NULL имеет свои нюансы. Например, если хотя бы один из аргументов операции сравнения равен NULL, результат этой операции также будет NULL. То же самое относится к умножению, сложению и другим операциям. Рекомендуем ознакомиться с документацией по каждой операции.
• В запросах проверять NULL можно с помощью операторов IS NULL и IS NOT NULL, а также связанных с ними функций isNull и isNotNull.

​

Heredoc

Heredoc — это способ задать строку (часто многострочную), сохранив исходное форматирование. Heredoc представляет собой пользовательский строковый литерал, заключённый между двумя символами $.

Например:

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

┌─'SHOW CREATE VIEW my_view'─┐
│ SHOW CREATE VIEW my_view   │
└────────────────────────────┘

• Значение между двумя блоками heredoc обрабатывается “как есть”.

• С помощью heredoc можно встраивать фрагменты кода SQL, HTML, XML и т. д.

​

Определение и использование параметров запроса

Параметры запроса позволяют писать универсальные запросы, содержащие абстрактные плейсхолдеры вместо конкретных идентификаторов. Когда выполняется запрос с параметрами, все плейсхолдеры разрешаются и заменяются фактическими значениями параметров запроса.

Параметры запроса можно задавать несколькими способами:

• SET param_<name>=<value> — с помощью команды SET в запросе.
• --param_<name>='<value>' — как аргумент командной строки для clickhouse-client.
• param_<name>=<value> — как параметр строки запроса URL для HTTP-интерфейса.

На параметр запроса можно ссылаться в запросе с помощью {<name>: <datatype>}, где <name> — имя параметра запроса, а <datatype> — тип данных, к которому он приводится.

Пример с командой SET

Например, следующий SQL определяет параметры с именами a, b, c и d — каждый со своим типом данных:

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

Если вы используете clickhouse-client, параметры указываются в виде --param_name=value. Например, следующий параметр имеет имя message и извлекается как String:

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

hello

Если параметр запроса представляет имя базы данных, таблицы, функции или другого идентификатора, используйте Identifier в качестве его типа. Например, следующий запрос возвращает строки из таблицы uk_price_paid:

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

Пример с HTTP-интерфейсом

Параметры запроса можно передавать как параметры строки запроса URL с префиксом param_. Например:

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

hello

Пример с веб-интерфейсом

Встроенный веб-интерфейс (play.html) автоматически обнаруживает плейсхолдеры параметров {name:Type} в запросе и отображает для каждого параметра подписанное поле ввода. Значения параметров включаются в HTTP-запрос, а также сохраняются в URL страницы для добавления в закладки и общего доступа.

Параметры запроса — это не универсальные текстовые подстановки, которые можно использовать в любых местах произвольных SQL-запросов. Они в первую очередь предназначены для работы в операторах SELECT вместо идентификаторов или литералов.

​

Функции

Вызовы функций записываются в виде идентификатора со списком аргументов (возможно, пустым) в (). В отличие от стандартного SQL, скобки обязательны даже при пустом списке аргументов. Например:

now()

Также есть:

• Обычные функции.
• Агрегатные функции.

Некоторые агрегатные функции могут содержать в скобках два списка аргументов. Например:

quantile (0.9)(x) 

Эти агрегатные функции называются “параметрическими”, а аргументы в первом списке — “параметрами”.

Синтаксис агрегатных функций без параметров такой же, как у обычных функций.

​

Операторы

Операторы преобразуются в соответствующие функции при разборе запроса с учетом их приоритета и ассоциативности.

Например, выражение

1 + 2 * 3 + 4

преобразуется в

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

​

Типы данных и движки таблиц базы данных

Типы данных и движки таблиц в запросе CREATE записываются так же, как идентификаторы и функции. Иными словами, они могут как содержать список аргументов в скобках, так и не содержать его.

Дополнительные сведения см. в разделах:

• Типы данных
• Движки таблиц
• CREATE.

​

Выражения

Выражением может быть любое из следующего:

• функция
• идентификатор
• литерал
• применение оператора
• выражение в скобках
• подзапрос
• звёздочка

Оно также может содержать псевдоним.

Список выражений — это одно или несколько выражений, разделённых запятыми. Функции и операторы, в свою очередь, могут использовать выражения в качестве аргументов.

Константное выражение — это выражение, результат которого известен во время анализа запроса, то есть до выполнения. Например, выражения, состоящие из литералов, являются константными выражениями.

​

Псевдонимы выражений

Псевдоним — это заданное пользователем имя для выражения в запросе.

expr AS alias

Ниже поясняются элементы приведённого выше синтаксиса.

Часть синтаксиса	Описание	Пример	Примечания
AS	Ключевое слово для задания псевдонимов. Псевдоним для имени таблицы или имени столбца в предложении SELECT можно задать и без ключевого слова AS.	SELECT table_name_alias.column_name FROM table_name table_name_alias.	В функции CAST ключевое слово AS имеет другое значение. См. описание функции.
expr	Любое выражение, поддерживаемое ClickHouse.	SELECT column_name * 2 AS double FROM some_table
alias	Имя для expr. Псевдонимы должны соответствовать синтаксису идентификаторов.	SELECT "table t".column_name FROM table_name AS "table t".

​

Примечания по использованию

• Псевдонимы действуют глобально в пределах запроса или подзапроса, и вы можете определить псевдоним для любого выражения в любой части запроса. Например:

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

• Псевдонимы недоступны в подзапросах и между ними. Например, при выполнении следующего запроса ClickHouse возвращает исключение Unknown identifier: num:

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

• Если для результирующих столбцов в предложении SELECT подзапроса задан псевдоним, эти столбцы видны во внешнем запросе. Например:

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

• Будьте осторожны с псевдонимами, совпадающими с именами столбцов или таблиц. Рассмотрим следующий пример:

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

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

Получено исключение от сервера (версия 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.

В предыдущем примере мы объявили таблицу t со столбцом b. Затем при выборке данных мы задали псевдоним sum(b) AS b. Поскольку псевдонимы являются глобальными, ClickHouse подставил в выражение argMax(a, b) выражение sum(b) вместо литерала b. Эта подстановка привела к Исключению.

Это поведение по умолчанию можно изменить, установив prefer_column_name_to_alias в 1.

​

Звёздочка

В запросе SELECT звёздочка может заменять выражение. Подробнее см. в разделе SELECT.