Na kojim principima je izgrađeno idealno skladište podataka?
Usredotočite se na poslovnu vrijednost i analitiku u nedostatku standardnog koda. Upravljanje DWH kao bazom koda: verzija, pregled, automatizirano testiranje i CI. Modularan, proširiv, otvorenog koda i zajednica. Dokumentacija prilagođena korisniku i vizualizacija ovisnosti (podatkovna linija).
Više o svemu ovome i o ulozi DBT-a u Big Data & Analytics ekosustavu - dobrodošli na kat.
Vsem privet
Artemy Kozyr je u kontaktu. Više od 5 godina radim sa skladištima podataka, izgradnjom ETL/ELT, kao i analitikom i vizualizacijom podataka. Trenutno radim u , predajem u OTUS-u na tečaju , a danas želim s vama podijeliti članak koji sam napisao uoči početka novi upisi na tečaj.
Kratki pregled
DBT okvir je sve o T u ELT (Extract - Transform - Load) akronimu.
S pojavom tako produktivnih i skalabilnih analitičkih baza podataka kao što su BigQuery, Redshift, Snowflake, nije bilo smisla raditi transformacije izvan Data Warehousea.
DBT ne preuzima podatke iz izvora, ali pruža velike mogućnosti za rad s podacima koji su već učitani u pohranu (u unutarnju ili vanjsku pohranu).
Glavna svrha DBT-a je uzeti kod, prevesti ga u SQL, izvršiti naredbe u ispravnom nizu u Repozitoriju.
DBT struktura projekta
Projekt se sastoji od direktorija i datoteka samo 2 vrste:
- Model (.sql) - jedinica transformacije izražena SELECT upitom
- Konfiguracijska datoteka (.yml) - parametri, postavke, testovi, dokumentacija
Na osnovnoj razini, rad je strukturiran na sljedeći način:
- Korisnik priprema kod modela u bilo kojem prikladnom IDE-u
- Pomoću CLI-a pokreću se modeli, DBT kompajlira kod modela u SQL
- Prevedeni SQL kod se izvršava u skladištu u zadanom nizu (graf)
Evo kako bi pokretanje iz CLI-ja moglo izgledati:
Sve je SELECT
Ovo je ubojita značajka okvira Data Build Tool. Drugim riječima, DBT apstrahira sav kod povezan s materijalizacijom vaših upita u Store (varijacije naredbi CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Svaki model uključuje pisanje jednog SELECT upita koji definira rezultirajući skup podataka.
U ovom slučaju, logika transformacije može biti višerazinska i konsolidirati podatke iz nekoliko drugih modela. Primjer modela koji će izgraditi izlog narudžbe (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
Što zanimljivosti možemo vidjeti ovdje?
Prvo: korišten CTE (Common Table Expressions) - za organiziranje i razumijevanje koda koji sadrži mnogo transformacija i poslovne logike
Drugo: Kod modela je mješavina SQL-a i jezika (jezik za izradu šablona).
Primjer koristi petlju za za generiranje iznosa za svaki način plaćanja naveden u izrazu postaviti. Također se koristi funkcija ref — sposobnost referenciranja drugih modela unutar koda:
- Tijekom kompilacije ref će se pretvoriti u ciljni pokazivač na tablicu ili prikaz u pohrani
- ref omogućuje vam da izgradite graf ovisnosti modela
Točno dodaje gotovo neograničene mogućnosti DBT-u. Najčešće korišteni su:
- If / else naredbe - naredbe grananja
- Za petlje - ciklusi
- Varijable
- Makro - stvaranje makronaredbi
Materijalizacija: Tablica, Pogled, Inkrementalno
Strategija materijalizacije je pristup prema kojem će rezultirajući skup podataka modela biti pohranjen u Storage.
U osnovnim crtama to je:
- Tablica - fizička tablica u Skladištu
- View - prikaz, virtualna tablica u Storageu
Postoje i složenije strategije materijalizacije:
- Incremental - inkrementalno učitavanje (velikih tablica činjenica); nove linije se dodaju, promijenjene linije se ažuriraju, izbrisane linije se brišu
- Efemeran – model se ne materijalizira izravno, već sudjeluje kao CTE u drugim modelima
- Bilo koje druge strategije koje sami možete dodati
Uz strategije materijalizacije, postoje prilike za optimizaciju za određene pohrane, na primjer:
- Pahuljica: Prijelazne tablice, ponašanje spajanja, grupiranje tablica, odobrenja za kopiranje, sigurni pogledi
- crveni pomak: Distkey, Sortkey (isprepleteni, složeni), Late Binding Views
- BigQueryja: Particioniranje tablice i klasteriranje, ponašanje spajanja, KMS enkripcija, oznake i oznake
- Iskra: Format datoteke (parquet, csv, json, orc, delta), partition_by, clustered_by, bucket, incremental_strategy
Trenutno su podržane sljedeće pohrane:
- postgres
- crveni pomak
- BigQueryja
- Pahuljica
- Presto (djelomično)
- Iskra (djelomično)
- Microsoft SQL Server (adapter zajednice)
Poboljšajmo naš model:
- Neka njegovo punjenje bude inkrementalno (inkrementalno)
- Dodajmo ključeve za segmentaciju i sortiranje za 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
Graf ovisnosti modela
To je također stablo ovisnosti. Također je poznat kao DAG (usmjereni aciklički graf).
DBT gradi graf na temelju konfiguracije svih modela projekta, odnosno, ref() povezuje unutar modela s drugim modelima. Posjedovanje grafikona omogućuje vam sljedeće stvari:
- Izvođenje modela u ispravnom redoslijedu
- Paralelizacija formiranja izloga
- Izvođenje proizvoljnog podgrafa
Primjer vizualizacije grafikona:
Svaki čvor grafa je model; rubovi grafa određeni su izrazom ref.
Kvaliteta podataka i dokumentacija
Osim generiranja samih modela, DBT vam omogućuje testiranje niza pretpostavki o rezultirajućem skupu podataka, kao što su:
- Nije Null
- Jedinstven
- Integritet reference - referentni integritet (na primjer, customer_id u tablici narudžbi odgovara ID-u u tablici kupaca)
- Usklađivanje s popisom prihvatljivih vrijednosti
Moguće je dodati vlastite testove (testove prilagođenih podataka), kao što je npr. % odstupanja prihoda s pokazateljima od prije dan, tjedan, mjesec dana. Svaka pretpostavka formulirana kao SQL upit može postati test.
Na taj način možete uhvatiti neželjena odstupanja i pogreške u podacima u prozorima Skladišta.
Što se tiče dokumentacije, DBT pruža mehanizme za dodavanje, izradu verzija i distribuciju metapodataka i komentara na razini modela, pa čak i na razini atributa.
Evo kako izgleda dodavanje testova i dokumentacije na razini konfiguracijske datoteke:
- 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']
A evo kako ova dokumentacija izgleda na generiranoj web stranici:
Makronaredbe i moduli
Svrha DBT-a nije toliko da postane skup SQL skripti, već da korisnicima pruži moćna sredstva bogata značajkama za izgradnju vlastitih transformacija i distribuciju ovih modula.
Makronaredbe su skupovi konstrukcija i izraza koji se mogu zvati kao funkcije unutar modela. Makronaredbe vam omogućuju ponovnu upotrebu SQL-a između modela i projekata u skladu s inženjerskim načelom DRY (Don't Repeat Yourself).
Primjer makronaredbe:
{% 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 %}
I njegova upotreba:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT dolazi s upraviteljem paketa koji korisnicima omogućuje objavljivanje i ponovno korištenje pojedinačnih modula i makronaredbi.
To znači mogućnost učitavanja i korištenja biblioteka kao što su:
- : rad s datumom/vremenom, zamjenskim ključevima, testovima shema, zakretanjem/poništavanjem zakretanja i drugima
- Gotovi predlošci izloga za usluge kao što su и
- Knjižnice za određene pohrane podataka, npr.
- — Modul za bilježenje DBT rada
Kompletan popis paketa možete pronaći na .
Još više značajki
Ovdje ću opisati nekoliko drugih zanimljivih značajki i implementacija koje tim i ja koristimo za izgradnju skladišta podataka .
Razdvajanje runtime okruženja DEV - TEST - PROD
Čak i unutar istog DWH klastera (unutar različitih shema). Na primjer, koristeći sljedeći izraz:
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 -%}
)
Ovaj kod doslovno kaže: za okruženja dev, test, ci uzeti podatke samo za zadnja 3 dana i ne više. Odnosno, rad u tim okruženjima bit će puno brži i zahtijevat će manje resursa. Prilikom trčanja na okoliš štap stanje filtra će se zanemariti.
Materijalizacija s alternativnim kodiranjem stupaca
Redshift je stupčasti DBMS koji vam omogućuje postavljanje algoritama za kompresiju podataka za svaki pojedinačni stupac. Odabir optimalnih algoritama može smanjiti prostor na disku za 20-50%.
Makro će izvršiti naredbu ANALIZA KOMPRESIJE, kreirati novu tablicu s preporučenim algoritmima za kodiranje stupaca, navedenim segmentacijskim ključevima (dist_key) i ključevima za sortiranje (sort_key), prenijeti podatke u nju i, ako je potrebno, izbrisati staru kopiju.
Makro potpis:
{{ 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) }}
Zapisivanje modela radi
Možete priložiti kuke za svako izvršenje modela, koje će se izvršiti prije pokretanja ili odmah nakon završetka izrade modela:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Modul za evidentiranje omogućit će vam snimanje svih potrebnih metapodataka u zasebnu tablicu, koja se kasnije može koristiti za reviziju i analizu uskih grla.
Ovako izgleda nadzorna ploča na temelju podataka zapisnika u Lookeru:
Automatizacija održavanja skladišta
Ako koristite neka proširenja funkcionalnosti korištenog Repozitorija, kao što su UDF (korisnički definirane funkcije), tada je stvaranje verzija ovih funkcija, kontrola pristupa i automatizirano uvođenje novih izdanja vrlo prikladno za DBT.
Koristimo UDF u Pythonu za izračunavanje hashova, domena e-pošte i dekodiranja bitmaske.
Primjer makronaredbe koja stvara UDF u bilo kojem okruženju za izvršavanje (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 %}
U Wheelyju koristimo Amazon Redshift, koji se temelji na PostgreSQL-u. Za Redshift je važno redovito prikupljati statistiku o tablicama i oslobađati prostor na disku - naredbe ANALIZIRAJ odnosno VAKUUMIRAJ.
Da biste to učinili, naredbe iz makronaredbe redshift_maintenance izvršavaju se svake noći:
{% 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 oblak
Moguće je koristiti DBT kao uslugu (Managed Service). Uključeno:
- Web IDE za razvoj projekata i modela
- Konfiguracija i raspored poslova
- Jednostavan i praktičan pristup zapisima
- Web stranica s dokumentacijom vašeg projekta
- Povezivanje CI (kontinuirana integracija)
Zaključak
Priprema i konzumacija DWH postaje jednako ugodna i korisna kao ispijanje smoothieja. DBT se sastoji od Jinje, korisničkih proširenja (modula), prevoditelja, izvršitelja i upravitelja paketima. Spajanjem ovih elemenata zajedno dobivate cjelovito radno okruženje za vaše skladište podataka. Teško da danas postoji bolji način za upravljanje transformacijom unutar DWH-a.
Uvjerenja koja slijede razvijači DBT-a formulirana su na sljedeći način:
- Kod, a ne GUI, najbolja je apstrakcija za izražavanje složene analitičke logike
- Rad s podacima treba prilagoditi najbolje prakse u softverskom inženjerstvu (Softversko inženjerstvo)
- Kritičnu podatkovnu infrastrukturu trebala bi kontrolirati korisnička zajednica kao softver otvorenog koda
- Ne samo analitički alati, nego i kod će sve više postajati vlasništvo Open Source zajednice
Ova temeljna uvjerenja iznjedrila su proizvod koji danas koristi više od 850 tvrtki i čine temelj mnogih uzbudljivih proširenja koja će biti stvorena u budućnosti.
Za one koje zanima, tu je video otvorene lekcije koju sam održao prije nekoliko mjeseci u sklopu otvorene lekcije u OTUS-u - .
Osim DBT-a i Data Warehousinga, u sklopu kolegija Data Engineer na OTUS platformi, moji kolege i ja držimo nastavu o nizu drugih relevantnih i modernih tema:
- Arhitektonski koncepti za aplikacije velikih podataka
- Vježbajte uz Spark i Spark Streaming
- Istraživanje metoda i alata za učitavanje izvora podataka
- Izrada analitičkih vitrina u DWH
- NoSQL koncepti: HBase, Cassandra, ElasticSearch
- Načela praćenja i orkestracije
- Završni projekt: spajanje svih vještina uz mentorsku podršku
reference:
- — Službena dokumentacija
- — Pregledni članak jednog od autora DBT
- — YouTube, Snimka otvorenog sata OTUS-a
- — Sljedeći otvoreni sat je 15. svibnja 2020
- —OTUS
- — Pogled u budućnost podataka i analitike
- — Evolucija analitike i utjecaj Open Sourcea
- — Načela izgradnje CI pomoću DBT-a
- — Praksa, Korak po korak upute za samostalan rad
- — Github, šifra obrazovnog projekta
Izvor: www.habr.com