Sa anong mga prinsipyo itinayo ang isang perpektong Data Warehouse?
Tumutok sa halaga ng negosyo at analytics sa kawalan ng boilerplate code. Pamamahala sa DWH bilang isang codebase: bersyon, pagsusuri, awtomatikong pagsubok at CI. Modular, extensible, open source at komunidad. User-friendly na dokumentasyon at dependency visualization (Data Lineage).
Higit pa tungkol sa lahat ng ito at tungkol sa papel ng DBT sa Big Data & Analytics ecosystem - welcome to cat.
Kumusta lahat
Si Artemy Kozyr ay nakikipag-ugnayan. Sa loob ng higit sa 5 taon, nagtatrabaho ako sa mga data warehouse, pagbuo ng ETL/ELT, pati na rin ang data analytics at visualization. Kasalukuyan akong nagtatrabaho sa , nagtuturo ako sa OTUS sa isang kurso , at ngayon gusto kong ibahagi sa iyo ang isang artikulo na isinulat ko sa pag-asa sa pagsisimula bagong enrollment para sa kurso.
Sinopsis
Ang balangkas ng DBT ay tungkol sa T sa ELT (Extract - Transform - Load) acronym.
Sa pagdating ng mga produktibo at nasusukat na analytical database gaya ng BigQuery, Redshift, Snowflake, walang saysay na gumawa ng mga pagbabago sa labas ng Data Warehouse.
Hindi nagda-download ang DBT ng data mula sa mga pinagmumulan, ngunit nagbibigay ng magagandang pagkakataon para sa pagtatrabaho sa data na na-load na sa Storage (sa Internal o External Storage).
Ang pangunahing layunin ng DBT ay kunin ang code, i-compile ito sa SQL, isagawa ang mga command sa tamang pagkakasunod-sunod sa Repository.
Istruktura ng Proyekto ng DBT
Ang proyekto ay binubuo ng mga direktoryo at mga file ng 2 uri lamang:
- Modelo (.sql) - isang yunit ng pagbabagong ipinahayag ng isang SELECT query
- Configuration file (.yml) - mga parameter, setting, pagsubok, dokumentasyon
Sa isang pangunahing antas, ang gawain ay nakabalangkas tulad ng sumusunod:
- Inihahanda ng user ang code ng modelo sa anumang maginhawang IDE
- Gamit ang CLI, inilunsad ang mga modelo, pinagsama-sama ng DBT ang code ng modelo sa SQL
- Ang pinagsama-samang SQL code ay isinasagawa sa Storage sa isang naibigay na pagkakasunod-sunod (graph)
Narito kung ano ang maaaring hitsura ng tumatakbo mula sa CLI:
Lahat ay PILI
Isa itong mamamatay na feature ng Data Build Tool framework. Sa madaling salita, ini-abstract ng DBT ang lahat ng code na nauugnay sa pagtupad sa iyong mga query sa Store (mga pagkakaiba-iba mula sa mga command na CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Ang anumang modelo ay nagsasangkot ng pagsulat ng isang SELECT query na tumutukoy sa resultang set ng data.
Sa kasong ito, ang lohika ng pagbabagong-anyo ay maaaring multi-level at pagsama-samahin ang data mula sa ilang iba pang mga modelo. Isang halimbawa ng isang modelo na bubuo ng isang showcase ng order (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
Anong mga kawili-wiling bagay ang makikita natin dito?
Una: Ginamit na CTE (Common Table Expressions) - upang ayusin at maunawaan ang code na naglalaman ng maraming pagbabago at lohika ng negosyo
Pangalawa: Ang code ng modelo ay pinaghalong SQL at wika (templating language).
Ang halimbawa ay gumagamit ng isang loop para upang bumuo ng halaga para sa bawat paraan ng pagbabayad na tinukoy sa expression itakda. Ginagamit din ang function Ref β ang kakayahang sumangguni sa iba pang mga modelo sa loob ng code:
- Sa panahon ng compilation Ref ay mako-convert sa isang target na pointer sa isang talahanayan o view sa Storage
- Ref nagbibigay-daan sa iyo na bumuo ng isang graph ng dependency ng modelo
Tamang-tama nagdaragdag ng halos walang limitasyong mga posibilidad sa DBT. Ang pinakakaraniwang ginagamit ay:
- If / else na mga pahayag - mga pahayag ng sangay
- Para sa mga loop
- Mga variable
- Macro - paglikha ng mga macro
Materialization: Table, View, Incremental
Ang diskarte sa materialization ay isang diskarte ayon sa kung saan ang magreresultang set ng data ng modelo ay maiimbak sa Storage.
Sa mga pangunahing termino ito ay:
- Talahanayan - pisikal na talahanayan sa Storage
- View - view, virtual na talahanayan sa Storage
Mayroon ding mas kumplikadong mga diskarte sa materialization:
- Incremental - incremental loading (ng malalaking fact table); ang mga bagong linya ay idinagdag, ang mga binagong linya ay ina-update, ang mga tinanggal na linya ay na-clear
- Ephemeral - ang modelo ay hindi direktang nagiging materyal, ngunit nakikilahok bilang isang CTE sa iba pang mga modelo
- Anumang iba pang mga diskarte na maaari mong idagdag sa iyong sarili
Bilang karagdagan sa mga diskarte sa materialization, may mga pagkakataon para sa pag-optimize para sa mga partikular na Storage, halimbawa:
- Snowflake: Lumilipas na mga talahanayan, Pagsasama-sama ng gawi, Pag-cluster ng talahanayan, Pagkopya ng mga gawad, Mga secure na view
- Redshift: Distkey, Sortkey (interleaved, compound), Late Binding Views
- BigQuery: Paghati at pag-cluster ng talahanayan, Pag-uugali ng Pagsamahin, KMS Encryption, Mga Label at Tag
- Dagitab: Format ng file (parquet, csv, json, orc, delta), partition_by, clustered_by, bucket, incremental_strategy
Ang mga sumusunod na Storage ay kasalukuyang sinusuportahan:
- Mga postgres
- Redshift
- BigQuery
- Snowflake
- Presto (bahagyang)
- Spark (bahagyang)
- Microsoft SQL Server (adaptor ng komunidad)
Pagbutihin natin ang ating modelo:
- Gawin nating incremental ang pagpuno nito (Incremental)
- Magdagdag tayo ng segmentation at sorting key 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
Isa rin itong dependency tree. Ito ay kilala rin bilang DAG (Directed Acyclic Graph).
Bumubuo ang DBT ng isang graph batay sa pagsasaayos ng lahat ng mga modelo ng proyekto, o sa halip, mga link ng ref() sa loob ng mga modelo sa iba pang mga modelo. Ang pagkakaroon ng graph ay nagbibigay-daan sa iyong gawin ang mga sumusunod na bagay:
- Pagpapatakbo ng mga modelo sa tamang pagkakasunod-sunod
- Parallelization ng storefront formation
- Pagpapatakbo ng arbitrary na subgraph
Halimbawa ng visualization ng graph:
Ang bawat node ng graph ay isang modelo; ang mga gilid ng graph ay tinukoy ng expression na ref.
Kalidad at Dokumentasyon ng Data
Bilang karagdagan sa pagbuo ng mga modelo mismo, pinapayagan ka ng DBT na subukan ang ilang mga pagpapalagay tungkol sa nagreresultang set ng data, tulad ng:
- Hindi Null
- Kakaiba
- Reference Integrity - referential integrity (halimbawa, customer_id sa talahanayan ng mga order ay tumutugma sa id sa talahanayan ng mga customer)
- Pagtutugma sa listahan ng mga katanggap-tanggap na halaga
Posibleng magdagdag ng sarili mong mga pagsubok (mga custom na pagsusuri sa data), gaya ng, halimbawa, % deviation ng kita na may mga indicator mula sa isang araw, isang linggo, isang buwan ang nakalipas. Ang anumang pagpapalagay na nabuo bilang isang SQL query ay maaaring maging isang pagsubok.
Sa ganitong paraan, maaari mong mahuli ang mga hindi gustong paglihis at mga error sa data sa mga window ng Warehouse.
Sa mga tuntunin ng dokumentasyon, ang DBT ay nagbibigay ng mga mekanismo para sa pagdaragdag, pag-bersyon, at pamamahagi ng metadata at mga komento sa modelo at maging sa mga antas ng katangian.
Ganito ang hitsura ng pagdaragdag ng mga pagsubok at dokumentasyon sa antas ng 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']
At narito ang hitsura ng dokumentasyong ito sa nabuong website:
Mga Macro at Module
Ang layunin ng DBT ay hindi gaanong maging isang set ng mga SQL script, ngunit upang bigyan ang mga user ng isang malakas at mayaman sa tampok na paraan para sa pagbuo ng kanilang sariling mga pagbabago at pamamahagi ng mga module na ito.
Ang mga macro ay mga hanay ng mga konstruksyon at expression na maaaring tawaging mga function sa loob ng mga modelo. Binibigyang-daan ka ng Macros na muling gamitin ang SQL sa pagitan ng mga modelo at proyekto alinsunod sa DRY (Don't Repeat Yourself) engineering principle.
Halimbawa ng macro:
{% 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 %}
At ang paggamit nito:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- Π²ΡΠ·ΠΎΠ² ΠΌΠ°ΠΊΡΠΎΡΠ°
from my_table
Ang DBT ay may kasamang manager ng package na nagbibigay-daan sa mga user na mag-publish at gumamit muli ng mga indibidwal na module at macro.
Nangangahulugan ito ng kakayahang mag-load at gumamit ng mga aklatan tulad ng:
- : nagtatrabaho sa Petsa/Oras, Mga Susi ng Kapalit, mga pagsubok sa Schema, Pivot/Unpivot at iba pa
- Handa nang mga template ng showcase para sa mga serbisyo tulad ng ΠΈ
- Mga aklatan para sa mga partikular na Data Store, hal.
- β Module para sa pag-log ng operasyon ng DBT
Ang isang kumpletong listahan ng mga pakete ay matatagpuan sa .
Higit pang mga tampok
Dito ay ilalarawan ko ang ilang iba pang mga kagiliw-giliw na tampok at pagpapatupad na ginagamit namin ng koponan upang bumuo ng isang Data Warehouse .
Paghihiwalay ng mga runtime environment DEV - TEST - PROD
Kahit na sa loob ng parehong DWH cluster (sa loob ng iba't ibang mga scheme). Halimbawa, gamit ang sumusunod na expression:
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 -%}
)
Literal na sinasabi ng code na ito: para sa mga kapaligiran dev, pagsubok, ci kumuha lang ng data sa huling 3 araw at wala na. Iyon ay, ang pagtakbo sa mga kapaligiran na ito ay magiging mas mabilis at mangangailangan ng mas kaunting mga mapagkukunan. Kapag tumatakbo sa kapaligiran prod hindi papansinin ang kundisyon ng filter.
Materialization na may kahaliling column encoding
Ang Redshift ay isang columnar DBMS na nagbibigay-daan sa iyong magtakda ng mga algorithm ng compression ng data para sa bawat indibidwal na column. Ang pagpili ng pinakamainam na mga algorithm ay maaaring mabawasan ang espasyo sa disk ng 20-50%.
Macro ay isasagawa ang ANALYZE COMPRESSION command, gagawa ng bagong table na may mga inirerekomendang column encoding algorithm, tinukoy na segmentation keys (dist_key) at sorting keys (sort_key), ilipat ang data dito, at, kung kinakailangan, tanggalin ang lumang kopya.
Macro signature:
{{ 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) }}
Tumatakbo ang modelo ng pag-log
Maaari kang mag-attach ng mga hook sa bawat execution ng modelo, na isasagawa bago ilunsad o kaagad pagkatapos makumpleto ang paglikha ng modelo:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Ang module ng pag-log ay magbibigay-daan sa iyo na itala ang lahat ng kinakailangang metadata sa isang hiwalay na talahanayan, na maaaring magamit sa pag-audit at pag-aralan ang mga bottleneck.
Ito ang hitsura ng dashboard batay sa data ng pag-log sa Looker:
Automation ng Storage Maintenance
Kung gagamit ka ng ilang extension ng functionality ng ginamit na Repository, gaya ng UDF (User Defined Functions), kung gayon ang pag-bersyon ng mga function na ito, access control, at awtomatikong paglulunsad ng mga bagong release ay napaka-maginhawang gawin sa DBT.
Gumagamit kami ng UDF sa Python para kalkulahin ang mga hash, email domain, at bitmask decoding.
Isang halimbawa ng macro na lumilikha ng UDF sa anumang 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 ginagamit namin ang Amazon Redshift, na batay sa PostgreSQL. Para sa Redshift, mahalagang regular na mangolekta ng mga istatistika sa mga talahanayan at magbakante ng espasyo sa disk - ang ANALYZE at VACUUM command, ayon sa pagkakabanggit.
Upang gawin ito, ang mga utos mula sa redshift_maintenance macro ay isinasagawa tuwing gabi:
{% 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
Posibleng gamitin ang DBT bilang isang serbisyo (Managed Service). Kasama:
- Web IDE para sa pagbuo ng mga proyekto at modelo
- Pag-configure at pag-iskedyul ng trabaho
- Simple at maginhawang pag-access sa mga log
- Website na may dokumentasyon ng iyong proyekto
- Pagkonekta ng CI (Patuloy na Pagsasama)
Konklusyon
Ang paghahanda at pagkonsumo ng DWH ay nagiging kasiya-siya at kapaki-pakinabang gaya ng pag-inom ng smoothie. Ang DBT ay binubuo ng Jinja, mga extension ng user (mga module), isang compiler, isang executor, at isang manager ng package. Sa pamamagitan ng pagsasama-sama ng mga elementong ito, makakakuha ka ng kumpletong kapaligiran sa pagtatrabaho para sa iyong Data Warehouse. Halos walang mas mahusay na paraan upang pamahalaan ang pagbabago sa loob ng DWH ngayon.
Ang mga paniniwalang sinusunod ng mga nag-develop ng DBT ay nabuo tulad ng sumusunod:
- Ang code, hindi GUI, ay ang pinakamahusay na abstraction para sa pagpapahayag ng kumplikadong analytical logic
- Ang pagtatrabaho sa data ay dapat umangkop sa mga pinakamahusay na kagawian sa software engineering (Software Engineering)
- Ang kritikal na imprastraktura ng data ay dapat kontrolin ng komunidad ng gumagamit bilang open source software
- Hindi lamang mga tool sa analytics, kundi pati na rin ang code ay magiging pag-aari ng Open Source na komunidad
Ang mga pangunahing paniniwalang ito ay nagbunga ng isang produkto na ginagamit ng higit sa 850 mga kumpanya ngayon, at sila ang nagiging batayan ng maraming kapana-panabik na mga extension na gagawin sa hinaharap.
Para sa mga interesado, mayroong isang video ng isang bukas na aralin na ibinigay ko ilang buwan na ang nakalipas bilang bahagi ng isang bukas na aralin sa OTUS - .
Bilang karagdagan sa DBT at Data Warehousing, bilang bahagi ng kursong Data Engineer sa platform ng OTUS, kami ng aking mga kasamahan ay nagtuturo ng mga klase sa ilang iba pang nauugnay at modernong paksa:
- Mga Konseptong Arkitektural para sa Mga Application ng Malaking Data
- Magsanay sa Spark at Spark Streaming
- Paggalugad ng mga pamamaraan at tool para sa pag-load ng mga pinagmumulan ng data
- Pagbuo ng mga analytical showcase sa DWH
- Mga konsepto ng NoSQL: HBase, Cassandra, ElasticSearch
- Mga prinsipyo ng pagsubaybay at orkestrasyon
- Pangwakas na Proyekto: pagsasama-sama ng lahat ng mga kasanayan sa ilalim ng suporta sa mentoring
Link:
- - Opisyal na dokumentasyon
- β Suriin ang artikulo ng isa sa mga may-akda ng DBT
- β YouTube, Pagre-record ng isang bukas na aralin sa OTUS
- β Ang susunod na bukas na aralin ay Mayo 15, 2020
- βOTUS
- β Isang pagtingin sa kinabukasan ng data at analytics
- β Ang ebolusyon ng analytics at ang impluwensya ng Open Source
- β Mga prinsipyo ng pagbuo ng CI gamit ang DBT
- β Pagsasanay, Hakbang-hakbang na mga tagubilin para sa malayang gawain
- β Github, code ng proyektong pang-edukasyon
Pinagmulan: www.habr.com