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 этот фокус не пройдет: там нет джойнов по нескольким нечетким условиям. Зато можно использовать функции для работы с массивами. Адаптация запроса делается в два действия:
- джойним по равенству, при этом группируем поля, которые не используются в условии джойна, в массив с помощью функции groupArray();
- во внешнем селекте используем фильтр по массиву 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
