На какви принципи се основава идеалното Складово Хранилище на Данни?

Фокус върху бизнес стойността и аналитиката при отсъствие на boilerplate code. Управление на DWH като кодова база: версиониране, прегледи, автоматизирано тестване и CI. Модулност, разширяемост, отворен код и общност. Удобна потребителска документация и визуализация на зависимостите (Data Lineage).

За всичко това в подробности и за ролята на DBT в екосистемата Big Data & Analytics — добре дошли под кат.

Здравейте на всички

Аз съм Артемий Козирь. Вече повече от 5 години работя с хранилища на данни, занимавам се с изграждане на ETL/ELT, както и с анализ на данни и визуализация. В момента работя в Wheely, преподавам в OTUS на курса Data Engineer, и днес искам да споделя с вас статия, която написах в преддверието на старта на нов набор на курса.

Кратък преглед

Фреймворкът DBT — това е всичко за буквата T в акронима ELT (Extract — Transform — Load).

С появата на такива производителни и мащабируеми аналитични бази данни като BigQuery, Redshift, Snowflake, изчезна всякакво значение да правим трансформации извън Хранилището на Данни.

DBT не извлича данни от източници, но предоставя огромни възможности за работа с тези данни, които вече са заредени в Склад (в Internal или External Storage).

Основното назначение на DBT е да вземе кода, да го компилира в SQL, и да изпълни командите в правилната последователност в Склад.

Структура на проекта DBT

Проектът се състои от две директории и файлове от два типа:

Модел (.sql) — единица трансформация, изразена с SELECT заявка
Файл конфигурация (.yml) — параметри, настройки, тестове, документация

На базово ниво работата е следната:

Потребителят подготвя кода на моделите в предпочитана IDE
Чрез CLI се извиква стартиране на моделите, DBT компилира кода на моделите в SQL
Скомпилираният SQL код се изпълнява в Склад в зададената последователност (граф)

Ето как може да изглежда стартирането от CLI:

Всичко е SELECT

Това е киллер-функция на фреймворка Data Build Tool. С други думи, DBT абстрахира целия код, свързан с материализацията на вашите заявки в Склад (вариации от командите CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, …).

Всяка модел предполага написването на една SELECT заявка, която определя резултатния набор от данни.

Възможна е многостепенна логика на преобразуване, която консолидира данни от множество други модели. Пример за модел, който изгражда витрина за поръчки (f_orders):

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}

с поръчки като (

   select * from {{ ref('stg_orders') }}

),

плащания по поръчки като (

   select * from {{ ref('order_payments') }}

),

финално като (

   select
       orders.order_id,
       orders.customer_id,
       orders.order_date,
       orders.status,
       {% for payment_method in payment_methods -%}
       order_payments.{{payment_method}}_amount,
       {% endfor -%}
       order_payments.total_amount as amount
   from orders
       left join order_payments using (order_id)

)

select * from final

Какво интересно можем да видим тук?

На първо място: Използвани са CTE (Общи Таблични Изрази) — за организиране и разбиране на кода, съдържащ много преобразувания и бизнес логика

На второ място: Кодът на модела — е смесица от SQL и език Jinja (език за шаблони).

В примера е използван цикъл for за формиране на сумата по всеки метод на плащане, посочен в израза set. Използва се и функцията ref — възможност за рефериране в кода на други модели:

По време на компилацията ref ще бъде преобразуван в целеви указател към таблица или представление в Хранилището
ref позволява изграждането на граф на зависимостите на моделите

Точно това Jinja предоставя на DBT почти неограничени възможности. Най-често използваните от тях са:

If / else оператори — условни оператори
For цикли — цикли
Променливи — променливи
Макро — създаване на макроси

Материализация: Таблица, Представление, Инкрементално

Стратегията за Материализация — подход, при който резултатният набор от данни на модела ще бъде запазен в Хранилището.

В основен план това е:

Таблица — физическа таблица в Хранилището
Представление — виртуална таблица в Хранилището

Има и по-сложни стратегии за материализация:

Инкрементално — инкрементално зареждане на (големи таблици факти); нови редове се добавят, променените — актуализират, изтритите — се почистват
Ephemeral — моделът не се материализира директно, но участва като CTE в други модели
Всички други стратегии, които можете да добавяте сами

В допълнение към стратегиите за материализация се откриват възможности за оптимизация според конкретни Хранилища, например:

Snowflake: Преходни таблици, Поведение при обединяване, Клъстеризация на таблици, Копиране на права, Сигурни представления
Redshift: Distkey, Sortkey (интерлевиран, съставен), Късно свързване на представления
BigQuery: Партициониране и клъстеризация на таблици, Поведение при обединяване, Криптиране с KMS, Етикети и Тагове
Spark: Формат на файла (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy

В момента се поддържат следните Хранилища:

Postgres
Redshift
BigQuery
Snowflake
Presto (частично)
Spark (частично)
Microsoft SQL Server (community adapter)

Нека подобрим нашия модел:

Да го направим с инкрементално зареждане (Incremental)
Да добавим ключове за сегментиране и сортиране за Redshift

-- Конфигурация на модела:
-- Инкрементално зареждане, уникален ключ за обновяване на записи (unique_key)
-- Ключ за сегментиране (dist), ключ за сортиране (sort)
{{
  config(
        materialized='incremental',
        unique_key='order_id',
        dist="customer_id",
        sort="order_date"
    )
}}

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}

with orders as (

   select * from {{ ref('stg_orders') }}
   where 1=1
   {% if is_incremental() -%}
       -- Този филтър ще бъде приложен само за инкрементално стартиране
       and order_date >= (select max(order_date) from {{ this }})
   {%- endif %}

),

order_payments as (

   select * from {{ ref('order_payments') }}

),

final as (

   select
       orders.order_id,
       orders.customer_id,
       orders.order_date,
       orders.status,
       {% for payment_method in payment_methods -%}
       order_payments.{{payment_method}}_amount,
       {% endfor -%}
       order_payments.total_amount as amount
   from orders
       left join order_payments using (order_id)

)

select * from final

Граф на зависимостите между моделите

Той е също така дърво на зависимостите. Той е DAG (Directed Acyclic Graph — Направен Ацикличен Граф).

DBT изгражда граф на базата на конфигурацията на всички модели в проекта, а именно референции ref() вътре в моделите към други модели. Наличието на граф позволява следните неща:

Стартиране на моделите в правилната последователност
Паралелизация на образуването на витрини
Стартиране на произволен подграф

Пример за визуализация на графа:

Всеки възел в графа — това е модел, ръбовете на графа се задават чрез израза ref.

Качество на данните и Документация

Освен формирането на самите модели, DBT позволява да тествате редица предположения (assertions) относно резултатния набор от данни, като:

Not Null
Unique
Reference Integrity — референтна цялост (например, customer_id в таблицата orders отговаря на id в таблицата customers)
Съответствие на списък с допустими стойности

Възможно е добавяне на свои тестове (custom data tests), като, например, % отклонение на приходите спрямо показателите от преди ден, седмица, месец. Всяко предположение, формулирано във вид на SQL заявка, може да стане тест.

По този начин може да се проследят нежеланите отклонения и грешки в данните в витрините на Хранилището.

Що се отнася до документирането, DBT предоставя механизми за добавяне, версиониране и разпространение на метаданни и коментари на ниво модели и дори атрибути.

Ето как изглежда добавянето на тестове и документация на ниво конфигурационен файл:

 - name: fct_orders
   description: Тази таблица съдържа основна информация за поръчките, както и някои извлечени факти на базата на плащанията
   columns:
      - name: order_id
        tests:
          - unique # проверка за уникалност на стойностите
          - not_null # проверка за наличие на null
        description: Това е уникален идентификатор за поръчка
      - name: customer_id
        description: Външен ключ към таблицата на клиентите
        tests:
          - not_null
          - relationships: # проверка на референтната цялост
              to: ref('dim_customers')
              field: customer_id
      - name: order_date
        description: Дата (UTC), на която е направена поръчката
      - name: status
        description: '{{ doc("orders_status") }}'
        tests:
          - accepted_values: # проверка за допустими стойности
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

А ето как изглежда тази документация вече на генерирания уебсайт:

Макроси и Модули

Основната цел на DBT не е просто да стане набор от SQL скриптове, а да предостави на потребителите мощни и богати на възможности средства за изграждане на собствени трансформации и разпространение на тези модули.

Макросите са набори от конструкции и изрази, които могат да бъдат извиквани като функции вътре в моделите. Макросите позволяват повторно използване на SQL между модели и проекти в съответствие с инженерния принцип DRY (Не повтаряй себе си).

Пример за макрос:

{% macro rename_category(column_name) %}
case
 when {{ column_name }} ilike  '%osx%' then 'osx'
 when {{ column_name }} ilike  '%android%' then 'android'
 when {{ column_name }} ilike  '%ios%' then 'ios'
 else 'other'
end as renamed_product
{% endmacro %}

И неговото използване:

{% set column_name = 'product' %}
select
 product,
 {{ rename_category(column_name) }} -- извикване на макроса
from my_table

DBT идва с мениджър на пакети (packages), който позволява на потребителите да публикуват и повторно използват отделни модули и макроси.

Това означава възможността да се заредят и използват библиотеки като:

dbt_utils: работа с Дата/Час, Временни ключове, Тестове на схема, Пивот/Депивот и други
Готови шаблони за витрини за услуги като Snowplow и Stripe
Библиотеки за определени Хранилища на Данни, например Redshift
Logging — Модул за логиране на работата на DBT

С пълния списък на пакетите можете да се запознаете на dbt hub.

Още повече възможности

Тук ще опиша няколко други интересни характеристики и реализации, които аз и екипът използваме за изграждане на Хранилище на Данни в Wheely.

Разделяне на средите DEV — TEST — PROD

Дори в рамките на един кластер DWH (в различни схеми). Например, с помощта на следното изразяване:

with source as (
 
   select * from {{ source('salesforce', 'users') }}
   where 1=1
   {%- if target.name in ['dev', 'test', 'ci'] -%}           
       where timestamp >= dateadd(day, -3, current_date)   
   {%- endif -%}
 
)

Този код буквално казва: за средите dev, test, ci вземи данните само за последните 3 дни и не повече. Тоест, изпълнението в тези среди ще бъде много по-бързо и ще изисква по-малко ресурси. При стартиране в среда prod условието за филтър ще бъде игнорирано.

Материализация с алтернативно кодиране на колоните

Redshift е колоночна СУБД, която позволява задаване на алгоритми за компресия на данните за всяка отделна колона. Изборът на оптимални алгоритми може да намали заетия обем на диска с 20-50%.

Макрос redshift.compress_table ще изпълни командата ANALYZE COMPRESSION, ще създаде нова таблица с препоръчителните алгоритми за кодиране на колоните, указани ключовете за сегментация (dist_key) и подреждане (sort_key), ще премести данните в нея и, при необходимост, ще изтрие старата копия.

Сигнатура на макроса:

{{ compress_table(schema, table,
                 drop_backup=False,
                 comprows=none|Integer,
                 sort_style=none|compound|interleaved,
                 sort_keys=none|List,
                 dist_style=none|all|even,
                 dist_key=none|String) }}

Логиране на стартовете на модели

На всяко изпълнение на модел могат да бъдат прикачени хукове (hooks), които ще се изпълняват преди стартирането или веднага след приключване на създаването на модела:

   pre-hook: "{{ logging.log_model_start_event() }}"
   post-hook: "{{ logging.log_model_end_event() }}"

Модулът за логиране ще позволи записване на всички необходими метаданни в отделна таблица, по която впоследствие може да се провежда одит и анализ на проблемните места (bottlenecks).

Ето как изглежда таблото за управление на данните от логовете в Looker:

Автоматизация на поддръжката на Хранилището

Ако използвате някакви разширения на функционалността на използваното Хранилище, като UDF (User Defined Functions), то версионирането на тези функции, управлението на достъпа и автоматизираното внедряване на нови версии е много удобно да се осъществява в DBT.

Ние използваме UDF на Python за изчисляване на хеш стойности, домейни на имейл адреси и декодиране на битови маски (bitmask).

Пример на макрос, който създава UDF в среда на изпълнение (dev, test, prod):

{% macro create_udf() -%}
  
  {% set sql %}
      CREATE OR REPLACE FUNCTION {{ target.schema }}.f_sha256(mes "varchar")
          RETURNS varchar
          LANGUAGE plpythonu
          STABLE
      AS $$  
          import hashlib
          return hashlib.sha256(mes).hexdigest()
      $$
      ;
  {% endset %}
  
  {% set table = run_query(sql) %}
  
{%- endmacro %}

В Wheely ние използваме Amazon Redshift, който е основан на PostgreSQL. За Redshift е важно редовно да се събират статистики за таблиците и да се освобождава пространство на диска – командите ANALYZE и VACUUM, съответно.

За целта всяка вечер се изпълняват командите от макроса redshift_maintenance:

{% macro redshift_maintenance() %}
  
   {% set vacuumable_tables=run_query(vacuumable_tables_sql) %}
  
   {% for row in vacuumable_tables %}
       {% set message_prefix=loop.index ~ " of " ~ loop.length %}
  
       {%- set relation_to_vacuum = adapter.get_relation(
                                   database=row['table_database'],
                                   schema=row['table_schema'],
                                   identifier=row['table_name']
                               ) -%}
       {% do run_query("commit") %}
  
       {% if relation_to_vacuum %}
           {% set start=modules.datetime.datetime.now() %}
           {{ dbt_utils.log_info(message_prefix ~ " Vacuuming " ~ relation_to_vacuum) }}
           {% do run_query("VACUUM " ~ relation_to_vacuum ~ " BOOST") %}
           {{ dbt_utils.log_info(message_prefix ~ " Analyzing " ~ relation_to_vacuum) }}
           {% do run_query("ANALYZE " ~ relation_to_vacuum) %}
           {% set end=modules.datetime.datetime.now() %}
           {% set total_seconds = (end - start).total_seconds() | round(2)  %}
           {{ dbt_utils.log_info(message_prefix ~ " Finished " ~ relation_to_vacuum ~ " in " ~ total_seconds ~ "s") }}
       {% else %}
           {{ dbt_utils.log_info(message_prefix ~ ' Skipping relation "' ~ row.values() | join ('"."') ~ '" as it does not exist') }}
       {% endif %}
  
   {% endfor %}
  
{% endmacro %}

DBT Cloud

Има възможност да се ползва DBT като услуга (Managed Service). Включва:

Web IDE за разработка на проекти и модели
Конфигуриране на джобовете и настройка на график
Лесен и удобен достъп до логовете
Уебсайт с документация на вашия проект
Връзка с CI (Continuous Integration)

Заключение

Приготвянето и използването на DWH става толкова приятно и полезно, колкото и пиенето на смутита. DBT се състои от Jinja, потребителски разширения (модули), компилатор, изпълнител (executor) и мениджър на пакети. Събрали тези елементи заедно, получавате пълноценно работно средище за вашето Хранилище на Данни. Днес едва ли има по-добър начин за управление на трансформациите в DWH.

Убежденията, които следват разработчиците на DBT, се формулират така:

Кодът, а не GUI, е най-добрата абстракция за изразяване на сложна аналитична логика.
Работата с данни трябва да адаптира най-добрите практики за разработка на софтуер (Software Engineering).

Най-важната инфраструктура за работа с данни трябва да се контролира от общността на потребителите като софтуер с отворен код.
Не само аналитичните инструменти, но и кодът все по-често ще става част от общността с отворен код.

Тези основни убеждения създадоха продукт, който днес се използва в повече от 850 компании и те формират основата на много интересни разширения, които ще бъдат създадени в бъдеще.

За тези, които се интересуват, има видеозапис на открит урок, който проведох преди няколко месеца в рамките на открития урок в OTUS — Data Build Tool за хранилище Amazon Redshift..

В допълнение към DBT и Хранилищата на Данни, в рамките на курса Data Engineer на платформата OTUS, аз и моите колеги водим занятия по редица други актуални и съвременни теми: