Операторы IN

Документация по операторам IN, за исключением операторов NOT IN, GLOBAL IN и GLOBAL NOT IN, которые рассматриваются отдельно

Операторы IN, NOT IN, GLOBAL IN и GLOBAL NOT IN рассматриваются отдельно, поскольку их функциональные возможности достаточно широки.

Левая часть оператора — это либо один столбец, либо кортеж.

Примеры:

SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...

Если левая часть — это один столбец, входящий в индекс, а правая часть — набор констант, система использует индекс для обработки запроса.

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

Правая часть оператора может представлять собой набор константных выражений, набор кортежей с константными выражениями (как показано в примерах выше), имя таблицы базы данных или подзапрос SELECT в скобках.

Из соображений обратной совместимости, если правая часть представляет собой одно выражение tuple, оно может интерпретироваться либо как набор значений, либо как одно значение кортежа — в зависимости от левой части оператора IN. Если левая часть является скалярным значением, ClickHouse рассматривает элементы этого единственного выражения tuple в правой части как отдельные значения IN:

Query

SELECT
    1 IN (tuple(1, 2)) AS one_in_tuple,
    2 IN (tuple(1, 2)) AS two_in_tuple,
    3 IN (tuple(1, 2)) AS three_in_tuple;

Response

┌─one_in_tuple─┬─two_in_tuple─┬─three_in_tuple─┐
│            1 │            1 │              0 │
└──────────────┴──────────────┴────────────────┘

Это работает аналогично SELECT 1 IN (1, 2). Если левая часть также является кортежем, правая часть интерпретируется как набор значений кортежа:

Query

SELECT tuple(1, 2) IN (tuple(1, 2)) AS tuple_in_tuple;

Response

┌─tuple_in_tuple─┐
│              1 │
└────────────────┘

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

Query

SELECT 1 IN (tuple(1, 2), tuple(3, 4));

Response

Code: 43. DB::Exception: Unsupported types for IN. First argument type UInt8. Second argument type Tuple(Tuple(UInt8, UInt8), Tuple(UInt8, UInt8)). (ILLEGAL_TYPE_OF_ARGUMENT)

ClickHouse допускает различие типов в левой и правой частях подзапроса IN. В этом случае значение правой части приводится к типу левой части, как если бы к правой части была применена функция accurateCastOrNull.

Это означает, что тип данных становится Nullable, и если преобразование невозможно, возвращается NULL.

Пример

Query

SELECT '1' IN (SELECT 1);

Response

┌─in('1', _subquery49)─┐
│                    1 │
└──────────────────────┘

Если правая часть оператора является именем таблицы (например, UserID IN users), это эквивалентно подзапросу UserID IN (SELECT * FROM users). Используйте это при работе с внешними данными, передаваемыми вместе с запросом. Например, запрос можно отправить вместе с набором идентификаторов пользователей, загруженных во временную таблицу ‘users’, по которой нужно выполнить фильтрацию.

Если правая часть оператора — это имя таблицы с движком Set (подготовленный набор данных, который всегда хранится в оперативной памяти), набор данных не будет создаваться заново при каждом запросе.

Подзапрос может задавать более одного столбца для фильтрации Tuple.

Пример:

Query

SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...

Столбцы слева и справа от оператора IN должны иметь одинаковый тип.

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

Query

SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC

Response

┌──EventDate─┬────ratio─┐
│ 2014-03-17 │        1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└────────────┴──────────┘

Для каждого дня после 17 марта вычислите долю просмотров страниц, совершённых пользователями, которые посещали сайт 17 марта. Подзапрос в условии IN всегда выполняется только один раз на одном сервере. Зависимых подзапросов нет.

​

Обработка NULL

При обработке запроса оператор IN считает, что результат операции с NULL всегда равен 0, независимо от того, находится NULL справа или слева от оператора. Значения NULL не включаются ни в один набор данных, не считаются равными друг другу и не могут сравниваться, если transform_null_in = 0.

Вот пример с таблицей t_null:

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘

В результате выполнения запроса SELECT x FROM t_null WHERE y IN (NULL,3) вы получите следующий результат:

┌─x─┐
│ 2 │
└───┘

Можно видеть, что строка, в которой y = NULL, исключается из результатов запроса. Это происходит потому, что ClickHouse не может определить, входит ли NULL в множество (NULL,3), возвращает 0 в качестве результата операции, а SELECT исключает эту строку из итогового вывода.

SELECT y IN (NULL, 3)
FROM t_null

┌─in(y, tuple(NULL, 3))─┐
│                     0 │
│                     1 │
└───────────────────────┘

​

Распределённые подзапросы

Для операторов IN с подзапросами (аналогично операторам JOIN) существует два варианта: обычный IN / JOIN и GLOBAL IN / GLOBAL JOIN. Они различаются способом выполнения при распределённой обработке запросов.

Помните, что описанные ниже алгоритмы могут работать по-разному в зависимости от значения настройки distributed_product_mode из раздела настроек.

При использовании обычного IN запрос отправляется на удалённые серверы, и каждый из них выполняет подзапросы в секции IN или JOIN.

При использовании GLOBAL IN / GLOBAL JOIN сначала выполняются все подзапросы для GLOBAL IN / GLOBAL JOIN, а результаты сохраняются во временные таблицы. Затем эти временные таблицы отправляются на каждый удалённый сервер, где запросы выполняются с использованием полученных временных данных.

Для GLOBAL ... JOIN то, какая сторона JOIN вычисляется как подзапрос, зависит от типа JOIN: для LEFT и INNER JOIN вычисляется правая таблица; для RIGHT JOIN вместо неё вычисляется левая таблица, поскольку правая таблица является сохраняемой стороной и должна считываться из сегментов.

Для нераспределённого запроса используйте обычные IN / JOIN.

Будьте осторожны при использовании подзапросов в секциях IN / JOIN при распределённой обработке запросов.

Рассмотрим несколько примеров. Предположим, что на каждом сервере кластера есть обычная таблица local_table. Кроме того, на каждом сервере есть таблица distributed_table с движком Distributed, которая охватывает все серверы кластера.

При выполнении запроса к distributed_table он будет отправлен на все удалённые серверы и выполнен на них с использованием local_table.

Например, запрос

SELECT uniq(UserID) FROM distributed_table

будет отправлен на все удалённые серверы как

SELECT uniq(UserID) FROM local_table

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

Теперь рассмотрим запрос с IN:

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

• Вычисление пересечения аудиторий двух сайтов.

Этот запрос будет отправлен на все удалённые серверы как

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

Иными словами, набор данных в выражении IN будет формироваться на каждом сервере независимо — только из тех данных, которые хранятся локально на этом сервере.

Это будет работать корректно и оптимально, если вы заранее предусмотрели такой сценарий и распределили данные по серверам кластера так, что данные для каждого отдельного UserID целиком хранятся на одном сервере. В этом случае все необходимые данные будут доступны локально на каждом сервере. В противном случае результат окажется неточным. Такой вариант запроса называется “local IN”.

Чтобы исправить поведение запроса в случае, когда данные распределены случайным образом по серверам кластера, можно указать distributed_table внутри подзапроса. Запрос будет выглядеть следующим образом:

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

Этот запрос будет отправлен на все удалённые серверы как

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

Подзапрос начнёт выполняться на каждом удалённом сервере. Поскольку подзапрос использует distributed таблицу, подзапрос на каждом удалённом сервере будет повторно отправлен на все удалённые серверы в виде:

SELECT UserID FROM local_table WHERE CounterID = 34

Например, при наличии кластера из 100 серверов выполнение всего запроса потребует 10 000 элементарных обращений, что, как правило, считается неприемлемым.

В таких случаях следует всегда использовать GLOBAL IN вместо IN. Рассмотрим, как это работает для следующего запроса:

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

Сервер-инициатор выполнит подзапрос:

SELECT UserID FROM distributed_table WHERE CounterID = 34

и результат будет помещён во временную таблицу в оперативной памяти. Затем запрос будет отправлен на каждый удалённый сервер в виде:

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1

Временная таблица _data1 будет отправлена на каждый удаленный сервер вместе с запросом (имя временной таблицы зависит от реализации).

Это более эффективно, чем использование обычного IN. Однако имейте в виду следующее:

1. При создании временной таблицы данные не становятся уникальными. Чтобы уменьшить объем данных, передаваемых по сети, укажите DISTINCT в подзапросе. (Для обычного IN этого делать не нужно.)
2. Временная таблица будет отправлена на все удаленные серверы. При передаче не учитывается топология сети. Например, если 10 удаленных серверов находятся в датацентре, значительно удаленном от сервера, инициировавшего запрос, данные будут 10 раз переданы по каналу в этот удаленный датацентр. Старайтесь избегать больших наборов данных при использовании GLOBAL IN.
3. При передаче данных на удаленные серверы ограничения пропускной способности сети не настраиваются. Это может привести к перегрузке сети.
4. Старайтесь распределять данные по серверам так, чтобы не приходилось регулярно использовать GLOBAL IN.
5. Если вам часто требуется использовать GLOBAL IN, спланируйте размещение кластера ClickHouse так, чтобы одна группа реплик находилась не более чем в одном датацентре, а между ними была быстрая сеть, — тогда запрос можно будет полностью обработать в пределах одного датацентра.

Также имеет смысл указать локальную таблицу в условии GLOBAL IN, если эта локальная таблица доступна только на сервере, инициировавшем запрос, и вы хотите использовать данные из нее на удаленных серверах.

​

Распределённые подзапросы и max_rows_in_set

Вы можете использовать max_rows_in_set и max_bytes_in_set, чтобы управлять объёмом данных, передаваемых при распределённых запросах.

Это особенно важно, если запрос GLOBAL IN возвращает большой объём данных. Рассмотрим следующий SQL:

SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE <some_predicate>)

Если some_predicate недостаточно селективен, он вернет большой объем данных, что приведет к проблемам с производительностью. В таких случаях стоит ограничить передачу данных по сети. Также обратите внимание, что set_overflow_mode имеет значение throw (по умолчанию), то есть при достижении этих пороговых значений генерируется исключение.

​

Распределённые подзапросы и max_parallel_replicas

Когда max_parallel_replicas больше 1, распределённые запросы дополнительно преобразуются.

Например:

SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3

на каждом сервере преобразуется в:

SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=M

где M находится в диапазоне от 1 до 3 в зависимости от того, на какой реплике выполняется локальный запрос.

Эти настройки влияют на каждую таблицу семейства MergeTree в запросе и дают тот же эффект, что и применение SAMPLE 1/3 OFFSET (M-1)/3 к каждой из них.

Поэтому добавление настройки max_parallel_replicas даст корректные результаты только в том случае, если обе таблицы используют одну и ту же схему репликации и сэмплируются по UserID или по одному из его подключей. В частности, если у local_table_2 нет ключа выборки, результаты будут некорректными. То же правило применяется и к JOIN.

Один из способов обойти это ограничение, если local_table_2 не удовлетворяет этим требованиям, — использовать GLOBAL IN или GLOBAL JOIN.

Если у таблицы нет ключа выборки, можно использовать более гибкие варианты parallel_replicas_custom_key, которые могут давать иное и более оптимальное поведение.