Millistel põhimõtetel on ideaalne Andmete Säilitamine üles ehitatud?

Fookus äriväärtusele ja analüütikale ilma boilerplate koodita. DWH haldamine nagu koodibaas: versioonimine, ülevaatus, automaatne testimine ja CI. Moodulsus, laienemisvõime, avatud lähtekood ja kogukond. Kasutajasõbralik dokumentatsioon ja sõltuvuste visualiseerimine (Data Lineage).

Kõigest sellest põhjalikumalt ja DBT rollist Big Data & Analytics ökosüsteemis — tere tulemast allapoole.

Tere kõigile

Olge ühenduses, Artemy Kozyr. Juba rohkem kui 5 aastat olen töötanud andmesalvestites, tehes ETL/ELT ehitust, samuti andmeanalüüsi ja visualiseerimist. Praegu töötan ma Wheely, õpetan OTUS-es kursusel Andmeinsener, ja täna tahan teiega jagada artiklit, mille ma kirjutasin seoses uue kursuse vastuvõtu algusega.

Lühike ülevaade

DBT raamistik — see on kõik T kohta akronüümis ELT (Extract — Transform — Load).

Kuna sellised võimsad ja skaleeritavad analüüsibaasid nagu BigQuery, Redshift ja Snowflake on tekkinud, pole enam mingit mõtet teha transformatsioone väljaspool Andmete Säilitamist.

DBT ei salvesta andmeid allikatest, kuid pakub tohutuid võimalusi töötamiseks juba laaditud andmetega Hoiuses (Internal või External Storage).

DBT peamine eesmärk on võtta kood, kompileerida see SQL-iks ja täita käsud õiges järjestuses Hoiuses.

DBT projekti struktuur

Projekt koosneb kahest tüüpi kataloogidest ja failidest:

Mudel (.sql) — transformatsiooni üksus, väljendatud SELECT-päringuna
Konfiguratsioonifail (.yml) — parameetrid, seaded, testid, dokumentatsioon

Põhitasandil töö toimub järgmiselt:

Kasutaja valmistab mudelite koodi ette igas mugavas IDE-s
CLI abil kutsutakse esile mudelite käivitamine, DBT kompileerib mudelite koodi SQL-iks
Kompileeritud SQL-kood täidetakse Hoiuses antud järjestuses (graaf)

Nii võib CLI käivitus välja näha:

Kohustuslik on SELECT

See on Data Build Tool raamistiku killer-feature. Teisisõnu, DBT abstraktsioonib kogu koodi, mis on seotud teie päringute materialiseerimisega Hoiuses (variatsioonid käskudest CREATE, INSERT, UPDATE, DELETE ALTER, GRANT jne).

Igast mudelist eeldatakse ühe SELECT-päringu kirjutamist, mis määratleb saadud andmestiku.

Transformatsioonide loogika võib olla mitmeastmeline ja konsolideerida andmeid mitmest teisest mudelist. Näide mudelist, mis loob tellimuste vitriini (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

Mida huvitavat me siin näha saame?

Esiteks: Kasutatakse CTE-d (Common Table Expressions) — koodi korraldamiseks ja mõistmiseks, mis sisaldab palju transformatsioone ja äriloogikat

Teiseks: Mudeli kood on SQL-i ja Jinja (mallimise keel) segu.

Näites kasutatakse tsüklit for iga maksemeetodi summa kujundamiseks, nagu on väljendatud set. Kasutatakse ka funktsiooni ref — võimalust viidata koodis teistele mudelitele:

Kompileerimise ajal ref muutub see sihtmärgiks tabelile või vaatele Andmehoidlas
ref lubab sõltuvuste graafiku koostamine mudelitest

Just Jinja lisab DBT-le peaaegu piiramatud võimalused. Kõige sagedamini kasutatavad neist on:

If/else laused — haruoperatorid
For tsüklid — tsüklid
Muudatused — muutujad
Makro — makrode loomine

Materjaliseerimine: Tabel, Vaade, Inkremetne

Materjaliseerimise strateegia — lähenemine, mille kohaselt salvestatakse mudeli tulemusandmete kogum Ladustamisse.

Põhilise käsitluse kohaselt see on:

Tabel — füüsiline tabel Ladustamises
Vaade — esitus, virtuaalne tabel Ladustamises

On olemas ka keerukamaid materjaliseerimise strateegiaid:

Inkremetne — inkremetne laadimine (suured faktitabelid); uued read lisatakse, muudetud — uuendatakse, kustutatud — eemaldatakse
Ephemeral — mudelit ei materjaliseerita otse, vaid see osaleb CTE-na teistes mudelites
Käideldavad muud strateegiad, mida saate ise lisada

Lisaks materjaliseerimise strateegiatele avanevad võimalused optimeerimiseks konkreetsete Ladustamiste jaoks, näiteks:

Snowflake: Ajutised tabelid, Ühinemise käitumine, Tabeli grupeerimine, Õiguste kopeerimine, Turvalised vaated
Redshift: Distkey, Sortkey (vahelduv, komposiit), Hiline sidumise vaated
BigQuery: Tabeli partitsioneerimine ja grupeerimine, Ühinemise käitumine, KMS-krüpteerimine, Sildid ja Märgid
Spark: Failivorming (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy

Hetkel toetatakse järgmisi salvestusvorme:

Postgres
Redshift
BigQuery
Snowflake
Presto (osaliselt)
Spark (osaliselt)
Microsoft SQL Server (kogukonna adapter)

Parandame meie mudelit:

Teeme selle täiendamise inkrementaalseks (Incremental)
Lisame segmentimise ja sortimise võtmed Redshiftile

-- Mudeli konfiguratsioon: 
-- Inkrementaalne täitmine, unikaalne võti rekordite uuendamiseks (unique_key)
-- Segmentimise võti (dist), sorteerimise võti (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() -%}
       -- See filter rakendatakse ainult inkrementaalse käivitamise jaoks
       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

Mudelite sõltuvuste graafik

See on ka sõltuvuste puu. Samuti DAG (Suunatud Akeetsükkel Graaf).

DBT ehitab graafi projekti kõigi mudelite konfiguratsiooni põhjal, täpsemalt mudelites olevate ref() viidete kaudu teistele mudelitele. Graafi olemasolu võimaldab teha järgmist:

Mudelite käitamine õiges järjekorras
Data warehouse'ide loomise paralleelne täitmine
Mitte mingisuguse alagraafi käitamine

Graafi visualiseerimise näide:

Iga graafi sõlm on mudel, graafi servad määratakse ref väljendiga.

Andmete kvaliteet ja dokumentatsioon

Lisaks mudelite loomisele võimaldab DBT testida rida hüpoteese (assertions) andmestiku tulemuste kohta, nagu näiteks:

Not Null
Unikaalne
Viidete terviklikkus (näiteks customer_id tabelis orders peab vastama id-le tabelis customers)
Sobivuse kontroll lubatud väärtuste loendiga

Võimalik on lisada oma teste (kohandatud andmete testid), näiteks % müügitulu kõrvalekalded eelmise päeva, nädala või kuu kohta. Iga hüpotees, mis on sõnastatud SQL-päringuna, võib muutuda testiks.

Nii saab Reservoiri vitriinides tuvastada soovimatud kõrvalekalded ja andmete vead.

Mis puutub dokumenteerimisse, siis DBT pakub mehhanisme metateabe ja kommentaaride lisamiseks, versioonide haldamiseks ning levitamiseks mudelite ja isegi atribuutide tasandil.

Nii näeb välja testide ja dokumentatsiooni lisamine konfiguratsioonifaili tasandil:

 - name: fct_orders
   description: See tabel sisaldab põhiteavet tellimuste kohta, samuti mõningaid maksete põhjal saadud faktilisi andmeid
   columns:
     - name: order_id
       tests:
         - unique # väärtuste unikaalsuse kontroll
         - not_null # null-i olemasolu kontroll
       description: See on unikaalne identifikaator tellimuse jaoks
     - name: customer_id
       description: Välisvõti klientide tabelisse
       tests:
         - not_null
         - relationships: # viidete terviklikkuse kontroll
             to: ref('dim_customers')
             field: customer_id
     - name: order_date
       description: Kuupäev (UTC), millal tellimus tehti
     - name: status
       description: '{{ doc("orders_status") }}'
       tests:
         - accepted_values: # lubatud väärtuste kontroll
             values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

Nii näeb see dokumentatsioon välja juba genereeritud veebilehel:

Makrode ja moodulite

DBT eesmärk ei ole mitte lihtsalt SQL-skriptide kogum, vaid pakkuda kasutajatele võimsaid ja mitmekesiseid vahendeid oma transformatsioonide loomiseks ja nende moodulite jagamiseks.

Makrosid kasutatakse konstruktsioonide ja väljendite kogumina, mida saab kutsuda funktsioonidena mudelites. Makrosid saab uuesti kasutada SQL-i mudelite ja projektide vahel vastavalt inseneriprinsipile DRY (Ära Korda Iseennast).

Makro näide:

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

Ja selle kasutamine:

{% set column_name = 'product' %}
select
 product,
 {{ rename_category(column_name) }} -- makro kutsumine
from my_table

DBT tuleb koos pakettide halduriga (packages), mis võimaldab kasutajatel avaldada ja uuesti kasutada eraldi mooduleid ja makrosid.

See tähendab, et on võimalik laadida ja kasutada selliseid teeke nagu:

dbt_utils: Date/Time töötlemine, asendusbitaendid, skeemi testid, Pivot/Unpivot ja muud
Valmis mallid selliste teenuste jaoks nagu Snowplow ja Stripe
Teegid konkreetsete andmehoidlate jaoks, näiteks Redshift
Logging — DBT töö logimise moodul

Kogu paketide loetelu on saadaval dbt hub.

Veel rohkem võimalusi

Siin kirjeldan mõningaid teisi huvitavaid omadusi ja rakendusi, mida mina ja minu meeskond kasutame Andmete Lao loomisel Wheely.

Käitusväljade jagamine DEV — TEST — PROD

Isegi ühe DWH klastri sees (erinevate skeemide raames). Näiteks järgmise lause abil:

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 -%}
 
)

See kood ütleb sõna-sõnalt: keskkondadele dev, test, ci võta andmed ainult viimase 3 päeva jooksul ja mitte rohkem. See tähendab, et need keskkonnad pääsevad palju kiiremini ja vajavad vähem ressursse. Käivitamisel keskkonnas prod filtreerimise tingimus jäetakse tähelepanuta.

Alternatiivse veergude kodeerimise materialiseerimine

Redshift on veergudega andmebaas, mis võimaldab määrata iga veeru jaoks andmete tihendamise algoritme. Optimaalsete algoritmide valik võib vähendada kettaruumi kasutust 20-50%.

Makro redshift.compress_table täidab ANALYZE COMPRESSION käsku, loob uue tabeli soovitatud veergude kodeerimisalgoritmidega, kasutades määratud segmentimise (dist_key) ja sortimise (sort_key) võtmeid, edastab andmed sinna ja vajadusel kustutab vana koopia.

Makro allkiri:

{{ compress_table(schema, table,
                    drop_backup=False,
                    comprows=none|Integer,
                    sort_style=none|compound|interleaved,
                    sort_keys=none|List,
                    dist_style=none|all|even,
                    dist_key=none|String) }}

Mudelite käivitamise logimine

Iga mudeli täitmise juurde saab lisada hook'e, mis käivitatakse enne mudeli käivitamist või kohe pärast mudeli loomise lõppu:

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

Logimismoodul võimaldab salvestada kõik vajalikud metaandmed eraldi tabelisse, mille põhjal on hiljem võimalik teostada auditeid ja analüüsida probleemikohti.

Nii näeb välja Lookeris logimise andmetel põhinev juhtpaneel:

Laokogumiku hoolduse automatiseerimine

Kui kasutate mõningaid laohalduse funktsionaalsuse laiendusi, nagu UDF (kasutaja määratud funktsioonid), on nende funktsioonide versiooniuuendamine, ligipääsu haldamine ja uute versioonide automaatne rakendamine DBT-s väga mugav.

Kasutame UDF-e Pythonis, et arvutada räsiväärtusi, meiliaadresside domeene ja dekodeerida bitmask-e.

Mikro näide, mis loob UDF-i igas täitevkeskkonnas (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 %}

Wheelys kasutame Amazon Redshifti, mis põhineb PostgreSQL-il. Redshifti jaoks on oluline regulaarselt koguda statistikat tabelite kohta ja vabastada ketas — vastavad käsud ANALYZE ja VACUUM.

Selleks käivitatakse iga öö redshift_maintenance makro sisalduvad käsud:

{% 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 teenusena (Halletud teenus) kasutamise võimalus. Komplekti kuuluvad:

Web IDE projektide ja mudelite arendamiseks
Tööde konfigureerimine ja ajastamine
Lihtne ja mugav juurdepääs logidele
Teie projekti dokumentatsiooni veebisait
CI (Continuous Integration) ühendamine

Kokkuvõte

Andmete ladustamise (DWH) valmistamine ja kasutamine on sama meeldiv ja kasulik kui smuuti joomine. DBT koosneb Jinjast, kohandatud laiendustest (moodulitest), kompilaatorist, täitmisajamust (executor) ja pakihaldurist. Kogudes need elemendid ühte, saate täiusliku töökorralduse oma andmete ladustamiseks. Tänapäeval on raske leida paremat viisi DWH-s transformatsioonide haldamiseks.

DBT arendajate järgitud veendumused on järgmised:

Kood, mitte GUI, on parim abstraktsioon keerulise analüütilise loogika väljendamiseks
Andmetega töötamine peaks kohandama parimaid tarkvaraarenduse (Software Engineering) praktikaid

Kõige olulisem andmete töötlemise infrastruktuur peaks olema kontrollitud kasutajate kogukonna poolt avatud lähtekoodiga tarkvarana
Mitte ainult analüüsitööriistad, vaid ka kood muutub järjest enam avatud lähtekoodiga kogukonna varaks

Needus uskumused on loonud toote, mida täna kasutavad üle 850 ettevõtte ja need on aluseks paljudele huvitavatele laiendustele, mis tulevikus luuakse.

Neile, kes on huvitatud, on olemas video salvestus avatud loengust, mille ma viisid läbi mõned kuud tagasi OTUSi avatud loengute raames — Data Build Tool Amazon Redshifti ladustamiseks.

Lisaks DBT-le ja Andmete Ladustamisele viib OTUSi Data Engineer kursuse raames mina ja mu kolleegid läbi loenguid mitmetes muudest aktuaalsetest ja kaasaegsetest teemadest: