dbt. Макросы и Jinja. Часть 2. Использование циклов. Получение датасета из макроса.

В заметке приведён пример использования цикла для создания pivot table в PostgreSQL.

Создание аналога сводной таблицы Excel в виде таблицы в базе данных средствами sql - задача, которая вполне может случиться в трудовой деятельности разработчика. Сегодня посмотрим, как такая задача может быть решена в dbt, используя цикл for Jinja.

Будем пивотить такой датасет:

Чтобы в итоге получить следующую таблицу:

Модель выглядит так:

{{
    config(
    materialized='incremental',
    alias='stg_order_status_din_pivot',
    schema='stg',
	pre_hook = ["{{truncate_table()}}"],
    )
}}

{%- set order_statuses = ['returned','shipped', 'placed'] -%}

with orders as (
select "ORDERSTATUS" orderstatus, "ORDERID" orderid, "SALESPERSON" salesperson 
from raw.raw_orders)

select salesperson,
{%- for order_status in order_statuses -%}
sum (case when orderstatus = '{{ order_status }}' then 1 else 0 end) as {{ order_status }}_qty,
{%- endfor %}
 count (*) total
from orders
group by salesperson

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

dbt compile -m stg_order_status_din_pivot

--Вот в такой обычный запрос превратился наш код с Jinja:
select salesperson
,sum (case when orderstatus = 'returned' then 1 else 0 end) as returned_qty
,sum (case when orderstatus = 'shipped' then 1 else 0 end) as shipped_qty
,sum (case when orderstatus = 'placed' then 1 else 0 end) as placed_qty,
 count (*) total
from orders
group by salesperson

Теперь остановимся на паре строк:

{%- set order_statuses = ['returned','shipped', 'placed'] -%}

Это уже знакомое по предыдущей заметке присвоение переменной order_statuses значения. Кладём туда список значений поля orderstatus, по которому будем пивотить. Их всего 3: 'returned', 'shipped', 'placed'

И, собственно, сам цикл, который формирует для каждого значения из вышеупомянутого списка строку sql.

{%- for order_status in order_statuses -%}

Подробнее о цикле for в Jinja можно посмотреть тут https://jinja.palletsprojects.com/en/3.1.x/templates/#for

Возможно, у читателя возникнет обоснованный вопрос: как быть, если мы заранее не знаем список значений поля, по которому будем пивотить? Плюс, в нашем учебном примере таких уникальных значений всего 3: 'returned', 'shipped', 'placed'. А если их 33, неужели пришлось бы в явном виде прописывать все значения в списке?

Да, можно строчку {%- set order_statuses = ['returned','shipped', 'placed'] -%}, где перечень статусов захардкожен, заменить на их вычисление. Далее код обновлённой модели, в комментариях он по-моему, не нуждается, всё интуитивно понятно.

{%- set order_statuses_query -%}
select distinct "ORDERSTATUS" ORDERSTATUS from  raw.raw_orders
{% endset %}

{% if execute %}
{% set res = run_query (order_statuses_query) %}

{% set order_statuses = res.columns[0].values() %}

--в самом селекте ничего не изменилось
with orders as (
select "ORDERSTATUS" ORDERSTATUS, "ORDERID" ORDERID, "SALESPERSON" SALESPERSON 
from raw.raw_orders)

select SALESPERSON,
{%- for order_status in order_statuses -%}
sum (case when orderstatus = '{{ order_status }}' then 1 else 0 end) as {{ order_status }}_qty,
{%- endfor %}
 count (*) total
from orders
group by SALESPERSON
{% endif %}

А теперь про получение датасета из макроса.

Само вычисление списка статусов можно было вынести в макрос, который выглядел бы так:

{# Это комментарий к макросу get_order_statuses, который возвращает список уникальных статусов заказа #}
{# select distinct "ORDERSTATUS" ORDERSTATUS from  raw.raw_orders #}
{% macro get_order_statuses() %}

{%- set order_statuses_query -%}
select distinct "ORDERSTATUS" ORDERSTATUS from  raw.raw_orders
{% endset %}

{% if execute %}
{% set results = run_query (order_statuses_query) %}

{% set results_list = results.columns[0].values() %}
{% endif %}

{{ return (results_list) }}
{% endmacro %}

Тогда в первоначальной модели достаточно заменить строчку

{%- set order_statuses = ['returned','shipped', 'placed'] -%}

на {%- set order_statuses = get_order_statuses() -%}

Насколько это будет хорошо и удобно? Не знаю.

P.S. Подробно о специфических для dbt функциях jinja можно посмотреть в документации

P.P.S. Если бы возникла необходимость указать в jinja зарезервированные символы, их надо будет экранировать апострофами, например, так

select *, {{'{{'}}

Целиком весь блок jinja можно экранировать, поместив его между

{% raw %} … {% endraw %}