Sa unsa nga mga prinsipyo gitukod ang usa ka sulundon nga Data Warehouse?
Pag-focus sa kantidad sa negosyo ug analytics kung wala ang boilerplate code. Pagdumala sa DWH isip codebase: pag-bersyon, pagrepaso, automated nga pagsulay ug CI. Modular, extensible, open source ug komunidad. Maabiabihon sa user nga dokumentasyon ug dependency visualization (Data Lineage).
Dugang pa mahitungod niining tanan ug mahitungod sa papel sa DBT sa Big Data & Analytics ecosystem - welcome to cat.
Kumusta tanan
Nakontak si Artemy Kozyr. Sulod sa kapin sa 5 ka tuig nagtrabaho ako sa mga bodega sa datos, nagtukod og ETL/ELT, ingon man ang data analytics ug visualization. Nagtrabaho ko karon , nagtudlo ko ug kurso sa OTUS , ug karon gusto nakong ipaambit kanimo ang usa ka artikulo nga akong gisulat sa pagpaabot sa pagsugod bag-ong enrolment para sa kurso.
Kasagaran
Ang balangkas sa DBT kay mahitungod sa T sa ELT (Extract - Transform - Load) acronym.
Sa pag-abot sa ingon nga produktibo ug scalable analytical databases sama sa BigQuery, Redshift, Snowflake, walay kapuslanan ang paghimo sa mga pagbag-o sa gawas sa Data Warehouse.
Ang DBT wala mag-download sa datos gikan sa mga tinubdan, apan naghatag ug dagkong mga oportunidad alang sa pagtrabaho uban sa mga datos nga na-load na ngadto sa Storage (sa Internal o External Storage).
Ang nag-unang katuyoan sa DBT mao ang pagkuha sa code, pagtipon niini sa SQL, pagpatuman sa mga sugo sa husto nga pagkasunod-sunod sa Repository.
Istruktura sa Proyekto sa DBT
Ang proyekto naglangkob sa mga direktoryo ug mga file sa 2 ra nga mga tipo:
- Modelo (.sql) - usa ka yunit sa pagbag-o nga gipahayag sa usa ka SELECT nga pangutana
- Configuration file (.yml) - mga parameter, mga setting, mga pagsulay, dokumentasyon
Sa sukaranan nga lebel, ang trabaho gi-istruktura sama sa mosunod:
- Ang tiggamit nag-andam sa modelo nga code sa bisan unsang kombenyente nga IDE
- Gamit ang CLI, gilunsad ang mga modelo, gi-compile sa DBT ang code sa modelo sa SQL
- Ang gihugpong nga SQL code gipatuman sa Storage sa gihatag nga han-ay (graph)
Ania kung unsa ang hitsura sa pagdagan gikan sa CLI:
PILI ang tanan
Kini usa ka makapatay nga bahin sa Data Build Tool framework. Sa laing pagkasulti, gi-abstract sa DBT ang tanang code nga nalangkit sa pag-materialize sa imong mga pangutana ngadto sa Store (mga variation gikan sa commands CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Ang bisan unsang modelo naglakip sa pagsulat sa usa ka PILI nga pangutana nga naghubit sa resulta nga set sa datos.
Sa kini nga kaso, ang lohika sa pagbag-o mahimo nga multi-level ug pagkonsolida sa datos gikan sa daghang ubang mga modelo. Usa ka pananglitan sa usa ka modelo nga maghimo usa ka order showcase (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
Unsang makapaikag nga mga butang ang atong makita dinhi?
Una: Gigamit nga CTE (Common Table Expressions) - aron maorganisar ug masabtan ang code nga adunay daghang pagbag-o ug lohika sa negosyo
Ikaduha: Ang kodigo sa modelo usa ka sinagol nga SQL ug pinulongan (templating nga pinulongan).
Ang pananglitan naggamit sa usa ka loop alang sa aron makamugna ang kantidad alang sa matag paagi sa pagbayad nga gipiho sa ekspresyon set. Gigamit usab ang function ref β ang abilidad sa paghisgot sa ubang mga modelo sulod sa code:
- Atol sa pag-compile ref makombertir sa usa ka target pointer sa usa ka lamesa o view sa Storage
- ref nagtugot kanimo sa paghimo og usa ka modelo nga dependency graph
Kini nagdugang halos walay kutub nga mga posibilidad sa DBT. Ang labing kasagarang gigamit mao ang:
- Kung / lain nga mga pahayag - mga pahayag sa sanga
- Alang sa mga loop
- Mga variable
- Macro - paghimo og mga macro
Materialization: Table, View, Incremental
Ang estratehiya sa pag-materyal usa ka pamaagi sumala sa diin ang resulta nga set sa data sa modelo itago sa Storage.
Sa sukaranan nga mga termino kini mao ang:
- Talaan - pisikal nga lamesa sa Pagtipig
- Pagtan-aw - pagtan-aw, virtual nga lamesa sa Pagtipig
Adunay usab mas komplikado nga mga estratehiya sa materyalisasyon:
- Incremental - incremental loading (sa dagkong mga lamesa sa kamatuoran); bag-ong mga linya gidugang, giusab nga mga linya gi-update, ang mga natangtang nga linya gitangtang
- Ephemeral - ang modelo dili direkta nga materyal, apan miapil isip CTE sa ubang mga modelo
- Bisan unsang ubang mga estratehiya mahimo nimong idugang sa imong kaugalingon
Dugang pa sa mga estratehiya sa materyalisasyon, adunay mga oportunidad alang sa pag-optimize alang sa piho nga mga Pagtipig, pananglitan:
- snowflake: Lumalabay nga mga lamesa, Paghiusa sa kinaiya, Pag-cluster sa lamesa, Pagkopya sa mga grant, Secure nga mga panglantaw
- Redshift: Distkey, Sortkey (interleaved, compound), Late Binding Views
- BigQuery: Pagbahin sa lamesa ug pag-cluster, Paghiusa sa pamatasan, KMS Encryption, Mga Label ug Tag
- Spark: File format (parquet, csv, json, orc, delta), partition_by, clustered_by, balde, incremental_strategy
Ang mosunod nga mga Pagtipig karon gisuportahan:
- Mga postgres
- Redshift
- BigQuery
- snowflake
- Presto (partially)
- Spark (bahin)
- Microsoft SQL Server (adapter sa komunidad)
Atong pauswagon ang atong modelo:
- Himoon nato ang pagpuno niini nga incremental (Incremental)
- Atong idugang ang segmentation ug sorting keys para sa 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
Model dependency graph
Usa usab kini ka punoan sa dependency. Nailhan usab kini nga DAG (Directed Acyclic Graph).
Ang DBT nagtukod ug graph base sa configuration sa tanang mga modelo sa proyekto, o hinoon, ref() mga link sulod sa mga modelo ngadto sa ubang mga modelo. Ang pagbaton og graph nagtugot kanimo sa pagbuhat sa mosunod nga mga butang:
- Pagpadagan sa mga modelo sa husto nga pagkasunod-sunod
- Parallelization sa storefront formation
- Pagpadagan sa usa ka arbitraryong subgraph
Pananglitan sa graph visualization:
Ang matag node sa graph usa ka modelo; ang mga kilid sa graph gipiho sa ekspresyong ref.
Kalidad sa Data ug Dokumentasyon
Dugang sa paghimo sa mga modelo sa ilang kaugalingon, ang DBT nagtugot kanimo sa pagsulay sa daghang mga pangagpas mahitungod sa resulta nga set sa datos, sama sa:
- Dili Null
- Talagsaong
- Reference Integrity - referential integrity (pananglitan, customer_id sa order table katumbas sa id sa customer table)
- Pagpares sa listahan sa madawat nga mga bili
Posible nga idugang ang imong kaugalingon nga mga pagsulay (mga pagsulay sa kostumbre sa datos), sama sa, pananglitan, % deviation sa kita nga adunay mga timailhan gikan sa usa ka adlaw, usa ka semana, usa ka bulan ang milabay. Ang bisan unsang pangagpas nga giporma isip usa ka SQL nga pangutana mahimong usa ka pagsulay.
Niining paagiha, makuha nimo ang dili gusto nga mga pagtipas ug mga sayup sa datos sa mga bintana sa Warehouse.
Sa termino sa dokumentasyon, ang DBT naghatag og mga mekanismo alang sa pagdugang, pag-bersiyon, ug pag-apod-apod sa metadata ug mga komento sa modelo ug bisan ang lebel sa hiyas.
Ania kung unsa ang hitsura sa pagdugang mga pagsulay ug dokumentasyon sa lebel sa configuration file:
- 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']
Ug ania ang hitsura niini nga dokumentasyon sa gihimo nga website:
Macros ug Module
Ang katuyoan sa DBT dili kaayo aron mahimong usa ka set sa mga script sa SQL, apan aron mahatagan ang mga tiggamit sa usa ka kusgan ug puno sa bahin nga paagi alang sa pagtukod sa ilang kaugalingon nga mga pagbag-o ug pag-apod-apod niini nga mga module.
Ang mga macro maoy mga hugpong sa mga konstruksyon ug mga ekspresyon nga matawag nga mga gimbuhaton sulod sa mga modelo. Gitugotan ka sa mga Macros nga magamit pag-usab ang SQL taliwala sa mga modelo ug proyekto uyon sa DRY (Ayaw Pag-usab sa Imong Kaugalingon) nga prinsipyo sa engineering.
Macro nga pananglitan:
{% 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 %}
Ug ang paggamit niini:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- Π²ΡΠ·ΠΎΠ² ΠΌΠ°ΠΊΡΠΎΡΠ°
from my_table
Ang DBT adunay usa ka manager sa pakete nga nagtugot sa mga tiggamit sa pagmantala ug paggamit pag-usab sa indibidwal nga mga module ug macro.
Kini nagpasabut nga maka-load ug makagamit sa mga librarya sama sa:
- : pagtrabaho kauban ang Petsa/Oras, Mga Surrogate Keys, mga pagsulay sa Schema, Pivot/Unpivot ug uban pa
- Andam nga gihimo nga mga template sa showcase para sa mga serbisyo sama sa ΠΈ
- Mga librarya para sa piho nga Data Stores, e.g.
- - Module alang sa pag-log sa operasyon sa DBT
Ang usa ka kompleto nga lista sa mga pakete makita sa .
Dugang nga mga bahin
Dinhi akong ihulagway ang pipila ka uban pang makapaikag nga mga bahin ug pagpatuman nga gigamit nako ug sa team sa pagtukod og Data Warehouse .
Pagbulag sa runtime environment DEV - TEST - PROD
Bisan sa sulod sa parehas nga cluster sa DWH (sa lainlaing mga laraw). Pananglitan, gamit ang mosunod nga ekspresyon:
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 -%}
)
Kini nga code literal nga nag-ingon: alang sa mga palibot dev, pagsulay, ci kuhaa ra ang datos sa miaging 3 ka adlaw ug wala na. Sa ato pa, ang pagdagan sa kini nga mga palibot mahimong labi ka paspas ug magkinahanglan gamay nga mga kapanguhaan. Kung nagdagan sa palibot prod ang kahimtang sa filter dili tagdon.
Pag-materyal gamit ang alternate column encoding
Ang Redshift usa ka columnar DBMS nga nagtugot kanimo sa pagtino sa data compression algorithms alang sa matag indibidwal nga column. Ang pagpili sa labing maayo nga mga algorithm makapakunhod sa espasyo sa disk sa 20-50%.
Macro ipatuman ang ANALYZE COMPRESSION command, paghimo ug bag-ong lamesa nga adunay girekomendar nga column encoding algorithms, gipiho nga segmentation keys (dist_key) ug sorting keys (sort_key), ibalhin ang data niini, ug, kon gikinahanglan, kuhaa ang daan nga kopya.
Macro nga pirma:
{{ 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) }}
Nagdagan ang modelo sa logging
Mahimo nimong i-attach ang mga kaw-it sa matag pagpatuman sa modelo, nga ipatuman sa dili pa ilunsad o dayon human mahuman ang paghimo sa modelo:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Ang module sa pag-log magtugot kanimo sa pagrekord sa tanan nga kinahanglan nga metadata sa usa ka lahi nga lamesa, nga pagkahuman magamit sa pag-audit ug pag-analisar sa mga bottleneck.
Kini ang hitsura sa dashboard base sa data sa pag-log sa Looker:
Automation sa Pagmentinar sa Pagtipig
Kung mogamit ka og pipila ka mga extension sa functionality sa gigamit nga Repository, sama sa UDF (User Defined Functions), nan ang pag-bersyon niini nga mga function, access control, ug automated rolling out sa mga bag-ong release sayon ββkaayo nga buhaton sa DBT.
Gigamit namo ang UDF sa Python para kuwentahon ang mga hash, email domains, ug bitmask decoding.
Usa ka pananglitan sa usa ka macro nga nagmugna og UDF sa bisan unsang execution environment (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 %}
Sa Wheely gigamit namo ang Amazon Redshift, nga gibase sa PostgreSQL. Alang sa Redshift, importante nga regular nga mangolekta og mga estadistika sa mga lamesa ug magbakante sa disk space - ang ANALYZE ug VACUUM nga mga sugo, matag usa.
Sa pagbuhat niini, ang mga sugo gikan sa redshift_maintenance macro kay gipatuman kada gabii:
{% 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
Posible nga gamiton ang DBT isip serbisyo (Managed Service). Apil:
- Web IDE alang sa pagpalambo sa mga proyekto ug mga modelo
- Pag-configure ug pag-iskedyul sa trabaho
- Yano ug sayon ββnga pag-access sa mga troso
- Website nga adunay dokumentasyon sa imong proyekto
- Pagdugtong sa CI (Padayon nga Paghiusa)
konklusyon
Ang pag-andam ug pagkonsumo sa DWH mahimong sama ka makalingaw ug mapuslanon sama sa pag-inom og smoothie. Ang DBT naglangkob sa Jinja, mga extension sa user (modules), usa ka compiler, usa ka executor, ug usa ka manager sa package. Pinaagi sa paghiusa niini nga mga elemento makakuha ka usa ka kompleto nga palibot sa pagtrabaho alang sa imong Data Warehouse. Walay mas maayong paagi sa pagdumala sa kausaban sulod sa DWH karon.
Ang mga pagtuo nga gisundan sa mga developers sa DBT giporma sama sa mosunod:
- Ang code, dili GUI, mao ang labing kaayo nga abstraction alang sa pagpahayag sa komplikado nga analytical logic
- Ang pagtrabaho sa datos kinahanglan nga mopahiangay sa labing kaayo nga mga gawi sa software engineering (Software Engineering)
- Ang kritikal nga imprastraktura sa datos kinahanglan kontrolahon sa komunidad sa tiggamit ingon open source software
- Dili lamang mga himan sa pag-analisa, apan ang code usab labi nga mahimong kabtangan sa Open Source nga komunidad
Kini nga mga kinauyokan nga mga pagtuo nakapatunghag usa ka produkto nga gigamit sa kapin sa 850 ka mga kompanya karon, ug kini nahimong sukaranan sa daghang mga kulbahinam nga mga extension nga himuon sa umaabot.
Alang sa mga interesado, adunay usa ka video sa usa ka bukas nga leksyon nga akong gihatag pipila ka bulan ang milabay isip bahin sa usa ka bukas nga leksyon sa OTUS - .
Dugang pa sa DBT ug Data Warehousing, isip kabahin sa Data Engineer nga kurso sa OTUS nga plataporma, ang akong mga kauban ug ako nagtudlo sa mga klase sa ubay-ubay nga uban pang may kalabutan ug modernong mga hilisgutan:
- Mga Konsepto sa Arkitektural alang sa Daghang Aplikasyon sa Data
- Pagpraktis sa Spark ug Spark Streaming
- Pagsuhid sa mga pamaagi ug mga himan alang sa pagkarga sa mga tinubdan sa datos
- Pagtukod ug analytical showcases sa DWH
- Mga konsepto sa NoSQL: HBase, Cassandra, ElasticSearch
- Mga prinsipyo sa pagmonitor ug orkestra
- Katapusan nga Proyekto: paghiusa sa tanan nga kahanas ubos sa suporta sa pagtudlo
Mga reperensiya:
- - Opisyal nga dokumentasyon
- β Repasuha ang artikulo sa usa sa mga tagsulat sa DBT
- - YouTube, Pagrekord sa usa ka bukas nga leksyon sa OTUS
- β Ang sunod nga bukas nga leksyon mao ang Mayo 15, 2020
- βOTUS
- - Usa ka pagtan-aw sa kaugmaon sa datos ug analytics
- - Ang ebolusyon sa analytics ug ang impluwensya sa Open Source
- - Mga prinsipyo sa pagtukod sa CI gamit ang DBT
- β Pagpraktis, Mga lakang sa lakang nga mga panudlo alang sa independente nga trabaho
- - Github, code sa proyekto sa edukasyon
Source: www.habr.com