Операторы IN

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

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

Примеры:

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

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

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

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

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

Пример

Запрос:

Результат:

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

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

Подзапрос может уточнять более одной колонки для фильтрации кортежей.

Пример:

Колонки слева и справа от оператора IN должны иметь один и тот же тип.

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

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

Обработка NULL

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

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

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

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

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

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

примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

Другими словами, набор данных в клаузе IN будет собран на каждом сервере независимо, только по данным, которые хранятся локально на каждом из серверов.

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

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

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

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

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

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

Запрашивающий сервер выполнит подзапрос:

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

Временная таблица _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:

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

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

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

Например, следующее:

трансформируется на каждом сервере в:

где 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, которые могут привести к другому и более оптимальному поведению.