К безошибочному UNION ALL

Это неблагодарная, но необходимая задача. В SQL часто нужно объединять (UNION ALL) две или более таблицы вертикально, чтобы объединить их значения.

Предположим, нам нужно объединить 3 таблицы: веб-трафик, расходы на рекламу и данные о продажах, чтобы получить полное представление о стоимости привлечения (CPA).

В конечном итоге, мы хотели бы агрегировать данные с детализацией по дате, URL целевой страницы, кампании и каналу — поэтому, как бы мы ни объединяли 3 таблицы, мы захотим обернуть это во внешний запрос с GROUP BY, чтобы уменьшить .

Для этого мы могли бы сделать несколько вещей:

• FULL OUTER JOIN: возвращает все значения + объединяет там, где есть совпадение по дате / целевой странице / кампании / каналу

• UNION: объединяет все значения вертикально + удаляет дубликаты, чтобы вернуть уникальные значения

• UNION ALL: объединяет все значения вертикально, без логики сопоставления

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

С точки зрения производительности, UNION ALL всегда будет более производительным, чем FULL OUTER JOIN, так как нет операции сопоставления, UNION ALL просто добавляет таблицы.

Задача заполнения значений null в UNION ALL

Часто при выполнении UNION ALL у таблиц будут несовпадающие столбцы: в таблице веб-трафика не будет значений дохода, а в данных о продажах не будет значений сессий.

Чтобы завершить объединение, мы должны заполнить эти столбцы как null или 0 в нашем запросе, чтобы объединение прошло успешно:

select
	date,
	landing_page_url,
	campaign,
	channel,
	sessions,
	pageviews,
	time_on_site,
	null as orders,
	null as customers,
	null as revenue
from sessions

union all 

select
	date,
	landing_page_url,
	campaign,
	channel,
	null as sessions,
	null as pageviews,
	null as time_on_site,
	orders,
	customers,
	revenue
from sales

Без этих значений null мы получим неприятную ошибку:

Запросы в UNION ALL имеют несовпадающее количество столбцов; запрос 1 имеет 10 столбцов, запрос 2 имеет 12 столбцов

Как вы можете себе представить, это становится очень болезненным, когда мы хотим добавить новый столбец в наши UNION — мы должны добавить столбец как значение null в каждое выражение запроса в стеке UNION ALL.

Встречайте макрос union_relations в dbt

dbt позволяет вам писать макросы в Jinja, чтобы автоматизировать эту задачу.

Макрос union_relations в пакете dbt_utils полностью освобождает нас от необходимости заполнять null или 0 для каждого столбца, который не существует в других таблицах, которые мы объединяем.

С помощью 3 строк кода мы можем достичь того, что ранее требовало десятков:

{{ dbt_utils.union_relations(

    relations=[ref('sessions'), ref('sales'), ref('ads')

) }}

Когда dbt выполняется (либо локально в командной строке, в dbt Cloud, или где бы вы ни решили его развернуть), макрос union_relations генерирует точно такой же SQL, как мы писали вручную.

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

Новичок в dbt? Ознакомьтесь с введением в dbt для получения дополнительной информации о dbt и рабочем процессе аналитической инженерии, который он поддерживает.

Кратко: dbt позволяет специалистам по данным писать код как инженеры-программисты, что в данном случае означает не повторять себя без необходимости.

Для меня магия макроса union_relations очевидна — он позволяет мне значительно сэкономить время на выполнении задачи заполнения значений null в выражении UNION ALL.

Если вам интересно углубиться в то, как работают макросы dbt, макрос union_relations использует несколько управляющих структур программирования: циклы for и условные операторы if — благодаря поддержке dbt шаблонов Jinja, мы теперь можем использовать эти управляющие структуры, чтобы писать SQL, который пишет сам себя.