Millised põhimõtted on ideaalne Andmesaal ehitatud?

Fookus äriväärtusel ja analüütikal ilma boilerplate koodita. DWH haldamine koodibaasina: versioonihaldus, ülevaatus, automaatne testimine ja CI. Moodulsus, laiendatavus, avatud lähtekood ja kogukond. Sõbralik kasutajate dokumentatsioon ja sõltuvuse visualiseerimine (Data Lineage).

Küsimuses selle ja DBT rollist Big Data & Analytics ökosüsteemis — tere tulemast edasi lugema.

Tere kõigile

Olen Artiom Koster. Juba üle 5 aasta töötan andmesaalidega, tehes ETL/ELT projekte ning tegeledes andmete analüüsiga ja visualiseerimisega. Praegu töötan Wheely, õpetan OTUS'is kursusel Data Engineer, ja täna tahan jagada teiega artiklit, mille kirjutasin enne uue kursuse komplekti algust.

Lühike ülevaade

DBT raamistiku olemus on kõik selle T kohta ELT akronüümis (Extract — Transform — Load).

Selliste produktiivsete ja skaleeritavate analüüsibaaside nagu BigQuery, Redshift, Snowflake ilmumisega kaotas igasugune mõte teha transformatsioone väljaspool Andmesaldo.

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

DBT põhieesmärk on võtta kood, kompileerida see SQL-ks ja käivitada käsud õiges järjekorras Andmesaalis.

DBT projekti struktuur

Projekt koosneb vaid kahest tüüpi kaustadest ja failidest:

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

Alustasime, et töö toimub järgmiselt:

Kasutaja valmistab mudelite koodi ette mis tahes mugavas IDE-s
CLI abil käivitatakse mudelid, DBT kompileerib mudelite koodi SQL-iks
Kompileeritud SQL-kood täidetakse Andmesaalis määratud järjekorras (graaf)

Siit võiks näha CLI-s käivitust:

Kõik on SELECT

See on Data Build Tool raamistikku tapvate omaduste üks. Teisisõnu, DBT abstrak Compute kõik kood, mis on seotud teie päringute materialiseerimisega Andmesaalis (variatsioonid käskudest CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, …).

Iga mudel sisaldab ühe SELECT-päringu kirjutamist, mis määrab tulemuseks oleva andmeseti.

Selle puhul võib teisendusloogika olla mitmetasandiline ja konsolideerida andmeid mitmest muust mudelist. Näide mudelist, mis loob tellimuste väljundi (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: Kasutatud on CTE-sid (Common Table Expressions) — koodi korraldamiseks ja mõistmiseks, mis sisaldab palju teisendusi ja äriloogikat.

Teiseks: Mudeli kood on segu SQL-ist ja Jinja (šabloonikeel).

Näites on kasutatud tsüklit for iga makseviisi summa kuvamiseks, mis on määratletud väljendis set. Samuti kasutatakse funktsiooni ref — võimalust viidata koodis teistele mudelitele:

Kompileerimise ajal ref muudetakse see sihituspunktiks, mis viitab tabelile või vaatele Andmehoidlas
ref võimaldab luua mudelite sõltuvuste graafi.

Just see Jinja lisab DBT-le peaaegu piiramatu võimekuse. Kõige sagedamini kasutatavad neist on:

If / else laused — haruoperatsioonid
For tsüklid — silmustamine
Muutujad — muutujad
Makro — makrode loomine

Materialiseerimine: Table, View, Incremental

Materialiseerimisstrateegia — lähenemine, mille kohaselt salvestatakse mudeli tulemusandmestik Andmehoidlas.

Põhjalikus vaates on see:

Table — füüsiline tabel Andmehoidlas
View — vaade, virtuaalne tabel Andmehoidlas

On ka keerukamaid materialiseerimise strateegiaid:

Incremental — inkrementaalne laadimine (suurte faktite tabelite); uued read lisatakse, muudetud — uuendatakse, kustutatud — eemaldatakse.
Ephemeral — mudel ei materialiseeru otse, kuid osaleb kui CTE teistes mudelites.
Kõik muud strateegiad, mida võite ise lisada.

Lisaks materialiseerimisstrateegiatele avanevad võimalused optimeerimiseks konkreetsete Andmehoidlate jaoks, näiteks:

Snowflake: 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
Spark: Failivormaat (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy

Praegu toetatakse järgmisi ladustamisi:

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

Laske meil oma mudelit täiustada:

Teeme selle täiendamise inkrementselt (Incremental)
Lisame Redshifti segmenteerimise ja sortimise võtmed

-- Mudeli konfiguratsioon:  
-- Inkrementaalne täiendamine, unikaalne võti salvestuste uuendamiseks (unique_key)  
-- Segmenteerimise võti (dist), sortimise 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 rakendub ainult inkrementaalsete käivituste 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õltuvusgraafik

Sama mis sõltuvuspuu. Sama mis DAG (Directed Acyclic Graph — suunatud tsükliline graaf).

DBT loob graafi kõikide projekti mudelite konfiguratsiooni põhjal, täpsemalt mudelite sees oleva ref() viidatud teistele mudelitele. Graafi olemasolu võimaldab järgmisi asju:

Mudelite käitamine õigetes järjestustes
Kuvandite loomise paralleliseerimine
Suvalise alagraafi käitamine

Graafi visualiseerimise näide:

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

Andmete kvaliteet ja Dokumentatsioon

Lisaks mudelite koostamisele võimaldab DBT testida rea oletusi (assertions) tulemuste andmestiku kohta, nagu näiteks:

Not Null
Ainulaadne
Reference Integrity — viidatud terviklikkus (nt customer_id tabelis orders peab vastama id-le tabelis customers)
Vastavus lubatud väärtuste loetelule

Võimalik on lisada oma teste (custom data tests), näiteks % tulude kõrvalekalle võrreldes näitajatega päev, nädal, kuu tagasi. Iga oletus, mis on sõnastatud SQL-päringuna, võib saada testiks.

Nii saab tuvastada ladustamissüsteemide kuvandites soovimatuid kõrvalekaldeid ja vigu andmetes.

Mis dokumenteerimise osas pakub DBT mehhanisme metaandmete ja kommentaaride lisamiseks, versioonide haldamiseks ja levitamiseks mudelite ja isegi atribuutide tasandil.

Siin on, kuidas näeb välja testide ja dokumentatsiooni lisamine konfiguratsioonifaili tasandil:

 - name: fct_orders
   description: See tabel sisaldab põhiteavet tellimuste kohta, samuti teatud tuletatud fakte maksete põhjal
   columns:
     - name: order_id
       tests:
         - unique # väärtuste unikaalsuse testimine
         - not_null # nulli olemasolu testimine
       description: See on tellimuse unikaalne identifikaator
     - name: customer_id
       description: Viidatud välisvõti klientide tabelisse
       tests:
         - not_null
         - relationships: # viidatud terviklikkuse testimine
             to: ref('dim_customers')
             field: customer_id
     - name: order_date
       description: Aeg (UTC), millal tellimus tehti
     - name: status
       description: '{{ doc("orders_status") }}'
       tests:
         - accepted_values: # lubatud väärtuste testimine
             values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

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

Makrod ja moodulid

DBT eesmärk ei ole mitte ainult SQL-skriptide kogumi loomine, vaid ka kasutajatele võimsate ja funktsionaalsete vahendite pakkumine oma transformatsioonide loomiseks ja nende moodulite levitamiseks.

Makrod on konstruktsioonide ja väljendite kogumid, mida saab mudelites funktsioonidena kutsuda. Makrod võimaldavad SQL-i taaskasutamist mudelite ja projektide vahel vastavalt inseneritehnika põhimõttele DRY (Ära Korrake End).

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 kutse
from my_table

DBT tarnitakse koos paketihalduriga (packages), mis võimaldab kasutajatel eraldi mooduleid ja makrosid avaldada ning taaskasutada.

See tähendab võimalust laadida ja kasutada selliseid teeke nagu:

dbt_utils: töötlus Date/Time, Surrogate Keys, skeemide testimine, Pivot/Unpivot ja muud
Valmis vitriinide mallid selliste teenuste jaoks nagu Snowplow ja Stripe
Spetsiifiliste andmehoidlate teegid, näiteks Redshift
Logging — DBT töö logimise moodul

Täispakettide nimekirjaga saab tutvuda dbt hub.

Veelgi rohkem võimalusi

Siin kirjeldan mõningaid teisi huvitavaid omadusi ja teostusi, mida mina ja minu meeskond kasutame Andmete Ladu ehitamiseks Wheely.

Käivituskeskkondade jagamine DEV — TEST — PROD

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

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 otseses mõttes: võtke andmed ainult viimase 3 päeva jooksul ja mitte rohkem. See tähendab, et töö käivitamine neis keskkondades on palju kiirem ja nõuab vähem ressursse. Käivitamisel keskkonnas dev, test, ci filtreerimise tingimus jäetakse kõrvale. prod Materjaliseerimine alternatiivsete veergude kodeerimisega

Redshift on veergude andmebaas, mis võimaldab määrata andmete tihendamise algoritme iga üksiku veeru jaoks. Optimaalsed algoritmide valikud võivad vähendada ketta vajalikku ruumi 20-50%.

Makro

redshift.compress_table täidab ANALYZE COMPRESSION käsu, loob uue tabeli soovitatud veergude kodeerimise algoritmidega, märkides segmenteerimise (dist_key) ja sortimise (sort_key) võtmed, kolib andmed sinna ja vajadusel eemaldab 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äivituste logimine

Iga mudeli täitmisele saab riputada konksud (hooks), mis täidetakse enne käivitamist või kohe pärast mudeli loomise lõpetamist:

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 alusel saab hiljem teostada auditi ja analüüsida probleemseid kohti (bottlenecks).

Näiteks näeb logimist, kuidas logimistabel Lookeris välja näeb:

Andmete ladustamise hoolduse automatiseerimine

Kui kasutate Andmete Ladustamiseks mõnda funktsionaalsuse laiendust, näiteks UDF (Kasutaja Määratletud Funktsioonid), on nende funktsioonide versioonide haldamine, juurdepääsude juhtimine ja automaatne uute versioonide juurutamine väga mugav DBT-s.

Kui kasutate oma andmehoidla funktsionaalsuse laiendamiseks mõningaid funktsioone, nagu UDF (kasutaja määratletud funktsioonid), on nende funktsioonide versioonihaldus, juurdepääsuhaldus ja uute väljaannete automatiseeritud juurutamine DBT-s väga mugav.

Me kasutame Pythonis UDF-i, et arvutada räsi väärtusi, domeene e-posti aadressidelt, dekodeerida bitimaskid (bitmask).

Makro näide, mis loob UDF-i igas täitmis keskkonnas (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 kettaruumi - vastavalt käskude ANALYZE ja VACUUM.

Selleks täidetakse igal ööl käsklused makrost 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

On võimalus kasutada DBT-t teenusena (Managed Service). Komplektis:

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

Kokkuvõte

DWH valmistamine ja kasutamine muutub sama meeldivaks ja kasulikuks nagu smuuti joomine. DBT koosneb Jinjast, kasutaja laienditest (moodulitest), kompilaatorist, käitistest ja paketihaldurist. Kui tuua need elemendid kokku, saate täieliku töökeskkonna oma Andmete Ladustamiseks. Vaevalt on täna olemas parem viis DWH-s transformatsioonide haldamiseks.

Usun, et DBT arendajad järgivad selliseid veendumusi:

Kood, mitte graafiline kasutajaliides, on parim abstraktsioon keerulise analüüsiloogika väljendamiseks
Andmetega töötamine peaks kohandama parimaid tarkvaraarenduse praktikaid

Oluline andmeinfrastruktuur peaks olema kontrollitav avatud lähtekoodiga tarkvara kasutajate kogukonna poolt
Kuna analüütika tööriistad, muutub ka kood üha enam avatud lähtekoodiga kogukonna pärandiks

Need põhiveendumused on tootnud toote, mida täna kasutab üle 850 ettevõtte ning nad moodustavad aluse paljudele tulevastele huvitavatele laiendustele.

Neile, kes on huvitatud, on saadaval videorecording avatud tunnist, mille ma mõned kuud tagasi läbi viisin OTUS-i avatud tunni raames — Data Build Tool Amazon Redshifti andmehoidla jaoks.

Peale DBT ja Andmehoidlate, õpetame OTUS-i Data Engineer kursusel mitmeid teisi aktuaalseid ja kaasaegseid teemasid: