Millä periaatteilla ihanteellinen tietovarasto rakennetaan?

Keskity liiketoiminnan arvoon ja analytiikkaan ilman vakiokoodia. DWH:n hallinta koodikantana: versiointi, tarkistus, automaattinen testaus ja CI. Modulaarinen, laajennettava, avoin lähdekoodi ja yhteisö. Käyttäjäystävällinen dokumentaatio ja riippuvuuden visualisointi (Data Lineage).

Lisää tästä kaikesta ja DBT:n roolista Big Data & Analytics -ekosysteemissä - tervetuloa kissaan.

Hei kaikki

Artemy Kozyr on yhteydessä. Yli 5 vuoden ajan olen työskennellyt tietovarastojen, ETL/ELT:n rakentamisen sekä data-analytiikan ja visualisoinnin parissa. Olen tällä hetkellä töissä Wheely, Opetan OTUS:ssa kurssilla Data Engineer, ja tänään haluan jakaa kanssasi artikkelin, jonka kirjoitin odottaessani alkua uusi ilmoittautuminen kurssille.

Lyhyt katsaus

DBT-kehyksessä on kyse T-kirjaimesta ELT-lyhenteessä (Extract - Transform - Load).

Tällaisten tuottavien ja skaalautuvien analyyttisten tietokantojen, kuten BigQuery, Redshift, Snowflake, ilmaantumisen myötä ei ollut mitään järkeä tehdä muunnoksia tietovaraston ulkopuolella.

DBT ei lataa dataa lähteistä, mutta tarjoaa erinomaiset mahdollisuudet työskennellä tietojen kanssa, jotka on jo ladattu tallennustilaan (sisäisessä tai ulkoisessa tallennustilassa).

DBT:n päätarkoitus on ottaa koodi, kääntää se SQL:ksi, suorittaa komennot oikeassa järjestyksessä arkistossa.

DBT-projektin rakenne

Projekti koostuu vain kahdentyyppisistä hakemistoista ja tiedostoista:

Malli (.sql) - SELECT-kyselyllä ilmaistu muunnosyksikkö
Asetustiedosto (.yml) - parametrit, asetukset, testit, dokumentaatio

Perustasolla työ rakentuu seuraavasti:

Käyttäjä valmistelee mallikoodin missä tahansa kätevässä IDE:ssä
CLI:n avulla mallit käynnistetään, DBT kokoaa mallikoodin SQL:ksi
Käännetty SQL-koodi suoritetaan tallennustilassa tietyssä järjestyksessä (kaavio)

Tältä CLI:stä suorittaminen voi näyttää:

Kaikki on SELECT

Tämä on Data Build Tool -kehyksen tappava ominaisuus. Toisin sanoen DBT tiivistää kaiken koodin, joka liittyy kyselyjesi toteutumiseen Storeen (muunnelmat komennoista CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).

Mikä tahansa malli sisältää yhden SELECT-kyselyn kirjoittamisen, joka määrittää tuloksena olevan tietojoukon.

Tässä tapauksessa muunnoslogiikka voi olla monitasoinen ja yhdistää tietoja useista muista malleista. Esimerkki mallista, joka rakentaa tilausesittelyn (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

Mitä mielenkiintoisia asioita voimme nähdä täällä?

Ensimmäinen: käytetty CTE (Common Table Expressions) - järjestää ja ymmärtää koodia, joka sisältää paljon muunnoksia ja liiketoimintalogiikkaa

Toiseksi: Mallikoodi on sekoitus SQL:ää ja kieltä Jinja (mallikieli).

Esimerkki käyttää silmukkaa varten luodaksesi summan kullekin lausekkeessa määritetylle maksutavalle setti. Toimintoa käytetään myös viite — kyky viitata muihin koodin malleihin:

Kokoamisen aikana viite muunnetaan kohdeosoittimeksi taulukkoon tai näkymään Storagessa
viite voit rakentaa mallin riippuvuuskaavion

Nimittäin Jinja lisää lähes rajattomat mahdollisuudet DBT:hen. Yleisimmin käytetyt ovat:

If / else -lauseet - haaralausekkeet
Silmukoille
Muuttujat
Makro - makrojen luominen

Materialisointi: Taulukko, Näkymä, Inkrementaalinen

Materialisointistrategia on lähestymistapa, jonka mukaan tuloksena oleva mallitietojoukko tallennetaan Tallennustilaan.

Periaatteessa se on:

Taulukko - fyysinen taulukko tallennustilassa
Näkymä - näkymä, virtuaalinen pöytä tallennustilassa

On myös monimutkaisempia materialisointistrategioita:

Inkrementaalinen - inkrementaalinen lataus (suurten tietotaulukoiden); uusia rivejä lisätään, muuttuneet rivit päivitetään, poistetut rivit tyhjennetään
Efemeraalinen - malli ei toteudu suoraan, vaan osallistuu CTE:nä muihin malleihin
Muita strategioita, joita voit lisätä itse

Toteutusstrategioiden lisäksi on olemassa mahdollisuuksia optimointia tietyille Tallennuksille, esimerkiksi:

Lumihiutale: ohimenevät taulukot, yhdistämiskäyttäytyminen, taulukoiden klusterointi, apurahojen kopioiminen, suojatut näkymät
Redshift: Distkey, Sortkey (lomiteltu, yhdistelmä), Late Binding Views
BigQueryn: Taulukon osiointi ja klusterointi, yhdistämiskäyttäytyminen, KMS-salaus, tarrat ja tunnisteet
Kipinä: Tiedostomuoto (parketti, csv, json, orc, delta), osio_by, clustered_by, buckets, inkrementaalinen_strategia

Seuraavat tallennustilat ovat tällä hetkellä tuettuja:

postgres
Redshift
BigQueryn
Lumihiutale
Presto (osittain)
Kipinä (osittain)
Microsoft SQL Server (yhteisösovitin)

Parannetaan malliamme:

Tehdään sen täytöstä inkrementaalinen (inkrementaalinen)
Lisätään segmentointi- ja lajitteluavaimet Redshiftille

-- Конфигурация модели: 
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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

Mallin riippuvuuskaavio

Se on myös riippuvuuspuu. Se tunnetaan myös nimellä DAG (Directed Acyclic Graph).

DBT rakentaa kaavion, joka perustuu kaikkien projektimallien kokoonpanoon, tai pikemminkin ref()-linkkeihin mallien sisällä muihin malleihin. Kaavion avulla voit tehdä seuraavat asiat:

Mallit ajetaan oikeassa järjestyksessä
Liikkeen muodostamisen rinnakkaisuus
Satunnaisen aligraafin suorittaminen

Esimerkki kaavion visualisoinnista:

Kukin graafin solmu on malli, graafin reunat määritellään lausekkeella ref.

Tietojen laatu ja dokumentointi

Itse mallien luomisen lisäksi DBT:n avulla voit testata useita tuloksena olevaa tietojoukkoa koskevia oletuksia, kuten:

Ei tyhjä
Ainutlaatuinen
Viittauksen eheys - viittauksen eheys (esimerkiksi asiakastunnus tilaustaulukossa vastaa asiakastaulukon tunnusta)
Vastaa hyväksyttyjen arvojen luetteloa

On mahdollista lisätä omia testejä (mukautettuja datatestejä), kuten esimerkiksi tulojen poikkeama % indikaattoreilla päivä, viikko, kuukausi sitten. Mikä tahansa SQL-kyselynä muotoiltu oletus voi tulla testiksi.

Tällä tavalla voit havaita ei-toivotut poikkeamat ja virheet tietoihin Varasto-ikkunoissa.

Mitä tulee dokumentaatioon, DBT tarjoaa mekanismeja metatietojen ja kommenttien lisäämiseen, versiointiin ja jakamiseen malli- ja jopa attribuuttitasoilla.

Tältä näyttää testien ja dokumentaation lisääminen asetustiedostotasolla:

 - 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']

Ja tältä tämä dokumentaatio näyttää luodulla verkkosivustolla:

Makrot ja moduulit

DBT:n tarkoituksena ei ole niinkään tulla joukko SQL-skriptejä, vaan tarjota käyttäjille tehokas ja monipuolinen väline omien muunnosten rakentamiseen ja näiden moduulien jakeluun.

Makrot ovat ryhmiä rakenteita ja lausekkeita, joita voidaan kutsua funktioiksi malleissa. Makrot mahdollistavat SQL:n uudelleenkäytön mallien ja projektien välillä DRY (Don't Repeat Yourself) -suunnitteluperiaatteen mukaisesti.

Esimerkki makrosta:

{% 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 sen käyttö:

{% set column_name = 'product' %}
select
 product,
 {{ rename_category(column_name) }} -- вызов макроса
from my_table

DBT:n mukana tulee paketinhallinta, jonka avulla käyttäjät voivat julkaista ja käyttää uudelleen yksittäisiä moduuleja ja makroja.

Tämä tarkoittaa, että pystyt lataamaan ja käyttämään kirjastoja, kuten:

dbt_utils: työskentely päivämäärän/ajan, sijaisavainten, skeematestien, pivotin/unpivotin ja muiden kanssa
Valmiita esittelymalleja palveluille, kuten Lumiaura и Raita
Tiettyjen tietosäilöjen kirjastot, esim. Redshift
Hakkuu — Moduuli DBT-toiminnan kirjaamiseen

Täydellinen luettelo paketeista löytyy osoitteesta dbt-keskitin.

Vielä enemmän ominaisuuksia

Tässä kuvailen muutamia muita mielenkiintoisia ominaisuuksia ja toteutuksia, joita tiimi ja minä käytämme tietovaraston rakentamiseen Wheely.

Ajonaikaisten ympäristöjen erottaminen DEV - TEST - PROD

Jopa saman DWH-klusterin sisällä (eri järjestelmissä). Esimerkiksi käyttämällä seuraavaa lauseketta:

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

Tämä koodi sanoo kirjaimellisesti: ympäristöille dev, testi, ci ota tiedot vain viimeiseltä 3 päivältä, ei enempää. Toisin sanoen käyttö näissä ympäristöissä on paljon nopeampaa ja vaatii vähemmän resursseja. Ajettaessa ympäristössä tuot suodattimen tila jätetään huomioimatta.

Materialisointi vaihtoehtoisella sarakekoodauksella

Redshift on sarakepohjainen DBMS, jonka avulla voit asettaa tietojen pakkausalgoritmeja kullekin yksittäiselle sarakkeelle. Optimaalisten algoritmien valitseminen voi vähentää levytilaa 20-50%.

makro redshift.compress_table suorittaa ANALYZE COMPRESSION -komennon, luo uuden taulukon, jossa on suositellut sarakekoodausalgoritmit, määritetyt segmentointiavaimet (dist_key) ja lajitteluavaimet (sort_key), siirtää tiedot siihen ja tarvittaessa poistaa vanhan kopion.

Makro allekirjoitus:

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

Kirjausmalli on käynnissä

Voit kiinnittää mallin jokaiseen suoritukseen koukut, jotka suoritetaan ennen julkaisua tai heti mallin luomisen jälkeen:

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

Kirjausmoduulin avulla voit tallentaa kaikki tarvittavat metatiedot erilliseen taulukkoon, jota voidaan myöhemmin käyttää pullonkaulojen tarkasteluun ja analysointiin.

Tältä kojelauta näyttää Lookerin lokitietojen perusteella:

Varastoinnin ylläpidon automatisointi

Jos käytät joitain käytetyn arkiston toimintojen laajennuksia, kuten UDF (User Defined Functions), näiden toimintojen versiointi, kulunvalvonta ja uusien julkaisujen automaattinen käyttöönotto on erittäin kätevää DBT:ssä.

Käytämme Pythonissa UDF:ää tiivisteiden, sähköpostien verkkotunnuksien ja bittimaskin dekoodauksen laskemiseen.

Esimerkki makrosta, joka luo UDF:n missä tahansa suoritusympäristössä (dev, testi, 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 %}

Wheelyllä käytämme Amazon Redshiftiä, joka perustuu PostgreSQL:ään. Redshiftille on tärkeää kerätä säännöllisesti tilastoja taulukoista ja vapauttaa levytilaa - ANALYZE- ja VACUUM-komennot, vastaavasti.

Tätä varten redshift_maintenance-makron komennot suoritetaan joka ilta:

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

DBT:tä on mahdollista käyttää palveluna (Managed Service). Mukana:

Web IDE projektien ja mallien kehittämiseen
Työn konfigurointi ja aikataulutus
Yksinkertainen ja kätevä pääsy lokeihin
Verkkosivusto, jossa on dokumentaatio projektistasi
Yhdistetään CI (jatkuva integrointi)

Johtopäätös

DWH:n valmistamisesta ja nauttimisesta tulee yhtä nautinnollista ja hyödyllistä kuin smoothien juominen. DBT koostuu Jinjasta, käyttäjälaajennuksista (moduuleista), kääntäjästä, suorittimesta ja paketinhallinnasta. Yhdistämällä nämä elementit yhteen saat täydellisen työympäristön tietovarastollesi. Nykyään tuskin on parempaa tapaa hallita muutosta DWH:ssa.

DBT:n kehittäjien noudattamat uskomukset on muotoiltu seuraavasti:

Koodi, ei GUI, on paras abstraktio monimutkaisen analyyttisen logiikan ilmaisemiseen
Tietojen kanssa työskentelyn tulisi mukauttaa ohjelmistosuunnittelun parhaita käytäntöjä (Software Engineering)

Käyttäjäyhteisön tulisi hallita kriittistä tietoinfrastruktuuria avoimen lähdekoodin ohjelmistoina
Analytiikkatyökalujen lisäksi myös koodi tulee yhä enemmän avoimen lähdekoodin yhteisön omaisuutta

Nämä ydinuskomukset ovat synnyttäneet tuotteen, jota yli 850 yritystä käyttää nykyään, ja ne muodostavat perustan monille jännittäville laajennuksille, joita luodaan tulevaisuudessa.

Kiinnostuneille on video avoimesta oppitunnista, jonka pidin muutama kuukausi sitten osana OTUS:n avointa oppituntia - Data Build Tool Amazon Redshift -tallennustilalle.

DBT:n ja Data Warehousingin lisäksi osana Data Engineer -kurssia OTUS-alustalla kollegani ja minä opetamme kursseja useista muista oleellisista ja nykyaikaisista aiheista:

Big Data -sovellusten arkkitehtoniset konseptit
Harjoittele Sparkilla ja Spark Streamingilla
Tietolähteiden latausmenetelmien ja työkalujen tutkiminen
Analyyttisten esittelyjen rakentaminen DWH:ssa
NoSQL-konseptit: HBase, Cassandra, ElasticSearch
Seurannan ja orkestroinnin periaatteet
Loppuprojekti: kaikkien taitojen yhdistäminen mentorointituen alle

viitteet:

DBT-dokumentaatio - Johdanto — Viralliset asiakirjat
Mikä tarkalleen ottaen on dbt? — Yhden DBT:n kirjoittajan arvosteluartikkeli
Data Build Tool Amazon Redshift -tallennustilalle — YouTube, OTUS:n avoimen oppitunnin tallennus
Greenplumiin tutustuminen - Seuraava avoin oppitunti on 15
Tietotekniikan kurssi – OTUS
Aikuisen analytiikkatyönkulun luominen — Katsaus datan ja analytiikan tulevaisuuteen
On avoimen lähdekoodin analytiikan aika — Analytiikan kehitys ja avoimen lähdekoodin vaikutus
Jatkuva integrointi ja automaattinen koontitestaus dbtCloudin avulla — CI:n rakentamisen periaatteet DBT:n avulla
Aloita DBT-opetusohjelma — Harjoittelu, vaiheittaiset ohjeet itsenäiseen työskentelyyn
Jaffle shop - Github DBT -opastus — Github, koulutusprojektin koodi

Lue lisää kurssista.

Lähde: will.com

Data Build Tool tai mikä on yhteistä Data Warehousen ja Smoothien välillä