Важно: Данный раздел актуален для Платформы данных в Публичном облаке и On-Premise.
SELECT выполняет получение данных.
[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]Все секции являются необязательными, за исключением списка выражений сразу после SELECT, о котором более подробно будет рассказано ниже.
Особенности каждой необязательной секции рассматриваются в отдельных разделах, которые перечислены в том же порядке, в каком они выполняются:
RT.WideStore поддерживает Общие табличные выражения, то есть позволяет использовать результаты выражений из секции WITH в остальной части SELECT запроса. Именованные подзапросы могут быть включены в текущий и дочерний контекст запроса в тех местах, где разрешены табличные объекты. Рекурсия предотвращается путем скрытия общего табличного выражения текущего уровня из выражения WITH.
Синтаксис
WITH <expression> AS <identifier>или
WITH <identifier> AS <subquery expression>Примеры:
Пример 1: Использование константного выражения как «переменной»
WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
EventDate = toDate(ts_upper_bound) AND
EventTime <= ts_upper_bound;Пример 2: Выкидывание выражения sum(bytes) из списка колонок в SELECT
WITH sum(bytes) as s
SELECT
formatReadableSize(s),
table
FROM system.parts
GROUP BY table
ORDER BY s;Пример 3: Использование результатов скалярного подзапроса
/* запрос покажет TOP 10 самых больших таблиц */
WITH
(
SELECT sum(bytes)
FROM system.parts
WHERE active
) AS total_disk_usage
SELECT
(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;Пример 4: Переиспользование выражения
WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;Выражения указанные в секции SELECT анализируются после завершения всех вычислений из секций, описанных выше. Вернее, анализируются выражения, стоящие над агрегатными функциями, если есть агрегатные функции.
Сами агрегатные функции и то, что под ними, вычисляются при агрегации (GROUP BY). Эти выражения работают так, как будто применяются к отдельным строкам результата.
Если в результат необходимо включить все столбцы, используйте символ звёздочка (*). Например, SELECT * FROM ....
Чтобы включить в результат несколько столбцов, выбрав их имена с помощью регулярных выражений re2, используйте выражение COLUMNS.
COLUMNS('regexp')Например, рассмотрим таблицу:
CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLogСледующий запрос выбирает данные из всех столбцов, содержащих в имени символ a.
SELECT COLUMNS('a') FROM col_names
┌─aa─┬─ab─┐
│ 1 │ 1 │
└────┴─────┘Выбранные столбцы возвращаются не в алфавитном порядке.
В запросе можно использовать несколько выражений COLUMNS, а также вызывать над ними функции.
Например:
SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names
┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐
│ 1 │ 1 │ 1 │ Int8 │
└────┴────┴────┴──────────────────┘Каждый столбец, возвращённый выражением COLUMNS, передаётся в функцию отдельным аргументом. Также можно передавать и другие аргументы, если функция их поддерживаем. Аккуратно используйте функции. Если функция не поддерживает переданное количество аргументов, то RT.WideStore генерирует исключение.
Например:
SELECT COLUMNS('a') + COLUMNS('c') FROM col_names
Received exception from server (version 19.14.1):
Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus doesn't match: passed 3, should be 2.В этом примере, COLUMNS('a') возвращает два столбца: aa и ab. COLUMNS('c') возвращает столбец bc. Оператор + не работает с тремя аргументами, поэтому RT.WideStore генерирует исключение с соответствующим сообщением.
Столбцы, которые возвращаются выражением COLUMNS могут быть разных типов. Если COLUMNS не возвращает ни одного столбца и это единственное выражение в запросе SELECT, то RT.WideStore генерирует исключение.
В любом месте запроса, вместо выражения, может стоять звёздочка. При анализе запроса звёздочка раскрывается в список всех столбцов таблицы (за исключением MATERIALIZED и ALIAS столбцов). Есть лишь немного случаев, когда оправдано использовать звёздочку:
В других случаях использование звёздочки является издевательством над системой, так как вместо преимуществ столбцовой СУБД вы получаете недостатки. То есть использовать звёздочку не рекомендуется.
Вы можете получить в дополнение к результату также минимальные и максимальные значения по столбцам результата. Для этого выставите настройку extremes в 1. Минимумы и максимумы считаются для числовых типов, дат, дат-с-временем. Для остальных столбцов будут выведены значения по умолчанию.
Вычисляются дополнительные две строчки – минимумы и максимумы, соответственно. Эти две дополнительные строки выводятся в форматах JSON*, TabSeparated*, и Pretty* отдельно от остальных строчек. В остальных форматах они не выводится.
Во форматах JSON*, экстремальные значения выводятся отдельным полем ‘extremes’. В форматах TabSeparated*, строка выводится после основного результата и после ‘totals’ если есть. Перед ней (после остальных данных) вставляется пустая строка. В форматах Pretty*, строка выводится отдельной таблицей после основного результата и после totals если есть.
Экстремальные значения вычисляются для строк перед LIMIT, но после LIMIT BY. Однако при использовании LIMIT offset, size, строки перед offset включаются в extremes. В потоковых запросах, в результате может учитываться также небольшое количество строчек, прошедших LIMIT.
Вы можете использовать синонимы (алиасы AS) в любом месте запроса.
В секциях GROUP BY, ORDER BY, в отличие от диалекта MySQL, и в соответствии со стандартным SQL, не поддерживаются позиционные аргументы.
Например, если вы напишите GROUP BY 1, 2 - то это будет воспринято, как группировка по константам (то есть, агрегация всех строк в одну).
Если в запросе отсутствуют секции DISTINCT, GROUP BY, ORDER BY, подзапросы в IN и JOIN, то запрос будет обработан полностью потоково, с использованием O(1) количества оперативки.
Иначе запрос может съесть много оперативки, если не указаны подходящие ограничения:
Подробнее смотрите в разделе «2.10 Настройки» документа «RT.WideStore. Руководство администратора». Присутствует возможность использовать внешнюю сортировку (с сохранением временных данных на диск) и внешнюю агрегацию.
Вы можете использовать следующие модификаторы в запросах SELECT.
Вызывает указанную функцию для каждой строки, возвращаемой внешним табличным выражением запроса.
Синтаксис:
SELECT <expr> APPLY( <func> ) FROM [db.]table_nameПример:
CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i);
INSERT INTO columns_transformers VALUES (100, 10, 324), (120, 8, 23);
SELECT * APPLY(sum) FROM columns_transformers;
┌─sum(i)─┬─sum(j)─┬─sum(k)─┐
│ 220 │ 18 │ 347 │
└─────────┴────────┴─────────┘Исключает из результата запроса один или несколько столбцов.
Синтаксис:
SELECT <expr> EXCEPT ( col_name1 [, col_name2, col_name3, ...] ) FROM [db.]table_nameПример:
SELECT * EXCEPT (i) from columns_transformers;
┌──j─┬───k─┐
│ 10 │ 324 │
│ 8 │ 23 │
└────┴──────┘Определяет одно или несколько выражений алиасов. Каждый алиас должен соответствовать имени столбца из запроса SELECT *. В списке столбцов результата запроса имя столбца, соответствующее алиасу, заменяется выражением в модификаторе REPLACE.
Этот модификатор не изменяет имена или порядок столбцов. Однако он может изменить значение и тип значения.
Синтаксис:
SELECT <expr> REPLACE( <expr> AS col_name) from [db.]table_nameПример:
SELECT * REPLACE(i + 1 AS i) from columns_transformers;
┌───i─┬──j──┬───k─┐
│ 101 │ 10 │ 324 │
│ 121 │ 8 │ 23 │
└─────┴─────┴─────┘Вы можете использовать каждый модификатор отдельно или комбинировать их.
Примеры:
Использование одного и того же модификатора несколько раз.
SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers;
┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐
│ 2 │ 3 │
└────────────────────────────┴─────────────────────────────┘Использование нескольких модификаторов в одном запросе.
SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;
┌─sum(plus(i, 1))─┬─sum(k)─┐
│ 222 │ 347 │
└───────────────────┴────────┘Вы можете задать значения необходимых настроек непосредственно в запросе SELECT в секции SETTINGS. Эти настройки действуют только в рамках данного запроса, а после его выполнения сбрасываются до предыдущего значения или значения по умолчанию.
Пример:
SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;Если указан SELECT DISTINCT, то в результате запроса останутся только уникальные строки. Таким образом, из всех наборов полностью совпадающих строк в результате останется только одна строка.
Вы можете указать столбцы, по которым хотите отбирать уникальные значения: SELECT DISTINCT ON (column1, column2,...). Если столбцы не указаны, то отбираются строки, в которых значения уникальны во всех столбцах.
Рассмотрим таблицу:
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴────┘Использование DISTINCT без указания столбцов:
SELECT DISTINCT * FROM t1;
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴────┘Использование DISTINCT с указанием столбцов:
SELECT DISTINCT ON (a,b) * FROM t1;
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 2 │ 2 │
└───┴────┴───┘RT.WideStore поддерживает использование секций DISTINCT и ORDER BY для разных столбцов в одном запросе. Секция DISTINCT выполняется до секции ORDER BY.
Таблица для примера:
┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘При выборе данных с помощью SELECT DISTINCT a FROM t1 ORDER BY b ASC, мы получаем следующий результат:
┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘Если мы изменим направление сортировки SELECT DISTINCT a FROM t1 ORDER BY b DESC, мы получаем следующий результат:
┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘Ряд 2, 4 был разрезан перед сортировкой.
Учитывайте эту специфику при разработке запросов.
DISTINCT работает с NULL как-будто NULL — обычное значение и NULL==NULL. Другими словами, в результате DISTINCT, различные комбинации с NULL встретятся только один раз. Это отличается от обработки NULL в большинстве других контекстов.
Можно получить такой же результат, применив GROUP BY для того же набора значений, которые указан в секции SELECT, без использования каких-либо агрегатных функций. Но есть несколько отличий от GROUP BY:
В секции FROM указывается источник, из которого будут читаться данные:
Секция JOIN и ARRAY JOIN могут быть использованы для расширения функциональных возможностей секции FROM.
Подзапрос — дополнительный SELECT запрос, который может быть указан в круглых скобках внутри секции FROM.
Секция FROM может содержать несколько источников данных, указанных через запятую, что эквивалентно выполнению CROSS JOIN на них.
Если в запросе используется модификатор FINAL, то RT.WideStore полностью мёржит данные перед выдачей результата, таким образом выполняя все преобразования данных, которые производятся движком таблиц при мёржах.
Он применим при выборе данных из таблиц, использующих MergeTree- семейство движков. Также поддерживается для:
Теперь SELECT запросы с FINAL выполняются параллельно и, следовательно, немного быстрее. Но имеются серьезные недостатки при их использовании (смотрите ниже). Настройка max_final_threads устанавливает максимальное количество потоков.
Запросы, которые используют FINAL выполняются немного медленнее, чем аналогичные запросы без него, потому что:
В большинстве случаев избегайте использования FINAL. Общий подход заключается в использовании агрегирующих запросов, которые предполагают, что фоновые процессы движков семейства MergeTree ещё не случились (например, сами отбрасывают дубликаты). {## TODO: examples ##}
Если секция FROM опущена, данные будут считываться из таблицы system.one. Таблица system.one содержит ровно одну строку.
Для выполнения запроса, из соответствующей таблицы, вынимаются все столбцы, перечисленные в запросе. Из подзапросов выкидываются столбцы, не нужные для внешнего запроса.
Если в запросе не перечислено ни одного столбца (например, SELECT count() FROM t), то из таблицы всё равно вынимается один какой-нибудь столбец (предпочитается самый маленький), для того, чтобы можно было посчитать количество строк.
Секция GROUP BY переключает SELECT запрос в режим агрегации, который работает следующим образом:
Если вы хотите для группировки данных в таблице указывать номера столбцов, а не названия, включите настройку enable_positional_arguments.
Примечание:
Есть ещё один способ запустить агрегацию по таблице. Если запрос содержит столбцы исходной таблицы только внутри агрегатных функций, то `GROUP BY` секцию можно опустить, и предполагается агрегирование по пустому набору ключей. Такие запросы всегда возвращают ровно одну строку.
При агрегации RT.WideStore интерпретирует NULL как обычное значение, то есть NULL==NULL. Это отличается от обработки NULL в большинстве других контекстов.
Предположим, что у вас есть эта таблица:
┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘Запрос
SELECT sum(x), y FROM t_null_big GROUP BY y выведет:
┌─sum(x)─┬────y─┐
│ 4 │ 2 │
│ 3 │ 3 │
│ 5 │ ᴺᵁᴸᴸ │
└────────┴───────┘Видно, что GROUP BY для У = NULL просуммировал x, как будто NULL — это значение.
Если в GROUP BY передать несколько ключей, то в результате мы получим все комбинации выборки, как если бы NULL был конкретным значением.
Модификатор WITH ROLLUP применяется для подсчета подытогов для ключевых выражений. При этом учитывается порядок следования ключевых выражений в списке GROUP BY. Подытоги подсчитываются в обратном порядке: сначала для последнего ключевого выражения в списке, потом для предпоследнего и так далее вплоть до самого первого ключевого выражения.
Строки с подытогами добавляются в конец результирующей таблицы. В колонках, по которым строки уже сгруппированы, указывается значение 0 или пустая строка.
Примечание:
Если в запросе есть секция [HAVING], она может повлиять на результаты расчета подытогов.
Пример:
Рассмотрим таблицу t:
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴────────┴─────┘Запрос:
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;Поскольку секция GROUP BY содержит три ключевых выражения, результат состоит из четырех таблиц с подытогами, которые как бы "сворачиваются" справа налево:
GROUP BY year, month, day;
и общий итог (все три колонки с ключевыми выражениями заполнены нулями).
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴────────┴──────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴────────┴─────┴──────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴────────┴──────┴─────────┘
┌─year─┬─month─┬──day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴────────┴──────┴─────────┘Модификатор WITH CUBE применяется для расчета подытогов по всем комбинациям группировки ключевых выражений в списке GROUP BY.
Строки с подытогами добавляются в конец результирующей таблицы. В колонках, по которым выполняется группировка, указывается значение 0 или пустая строка.
Примечание:
Если в запросе есть секция [HAVING], она может повлиять на результаты расчета подитогов.
Пример:
Рассмотрим таблицу t:
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴────────┴──────┘Запрос:
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;Поскольку секция GROUP BY содержит три ключевых выражения, результат состоит из восьми таблиц с подытогами — по таблице для каждой комбинации ключевых выражений:
Колонки, которые не участвуют в GROUP BY, заполнены нулями.
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴────────┴──────┴─────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴────────┴─────┴──────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 2020 │ 0 │ 5 │ 2 │
│ 2019 │ 0 │ 5 │ 1 │
│ 2020 │ 0 │ 15 │ 2 │
│ 2019 │ 0 │ 15 │ 1 │
└──────┴────────┴──────┴─────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└───────┴───────┴─────┴──────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 5 │ 2 │
│ 0 │ 10 │ 15 │ 1 │
│ 0 │ 10 │ 5 │ 1 │
│ 0 │ 1 │ 15 │ 2 │
└──────┴────────┴──────┴─────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 0 │ 1 │ 0 │ 4 │
│ 0 │ 10 │ 0 │ 2 │
└───────┴───────┴──────┴─────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 0 │ 0 │ 5 │ 3 │
│ 0 │ 0 │ 15 │ 3 │
└──────┴────────┴──────┴─────────┘
┌─year─┬─month─┬─day──┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴────────┴──────┴─────────┘Если указан модификатор WITH TOTALS, то будет посчитана ещё одна строчка, в которой в столбцах-ключах будут содержаться значения по умолчанию (нули, пустые строки), а в столбцах агрегатных функций - значения, посчитанные по всем строкам («тотальные» значения)
Этот дополнительный ряд выводится только в форматах JSON*, TabSeparated*, и Pretty*, отдельно от других строк:
Примечание:
totals выводится только в результатах запросов `SELECT`, и не выводится в `INSERT INTO ... SELECT`.
При использовании секции HAVING поведение WITH TOTALS контролируется настройкой totals_mode.
По умолчанию totals_mode = 'before_having'. В этом случае totals считается по всем строчкам, включая не прошедших через HAVING и max_rows_to_group_by.
Остальные варианты учитывают в totals только строчки, прошедшие через HAVING, и имеют разное поведение при наличии настройки max_rows_to_group_by и group_by_overflow_mode = 'any'.
after_having_exclusive - не учитывать строчки, не прошедшие max_rows_to_group_by. То есть в totals попадёт меньше или столько же строчек, чем если бы max_rows_to_group_by не было.
after_having_inclusive - учитывать в totals все строчки, не прошедшие max_rows_to_group_by. То есть в totals попадёт больше или столько же строчек, чем если бы max_rows_to_group_by не было.
after_having_auto - считать долю строчек, прошедших через HAVING. Если она больше некоторого значения (по умолчанию - 50%), то включить все строчки, не прошедшие max_rows_to_group_by в totals, иначе - не включить.
totals_auto_threshold - по умолчанию 0.5. Коэффициент для работы after_having_auto.
Если max_rows_to_group_by и group_by_overflow_mode = 'any' не используются, то все варианты вида after_having не отличаются, и вы можете использовать любой из них, например, after_having_auto.
Вы можете использовать WITH TOTALS в подзапросах, включая подзапросы в секции JOIN (в этом случае соответствующие тотальные значения будут соединены).
Примеры:
Пример:
SELECT
count(),
median(FetchTiming > 60 ? 60 : FetchTiming),
count() - sum(Refresh)
FROM hitsВ отличие от MySQL (и в соответствии со стандартом SQL), вы не можете получить какое-нибудь значение некоторого столбца, не входящего в ключ или агрегатную функцию (за исключением константных выражений). Для обхода этого вы можете воспользоваться агрегатной функцией any (получить первое попавшееся значение) или min/max.
Пример:
SELECT
domainWithoutWWW(URL) AS domain,
count(),
any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domainGROUP BY вычисляет для каждого встретившегося различного значения ключей, набор значений агрегатных функций.
Агрегация является одной из наиболее важных возможностей столбцовых СУБД, и поэтому её реализация является одной из наиболее сильно оптимизированных частей RT.WideStore. По умолчанию агрегирование выполняется в памяти с помощью хэш-таблицы. Она имеет более 40 специализаций, которые выбираются автоматически в зависимости от типов данных ключа группировки.
Агрегирование данных в отсортированных таблицах может выполняться более эффективно, если выражение GROUP BY содержит хотя бы префикс ключа сортировки или инъективную функцию с этим ключом. В таких случаях в момент считывания из таблицы нового значения ключа сортировки промежуточный результат агрегирования будет финализироваться и отправляться на клиентскую машину. Чтобы включить такой способ выполнения запроса, используйте настройку optimize_aggregation_in_order. Подобная оптимизация позволяет сэкономить память во время агрегации, но в некоторых случаях может привести к увеличению времени выполнения запроса.
Можно включить сброс временных данных на диск, чтобы ограничить потребление оперативной памяти при выполнении GROUP BY. Настройка max_bytes_before_external_group_by определяет пороговое значение потребления RAM, по достижении которого временные данные GROUP BY сбрасываются в файловую систему. Если равно 0 (по умолчанию) - значит выключено.
При использовании max_bytes_before_external_group_by, рекомендуем выставить max_memory_usage приблизительно в два раза больше. Это следует сделать, потому что агрегация выполняется в две стадии: чтение и формирование промежуточных данных (1) и слияние промежуточных данных (2). Сброс данных на файловую систему может производиться только на стадии 1. Если сброса временных данных не было, то на стадии 2 может потребляться до такого же объёма памяти, как на стадии 1.
Например, если max_memory_usage было выставлено в 10000000000, и вы хотите использовать внешнюю агрегацию, то имеет смысл выставить max_bytes_before_external_group_by в 10000000000, а max_memory_usage в 20000000000. При срабатывании внешней агрегации (если был хотя бы один сброс временных данных в файловую систему) максимальное потребление оперативки будет лишь чуть-чуть больше max_bytes_before_external_group_by.
При распределённой обработке запроса внешняя агрегация производится на удалённых серверах. Для того чтобы на сервере-инициаторе запроса использовалось немного оперативки, нужно выставить настройку distributed_aggregation_memory_efficient в 1.
Позволяет фильтровать результаты агрегации, полученные с помощью GROUP BY. Разница с WHERE в том, что WHERE выполняется перед агрегацией, в то время как HAVING выполняется после него.
Из секции HAVING можно ссылаться на результаты агреации из секции SELECT по их алиасу. Также секция HAVING может фильтровать по результатам дополнительных агрегатов, которые не возвращаются в результатах запроса.
HAVING нельзя использовать, если агрегация не выполняется. Вместо этого можно использовать WHERE.
INTERSECT возвращает строки, которые есть только в результатах первого и второго запросов. В запросах должны совпадать количество столбцов, их порядок и тип. Результат INTERSECT может содержать повторяющиеся строки.
Если используется несколько INTERSECT и скобки не указаны, пересечение выполняется слева направо. У INTERSECT более высокий приоритет выполнения, чем у UNION и EXCEPT.
SELECT column1 [, column2 ]
FROM table1
[WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table2
[WHERE condition]Условие может быть любым в зависимости от ваших требований.
Примеры:
Запрос:
SELECT number FROM numbers(1,10) INTERSECT SELECT number FROM numbers(3,6);Результат:
┌─number─┐
│ 3 │
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
└─────────┘Запрос:
CREATE TABLE t1(one String, two String, three String) ENGINE=Memory();
CREATE TABLE t2(four String, five String, six String) ENGINE=Memory();
INSERT INTO t1 VALUES ('q', 'm', 'b'), ('s', 'd', 'f'), ('l', 'p', 'o'), ('s', 'd', 'f'), ('s', 'd', 'f'), ('k', 't', 'd'), ('l', 'p', 'o');
INSERT INTO t2 VALUES ('q', 'm', 'b'), ('b', 'd', 'k'), ('s', 'y', 't'), ('s', 'd', 'f'), ('m', 'f', 'o'), ('k', 'k', 'd');
SELECT * FROM t1 INTERSECT SELECT * FROM t2;Результат:
┌─one─┬─two─┬─three─┐
│ q │ m │ b │
│ s │ d │ f │
│ s │ d │ f │
│ s │ d │ f │
└─────┴──────┴───────┘См. также:
Секция INTO OUTFILE перенаправляет результат запроса SELECT в файл на стороне клиента.
Поддерживаются сжатые файлы. Формат сжатия определяется по расширению файла (по умолчанию используется режим 'auto'), либо он может быть задан явно в секции COMPRESSION. Уровень сжатия для конкретного алгоритма может быть задан в секции LEVEL.
Синтаксис:
SELECT <expr_list> INTO OUTFILE file_name [COMPRESSION type [LEVEL level]]file_name и type задаются в виде строковых литералов. Поддерживаются форматы сжатия: 'none', 'gzip', 'deflate', 'br', 'xz', 'zstd', 'lz4', 'bz2'.
level задается в виде числового литерала. Поддерживаются положительные значения в следующих диапазонах: 1-12 для формата lz4, 1-22 для формата zstd и 1-9 для остальных форматов.
Пример:
Выполните следующий запрос, используя клиент командной строки:
widestore-client --query="SELECT 1,'ABC' INTO OUTFILE 'select.gz' FORMAT CSV;"
zcat select.gz Результат:
1,"ABC"JOIN создаёт новую таблицу путем объединения столбцов из одной или нескольких таблиц с использованием общих для каждой из них значений. Это обычная операция в базах данных с поддержкой SQL, которая соответствует join из реляционной алгебры. Частный случай соединения одной таблицы часто называют self-join.
Синтаксис:
SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...Выражения из секции ON и столбцы из секции USING называется «ключами соединения». Если не указано иное, при присоединение создаётся Декартово произведение из строк с совпадающими значениями ключей соединения, что может привести к получению результатов с гораздо большим количеством строк, чем исходные таблицы.
Все типы из стандартного SQL JOIN поддерживаются:
Без указания типа JOIN подразумевается INNER. Ключевое слово OUTER можно опускать. Альтернативным синтаксисом для CROSS JOIN является ли указание нескольких таблиц, разделённых запятыми, в секции FROM.
Дополнительные типы соединений, доступные в RT.WideStore:
Примечание:
Если настройка join_algorithm установлена в значение partial_merge, то для RIGHT JOIN и FULL JOIN поддерживается только уровень строгости ALL (SEMI, ANTI, ANY и ASOF не поддерживаются).
Значение строгости по умолчанию может быть переопределено с помощью настройки join_default_strictness.
Поведение сервера RT.WideStore для операций ANY JOIN зависит от параметра any_join_distinct_right_table_keys.
См. также:
Секция ON может содержать несколько условий, связанных операторами AND и OR. Условия, задающие ключи соединения, должны содержать столбцы левой и правой таблицы и должны использовать оператор равенства. Прочие условия могут использовать другие логические операторы, но в отдельном условии могут использоваться столбцы либо только левой, либо только правой таблицы.
Строки объединяются только тогда, когда всё составное условие выполнено. Если оно не выполнено, то строки могут попасть в результат в зависимости от типа JOIN. Обратите внимание, что если то же самое условие поместить в секцию WHERE, то строки, для которых оно не выполняется, никогда не попаду в результат.
Оператор OR внутри секции ON работает, используя алгоритм хеш-соединения — на каждый агрумент OR с ключами соединений для JOIN создается отдельная хеш-таблица, поэтому потребление памяти и время выполнения запроса растет линейно при увеличении количества выражений OR секции ON.
Примечание:
Если в условии использованы столбцы из разных таблиц, то пока поддерживается только оператор равенства (`=`).
Пример:
Рассмотрим table_1 и table_2:
┌─Id─┬─name─┐ ┌─Id─┬─text────────────┬─scores─┐
│ 1 │ A │ │ 1 │ Text A │ 10 │
│ 2 │ B │ │ 1 │ Another text A │ 12 │
│ 3 │ C │ │ 2 │ Text B │ 15 │
└────┴───────┘ └────┴──────────────────┴────────┘Запрос с одним условием, задающим ключ соединения, и дополнительным условием для table_2:
SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');Обратите внимание, что результат содержит строку с именем C и пустым текстом. Строка включена в результат, потому что использован тип соединения OUTER.
┌─name─┬─text───┐
│ A │ Text A │
│ B │ Text B │
│ C │ │
└───────┴────────┘Запрос с типом соединения INNER и несколькими условиями:
SELECT name, text, scores FROM table_1 INNER JOIN table_2
ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');Результат:
┌─name─┬─text───┬─scores─┐
│ B │ Text B │ 15 │
└───────┴────────┴─────────┘Запрос с типом соединения INNER и условием с оператором OR:
CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;
CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;
INSERT INTO t1 SELECT number as a, -a as b from numbers(5);
INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);
SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;Результат:
┌─a─┬──b─┬─val─┐
│ 0 │ 0 │ 0 │
│ 1 │ -1 │ 1 │
│ 2 │ -2 │ 2 │
│ 3 │ -3 │ 3 │
│ 4 │ -4 │ 4 │
└───┴────┴──────┘Запрос с типом соединения INNER и условиями с операторами OR и AND:
SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;Результат:
┌─a─┬──b─┬─val─┐
│ 0 │ 0 │ 0 │
│ 2 │ -2 │ 2 │
│ 4 │ -4 │ 4 │
└───┴─────┴─────┘ASOF JOIN применим в том случае, когда необходимо объединять записи, которые не имеют точного совпадения.
Для работы алгоритма необходим специальный столбец в таблицах. Этот столбец:
Синтаксис ASOF JOIN ... ON:
SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_condМожно использовать произвольное количество условий равенства и одно условие на ближайшее совпадение. Например, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.
Условия, поддержанные для проверки на ближайшее совпадение: >, >=, <, <=.
Синтаксис ASOF JOIN ... USING:
SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)Для слияния по равенству ASOF JOIN использует equi_columnX, а для слияния по ближайшему совпадению использует asof_column с условием table_1.asof_column >= table_2.asof_column. Столбец asof_column должен быть последним в секции USING.
Например, рассмотрим следующие таблицы:
table_1 table_2
event | ev_time | user_id event | ev_time | user_id
----------|---------|---------- ----------|---------|----------
... ...
event_1_1 | 12:00 | 42 event_2_1 | 11:59 | 42
... event_2_2 | 12:30 | 42
event_1_2 | 13:00 | 42 event_2_3 | 13:00 | 42
... ...ASOF JOIN принимает метку времени пользовательского события из table_1 и находит такое событие в table_2 метка времени которого наиболее близка к метке времени события из table_1 в соответствии с условием на ближайшее совпадение. При этом столбец user_id используется для объединения по равенству, а столбец ev_time для объединения по ближайшему совпадению. В нашем примере event_1_1 может быть объединено с event_2_1, event_1_2 может быть объединено с event_2_3, а event_2_2 не объединяется.
Примечание:
`ASOF JOIN` не поддержан для движка таблиц [Join].
Чтобы задать значение строгости по умолчанию, используйте сессионный параметр join_default_strictness.
Есть два пути для выполнения соединения с участием распределённых таблиц:
Будьте аккуратны при использовании GLOBAL. За дополнительной информацией обращайтесь в раздел Распределенные подзапросы.
Запросы INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL JOIN поддерживают неявные преобразования типов для ключей соединения. Однако запрос не может быть выполнен, если не существует типа, к которому можно привести значения ключей с обеих сторон (например, нет типа, который бы одновременно вмещал в себя значения UInt64 и Int64, или String и Int32).
Пример:
Рассмотрим таблицу t_1:
┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16 │ UInt8 │
│ 2 │ 2 │ UInt16 │ UInt8 │
└───┴────┴────────────────┴────────────────┘и таблицу t_2:
┌──a─┬─────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │ 1 │ Int16 │ Nullable(Int64) │
│ 1 │ -1 │ Int16 │ Nullable(Int64) │
│ 1 │ 1 │ Int16 │ Nullable(Int64) │
└─────┴──────┴────────────────┴───────────────────┘Запрос:
SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);вернёт результат:
┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)────┐
│ 1 │ 1 │ Int32 │ Nullable(Int64) │
│ 2 │ 2 │ Int32 │ Nullable(Int64) │
│ -1 │ 1 │ Int32 │ Nullable(Int64) │
│ 1 │ -1 │ Int32 │ Nullable(Int64) │
└────┴───────┴────────────────┴───────────────────┘При соединении таблиц могут появляться пустые ячейки. Настройка join_use_nulls определяет, как RT.WideStore заполняет эти ячейки.
Если ключами JOIN выступают поля типа Nullable, то строки, где хотя бы один из ключей имеет значение NULL, не соединяются.
Требуется, чтобы столбцы, указанные в USING, назывались одинаково в обоих подзапросах, а остальные столбцы - по-разному. Изменить имена столбцов в подзапросах можно с помощью синонимов.
В секции USING указывается один или несколько столбцов для соединения, что обозначает условие на равенство этих столбцов. Список столбцов задаётся без скобок. Более сложные условия соединения не поддерживаются.
Для множественных секций JOIN в одном запросе SELECT:
Для секций ON, WHERE и GROUP BY:
При запуске JOIN, отсутствует оптимизация порядка выполнения по отношению к другим стадиям запроса. Соединение (поиск в «правой» таблице) выполняется до фильтрации в WHERE и до агрегации. Чтобы явно задать порядок вычислений, рекомендуется выполнять JOIN подзапроса с подзапросом.
Каждый раз для выполнения запроса с одинаковым JOIN, подзапрос выполняется заново — результат не кэшируется. Это можно избежать, используя специальный движок таблиц Join, представляющий собой подготовленное множество для соединения, которое всегда находится в оперативке.
В некоторых случаях это более эффективно использовать IN вместо JOIN.
Если JOIN необходим для соединения с таблицами измерений (dimension tables - сравнительно небольшие таблицы, которые содержат свойства измерений - например, имена для рекламных кампаний), то использование JOIN может быть не очень удобным из-за громоздкости синтаксиса, а также из-за того, что правая таблица читается заново при каждом запросе. Специально для таких случаев существует функциональность «Внешние словари», которую следует использовать вместо JOIN. Дополнительные сведения смотрите в разделе «Внешние словари».
По умолчанию RT.WideStore использует алгоритм hash join. RT.WideStore берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти. При включённой настройке join_algorithm = 'auto', после некоторого порога потребления памяти RT.WideStore переходит к алгоритму merge join. Описание алгоритмов JOIN см. в настройке join_algorithm.
Если вы хотите ограничить потребление памяти во время выполнения операции JOIN, используйте настройки:
По достижении любого из этих ограничений RT.WideStore действует в соответствии с настройкой join_overflow_mode.
Примеры:
Пример:
SELECT
CounterID,
hits,
visits
FROM
(
SELECT
CounterID,
count() AS hits
FROM test.hits
GROUP BY CounterID
) ANY LEFT JOIN
(
SELECT
CounterID,
sum(Sign) AS visits
FROM test.visits
GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10
┌─CounterID─┬───hits─┬─visits─┐
│ 1143050 │ 523264 │ 13665 │
│ 731962 │ 475698 │ 102716 │
│ 722545 │ 337212 │ 108187 │
│ 722889 │ 252197 │ 10547 │
│ 2237260 │ 196036 │ 9522 │
│ 23057320 │ 147211 │ 7689 │
│ 722818 │ 90109 │ 17847 │
│ 48221 │ 85379 │ 4652 │
│ 19762435 │ 77807 │ 7026 │
│ 722884 │ 77492 │ 11056 │
└────────────┴─────────┴────────┘Запрос с секцией LIMIT n BY expressions выбирает первые n строк для каждого отличного значения expressions. Ключ LIMIT BY может содержать любое количество выражений.
RT.WideStore поддерживает следующий синтаксис:
Во время обработки запроса, RT.WideStore выбирает данные, упорядоченные по ключу сортировки. Ключ сортировки задаётся явно в секции ORDER BY или неявно в свойствах движка таблицы (порядок строк гарантирован только при использовании ORDER BY, в ином случае блоки строк не будут упорядочены из-за многопоточной обработки). Затем RT.WideStore применяет LIMIT n BY expressions и возвращает первые n для каждой отличной комбинации expressions. Если указан OFFSET, то для каждого блока данных, который принадлежит отдельной комбинации expressions, RT.WideStore отступает offset_value строк от начала блока и возвращает не более n. Если offset_value больше, чем количество строк в блоке данных, RT.WideStore не возвращает ни одной строки.
LIMIT BY не связана с секцией LIMIT. Их можно использовать в одном запросе.
Если вы хотите использовать в секции LIMIT BY номера столбцов вместо названий, включите настройку enable_positional_arguments.
Примеры:
Образец таблицы:
CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by values(1, 10), (1, 11), (1, 12), (2, 20), (2, 21);Запросы:
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id
┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴──────┘
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴──────┘Запрос SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id возвращает такой же результат.
Следующий запрос выбирает топ 5 рефереров для каждой пары domain, device_type, но не более 100 строк (LIMIT n BY + LIMIT).
SELECT
domainWithoutWWW(URL) AS domain,
domainWithoutWWW(REFERRER_URL) AS referrer,
device_type,
count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100Запрос выберет топ 5 рефереров для каждой пары domain, device_type, но не более 100 строк (LIMIT n BY + LIMIT).
LIMIT n BY работает с NULL как если бы это было конкретное значение. Т.е. в результате запроса пользователь получит все комбинации полей, указанных в BY.
LIMIT m позволяет выбрать из результата первые m строк.
LIMIT n, m позволяет выбрать из результата первые m строк после пропуска первых n строк. Синтаксис LIMIT m OFFSET n также поддерживается.
n и m должны быть неотрицательными целыми числами.
При отсутствии секции ORDER BY, однозначно сортирующей результат, результат может быть произвольным и может являться недетерминированным.
Примечание:
Количество возвращаемых строк может зависеть также от настройки limit.
Когда вы установите модификатор WITH TIES для LIMIT n[,m] и указываете ORDER BY expr_list, вы получите первые n или n,m строк и дополнительно все строки с теми же самым значениями полей указанных в ORDER BY равными строке на позиции n для LIMIT n или m для LIMIT n,m.
Этот модификатор также может быть скомбинирован с ORDER BY ... WITH FILL модификатором
Для примера следующий запрос:
SELECT * FROM (
SELECT number%50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0,5возвращает
┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
└───┘но после применения модификатора WITH TIES
SELECT * FROM (
SELECT number%50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0,5 WITH TIESвозвращает другой набор строк
┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘поскольку строка на позиции 6 имеет тоже самое значение "2" для поля n что и строка на позиции 5.
OFFSET и FETCH позволяют извлекать данные по частям. Они указывают строки, которые вы хотите получить в результате запроса.
OFFSET offset_row_count {ROW | ROWS}] [FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} {ONLY | WITH TIES}]offset_row_count или fetch_row_count может быть числом или литеральной константой. Если вы не задаете fetch_row_count явно, используется значение по умолчанию, равное 1.
OFFSET указывает количество строк, которые необходимо пропустить перед началом возврата строк из запроса.
FETCH указывает максимальное количество строк, которые могут быть получены в результате запроса.
Опция ONLY используется для возврата строк, которые следуют сразу же за строками, пропущенными секцией OFFSET. В этом случае FETCH — это альтернатива LIMIT. Например, следующий запрос
SELECT * FROM test_fetch ORDER BY a OFFSET 1 ROW FETCH FIRST 3 ROWS ONLY;идентичен запросу
SELECT * FROM test_fetch ORDER BY a LIMIT 3 OFFSET 1;Опция WITH TIES используется для возврата дополнительных строк, которые привязываются к последней в результате запроса. Например, если fetch_row_count имеет значение 5 и существуют еще 2 строки с такими же значениями столбцов, указанных в ORDER BY, что и у пятой строки результата, то финальный набор будет содержать 7 строк.
Примечание:
Секция `OFFSET` должна находиться перед секцией `FETCH`, если обе присутствуют.
Примечание:
Общее количество пропущенных строк может зависеть также от настройки offset.
Примеры:
Входная таблица:
┌─a─┬─b─┐
│ 1 │ 1 │
│ 2 │ 1 │
│ 3 │ 4 │
│ 1 │ 3 │
│ 5 │ 4 │
│ 0 │ 6 │
│ 5 │ 7 │
└───┴───┘Использование опции ONLY:
SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS ONLY;Результат:
┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
└───┴───┘Использование опции WITH TIES:
SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS WITH TIES;Результат:
┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
│ 5 │ 7 │
└───┴───┘Секция ORDER BY содержит список выражений, к каждому из которых также может быть приписано DESC или ASC (направление сортировки). Если ничего не приписано - это аналогично приписыванию ASC. ASC - сортировка по возрастанию, DESC - сортировка по убыванию. Обозначение направления сортировки действует на одно выражение, а не на весь список. Пример: ORDER BY Visits DESC, SearchPhrase.
Если вы хотите для сортировки данных указывать номера столбцов, а не названия, включите настройку enable_positional_arguments.
Строки, для которых список выражений, по которым производится сортировка, принимает одинаковые значения, выводятся в произвольном порядке, который может быть также недетерминированным (каждый раз разным). Если секция ORDER BY отсутствует, то, аналогично, порядок, в котором идут строки, не определён, и может быть недетерминированным.
Существует два подхода к участию NaN и NULL в порядке сортировки:
Пример:
Для таблицы
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │ 2 │
│ 1 │ nan │
│ 2 │ 2 │
│ 3 │ 4 │
│ 5 │ 6 │
│ 6 │ nan │
│ 7 │ ᴺᵁᴸᴸ │
│ 6 │ 7 │
│ 8 │ 9 │
└───┴───────┘Выполните запрос SELECT * FROM t_null_nan ORDER BY y NULLS FIRST чтобы получить:
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 7 │ ᴺᵁᴸᴸ │
│ 1 │ nan │
│ 6 │ nan │
│ 2 │ 2 │
│ 2 │ 2 │
│ 3 │ 4 │
│ 5 │ 6 │
│ 6 │ 7 │
│ 8 │ 9 │
└───┴───────┘При сортировке чисел с плавающей запятой NaNs отделяются от других значений. Независимо от порядка сортировки, NaNs приходят в конце. Другими словами, при восходящей сортировке они помещаются так, как будто они больше всех остальных чисел, а при нисходящей сортировке они помещаются так, как будто они меньше остальных.
Для сортировки по значениям типа String есть возможность указать collation (сравнение). Пример: ORDER BY SearchPhrase COLLATE 'tr' - для сортировки по поисковой фразе, по возрастанию, с учётом турецкого алфавита, регистронезависимо, при допущении, что строки в кодировке UTF-8. COLLATE может быть указан или не указан для каждого выражения в ORDER BY независимо. Если есть ASC или DESC, то COLLATE указывается после них. При использовании COLLATE сортировка всегда регистронезависима.
Сравнение поддерживается при использовании типов LowCardinality, Nullable, Array и Tuple.
Рекомендуется использовать COLLATE только для окончательной сортировки небольшого количества строк, так как производительность сортировки с указанием COLLATE меньше, чем обычной сортировки по байтам.
Пример с значениями типа String:
Входная таблица:
┌─x─┬─s────┐
│ 1 │ bca │
│ 2 │ ABC │
│ 3 │ 123a │
│ 4 │ abc │
│ 5 │ BCA │
└───┴──────┘Запрос:
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';Результат:
┌─x─┬─s────┐
│ 3 │ 123a │
│ 4 │ abc │
│ 2 │ ABC │
│ 1 │ bca │
│ 5 │ BCA │
└───┴──────┘Пример со строками типа Nullable:
Входная таблица:
┌─x─┬─s────┐
│ 1 │ bca │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ ABC │
│ 4 │ 123a │
│ 5 │ abc │
│ 6 │ ᴺᵁᴸᴸ │
│ 7 │ BCA │
└───┴───────┘Запрос:
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';Результат:
┌─x─┬─s────┐
│ 4 │ 123a │
│ 5 │ abc │
│ 3 │ ABC │
│ 1 │ bca │
│ 7 │ BCA │
│ 6 │ ᴺᵁᴸᴸ │
│ 2 │ ᴺᵁᴸᴸ │
└───┴──────┘Пример со строками в Array:
Входная таблица:
┌─x─┬─s──────────────┐
│ 1 │ ['Z'] │
│ 2 │ ['z'] │
│ 3 │ ['a'] │
│ 4 │ ['A'] │
│ 5 │ ['z','a'] │
│ 6 │ ['z','a','a'] │
│ 7 │ [''] │
└───┴────────────────┘Запрос:
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';Результат:
┌─x─┬─s──────────────┐
│ 7 │ [''] │
│ 3 │ ['a'] │
│ 4 │ ['A'] │
│ 2 │ ['z'] │
│ 5 │ ['z','a'] │
│ 6 │ ['z','a','a'] │
│ 1 │ ['Z'] │
└───┴────────────────┘Пример со строками типа LowCardinality:
Входная таблица:
┌─x─┬─s───┐
│ 1 │ Z │
│ 2 │ z │
│ 3 │ a │
│ 4 │ A │
│ 5 │ za │
│ 6 │ zaa │
│ 7 │ │
└───┴──────┘Запрос:
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';Результат:
┌─x─┬─s───┐
│ 7 │ │
│ 3 │ a │
│ 4 │ A │
│ 2 │ z │
│ 1 │ Z │
│ 5 │ za │
│ 6 │ zaa │
└───┴──────┘Пример со строками в Tuple:
┌─x─┬─s───────┐
│ 1 │ (1,'Z') │
│ 2 │ (1,'z') │
│ 3 │ (1,'a') │
│ 4 │ (2,'z') │
│ 5 │ (1,'A') │
│ 6 │ (2,'Z') │
│ 7 │ (2,'A') │
└───┴──────────┘Запрос:
SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';Результат:
┌─x─┬─s───────┐
│ 3 │ (1,'a') │
│ 5 │ (1,'A') │
│ 2 │ (1,'z') │
│ 1 │ (1,'Z') │
│ 7 │ (2,'A') │
│ 4 │ (2,'z') │
│ 6 │ (2,'Z') │
└───┴──────────┘Если кроме ORDER BY указан также не слишком большой LIMIT, то расходуется меньше оперативки. Иначе расходуется количество памяти, пропорциональное количеству данных для сортировки. При распределённой обработке запроса, если отсутствует GROUP BY, сортировка частично делается на удалённых серверах, а на сервере-инициаторе запроса производится слияние результатов. Таким образом, при распределённой сортировке, может сортироваться объём данных, превышающий размер памяти на одном сервере.
Существует возможность выполнять сортировку во внешней памяти (с созданием временных файлов на диске), если оперативной памяти не хватает. Для этого предназначена настройка max_bytes_before_external_sort. Если она выставлена в 0 (по умолчанию), то внешняя сортировка выключена. Если она включена, то при достижении объёмом данных для сортировки указанного количества байт, накопленные данные будут отсортированы и сброшены во временный файл. После того, как все данные будут прочитаны, будет произведено слияние всех сортированных файлов и выдача результата. Файлы записываются в директорию /var/lib/widestore/tmp/ (по умолчанию, может быть изменено с помощью параметра tmp_path) в конфиге.
На выполнение запроса может расходоваться больше памяти, чем max_bytes_before_external_sort. Поэтому, значение этой настройки должно быть существенно меньше, чем max_memory_usage. Для примера, если на вашем сервере 128 GB оперативки, и вам нужно выполнить один запрос, то выставите max_memory_usage в 100 GB, а max_bytes_before_external_sort в 80 GB.
Внешняя сортировка работает существенно менее эффективно, чем сортировка в оперативке.
Если в списке выражений в секции ORDER BY первыми указаны те поля, по которым проиндексирована таблица, по которой строится выборка, такой запрос можно оптимизировать — для этого используйте настройку optimize_read_in_order.
Когда настройка optimize_read_in_order включена, при выполнении запроса сервер использует табличные индексы и считывает данные в том порядке, который задан списком выражений ORDER BY. Поэтому если в запросе установлен LIMIT, сервер не станет считывать лишние данные. Таким образом, запросы к большим таблицам, но имеющие ограничения по числу записей, выполняются быстрее.
Оптимизация работает при любом порядке сортировки ASC или DESC, но не работает при использовании группировки GROUP BY и модификатора 1.5.1 Модификатор FINAL.
Когда настройка optimize_read_in_order отключена, при выполнении запросов SELECT табличные индексы не используются.
Для запросов с сортировкой ORDER BY, большим значением LIMIT и условиями отбора WHERE, требующими чтения больших объемов данных, рекомендуется отключать optimize_read_in_order вручную.
Оптимизация чтения данных поддерживается в следующих движках:
В движке MaterializedView оптимизация поддерживается при работе с сохраненными запросами (представлениями) вида SELECT ... FROM merge_tree_table ORDER BY pk. Но оптимизация не поддерживается для запросов вида SELECT ... FROM view ORDER BY pk, если в сохраненном запросе нет секции ORDER BY.
Этот модификатор также может быть скомбинирован с модификатором LIMIT ... WITH TIES
Модификатор WITH FILL может быть установлен после ORDER BY expr с опциональными параметрами FROM expr, TO expr и STEP expr. Все пропущенные значения для колонки expr будут заполнены значениями, соответствующими предполагаемой последовательности значений колонки, другие колонки будут заполнены значениями по умолчанию.
Используйте следующую конструкцию для заполнения нескольких колонок с модификатором WITH FILL с необязательными параметрами после каждого имени поля в секции ORDER BY.
ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr]
[INTERPOLATE [(col [AS expr], ... colN [AS exprN])]]WITH FILL может быть применен к полям с числовыми (все разновидности float, int, decimal) или временными (все разновидности Date, DateTime) типами. В случае применения к полям типа String недостающие значения заполняются пустой строкой. Когда не определен FROM const_expr, последовательность заполнения использует минимальное значение поля expr из ORDER BY. Когда не определен TO const_expr, последовательность заполнения использует максимальное значение поля expr из ORDER BY. Когда STEP const_numeric_expr определен, const_numeric_expr интерпретируется "как есть" для числовых типов, как "дни" для типа Date и как "секунды" для типа DateTime.
Когда STEP const_numeric_expr не указан, тогда используется 1.0 для числовых типов, 1 день для типа Date и 1 секунда для типа DateTime.
INTERPOLATE может быть применен к колонкам, не участвующим в ORDER BY WITH FILL. Такие колонки заполняются значениями, вычисляемыми применением expr к предыдущему значению. Если expr опущен, то колонка заполняется предыдущим значением. Если список колонок не указан, то включаются все разрешенные колонки.
Пример запроса без использования WITH FILL:
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n;Результат:
┌─n─┬─source───┐
│ 1 │ original │
│ 4 │ original │
│ 7 │ original │
└───┴───────────┘Тот же запрос после применения модификатора WITH FILL:
SELECT n, source FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5Результат:
┌───n─┬─source───┐
│ 0 │ │
│ 0.5 │ │
│ 1 │ original │
│ 1.5 │ │
│ 2 │ │
│ 2.5 │ │
│ 3 │ │
│ 3.5 │ │
│ 4 │ original │
│ 4.5 │ │
│ 5 │ │
│ 5.5 │ │
│ 7 │ original │
└─────┴───────────┘Для случая с несколькими полями ORDER BY field2 WITH FILL, field1 WITH FILL порядок заполнения будет соответствовать порядку полей в секции ORDER BY.
Пример:
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d2 WITH FILL,
d1 WITH FILL STEP 5;Результат:
┌───d1────────┬───d2────────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-01 │ 1970-01-03 │ │
│ 1970-01-01 │ 1970-01-04 │ │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-01-01 │ 1970-01-06 │ │
│ 1970-01-01 │ 1970-01-07 │ │
│ 1970-03-12 │ 1970-01-08 │ original │
└─────────────┴─────────────┴───────────┘Поле d1 не заполняется и использует значение по умолчанию. Поскольку у нас нет повторяющихся значений для d2, мы не можем правильно рассчитать последовательность заполнения для d1.
Следующий запрос (с измененным порядком в ORDER BY):
SELECT
toDate((number * 10) * 86400) AS d1,
toDate(number * 86400) AS d2,
'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
d1 WITH FILL STEP 5,
d2 WITH FILL;Результат:
┌───d1────────┬───d2────────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-16 │ 1970-01-01 │ │
│ 1970-01-21 │ 1970-01-01 │ │
│ 1970-01-26 │ 1970-01-01 │ │
│ 1970-01-31 │ 1970-01-01 │ │
│ 1970-02-05 │ 1970-01-01 │ │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-02-15 │ 1970-01-01 │ │
│ 1970-02-20 │ 1970-01-01 │ │
│ 1970-02-25 │ 1970-01-01 │ │
│ 1970-03-02 │ 1970-01-01 │ │
│ 1970-03-07 │ 1970-01-01 │ │
│ 1970-03-12 │ 1970-01-08 │ original │
└─────────────┴─────────────┴───────────┘Пример запроса без INTERPOLATE:
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number as inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;Результат:
┌───n─┬─source────┬─inter─┐
│ 0 │ │ 0 │
│ 0.5 │ │ 0 │
│ 1 │ original │ 1 │
│ 1.5 │ │ 0 │
│ 2 │ │ 0 │
│ 2.5 │ │ 0 │
│ 3 │ │ 0 │
│ 3.5 │ │ 0 │
│ 4 │ original │ 4 │
│ 4.5 │ │ 0 │
│ 5 │ │ 0 │
│ 5.5 │ │ 0 │
│ 7 │ original │ 7 │
└─────┴───────────┴────────┘Тот же запрос с INTERPOLATE:
SELECT n, source, inter FROM (
SELECT toFloat32(number % 10) AS n, 'original' AS source, number as inter
FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1);Результат:
┌───n─┬─source────┬─inter─┐
│ 0 │ │ 0 │
│ 0.5 │ │ 0 │
│ 1 │ original │ 1 │
│ 1.5 │ │ 2 │
│ 2 │ │ 3 │
│ 2.5 │ │ 4 │
│ 3 │ │ 5 │
│ 3.5 │ │ 6 │
│ 4 │ original │ 4 │
│ 4.5 │ │ 5 │
│ 5 │ │ 6 │
│ 5.5 │ │ 7 │
│ 7 │ original │ 7 │
└─────┴───────────┴────────┘Prewhere — это оптимизация для более эффективного применения фильтрации. Она включена по умолчанию, даже если секция PREWHERE явно не указана. В этом случае работает автоматическое перемещение части выражения из WHERE до стадии prewhere. Роль секции PREWHERE только для управления этой оптимизацией, если вы думаете, что знаете, как сделать перемещение условия лучше, чем это происходит по умолчанию.
При оптимизации prewhere сначала читаются только те столбцы, которые необходимы для выполнения выражения prewhere. Затем читаются другие столбцы, необходимые для выполнения остальной части запроса, но только те блоки, в которых находится выражение prewhere «верно» по крайней мере для некоторых рядов. Если есть много блоков, где выражение prewhere «ложно» для всех строк и для выражения prewhere требуется меньше столбцов, чем для других частей запроса, это часто позволяет считывать гораздо меньше данных с диска для выполнения запроса.
PREWHERE имеет смысл использовать, если есть условия фильтрации, которые использует меньшинство столбцов из тех, что есть в запросе, но достаточно сильно фильтрует данные. Таким образом, сокращается количество читаемых данных.
В запросе может быть одновременно указаны и PREWHERE, и WHERE. В этом случае PREWHERE предшествует WHERE.
Если значение параметра optimize_move_to_prewhere равно 0, эвристика по автоматическому перемещению части выражений из WHERE к PREWHERE отключается.
Если в запросе есть модификатор FINAL, оптимизация PREWHERE не всегда корректна. Она действует только если включены обе настройки optimize_move_to_prewhere и optimize_move_to_prewhere_if_final.
Внимание:
Секция `PREWHERE` выполняется до `FINAL`, поэтому результаты запросов `FROM ... FINAL` могут исказиться при использовании `PREWHERE` с полями, не входящими в `ORDER BY` таблицы.
PREWHERE поддерживается только табличными движками из семейства *MergeTree.
Секция SAMPLE позволяет выполнять запросы приближённо. Например, чтобы посчитать статистику по всем визитам, можно обработать 1/10 всех визитов и результат домножить на 10.
Сэмплирование имеет смысл, когда:
Внимание: Не стоит использовать сэмплирование в тех задачах, где важна точность расчетов. Например, при работе с финансовыми отчетами.
Свойства сэмплирования:
Сэмплирование поддерживается только таблицами семейства MergeTree и только в том случае, если для таблиц был указан ключ сэмплирования (выражение, на основе которого должна производиться выборка). Подробнее см. в разделе Создание таблицы MergeTree.
Выражение SAMPLE в запросе можно задать следующими способами:
|
Способ задания SAMPLE |
Описание |
|---|---|
| SAMPLE k |
Здесь k – это дробное число в интервале от 0 до 1. Запрос будет выполнен по k доле данных. Например, если указано SAMPLE 1/10, то запрос будет выполнен для выборки из 1/10 данных. |
| SAMPLE n |
Здесь n – это достаточно большое целое число. Запрос будет выполнен для выборки, состоящей из не менее чем n строк. Например, если указано SAMPLE 10000000, то запрос будет выполнен для не менее чем 10,000,000 строк. |
| SAMPLE k OFFSET m |
Здесь k и m – числа от 0 до 1. Запрос будет выполнен по k доле данных. При этом выборка будет сформирована со смещением на m долю. |
Здесь k – число в интервале от 0 до 1. Поддерживается как дробная, так и десятичная форма записи. Например, SAMPLE 1/2 или SAMPLE 0.5.
Если задано выражение SAMPLE k, запрос будет выполнен для k доли данных. Рассмотрим пример:
SELECT
Title,
count() * 10 AS PageViews
FROM hits_distributed
SAMPLE 0.1
WHERE
CounterID = 34
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000В этом примере запрос выполняется по выборке из 0.1 (10%) данных. Значения агрегатных функций не корректируются автоматически, поэтому чтобы получить приближённый результат, значение count() нужно вручную умножить на 10.
Выборка с указанием относительного коэффициента является «согласованной»: для таблиц с одним и тем же ключом сэмплирования, выборка с одинаковой относительной долей всегда будет составлять одно и то же подмножество данных. То есть выборка из разных таблиц, на разных серверах, в разное время, формируется одинаковым образом.
Здесь n – это достаточно большое целое число. Например, SAMPLE 10000000.
Если задано выражение SAMPLE n, запрос будет выполнен для выборки из не менее n строк (но не значительно больше этого значения). Например, если задать SAMPLE 10000000, в выборку попадут не менее 10,000,000 строк.
Примечание:
Следует иметь в виду, что `n` должно быть достаточно большим числом. Так как минимальной единицей данных для чтения является одна гранула (её размер задаётся настройкой `index_granularity` для таблицы), имеет смысл создавать выборки, размер которых существенно превосходит размер гранулы.
При выполнении SAMPLE n коэффициент сэмплирования заранее неизвестен (то есть нет информации о том, относительно какого количества данных будет сформирована выборка). Чтобы узнать коэффициент сэмплирования, используйте столбец _sample_factor.
Виртуальный столбец _sample_factor автоматически создается в тех таблицах, для которых задано выражение SAMPLE BY (подробнее см. в разделе Создание таблицы MergeTree). В столбце содержится коэффициент сэмплирования для таблицы – он рассчитывается динамически по мере добавления данных в таблицу. Ниже приведены примеры использования столбца _sample_factor.
Предположим, у нас есть таблица, в которой ведется статистика посещений сайта. Пример ниже показывает, как рассчитать суммарное число просмотров:
SELECT sum(PageViews * _sample_factor)
FROM visits
SAMPLE 10000000
Следующий пример показывает, как посчитать общее число визитов:
SELECT sum(_sample_factor)
FROM visits
SAMPLE 10000000В примере ниже рассчитывается среднее время на сайте. Обратите внимание, при расчете средних значений, умножать результат на коэффициент сэмплирования не нужно.
SELECT avg(Duration)
FROM visits
SAMPLE 10000000Здесь k и m – числа в интервале от 0 до 1. Например, SAMPLE 0.1 OFFSET 0.5. Поддерживается как дробная, так и десятичная форма записи.
При задании SAMPLE k OFFSET m, выборка будет сформирована из k доли данных со смещением на долю m. Примеры приведены ниже.
Пример 1:
SAMPLE 1/10В этом примере выборка будет сформирована по 1/10 доле всех данных:
[++------------------]
Пример 2:
SAMPLE 1/10 OFFSET 1/2Здесь выборка, которая состоит из 1/10 доли данных, взята из второй половины данных.
[----------++--------]
Вы можете использовать UNION в двух режимах: UNION ALL или UNION DISTINCT.
Если UNION используется без указания ALL или DISTINCT, то его поведение определяется настройкой union_default_mode. Разница между UNION ALL и UNION DISTINCT в том, что UNION DISTINCT выполняет явное преобразование для результата объединения. Это равнозначно выражению SELECT DISTINCT из подзапроса, содержащего UNION ALL.
Чтобы объединить любое количество SELECT запросов путем объединения их результатов, вы можете использовать UNION. Пример:
SELECT CounterID, 1 AS table, toInt64(count()) AS c
FROM test.hits
GROUP BY CounterID
UNION ALL
SELECT CounterID, 2 AS table, sum(Sign) AS c
FROM test.visits
GROUP BY CounterID
HAVING c > 0Результирующие столбцы сопоставляются по их индексу (порядку внутри SELECT). Если имена столбцов не совпадают, то имена для конечного результата берутся из первого запроса.
При объединении выполняет приведение типов. Например, если два запроса имеют одно и то же поле с не-Nullable и Nullable совместимыми типами, полученные в результате UNION данные будут иметь Nullable тип.
Запросы, которые являются частью UNION, могут быть заключены в круглые скобки. ORDER BY и LIMIT применяются к отдельным запросам, а не к конечному результату. Если вам нужно применить преобразование к конечному результату, вы можете разместить все объединенные с помощью UNION запросы в подзапрос в секции FROM.
Если используете UNION без явного указания UNION ALL или UNION DISTINCT, то вы можете указать режим объединения с помощью настройки union_default_mode, значениями которой могут быть ALL, DISTINCT или пустая строка. Однако если вы используете UNION с настройкой union_default_mode, значением которой является пустая строка, то будет сгенерировано исключение. В следующих примерах продемонстрированы результаты запросов при разных значениях настройки.
Запрос:
SET union_default_mode = 'DISTINCT';
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2;Результат:
┌─1─┐
│ 1 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 3 │
└───┘Запрос:
SET union_default_mode = 'ALL';
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 2;Результат:
┌─1─┐
│ 1 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 2 │
└───┘
┌─1─┐
│ 3 │
└───┘Запросы, которые являются частью UNION/UNION ALL/UNION DISTINCT, выполняются параллельно, и их результаты могут быть смешаны вместе.
Смотрите также:
EXCEPT возвращает только те строки, которые являются результатом первого запроса без результатов второго. В запросах количество, порядок следования и типы столбцов должны совпадать. Результат EXCEPT может содержать повторяющиеся строки.
Если используется несколько EXCEPT, и в выражении не указаны скобки, EXCEPT выполняется по порядку слева направо. EXCEPT имеет такой же приоритет выполнения, как UNION, и приоритет ниже, чем у INTERSECT.
SELECT column1 [, column2 ]
FROM table1
[WHERE condition]
EXCEPT
SELECT column1 [, column2 ]
FROM table2
[WHERE condition]Условие в секции WHERE может быть любым в зависимости от ваших требований.
Примеры:
Запрос:
SELECT number FROM numbers(1,10) EXCEPT SELECT number FROM numbers(3,6);Результат:
┌─number─┐
│ 1 │
│ 2 │
│ 9 │
│ 10 │
└─────────┘Запрос:
CREATE TABLE t1(one String, two String, three String) ENGINE=Memory();
CREATE TABLE t2(four String, five String, six String) ENGINE=Memory();
INSERT INTO t1 VALUES ('q', 'm', 'b'), ('s', 'd', 'f'), ('l', 'p', 'o'), ('s', 'd', 'f'), ('s', 'd', 'f'), ('k', 't', 'd'), ('l', 'p', 'o');
INSERT INTO t2 VALUES ('q', 'm', 'b'), ('b', 'd', 'k'), ('s', 'y', 't'), ('s', 'd', 'f'), ('m', 'f', 'o'), ('k', 'k', 'd');
SELECT * FROM t1 EXCEPT SELECT * FROM t2;Результат:
┌─one─┬─two─┬─three─┐
│ l │ p │ o │
│ k │ t │ d │
│ l │ p │ o │
└─────┴──────┴───────┘См. также:
Если в таблице несколько совпадающих строк, то ALL возвращает все из них. Поведение запроса SELECT ALL точно такое же, как и SELECT без аргумента DISTINCT. Если указаны оба аргумента: ALL и DISTINCT, функция вернет исключение.
ALL может быть указан внутри агрегатной функции, например, результат выполнения запроса:
SELECT sum(ALL number) FROM numbers(10);равен результату выполнения запроса:
SELECT sum(number) FROM numbers(10);Типовая операция для таблиц, содержащих столбец-массив — произвести новую таблицу, которая будет иметь столбец с каждым отдельным элементом массивов из изначального столбца, в то время как значения других столбцов дублируются. Это основной сценарий использования секции ARRAY JOIN.
Название этой секции происходит от того, что эту операцию можно рассматривать как исполняющий JOIN с массивом или вложенной структурой данных. Цель использования похожа на функцию arrayJoin, но функциональность секции шире.
Синтаксис:
SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...Вы можете указать только одну секцию ARRAY JOIN в SELECT запросе.
Поддерживаемые виды ARRAY JOIN перечислены ниже:
Приведенные ниже примеры демонстрируют использование ARRAY JOIN и LEFT ARRAY JOIN. Cоздадим таблицу с колонкой типа данных Array и вставим в него значения:
CREATE TABLE arrays_test
(
s String,
arr Array(UInt8)
) ENGINE = Memory;
INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
┌─s────────────┬─arr─────┐
│ Hello │ [1,2] │
│ World │ [3,4,5] │
│ Goodbye │ [] │
└──────────────┴──────────┘В приведенном ниже примере используется секция ARRAY JOIN:
SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;
┌─s─────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
└───────┴──────┘В следующем примере используется LEFT ARRAY JOIN:
SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;
┌─s────────────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
│ Goodbye │ 0 │
└──────────────┴──────┘В секции ARRAY JOIN может быть указан алиас для массива. В этом случае элемент массива доступен по этому алиас, а сам массив доступен по исходному имени. Пример:
SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;
┌─s─────┬─arr──────┬─a─┐
│ Hello │ [1,2] │ 1 │
│ Hello │ [1,2] │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴───────────┴───┘Используя псевдонимы, вы можете выполнять ARRAY JOIN с внешними массивами. Например:
SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;
┌─s───────────┬─arr_external─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ Hello │ 3 │
│ World │ 1 │
│ World │ 2 │
│ World │ 3 │
│ Goodbye │ 1 │
│ Goodbye │ 2 │
│ Goodbye │ 3 │
└──────────────┴───────────────┘Несколько массивов могут быть перечислены через запятую в секции ARRAY JOIN. В этом случае, JOIN выполняется с ними одновременно (прямая сумма, а не декартово произведение). Обратите внимание, что все массивы должны иметь одинаковый размер. Пример:
SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;
┌─s─────┬─arr──────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2] │ 1 │ 1 │ 2 │
│ Hello │ [1,2] │ 2 │ 2 │ 3 │
│ World │ [3,4,5] │ 3 │ 1 │ 4 │
│ World │ [3,4,5] │ 4 │ 2 │ 5 │
│ World │ [3,4,5] │ 5 │ 3 │ 6 │
└───────┴──────────┴────┴─────┴─────────┘В приведенном ниже примере используется функция arrayEnumerate:
SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
┌─s─────┬─arr──────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2] │ 1 │ 1 │ [1,2] │
│ Hello │ [1,2] │ 2 │ 2 │ [1,2] │
│ World │ [3,4,5] │ 3 │ 1 │ [1,2,3] │
│ World │ [3,4,5] │ 4 │ 2 │ [1,2,3] │
│ World │ [3,4,5] │ 5 │ 3 │ [1,2,3] │
└────────┴─────────┴────┴─────┴───────────────────────┘ARRAY JOIN также работает с вложенными структурами данных:
CREATE TABLE nested_test
(
s String,
nest Nested(
x UInt8,
y UInt32)
) ENGINE = Memory;
INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
┌─s───────┬─nest.x───┬─nest.y─────┐
│ Hello │ [1,2] │ [10,20] │
│ World │ [3,4,5] │ [30,40,50] │
│ Goodbye │ [] │ [] │
└──────────┴──────────┴─────────────┘
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└────────┴────────┴─────────┘При указании имен вложенных структур данных в ARRAY JOIN, секция работает так же, как и ARRAY JOIN со всеми элементами массива, из которых он состоит. Примеры приведены ниже:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;
┌─s─────┬─nest.x─┬──nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴─────────┴─────────┘Такая вариация также возможна:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;
┌─s─────┬─nest.x─┬─nest.y──────┐
│ Hello │ 1 │ [10,20] │
│ Hello │ 2 │ [10,20] │
│ World │ 3 │ [30,40,50] │
│ World │ 4 │ [30,40,50] │
│ World │ 5 │ [30,40,50] │
└───────┴─────────┴─────────────┘Алиас для вложенной структуры данных можно использовать, чтобы выбрать как результат JOIN-а, так и исходный массив. Пример:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;
┌─s─────┬─n.x─┬─n.y─┬─nest.x───┬─nest.y─────┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │
└───────┴──────┴─────┴──────────┴─────────────┘Пример использования функции arrayEnumerate:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
┌─s─────┬─n.x─┬─n.y──┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │ 1 │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │ 2 │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │ 1 │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │ 2 │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │ 3 │
└───────┴──────┴─────┴──────────┴─────────────┴──────┘Хотя секция ARRAY JOIN всегда должна быть указана перед WHERE/PREWHERE, технически они могут быть выполнены в любом порядке, если только результат ARRAY JOIN используется для фильтрации. Порядок обработки контролируется оптимизатором запросов.
В секции FROM указывается источник, из которого будут читаться данные:
Секция JOIN и ARRAY JOIN могут быть использованы для расширения функциональных возможностей секции FROM.
Подзапрос — дополнительный SELECT запрос, который может быть указан в круглых скобках внутри секции FROM.
Секция FROM может содержать несколько источников данных, указанных через запятую, что эквивалентно выполнению CROSS JOIN на них.
Если в запросе используется модификатор FINAL, то RT.WideStore полностью мёржит данные перед выдачей результата, таким образом выполняя все преобразования данных, которые производятся движком таблиц при мёржах.
Он применим при выборе данных из таблиц, использующих MergeTree- семейство движков. Также поддерживается для:
Теперь SELECT запросы с FINAL выполняются параллельно и, следовательно, немного быстрее. Но имеются серьезные недостатки при их использовании (смотрите ниже). Настройка max_final_threads устанавливает максимальное количество потоков.
Запросы, которые используют FINAL выполняются немного медленнее, чем аналогичные запросы без него, потому что:
Данные мёржатся во время выполнения запроса в памяти, и это не приводит к физическому мёржу кусков на дисках.
Запросы с модификатором FINAL читают столбцы первичного ключа в дополнение к столбцам, используемым в запросе.
В большинстве случаев избегайте использования FINAL. Общий подход заключается в использовании агрегирующих запросов, которые предполагают, что фоновые процессы движков семейства MergeTree ещё не случились (например, сами отбрасывают дубликаты). {TODO: examples}
Если секция FROM опущена, данные будут считываться из таблицы system.one. Таблица system.one содержит ровно одну строку.
Для выполнения запроса, из соответствующей таблицы, вынимаются все столбцы, перечисленные в запросе. Из подзапросов выкидываются столбцы, не нужные для внешнего запроса.
Если в запросе не перечислено ни одного столбца (например, SELECT count() FROM t), то из таблицы всё равно вынимается один какой-нибудь столбец (предпочитается самый маленький), для того, чтобы можно было посчитать количество строк.
Позволяет задать выражение, которое RT.WideStore использует для фильтрации данных перед всеми другими действиями в запросе кроме выражений, содержащихся в секции PREWHERE. Обычно это выражение с логическими операторами.
Результат выражения должен иметь тип UInt8.
RT.WideStore использует в выражении индексы, если это позволяет движок таблицы.
Примечание:
Существует оптимизация фильтрации под названием [PREWHERE].
Если в секции необходимо проверить NULL, то используйте операторы IS NULL и IS NOT NULL, а также соответствующие функции isNull и isNotNull. В противном случае выражение будет считаться всегда не выполненным.
Пример:
Чтобы найти числа, которые кратны 3 и больше 10, можно выполнить запрос к таблице numbers:
SELECT number FROM numbers(20) WHERE (number > 10) AND (number % 3 == 0);Результат:
┌─number─┐
│ 12 │
│ 15 │
│ 18 │
└─────────┘Пример проверки на NULL:
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE=MergeTree() ORDER BY x;
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT * FROM t_null WHERE y IS NULL;
SELECT * FROM t_null WHERE y != 0;Результат:
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
└───┴──────┘
┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘RT.WideStore поддерживает широкий спектр форматов сериализации это может быть использовано, в частности, для результатов запросов. Существует несколько способов выбора формата для SELECT, один из них заключается в том, чтобы указать FORMAT format в конце запроса, чтобы получить результирующие данные в любом конкретном формате.
Определенный формат может использоваться для удобства, интеграции с другими системами или для повышения производительности.
Если FORMAT предложение опущено, используется формат по умолчанию, который зависит как от настроек, так и от интерфейса, используемого для доступа к серверу RT.WideStore. Для HTTP интерфейса и клиента командной строки в пакетном режиме, формат по умолчанию — TabSeparated. Для клиента командной строки в интерактивном режиме по умолчанию используется формат PrettyCompact (он производит компактные человекочитаемые таблицы).
При использовании клиента командной строки данные всегда передаются по сети во внутреннем эффективном формате (Native). Клиент самостоятельно интерпретирует FORMAT предложение запроса и форматирует сами данные (тем самым освобождая сеть и сервер от дополнительной нагрузки).
Добавляет данные в таблицу.
Базовый формат запроса:
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...Вы можете указать список столбцов для вставки, используя синтаксис (c1, c2, c3). Также можно использовать выражение cо звездочкой и/или модификаторами, такими как APPLY, EXCEPT, REPLACE.
В качестве примера рассмотрим таблицу:
SHOW CREATE insert_select_testtable
CREATE TABLE insert_select_testtable
(
`a` Int8,
`b` String,
`c` Int8
)
ENGINE = MergeTree()
ORDER BY a
INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1)Если вы хотите вставить данные во все столбцы, кроме 'b', вам нужно передать столько значений, сколько столбцов вы указали в скобках:
INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2)
SELECT * FROM insert_select_testtable
┌─a─┬─b─┬─c──┐
│ 2 │ │ 2 │
└───┴───┴────┘
┌─a─┬─b─┬─c──┐
│ 1 │ a │ 1 │
└───┴───┴────┘В этом примере мы видим, что вторая строка содержит столбцы a и c, заполненные переданными значениями и b, заполненный значением по умолчанию:
INSERT INTO insert_select_testtable VALUES (1, DEFAULT, 1) ;Если список столбцов не включает все существующие столбцы, то все остальные столбцы заполняются следующим образом:
В INSERT можно передавать данные любого формата, который поддерживает RT.WideStore. Для этого формат необходимо указать в запросе в явном виде:
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_setНапример, следующий формат запроса идентичен базовому варианту INSERT … VALUES:
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13), (v21, v22, v23), ...RT.WideStore отсекает все пробелы и один перенос строки (если он есть) перед данными. Рекомендуем при формировании запроса переносить данные на новую строку после операторов запроса (это важно, если данные начинаются с пробелов).
Пример:
INSERT INTO t FORMAT TabSeparated
11 Hello, world!
22 QwertyС помощью консольного клиента или HTTP интерфейса можно вставлять данные отдельно от запроса. Как это сделать, читайте в разделе «Интерфейсы».
Если в таблице объявлены ограничения, то их выполнимость будет проверена для каждой вставляемой строки. Если для хотя бы одной строки ограничения не будут выполнены, запрос будет остановлен.
INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...Соответствие столбцов определяется их позицией в секции SELECT. При этом, их имена в выражении SELECT и в таблице для INSERT, могут отличаться. При необходимости выполняется приведение типов данных, эквивалентное соответствующему оператору CAST.
Все форматы данных кроме Values не позволяют использовать в качестве значений выражения, такие как now(), 1 + 2 и подобные. Формат Values позволяет ограниченно использовать выражения, но это не рекомендуется, так как в этом случае для их выполнения используется неэффективный вариант кода.
Не поддерживаются другие запросы на модификацию части данных: UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE.
Вы можете удалять старые данные с помощью запроса ALTER TABLE ... DROP PARTITION.
Для табличной функции input() после секции SELECT должна следовать секция FORMAT.
INSERT сортирует входящие данные по первичному ключу и разбивает их на партиции по ключу партиционирования. Если вы вставляете данные в несколько партиций одновременно, то это может значительно снизить производительность запроса INSERT. Чтобы избежать этого:
Снижения производительности не будет, если:
Запрос create создает новую сущность одного из следующих типов:
Создает базу данных.
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)] [COMMENT 'Comment']Если база данных с именем db_name уже существует, то RT.WideStore не создает базу данных и:
RT.WideStore создаёт базу данных с именем db_name на всех серверах указанного кластера. Более подробную информацию смотрите в разделе Распределенные DDL запросы.
По умолчанию RT.WideStore использует собственный движок баз данных Atomic. Есть также движки баз данных Lazy, MySQL, PostgreSQL, MaterializedMySQL, MaterializedPostgreSQL, Replicated, SQLite.
Вы можете добавить комментарий к базе данных при ее создании.
Комментарий поддерживается для всех движков баз данных.
Синтаксис:
CREATE DATABASE db_name ENGINE = engine(...) COMMENT 'Comment'Пример:
Запрос:
CREATE DATABASE db_comment ENGINE = Memory COMMENT 'The temporary database';
SELECT name, comment FROM system.databases WHERE name = 'db_comment';Результат:
┌─name───────┬─comment──────────────────┐
│ db_comment │ The temporary database │
└─────────────┴──────────────────────────┘Запрос CREATE TABLE может иметь несколько форм, которые используются в зависимости от контекста и решаемых задач.
По умолчанию таблицы создаются на текущем сервере. Распределенные DDL запросы создаются с помощью секции ON CLUSTER, которая описана отдельно.
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
...
) ENGINE = engineСоздаёт таблицу с именем name в БД db или текущей БД, если db не указана, со структурой, указанной в скобках, и движком engine. Структура таблицы представляет список описаний столбцов. Индексы, если поддерживаются движком, указываются в качестве параметров для движка таблицы.
Описание столбца, это name type, в простейшем случае. Пример: RegionID UInt32. Также могут быть указаны выражения для значений по умолчанию - смотрите ниже.
При необходимости можно указать первичный ключ с одним или несколькими ключевыми выражениями.
CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]Создаёт таблицу с такой же структурой, как другая таблица. Можно указать другой движок для таблицы. Если движок не указан, то будет выбран такой же движок, как у таблицы db2.name2.
CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()Создаёт таблицу с такой же структурой и данными, как результат соответствующей табличной функции. Созданная таблица будет работать так же, как и указанная табличная функция.
CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...Создаёт таблицу со структурой, как результат запроса SELECT, с движком engine, и заполняет её данными из SELECT. Также вы можете явно задать описание столбцов.
Если таблица уже существует и указано IF NOT EXISTS, то запрос ничего не делает.
После секции ENGINE в запросе могут использоваться и другие секции в зависимости от движка. Подробную документацию по созданию таблиц смотрите в описаниях движков таблиц.
Пример:
Запрос:
CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;
SELECT x, toTypeName(x) FROM t1;Результат:
┌─x─┬─toTypeName(x)─┐
│ 1 │ String │
└───┴────────────────┘Модификатор NULL или NOT NULL, указанный после типа данных в определении столбца, позволяет или не позволяет типу данных быть Nullable.
Если тип не Nullable и указан модификатор NULL, то столбец будет иметь тип Nullable; если NOT NULL, то не Nullable. Например, INT NULL то же, что и Nullable(INT). Если тип Nullable и указаны модификаторы NULL или NOT NULL, то будет вызвано исключение.
Смотрите также настройку data_type_default_nullable.
В описании столбца, может быть указано выражение для значения по умолчанию, одного из следующих видов: DEFAULT expr, MATERIALIZED expr, ALIAS expr. Пример: URLDomain String DEFAULT domain(URL).
Если выражение для значения по умолчанию не указано, то в качестве значений по умолчанию будут использоваться нули для чисел, пустые строки для строк, пустые массивы для массивов, а также 0000-00-00 для дат и 0000-00-00 00:00:00 для дат с временем. NULL-ы не поддерживаются.
В случае, если указано выражение по умолчанию, то указание типа столбца не обязательно. При отсутствии явно указанного типа, будет использован тип выражения по умолчанию. Пример: EventDate DEFAULT toDate(EventTime) - для столбца EventDate будет использован тип Date.
При наличии явно указанного типа данных и выражения по умолчанию, это выражение будет приводиться к указанному типу с использованием функций приведения типа. Пример: Hits UInt32 DEFAULT 0 - имеет такой же смысл, как Hits UInt32 DEFAULT toUInt32(0).
В качестве выражения для умолчания, может быть указано произвольное выражение от констант и столбцов таблицы. При создании и изменении структуры таблицы, проверяется, что выражения не содержат циклов. При INSERT-е проверяется разрешимость выражений - что все столбцы, из которых их можно вычислить, переданы.
DEFAULT expr
Обычное значение по умолчанию. Если в запросе INSERT не указан соответствующий столбец, то он будет заполнен путём вычисления соответствующего выражения.
MATERIALIZED expr
Материализованное выражение. Такой столбец не может быть указан при INSERT, то есть, он всегда вычисляется. При INSERT без указания списка столбцов, такие столбцы не рассматриваются. Также этот столбец не подставляется при использовании звёздочки в запросе SELECT. Это необходимо, чтобы сохранить инвариант, что дамп, полученный путём SELECT *, можно вставить обратно в таблицу INSERT-ом без указания списка столбцов.
EPHEMERAL [expr]
Эфемерное выражение. Такой столбец не хранится в таблице и не может быть получен в запросе SELECT, но на него можно ссылаться в выражениях по умолчанию запроса CREATE. Если значение по умолчанию expr не указано, то тип колонки должен быть специфицирован. INSERT без списка столбцов игнорирует этот столбец, таким образом сохраняется инвариант - т.е. дамп, полученный путём SELECT *, можно вставить обратно в таблицу INSERT-ом без указания списка столбцов.
ALIAS expr
Синоним. Такой столбец вообще не хранится в таблице. Его значения не могут быть вставлены в таблицу, он не подставляется при использовании звёздочки в запросе SELECT. Он может быть использован в SELECT-ах - в таком случае, во время разбора запроса, алиас раскрывается.
При добавлении новых столбцов с помощью запроса ALTER, старые данные для этих столбцов не записываются. Вместо этого, при чтении старых данных, для которых отсутствуют значения новых столбцов, выполняется вычисление выражений по умолчанию на лету. При этом, если выполнение выражения требует использования других столбцов, не указанных в запросе, то эти столбцы будут дополнительно прочитаны, но только для тех блоков данных, для которых это необходимо.
Если добавить в таблицу новый столбец, а через некоторое время изменить его выражение по умолчанию, то используемые значения для старых данных (для данных, где значения не хранились на диске) поменяются. Также заметим, что при выполнении фоновых слияний, данные для столбцов, отсутствующих в одном из сливаемых кусков, записываются в объединённый кусок.
Отсутствует возможность задать значения по умолчанию для элементов вложенных структур данных.
Вы можете определить первичный ключ при создании таблицы. Первичный ключ может быть указан двумя способами:
CREATE TABLE db.table_name
(
name1 type1, name2 type2, ...,
PRIMARY KEY(expr1[, expr2,...])]
)
ENGINE = engine;
CREATE TABLE db.table_name
(
name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);
ПРЕДУПРЕЖДЕНИЕ:
Вы не можете сочетать оба способа в одном запросе.
Ограничения
Наряду с объявлением столбцов можно объявить ограничения на значения в столбцах таблицы:
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
...
CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
...
)
ENGINE = engineboolean_expr_1 может быть любым булевым выражением, состоящим из операторов сравнения или функций. При наличии одного или нескольких ограничений в момент вставки данных выражения ограничений будут проверяться на истинность для каждой вставляемой строки данных. В случае, если в теле INSERT запроса придут некорректные данные — клиент получит исключение с описанием нарушенного ограничения.
Добавление большого числа ограничений может негативно повлиять на производительность INSERT запросов.
Выражение для TTL
Определяет время хранения значений. Может быть указано только для таблиц семейства MergeTree. Подробнее смотрите в TTL для столбцов и таблиц.
Кодеки сжатия столбцов
По умолчанию, RT.WideStore применяет к столбцу метод сжатия, определённый в конфигурации сервера. Кроме этого, можно задать метод сжатия для каждого отдельного столбца в запросе CREATE TABLE.
CREATE TABLE codec_example
(
dt Date CODEC(ZSTD),
ts DateTime CODEC(LZ4HC),
float_value Float32 CODEC(NONE),
double_value Float64 CODEC(LZ4HC(9)),
value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...Если кодек Default задан для столбца, используется сжатие по умолчанию, которое может зависеть от различных настроек (и свойств данных) во время выполнения. Пример: value UInt64 CODEC(Default) — то же самое, что не указать кодек.
Также можно подменить кодек столбца сжатием по умолчанию, определенным в config.xml:
ALTER TABLE codec_example MODIFY COLUMN float_value CODEC(Default);Кодеки можно последовательно комбинировать, например, CODEC(Delta, Default).
Нельзя распаковать базу данных RT.WideStore с помощью сторонних утилит наподобие `lz4`. Необходимо использовать специальную утилиту [WideStore-compressor].
Сжатие поддерживается для следующих движков таблиц:
RT.WideStore поддерживает кодеки общего назначения и специализированные кодеки.
Кодеки:
NONE — без сжатия.LZ4 — алгоритм сжатия без потерь используемый по умолчанию. Применяет быстрое сжатие LZ4.LZ4HC[(level)] — алгоритм LZ4 HC (high compression) с настраиваемым уровнем сжатия. Уровень по умолчанию — 9. Настройка level <= 0 устанавливает уровень сжания по умолчанию. Возможные уровни сжатия: [1, 12]. Рекомендуемый диапазон уровней: [4, 9].ZSTD[(level)] — алгоритм сжатия ZSTD с настраиваемым уровнем сжатия level. Возможные уровни сжатия: [1, 22]. Уровень сжатия по умолчанию: 1.Высокие уровни сжатия полезны для ассимметричных сценариев, подобных «один раз сжал, много раз распаковал». Они подразумевают лучшее сжатие, но большее использование CPU.
Эти кодеки разработаны для того, чтобы, используя особенности данных сделать сжатие более эффективным. Некоторые из этих кодеков не сжимают данные самостоятельно. Они готовят данные для кодеков общего назначения, которые сжимают подготовленные данные эффективнее, чем неподготовленные.
Специализированные кодеки:
Delta(delta_bytes) — Метод, в котором исходные значения заменяются разностью двух соседних значений, за исключением первого значения, которое остаётся неизменным. Для хранения разниц используется до delta_bytes, т.е. delta_bytes — это максимальный размер исходных данных. Возможные значения delta_bytes: 1, 2, 4, 8. Значение по умолчанию для delta_bytes равно sizeof(type), если результат 1, 2, 4, or 8. Во всех других случаях — 1.DoubleDelta — Вычисляется разницу от разниц и сохраняет её в компактном бинарном виде. Оптимальная степень сжатия достигается для монотонных последовательностей с постоянным шагом, наподобие временных рядов. Можно использовать с любым типом данных фиксированного размера. Реализует алгоритм, используемый в TSDB Gorilla, поддерживает 64-битные типы данных. Использует 1 дополнительный бит для 32-байтовых значений: 5-битные префиксы вместо 4-битных префиксов. Подробнее читайте в разделе «Compressing Time Stamps» документа Gorilla: A Fast, Scalable, In-Memory Time Series Database.Gorilla — Вычисляет XOR между текущим и предыдущим значением и записывает результат в компактной бинарной форме. Эффективно сохраняет ряды медленно изменяющихся чисел с плавающей запятой, поскольку наилучший коэффициент сжатия достигается, если соседние значения одинаковые. Реализует алгоритм, используемый в TSDB Gorilla, адаптируя его для работы с 64-битными значениями. Подробнее читайте в разделе «Compressing Values» документа Gorilla: A Fast, Scalable, In-Memory Time Series Database.T64 — Метод сжатия который обрезает неиспользуемые старшие биты целочисленных значений (включая Enum, Date и DateTime). На каждом шаге алгоритма, кодек помещает блок из 64 значений в матрицу 64✕64, транспонирует её, обрезает неиспользуемые биты, а то, что осталось возвращает в виде последовательности. Неиспользуемые биты, это биты, которые не изменяются от минимального к максимальному на всём диапазоне значений куска данных.Кодеки DoubleDelta и Gorilla используются в TSDB Gorilla как компоненты алгоритма сжатия. Подход Gorilla эффективен в сценариях, когда данные представляют собой медленно изменяющиеся во времени величины. Метки времени эффективно сжимаются кодеком DoubleDelta, а значения кодеком Gorilla. Например, чтобы создать эффективно хранящуюся таблицу, используйте следующую конфигурацию:
CREATE TABLE codec_example
(
timestamp DateTime CODEC(DoubleDelta),
slow_values Float32 CODEC(Gorilla)
)
ENGINE = MergeTree()Эти кодеки не сжимают данные, вместо этого они зашифровывают данные на диске. Воспользоваться кодеками можно, только когда ключ шифрования задан параметрами шифрования. Обратите внимание: ставить кодеки шифрования имеет смысл в самый конец цепочки кодеков, потому что зашифрованные данные, как правило, нельзя сжать релевантным образом.
Кодеки шифрования:
CODEC('AES-128-GCM-SIV') — Зашифровывает данные с помощью AES-128 в режиме RFC 8452 GCM-SIV.CODEC('AES-256-GCM-SIV') — Зашифровывает данные с помощью AES-256 в режиме GCM-SIV.Эти кодеки используют фиксированный одноразовый ключ шифрования. Таким образом, это детерминированное шифрование. Оно совместимо с поддерживающими дедупликацию движками, в частности, ReplicatedMergeTree. Однако у шифрования имеется недостаток: если дважды зашифровать один и тот же блок данных, текст на выходе получится одинаковым, и злоумышленник, у которого есть доступ к диску, заметит эту эквивалентность (при этом доступа к содержимому он не получит).
Внимание:
Большинство движков, включая семейство `MergeTree`, создают на диске индексные файлы, не применяя кодеки. А значит, в том случае, если зашифрованный столбец индексирован, на диске отобразится незашифрованный текст.
Внимание:
Если вы выполняете запрос SELECT с упоминанием конкретного значения в зашифрованном столбце (например, при использовании секции WHERE), это значение может появиться в [system.query_log]. Рекомендуем отключить логирование.
Пример:
CREATE TABLE mytable
(
x String Codec(AES_128_GCM_SIV)
)
ENGINE = MergeTree ORDER BY x;
Замечание:
Если необходимо применить сжатие, это нужно явно прописать в запросе. Без этого будет выполнено только шифрование данных.
Пример:
CREATE TABLE mytable
(
x String Codec(Delta, LZ4, AES_128_GCM_SIV)
)
ENGINE = MergeTree ORDER BY x;RT.WideStore поддерживает временные таблицы со следующими характеристиками:
KeeperMap.ON CLUSTER): такая таблица существует только в рамках существующей сессии.Чтобы создать временную таблицу, используйте следующий синтаксис:
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) [ENGINE = engine]В большинстве случаев, временные таблицы создаются не вручную, а при использовании внешних данных для запроса, или при распределённом (GLOBAL) IN. Подробнее см. соответствующие разделы
Вместо временных можно использовать обычные таблицы с ENGINE = Memory.
Запрос REPLACE позволяет частично изменить таблицу (структуру или данные).
Замечание:
Такие запросы поддерживаются только движком БД Atomic.
Чтобы удалить часть данных из таблицы, вы можете создать новую таблицу, добавить в нее данные из старой таблицы, которые вы хотите оставить (отобрав их с помощью запроса SELECT), затем удалить старую таблицу и переименовать новую таблицу так как старую:
CREATE TABLE myNewTable AS myOldTable;
INSERT INTO myNewTable SELECT * FROM myOldTable WHERE CounterID <12345;
DROP TABLE myOldTable;
RENAME TABLE myNewTable TO myOldTable;Вместо перечисленных выше операций можно использовать один запрос:
REPLACE TABLE myOldTable SELECT * FROM myOldTable WHERE CounterID <12345;Синтаксис:
{CREATE [OR REPLACE]|REPLACE} TABLE [db.]table_nameДля данного запроса можно использовать любые варианты синтаксиса запроса CREATE. Запрос REPLACE для несуществующей таблицы вызовет ошибку.
Примеры:
Рассмотрим таблицу:
CREATE DATABASE base ENGINE = Atomic;
CREATE OR REPLACE TABLE base.t1 (n UInt64, s String) ENGINE = MergeTree ORDER BY n;
INSERT INTO base.t1 VALUES (1, 'test');
SELECT * FROM base.t1;
┌─n─┬─s────┐
│ 1 │ test │
└───┴───────┘Используем запрос REPLACE для удаления всех данных:
CREATE OR REPLACE TABLE base.t1 (n UInt64, s Nullable(String)) ENGINE = MergeTree ORDER BY n;
INSERT INTO base.t1 VALUES (2, null);
SELECT * FROM base.t1;
┌─n─┬─s──┐
│ 2 │ \N │
└───┴────┘Используем запрос REPLACE для изменения структуры таблицы:
REPLACE TABLE base.t1 (n UInt64) ENGINE = MergeTree ORDER BY n;
INSERT INTO base.t1 VALUES (3);
SELECT * FROM base.t1;
┌─n─┐
│ 3 │
└───┘Вы можете добавить комментарий к таблице при ее создании.
Замечание:
Комментарий поддерживается для всех движков таблиц, кроме Kafka, RabbitMQ и EmbeddedRocksDB.
Синтаксис:
CREATE TABLE db.table_name
(
name1 type1, name2 type2, ...
)ENGINE = engine
COMMENT 'Comment'Пример:
Запрос:
CREATE TABLE t1 (x String) ENGINE = Memory COMMENT 'The temporary table';
SELECT name, comment FROM system.tables WHERE name = 't1';Результат:
┌─name─┬─comment──────────────┐
│ t1 │ The temporary table │
└───────┴──────────────────────┘Создаёт представление. Представления бывают обычные, материализованные (MATERIALIZED) и LIVE.
CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster_name] AS SELECT ...Обычные представления не хранят никаких данных, они выполняют чтение данных из другой таблицы при каждом доступе. Другими словами, обычное представление — это не что иное, как сохраненный запрос. При чтении данных из представления этот сохраненный запрос используется как подзапрос в секции FROM.
Для примера, пусть вы создали представление:
CREATE VIEW view AS SELECT ...и написали запрос:
SELECT a, b, c FROM viewЭтот запрос полностью эквивалентен использованию подзапроса:
SELECT a, b, c FROM (SELECT ...)CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...Материализованные (MATERIALIZED) представления хранят данные, преобразованные соответствующим запросом SELECT.
При создании материализованного представления без использования TO [db].[table], нужно обязательно указать ENGINE - движок таблицы для хранения данных.
При создании материализованного представления с использованием TO [db].[table], нельзя указывать POPULATE.
Материализованное представление устроено следующим образом: при вставке данных в таблицу, указанную в SELECT-е, кусок вставляемых данных преобразуется этим запросом SELECT, и полученный результат вставляется в представление.
Важно:
Материализованные представления в RT.WideStore используют **имена столбцов** вместо порядка следования столбцов при вставке в целевую таблицу. Если в результатах запроса `SELECT` некоторые имена столбцов отсутствуют, то RT.WideStore использует значение по умолчанию, даже если столбец не является Nullable. Безопасной практикой при использовании материализованных представлений считается добавление псевдонимов для каждого столбца.Материализованные представления в RT.WideStore больше похожи на `after insert` триггеры. Если в запросе материализованного представления есть агрегирование, оно применяется только к вставляемому блоку записей. Любые изменения существующих данных исходной таблицы (например обновление, удаление, удаление раздела и т.д.) не изменяют материализованное представление.
Если указано POPULATE, то при создании представления в него будут добавлены данные, уже содержащиеся в исходной таблице, как если бы был сделан запрос CREATE TABLE ... AS SELECT ... . Если POPULATE не указано, представление будет содержать только данные, добавленные в таблицу после создания представления. Использовать POPULATE не рекомендуется, так как в представление не попадут данные, добавляемые в таблицу во время создания представления.
Запрос SELECT может содержать DISTINCT, GROUP BY, ORDER BY, LIMIT… Следует иметь ввиду, что соответствующие преобразования будут выполняться независимо, на каждый блок вставляемых данных. Например, при наличии GROUP BY, данные будут агрегироваться при вставке, но только в рамках одной пачки вставляемых данных. Далее, данные не будут доагрегированы. Исключение - использование ENGINE, производящего агрегацию данных самостоятельно, например, SummingMergeTree.
Выполнение запросов ALTER над материализованными представлениями имеет свои особенности, поэтому эти запросы могут быть неудобными для использования. Если материализованное представление использует конструкцию TO [db.]name, то можно выполнить DETACH представления, ALTER для целевой таблицы и последующий ATTACH ранее отсоединенного (DETACH) представления.
Обратите внимание, что работа материализованного представления находится под влиянием настройки optimize_on_insert. Перед вставкой данных в таблицу происходит их слияние.
Представления выглядят так же, как обычные таблицы. Например, они перечисляются в результате запроса SHOW TABLES.
Чтобы удалить представление, следует использовать DROP VIEW. Впрочем, DROP TABLE тоже работает для представлений.
Важно:
Представления `LIVE VIEW` являются экспериментальной возможностью. Их использование может повлечь потерю совместимости в будущих версиях.Чтобы использовать `LIVE VIEW` и запросы `WATCH`, включите настройку allow_experimental_live_view.
CREATE LIVE VIEW [IF NOT EXISTS] [db.]table_name [WITH [TIMEOUT [value_in_sec] [AND]] [REFRESH [value_in_sec]]] AS SELECT ...LIVE VIEW хранит результат запроса SELECT, указанного при создании, и обновляется сразу же при изменении этого результата. Конечный результат запроса и промежуточные данные, из которых формируется результат, хранятся в оперативной памяти, и это обеспечивает высокую скорость обработки для повторяющихся запросов. LIVE-представления могут отправлять push-уведомления при изменении результата исходного запроса SELECT. Для этого используйте запрос WATCH.
Изменение LIVE VIEW запускается при вставке данных в таблицу, указанную в исходном запросе SELECT.
LIVE-представления работают по тому же принципу, что и распределенные таблицы. Но вместо объединения отдельных частей данных с разных серверов, LIVE-представления объединяют уже имеющийся результат с новыми данными. Если в исходном запросе LIVE-представления есть вложенный подзапрос, его результаты не кешируются, в кеше хранится только результат основного запроса.
ОГРАНИЧЕНИЯ:
Табличные функции в основном запросе не поддерживаются. Таблицы, не поддерживающие изменение с помощью запроса `INSERT`, такие как словари и системные таблицы, а также нормальные представления или материализованные представления, не запускают обновление LIVE-представления.- В LIVE-представлениях могут использоваться только такие запросы, которые объединяют результаты по старым и новым данным. LIVE-представления не работают с запросами, требующими полного пересчета данных или агрегирования с сохранением состояния.- `LIVE VIEW` не работает для реплицируемых и распределенных таблиц, добавление данных в которые происходит на разных узлах. `LIVE VIEW` не обновляется, если в исходном запросе используются несколько таблиц.В случаях, когда `LIVE VIEW` не обновляется автоматически, чтобы обновлять его принудительно с заданной периодичностью, используйте WITH REFRESH.
Для отслеживания изменений LIVE-представления используйте запрос WATCH.
Пример:
CREATE TABLE mt (x Int8) Engine = MergeTree ORDER BY x;
CREATE LIVE VIEW lv AS SELECT sum(x) FROM mt;Отслеживаем изменения LIVE-представления при вставке данных в исходную таблицу.
WATCH lv;
┌─sum(x)─┬─_version─┐
│ 1 │ 1 │
└─────────┴──────────┘
┌─sum(x)─┬─_version─┐
│ 3 │ 2 │
└─────────┴──────────┘
┌─sum(x)─┬─_version─┐
│ 6 │ 3 │
└─────────┴──────────┘
INSERT INTO mt VALUES (1);
INSERT INTO mt VALUES (2);
INSERT INTO mt VALUES (3);Для получения списка изменений используйте ключевое слово EVENTS.
WATCH lv EVENTS;
┌─version─┐
│ 1 │
└──────────┘
┌─version─┐
│ 2 │
└──────────┘
┌─version─┐
│ 3 │
└──────────┘
...Для работы с LIVE-представлениями, как и с любыми другими, можно использовать запросы SELECT. Если результат запроса кеширован, он будет возвращен немедленно, без обращения к исходным таблицам представления.
SELECT * FROM [db.]live_view WHERE ...Чтобы принудительно обновить LIVE-представление, используйте запрос ALTER LIVE VIEW [db.]table_name REFRESH.
LIVE-представление, созданное с параметром WITH REFRESH, будет автоматически обновляться через указанные промежутки времени, начиная с момента последнего обновления.
CREATE LIVE VIEW [db.]table_name WITH REFRESH [value_in_sec] AS SELECT ...Если значение временного промежутка не задано, используется значение periodic_live_view_refresh.
Пример:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv;
┌────────────────now()─┬─_version─┐
│ 2021-02-21 08:47:05 │ 1 │
└───────────────────────┴───────────┘
┌────────────────now()─┬─_version──┐
│ 2021-02-21 08:47:10 │ 2 │
└───────────────────────┴───────────┘
┌────────────────now()─┬─_version──┐
│ 2021-02-21 08:47:15 │ 3 │
└───────────────────────┴───────────┘
WATCH lv;
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table default.lv doesn't exist..Наиболее частые случаи использования LIVE-представлений:
SELECT.Создаёт внешний словарь с заданной структурой, источником, способом размещения в памяти и периодом обновления.
Синтаксис:
CREATE DICTIONARY [OR REPLACE][IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
key1 type1 [DEFAULT|EXPRESSION expr1] [IS_OBJECT_ID],
key2 type2 [DEFAULT|EXPRESSION expr2],
attr1 type2 [DEFAULT|EXPRESSION expr3] [HIERARCHICAL|INJECTIVE],
attr2 type2 [DEFAULT|EXPRESSION expr4] [HIERARCHICAL|INJECTIVE]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME({MIN min_val MAX max_val | max_val})
SETTINGS(setting_name = setting_value, setting_name = setting_value, ...)
COMMENT 'Comment'Структура внешнего словаря состоит из атрибутов. Атрибуты словаря задаются как столбцы таблицы. Единственным обязательным свойством атрибута является его тип, все остальные свойства могут иметь значения по умолчанию.
В зависимости от способа размещения словаря в памяти, ключами словаря могут быть один и более атрибутов.
Более подробную информацию смотрите в разделе внешние словари.
Вы можете добавить комментарий к словарю при его создании, используя секцию COMMENT.
Пример:
Входная таблица source_table:
┌─id─┬─value──┐
│ 1 │ First │
│ 2 │ Second │
└────┴─────────┘Создание словаря:
CREATE DICTIONARY dictionary_with_comment
(
id UInt64,
value String
)
PRIMARY KEY id
SOURCE(WIDESTORE(HOST 'localhost' PORT tcpPort() TABLE 'source_table'))
LAYOUT(FLAT())
LIFETIME(MIN 0 MAX 1000)
COMMENT 'The temporary dictionary';Вывод словаря:
SHOW CREATE DICTIONARY dictionary_with_comment;
┌─statement─────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE DICTIONARY default.dictionary_with_comment │
│( │
│ `id` UInt64, │
│ `value` String │
│) │
│PRIMARY KEY idSOURCE(WIDESTORE(HOST 'localhost' PORT tcpPort() TABLE 'source_table')) │
│LIFETIME(MIN 0 MAX 1000) │
│LAYOUT(FLAT()) │
│COMMENT 'The temporary dictionary' │
└────────────────────────────────────────────────────────────────────────────────────────────────┘Вывод комментария к словарю:
SELECT comment FROM system.dictionaries WHERE name == 'dictionary_with_comment' AND database == currentDatabase();
┌─comment───────────────────┐
│ The temporary dictionary │
└────────────────────────────┘См. также:
Создает пользовательскую функцию из лямбда-выражения. Выражение должно состоять из параметров функции, констант, операторов и вызовов других функций.
Синтаксис:
CREATE FUNCTION name AS (parameter0, ...) -> expressionУ функции может быть произвольное число параметров.
Существует несколько ограничений на создаваемые функции:
Если какое-нибудь ограничение нарушается, то при попытке создать функцию возникает исключение.
Пример:
Запрос:
CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b;
SELECT number, linear_equation(number, 2, 1) FROM numbers(3);Результат:
┌─number─┬─plus(multiply(2, number), 1)─┐
│ 0 │ 1 │
│ 1 │ 3 │
│ 2 │ 5 │
└─────────┴────────────────────────────────┘В следующем запросе пользовательская функция вызывает условную функцию:
CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even');
SELECT number, parity_str(number) FROM numbers(3);Результат:
┌─number─┬─if(modulo(number, 2), 'odd', 'even')─┐
│ 0 │ even │
│ 1 │ odd │
│ 2 │ even │
└─────────┴─────────────────────────────────────────┘Создает аккаунты пользователей.
Синтаксис:
CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
[, name2 [ON CLUSTER cluster_name2] ...]
[NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']}]
[HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[DEFAULT ROLE role [,...]]
[DEFAULT DATABASE database | NONE]
[GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]ON CLUSTER позволяет создавать пользователей в кластере, см. Распределенные DDL.
Существует несколько способов идентификации пользователя:
IDENTIFIED WITH no_passwordIDENTIFIED WITH plaintext_password BY 'qwerty'IDENTIFIED WITH sha256_password BY 'qwerty' or IDENTIFIED BY 'password'IDENTIFIED WITH sha256_hash BY 'hash' or IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt'IDENTIFIED WITH double_sha1_password BY 'qwerty'IDENTIFIED WITH double_sha1_hash BY 'hash'IDENTIFIED WITH ldap SERVER 'server_name'IDENTIFIED WITH kerberos or IDENTIFIED WITH kerberos REALM 'realm'Для идентификации с sha256_hash используя SALT - хэш должен быть вычислен от конкатенации 'password' и 'salt'.
Пользовательский хост — это хост, с которого можно установить соединение с сервером RT.WideStore. Хост задается в секции HOST следующими способами:
HOST IP 'ip_address_or_subnetwork' — Пользователь может подключиться к серверу RT.WideStore только с указанного IP-адреса или подсети. Примеры: HOST IP '192.168.0.0/16', HOST IP '2001:DB8::/32'. При использовании в эксплуатации указывайте только элементы HOST IP (IP-адреса и маски подсети), так как использование host и host_regexp может привести к дополнительной задержке.HOST ANY — Пользователь может подключиться с любого хоста. Используется по умолчанию.HOST LOCAL — Пользователь может подключиться только локально.HOST NAME 'fqdn' — Хост задается через FQDN. Например, HOST NAME 'mysite.com'.HOST REGEXP 'regexp' — Позволяет использовать регулярные выражения pcre, чтобы задать хосты. Например, HOST REGEXP '.*\.mysite\.com'.HOST LIKE 'template' — Позволяет использовать оператор LIKE для фильтрации хостов. Например, HOST LIKE '%' эквивалентен HOST ANY; HOST LIKE '%.mysite.com' разрешает подключение со всех хостов в домене mysite.com.Также, чтобы задать хост, вы можете использовать @ вместе с именем пользователя. Примеры:
CREATE USER mira@'127.0.0.1' — Эквивалентно HOST IP.CREATE USER mira@'localhost' — Эквивалентно HOST LOCAL.CREATE USER mira@'192.168.%.%' — Эквивалентно HOST LIKE.ВНИМАНИЕ:
RT.WideStore трактует конструкцию `user_name@'address'` как имя пользователя целиком. То есть технически вы можете создать несколько пользователей с одинаковыми `user_name`, но разными частями конструкции после `@`, но лучше так не делать.
Указываются пользователи или роли, которым разрешено получать привилегии от создаваемого пользователя при условии, что этому пользователю также предоставлен весь необходимый доступ с использованием GRANT OPTION. Параметры секции GRANTEES:
user — указывается пользователь, которому разрешено получать привилегии от создаваемого пользователя.role — указывается роль, которой разрешено получать привилегии от создаваемого пользователя.ANY — любому пользователю или любой роли разрешено получать привилегии от создаваемого пользователя. Используется по умолчанию.NONE — никому не разрешено получать привилегии от создаваемого пользователя.Вы можете исключить любого пользователя или роль, используя выражение EXCEPT. Например, CREATE USER user1 GRANTEES ANY EXCEPT user2. Это означает, что если user1 имеет привилегии, предоставленные с использованием GRANT OPTION, он сможет предоставить их любому, кроме user2.
Примеры:
Создать аккаунт mira, защищенный паролем qwerty:
CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty';Пользователь mira должен запустить клиентское приложение на хосте, где запущен RT.WideStore.
Создать аккаунт john, назначить на него роли, сделать данные роли ролями по умолчанию:
CREATE USER john DEFAULT ROLE role1, role2;Создать аккаунт john и установить ролями по умолчанию все его будущие роли:
CREATE USER john DEFAULT ROLE ALL;Когда роль будет назначена аккаунту john, она автоматически станет ролью по умолчанию.
Создать аккаунт john и установить ролями по умолчанию все его будущие роли, кроме role1 и role2:
CREATE USER john DEFAULT ROLE ALL EXCEPT role1, role2;Создать пользователя с аккаунтом john и разрешить ему предоставить свои привилегии пользователю с аккаунтом jack:
CREATE USER john GRANTEES jack;Создает роли. Роль — это набор привилегий. Пользователь, которому назначена роль, получает все привилегии этой роли.
Синтаксис:
CREATE ROLE [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1] [, name2 [ON CLUSTER cluster_name2] ...]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | PROFILE 'profile_name'] [,...]Одному пользователю можно назначить несколько ролей. Пользователи могут применять назначенные роли в произвольных комбинациях с помощью выражения SET ROLE. Конечный объем привилегий — это комбинация всех привилегий всех примененных ролей. Если у пользователя имеются привилегии, присвоенные его аккаунту напрямую, они также прибавляются к привилегиям, присвоенным через роли.
Роли по умолчанию применяются при входе пользователя в систему. Установить роли по умолчанию можно с помощью выражений SET DEFAULT ROLE или ALTER USER.
Для отзыва роли используется выражение REVOKE.
Для удаления роли используется выражение DROP ROLE. Удаленная роль автоматически отзывается у всех пользователей, которым была назначена.
Примеры:
CREATE ROLE accountant;GRANT SELECT ON db.* TO accountant;Такая последовательность запросов создаст роль accountant, у которой есть привилегия на чтение из базы данных accounting.
Назначить роль accountant аккаунту mira:
GRANT accountant TO mira;После назначения роли пользователь может ее применить и выполнять разрешенные ей запросы. Например:
SET ROLE accountant;SELECT * FROM db.*;Создает политики доступа к строкам, т.е. фильтры, которые определяют, какие строки пользователь может читать из таблицы.
Синтаксис:
CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name1 [ON CLUSTER cluster_name1] ON [db1.]table1
[, policy_name2 [ON CLUSTER cluster_name2] ON [db2.]table2 ...]
[AS {PERMISSIVE | RESTRICTIVE}]
[FOR SELECT] USING condition
[TO {role [,...] | ALL | ALL EXCEPT role [,...]}]Секция USING указывает условие для фильтрации строк. Пользователь может видеть строку, если это условие, вычисленное для строки, дает ненулевой результат.
В секции TO перечисляются пользователи и роли, для которых должна действовать политика. Например, CREATE ROW POLICY ... TO accountant, john@localhost.
Ключевым словом ALL обозначаются все пользователи, включая текущего. Ключевые слова ALL EXCEPT позволяют исключить пользователей из списка всех пользователей. Например, CREATE ROW POLICY ... TO ALL EXCEPT accountant, john@localhost
Примечание:
Если для таблицы не задано ни одной политики доступа к строкам, то любой пользователь может выполнить команду SELECT и получить все строки таблицы. Если определить хотя бы одну политику для таблицы, до доступ к строкам будет управляться этими политиками, причем для всех пользователей (даже для тех, для кого политики не определялись). Например, следующая политика`CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter`запретит пользователям `mira` и `peter` видеть строки с `b != 1`, и еще запретит всем остальным пользователям (например, пользователю `paul`) видеть какие-либо строки вообще из таблицы `mydb.table1`.Если это нежелательно, такое поведение можно исправить, определив дополнительную политику:
`CREATE ROW POLICY pol2 ON mydb.table1 USING 1 TO ALL EXCEPT mira, peter`.
Может быть одновременно активно более одной политики для одной и той же таблицы и одного и того же пользователя. Поэтому нам нужен способ комбинировать политики.
По умолчанию политики комбинируются с использованием логического оператора OR. Например, политики:
CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 TO peter, antonioразрешат пользователю с именем peter видеть строки, для которых будет верно b=1 или c=2.
Секция AS указывает, как политики должны комбинироваться с другими политиками. Политики могут быть или разрешительными (PERMISSIVE), или ограничительными (RESTRICTIVE). По умолчанию политики создаются разрешительными (PERMISSIVE); такие политики комбинируются с использованием логического оператора OR.
Ограничительные (RESTRICTIVE) политики комбинируются с использованием логического оператора AND.
Общая формула выглядит так:
строка_видима = (одна или больше permissive-политик дала ненулевой результат проверки условия) И
(все restrictive-политики дали ненулевой результат проверки условия)Например, политики:
CREATE ROW POLICY pol1 ON mydb.table1 USING b=1 TO mira, peter
CREATE ROW POLICY pol2 ON mydb.table1 USING c=2 AS RESTRICTIVE TO peter, antonioразрешат пользователю с именем peter видеть только те строки, для которых будет одновременно b=1 и c=2.
Секция ON CLUSTER позволяет создавать политики на кластере, см. Распределенные DDL запросы.
Примеры:
CREATE ROW POLICY filter1 ON mydb.mytable USING a<1000 TO accountant, john@localhost
CREATE ROW POLICY filter2 ON mydb.mytable USING a<1000 AND b=5 TO ALL EXCEPT mira
CREATE ROW POLICY filter3 ON mydb.mytable USING 1 TO admin
Создает квоту, которая может быть присвоена пользователю или роли.
Синтаксис:
CREATE QUOTA [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
[KEYED BY {user_name | ip_address | client_key | client_key, user_name | client_key, ip_address} | NOT KEYED]
[FOR [RANDOMIZED] INTERVAL number {second | minute | hour | day | week | month | quarter | year}
{MAX { {queries | query_selects | query_inserts | errors | result_rows | result_bytes | read_rows | read_bytes | execution_time} = number } [,...] |
NO LIMITS | TRACKING ONLY} [,...]]
[TO {role [,...] | ALL | ALL EXCEPT role [,...]}]Ключи user_name, ip_address, client_key, client_key, user_name и client_key, ip_address соответствуют полям таблицы system.quotas.
Параметры queries, query_selects, query_inserts, errors, result_rows, result_bytes, read_rows, read_bytes, execution_time соответствуют полям таблицы system.quotas_usage.
В секции ON CLUSTER можно указать кластеры, на которых создается квота, см. Распределенные DDL запросы.
Примеры:
Ограничить максимальное количество запросов для текущего пользователя — не более 123 запросов за каждые 15 месяцев:
CREATE QUOTA qA FOR INTERVAL 15 month MAX queries = 123 TO CURRENT_USER;Ограничить по умолчанию максимальное время выполнения запроса — не более полсекунды за каждые 30 минут, а также максимальное число запросов — не более 321 и максимальное число ошибок — не более 10 за каждые 5 кварталов:
CREATE QUOTA qB FOR INTERVAL 30 minute MAX execution_time = 0.5, FOR INTERVAL 5 quarter MAX queries = 321, errors = 10 TO default;Создает профили настроек, которые могут быть присвоены пользователю или роли.
Синтаксис:
CREATE SETTINGS PROFILE [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
[, name2 [ON CLUSTER cluster_name2] ...]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | INHERIT 'profile_name'] [,...]Секция ON CLUSTER позволяет создавать профили на кластере, см. Распределенные DDL запросы.
Пример:
Создать профиль настроек max_memory_usage_profile, который содержит значение и ограничения для настройки max_memory_usage. Присвоить профиль пользователю robin:
CREATE SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000 TO robinИзменение структуры таблицы.
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...В запросе указывается список из одного или более действий через запятую.
Каждое действие – операция над столбцом.
Большинство запросов ALTER изменяют настройки таблицы или данные:
Предупреждение:
Запрос ALTER поддерживается только для таблиц типа *MergeTree, а также Merge и Distributed. Запрос имеет несколько вариантов.
Следующие запросы ALTER изменяют сущности, связанные с управлением доступом на основе ролей:
Мутации – разновидность запроса ALTER, позволяющая изменять или удалять данные в таблице. В отличие от стандартных запросов ALTER TABLE … DELETE и ALTER TABLE … UPDATE, рассчитанных на точечное изменение данных, область применения мутаций – достаточно тяжёлые изменения, затрагивающие много строк в таблице. Поддержана для движков таблиц семейства MergeTree, в том числе для движков с репликацией.
Конвертировать существующие таблицы для работы с мутациями не нужно. Но после применения первой мутации формат данных таблицы становится несовместимым с предыдущими версиями и откатиться на предыдущую версию уже не получится.
На данный момент доступны команды:
ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_nameКоманда перестроит вторичный индекс name для партиции partition_name.
В одном запросе можно указать несколько команд через запятую.
Для *MergeTree-таблиц мутации выполняются, перезаписывая данные по кускам (parts). При этом атомарности нет – куски заменяются на помутированные по мере выполнения и запрос SELECT, заданный во время выполнения мутации, увидит данные как из измененных кусков, так и из кусков, которые еще не были изменены.
Мутации линейно упорядочены между собой и накладываются на каждый кусок в порядке добавления. Мутации также упорядочены со вставками - гарантируется, что данные, вставленные в таблицу до начала выполнения запроса мутации, будут изменены, а данные, вставленные после окончания запроса мутации, изменены не будут. При этом мутации никак не блокируют вставки.
Запрос завершается немедленно после добавления информации о мутации (для реплицированных таблиц - в ZooKeeper, для нереплицированных - на файловую систему). Сама мутация выполняется асинхронно, используя настройки системного профиля. Следить за ходом её выполнения можно по таблице system.mutations. Добавленные мутации будут выполняться до конца даже в случае перезапуска серверов RT.WideStore. Откатить мутацию после её добавления нельзя, но если мутация по какой-то причине не может выполниться до конца, её можно остановить с помощью запроса KILL MUTATION.
Записи о последних выполненных мутациях удаляются не сразу (количество сохраняемых мутаций определяется параметром движка таблиц finished_mutations_to_keep). Более старые записи удаляются.
Для нереплицируемых таблиц, все запросы ALTER выполняются синхронно. Для реплицируемых таблиц, запрос всего лишь добавляет инструкцию по соответствующим действиям в ZooKeeper, а сами действия осуществляются при первой возможности. Но при этом, запрос может ждать завершения выполнения этих действий на всех репликах.
Для запросов ALTER ... ATTACH|DETACH|DROP можно настроить ожидание, с помощью настройки replication_alter_partitions_sync.
Возможные значения: 0 - не ждать, 1 - ждать выполнения только у себя (по умолчанию), 2 - ждать всех.
Для запросов ALTER TABLE ... UPDATE|DELETE синхронность выполнения определяется настройкой mutations_sync.
Перегружает все Встроенные словари.
По умолчанию встроенные словари выключены.
Всегда возвращает Ok., вне зависимости от результата обновления встроенных словарей.
Перегружает все словари, которые были успешно загружены до этого.
По умолчанию включена ленивая загрузка dictionaries_lazy_load, поэтому словари не загружаются автоматически при старте, а только при первом обращении через dictGet или SELECT к ENGINE=Dictionary. После этого такие словари (LOADED) будут перегружаться командой system reload dictionaries.
Всегда возвращает Ok., вне зависимости от результата обновления словарей.
Полностью перегружает словарь dictionary_name, вне зависимости от состояния словаря (LOADED/NOT_LOADED/FAILED).
Всегда возвращает Ok., вне зависимости от результата обновления словаря.
Состояние словаря можно проверить запросом к system.dictionaries.
SELECT name, status FROM system.dictionaries;Сбрасывает внутренний DNS кеш RT.WideStore. Иногда (для старых версий RT.WideStore) необходимо использовать эту команду при изменении инфраструктуры (смене IP адреса у другого RT.WideStore сервера или сервера, используемого словарями).
Для более удобного (автоматического) управления кешем см. параметры disable_internal_dns_cache, dns_cache_update_period.
Сбрасывает кеш «засечек» (mark cache). Используется при разработке RT.WideStore и тестах производительности.
Мертвые реплики можно удалить, используя следующий синтаксис:
SYSTEM DROP REPLICA 'replica_name' FROM TABLE database.table;
SYSTEM DROP REPLICA 'replica_name' FROM DATABASE database;
SYSTEM DROP REPLICA 'replica_name';
SYSTEM DROP REPLICA 'replica_name' FROM ZKPATH '/path/to/table/in/zk';Удаляет путь реплики из ZooKeeper-а. Это полезно, когда реплика мертва и ее метаданные не могут быть удалены из ZooKeeper с помощью DROP TABLE, потому что такой таблицы больше нет. DROP REPLICA может удалить только неактивную / устаревшую реплику и не может удалить локальную реплику, используйте для этого DROP TABLE. DROP REPLICA не удаляет таблицы и не удаляет данные или метаданные с диска.
Первая команда удаляет метаданные реплики 'replica_name' для таблицы database.table.
Вторая команда удаляет метаданные реплики 'replica_name' для всех таблиц базы данных database.
Третья команда удаляет метаданные реплики 'replica_name' для всех таблиц, существующих на локальном сервере (список таблиц генерируется из локальной реплики).
Четвертая команда полезна для удаления метаданных мертвой реплики, когда все другие реплики таблицы уже были удалены ранее, поэтому необходимо явно указать ZooKeeper путь таблицы. ZooKeeper путь – это первый аргумент для ReplicatedMergeTree движка при создании таблицы.
Сбрасывает кеш не сжатых данных. Используется при разработке RT.WideStore и тестах производительности.
Для управления кешем не сжатых данных используйте следующие настройки уровня сервера uncompressed_cache_size и настройки уровня запрос/пользователь/профиль use_uncompressed_cache
Сбрасывает кеш скомпилированных выражений. Используется при разработке RT.WideStore и тестах производительности.
Компилированные выражения используются когда включена настройка уровня запрос/пользователь/профиль compile.
Записывает буферы логов в системные таблицы (например system.query_log). Позволяет не ждать 7.5 секунд при отладке.
Если буфер логов пустой, то этот запрос просто создаст системные таблицы.
Перечитывает конфигурацию настроек RT.WideStore. Используется при хранении конфигурации в zookeeeper.
Штатно завершает работу RT.WideStore (аналог service widestore-server stop / kill {$widestore-server}).
RT.WideStore может оперировать распределёнными таблицами. Когда пользователь вставляет данные в эти таблицы, RT.WideStore сначала формирует очередь из данных, которые должны быть отправлены на узлы кластера, а затем асинхронно отправляет подготовленные данные. Вы можете управлять очередью с помощью запросов STOP DISTRIBUTED SENDS, START DISTRIBUTED SENDS и FLUSH DISTRIBUTED. Также есть возможность синхронно вставлять распределенные данные с помощью настройки insert_distributed_sync.
Отключает фоновую отправку при вставке данных в распределённые таблицы.
SYSTEM STOP DISTRIBUTED SENDS [db.]<distributed_table_name>В синхронном режиме отправляет все данные на узлы кластера. Если какие-либо узлы недоступны, RT.WideStore генерирует исключение и останавливает выполнение запроса. Такой запрос можно повторять до успешного завершения, что будет означать возвращение связанности с остальными узлами кластера.
SYSTEM FLUSH DISTRIBUTED [db.]<distributed_table_name>Включает фоновую отправку при вставке данных в распределенные таблицы.
SYSTEM START DISTRIBUTED SENDS [db.]<distributed_table_name>Managing MergeTree Tables.
RT.WideStore может управлять фоновыми процессами в MergeTree таблицах.
Позволяет остановить фоновые мержи для таблиц семейства MergeTree:
SYSTEM STOP MERGES [ON VOLUME <volume_name> | [db.]merge_tree_family_table_name]
Предупреждение:
DETACH / ATTACH таблицы восстанавливает фоновые мержи для этой таблицы (даже в случае отключения фоновых мержей для всех таблиц семейства MergeTree до DETACH).
Включает фоновые мержи для таблиц семейства MergeTree:
SYSTEM START MERGES [ON VOLUME <volume_name> | [db.]merge_tree_family_table_name]Позволяет остановить фоновые процессы удаления старых данных основанные на выражениях TTL для таблиц семейства MergeTree:
Возвращает Ok. даже если указана несуществующая таблица или таблица имеет тип отличный от MergeTree. Возвращает ошибку если указана не существующая база данных:
SYSTEM STOP TTL MERGES [[db.]merge_tree_family_table_name]Запускает фоновые процессы удаления старых данных основанные на выражениях TTL для таблиц семейства MergeTree:
Возвращает Ok. даже если указана несуществующая таблица или таблица имеет тип отличный от MergeTree. Возвращает ошибку если указана не существующая база данных:
SYSTEM START TTL MERGES [[db.]merge_tree_family_table_name]Позволяет остановить фоновые процессы переноса данных основанные табличных выражениях TTL с использованием TO VOLUME или TO DISK for tables in the MergeTree family:
Возвращает Ok. даже если указана несуществующая таблица или таблица имеет тип отличный от MergeTree. Возвращает ошибку если указана не существующая база данных:
SYSTEM STOP MOVES [[db.]merge_tree_family_table_name]Запускает фоновые процессы переноса данных основанные табличных выражениях TTL с использованием TO VOLUME или TO DISK for tables in the MergeTree family:
Возвращает Ok. даже если указана несуществующая таблица или таблица имеет тип отличный от MergeTree. Возвращает ошибку если указана не существующая база данных:
SYSTEM STOP MOVES [[db.]merge_tree_family_table_name]RT.WideStore может управлять фоновыми процессами связанными c репликацией в таблицах семейства ReplicatedMergeTree.
Позволяет остановить фоновые процессы синхронизации новыми вставленными кусками данных с другими репликами в кластере для таблиц семейства ReplicatedMergeTree:
Всегда возвращает Ok. вне зависимости от типа таблицы и даже если таблица или база данных не существует:
SYSTEM STOP FETCHES [[db.]replicated_merge_tree_family_table_name]Позволяет запустить фоновые процессы синхронизации новыми вставленными кусками данных с другими репликами в кластере для таблиц семейства ReplicatedMergeTree:
Всегда возвращает Ok. вне зависимости от типа таблицы и даже если таблица или база данных не существует:
SYSTEM START FETCHES [[db.]replicated_merge_tree_family_table_name]Позволяет остановить фоновые процессы отсылки новых вставленных кусков данных другим репликам в кластере для таблиц семейства ReplicatedMergeTree:
SYSTEM STOP REPLICATED SENDS [[db.]replicated_merge_tree_family_table_name]Позволяет запустить фоновые процессы отсылки новых вставленных кусков данных другим репликам в кластере для таблиц семейства ReplicatedMergeTree:
SYSTEM START REPLICATED SENDS [[db.]replicated_merge_tree_family_table_name]Останавливает фоновые процессы разбора заданий из очереди репликации, которая хранится в Zookeeper для таблиц семейства ReplicatedMergeTree. Возможные типы заданий - merges, fetches, mutation, DDL запросы с ON CLUSTER:
SYSTEM STOP REPLICATION QUEUES [[db.]replicated_merge_tree_family_table_name]Запускает фоновые процессы разбора заданий из очереди репликации, которая хранится в Zookeeper для таблиц семейства ReplicatedMergeTree. Возможные типы заданий - merges, fetches, mutation, DDL запросы с ON CLUSTER:
SYSTEM START REPLICATION QUEUES [[db.]replicated_merge_tree_family_table_name]Ждет, когда таблица семейства ReplicatedMergeTree будет синхронизирована с другими репликами в кластере, будет работать до достижения receive_timeout, если синхронизация для таблицы отключена в настоящий момент времени:
SYSTEM SYNC REPLICA [db.]replicated_merge_tree_family_table_nameРеинициализация состояния Zookeeper сессий для таблицы семейства ReplicatedMergeTree, сравнивает текущее состояние с тем что хранится в Zookeeper как источник правды и добавляет задачи Zookeeper очередь если необходимо.
Инициализация очереди репликации на основе данных ZooKeeper, происходит так же как при attach table. На короткое время таблица станет недоступной для любых операций:
SYSTEM RESTART REPLICA [db.]replicated_merge_tree_family_table_nameРеинициализация состояния Zookeeper сессий для всех ReplicatedMergeTree таблиц, сравнивает текущее состояние с тем что хранится в Zookeeper как источник правды и добавляет задачи Zookeeper очередь если необходимо.
SHOW CREATE [TEMPORARY] [TABLE|DICTIONARY] [db.]table [INTO OUTFILE filename] [FORMAT format]Возвращает один столбец типа String с именем statement, содержащий одно значение – запрос CREATE TABLE, с помощью которого был создан указанный объект.
Выводит список всех баз данных:
SHOW DATABASES [LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE filename] [FORMAT format]Этот запрос идентичен запросу:
SELECT name FROM system.databases [WHERE name LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE filename] [FORMAT format]Примеры:
Получение списка баз данных, имена которых содержат последовательность символов 'de':
SHOW DATABASES LIKE '%de%'Результат:
┌─name────┐
│ default │
└──────────┘Получение списка баз данных, имена которых содержат последовательность символов 'de' независимо от регистра:
SHOW DATABASES ILIKE '%DE%'Результат:
┌─name────┐
│ default │
└──────────┘Получение списка баз данных, имена которых не содержат последовательность символов 'de':
SHOW DATABASES NOT LIKE '%de%'Результат:
┌─name─────────────────────────────┐
│ _temporary_and_external_tables │
│ system │
│ test │
│ tutorial │
└───────────────────────────────────┘Получение первых двух строк из списка имен баз данных:
SHOW DATABASES LIMIT 2Результат:
┌─name─────────────────────────────┐
│ _temporary_and_external_tables │
│ default │
└───────────────────────────────────┘Смотрите также:
SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]Выводит содержимое таблицы system.processes, которая содержит список запросов, выполняющихся в данный момент времени, кроме самих запросов SHOW PROCESSLIST.
Запрос SELECT * FROM system.processes возвращает данные обо всех текущих запросах.
Полезный совет (выполните в консоли):
$ watch -n1 "widestore-client --query='SHOW PROCESSLIST'"Выводит список таблиц:
SHOW [TEMPORARY] TABLES [{FROM | IN} <db>] [LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]Если условие FROM не указано, запрос возвращает список таблиц из текущей базы данных.
Этот запрос идентичен запросу:
SELECT name FROM system.tables [WHERE name LIKE | ILIKE | NOT LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]Примеры:
Получение списка таблиц, имена которых содержат последовательность символов 'user':
SHOW TABLES FROM system LIKE '%user%'Результат:
┌─name──────────────┐
│ user_directories │
│ users │
└────────────────────┘Получение списка таблиц, имена которых содержат последовательность символов 'user' без учета регистра:
SHOW TABLES FROM system ILIKE '%USER%'Результат:
┌─name──────────────┐
│ user_directories │
│ users │
└────────────────────┘Получение списка таблиц, имена которых не содержат символ 's':
SHOW TABLES FROM system NOT LIKE '%s%'Результат:
┌─name─────────┐
│ metric_log │
│ metric_log_0 │
│ metric_log_1 │
└───────────────┘Получение первых двух строк из списка таблиц:
SHOW TABLES FROM system LIMIT 2Результат:
┌─name─────────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_metric_log │
└───────────────────────────────────┘Смотрите также:
Выводит список внешних словарей:
SHOW DICTIONARIES [FROM <db>] [LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]Если секция FROM не указана, запрос возвращает список словарей из текущей базы данных.
Аналогичный результат можно получить следующим запросом:
SELECT name FROM system.dictionaries WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]Пример:
Запрос выводит первые две стоки из списка таблиц в базе данных system, имена которых содержат reg:
SHOW DICTIONARIES FROM db LIKE '%reg%' LIMIT 2
┌─name──────────┐
│ regions │
│ region_names │
└───────────────┘Выводит привилегии пользователя.
Синтаксис:
SHOW GRANTS [FOR user]Если пользователь не задан, запрос возвращает привилегии текущего пользователя.
Выводит параметры, использованные при создании пользователя.
SHOW CREATE USER не возвращает пароль пользователя.
Синтаксис:
SHOW CREATE USER [name1 [, name2 ...] | CURRENT_USER]Выводит параметры, использованные при создании роли.
Синтаксис:
SHOW CREATE ROLE name1 [, name2 ...]Выводит параметры, использованные при создании политики доступа к строкам.
Синтаксис:
SHOW CREATE [ROW] POLICY name ON [database1.]table1 [, [database2.]table2 ...]Выводит параметры, использованные при создании квоты.
Синтаксис:
SHOW CREATE QUOTA [name1 [, name2 ...] | CURRENT]Выводит параметры, использованные при создании профиля настроек.
Синтаксис:
SHOW CREATE [SETTINGS] PROFILE name1 [, name2 ...]Выводит список пользовательских аккаунтов. Для просмотра параметров пользовательских аккаунтов, см. системную таблицу system.users.
Синтаксис:
SHOW USERSВыводит список ролей. Для просмотра параметров ролей, см. системные таблицы system.roles и system.role-grants.
Синтаксис:
SHOW [CURRENT|ENABLED] ROLESВыводит список профилей настроек. Для просмотра других параметров профилей настроек, см. системную таблицу settings_profiles.
Синтаксис:
SHOW [SETTINGS] PROFILESВыводит список политик доступа к строкам для указанной таблицы. Для просмотра других параметров, см. системную таблицу system.row_policies.
Синтаксис:
SHOW [ROW] POLICIES [ON [db.]table]Выводит список квот. Для просмотра параметров квот, см. системную таблицу system.quotas.
Синтаксис:
SHOW QUOTASВыводит потребление квоты для всех пользователей или только для текущего пользователя. Для просмотра других параметров, см. системные таблицы system.quotas_usage и system.quota_usage.
Синтаксис:
SHOW [CURRENT] QUOTAВыводит список всех пользователей, ролей, профилей и пр., а также все привилегии.
Синтаксис:
SHOW ACCESSОтозвать привилегию можно с помощью выражения REVOKE. Чтобы вывести список присвоенных привилегий, воспользуйтесь выражением SHOW GRANTS.
GRANT [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON
{db.table|db.*|*.*|table|*} TO {user | role | CURRENT_USER} [,...] [WITH GRANT OPTION]
WITH GRANT OPTION разрешает пользователю или роли выполнять запрос GRANT. Пользователь может выдавать только те привилегии, которые есть у него, той же или меньшей области действий.
GRANT [ON CLUSTER cluster_name] role [,...] TO {user | another_role | CURRENT_USER} [,...] [WITH ADMIN OPTION]
WITH ADMIN OPTION присваивает привилегию ADMIN OPTION пользователю или роли.
Для использования GRANT пользователь должен иметь привилегию GRANT OPTION. Пользователь может выдавать привилегии только внутри области действий, назначенных ему самому привилегий.
Например, администратор выдал привилегию пользователю john:
GRANT SELECT(x,y) ON db.table TO john WITH GRANT OPTIONЭто означает, что пользователю john разрешено выполнять:
john не может выполнить SELECT z FROM db.table или SELECT * FROM db.table. После обработки данных запросов RT.WideStore ничего не вернет – даже x или y. Единственное исключение – если таблица содержит только столбцы x и y. В таком случае RT.WideStore вернет все данные.
Также у john есть привилегия GRANT OPTION. john может выдать другим пользователям привилегии той же или меньшей области действий из тех, которые есть у него.
При присвоении привилегий допускается использовать астериск (*) вместо имени таблицы или базы данных. Например, запрос GRANT SELECT ON db.* TO john позволит пользователю john выполнять SELECT над всеми таблицами в базе данных db. Также вы можете опускать имя базы данных. В таком случае привилегии позволят совершать операции над текущей базой данных. Например, запрос GRANT SELECT ON * TO john выдаст привилегию на выполнение SELECT над всеми таблицами в текущей базе данных, GRANT SELECT ON mytable TO john – только над таблицей mytable в текущей базе данных.
Доступ к базе данных system разрешен всегда (данная база данных используется при обработке запросов).
Вы можете присвоить несколько привилегий нескольким пользователям в одном запросе. Запрос GRANT SELECT, INSERT ON *.* TO john, robin позволит пользователям john и robin выполнять INSERT и SELECT над всеми таблицами всех баз данных на сервере.
Привилегия – это разрешение на выполнение определенного типа запросов.
Привилегии имеют иерархическую структуру. Набор разрешенных запросов зависит от области действия привилегии.
Иерархия привилегий:
Примеры того, как трактуется данная иерархия:
Привилегии применяются на разных уровнях. Уровень определяет синтаксис присваивания привилегии.
Уровни (от низшего к высшему):
Примеры допустимого синтаксиса:
Примеры недопустимого синтаксиса:
Специальная привилегия ALL присваивает все привилегии пользователю или роли.
По умолчанию пользователь или роль не имеют привилегий.
Отсутствие привилегий у пользователя или роли отображается как привилегия NONE.
Выполнение некоторых запросов требует определенного набора привилегий. Например, чтобы выполнить запрос RENAME, нужны следующие привилегии: SELECT, CREATE TABLE, INSERT и DROP TABLE.
SELECT
Разрешает выполнять запросы SELECT.
Уровень: COLUMN.
Описание:
Пользователь с данной привилегией может выполнять запросы SELECT над определенными столбцами из определенной таблицы и базы данных. При включении в запрос других столбцов запрос ничего не вернет.
Рассмотрим следующую привилегию:
GRANT SELECT(x,y) ON db.table TO johnДанная привилегия позволяет пользователю john выполнять выборку данных из столбцов x и/или y в db.table, например, SELECT x FROM db.table. john не может выполнить SELECT z FROM db.table или SELECT * FROM db.table. После обработки данных запросов RT.WideStore ничего не вернет – даже x или y. Единственное исключение – если таблица содержит только столбцы x и y. В таком случае RT.WideStore вернет все данные.
Разрешает выполнять запросы INSERT.
Уровень: COLUMN.
Описание:
Пользователь с данной привилегией может выполнять запросы INSERT над определенными столбцами из определенной таблицы и базы данных. При включении в запрос других столбцов запрос не добавит никаких данных.
Пример:
GRANT INSERT(x,y) ON db.table TO johnПрисвоенная привилегия позволит пользователю john вставить данные в столбцы x и/или y в db.table.
Разрешает выполнять запросы ALTER в соответствии со следующей иерархией привилегий:
Примеры того, как трактуется данная иерархия:
Дополнительно:
Разрешает выполнять DDL-запросы CREATE и ATTACH в соответствии со следующей иерархией привилегий:
Дополнительно:
Разрешает выполнять запросы DROP и DETACH в соответствии со следующей иерархией привилегий:
Разрешает выполнять запросы TRUNCATE.
Уровень: TABLE.
OPTIMIZE
Разрешает выполнять запросы OPTIMIZE TABLE.
Уровень: TABLE.
Разрешает выполнять запросы SHOW, DESCRIBE, USE и EXISTS в соответствии со следующей иерархией привилегий:
Дополнительно:
У пользователя есть привилегия SHOW, если ему присвоена любая другая привилегия по отношению к определенной таблице, словарю или базе данных.
Разрешает выполнять запросы KILL в соответствии со следующей иерархией привилегий:
Уровень: GLOBAL.
Дополнительно:
KILL QUERY позволяет пользователю останавливать запросы других пользователей.
Разрешает пользователю выполнять запросы на управление пользователями, ролями и политиками доступа к строкам:
Привилегия ROLE ADMIN разрешает пользователю назначать и отзывать любые роли, включая те, которые не назначены пользователю с опцией администратора.
Разрешает выполнять запросы SYSTEM в соответствии со следующей иерархией привилегий:
Привилегия SYSTEM RELOAD EMBEDDED DICTIONARIES имплицитно присваивается привилегией SYSTEM RELOAD DICTIONARY ON *.*.
Разрешает использовать функции интроспекции:
Разрешает использовать внешние источники данных. Применяется к движкам таблиц и табличным функциям:
Привилегия SOURCES разрешает использование всех источников. Также вы можете присвоить привилегию для каждого источника отдельно. Для использования источников необходимы дополнительные привилегии.
Примеры:
Разрешает вызывать функции dictGet, dictHas, dictGetHierarchy, dictIsIn.
Уровень: DICTIONARY.
Примеры:
Присваивает пользователю или роли все привилегии на объект с регулируемым доступом.
Не присваивает никаких привилегий.
Привилегия ADMIN OPTION разрешает пользователю назначать свои роли другому пользователю.
Выводит план выполнения запроса.
Синтаксис:
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]Пример:
EXPLAIN SELECT sum(number) FROM numbers(10)
UNION ALL
SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;
Union
Expression (Projection)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)
Expression (Projection)
MergingSorted (Merge sorted streams for ORDER BY)
MergeSorting (Merge sorted blocks for ORDER BY)
PartialSorting (Sort each block for ORDER BY)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)AST — абстрактное синтаксическое дерево.SYNTAX — текст запроса после оптимизации на уровне AST.PLAN — план выполнения запроса.PIPELINE — конвейер выполнения запроса.Дамп AST запроса. Поддерживает все типы запросов, не только SELECT.
Примеры:
EXPLAIN AST SELECT 1;
SelectWithUnionQuery (children 1)
ExpressionList (children 1)
SelectQuery (children 1)
ExpressionList (children 1)
Literal UInt64_1
EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();
explain
AlterQuery t1 (children 1)
ExpressionList (children 1)
AlterCommand 27 (children 1)
Function equals (children 1)
ExpressionList (children 2)
Identifier date
Function today (children 1)
ExpressionListВозвращает текст запроса после применения синтаксических оптимизаций.
Пример:
EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;
SELECT
`--a.number` AS `a.number`,
`--b.number` AS `b.number`,
number AS `c.number`
FROM
(
SELECT
number AS `--a.number`,
b.number AS `--b.number`
FROM system.numbers AS a
CROSS JOIN system.numbers AS b
)
AS `--.s`
CROSS JOIN system.numbers AS cДамп шагов выполнения запроса.
Настройки:
header — выводит выходной заголовок для шага. По умолчанию: 0.description — выводит описание шага. По умолчанию: 1.indexes — показывает используемые индексы, количество отфильтрованных кусков и гранул для каждого примененного индекса. По умолчанию: 0. Поддерживается для таблиц семейства MergeTree.actions — выводит подробную информацию о действиях, выполняемых на данном шаге. По умолчанию: 0.json — выводит шаги выполнения запроса в виде строки в формате JSON. По умолчанию: 0. Чтобы избежать ненужного экранирования, рекомендуется использовать формат TSVRaw.Пример:
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
Union
Expression (Projection)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)
Примечание:
Оценка стоимости выполнения шага и запроса не поддерживается.
При json = 1 шаги выполнения запроса выводятся в формате JSON. Каждый узел — это словарь, в котором всегда есть ключи Node Type и Plans. Node Type — это строка с именем шага. Plans — это массив с описаниями дочерних шагов. Другие дополнительные ключи могут быть добавлены в зависимости от типа узла и настроек.
Пример:
EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;
[
{
"Plan": {
"Node Type": "Union",
"Plans": [
{
"Node Type": "Expression",
"Plans": [
{
"Node Type": "SettingQuotaAndLimits",
"Plans": [
{
"Node Type": "ReadFromStorage"
}
]
}
]
},
{
"Node Type": "Expression",
"Plans": [
{
"Node Type": "SettingQuotaAndLimits",
"Plans": [
{
"Node Type": "ReadFromStorage"
}
]
}
]
}
]
}
}
]При description = 1 к шагу добавляется ключ Description:
{
"Node Type": "ReadFromStorage",
"Description": "SystemOne"
}При header = 1 к шагу добавляется ключ Header в виде массива столбцов.
Пример:
EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;
[
{
"Plan": {
"Node Type": "Expression",
"Header": [
{
"Name": "1",
"Type": "UInt8"
},
{
"Name": "plus(2, dummy)",
"Type": "UInt16"
}
],
"Plans": [
{
"Node Type": "SettingQuotaAndLimits",
"Header": [
{
"Name": "dummy",
"Type": "UInt8"
}
],
"Plans": [
{
"Node Type": "ReadFromStorage",
"Header": [
{
"Name": "dummy",
"Type": "UInt8"
}
]
}
]
}
]
}
}
]При indexes = 1 добавляется ключ Indexes. Он содержит массив используемых индексов. Каждый индекс описывается как строка в формате JSON с ключом Type (MinMax, Partition, PrimaryKey или Skip) и дополнительные ключи:
Name — имя индекса (на данный момент используется только для индекса Skip).Keys — массив столбцов, используемых индексом.Condition — строка с используемым условием.Description — индекс (на данный момент используется только для индекса Skip).Parts — количество кусков до/после применения индекса.Granules — количество гранул до/после применения индекса.Пример:
"Node Type": "ReadFromMergeTree",
"Indexes": [
{
"Type": "MinMax",
"Keys": ["y"],
"Condition": "(y in [1, +inf))",
"Parts": 5/4,
"Granules": 12/11 },
{
"Type": "Partition",
"Keys": ["y", "bitAnd(z, 3)"],
"Condition": "and((bitAnd(z, 3) not in [1, 1]), and((y in [1, +inf)), (bitAnd(z, 3) not in [1, 1])))",
"Parts": 4/3,
"Granules": 11/10 },
{
"Type": "PrimaryKey",
"Keys": ["x", "y"],
"Condition": "and((x in [11, +inf)), (y in [1, +inf)))",
"Parts": 3/2,
"Granules": 10/6 },
{
"Type": "Skip",
"Name": "t_minmax",
"Description": "minmax GRANULARITY 2",
"Parts": 2/1,
"Granules": 6/2
},
{
"Type": "Skip",
"Name": "t_set",
"Description": "set GRANULARITY 2",
"": 1/1,
"Granules": 2/1
}
]При actions = 1 добавляются ключи, зависящие от типа шага.
Пример:
EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;
[
{
"Plan": {
"Node Type": "Expression",
"Expression": {
"Inputs": [],
"Actions": [
{
"Node Type": "Column",
"Result Type": "UInt8",
"Result Type": "Column",
"Column": "Const(UInt8)",
"Arguments": [],
"Removed Arguments": [],
"Result": 0
}
],
"Outputs": [
{
"Name": "1",
"Type": "UInt8"
}
],
"Positions": [0],
"Project Input": true
},
"Plans": [
{
"Node Type": "SettingQuotaAndLimits",
"Plans": [
{
"Node Type": "ReadFromStorage"
}
]
}
]
}
}
]Настройки:
header — выводит заголовок для каждого выходного порта. По умолчанию: 0.graph — выводит граф, описанный на языке DOT. По умолчанию: 0.compact — выводит граф в компактном режиме, если включена настройка graph. По умолчанию: 1.Пример:
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;
(Union)
(Expression)
ExpressionTransform
(Expression)
ExpressionTransform
(Aggregating)
Resize 2 → 1
AggregatingTransform × 2
(Expression)
ExpressionTransform × 2
(SettingQuotaAndLimits)
(ReadFromStorage)
NumbersMt × 2 0 → 1Отображает оценки числа строк, засечек и кусков, которые будут прочитаны при выполнении запроса. Применяется для таблиц семейства MergeTree.
Пример:
Создадим таблицу:
CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;
INSERT INTO ttt SELECT number FROM numbers(128);
OPTIMIZE TABLE ttt;Запрос:
EXPLAIN ESTIMATE SELECT * FROM ttt;Результат:
┌─database─┬─table─┬─parts─┬─rows──┬─marks─┐
│ default │ ttt │ 1 │ 128 │ 8 │
└───────────┴───────┴────────┴───────┴───────┘Отзывает привилегии у пользователей или ролей.
Синтаксис:
Отзыв привилегий у пользователей:
REVOKE [ON CLUSTER cluster_name] privilege[(column_name [,...])] [,...] ON {db.table|db.*|*.*|table|*} FROM {user | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user | CURRENT_USER} [,...]Отзыв ролей у пользователей:
REVOKE [ON CLUSTER cluster_name] [ADMIN OPTION FOR] role [,...] FROM {user | role | CURRENT_USER} [,...] | ALL | ALL EXCEPT {user_name | role_name | CURRENT_USER} [,...]Описание:
Для отзыва привилегий можно использовать привилегию более широкой области действия. Например, если у пользователя есть привилегия SELECT (x,y), администратор может отозвать ее с помощью одного из запросов: REVOKE SELECT(x,y) ..., REVOKE SELECT * ... или даже REVOKE ALL PRIVILEGES ....
Вы можете отозвать часть привилегии. Например, если у пользователя есть привилегия SELECT *.*, вы можете отозвать привилегию на чтение данных из какой-то таблицы или базы данных.
Примеры:
Присвоить пользователю john привилегию на SELECT из всех баз данных кроме accounts:
GRANT SELECT ON *.* TO john;
REVOKE SELECT ON accounts.* FROM john;Присвоить пользователю mira привилегию на SELECT из всех столбцов таблицы accounts.staff кроме столбца wage:
GRANT SELECT ON accounts.staff TO mira;
REVOKE SELECT(wage) ON accounts.staff FROM mira;Выполняет подключение таблицы или словаря, например, при перемещении базы данных на другой сервер.
Синтаксис:
ATTACH TABLE|DICTIONARY [IF NOT EXISTS] [db.]name [ON CLUSTER cluster] ...Запрос не создаёт данные на диске, а предполагает, что данные уже лежат в соответствующих местах, и всего лишь добавляет информацию о таблице или словаре на сервер. После выполнения запроса ATTACH сервер будет знать о существовании таблицы или словаря.
Если таблица перед этим была отключена при помощи (DETACH), т.е. её структура известна, можно использовать сокращенную форму записи без определения структуры.
Синтаксис:
ATTACH TABLE [IF NOT EXISTS] [db.]name [ON CLUSTER cluster]Этот запрос используется при старте сервера. Сервер хранит метаданные таблиц в виде файлов с запросами ATTACH, которые он просто исполняет при запуске (за исключением некоторых системных таблиц, которые явно создаются на сервере).
Если таблица была отключена перманентно, она не будет подключена обратно во время старта сервера, так что нужно явно использовать запрос ATTACH, чтобы подключить ее.
Запрос создает новую таблицу с указанной структурой и присоединяет табличные данные из соответствующего каталога в user_files.
Синтаксис:
ATTACH TABLE name FROM 'path/to/data/' (col1 Type1, ...)Пример:
Запрос:
DROP TABLE IF EXISTS test;
INSERT INTO TABLE FUNCTION file('01188_attach/test/data.TSV', 'TSV', 's String, n UInt8') VALUES ('test', 42);
ATTACH TABLE test FROM '01188_attach/test' (s String, n UInt8) ENGINE = File(TSV);
SELECT * FROM test;Результат:
┌─s────┬──n─┐
│ test │ 42 │
└──────┴─────┘Этот запрос создает новую таблицу с указанной структурой и присоединяет данные из таблицы с указанным UUID. Запрос поддерживается только движком баз данных Atomic.
Синтаксис
ATTACH TABLE name UUID '<uuid>' (col1 Type1, ...)Присоединяет ранее отключенный словарь.
Синтаксис
ATTACH DICTIONARY [IF NOT EXISTS] [db.]name [ON CLUSTER cluster]Проверяет таблицу на повреждение данных.
CHECK TABLE [db.]nameЗапрос CHECK TABLE сравнивает текущие размеры файлов (в которых хранятся данные из колонок) с ожидаемыми значениями. Если значения не совпадают, данные в таблице считаются поврежденными. Искажение возможно, например, из-за сбоя при записи данных.
Ответ содержит колонку result, содержащую одну строку с типом Boolean. Допустимые значения:
Запрос CHECK TABLE поддерживает следующие движки таблиц:
При попытке выполнить запрос с таблицами с другими табличными движками, RT.WideStore генерирует исключение.
В движках *Log не предусмотрено автоматическое восстановление данных после сбоя. Используйте запрос CHECK TABLE, чтобы своевременно выявлять повреждение данных.
Для таблиц семейства MergeTree если check_query_single_value_result = 0, запрос CHECK TABLE возвращает статус каждого куска данных таблицы на локальном сервере.
SET check_query_single_value_result = 0;
CHECK TABLE test_table;
┌─part_path─┬─is_passed─┬─message─┐
│ all_1_4_1 │ 1 │ │
│ all_1_4_2 │ 1 │ │
└────────────┴────────────┴──────────┘Если check_query_single_value_result = 1, запрос CHECK TABLE возвращает статус таблицы в целом.
SET check_query_single_value_result = 1;CHECK TABLE test_table;
┌─result─┐
│ 1 │
└─────────┘В этом случае можно скопировать оставшиеся неповрежденные данные в другую таблицу. Для этого:
CREATE TABLE <new_table_name> AS <damaged_table_name>.SET max_threads = 1.INSERT INTO <new_table_name> SELECT * FROM <damaged_table_name>. В результате неповрежденные данные будут скопированы в другую таблицу. Обратите внимание, будут скопированы только те данные, которые следуют до поврежденного участка.clickhouse-client, чтобы вернуть предыдущее значение параметра max_threads.Возвращает описание столбцов таблицы.
Синтаксис:
DESC|DESCRIBE TABLE [db.]table [INTO OUTFILE filename] [FORMAT format]Запрос DESCRIBE для каждого столбца таблицы возвращает строку со следующими значениями типа String:
Каждый столбец Nested структур описывается отдельно. Перед его именем ставится имя родительского столбца с точкой. Чтобы отобразить внутренние подстолбцы других типов данных, нужно включить настройку describe_include_subcolumns.
Пример:
Запрос:
CREATE TABLE describe_example (
id UInt64, text String DEFAULT 'unknown' CODEC(ZSTD),
user Tuple (name String, age UInt8)
) ENGINE = MergeTree() ORDER BY id;
DESCRIBE TABLE describe_example;
DESCRIBE TABLE describe_example SETTINGS describe_include_subcolumns=1;Результат:
┌─name─┬─type────────────────────────────┬─default_type─┬─default_expression──┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ UInt64 │ │ │ │ │ │
│ text │ String │ DEFAULT │ 'unknown' │ │ ZSTD(1) │ │
│ user │ Tuple(name String, age UInt8) │ │ │ │ │ │
└──────┴──────────────────────────────────┴────────────────┴─────────────────────┴──────────┴────────────────────┴─────────────────┘Второй запрос дополнительно выводит информацию о подстолбцах:
┌─name──────┬─type─────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression──┬─is_subcolumn─┐
│ id │ UInt64 │ │ │ │ │ │ 0 │
│ text │ String │ DEFAULT │ 'unknown' │ │ ZSTD(1) │ │ 0 │
│ user │ Tuple(name String, age UInt8) │ │ │ │ │ │ 0 │
│ user.name │ String │ │ │ │ │ │ 1 │
│ user.age │ UInt8 │ │ │ │ │ │ 1 │
└────────────┴──────────────────────────────────┴───────────────┴──────────────────────┴──────────┴───────────────────┴──────────────────┴───────────────┘См. также:
Удаляет из сервера информацию о таблице name. Сервер перестаёт знать о существовании таблицы.
Синтаксис:
DETACH TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]Но ни данные, ни метаданные таблицы не удаляются. При следующем запуске сервера, сервер прочитает метаданные и снова узнает о таблице.
Также, «отцепленную» таблицу можно прицепить заново запросом ATTACH (за исключением системных таблиц, для которых метаданные не хранятся).
Запроса DETACH DATABASE нет.
Удаляет существующий объект. Если указано IF EXISTS - не выдавать ошибку, если объекта не существует.
Удаляет все таблицы в базе данных db, затем удаляет саму базу данных db.
Синтаксис:
DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]Удаляет таблицу.
Синтаксис:
DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]Удаляет словарь.
Синтаксис:
DROP DICTIONARY [IF EXISTS] [db.]nameУдаляет пользователя.
Синтаксис:
DROP USER [IF EXISTS] name [,...] [ON CLUSTER cluster_name]Удаляет роль. При удалении роль отзывается у всех объектов системы доступа, которым она присвоена.
Синтаксис:
DROP ROLE [IF EXISTS] name [,...] [ON CLUSTER cluster_name]Удаляет политику доступа к строкам. При удалении политика отзывается у всех объектов системы доступа, которым она присвоена.
Синтаксис:
DROP [ROW] POLICY [IF EXISTS] name [,...] ON [database.]table [,...] [ON CLUSTER cluster_name]Удаляет квоту. При удалении квота отзывается у всех объектов системы доступа, которым она присвоена.
Синтаксис:
DROP QUOTA [IF EXISTS] name [,...] [ON CLUSTER cluster_name]Удаляет профиль настроек. При удалении профиль отзывается у всех объектов системы доступа, которым он присвоен.
Синтаксис:
DROP [SETTINGS] PROFILE [IF EXISTS] name [,...] [ON CLUSTER cluster_name]Удаляет представление. Представления могут быть удалены и командой DROP TABLE, но команда DROP VIEW проверяет, что [db.]name является представлением.
Синтаксис:
DROP VIEW [IF EXISTS] [db.]name [ON CLUSTER cluster]Удаляет пользовательскую функцию, созданную с помощью CREATE FUNCTION. Удалить системные функции нельзя.
Синтаксис:
DROP FUNCTION [IF EXISTS] function_nameПример:
CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b;DROP FUNCTION linear_equation;EXISTS [TEMPORARY] TABLE [db.]name [INTO OUTFILE filename] [FORMAT format]Возвращает один столбец типа UInt8, содержащий одно значение - 0, если таблицы или БД не существует и 1, если таблица в указанной БД существует.
Существует два вида операторов KILL: KILL QUERY и KILL MUTATION.
KILL QUERY [ON CLUSTER cluster]
WHERE <where expression to SELECT FROM system.processes query>
[SYNC|ASYNC|TEST]
[FORMAT format]Пытается принудительно остановить исполняющиеся в данный момент запросы.
Запросы для принудительной остановки выбираются из таблицы system.processes с помощью условия, указанного в секции WHERE запроса KILL.
Примеры:
-- Принудительно останавливает все запросы с указанным query_id:
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90'
-- Синхронно останавливает все запросы пользователя 'username':
KILL QUERY WHERE user='username' SYNCReadonly-пользователи могут останавливать только свои запросы.
По умолчанию используется асинхронный вариант запроса (ASYNC), который не дожидается подтверждения остановки запросов.
Синхронный вариант (SYNC) ожидает остановки всех запросов и построчно выводит информацию о процессах по ходу их остановки.
Ответ содержит колонку kill_status, которая может принимать следующие значения:
Тестовый вариант запроса (TEST) только проверяет права пользователя и выводит список запросов для остановки.
KILL MUTATION [ON CLUSTER cluster]
WHERE <where expression to SELECT FROM system.mutations query>
[TEST]
[FORMAT format]Пытается остановить выполняющиеся в данные момент мутации. Мутации для остановки выбираются из таблицы system.mutations с помощью условия, указанного в секции WHERE запроса KILL.
Тестовый вариант запроса (TEST) только проверяет права пользователя и выводит список запросов для остановки.
Примеры:
-- Останавливает все мутации одной таблицы:
KILL MUTATION WHERE database = 'default' AND table = 'table'
-- Останавливает конкретную мутацию:
KILL MUTATION WHERE database = 'default' AND table = 'table' AND mutation_id = 'mutation_3.txt'Запрос полезен в случаях, когда мутация не может выполниться до конца (например, если функция в запросе мутации бросает исключение на данных таблицы).
Данные, уже изменённые мутацией, остаются в таблице (отката на старую версию данных не происходит).
OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE]Запрос пытается запустить внеплановый мёрж кусков данных для таблиц семейства MergeTree. Другие движки таблиц не поддерживаются.
Если OPTIMIZE применяется к таблицам семейства ReplicatedMergeTree, RT.WideStore создаёт задачу на мёрж и ожидает её исполнения на всех узлах (если активирована настройка replication_alter_partitions_sync).
Внимание:
Запрос OPTIMIZE не может устранить причину появления ошибки «Too many parts».
Переименовывает одну или несколько таблиц.
RENAME TABLE [db11.]name11 TO [db12.]name12, [db21.]name21 TO [db22.]name22, ... [ON CLUSTER cluster]Переименовывание таблицы является лёгкой операцией. Если вы указали после TO другую базу данных, то таблица будет перенесена в эту базу данных. При этом, директории с базами данных должны быть расположены в одной файловой системе (иначе возвращается ошибка). В случае переименования нескольких таблиц в одном запросе – это неатомарная операция, может выполнится частично, запросы в других сессиях могут получить ошибку Table ... doesn't exist....
Атомарно обменивает имена двух таблиц или словарей. Это действие также можно выполнить с помощью запроса RENAME, используя третье временное имя, но в таком случае действие неатомарно.
Примечание:
Запрос `EXCHANGE` поддерживается только движком баз данных [Atomic](/docs/ru/engines/database-engines/atomic).
Синтаксис:
EXCHANGE TABLES|DICTIONARIES [db0.]name_A AND [db1.]name_B [ON CLUSTER cluster]Обменивает имена двух таблиц.
Синтаксис:
EXCHANGE TABLES [db0.]table_A AND [db1.]table_B [ON CLUSTER cluster]Обменивает имена двух словарей.
Синтаксис:
EXCHANGE DICTIONARIES [db0.]dict_A AND [db1.]dict_B [ON CLUSTER cluster]Смотрите также:
SET param = valueУстанавливает значение value для настройки param в текущей сессии. Конфигурационные параметры сервера нельзя изменить подобным образом.
Можно одним запросом установить все настройки из заданного профиля настроек.
SET profile = 'profile-name-from-the-settings-file'Подробности смотрите в разделе Настройки.
Активирует роли для текущего пользователя.
Синтаксис:
SET ROLE {DEFAULT | NONE | role [,...] | ALL | ALL EXCEPT role [,...]}Устанавливает роли по умолчанию для пользователя.
Роли по умолчанию активируются автоматически при входе пользователя. Ролями по умолчанию могут быть установлены только ранее назначенные роли. Если роль не назначена пользователю, RT.WideStore выбрасывает исключение.
Синтаксис:
SET DEFAULT ROLE {NONE | role [,...] | ALL | ALL EXCEPT role [,...]} TO {user|CURRENT_USER} [,...]Примеры:
Установить несколько ролей по умолчанию для пользователя:
SET DEFAULT ROLE role1, role2, ... TO userУстановить ролями по умолчанию все назначенные пользователю роли:
SET DEFAULT ROLE ALL TO userУдалить роли по умолчанию для пользователя:
SET DEFAULT ROLE NONE TO userУстановить ролями по умолчанию все назначенные пользователю роли за исключением указанных:
SET DEFAULT ROLE ALL EXCEPT role1, role2 TO userTRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]Удаляет все данные из таблицы. Если условие IF EXISTS не указано, запрос вернет ошибку, если таблицы не существует.
Запрос TRUNCATE не поддерживается для следующих движков: View, File, URL и Null.
USE dbПозволяет установить текущую базу данных для сессии.
Текущая база данных используется для поиска таблиц, если база данных не указана в запросе явно через точку перед именем таблицы.
При использовании HTTP протокола запрос не может быть выполнен, так как понятия сессии не существует.
Важно:
Это экспериментальная функция. Она может повлечь потерю совместимости в будущих версиях.Чтобы использовать `LIVE VIEW` и запросы `WATCH`, включите настройку `set allow_experimental_live_view = 1`.
Синтаксис:
WATCH [db.]live_view [EVENTS] [LIMIT n] [FORMAT format]Запрос WATCH постоянно возвращает содержимое LIVE-представления. Если параметр LIMIT не был задан, запрос WATCH будет непрерывно обновлять содержимое LIVE-представления.
WATCH [db.]live_view;Виртуальный столбец _version в результате запроса обозначает версию данного результата.
Пример:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();WATCH lv;
┌────────────────now()─┬─_version─┐
│ 2021-02-21 09:17:21 │ 1 │
└───────────────────────┴───────────┘
┌────────────────now()─┬─_version──┐
│ 2021-02-21 09:17:26 │ 2 │
└───────────────────────┴───────────┘
┌────────────────now()─┬─_version──┐
│ 2021-02-21 09:17:31 │ 3 │
└───────────────────────┴───────────┘
...По умолчанию запрашиваемые данные возвращаются клиенту, однако в сочетании с запросом INSERT INTO они могут быть перенаправлены для вставки в другую таблицу.
Пример:
INSERT INTO [db.]table WATCH [db.]live_view ...С помощью параметра EVENTS можно получить компактную форму результата запроса WATCH. Вместо полного результата вы получаете номер последней версии результата.
WATCH [db.]live_view EVENTS;Пример:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv EVENTS;
┌─version─┐
│ 1 │
└──────────┘
┌─version─┐
│ 2 │
└──────────┘
...Параметр LIMIT n задает количество обновлений запроса WATCH, после которого отслеживание прекращается. По умолчанию это число не задано, поэтому запрос будет выполняться постоянно. Значение LIMIT 0 означает, что запрос WATCH вернет единственный актуальный результат запроса и прекратит отслеживание.
WATCH [db.]live_view LIMIT 1;Пример:
CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv EVENTS LIMIT 1;
┌─version─┐
│ 1 │
└──────────┘Параметр FORMAT работает аналогично одноименному параметру запроса SELECT.
ПРИМЕЧАНИЕ:
При отслеживании LIVE VIEW через интерфейс HTTP следует использовать формат JSONEachRowWithProgress. Постоянные сообщения об изменениях будут добавлены в поток вывода для поддержания активности долговременного HTTP-соединения до тех пор, пока результат запроса изменяется. Промежуток времени между сообщениями об изменениях управляется настройкой live_view_heartbeat_interval.