Mbi cilat parime është ndërtuar një Depo e Dhënave ideale?
Përqendrohuni në vlerën e biznesit dhe analitikën në mungesë të kodit të pllakës së bojlerit. Menaxhimi i DWH si një bazë kodesh: versionimi, rishikimi, testimi i automatizuar dhe CI. Modular, i zgjeruar, me burim të hapur dhe komunitet. Dokumentacioni miqësor për përdoruesit dhe vizualizimi i varësisë (Linja e të dhënave).
Më shumë për të gjitha këto dhe për rolin e DBT në ekosistemin Big Data & Analytics - mirë se vini në cat.
Të gjithë Përshëndetje
Artemy Kozyr është në kontakt. Për më shumë se 5 vjet kam punuar me depo të dhënash, duke ndërtuar ETL/ELT, si dhe me analitikë dhe vizualizim të të dhënave. Aktualisht jam duke punuar në , Unë jap një kurs në OTUS , dhe sot dua të ndaj me ju një artikull që kam shkruar në pritje të fillimit regjistrim i ri për kursin.
Përmbledhje
Korniza DBT ka të bëjë me T-në në akronimin ELT (Ekstrakt - Transformim - Ngarkesë).
Me ardhjen e bazave të të dhënave analitike të tilla produktive dhe të shkallëzueshme si BigQuery, Redshift, Snowflake, nuk kishte kuptim të bënim transformime jashtë Depove të të Dhënave.
DBT nuk shkarkon të dhëna nga burimet, por ofron mundësi të shkëlqyera për të punuar me të dhënat që janë ngarkuar tashmë në ruajtje (në ruajtje të brendshme ose të jashtme).
Qëllimi kryesor i DBT është të marrë kodin, ta përpilojë atë në SQL, të ekzekutojë komandat në sekuencën e duhur në Depo.
Struktura e projektit DBT
Projekti përbëhet nga drejtori dhe skedarë të vetëm 2 llojeve:
- Model (.sql) - një njësi transformimi e shprehur nga një pyetje SELECT
- Skedari i konfigurimit (.yml) - parametrat, cilësimet, testet, dokumentacioni
Në një nivel bazë, puna është e strukturuar si më poshtë:
- Përdoruesi përgatit kodin e modelit në çdo IDE të përshtatshme
- Duke përdorur CLI, modelet lëshohen, DBT përpilon kodin e modelit në SQL
- Kodi i përpiluar SQL ekzekutohet në Storage në një sekuencë të caktuar (grafik)
Ja se si mund të duket drejtimi nga CLI:
Gjithçka është SELECT
Ky është një tipar vrasës i kornizës së Veglave të Ndërtimit të të Dhënave. Me fjalë të tjera, DBT abstrakton të gjithë kodin që lidhet me materializimin e pyetjeve tuaja në Store (variacione nga komandat CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Çdo model përfshin shkrimin e një pyetje SELECT që përcakton grupin e të dhënave që rezulton.
Në këtë rast, logjika e transformimit mund të jetë me shumë nivele dhe të konsolidojë të dhëna nga disa modele të tjera. Një shembull i një modeli që do të ndërtojë një vitrinë porosie (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
Çfarë gjërash interesante mund të shohim këtu?
Së pari: Përdoret CTE (Shprehjet e zakonshme të tabelës) - për të organizuar dhe kuptuar kodin që përmban shumë transformime dhe logjikë biznesi
Së dyti: Kodi i modelit është një përzierje e SQL dhe gjuhës (gjuha e shabllonit).
Shembulli përdor një lak për për të gjeneruar shumën për secilën mënyrë pagese të specifikuar në shprehje i vendosur. Funksioni përdoret gjithashtu ref — aftësia për të referuar modele të tjera brenda kodit:
- Gjatë përpilimit ref do të konvertohet në një tregues objektiv në një tabelë ose pamje në hapësirën ruajtëse
- ref ju lejon të ndërtoni një grafik të varësisë së modelit
Pikërisht shton mundësi pothuajse të pakufizuara në DBT. Më të përdorurat janë:
- Deklaratat If / else - deklaratat e degës
- Për sythe - cikle
- Variablat
- Makro - krijimi i makrove
Materializimi: Tabela, Pamje, Rritje
Strategjia e materializimit është një qasje sipas së cilës grupi rezultues i të dhënave të modelit do të ruhet në Storage.
Në terma bazë është:
- Tabela - tabelë fizike në Magazinim
- View - view, tabela virtuale në Storage
Ekzistojnë gjithashtu strategji më komplekse të materializimit:
- Ngarkimi inkremental - rritës (i tabelave të mëdha të fakteve); shtohen linja të reja, linjat e ndryshuara përditësohen, linjat e fshira pastrohen
- Efemeral - modeli nuk materializohet drejtpërdrejt, por merr pjesë si CTE në modele të tjera
- Çdo strategji tjetër që mund të shtoni vetë
Përveç strategjive të materializimit, ka mundësi për optimizim për ruajtje specifike, për shembull:
- Flok dëbore: Tabelat kalimtare, Sjellja e bashkimit, Grumbullimi i tabelave, Kopjimi i granteve, Pamje të sigurta
- RedShift: Distkey, Sortkey (i ndërthurur, i përbërë), Pamje me lidhje të vonshme
- BigQuery: Ndarja dhe grupimi i tabelave, Sjellja e bashkimit, Kriptimi KMS, Etiketat dhe etiketat
- Shkëndijë: Formati i skedarit (parket, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
Ruajtjet e mëposhtme mbështeten aktualisht:
- postgres
- RedShift
- BigQuery
- Flok dëbore
- Presto (pjesërisht)
- Shkëndija (pjesërisht)
- Microsoft SQL Server (përshtatës i komunitetit)
Le të përmirësojmë modelin tonë:
- Le ta bëjmë mbushjen e saj në rritje (Incremental)
- Le të shtojmë çelësat e segmentimit dhe renditjes për 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
Grafiku i varësisë së modelit
Është gjithashtu një pemë varësie. Njihet gjithashtu si DAG (Directed Acyclic Graph).
DBT ndërton një grafik bazuar në konfigurimin e të gjitha modeleve të projektit, ose më mirë, lidhjet ref() brenda modeleve me modelet e tjera. Pasja e një grafiku ju lejon të bëni gjërat e mëposhtme:
- Ekzekutimi i modeleve në sekuencën e duhur
- Paralelizimi i formimit të vitrinës
- Drejtimi i një nëngrafi arbitrar
Shembull i vizualizimit të grafikut:
Çdo nyje e grafikut është një model; skajet e grafikut specifikohen nga shprehja ref.
Cilësia dhe Dokumentacioni i të Dhënave
Përveç gjenerimit të vetë modeleve, DBT ju lejon të testoni një numër supozimesh në lidhje me grupin e të dhënave që rezulton, si p.sh.
- Jo Null
- Unik
- Integriteti i referencës - integriteti referues (për shembull, klienti_id në tabelën e porosive korrespondon me ID-në në tabelën e klientëve)
- Përputhja e listës së vlerave të pranueshme
Është e mundur të shtoni testet tuaja (testet e të dhënave me porosi), të tilla si, për shembull, devijimi % i të ardhurave me tregues nga një ditë, një javë, një muaj më parë. Çdo supozim i formuluar si një pyetje SQL mund të bëhet një test.
Në këtë mënyrë, ju mund të kapni devijimet dhe gabimet e padëshiruara në të dhënat në dritaret e Depove.
Për sa i përket dokumentacionit, DBT ofron mekanizma për shtimin, versionimin dhe shpërndarjen e meta të dhënave dhe komenteve në nivelet e modelit dhe madje edhe të atributeve.
Ja se si duket shtimi i testeve dhe dokumentacionit në nivelin e skedarit të konfigurimit:
- 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']
Dhe ja se si duket ky dokumentacion në faqen e krijuar të internetit:
Makro dhe module
Qëllimi i DBT nuk është aq shumë të bëhet një grup skriptesh SQL, por t'u sigurojë përdoruesve një mjet të fuqishëm dhe të pasur me veçori për ndërtimin e transformimeve të tyre dhe shpërndarjen e këtyre moduleve.
Makrot janë grupe konstruktesh dhe shprehjesh që mund të quhen si funksione brenda modeleve. Makrot ju lejojnë të ripërdorni SQL midis modeleve dhe projekteve në përputhje me parimin inxhinierik DRY (Don't Repeat Yourself).
Shembull makro:
{% 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 %}
Dhe përdorimet e tij:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT vjen me një menaxher paketash që lejon përdoruesit të publikojnë dhe ripërdorin module dhe makro individuale.
Kjo do të thotë të jesh në gjendje të ngarkosh dhe përdorësh biblioteka të tilla si:
- : duke punuar me datën/kohën, çelësat zëvendësues, testet e skemës, Pivot/Unpivot dhe të tjera
- Modele të gatshme vitrinë për shërbime si p.sh и
- Bibliotekat për dyqane specifike të të dhënave, p.sh.
- — Moduli për regjistrimin e funksionimit të DBT
Një listë e plotë e paketave mund të gjendet në .
Edhe më shumë veçori
Këtu do të përshkruaj disa veçori dhe zbatime të tjera interesante që ekipi dhe unë përdorim për të ndërtuar një Depo të Dhënave në .
Ndarja e mjediseve të funksionimit DEV - TEST - PROD
Edhe brenda të njëjtit grup DWH (brenda skemave të ndryshme). Për shembull, duke përdorur shprehjen e mëposhtme:
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 -%}
)
Ky kod fjalë për fjalë thotë: për mjedise dev, test, ci merrni të dhëna vetëm për 3 ditët e fundit dhe jo më shumë. Kjo do të thotë, vrapimi në këto mjedise do të jetë shumë më i shpejtë dhe do të kërkojë më pak burime. Kur vraponi në mjedis stimuloj gjendja e filtrit do të shpërfillet.
Materializimi me kodim kolone alternative
Redshift është një DBMS kolone që ju lejon të vendosni algoritme të kompresimit të të dhënave për secilën kolonë individuale. Zgjedhja e algoritmeve optimale mund të zvogëlojë hapësirën në disk me 20-50%.
makro do të ekzekutojë komandën ANALYZE COMPRESSION, do të krijojë një tabelë të re me algoritmet e kodimit të rekomanduara të kolonës, çelësat e specifikuar të segmentimit (dist_key) dhe çelësat e renditjes (sort_key), do të transferojë të dhënat në të dhe, nëse është e nevojshme, do të fshijë kopjen e vjetër.
Nënshkrimi makro:
{{ 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) }}
Modeli i regjistrimit funksionon
Ju mund t'i bashkëngjitni grepa për çdo ekzekutim të modelit, të cilat do të ekzekutohen përpara nisjes ose menjëherë pas përfundimit të krijimit të modelit:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Moduli i regjistrimit do t'ju lejojë të regjistroni të gjitha meta të dhënat e nevojshme në një tabelë të veçantë, e cila më pas mund të përdoret për të audituar dhe analizuar pengesat.
Kjo është se si duket paneli i kontrollit bazuar në të dhënat e regjistrimit në Looker:
Automatizimi i mirëmbajtjes së magazinimit
Nëse përdorni disa shtesa të funksionalitetit të depove të përdorura, të tilla si UDF (Funksionet e Përcaktuara nga Përdoruesi), atëherë versionimi i këtyre funksioneve, kontrolli i aksesit dhe nxjerrja e automatizuar e publikimeve të reja është shumë e përshtatshme për t'u bërë në DBT.
Ne përdorim UDF në Python për të llogaritur hash-et, domenet e postës elektronike dhe dekodimin e bitmaskeve.
Një shembull i një makro që krijon një UDF në çdo mjedis ekzekutimi (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 %}
Në Wheely ne përdorim Amazon Redshift, i cili bazohet në PostgreSQL. Për Redshift, është e rëndësishme të mblidhni rregullisht statistika në tabela dhe të lironi hapësirën në disk - përkatësisht komandat ANALYZE dhe VACUUM.
Për ta bërë këtë, komandat nga makro redshift_maintenance ekzekutohen çdo natë:
{% 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
Është e mundur të përdoret DBT si shërbim (Shërbimi i menaxhuar). Të përfshira:
- Web IDE për zhvillimin e projekteve dhe modeleve
- Konfigurimi dhe planifikimi i punës
- Qasje e thjeshtë dhe e përshtatshme në shkrimet
- Faqja e internetit me dokumentacionin e projektit tuaj
- Lidhja CI (integrimi i vazhdueshëm)
Përfundim
Përgatitja dhe konsumimi i DWH bëhet po aq i këndshëm dhe i dobishëm sa edhe pirja e një smoothie. DBT përbëhet nga Jinja, shtesat e përdoruesve (modulet), një përpilues, një ekzekutues dhe një menaxher paketash. Duke i bashkuar këto elemente, ju merrni një mjedis të plotë pune për Depon e të Dhënave tuaja. Nuk ka një mënyrë më të mirë për të menaxhuar transformimin brenda DWH sot.
Besimet e ndjekura nga zhvilluesit e DBT janë formuluar si më poshtë:
- Kodi, jo GUI, është abstraksioni më i mirë për të shprehur logjikën komplekse analitike
- Puna me të dhëna duhet të përshtatë praktikat më të mira në inxhinierinë softuerike (Inxhinieri Softuerësh)
- Infrastruktura kritike e të dhënave duhet të kontrollohet nga komuniteti i përdoruesve si softuer me burim të hapur
- Jo vetëm mjetet analitike, por edhe kodi do të bëhen gjithnjë e më shumë pronë e komunitetit me burim të hapur
Këto besime thelbësore kanë sjellë një produkt që përdoret nga mbi 850 kompani sot, dhe ato përbëjnë bazën e shumë zgjerimeve emocionuese që do të krijohen në të ardhmen.
Për të interesuarit, ka një video të një mësimi të hapur që kam dhënë disa muaj më parë si pjesë e një mësimi të hapur në OTUS - .
Përveç DBT dhe Data Warehousing, si pjesë e kursit të Inxhinierit të të Dhënave në platformën OTUS, unë dhe kolegët e mi japim mësime në një sërë temash të tjera relevante dhe moderne:
- Konceptet arkitekturore për aplikimet e të dhënave të mëdha
- Praktikoni me Spark dhe Spark Streaming
- Eksplorimi i metodave dhe mjeteve për ngarkimin e burimeve të të dhënave
- Ndërtimi i vitrinave analitike në DWH
- Konceptet NoSQL: HBase, Cassandra, ElasticSearch
- Parimet e monitorimit dhe orkestrimit
- Projekti përfundimtar: bashkimi i të gjitha aftësive nën mbështetjen e mentorimit
referencat:
- — Dokumentacioni zyrtar
- — Rishikoni artikullin nga një prej autorëve të DBT
- — YouTube, Regjistrimi i një mësimi të hapur OTUS
- — Mësimi tjetër i hapur është 15 maj 2020
- -OTUS
- — Një vështrim në të ardhmen e të dhënave dhe analitikës
- — Evolucioni i analitikës dhe ndikimi i burimit të hapur
- — Parimet e ndërtimit të CI duke përdorur DBT
- — Praktikë, udhëzime hap pas hapi për punë të pavarur
- — Github, kodi i projektit arsimor
Burimi: www.habr.com