På hvilke prinsipper bygges et ideelt datavarehus?
Fokuser på forretningsverdi og analyse i fravær av standardkode. Administrere DWH som en kodebase: versjonering, gjennomgang, automatisert testing og CI. Modulær, utvidbar, åpen kildekode og fellesskap. Brukervennlig dokumentasjon og avhengighetsvisualisering (Data Lineage).
Mer om alt dette og om rollen til DBT i Big Data & Analytics-økosystemet - velkommen til kat.
hei alle sammen
Artemy Kozyr tar kontakt. I mer enn 5 år har jeg jobbet med datavarehus, bygging av ETL/ELT, samt dataanalyse og visualisering. Jeg jobber for tiden i , jeg underviser på OTUS på et kurs , og i dag vil jeg dele en artikkel med dere som jeg skrev i påvente av starten ny påmelding til kurset.
Synopsis
DBT-rammeverket handler om T i ELT (Extract - Transform - Load) akronym.
Med bruken av så produktive og skalerbare analytiske databaser som BigQuery, Redshift, Snowflake, var det ingen vits i å gjøre transformasjoner utenfor datavarehuset.
DBT laster ikke ned data fra kilder, men gir store muligheter for å jobbe med data som allerede er lastet inn i Storage (i Intern eller Ekstern Storage).
Hovedformålet med DBT er å ta koden, kompilere den til SQL, utføre kommandoene i riktig rekkefølge i Repository.
DBT prosjektstruktur
Prosjektet består av kataloger og filer av kun 2 typer:
- Model (.sql) - en transformasjonsenhet uttrykt av en SELECT-spørring
- Konfigurasjonsfil (.yml) - parametere, innstillinger, tester, dokumentasjon
På et grunnleggende nivå er arbeidet strukturert som følger:
- Brukeren forbereder modellkode i en hvilken som helst praktisk IDE
- Ved å bruke CLI lanseres modeller, DBT kompilerer modellkoden til SQL
- Den kompilerte SQL-koden kjøres i Storage i en gitt sekvens (graf)
Slik kan kjøring fra CLI se ut:
Alt er SELECT
Dette er en fantastisk funksjon i Data Build Tool-rammeverket. Med andre ord abstraherer DBT all koden som er knyttet til å materialisere forespørslene dine i butikken (variasjoner fra kommandoene CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Enhver modell innebærer å skrive en SELECT-spørring som definerer det resulterende datasettet.
I dette tilfellet kan transformasjonslogikken være på flere nivåer og konsolidere data fra flere andre modeller. Et eksempel på en modell som vil bygge en ordreutstilling (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: Brukte CTE (Common Table Expressions) - for å organisere og forstå kode som inneholder mange transformasjoner og forretningslogikk
For det andre: Modellkode er en blanding av SQL og språk (malspråk).
Eksemplet bruker en loop forum for å generere beløpet for hver betalingsmetode spesifisert i uttrykket sett. Funksjonen brukes også ref — muligheten til å referere til andre modeller i koden:
- Under kompilering ref vil bli konvertert til en målpeker til en tabell eller visning i Storage
- ref lar deg bygge en modellavhengighetsgraf
Nøyaktig gir nesten ubegrensede muligheter til DBT. De mest brukte er:
- If / else statements - branch statements
- For løkker
- Variabler
- Makro - lage makroer
Materialisering: Tabell, Visning, Inkrementell
Materialiseringsstrategi er en tilnærming der det resulterende settet med modelldata vil bli lagret i lagringen.
I grunnleggende termer er det:
- Bord - fysisk bord i Lagret
- Vis - visning, virtuell tabell i lagring
Det er også mer komplekse materialiseringsstrategier:
- Inkrementell - inkrementell lasting (av store faktatabeller); nye linjer legges til, endrede linjer oppdateres, slettede linjer slettes
- Ephemeral - modellen materialiserer seg ikke direkte, men deltar som CTE i andre modeller
- Eventuelle andre strategier du kan legge til selv
I tillegg til materialiseringsstrategier er det muligheter for optimalisering for spesifikke lagringsenheter, for eksempel:
- Snowflake: Forbigående tabeller, sammenslåingsadferd, tabellgruppering, kopieringstilskudd, sikre visninger
- rødforskyvning: Distkey, Sortkey (interleaved, compound), Late Binding Views
- BigQuery: Tabellpartisjonering og klynging, fletteatferd, KMS-kryptering, etiketter og etiketter
- Spark: Filformat (parkett, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
Følgende lagringer støttes for øyeblikket:
- postgres
- rødforskyvning
- BigQuery
- Snowflake
- Presto (delvis)
- Gnist (delvis)
- Microsoft SQL Server (fellesskapsadapter)
La oss forbedre modellen vår:
- La oss gjøre fyllingen inkrementell (inkrementell)
- La oss legge til segmenterings- og sorteringsnøkler for 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
Modellavhengighetsgraf
Det er også et avhengighetstre. Det er også kjent som DAG (Directed Acyclic Graph).
DBT bygger en graf basert på konfigurasjonen av alle prosjektmodeller, eller rettere sagt ref() lenker innenfor modeller til andre modeller. Ved å ha en graf kan du gjøre følgende:
- Kjøre modeller i riktig rekkefølge
- Parallellisering av butikkfrontdannelse
- Kjøre en vilkårlig subgraf
Eksempel på grafvisualisering:
Hver node i grafen er en modell; kantene på grafen er spesifisert av uttrykket ref.
Datakvalitet og dokumentasjon
I tillegg til å generere modellene selv, lar DBT deg teste en rekke antakelser om det resulterende datasettet, for eksempel:
- Ikke null
- Unik
- Referanseintegritet - referanseintegritet (for eksempel tilsvarer kunde_id i ordretabellen id i kundetabellen)
- Matcher listen over akseptable verdier
Det er mulig å legge til egne tester (egendefinerte datatester), som for eksempel % avvik av inntekt med indikatorer fra en dag, en uke, en måned siden. Enhver forutsetning formulert som en SQL-spørring kan bli en test.
På denne måten kan du fange opp uønskede avvik og feil i data i Lagervinduene.
Når det gjelder dokumentasjon, tilbyr DBT mekanismer for å legge til, versjonere og distribuere metadata og kommentarer på modell- og til og med attributtnivåer.
Slik ser det ut å legge til tester og dokumentasjon på konfigurasjonsfilnivå:
- 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 dokumentasjonen ser ut på det genererte nettstedet:
Makroer og moduler
Formålet med DBT er ikke så mye å bli et sett med SQL-skript, men å gi brukerne et kraftig og funksjonsrikt middel for å bygge sine egne transformasjoner og distribuere disse modulene.
Makroer er sett med konstruksjoner og uttrykk som kan kalles funksjoner i modeller. Makroer lar deg gjenbruke SQL mellom modeller og prosjekter i samsvar med DRY (Don't Repeat Yourself) ingeniørprinsippet.
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 bruken:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT kommer med en pakkebehandling som lar brukere publisere og gjenbruke individuelle moduler og makroer.
Dette betyr å kunne laste og bruke biblioteker som:
- : arbeider med dato/klokkeslett, surrogatnøkler, skjematester, pivot/unpivot og andre
- Ferdige utstillingsmaler for tjenester som f.eks и
- Biblioteker for spesifikke datalagre, f.eks.
- — Modul for logging av DBT-drift
En komplett liste over pakker finner du på .
Enda flere funksjoner
Her vil jeg beskrive noen andre interessante funksjoner og implementeringer som teamet og jeg bruker til å bygge et datavarehus i .
Separasjon av kjøretidsmiljøer DEV - TEST - PROD
Selv innenfor samme DWH-klynge (innenfor forskjellige ordninger). Bruk for eksempel følgende uttrykk:
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 koden sier bokstavelig talt: for miljøer dev, test, ci ta kun data for de siste 3 dagene og ikke mer. Det vil si at kjøring i disse miljøene vil være mye raskere og kreve færre ressurser. Når du kjører på miljø prod filterbetingelsen vil bli ignorert.
Materialisering med alternativ kolonnekoding
Redshift er en kolonneformet DBMS som lar deg angi datakomprimeringsalgoritmer for hver enkelt kolonne. Å velge optimale algoritmer kan redusere diskplass med 20-50 %.
Makro vil utføre kommandoen ANALYSE COMPRESSION, opprette en ny tabell med de anbefalte kolonnekodingsalgoritmene, spesifiserte segmenteringsnøkler (dist_key) og sorteringsnøkler (sort_key), overføre dataene til den, og om nødvendig slette den gamle kopien.
Makrosignatur:
{{ 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) }}
Loggmodell kjører
Du kan feste kroker til hver utførelse av modellen, som vil bli utført før lansering eller umiddelbart etter at opprettelsen av modellen er fullført:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Loggmodulen lar deg registrere alle nødvendige metadata i en egen tabell, som deretter kan brukes til å revidere og analysere flaskehalser.
Slik ser dashbordet ut basert på loggdata i Looker:
Automatisering av lagringsvedlikehold
Hvis du bruker noen utvidelser av funksjonaliteten til det brukte depotet, for eksempel UDF (User Defined Functions), er versjonskontroll av disse funksjonene, tilgangskontroll og automatisert utrulling av nye utgivelser veldig praktisk å gjøre i DBT.
Vi bruker UDF i Python til å beregne hasher, e-postdomener og bitmask-dekoding.
Et eksempel på en makro som lager en UDF i et hvilket som helst utførelsesmiljø (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 bruker vi Amazon Redshift, som er basert på PostgreSQL. For Redshift er det viktig å regelmessig samle inn statistikk på tabeller og frigjøre diskplass – henholdsvis ANALYSE og VACUUM kommandoene.
For å gjøre dette, blir kommandoene fra makroen redshift_maintenance utført hver natt:
{% 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 mulig å bruke DBT som en tjeneste (Managed Service). Inkludert:
- Web-IDE for utvikling av prosjekter og modeller
- Jobbkonfigurasjon og planlegging
- Enkel og praktisk tilgang til logger
- Nettside med dokumentasjon av ditt prosjekt
- Koble til CI (kontinuerlig integrasjon)
Konklusjon
Å tilberede og innta DWH blir like hyggelig og nyttig som å drikke en smoothie. DBT består av Jinja, brukerutvidelser (moduler), en kompilator, en eksekutør og en pakkebehandling. Ved å sette disse elementene sammen får du et komplett arbeidsmiljø for ditt datavarehus. Det finnes knapt en bedre måte å håndtere transformasjon innenfor DWH i dag.
Troen som følges av utviklerne av DBT er formulert som følger:
- Kode, ikke GUI, er den beste abstraksjonen for å uttrykke kompleks analytisk logikk
- Arbeid med data bør tilpasse beste praksis innen programvareutvikling (Software Engineering)
- Kritisk datainfrastruktur bør kontrolleres av brukerfellesskapet som åpen kildekode-programvare
- Ikke bare analyseverktøy, men også kode vil i økende grad bli eiendommen til Open Source-fellesskapet
Disse kjernetroene har skapt et produkt som brukes av over 850 selskaper i dag, og de danner grunnlaget for mange spennende utvidelser som vil bli laget i fremtiden.
For de interesserte er det en video av en åpen leksjon jeg ga for noen måneder siden som en del av en åpen leksjon på OTUS - .
I tillegg til DBT og Data Warehousing, som en del av Data Engineer-kurset på OTUS-plattformen, underviser mine kolleger og jeg kurs om en rekke andre relevante og moderne emner:
- Arkitektoniske konsepter for Big Data-applikasjoner
- Øv med Spark og Spark Streaming
- Utforske metoder og verktøy for å laste datakilder
- Bygge analytiske utstillingsvinduer i DWH
- NoSQL-konsepter: HBase, Cassandra, ElasticSearch
- Prinsipper for overvåking og orkestrering
- Sluttprosjekt: å sette alle ferdighetene sammen under veiledningsstøtte
referanser:
- — Offisiell dokumentasjon
- — Gjennomgangsartikkel av en av forfatterne av DBT
- — YouTube, opptak av en åpen leksjon i OTUS
- — Neste åpne leksjon er 15. mai 2020
- —OTUS
- — Et blikk på fremtiden for data og analyser
- — Utviklingen av analyse og påvirkningen av åpen kildekode
- — Prinsipper for å bygge CI ved hjelp av DBT
- — Øvelse, trinnvise instruksjoner for selvstendig arbeid
- — Github, pedagogisk prosjektkode
Kilde: www.habr.com