På hvilke principper er et ideelt datavarehus bygget?
Fokus på forretningsværdi og analyser i fravær af standardkode. Håndtering af DWH som en kodebase: versionering, gennemgang, automatiseret test og CI. Modulær, udvidelig, open source og fællesskab. Brugervenlig dokumentation og afhængighedsvisualisering (Data Lineage).
Mere om alt dette og om DBT's rolle i Big Data & Analytics-økosystemet - velkommen til kat.
Hej alle
Artemy Kozyr er i kontakt. I mere end 5 år har jeg arbejdet med datavarehuse, opbygning af ETL/ELT, samt dataanalyse og visualisering. Jeg arbejder i øjeblikket i , jeg underviser på OTUS på et kursus , og i dag vil jeg dele en artikel med jer, som jeg skrev i forventning om starten ny tilmelding til kurset.
Kort gennemgang
DBT-rammen handler om T'et i ELT (Extract - Transform - Load) akronymet.
Med fremkomsten af så produktive og skalerbare analytiske databaser som BigQuery, Redshift, Snowflake var der ingen mening i at lave transformationer uden for Data Warehouse.
DBT henter ikke data fra kilder, men giver gode muligheder for at arbejde med data, der allerede er indlæst i Storaget (i Internt eller Eksternt Storage).
Hovedformålet med DBT er at tage koden, kompilere den til SQL, udføre kommandoerne i den rigtige rækkefølge i Repository.
DBT projektstruktur
Projektet består af mapper og filer af kun 2 typer:
- Model (.sql) - en transformationsenhed udtrykt ved en SELECT-forespørgsel
- Konfigurationsfil (.yml) - parametre, indstillinger, test, dokumentation
På et grundlæggende niveau er arbejdet struktureret som følger:
- Brugeren forbereder modelkode i enhver praktisk IDE
- Ved hjælp af CLI lanceres modeller, DBT kompilerer modelkoden til SQL
- Den kompilerede SQL-kode udføres i Storage i en given rækkefølge (graf)
Sådan kan det se ud at køre fra CLI:
Alt er SELECT
Dette er en dræber funktion i Data Build Tool-rammen. Med andre ord abstraherer DBT al den kode, der er forbundet med at materialisere dine forespørgsler i butikken (variationer fra kommandoerne CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Enhver model involverer at skrive én SELECT-forespørgsel, der definerer det resulterende datasæt.
I dette tilfælde kan transformationslogikken være på flere niveauer og konsolidere data fra flere andre modeller. Et eksempel på en model, der vil bygge en ordreudstilling (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
Hvilke interessante ting kan vi se her?
Først: Brugt CTE (Common Table Expressions) - til at organisere og forstå kode, der indeholder en masse transformationer og forretningslogik
For det andet: Modelkode er en blanding af SQL og sprog (skabelonsprog).
Eksemplet bruger en loop forum for at generere beløbet for hver betalingsmetode, der er angivet i udtrykket sæt. Funktionen bruges også ref — evnen til at henvise til andre modeller i koden:
- Under kompilering ref vil blive konverteret til en målpeger til en tabel eller visning i Storage
- ref giver dig mulighed for at bygge en modelafhængighedsgraf
Præcis tilføjer næsten ubegrænsede muligheder til DBT. De mest brugte er:
- If/else-udsagn - filialudsagn
- Til sløjfer
- Variabler
- Makro - oprettelse af makroer
Materialisering: tabel, udsigt, inkrementel
Materialiseringsstrategi er en tilgang, hvorefter det resulterende sæt af modeldata vil blive lagret i Storage.
Grundlæggende er det:
- Bord - fysisk bord i Lageret
- View - view, virtuel tabel i Storage
Der er også mere komplekse materialiseringsstrategier:
- Inkrementel - trinvis belastning (af store faktatabeller); nye linjer tilføjes, ændrede linjer opdateres, slettede linjer ryddes
- Ephemeral - modellen materialiserer sig ikke direkte, men deltager som CTE i andre modeller
- Eventuelle andre strategier, du selv kan tilføje
Ud over materialiseringsstrategier er der muligheder for optimering af specifikke Storages, for eksempel:
- Snowflake: Forbigående tabeller, fletteadfærd, tabelklynger, kopieringsbevillinger, sikre visninger
- rødforskydning: Distkey, Sortkey (interleaved, sammensat), Late Binding Views
- BigQuery: Tabelopdeling og -klynger, fletteadfærd, KMS-kryptering, etiketter og tags
- Spark: Filformat (parket, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
Følgende lager understøttes i øjeblikket:
- PostgreSQL
- rødforskydning
- BigQuery
- Snowflake
- Presto (delvist)
- Gnist (delvis)
- Microsoft SQL Server (fællesskabsadapter)
Lad os forbedre vores model:
- Lad os gøre dens påfyldning trinvis (inkrementel)
- Lad os tilføje segmenterings- og sorteringsnøgler til 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
Model afhængighed graf
Det er også et afhængighedstræ. Det er også kendt som DAG (Directed Acyclic Graph).
DBT bygger en graf baseret på konfigurationen af alle projektmodeller, eller rettere ref() links i modeller til andre modeller. At have en graf giver dig mulighed for at gøre følgende:
- Køre modeller i den rigtige rækkefølge
- Parallelisering af butiksfacadedannelse
- Kørsel af en vilkårlig undergraf
Eksempel på grafvisualisering:
Hver knude på grafen er en model; kanterne på grafen er specificeret med udtrykket ref.
Datakvalitet og dokumentation
Ud over at generere selve modellerne giver DBT dig mulighed for at teste en række antagelser om det resulterende datasæt, såsom:
- Ikke Nul
- Enestående
- Referenceintegritet - referenceintegritet (f.eks. svarer kunde_id i ordretabellen til id i kundetabellen)
- Matcher listen over acceptable værdier
Det er muligt at tilføje egne tests (brugerdefinerede datatests), som fx % afvigelse af omsætning med indikatorer fra en dag, en uge, en måned siden. Enhver antagelse formuleret som en SQL-forespørgsel kan blive en test.
På den måde kan du fange uønskede afvigelser og fejl i data i Lagervinduerne.
Med hensyn til dokumentation leverer DBT mekanismer til tilføjelse, versionering og distribution af metadata og kommentarer på model- og endda attributniveauer.
Sådan ser tilføjelse af test og dokumentation ud på konfigurationsfilniveau:
- 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']
Og her er, hvordan denne dokumentation ser ud på det genererede websted:
Makroer og moduler
Formålet med DBT er ikke så meget at blive et sæt SQL-scripts, men at give brugerne et kraftfuldt og funktionsrigt middel til at bygge deres egne transformationer og distribuere disse moduler.
Makroer er sæt af konstruktioner og udtryk, der kan kaldes som funktioner inden for modeller. Makroer giver dig mulighed for at genbruge SQL mellem modeller og projekter i overensstemmelse med DRY (Don't Repeat Yourself) princippet.
Makro eksempel:
{% 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 %}
Og dens anvendelser:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT kommer med en pakkehåndtering, der giver brugerne mulighed for at publicere og genbruge individuelle moduler og makroer.
Dette betyder at være i stand til at indlæse og bruge biblioteker som:
- : arbejde med dato/tid, surrogatnøgler, skematest, pivot/unpivot og andre
- Færdiglavede udstillingsskabeloner til tjenester som f.eks и
- Biblioteker til specifikke datalagre, f.eks.
- — Modul til logning af DBT-drift
En komplet liste over pakker kan findes på .
Endnu flere funktioner
Her vil jeg beskrive et par andre interessante funktioner og implementeringer, som teamet og jeg bruger til at bygge et datavarehus i .
Adskillelse af runtime-miljøer DEV - TEST - PROD
Selv inden for den samme DWH-klynge (inden for forskellige ordninger). For eksempel ved at bruge følgende udtryk:
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 -%}
)
Denne kode siger bogstaveligt: for miljøer dev, test, ci tage kun data for de sidste 3 dage og ikke mere. Det vil sige, at kørsel i disse miljøer vil være meget hurtigere og kræve færre ressourcer. Når du kører på miljø prod filterbetingelsen vil blive ignoreret.
Materialisering med alternativ kolonnekodning
Redshift er en kolonneformet DBMS, der giver dig mulighed for at indstille datakomprimeringsalgoritmer for hver enkelt kolonne. Valg af optimale algoritmer kan reducere diskplads med 20-50 %.
Makro vil udføre kommandoen ANALYSE COMPRESSION, oprette en ny tabel med de anbefalede kolonnekodningsalgoritmer, specificerede segmenteringsnøgler (dist_key) og sorteringsnøgler (sort_key), overføre dataene til den, og om nødvendigt slette den gamle kopi.
Makro signatur:
{{ 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) }}
Logningsmodellen kører
Du kan fastgøre kroge til hver udførelse af modellen, som vil blive udført før lanceringen eller umiddelbart efter oprettelsen af modellen er afsluttet:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Logningsmodulet giver dig mulighed for at registrere alle nødvendige metadata i en separat tabel, som efterfølgende kan bruges til at auditere og analysere flaskehalse.
Sådan ser dashboardet ud baseret på logdata i Looker:
Automatisering af lagervedligeholdelse
Hvis du bruger nogle udvidelser af funktionaliteten af det brugte lager, såsom UDF (User Defined Functions), så er versionering af disse funktioner, adgangskontrol og automatiseret udrulning af nye udgivelser meget praktisk at gøre i DBT.
Vi bruger UDF i Python til at beregne hashes, e-mail-domæner og bitmask-afkodning.
Et eksempel på en makro, der opretter en UDF på et hvilket som helst eksekveringsmiljø (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 %}
Hos Wheely bruger vi Amazon Redshift, som er baseret på PostgreSQL. For Redshift er det vigtigt jævnligt at indsamle statistik på tabeller og frigøre diskplads – henholdsvis ANALYSE og VACUUM kommandoerne.
For at gøre dette udføres kommandoerne fra makroen redshift_maintenance hver nat:
{% 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
Det er muligt at bruge DBT som en service (Managed Service). Inkluderet:
- Web-IDE til udvikling af projekter og modeller
- Jobkonfiguration og planlægning
- Enkel og bekvem adgang til logfiler
- Hjemmeside med dokumentation af dit projekt
- Connecting CI (Continuous Integration)
Konklusion
At forberede og indtage DWH bliver lige så behageligt og gavnligt som at drikke en smoothie. DBT består af Jinja, brugerudvidelser (moduler), en compiler, en eksekvering og en pakkehåndtering. Ved at sætte disse elementer sammen får du et komplet arbejdsmiljø for dit datavarehus. Der findes næppe en bedre måde at styre transformation på inden for DWH i dag.
De overbevisninger, som udviklerne af DBT følger, er formuleret som følger:
- Kode, ikke GUI, er den bedste abstraktion til at udtrykke kompleks analytisk logik
- Arbejde med data bør tilpasse bedste praksis inden for software engineering (Software Engineering)
- Kritisk datainfrastruktur bør kontrolleres af brugerfællesskabet som open source-software
- Ikke kun analyseværktøjer, men også kode vil i stigende grad blive Open Source-fællesskabets ejendom
Disse kerneoverbevisninger har affødt et produkt, der bruges af over 850 virksomheder i dag, og de danner grundlaget for mange spændende udvidelser, der vil blive skabt i fremtiden.
For de interesserede er der en video af en åben lektion, jeg gav for et par måneder siden som en del af en åben lektion på OTUS - .
Ud over DBT og Data Warehousing underviser mine kolleger og jeg som en del af Data Engineer-kurset på OTUS-platformen i en række andre relevante og moderne emner:
- Arkitektoniske koncepter for Big Data-applikationer
- Øv med Spark og Spark Streaming
- Udforskning af metoder og værktøjer til indlæsning af datakilder
- Opbygning af analytiske udstillingsvinduer i DWH
- NoSQL-koncepter: HBase, Cassandra, ElasticSearch
- Principper for overvågning og orkestrering
- Afsluttende projekt: at samle alle færdigheder under mentorstøtte
referencer:
- — Officiel dokumentation
- — Anmeldelsesartikel af en af forfatterne til DBT
- — YouTube, Optagelse af en åben OTUS-lektion
- — Næste åbne lektion er den 15. maj 2020
- —OTUS
- — Et kig på fremtiden for data og analyser
- — Udviklingen af analyser og indflydelsen fra Open Source
- — Principper for opbygning af CI ved hjælp af DBT
- — Øvelse, Trin-for-trin instruktioner til selvstændigt arbejde
- — Github, uddannelsesprojektkode
Kilde: www.habr.com