Yeyiphi imigaqo apho iNdawo yokugcina iDatha efanelekileyo yakhiwe?

Gxininisa kwixabiso leshishini kunye nohlalutyo ngokungabikho kwekhowudi ye-boilerplate. Ukulawula i-DWH njengesiseko sekhowudi: uguqulelo, uphononongo, uvavanyo oluzenzekelayo kunye neCI. Imodyuli, eyandisiweyo, umthombo ovulekileyo kunye noluntu. Amaxwebhu asebenziseka lula kunye nokubonwa kokuxhomekeka (Uluhlu lweDatha).

Okunye malunga nayo yonke le nto kunye nendima ye-DBT kwiDatha enkulu kunye ne-Analytics ecosystem - wamkelekile kwikati.

Molweni nonke

U-Artemy Kozyr unxibelelana naye. Ngaphezulu kweminyaka eyi-5 ndisebenza kunye neendawo zokugcina idatha, ukwakha i-ETL / ELT, kunye nohlalutyo lwedatha kunye nokubonwa. Ndisebenza ngoku esinamavili, Ndifundisa ikhosi kwi-OTUS Injini Yedatha, kwaye namhlanje ndifuna ukwabelana nawe ngenqaku endilibhalileyo ngokulindela ukuqala ubhaliso olutsha kule khosi.

Uphononongo olufutshane

Isakhelo se-DBT simalunga ne-T kwi-ELT (I-Extract - Transform - Load) isifinyezo.

Ngokufika koluhlu lwedatha olunemveliso kunye nohlalutyayo njengeBigQuery, Redshift, Snowflake, kwakungekho sizathu sokwenza iinguqu ngaphandle kweNdawo yokuGcina iDatha.

I-DBT ayikhupheli idatha kwimithombo, kodwa ibonelela ngamathuba amahle okusebenza ngedatha esele ilayishiwe kwiSitora (kwiNdawo yangaphakathi okanye yaNgaphandle).

Eyona njongo iphambili ye-DBT kukuthatha ikhowudi, ukuyiqokelela kwi-SQL, yenza imiyalelo ngokulandelelana okuchanekileyo kwi-Repository.

Isakhiwo seProjekthi ye-DBT

Le projekthi iqulathe abalawuli kunye neefayile zeentlobo ezi-2 kuphela:

Umzekelo (.sql) - iyunithi yenguqu echazwe ngumbuzo KHETHA
Ifayile yoqwalaselo (.yml) - iiparamitha, izicwangciso, iimvavanyo, amaxwebhu

Kwinqanaba elisisiseko, umsebenzi wakhiwe ngolu hlobo lulandelayo:

Umsebenzisi ulungiselela ikhowudi yemodeli kuyo nayiphi na i-IDE efanelekileyo
Ukusebenzisa i-CLI, iimodeli ziqalisiwe, i-DBT iqulunqa ikhowudi yomzekelo kwi-SQL
Ikhowudi yeSQL eqokelelweyo isenziwa kuGcino ngolandelelwano olunikiweyo (igrafu)

Nantsi into yokubaleka kwi-CLI inokubonakala ngathi:

Yonke into KHETHA

Olu luphawu olubulalayo lwesakhelo seSixhobo soKwakha iDatha. Ngamanye amazwi, i-DBT itsala yonke ikhowudi eyayanyaniswa nokwenza imibuzo yakho kwiVenkile (iiyantlukwano ezivela kwimiyalelo YENZA, YIFAKA, HLAZIYA, CUMA I-ALTER, GRANT, ...).

Nayiphi na imodeli ibandakanya ukubhala umbuzo KHETHA omnye ochaza isethi yedatha enesiphumo.

Kule meko, ingqiqo yokuguqulwa ingaba ngamanqanaba amaninzi kwaye idibanise idatha esuka kwezinye iimodeli ezininzi. Umzekelo wemodeli eya kwakha umboniso weodolo (f_oda):

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

Ziziphi izinto ezibangela umdla esizibona apha?

Okokuqala: I-CTE esetyenzisiweyo (i-Common Table Expressions) - ukulungelelanisa nokuqonda ikhowudi equlethe iinguqu ezininzi kunye nengqiqo yezoshishino.

Okwesibini: Ikhowudi yomzekelo ngumxube weSQL kunye nolwimi Jinja (ulwimi lokulinganisa).

Umzekelo usebenzisa i-loop kuba ukuvelisa imali yendlela nganye yokuhlawula echazwe kwintetho iseti. Umsebenzi uyasetyenziswa kwakhona Ref - ukukwazi ukubhekisa kwezinye iimodeli ngaphakathi kwekhowudi:

Ngexesha lokuhlanganiswa Ref izakuguqulelwa kwisalathisi ekujoliswe kuso kwitafile okanye imboniselo kuGcino
Ref ikuvumela ukuba wakhe imodeli yokuxhomekeka kwegrafu

Kwakukho Jinja yongeza phantse amathuba angenamkhawulo kwi-DBT. Ezona zisetyenziswa kakhulu zezi:

Ukuba / enye ingxelo - iinkcazo zesebe
Kuba iilophu - imijikelo
Izinto eziguquguqukayo
Imakhro - ukwenza iimacros

Ukwenziwa kwezinto: Itheyibhile, imboniselo, eyongezelelweyo

Isicwangciso sokwenziwa kwezinto yinkqubo apho iseti yeziphumo zedatha yemodeli iya kugcinwa kuGcino.

Ngokwesiseko sithi:

Itheyibhile - itafile ebonakalayo kwindawo yoGcino
Jonga - jonga, itafile ebonakalayo kwindawo yoGcino

Kukwakho nezicwangciso zokwenziwa kwezinto ezintsonkothileyo:

Ukongezwa - ukulayisha okongeziweyo (kweetheyibhile ezinkulu zeenyaniso); imigca emitsha yongezwa, imigca etshintshileyo iyahlaziywa, imigca ecinyiweyo iyacinywa
I-Ephemeral - imodeli ayifuni ngokuthe ngqo, kodwa ithatha inxaxheba njenge-CTE kwezinye iimodeli
Nawaphi na amanye amacebo ungazongeza ngokwakho

Ukongeza kwizicwangciso zokwenziwa kwezinto, kukho amathuba okuphucula ukugcinwa kwezinto ezithile, umzekelo:

Snowflake: Iitafile ezidlulayo, Ukudibanisa ukuziphatha, Ukuhlanganiswa kweTheyibhile, Ukukhuphela izibonelelo, iimboniselo ezikhuselekileyo
Redshift: I-Distkey, i-Sortkey (i-interleaved, i-compound), i-Late Binding Views
enkulu: Ukwahlulahlula kwetheyibhile kunye nokuhlanganisa, Hlanganisa isimilo, Ufihlo lweKMS, Iileyibhile kunye neeThegi
spark: Ifomathi yefayile (parquet, csv, json, orc, delta), ulwahlulo_nge, ludityaniswe_nge, iibhakethi, isicwangciso_songezo

Ezi ziGcino zilandelayo ziyaxhaswa okwangoku:

Iiposti
Redshift
enkulu
Snowflake
Presto (ngokungaphelelanga)
Intlantsi (inxalenye)
Iseva kaMicrosoft SQL (iadaptha yasekuhlaleni)

Masiyiphucule imodeli yethu:

Masenze ukuzaliswa kwayo kunyuswe (Ukunyusa)
Masidibanise icandelo kunye nezitshixo zokuhlela ze-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

Igrafu yokuxhomekeka kwemodeli

Ikwangumthi wokuxhomekeka. Ikwabizwa ngokuba yi-DAG (I-Directed Acyclic Graph).

I-DBT yakha igrafu esekelwe kuqwalaselo lwayo yonke imifuziselo yeprojekthi, okanye kunoko, i-ref () ikhonkco ngaphakathi kwemifuziselo ukuya kweminye imifuziselo. Ukuba negrafu kukuvumela ukuba wenze ezi zinto zilandelayo:

Iimodeli ezibalekayo ngolandelelwano oluchanekileyo
Ukudityaniswa kokwenziwa kwevenkile engaphambili
Ukuqhuba i-subgraph engenamkhethe

Umzekelo wokubonwa kwegrafu:

NONE

Umgangatho weDatha kunye noXwebhu

Ukongeza ekuveliseni iimodeli ngokwazo, i-DBT ikuvumela ukuba uvavanye inani leentelekelelo malunga nesiphumo seseti yedatha, efana nale:

Hayi Null
eyodwa
IMfezeko yoReferensi - intembeko yereferensi (umzekelo, umthengi_id kwitafile yeeodolo ihambelana ne-id kwitafile yabathengi)
Ukutshatisa uluhlu lwamaxabiso amkelekileyo

Kunokwenzeka ukuba ungeze iimvavanyo zakho (uvavanyo lwedatha yesiko), njengokuba, umzekelo, i-% ukuphambuka kwengeniso kunye nezalathi ukusuka kwimini, ngeveki, kwinyanga edlulileyo. Naluphi na uqikelelo olwenziwe njengombuzo weSQL lunokuba luvavanyo.

Ngale ndlela, unokubamba ukutenxa okungafunwayo kunye neempazamo kwidatha kwiifestile zeWarehouse.

Ngokubhekiselele kumaxwebhu, i-DBT ibonelela ngeendlela zokongeza, uguqulelo, kunye nokusasazwa kwemethadatha kunye nezimvo kumzekelo kunye namanqanaba ophawu.

Nantsi into eyongeza iimvavanyo kunye namaxwebhu ajongeka njani kwinqanaba lefayile yoqwalaselo:

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

Kwaye nantsi indlela olu xwebhu lujongeka ngayo kwiwebhusayithi eyenziweyo:

IiMacros kunye neeModyuli

Injongo ye-DBT ayikho kakhulu ukuba ibe yisethi yeskripthi ze-SQL, kodwa ukubonelela abasebenzisi ngeendlela ezinamandla kunye nezityebileyo zokwakha iinguqu zabo kunye nokusabalalisa ezi modyuli.

IiMacros ziiseti zolwakhiwo kunye namazwi anokuthiwa njengemisebenzi ngaphakathi kwemodeli. IiMacros zikuvumela ukuba usebenzise kwakhona i-SQL phakathi kweemodeli kunye neeprojekthi ngokuhambelana ne-DRY (Musa ukuphindaphinda) umgaqo wobunjineli.

Umzekelo omkhulu:

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

Kwaye nokusetyenziswa kwayo:

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

I-DBT iza nomphathi wephakheji evumela abasebenzisi ukuba bapapashe kwaye basebenzise kwakhona iimodyuli kunye neemacros.

Oku kuthetha ukukwazi ukulayisha nokusebenzisa amathala eencwadi afana nala:

dbt_izixhobo: ukusebenza kunye noMhla / ixesha, izitshixo zokungena, iimvavanyo zeSchema, iPivot / i-Unpivot kunye nabanye
Iitemplates zokubonisa ezisele zenziwe ngeenkonzo ezifana Ikhephu и ngombala
Amathala eencwadi kwiiVenkile zeDatha ezithile, umz. Redshift
Ukungena -Imodyuli yokuloga ukusebenza kweDBT

Uluhlu olupheleleyo lweepakethe lunokufumaneka apha dbt hub.

Iimpawu ezingakumbi

Apha ndiza kuchaza ezinye izinto ezimbalwa ezinomdla kunye nokuphunyezwa endisebenzisa mna neqela ukwakha iNdawo yokuGcina iDatha esinamavili.

Ukwahlulwa kweendawo zexesha lokusebenza DEV - TEST - PROD

Nangaphakathi kweqela elinye leDWH (ngaphakathi kwezikimu ezahlukeneyo). Umzekelo, usebenzisa eli binzana lilandelayo:

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

Le khowudi ngokwenyani ithi: kwimo engqongileyo dev, uvavanyo, ci thatha idatha kuphela kwiintsuku ezi-3 zokugqibela kwaye akukho ngaphezulu. Oko kukuthi, ukusebenza kwezi meko kuya kukhawuleza kwaye kufuna izixhobo ezimbalwa. Xa ubaleka kwindawo Qhuba imeko yokucoca ayisayi kuhoywa.

Ukwenziwa kwezinto ngolunye ubhalo lwekhowudo kwikholamu

I-Redshift yi-columnar DBMS ekuvumela ukuba usete i-algorithms yoxinzelelo lwedatha kwikholamu nganye. Ukukhetha i-algorithms efanelekileyo kunokunciphisa indawo yediski nge-20-50%.

Ubukhulu becala redshift.compress_table iya kwenza i HLALUTYA COMPRESSION umyalelo, yenza itheyibhile entsha kunye nencopho ecetyiswayo yekholamu yee-algorithms, izitshixo ezikhankanyiweyo zezahlulo (dist_key) kunye nezitshixo zokuhlela (sort_key), ukudlulisela idatha kuyo, kwaye, ukuba kuyimfuneko, cima ikopi endala.

Utyikityo olukhulu:

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

Imodeli yokugawulwa kwemithi iyasebenza

Unokuqhoboshela iigwegwe ekuqhutyweni kwemodeli nganye, eya kwenziwa ngaphambi kokuqaliswa okanye ngokukhawuleza emva kokudalwa kwemodeli kugqityiwe:

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

Imodyuli yokugawulwa kwemithi iya kukuvumela ukuba urekhode yonke imethadatha eyimfuneko kwitafile eyahlukileyo, enokuthi emva koko isetyenziswe ukuphicotha kunye nokuhlalutya iibhotile.

Le yindlela ideshbhodi ejongeka ngayo ngokusekwe kwidatha yokungena kwiLooker:

Ukuzenzekela koGcino lokuGcina

Ukuba usebenzisa ezinye izandiso zokusebenza kovimba osetyenzisiweyo, njenge-UDF (iMisebenzi echaziweyo yomsebenzisi), ngoko uguqulelo lwale misebenzi, ulawulo lofikelelo, kunye nokukhutshwa okuzenzekelayo kokukhutshwa kwezinto ezintsha kulula kakhulu ukwenza kwi-DBT.

Sisebenzisa i-UDF kwiPython ukubala i-hashes, imimandla ye-imeyile, kunye ne-bitmask decoding.

Umzekelo we-macro eyenza i-UDF kuyo nayiphi na imeko-bume yophumezo (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 %}

KwiWheely sisebenzisa iAmazon Redshift, esekwe kwiPostgreSQL. Kwi-Redshift, kubalulekile ukuqokelela rhoqo izibalo kwiitafile kwaye ukhulule indawo yediski - i-ANALYZE kunye ne-VACUUM imiyalelo, ngokulandelanayo.

Ukwenza oku, imiyalelo evela kwi-redshift_maintenance macro isetyenziswa rhoqo ebusuku:

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

Kuyenzeka ukusebenzisa iDBT njengenkonzo (iNkonzo eLawulwayo). Ibandakanyiwe:

I-Web IDE yokuphuhlisa iiprojekthi kunye neemodeli
Ubume bomsebenzi kunye nokucwangcisa
Ufikelelo olulula nolufanelekileyo kwiilogi
Iwebhusayithi enamaxwebhu eprojekthi yakho
Ukuqhagamshela i-CI (Uhlanganiso oluqhubekayo)

isiphelo

Ukulungiselela kunye nokusebenzisa i-DWH kuba mnandi kwaye kunenzuzo njengokusela i-smoothie. I-DBT iquka iJinja, izandiso zabasebenzisi (iimodyuli), umqokeleli, umenzi, kunye nomphathi wephakheji. Ngokudibanisa ezi zinto ufumana indawo yokusebenza epheleleyo yeWarehouse yakho yeDatha. Akukho ndlela ingcono yokulawula inguqu kwi-DWH namhlanje.

Iinkolelo ezilandelwa ngabaphuhlisi be-DBT zenziwe ngolu hlobo lulandelayo:

Ikhowudi, hayi i-GUI, yeyona nto ilungileyo yokukhupha inkcazo yokucinga okuntsokothileyo kohlalutyo
Ukusebenza ngedatha kufuneka kuhlengahlengise iindlela ezilungileyo kubunjineli besoftware (ubunjineli beSoftware)

Iziseko ezingundoqo zedatha kufuneka zilawulwe luluntu lwabasebenzisi njengesoftware yomthombo ovulekileyo
Ayizizo izixhobo zokuhlalutya kuphela, kodwa kunye nekhowudi iya kuba yipropathi yoMthombo ovulekileyo woluntu

Ezi nkolelo ziphambili ziye zavelisa imveliso esetyenziswa ziinkampani ezingaphezu kwama-850 namhlanje, kwaye zenza isiseko sezandiso ezininzi ezinomdla eziza kwenziwa kwixesha elizayo.

Kwabo banomdla, kukho ividiyo yesifundo esivulekileyo endisinike kwiinyanga ezimbalwa ezidlulileyo njengenxalenye yesifundo esivulekileyo kwi-OTUS - Isixhobo soKwakha iDatha ye-Amazon Redshift yoGcino.

Ukongeza kwi-DBT kunye ne-Data Warehousing, njengenxalenye yekhosi yeNjineli yeDatha kwi-platform ye-OTUS, oogxa bam kunye nam sifundisa iiklasi ngenani lezinye izihloko ezifanelekileyo kunye nezanamhlanje:

Iingcinga zoYilo lweZicelo zeDatha enkulu
Ziqhelanise ne-Spark kunye ne-Spark Streaming
Ukuphonononga iindlela kunye nezixhobo zokulayisha imithombo yedatha
Ukwakha imiboniso yohlalutyo kwi-DWH
Iikhonsepthi zeNoSQL: HBase, Cassandra, ElasticSearch
Imigaqo yokubeka iliso kunye ne-orchestration
Iprojekthi yokugqibela: ukubeka zonke izakhono kunye phantsi kwenkxaso yoqeqesho

Izalathiso:

Amaxwebhu eDBT-Intshayelelo - Amaxwebhu asemthethweni
Yintoni, kanye, i-dbt? - Inqaku lokuphonononga ngomnye wababhali be-DBT
Isixhobo soKwakha iDatha ye-Amazon Redshift yoGcino -IYouTube, Ukurekhodwa kwesifundo esivulekileyo se-OTUS
Ukwazi iGreenplum — Isifundo esilandelayo esivuliweyo nguMeyi 15, 2020
Ikhosi yobuNjineli beDatha —OTUS
Ukwakha uHamba lokuSebenza oluQolileyo -Ukujongwa kwikamva ledatha kunye nohlalutyo
Lixesha lokuhlalutya umthombo ovulekileyo -Ukuvela kohlahlelo kunye nefuthe loMthombo oVulekileyo
Udibaniso oluqhubekayo kunye noVavanyo loKwakha oluzenzekelayo kunye ne-dbtCloud - Imigaqo yokwakha iCI usebenzisa iDBT
Ukuqalisa nge DBT tutorial - Ukuziqhelanisa, imiyalelo yesinyathelo-nge-nyathelo yomsebenzi ozimeleyo
Jaffle shop - Github DBT Tutorial -Github, ikhowudi yeprojekthi yezemfundo

Funda ngakumbi malunga nekhosi.

umthombo: www.habr.com

Isixhobo soKwakha iDatha okanye yintoni eqhelekileyo phakathi kweNdawo yokugcina iDatha kunye neSmoothie