Ndi mfundo ziti zomwe malo abwino osungiramo data amamangidwa?

Yang'anani pamtengo wamabizinesi ndi ma analytics pakalibe boilerplate code. Kuwongolera DWH ngati codebase: kumasulira, kubwereza, kuyezetsa makina ndi CI. Modular, extensible, open source and community. Zolemba zosavuta kugwiritsa ntchito komanso mawonekedwe odalira (Data Lineage).

Zambiri za izi komanso za udindo wa DBT mu Big Data & Analytics ecosystem - talandiridwa kumphaka.

moni nonse

Artemy Kozyr akulumikizana. Kwa zaka zoposa 5 ndakhala ndikugwira ntchito ndi malo osungiramo deta, kumanga ETL / ELT, komanso kusanthula deta ndi kuwonetseratu. Panopa ndikugwira ntchito ku wamagudumu, Ndimaphunzitsa ku OTUS pa maphunziro Katswiri wa Zolemba, ndipo lero ndikufuna kugawana nanu nkhani yomwe ndinalemba poyembekezera chiyambi olembetsa atsopano pamaphunzirowa.

Ndemanga yachidule

Dongosolo la DBT limakhudza zonse za T mu ELT (Extract - Transform - Load) acronym.

Kubwera kwa nkhokwe zowunikira komanso zowunikira ngati BigQuery, Redshift, Snowflake, panalibe chifukwa chosinthira kunja kwa Data Warehouse. 

DBT sitsitsa deta kuchokera ku magwero, koma imapereka mwayi waukulu wogwira ntchito ndi deta yomwe yalowetsedwa kale mu Kusungirako (Mukati kapena Kusungirako Kwanja).

Cholinga chachikulu cha DBT ndikutenga kachidindoyo, kuyipanga mu SQL, ndikuchita malamulowo motsatira ndondomeko yoyenera mu Repository.

DBT Project Mapangidwe

Ntchitoyi imakhala ndi zolemba ndi mafayilo amitundu iwiri yokha:

• Chitsanzo (.sql) - gawo losinthika lowonetsedwa ndi funso la SELECT
• Fayilo yokonzekera (.yml) - magawo, zoikamo, mayesero, zolemba

Mulingo woyambira, ntchitoyo imapangidwa motere:

• Wogwiritsa ntchito amakonza khodi yachitsanzo mu IDE iliyonse yabwino
• Pogwiritsa ntchito CLI, mitundu imayambitsidwa, DBT imapanga khodi yachitsanzo kukhala SQL
• Khodi yophatikizidwa ya SQL imachitidwa mu Kusungirako motsatizana (graph)

Izi ndi zomwe kuthamanga kuchokera ku CLI kungawonekere:

Chilichonse CHOSAKHA

Ichi ndi chinthu chakupha cha chimango cha Data Build Tool. Mwanjira ina, DBT imachotsa ma code onse okhudzana ndi kupanga mafunso anu mu Store (zosiyana ndi malamulo CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).

Mtundu uliwonse umaphatikizapo kulemba funso limodzi losankhira lomwe limatanthawuza seti ya deta.

Pankhaniyi, malingaliro osinthika amatha kukhala amitundu yambiri ndikuphatikiza deta kuchokera kumitundu ina ingapo. Chitsanzo chachitsanzo chomwe chingapange chiwonetsero cha maoda (f_order):

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

Ndi zinthu zochititsa chidwi ziti zomwe tikuwona pano?

Choyamba: Ntchito CTE (Common Table Expressions) - kukonza ndikumvetsetsa ma code omwe ali ndi masinthidwe ambiri ndi malingaliro abizinesi

Chachiwiri: Code code ndi chisakanizo cha SQL ndi chinenero Jinja (chiyankhulo cha template).

Chitsanzo chimagwiritsa ntchito lupu chifukwa kupanga ndalama panjira iliyonse yolipira yomwe yafotokozedwa m'mawuwo akonzedwa. Njirayi imagwiritsidwanso ntchito ref - Kutha kutchula mitundu ina mkati mwa code:

• Pakuphatikiza ref idzasinthidwa kukhala cholozera chandamale kukhala tebulo kapena kuwona mu Kusunga
• ref amakulolani kuti mupange graph yodalira chitsanzo

Icho chinali Jinja imawonjezera mwayi wopanda malire ku DBT. Zomwe zimagwiritsidwa ntchito kwambiri ndi:

• Ngati / zina ziganizo - ziganizo za nthambi
• Za malupu
• Zosintha
• Macro - kupanga macros

Kupanga Zinthu: Table, View, Zowonjezera

Njira yopangira zinthu ndi njira yotengera zomwe zotsatira za data zachitsanzo zidzasungidwa mu Kusungirako.

M'mawu oyambira ndi:

• Table - tebulo lakuthupi mu yosungirako
• Onani - onani, tebulo lenileni mu Storage

Palinso njira zovuta zopangira zinthu:

• Zowonjezera - kutsitsa kwakukulu (kwa matebulo akuluakulu); mizere yatsopano imawonjezedwa, mizere yosinthidwa imasinthidwa, mizere yochotsedwa imachotsedwa 
• Ephemeral - chitsanzo sichimangokhala mwachindunji, koma chimatenga nawo mbali ngati CTE mumitundu ina
• Njira zina zilizonse zomwe mungawonjezere nokha

Kuphatikiza pa njira zopangira zinthu zakuthupi, pali mwayi wokhathamiritsa Zosungirako zinazake, mwachitsanzo:

• Snowflake: Matebulo osakhalitsa, Gwirizanitsani machitidwe, Kuphatikizira patebulo, Kukopera zopereka, Mawonedwe otetezedwa
• Kusintha: Distkey, Sortkey (yolowetsedwa, pawiri), Mawonedwe Akumapeto Kwambiri
• BigQuery: Kugawa kwatebulo & kusanja, Gwirizanitsani khalidwe, KMS Encryption, Labels & Tags
• Kuthamanga: Mafayilo (parquet, csv, json, orc, delta), partition_by, clustered_by, ndowa, incremental_strategy

Zosungira zotsatirazi ndizothandizira pano:

• Zolemba
• Kusintha
• BigQuery
• Snowflake
• Presto (pang'ono)
• Spark (pang'ono)
• Microsoft SQL Server (adapter yamagulu)

Tiyeni tiwongolere chitsanzo chathu:

• Tiyeni tiwonjezere kudzaza kwake (Kuwonjezera)
• Tiyeni tiwonjezere magawo ndikusankha makiyi a 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

Chitsanzo chodalira graph

Ndi mtengo wodalira. Imadziwikanso kuti DAG (Directed Acyclic Graph).

DBT imapanga ma graph kutengera masanjidwe amitundu yonse yama projekiti, kapena m'malo mwake, ref() maulalo mkati mwamitundu kupita kumitundu ina. Kukhala ndi graph kumakupatsani mwayi wochita izi:

• Kuthamanga zitsanzo mu ndondomeko yolondola
• Kufanana kwa mapangidwe a sitolo
• Kuyendetsa subgraph mosasintha 

Chitsanzo cha mawonekedwe a graph:

Node iliyonse ya graph ndi chitsanzo; m'mphepete mwa graph amatchulidwa ndi mawu akuti ref.

Ubwino wa Data ndi Zolemba

Kuphatikiza pakupanga mitundu yokhayo, DBT imakulolani kuti muyese malingaliro angapo okhudza zomwe zachitika, monga:

• Osati Null
• lapadera
• Reference Integrity - kukhulupirika kotsimikizika (mwachitsanzo, kasitomala_id patebulo la maoda amafanana ndi id patebulo lamakasitomala)
• Kufananiza mndandanda wazinthu zovomerezeka

N'zotheka kuwonjezera mayesero anu (mayeso a deta), monga, mwachitsanzo, % kupatuka kwa ndalama ndi zizindikiro kuyambira tsiku, sabata, mwezi wapitawo. Lingaliro lililonse lopangidwa ngati funso la SQL litha kukhala mayeso.

Mwanjira imeneyi, mutha kugwira zopatuka zapathengo ndi zolakwika mu data mu Warehouse mawindo.

Pankhani ya zolembedwa, DBT imapereka njira zowonjezerera, kumasulira, ndi kugawa metadata ndi ndemanga pazachitsanzo komanso ngakhale milingo. 

Izi ndi zomwe kuwonjezera mayeso ndi zolemba kumawoneka ngati mulingo wamafayilo:

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

Ndipo izi ndi momwe zolembedwazi zimawonekera patsamba lopangidwa:

Macros ndi ma modules

Cholinga cha DBT sichochuluka kwambiri kuti mukhale seti ya zolemba za SQL, koma kupatsa ogwiritsa ntchito njira zamphamvu komanso zolemera zopangira zosintha zawo ndikugawa ma module awa.

Macros ndi magulu a zomanga ndi mawu omwe angatchulidwe ngati ntchito mkati mwa zitsanzo. Macros amakulolani kuti mugwiritsenso ntchito SQL pakati pa zitsanzo ndi mapulojekiti molingana ndi mfundo ya uinjiniya ya DRY (Musabwereze Nokha).

Macro chitsanzo:

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

Ndipo ntchito zake:

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

DBT imabwera ndi woyang'anira phukusi yemwe amalola ogwiritsa ntchito kusindikiza ndikugwiritsanso ntchito ma module ndi ma macros.

Izi zikutanthauza kutha kutsitsa ndikugwiritsa ntchito malaibulale monga:

• dbt_zida: kugwira ntchito ndi Date/Time, Surrogate Keys, mayeso a Schema, Pivot/Unpivot ndi ena
• Ma tempulo okonzekera okonzeka a mautumiki monga Chipale chofewa и Sungani 
• Malaibulale a Masitolo a Data enieni, mwachitsanzo. Kusintha 
• Kulemba - Module yodula mitengo ya DBT

Mndandanda wathunthu wamaphukusi angapezeke pa dbt ku.

Ngakhale zambiri

Apa ndifotokoza zina zingapo zosangalatsa ndi kukhazikitsa komwe ine ndi gulu timagwiritsa ntchito pomanga Malo Osungiramo Zinthu wamagudumu.

Kupatukana kwa malo othamanga DEV - TEST - PROD

Ngakhale mkati mwa gulu lomwelo la DWH (m'machitidwe osiyanasiyana). Mwachitsanzo, kugwiritsa ntchito mawu otsatirawa:

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

Khodi iyi imati: za chilengedwe dev, test, ci tengani zambiri zamasiku atatu omaliza okha osapitilirapo. Ndiko kuti, kuthamanga m'malo awa kudzakhala kofulumira kwambiri ndipo kumafunikira zinthu zochepa. Pamene kuthamanga pa chilengedwe kapulidwe mawonekedwe a fyuluta adzanyalanyazidwa.

Kupanga zinthu ndi kabisidwe kosinthira ndime

Redshift ndi columnar DBMS yomwe imakulolani kuti muyike ma algorithms a compression a data pagawo lililonse. Kusankha ma aligorivimu abwino kumatha kuchepetsa malo a disk ndi 20-50%.

Macro redshift.compress_table ipereka lamulo la ANALYZE COMPRESSION, pangani tebulo latsopano lokhala ndi ma aligorivimu ovomerezeka, makiyi agawidwe (dist_key) ndi makiyi osanja (sort_key), kusamutsa detayo, ndipo, ngati kuli kofunikira, chotsani kopi yakale.

Siginecha yayikulu:

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

Mtundu wodula mitengo umayenda

Mutha kumangirira mbedza pachiwonetsero chilichonse chachitsanzocho, chomwe chidzachitike musanayambike kapena atangomaliza kupanga fanizolo:

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

Gawo lodula mitengo limakupatsani mwayi kuti mujambule ma metadata onse ofunikira patebulo lina, lomwe pambuyo pake lingagwiritsidwe ntchito kuwunika ndikuwunika zovuta.

Izi ndi zomwe dashboard imawonekera kutengera deta yodula mitengo mu Looker:

Makina Osungirako Kusungirako

Ngati mugwiritsa ntchito zowonjezera za magwiridwe antchito a Malo Ogwiritsidwa Ntchito, monga UDF (User Defined Functions), ndiye kuti kumasulira kwa magwiridwe antchitowa, kuwongolera mwayi, ndi kutulutsa zatsopano ndizosavuta kuchita mu DBT.

Timagwiritsa ntchito UDF ku Python kuwerengera ma hashes, madera a imelo, ndi kumasulira kwa bitmask.

Chitsanzo cha macro chomwe chimapanga UDF pamalo aliwonse opangira (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 %}

Ku Wheely timagwiritsa ntchito Amazon Redshift, yomwe imachokera ku PostgreSQL. Kwa Redshift, ndikofunikira kusonkhanitsa ziwerengero pafupipafupi pamatebulo ndikumasula malo a disk - malamulo a ANALYZE ndi VACUUM, motsatana.

Kuti muchite izi, malamulo ochokera ku redshift_maintenance macro amachitidwa usiku uliwonse:

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

Mtambo wa DBT

Ndizotheka kugwiritsa ntchito DBT ngati ntchito (Managed Service). Zinalipo:

• Webusaiti IDE yopanga ma projekiti ndi mitundu
• Kusintha kwa ntchito ndi ndandanda
• Zosavuta komanso zosavuta kupeza zipika
• Webusaiti yokhala ndi zolemba za polojekiti yanu
• Kulumikiza CI (Kuphatikizana Kopitilira)

Pomaliza

Kukonzekera ndi kudya DWH kumakhala kosangalatsa komanso kopindulitsa monga kumwa mowa wotsekemera. DBT imakhala ndi Jinja, zowonjezera za ogwiritsa ntchito (ma module), wophatikiza, wowongolera, ndi woyang'anira phukusi. Mwa kuphatikiza zinthu izi mumapeza malo ogwirira ntchito a Data Warehouse yanu. Palibe njira yabwinoko yoyendetsera kusintha kwa DWH lero.

Zikhulupiriro zotsatiridwa ndi omwe amapanga DBT zidapangidwa motere:

• Code, osati GUI, ndiye njira yabwino kwambiri yofotokozera zovuta zowunikira
• Kugwira ntchito ndi data kuyenera kusintha machitidwe abwino mu engineering software (Software Engineering)

• Zomangamanga za data zofunikira ziyenera kuyendetsedwa ndi gulu la ogwiritsa ntchito ngati pulogalamu yotseguka
• Osati zida za analytics zokha, komanso code idzakhala katundu wa gulu la Open Source

Zikhulupiriro zazikuluzikuluzi zatulutsa chinthu chomwe chimagwiritsidwa ntchito ndi makampani opitilira 850 masiku ano, ndipo zimapanga maziko owonjezera osangalatsa omwe adzapangidwe mtsogolo.

Kwa omwe ali ndi chidwi, pali kanema wa phunziro lotseguka lomwe ndidapereka miyezi ingapo yapitayo ngati gawo la phunziro lotseguka ku OTUS - Chida Chomanga Chida cha Amazon Redshift Storage.

Kuphatikiza pa DBT ndi Data Warehousing, monga gawo la maphunziro a Data Engineer pa nsanja ya OTUS, anzanga ndi ine timaphunzitsa makalasi pamitu ina yofunikira komanso yamakono:

• Malingaliro Omanga a Big Data Application
• Yesetsani ndi Spark ndi Spark Streaming
• Kufufuza njira ndi zida zotsatsira magwero a data
• Kupanga ziwonetsero zowunikira mu DWH
• Malingaliro a NoSQL: HBase, Cassandra, ElasticSearch
• Mfundo zowunikira ndi kuyimba 
• Ntchito Yomaliza: Kuyika maluso onse pamodzi mothandizidwa ndi upangiri

Zolemba:

1. Zolemba za DBT - Chiyambi - Zolemba zovomerezeka
2. Kodi dbt ndi chiyani kwenikweni? - Onaninso nkhani yolembedwa ndi m'modzi mwa olemba a DBT 
3. Chida Chomanga Chida cha Amazon Redshift Storage - YouTube, Kujambula kwa phunziro lotseguka la OTUS
4. Kudziwa Greenplum - Phunziro lotsatira lotseguka ndi Meyi 15, 2020
5. Maphunziro a Engineering Engineering - OTUS
6. Kupanga Mature Analytics Workflow - Kuyang'ana tsogolo la data ndi analytics
7. Yakwana nthawi yowunikira magwero otseguka - Kusintha kwa ma analytics ndi mphamvu ya Open Source
8. Kuphatikiza Kupitilira ndi Kuyesa Mwadzidzidzi Kumanga ndi dbtCloud - Mfundo zomanga CI pogwiritsa ntchito DBT
9. Kuyamba ndi maphunziro a DBT - Yesani, malangizo a pang'onopang'ono a ntchito yodziyimira pawokha
10. Jaffle shopu - Maphunziro a Github DBT - Github, khodi ya polojekiti yophunzitsa

Dziwani zambiri za maphunzirowa.

Source: www.habr.com