На якіх прынцыпах будуецца ідэальнае Сховішча Дадзеных?
Фокус на бізнес-каштоўнасці і аналітыцы пры адсутнасці boilerplate code. Кіраванне DWH як кодавай базай: версіяванне, рэўю, аўтаматычнае тэсціраванне і CI. Модульнасць, пашыральнасць, адкрыты зыходны код і супольнасць. Дружалюбная карыстацкая дакументацыя і візуалізацыя залежнасцяў (Data Lineage).
Аб усім гэтым падрабязней і аб ролі DBT у экасістэме Big Data & Analytics – сардэчна запрашаем пад кат.
Ўсім прывітанне
На сувязі Арцемій Козыр. Ужо больш за 5 гадоў я працую са сховішчамі дадзеных, займаюся пабудовай ETL/ELT, а таксама аналітыкай дадзеных і візуалізацыяй. У цяперашні час я працую ў , выкладаю ў OTUS на курсе , і сёння хачу падзяліцца з вамі артыкулам, які я напісаў напярэдадні старту новага набору на курс.
Кароткі агляд
Фрэймворк DBT – гэта ўсё пра літару T у акроніме ELT (Extract – Transform – Load).
З з'яўленнем такіх прадукцыйных і якія маштабуюцца аналітычных баз дадзеных як BigQuery, Redshift, Snowflake, знік які-небудзь сэнс рабіць трансфармацыі па-за Сховішчы Дадзеных.
DBT не выгружае дадзеныя з крыніц, але дае велізарныя магчымасці па працы з тымі дадзенымі, якія ўжо загружаны ў Сховішча (у Internal ці External Storage).
Асноўнае прызначэнне DBT - узяць код, скампіляваць яго ў SQL, выканаць каманды ў правільнай паслядоўнасці ў Сховішча.
Структура праекту DBT
Праект складаецца з дырэкторый і файлаў усяго 2-х тыпаў:
- Мадэль (.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'] %}
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 і мовы (templating language).
У прыкладзе выкарыстаны цыкл для для фарміравання сумы па кожным метадзе плацяжу, указаным у выразе камплект. Таксама выкарыстоўваецца функцыя спасылка - магчымасць спасылацца ўнутры кода на іншыя мадэлі:
- У час кампіляцыі спасылка будзе пераўтвораны ў мэтавы паказальнік на табліцу або прадстаўленне ў Сховішча
- спасылка дазваляе пабудаваць граф залежнасцяў мадэляў
Менавіта дадае ў DBT амаль неабмежаваныя магчымасці. Найбольш часта выкарыстоўваюцца з іх:
- If / else statements - аператары галінавання
- For loops - цыклы
- Variables - зменныя
- Macro - стварэнне макрасаў
Матэрыялізацыя: Table, View, Incremental
Стратэгія Матэрыялізацыі - падыход, паводле якога выніковы набор дадзеных мадэлі будзе захаваны ў Сховішча.
У базавым разглядзе гэта:
- Table - фізічная табліца ў Сховішча
- View - прадстаўленне, віртуальная табліца ў Сховішча
Ёсць і больш складаныя стратэгіі матэрыялізацыі:
- Incremental - інкрыментальная загрузка (вялікіх табліц фактаў); новыя радкі дадаюцца, змененыя - абнаўляюцца, выдаленыя - вычышчаюцца
- Ephemeral - мадэль не матэрыялізуецца напрамую, але ўдзельнічае як CTE ў іншых мадэлях
- Любыя іншыя стратэгіі, якія вы можаце дадаць самастойна
У дадатак да стратэгій матэрыялізацыі адкрываюцца магчымасці для аптымізацыі пад канкрэтныя Сховішчы, напрыклад:
- Сняжынка: Transient tables, Merge behavior, Table clustering, Copying grants, Secure views
- Redshift: Distkey, Sortkey (interleaved, compound), Late Binding Views
- BigQuery: Table partitioning & clustering, Merge behavior, KMS Encryption, Labels & Tags
- Іскрыцца: File format (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
На бягучы момант падтрымліваюцца наступныя Сховішчы:
- Postgres
- Redshift
- BigQuery
- Сняжынка
- Presto (часткова)
- Spark (часткова)
- Microsoft SQL Server (кам'юніці адаптар)
Давайце ўдасканалім нашу мадэль:
- Зробім яе напаўненне інкрыментальным (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
- Унікальны
- Reference Integrity - спасылачная цэласнасць (напрыклад, customer_id у табліцы orders адпавядае id у табліцы customers)
- Адпаведнасць спісу дапушчальных значэнняў
Магчыма даданне сваіх тэстаў (custom data tests), такіх як, напрыклад, % адхіленні выручкі з паказчыкамі дзень, тыдзень, месяц таму. Любая здагадка, сфармуляванае ў выглядзе 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 (Don't Repeat Yourself).
Прыклад макраса:
{% 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), які дазваляе карыстальнікам публікаваць і перавыкарыстоўваць асобныя модулі і макрасы.
Гэта азначае магчымасць загрузіць і выкарыстоўваць такія бібліятэкі як:
- : праца з Date/Time,Surrogate Keys, Schema tests, Pivot/Unpivot і іншыя
- Гатовыя шаблоны вітрын для такіх сэрвісаў як и
- Бібліятэкі для пэўных Сховішчаў Дадзеных, напрыклад
- - Модуль для лагавання працы 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, test, ci вазьмі дадзеныя толькі за апошнія 3 дні і не больш за. Гэта значыць прагон у гэтых асяроддзях будзе значна хутчэй і запатрабуе менш рэсурсаў. Пры запуску на асяроддзі prod ўмова фільтра будзе праігнаравана.
Матэрыялізацыя з альтэрнатыўным кадаваннем слупкоў
Redshift - калоначная СКБД, якая дазваляе задаваць алгарытмы кампрэсіі дадзеных для кожнай асобнай калонкі. Выбар аптымальных алгарытмаў можа скараціць займаны аб'ём на дыску на 20-50%.
макрас выканае каманду 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) }}
Лагіраванне запускаў мадэляў
На кожнае выкананне мадэлі можна павесіць хукі (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)
- Найважнейшая інфраструктура па працы з дадзенымі павінна кантралявацца супольнасцю карыстачоў як праграмнае забеспячэнне з адчыненым зыходным кодам
- Не толькі інструменты аналітыкі, але і код усё часцей будзе станавіцца здабыткам супольнасці Open Source
Гэтыя асноўныя перакананні спарадзілі прадукт, які сёння выкарыстоўваецца ў больш за 850 кампаніях, і яны складаюць аснову шматлікіх цікавых пашырэнняў, якія будуць створаны ў будучыні.
Для тых, хто зацікавіўся, ёсць відэазапіс адкрытага ўрока, які я правёў некалькі месяцаў таму ў рамках адкрытага ўрока ў OTUS. .
Апроч DBT і Сховішчаў Дадзеных, у рамках курса Data Engineer на платформе OTUS, я і мае калегі вядзем заняткі па шэрагу іншых актуальных і сучасных тэм:
- Архітэктурныя канцэпцыі прыкладанняў Вялікіх Даных
- Практыка са Spark і Spark Streaming
- Вывучэнне спосабаў і інструментаў загрузкі крыніц даных
- Пабудова аналітычных вітрын у DWH
- Канцэпцыі NoSQL: HBase, Cassandra, ElasticSearch
- Прынцыпы арганізацыі маніторынгу і аркестрацыі
- Фінальны Праект: збіраем усе скілы разам пад ментарскай падтрымкай
спасылкі:
- - Афіцыйная дакументацыя
- - Аглядны артыкул аднаго з аўтараў DBT
- - YouTube, Запіс адкрытага ўрока OTUS
- - Бліжэйшы адкрыты ўрок 15 мая 2020
- - OTUS
- - Погляд на будучыню працы з дадзенымі і аналітыку
- - Эвалюцыя аналітыкі і ўплыў Open Source
- - Прынцыпы пабудова CI з выкарыстаннем DBT
- - Практыка, Пакрокавыя інструкцыі для самастойнай працы
- - Github, код навучальнага праекта
Крыніца: habr.com