Na jakých principech je postaven ideální datový sklad?
Zaměřte se na obchodní hodnotu a analýzu při absenci standardního kódu. Správa DWH jako kódové základny: verzování, revize, automatické testování a CI. Modulární, rozšiřitelný, open source a komunitní. Uživatelsky přívětivá dokumentace a vizualizace závislostí (Data Lineage).
Více o tom všem a o roli DBT v ekosystému Big Data & Analytics – vítejte v kat.
Ahoj všichni
Artemy Kozyr je v kontaktu. Více než 5 let pracuji s datovými sklady, budováním ETL/ELT a také datovou analytikou a vizualizací. Momentálně pracuji v , učím na OTUS na kurzu , a dnes se s vámi chci podělit o článek, který jsem napsal v očekávání startu nový zápis do kurzu.
Krátká recenze
Rámec DBT je celý o T ve zkratce ELT (Extract - Transform - Load).
S příchodem tak produktivních a škálovatelných analytických databází jako BigQuery, Redshift, Snowflake nemělo smysl provádět transformace mimo Data Warehouse.
DBT nestahuje data ze zdrojů, ale poskytuje skvělé příležitosti pro práci s daty, která již byla načtena do úložiště (v interním nebo externím úložišti).
Hlavním účelem DBT je vzít kód, zkompilovat ho do SQL, provést příkazy ve správném pořadí v úložišti.
Struktura projektu DBT
Projekt se skládá z adresářů a souborů pouze 2 typů:
- Model (.sql) - jednotka transformace vyjádřená SELECT dotazem
- Konfigurační soubor (.yml) - parametry, nastavení, testy, dokumentace
Na základní úrovni je práce strukturována takto:
- Uživatel připraví kód modelu v libovolném vhodném IDE
- Pomocí CLI se spouštějí modely, DBT zkompiluje kód modelu do SQL
- Zkompilovaný kód SQL je spuštěn v úložišti v daném pořadí (grafu)
Spouštění z CLI může vypadat následovně:
Vše je SELECT
Toto je zabijácká funkce rámce Data Build Tool. Jinými slovy, DBT abstrahuje veškerý kód spojený s materializací vašich dotazů do Store (variace od příkazů CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Jakýkoli model zahrnuje napsání jednoho dotazu SELECT, který definuje výslednou datovou sadu.
V tomto případě může být transformační logika víceúrovňová a konsolidovat data z několika dalších modelů. Příklad modelu, který vytvoří ukázku objednávek (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
Co zajímavého zde můžeme vidět?
Za prvé: Používá se CTE (Common Table Expressions) – k uspořádání a pochopení kódu, který obsahuje spoustu transformací a obchodní logiku
Za druhé: Kód modelu je směsí SQL a jazyka (jazyk šablon).
Příklad používá smyčku for vygenerovat částku pro každou platební metodu uvedenou ve výrazu sada. Funkce se také používá ref — schopnost odkazovat na jiné modely v rámci kódu:
- Během kompilace ref bude převeden na cílový ukazatel na tabulku nebo pohled v Úložišti
- ref umožňuje sestavit graf závislosti modelu
Přesně přidává téměř neomezené možnosti k DBT. Nejčastěji používané jsou:
- Příkazy If / else - příkazy větve
- Pro smyčky
- Proměnné
- Makro - vytváření maker
Materializace: Tabulka, Pohled, Přírůstková
Materializační strategie je přístup, podle kterého bude výsledná sada modelových dat uložena v Úložišti.
V základních termínech je:
- Tabulka - fyzická tabulka v Úložišti
- View - pohled, virtuální tabulka v Storage
Existují také složitější strategie materializace:
- Incremental - přírůstkové načítání (velkých tabulek faktů); jsou přidány nové řádky, změněné řádky jsou aktualizovány, odstraněné řádky jsou vymazány
- Pomíjivý - model se nezhmotňuje přímo, ale účastní se jako CTE v jiných modelech
- Jakékoli další strategie můžete přidat sami
Kromě strategií materializace existují příležitosti pro optimalizaci pro konkrétní úložiště, například:
- Sněhová vločka: Přechodné tabulky, chování při slučování, shlukování tabulek, kopírování grantů, zabezpečené pohledy
- Redshift: Distkey, Sortkey (prokládaný, složený), pohledy pozdní vazby
- BigQuery: Dělení a shlukování tabulek, chování při slučování, šifrování KMS, štítky a značky
- Jiskra: Formát souboru (parkety, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
V současné době jsou podporována následující úložiště:
- postgres
- Redshift
- BigQuery
- Sněhová vločka
- Presto (částečně)
- Jiskra (částečně)
- Microsoft SQL Server (komunitní adaptér)
Pojďme vylepšit náš model:
- Udělejme jeho plnění přírůstkové (Incremental)
- Přidejme segmentační a třídicí klíče pro 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
Graf závislosti modelu
Je to také strom závislostí. Je také známý jako DAG (Directed Acyclic Graph).
DBT vytváří graf na základě konfigurace všech modelů projektu, nebo spíše odkazů ref() v rámci modelů na jiné modely. S grafem můžete dělat následující věci:
- Spuštění modelů ve správném pořadí
- Paralelizace tvorby výlohy
- Spuštění libovolného podgrafu
Příklad vizualizace grafu:
Každý uzel grafu je model, okraje grafu jsou specifikovány výrazem ref.
Kvalita dat a dokumentace
Kromě generování samotných modelů vám DBT umožňuje testovat řadu předpokladů o výsledném souboru dat, jako například:
- Nenulový
- Unikátní
- Referenční integrita – referenční integrita (například customer_id v tabulce orders odpovídá id v tabulce customers)
- Odpovídající seznamu přijatelných hodnot
Je možné přidat vlastní testy (testy vlastních dat), jako je například % odchylka tržeb s ukazateli před dnem, týdnem, měsícem. Testem se může stát jakýkoli předpoklad formulovaný jako SQL dotaz.
Tímto způsobem můžete zachytit nežádoucí odchylky a chyby v datech v oknech Skladu.
Pokud jde o dokumentaci, DBT poskytuje mechanismy pro přidávání, verzování a distribuci metadat a komentářů na úrovni modelu a dokonce i atributů.
Takto vypadá přidávání testů a dokumentace na úrovni konfiguračního souboru:
- 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']
A takto vypadá tato dokumentace na vygenerovaném webu:
Makra a moduly
Účelem DBT není ani tak stát se sadou SQL skriptů, ale poskytnout uživatelům výkonné a na funkce bohaté prostředky pro vytváření vlastních transformací a distribuci těchto modulů.
Makra jsou sady konstrukcí a výrazů, které lze v rámci modelů volat jako funkce. Makra umožňují opětovné použití SQL mezi modely a projekty v souladu s principem DRY (Don't Repeat Yourself) inženýrství.
Příklad makra:
{% 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 %}
A jeho použití:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT přichází se správcem balíčků, který uživatelům umožňuje publikovat a znovu používat jednotlivé moduly a makra.
To znamená, že je možné načíst a používat knihovny, jako jsou:
- : práce s datem/časem, náhradními klíči, testy schémat, Pivot/Unpivot a dalšími
- Hotové šablony vitrín pro služby jako např и
- Knihovny pro konkrétní úložiště dat, např.
- — Modul pro záznam provozu DBT
Kompletní seznam balíčků naleznete na .
Ještě více funkcí
Zde popíšu pár dalších zajímavých funkcí a implementací, které tým a já používáme k vybudování datového skladu .
Oddělení runtime prostředí DEV - TEST - PROD
Dokonce i v rámci stejného clusteru DWH (v rámci různých schémat). Například pomocí následujícího výrazu:
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 -%}
)
Tento kód doslova říká: pro prostředí dev, test, ci brát data pouze za poslední 3 dny a ne více. To znamená, že běh v těchto prostředích bude mnohem rychlejší a bude vyžadovat méně prostředků. Při běhu v prostředí prod podmínka filtru bude ignorována.
Materializace s alternativním kódováním sloupců
Redshift je sloupcový DBMS, který vám umožňuje nastavit algoritmy komprese dat pro každý jednotlivý sloupec. Výběr optimálních algoritmů může snížit místo na disku o 20–50 %.
Makro provede příkaz ANALYZE COMPRESSION, vytvoří novou tabulku s doporučenými algoritmy kódování sloupců, zadanými segmentačními klíči (dist_key) a třídicími klíči (sort_key), přenese do ní data a v případě potřeby smaže starou kopii.
Makro podpis:
{{ 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) }}
Model protokolování běží
Ke každému provedení modelu můžete připevnit háčky, které budou provedeny před spuštěním nebo ihned po dokončení vytvoření modelu:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Logovací modul vám umožní zaznamenávat všechna potřebná metadata do samostatné tabulky, kterou lze následně použít k auditu a analýze úzkých míst.
Takto vypadá řídicí panel na základě protokolovaných dat v aplikaci Looker:
Automatizace údržby úložiště
Pokud používáte některá rozšíření funkčnosti použitého úložiště, jako je UDF (User Defined Functions), pak je v DBT velmi pohodlné vytváření verzí těchto funkcí, řízení přístupu a automatizované zavádění nových verzí.
UDF používáme v Pythonu k výpočtu hashů, e-mailových domén a dekódování bitové masky.
Příklad makra, které vytváří UDF v libovolném prováděcím prostředí (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 %}
Ve Wheely používáme Amazon Redshift, který je založen na PostgreSQL. Pro Redshift je důležité pravidelně sbírat statistiky o tabulkách a uvolňovat místo na disku – příkazy ANALYZE a VACUUM, resp.
K tomu se každou noc provádějí příkazy z makra 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 je možné používat jako službu (Managed Service). Zahrnuta:
- Web IDE pro vývoj projektů a modelů
- Konfigurace a plánování práce
- Jednoduchý a pohodlný přístup k protokolům
- Web s dokumentací vašeho projektu
- Připojení CI (průběžná integrace)
Závěr
Příprava a konzumace DWH se stává stejně příjemnou a prospěšnou jako pití smoothie. DBT se skládá z Jinja, uživatelských rozšíření (modulů), kompilátoru, exekutoru a správce balíčků. Spojením těchto prvků dohromady získáte kompletní pracovní prostředí pro svůj datový sklad. Sotva dnes existuje lepší způsob, jak řídit transformaci v rámci DWH.
Názory, kterými se vývojáři DBT řídí, jsou formulována následovně:
- Kód, nikoli GUI, je nejlepší abstrakcí pro vyjádření složité analytické logiky
- Práce s daty by měla přizpůsobit osvědčené postupy v softwarovém inženýrství (Software Engineering)
- Kritická datová infrastruktura by měla být řízena komunitou uživatelů jako software s otevřeným zdrojovým kódem
- Nejen analytické nástroje, ale i kód se budou stále více stávat majetkem komunity Open Source
Tato základní přesvědčení dala vzniknout produktu, který dnes používá více než 850 společností, a tvoří základ mnoha vzrušujících rozšíření, která budou vytvořena v budoucnu.
Pro zájemce je zde video z otevřené lekce, kterou jsem před pár měsíci vedl v rámci otevřené lekce na OTUS - .
Kromě DBT a Data Warehousingu v rámci kurzu Data Engineer na platformě OTUS vedeme s kolegy kurzy řady dalších relevantních a moderních témat:
- Architektonické koncepty pro aplikace velkých dat
- Cvičte se Sparkem a Spark Streamingem
- Zkoumání metod a nástrojů pro načítání datových zdrojů
- Budování analytických vitrín v DWH
- NoSQL koncepty: HBase, Cassandra, ElasticSearch
- Principy monitorování a orchestrace
- Závěrečný projekt: spojení všech dovedností pod mentorskou podporou
Odkazy:
- — Úřední dokumentace
- — Přehledový článek jednoho z autorů DBT
- — YouTube, Záznam otevřené lekce OTUS
- — Další otevřená lekce je 15. května 2020
- —OTUS
- — Pohled na budoucnost dat a analytiky
- — Vývoj analytiky a vliv Open Source
- — Principy budování CI pomocí DBT
- — Cvičení, Pokyny pro samostatnou práci krok za krokem
- — Github, kód vzdělávacího projektu
Zdroj: www.habr.com