На кои принципи е изграден идеален складиште за податоци?

Фокусирајте се на деловната вредност и аналитиката во отсуство на код за котел. Управување со DWH како база на кодови: верзии, преглед, автоматско тестирање и CI. Модуларен, проширлив, со отворен код и заедница. Корисничка документација и визуелизација на зависност (Data Lineage).

Повеќе за сето ова и за улогата на DBT во екосистемот Big Data & Analytics - добредојдовте во cat.

Здраво на сите

Артеми Козир е во контакт. Повеќе од 5 години работам со складишта на податоци, градење ETL/ELT, како и анализа на податоци и визуелизација. Моментално работам во тркаласт, предавам во ОТУС на курс Инженер за податоци, а денес сакам да споделам со вас една статија која ја напишав во пресрет на почетокот нови уписи за курсот.

Преглед

Рамката DBT е сè за T во кратенката ELT (Extract - Transform - Load).

Со доаѓањето на такви продуктивни и скалабилни аналитички бази на податоци како BigQuery, Redshift, Snowflake, немаше смисла да се прават трансформации надвор од Data Warehouse. 

DBT не презема податоци од извори, но дава одлични можности за работа со податоци што веќе се вчитани во Складирањето (во внатрешно или надворешно складирање).

Главната цел на DBT е да го земе кодот, да го компајлира во SQL, да ги изврши командите во правилната секвенца во складиштето.

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

Проектот се состои од директориуми и датотеки од само 2 типа:

• Модел (.sql) - единица на трансформација изразена со барање SELECT
• Конфигурациска датотека (.yml) - параметри, поставки, тестови, документација

На основно ниво, работата е структурирана на следниов начин:

• Корисникот подготвува модел код во кој било удобен IDE
• Користејќи го CLI, моделите се лансираат, DBT го компајлира кодот на моделот во SQL
• Компајлираниот SQL код се извршува во Storage во дадена низа (график)

Еве како може да изгледа трчањето од CLI:

Сè е SELECT

Ова е убиствена карактеристика на рамката на алатката за градење податоци. Со други зборови, DBT го апстрахира целиот код поврзан со материјализирање на вашите барања во продавницата (варијации од командите CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).

Секој модел вклучува пишување на едно барање SELECT што го дефинира добиениот сет на податоци.

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

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}
 
with orders as (
 
   select * from {{ ref('stg_orders') }}
 
),
 
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

Кои интересни работи можеме да ги видиме овде?

Прво: Користени CTE (Common Table Expressions) - за организирање и разбирање на код кој содржи многу трансформации и деловна логика

Второ: Моделниот код е мешавина од SQL и јазик Jinja (јазик на шаблон).

Примерот користи јамка за за генерирање на износот за секој начин на плаќање наведен во изразот постави. Се користи и функцијата РЕФ — можност за упатување на други модели во рамките на кодот:

• За време на компилацијата РЕФ ќе се конвертира во целен покажувач на табела или поглед во Storage
• РЕФ ви овозможува да изградите графикон за зависност од модел

Точно Jinja додава речиси неограничени можности на DBT. Најчесто користени се:

• Ако / друго изјави - изјави на гранка
• За јамки
• Променливи
• Макро - создавање макроа

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

Стратегијата за материјализација е пристап според кој добиениот сет на податоци за моделот ќе се складира во Storage.

Во основна смисла, тоа е:

• Табела - физичка табела во Складирањето
• Преглед - преглед, виртуелна табела во Storage

Постојат и посложени стратегии за материјализација:

• Инкрементално - инкрементално вчитување (на големи табели со факти); се додаваат нови линии, се ажурираат променетите линии, се бришат избришаните линии 
• Ефемерен - моделот не се материјализира директно, туку учествува како CTE во други модели
• Сите други стратегии што можете да ги додадете сами

Покрај стратегиите за материјализација, постојат можности за оптимизација за одредени складишта, на пример:

• Снегулка: минливи табели, однесување со спојување, групирање табели, копирање грантови, безбедни прегледи
• Redshift: Distkey, Sortkey (преплетено, сложено), Late Binding Views
• големо прашање: Поделба и групирање табели, однесување на спојување, шифрирање KMS, етикети и ознаки
• Свеќи: Формат на датотека (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy

Во моментов се поддржани следните складишта:

• Постгрес
• Redshift
• големо прашање
• Снегулка
• Престо (делумно)
• Искра (делумно)
• Microsoft SQL Server (адаптер на заедницата)

Ајде да го подобриме нашиот модел:

• Да го направиме неговото полнење поединечно (Порасно)
• Ајде да додадеме копчиња за сегментација и сортирање за 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 ви овозможува да тестирате голем број на претпоставки (тврдења) за добиениот сет на податоци, како што се:

• Не Нулта
• Уникатни Планови
• Референтен интегритет - референтен интегритет (на пример, customer_id во табелата со нарачки одговара на id во табелата за клиенти)
• Усогласување на листата на прифатливи вредности

Можно е да додадете свои тестови (прилагодени тестови на податоци), како што се, на пример, % отстапување на приходите со индикатори од пред еден ден, една недела, еден месец. Секоја претпоставка формулирана како SQL барање може да стане тест.

На овој начин, можете да ги фатите несаканите отстапувања и грешки во податоците во прозорците на Магацин.

Во однос на документацијата, DBT обезбедува механизми за додавање, верзија и дистрибуција на метаподатоци и коментари на нивоата на моделот, па дури и на атрибутите. 

Еве како изгледа додавањето тестови и документација на ниво на конфигурациска датотека:

 - name: fct_orders
   description: This table has basic information about orders, as well as some derived facts based on payments
   columns:
     - name: order_id
       tests:
         - unique # проверка на уникальность значений
         - not_null # проверка на наличие null
       description: This is a unique identifier for an order
     - name: customer_id
       description: Foreign key to the customers table
       tests:
         - not_null
         - relationships: # проверка ссылочной целостности
             to: ref('dim_customers')
             field: customer_id
     - name: order_date
       description: Date (UTC) that the order was placed
     - 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 доаѓа со менаџер на пакети кој им овозможува на корисниците да објавуваат и повторно да користат поединечни модули и макроа.

Ова значи можност да се вчитаат и користат библиотеките како што се:

• dbt_utils: работа со Date/Time, Surrogate Keys, Schema тестови, Pivot/Unpivot и други
• Готови витрински шаблони за услуги како што се Чистач на снег и Лента 
• Библиотеки за специфични продавници за податоци, на пр. Redshift 
• Влези — Модул за логирање на операција DBT

Комплетна листа на пакети може да се најде на dbt центар.

Уште повеќе карактеристики

Овде ќе опишам неколку други интересни карактеристики и имплементации кои тимот и јас ги користиме за да изградиме складиште на податоци во тркаласт.

Одвојување на околини за траење 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, тест, ци земајте податоци само за последните 3 дена и не повеќе. Односно, трчањето во овие средини ќе биде многу побрзо и ќе бара помалку ресурси. Кога работи на околина prod состојбата на филтерот ќе се игнорира.

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

Redshift е колонообразен DBMS кој ви овозможува да поставите алгоритми за компресија на податоци за секоја поединечна колона. Изборот на оптимални алгоритми може да го намали просторот на дискот за 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<String>,
                 dist_style=none|all|even,
                 dist_key=none|String) }}

Моделот за логирање работи

Можете да прикачите куки на секое извршување на моделот, што ќе се изврши пред лансирањето или веднаш по завршувањето на создавањето на моделот:

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

Модулот за евиденција ќе ви овозможи да ги снимите сите потребни метаподатоци во посебна табела, која последователно може да се користи за ревизија и анализа на тесните грла.

Вака изгледа контролната табла врз основа на податоците за евиденција во Looker:

Автоматизација на одржување на складиштето

Ако користите некои екстензии на функционалноста на користеното складиште, како што е UDF (Функции дефинирани од корисникот), тогаш верзиирањето на овие функции, контролата на пристапот и автоматското пуштање нови изданија е многу погодно за правење во DBT.

Ние користиме UDF во Python за да пресметаме хашови, домени за е-пошта и декодирање на битмаски.

Пример за макро што создава UDF на која било средина за извршување (dev, тест, прод):

{% 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

Можно е да се користи DBT како услуга (Управувана услуга). Вклучено:

• Web IDE за развој на проекти и модели
• Конфигурација и распоред на работа
• Едноставен и удобен пристап до дневниците
• Веб-страница со документација за вашиот проект
• Поврзување CI (континуирана интеграција)

Заклучок

Подготовката и консумирањето на DWH станува исто толку пријатно и корисно како и пиењето смути. DBT се состои од Jinja, кориснички екстензии (модули), компајлер, извршител и менаџер на пакети. Со спојување на овие елементи добивате целосна работна средина за вашиот складиште на податоци. Едвај има подобар начин за управување со трансформацијата во рамките на DWH денес.

Верувањата што ги следат развивачите на DBT се формулирани на следниов начин:

• Кодот, а не GUI, е најдобрата апстракција за изразување сложена аналитичка логика
• Работата со податоци треба да ги приспособи најдобрите практики во софтверското инженерство (Софтверско инженерство)

• Инфраструктурата на критични податоци треба да биде контролирана од заедницата на корисници како софтвер со отворен код
• Не само алатките за анализа, туку и кодот сè повеќе ќе станат сопственост на заедницата со отворен код

Овие основни верувања создадоа производ што го користат преку 850 компании денес, и тие ја формираат основата на многу возбудливи екстензии што ќе бидат создадени во иднина.

За заинтересираните има видео од отворена лекција која ја одржав пред неколку месеци како дел од отворена лекција во ОТУС - Алатка за градење податоци за складирање на Amazon Redshift.

Покрај DBT и Data Warehousing, како дел од курсот Data Engineer на платформата OTUS, јас и моите колеги одржуваме часови на голем број други релевантни и модерни теми:

• Архитектонски концепти за апликации за големи податоци
• Вежбајте со Spark и Spark Streaming
• Истражување на методи и алатки за вчитување на извори на податоци
• Изградба на аналитички витрини во DWH
• NoSQL концепти: HBase, Cassandra, ElasticSearch
• Принципи на мониторинг и оркестрација 
• Завршен проект: здружување на сите вештини под менторска поддршка

Референци:

1. DBT документација - Вовед — Официјална документација
2. Што точно е dbt? - Прегледајте ја статијата од еден од авторите на DBT 
3. Алатка за градење податоци за складирање на Amazon Redshift — YouTube, Снимање на отворена лекција OTUS
4. Запознавање со Гринплум — Следната отворена лекција е 15 мај 2020 година
5. Курс за инженерство на податоци - ОТУС
6. Градење работен тек на зрели анализи - Поглед на иднината на податоците и аналитиката
7. Време е за аналитика со отворен код — Еволуцијата на аналитиката и влијанието на отворениот код
8. Континуирана интеграција и автоматско тестирање за градење со dbtCloud — Принципи на градење CI користејќи DBT
9. Започнуваме со упатство за DBT — Вежба, чекор-по-чекор инструкции за самостојна работа
10. Jaffle shop — Упатство за Github DBT — Github, код за едукативен проект

Дознајте повеќе за курсот.

Извор: www.habr.com