(Зображення від автора)
Цикли Jinja в dbt є потужним інструментом для автоматизації генерації запитів та зменшення повторюваного коду, особливо коли працюєш з загальними шаблонами. В цих циклах можна ітеративно обробляти списки або словники за синтаксисом, схожим на Python, генеруючи SQL-вирази.
У п'яти загальних шаблонах, розглянутих нижче, цикли використовуються для завдань, таких як динамічне відображення умов CASE, агрегація даних з кількох джерел, обчислення динамічних перцентилів та побудова менш повторюваних запитів тощо. Кожен шаблон спрощує створення моделей dbt та покращує їхню масштабованість.
Шаблон 1: Динамічні умови CASE
Перший шаблон використовує цикли Jinja для динамічного відображення назв продуктів на категорії в умовах CASE. Ітерація через словник пар "ключ-значення" дозволяє ефективно класифікувати дані без необхідності вручну прописувати кожну умову, роблячи ваші моделі чистішими та масштабованішими.
{%
set type_mapping = {
"shirt": "clothing",
"truck": "vehicle",
"hat": "clothing",
"jeans": "clothing",
"car": "vehicle",
}
%}
select
id,
product_name,
case
{% for key, value in type_mapping.items() %}
when product_name == '{{ key }}' then '{{ value }}'
{% endfor %}
else 'other'
end as product_type
from {{ ref('seed_products') }}
Вихід моделі (Зображення від автора)
Шаблон 2: Генерація умов UNION
Цей шаблон динамічно генерує запити UNION для кількох таблиць або наборів даних. Як показано тут, ітерація через список типів продуктів дозволяє легко об'єднувати дані з різних джерел без необхідності вручну писати кожен запит SELECT, зменшуючи надмірність.
{%
set products = [
"pants",
"shirts",
"shoes"
]
%}
{% for product in products %}
select
id,
product_name,
product_price
from {{ ref('seed_' + product) }}
{% if not loop.last %}union all{% endif %}
{% endfor %}
Вихід моделі (Зображення від автора)
Шаблон 3: Автоматизація подібних CTE
Створення загальних виразів таблиць (CTE) можна автоматизувати для кількох джерел даних. Ітерація через список вихідних таблиць дозволяє динамічно генерувати однакові CTE, зменшуючи повторюваний код для складних запитів.
{%
set sources = [
"customers",
"orders",
"inventories"
]
%}
with
{% for source in sources %}
{{ source }}_cte as (
select *
from {{ ref('seed_' + source) }}
)
{% if not loop.last %},{% endif %}
{% endfor %}
select
c.customer_id,
o.order_id,
i.inventory_id,
c.customer_name,
o.order_amount,
i.product_name,
i.stock_quantity
from customers_cte as c
left join orders_cte as o
on c.customer_id = o.customer_id
left join inventories_cte as i
on o.inventory_id = i.inventory_id
Вихід моделі (Зображення від автора)
Шаблон 4: Гнучка агрегація
Цей шаблон спрощує агрегацію через кілька стовпців, ітеруючи через заздалегідь визначений список. Динамічне підсумовування даних по продажах для кожного регіону дозволяє ефективно генерувати узгоджену агрегаційну логіку без необхідності писати повторюваний SQL для кожного стовпця. Це чистий і масштабований підхід до обробки багатовимірних даних.
Як бонус, ви також можете використовувати цикл для динамічної агрегації різних показників у зовнішньому циклі.
{%
set regions = [
"north",
"south",
"east",
"west"
]
%}
select
{% for region in regions %}
sum({{ region }}_sales) as total_{{ region }}_sales
{% if not loop.last %}, {% endif %}
{% endfor %}
from {{ ref('seed_sales') }}
Вихід моделі (Зображення від автора)
Шаблон 5: Генерація параметрів функції
Останній шаблон циклу генерує значення параметрів функції адаптивно. У цьому конкретному прикладі, використовуючи діапазон значень перцентилей, він створює розподіл перцентилей для метрик без явного перерахування кожного налаштування параметра. Крім того, якщо потрібно змінити розподільчі значення для цих метрик, буде достатньо лише оновити діапазон значень.
{%
set percentiles = range(25, 99, 25)
%}
select
{% for p in percentiles %}
percentile_cont({{ p }} / 100)
within group (order by price) as price_p{{ p }},
percentile_cont({{ p }} / 100)
within group (order by rating) as rating_p{{ p }}
{% if not loop.last %}, {% endif %}
{% endfor %}
from {{ ref('seed_product_sales') }}
Вихід моделі (Зображення від автора)
Резюме
Цикли Jinja можуть бути потужним інструментом в dbt, коли їх використовують ефективно, спрощуючи генерацію запитів і зменшуючи повторюваний код. Автоматизуючи завдання, такі як створення умовних операторів, агрегація даних або створення UNION, цикли можуть значно підвищити ефективність ваших моделей.
Шаблони, описані вище, є кількома загальними і корисними прикладами, з якими ви, ймовірно, зіткнетеся. Ці техніки стають ще більш ефективними, коли комбінуються з динамічним створенням списків і словників, дозволяючи створювати гнучкі і масштабовані запити, які автоматично оновлюються.
Пам'ятайте про ці шаблони циклів, коли продовжуєте розробляти моделі dbt!
Перекладено з: 5 Useful Loop Patterns in dbt