ClickHouse vs BigQuery: 4 отличия в SQL

Привет, Хабр!

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

Наша аналитическая база данных несколько лет строилась на базе Google BigQuery. Она содержала сотни представлений на гугл-диалекте SQL, и весь этот технопарк было решено перебазировать на российские платформы. Понятное дело, что ClickHouse и BigQuery — далеко не одно и то же, так что в процессе переезда нам пришлось набить немало шишек. В этой статье я покажу несколько отличий в SQL этих СУБД. Надеюсь, их понимание поможет сэкономить время и нервы тем, кто сталкивается с аналогичной задачей.

JOIN с нечеткими условиями

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

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

В таблице user_group поля link_begin и link_end отражают, соответственно, начало и окончание нахождения данного юзера в данной группе.

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

В BQ задача решается легко и изящно:

SELECT t.user_id AS user_id,     date_paid, amount, group_id FROM (     SELECT user_id, date_paid, amount     FROM dataset.transactions ) AS t LEFT JOIN dataset.user_group AS ug      ON ug.user_id = t.user_id     AND t.date_paid >= ug.link_begin     AND t.date_paid <= ug.link_end

В ClickHouse этот фокус не пройдет: там нет джойнов по нескольким нечетким условиям. Зато можно использовать функции для работы с массивами. Адаптация запроса делается в два действия:

1. джойним по равенству, при этом группируем поля, которые не используются в условии джойна, в массив с помощью функции groupArray();

2. во внешнем селекте используем фильтр по массиву arrayFilter(), чтобы вытащить только строки, подпадающие под нечеткие условия.

Запрос для ClickHouse, таким образом, будет иметь следующий вид:

SELECT t.user_id AS user_id,     date_paid, amount,     arrayFilter(x-> x.2<=date_paid AND x.3>=date_paid, ug.params)[1].1 AS group_id FROM (     SELECT user_id, date_paid, amount     FROM dataset.transactions ) AS t LEFT JOIN  (   SELECT user_id,     groupArray(tuple(group_id, link_begin, link_end)) AS params   FROM dataset.user_group    GROUP BY 1 ) AS ug ON ug.user_id = t.user_id

Вычисление столбцов сразу

Специалистам BQ будет непривычно, что поля, вычисленные в подзапросе в ClickHouse, можно использовать в том же подзапросе. Рассмотрим простой запрос:

SELECT a+1 AS a, a+2 AS b FROM ( SELECT 1 AS a )

 

В BQ он вернет результат:

Поле b здесь вычисляется на основе значения поля a из источника. А вот ClickHouse отдаст нам другой вариант:

Здесь для расчета b используется только что модифицированное значение исходного a. Эта особенность требует тщательного пересмотра витрин BQ, ведь запросы не будут отдавать ошибок, но вычисления могут «поехать».

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

SELECT a+1 AS a, a+2 AS b, b*3 AS c FROM ( SELECT 1 AS a )

Хранение и отображение даты

При переносе данных, содержащих поля с датами, из BigQuery в ClickHouse важно иметь в виду следующий нюанс. В BQ по умолчанию даты хранятся в тайм-зоне UTC. То есть, когда вы задаете дату, она переводится в UTC:

SELECT utc, STRING(utc) AS utc_str,     msk, STRING(msk) AS msk_str,     msk3, STRING(msk3) AS msk3_str FROM (     SELECT TIMESTAMP("2024-05-21 00:00:00") AS utc,         TIMESTAMP("2024-05-21 00:00:00", "Europe/Moscow") AS msk,         TIMESTAMP("2024-05-21 00:00:00+03") AS msk3 )

 

Если таймстемп задается в тайм-зоне Moscow, то из значения времени вычтется три часа. Также видно, что при переводе из таймстемпа в строку значимая информация не меняется. Можно легко и без искажений перевести дату в строку и извлечь, например, год с месяцем:

SELECT LEFT(STRING(TIMESTAMP("2024-05-21 00:00:00", "Europe/Moscow")), 7) AS period

 

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

SELECT utc, CAST(utc AS String) AS utc_str,     msk, CAST(msk AS String) AS msk_str FROM ( SELECT toDateTime('2024-05-21 00:00:00') AS utc ,     toDateTime('2024-05-21 00:00:00', 'Europe/Moscow') AS msk  )

 

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

Настройка join_use_nulls

В BigQuery, если при джойне в правой таблице не нашлось строки, подходящей под условия объединения, в результате поля этой строки будут заполнены пустотой — NULL, независимо от типа данных этих полей. 

SELECT * FROM (     SELECT 1 AS a, 'b' AS b     UNION ALL     SELECT 2 AS a, 'c' AS b ) AS t1 LEFT JOIN (     SELECT 1 AS a1, 'd' AS d, CURRENT_DATE() AS t ) AS t2 ON t1.a = t2.a1

 

В ClickHouse не всё так однозначно: результат зависит от настройки пользователя, от имени которого запускается запрос. Речь идет о настройке join_use_nulls. Если она установлена в 1, то результат будет такой же, как в BigQuery. А вот если join_use_nulls = 0, то вместо NULL в итог передастся 0 для числовых значений, пустая строка ” для типа String и 1970-01-01, то есть 0, для даты:

Если вы имеете дело с объемными таблицами и сложными запросами, эта особенность может подпортить вам нервы, поэтому сразу установите всем пользователям настройку join_use_nulls = 1, а для «подстраховки» ее можно добавлять и в сам запрос:

SELECT * FROM (     SELECT 1 AS a, 'b' AS b     UNION ALL     SELECT 2 AS a, 'c' AS b ) AS t1 LEFT JOIN (     SELECT 1 AS a1, 'd' AS d, today() AS t ) AS t2 ON t1.a = t2.a1 SETTINGS join_use_nulls = 1

Заключение

Итак, BigQuery — это далеко не то же самое, что ClickHouse. При переезде с первого на второе можно иметь в виду следующее:

• JOIN с нечеткими условиями вида t1.a > t2.b в ClickHouse можно реализовать, используя сначала группировку строк в массив с помощью groupArray(), а затем вытаскивая нужные строки с помощью arrayFilter().

• По умолчанию в ClickHouse вычисляемые столбцы можно использовать в том же SELECT, в котором они вычислены, поэтому внимательно следите за алиасами полей!

• BQ и CH по-разному хранят и отображают даты: BigQuery — в тайм-зоне UTC, а ClickHouse — в тайм-зоне, указанной в настройках сервера. Чтобы не запутаться, не используйте в запросах тайм-зоны и обязательно сверяйте даты в витринах с источником.

• При джойнах в ClickHouse не найденные в присоединяемой таблице поля могут иметь значение, отличное от NULL, в зависимости от настройки join_use_nulls. Установите ее в 1, если хотите получить поведение, аналогичное таковому в BigQuery.

вАЙТИ — DIY-медиа для ИТ-специалистов. Делитесь личными историями про решение самых разных ИТ-задач и получайте вознаграждение.