Kokiais principais kuriama ideali duomenų saugykla?
Sutelkite dėmesį į verslo vertę ir analizę, jei nėra pagrindinio kodo. DWH kaip kodų bazės valdymas: versijų kūrimas, peržiūra, automatinis testavimas ir CI. Modulinė, išplečiama, atvirojo kodo ir bendruomenė. Patogi vartotojui dokumentacija ir priklausomybės vizualizacija (Data Lineage).
Daugiau apie visa tai ir apie DBT vaidmenį didelių duomenų ir analizės ekosistemoje – sveiki atvykę į katę.
Sveiki visi
Artemy Kozyras susisiekia. Daugiau nei 5 metus dirbu su duomenų saugyklomis, kuriu ETL/ELT, taip pat su duomenų analitika ir vizualizacija. Šiuo metu dirbu , dėstau OTUS kursuose , o šiandien noriu su jumis pasidalinti straipsniu, kurį parašiau laukdamas pradžios nauja registracija į kursus.
Apžvalga
DBT sistema yra susijusi su T akronimu ELT (Extract – Transform – Load).
Atsiradus tokioms produktyvioms ir keičiamo dydžio analitinėms duomenų bazėms kaip „BigQuery“, „Redshift“, „Snowflake“, nebuvo prasmės atlikti transformacijų už duomenų saugyklos ribų.
DBT neatsisiunčia duomenų iš šaltinių, tačiau suteikia puikias galimybes dirbti su duomenimis, kurie jau buvo įkelti į saugyklą (vidinėje arba išorinėje saugykloje).
Pagrindinis DBT tikslas yra paimti kodą, sukompiliuoti jį į SQL, vykdyti komandas reikiama seka saugykloje.
DBT projekto struktūra
Projektą sudaro tik 2 tipų katalogai ir failai:
- Modelis (.sql) – transformacijos vienetas, išreiškiamas SELECT užklausa
- Konfigūracijos failas (.yml) – parametrai, nustatymai, testai, dokumentacija
Pradiniame lygmenyje darbas suskirstytas taip:
- Vartotojas paruošia modelio kodą bet kurioje patogioje IDE
- Naudojant CLI, modeliai paleidžiami, DBT sukompiliuoja modelio kodą į SQL
- Sukompiliuotas SQL kodas yra vykdomas saugykloje tam tikra seka (grafe)
Štai kaip gali atrodyti paleidimas iš CLI:
Viskas yra SELECT
Tai žudikiška duomenų kūrimo įrankio sistemos funkcija. Kitaip tariant, DBT abstrahuoja visą kodą, susijusį su jūsų užklausų realizavimu parduotuvėje (komandų CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ... variantai).
Bet kuriame modelyje reikia parašyti vieną SELECT užklausą, kuri apibrėžia gautą duomenų rinkinį.
Šiuo atveju transformacijos logika gali būti kelių lygių ir konsoliduoti kelių kitų modelių duomenis. Modelio, kuriuo bus sukurta užsakymų vitrina (f_orders), pavyzdys:
{% 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
Ką čia įdomaus galime pamatyti?
Pirma: Naudojamas CTE (bendrosios lentelės išraiškos) – norint organizuoti ir suprasti kodą, kuriame yra daug transformacijų ir verslo logikos
Antra: modelio kodas yra SQL ir kalbos mišinys (šablonų kalba).
Pavyzdyje naudojama kilpa forumas sugeneruoti sumą kiekvienam mokėjimo būdui, nurodytam išraiškoje nustatyti. Funkcija taip pat naudojama Nuoroda — galimybė nurodyti kitus modelius kode:
- Kompiliacijos metu Nuoroda bus konvertuojamas į tikslinę rodyklę į lentelę arba rodinį saugykloje
- Nuoroda leidžia sukurti modelio priklausomybės grafiką
būtent prideda beveik neribotas DBT galimybes. Dažniausiai naudojami šie:
- If / else teiginiai – šakiniai teiginiai
- Kilpoms
- Kintamieji
- Makrokomandos – makrokomandų kūrimas
Materializavimas: lentelė, vaizdas, prieaugis
Materializacijos strategija yra metodas, pagal kurį gautas modelio duomenų rinkinys bus saugomas saugykloje.
Iš esmės tai yra:
- Lentelė – fizinė lentelė saugykloje
- Rodinys – rodinys, virtuali lentelė saugykloje
Taip pat yra sudėtingesnių materializacijos strategijų:
- Inkrementinis – prieauginis įkėlimas (didelių faktų lentelių); pridedamos naujos eilutės, pakeistos eilutės atnaujinamos, ištrintos eilutės išvalomos
- Efemeriškas – modelis nematerializuojasi tiesiogiai, o dalyvauja kaip CTE kituose modeliuose
- Visos kitos strategijos, kurias galite pridėti patys
Be materializavimo strategijų, yra galimybių optimizuoti konkrečias saugyklas, pavyzdžiui:
- Snieguolė: pereinamosios lentelės, sujungimo elgsena, lentelių grupavimas, dotacijų kopijavimas, saugūs rodiniai
- Redshift: Distkey, Sortkey (įterptas, sudėtinis), vėlyvojo įrišimo rodiniai
- „BigQuery“: lentelių skaidymas ir grupavimas, sujungimo elgsena, KMS šifravimas, etiketės ir žymos
- Sukelti: failo formatas (parketas, csv, json, orc, delta), partition_by, clustered_by, segmentai, prieaugio_strategija
Šiuo metu palaikomos šios saugyklos:
- postgres
- Redshift
- „BigQuery“
- Snieguolė
- Presto (iš dalies)
- Kibirkštis (iš dalies)
- „Microsoft SQL Server“ (bendruomenės adapteris)
Patobulinkime savo modelį:
- Padarykime, kad jo užpildymas būtų laipsniškas (Incremental)
- Pridėkime „Redshift“ segmentavimo ir rūšiavimo klavišus
-- Конфигурация модели:
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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
Modelio priklausomybės grafikas
Tai taip pat priklausomybės medis. Jis taip pat žinomas kaip DAG (Directed Acyclic Graph).
DBT sukuria grafiką, pagrįstą visų projekto modelių konfigūracija arba, tiksliau, nuorodomis () modeliuose su kitais modeliais. Turėdami grafiką, galite atlikti šiuos veiksmus:
- Modeliai veikia teisinga seka
- Parduotuvės formavimo lygiagretinimas
- Savavališko pografo vykdymas
Grafiko vizualizacijos pavyzdys:
Kiekvienas grafo mazgas yra modelis, grafiko kraštai nurodomi išraiška ref.
Duomenų kokybė ir dokumentacija
Be pačių modelių generavimo, DBT leidžia išbandyti daugybę prielaidų apie gautą duomenų rinkinį, pavyzdžiui:
- Ne Null
- Unikalus
- Nuorodos vientisumas – nuorodos vientisumas (pavyzdžiui, kliento_id užsakymų lentelėje atitinka id klientų lentelėje)
- Atitinka priimtinų verčių sąrašą
Galima pridėti savo testus (custom data tests), tokius kaip, pavyzdžiui, pajamų nuokrypis % su rodikliais prieš dieną, savaitę, mėnesį. Bet kuri prielaida, suformuluota kaip SQL užklausa, gali tapti testu.
Tokiu būdu galite užfiksuoti nepageidaujamus duomenų nukrypimus ir klaidas Sandėlio languose.
Kalbant apie dokumentaciją, DBT pateikia metaduomenų ir komentarų pridėjimo, versijų kūrimo ir platinimo modelių ir net atributų lygiais mechanizmus.
Štai kaip atrodo testų ir dokumentų pridėjimas konfigūracijos failo lygiu:
- 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']
Štai kaip ši dokumentacija atrodo sugeneruotoje svetainėje:
Makrokomandos ir moduliai
DBT tikslas yra ne tiek tapti SQL scenarijų rinkiniu, bet suteikti vartotojams galingas ir daug funkcijų turinčias priemones, skirtas kurti savo transformacijas ir platinti šiuos modulius.
Makrokomandos yra konstrukcijų ir išraiškų rinkiniai, kuriuos galima vadinti funkcijomis modeliuose. Makrokomandos leidžia pakartotinai naudoti SQL tarp modelių ir projektų pagal DRY (Don't Repeat Yourself) inžinerijos principą.
Makrokomandos pavyzdys:
{% 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 %}
Ir jo naudojimas:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT yra su paketų tvarkykle, kuri leidžia vartotojams skelbti ir pakartotinai naudoti atskirus modulius ir makrokomandas.
Tai reiškia, kad turite galimybę įkelti ir naudoti tokias bibliotekas kaip:
- : darbas su data/laiku, surogatiniais raktais, schemų testais, susukimu/atsukimu ir kt.
- Paruošti vitrinų šablonai tokioms paslaugoms kaip и
- Konkrečių duomenų saugyklų bibliotekos, pvz.
- — DBT veikimo registravimo modulis
Visą paketų sąrašą galite rasti adresu .
Dar daugiau funkcijų
Čia aprašysiu keletą kitų įdomių funkcijų ir diegimų, kuriuos mes ir komanda naudojame kurdami duomenų saugyklą .
Vykdymo laiko aplinkų atskyrimas DEV - TEST - PROD
Net tame pačiame DWH klasteryje (skirtingose schemose). Pavyzdžiui, naudojant šią išraišką:
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 -%}
)
Šis kodas pažodžiui sako: aplinkai dev, testas, ci imti duomenis tik paskutines 3 dienas ir ne daugiau. Tai reiškia, kad darbas šiose aplinkose bus daug greitesnis ir pareikalaus mažiau išteklių. Kai važiuojama aplinka prod filtro būsena bus ignoruojama.
Materializavimas su alternatyviu stulpelių kodavimu
Redshift yra stulpelinė DBVS, leidžianti nustatyti kiekvieno atskiro stulpelio duomenų glaudinimo algoritmus. Pasirinkus optimalius algoritmus, vietos diske galima sumažinti 20-50%.
Makro įvykdys komandą ANALIZUOTI KOMPRESIJA, sukurs naują lentelę su rekomenduojamais stulpelių kodavimo algoritmais, nurodytais segmentavimo raktais (dist_key) ir rūšiavimo raktais (rūšiavimo raktas), perkels į ją duomenis ir, jei reikia, ištrins seną kopiją.
Makro parašas:
{{ 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) }}
Paleidžiamas registravimo modelis
Prie kiekvieno modelio vykdymo galite pritvirtinti kabliukus, kurie bus vykdomi prieš paleidimą arba iš karto po modelio kūrimo:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Registravimo modulis leis įrašyti visus reikalingus metaduomenis į atskirą lentelę, kurią vėliau galėsite naudoti kliūtims audituoti ir analizuoti.
Taip atrodo prietaisų skydelis, pagrįstas „Looker“ registravimo duomenimis:
Sandėliavimo priežiūros automatizavimas
Jei naudojate kai kuriuos naudojamos saugyklos funkcionalumo plėtinius, pvz., UDF (User Defined Functions), šių funkcijų versijų kūrimas, prieigos kontrolė ir automatinis naujų leidimų diegimas yra labai patogus DBT.
„Python“ programoje naudojame UDF maišai, el. pašto domenams ir bitų kaukių dekodavimui apskaičiuoti.
Makrokomandos, kuri sukuria UDF bet kurioje vykdymo aplinkoje (dev, test, prod), pavyzdys:
{% 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 %}
„Wheely“ naudojame „Amazon Redshift“, kuris yra pagrįstas „PostgreSQL“. Naudojant „Redshift“, svarbu reguliariai rinkti statistiką lentelėse ir atlaisvinti vietos diske – atitinkamai komandos ANALIZĖS ir VACUUM.
Norėdami tai padaryti, komandos iš redshift_maintenance makrokomandos vykdomos kiekvieną naktį:
{% 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 debesis
Galima naudoti DBT kaip paslaugą (valdoma paslauga). Įskaitant:
- Web IDE projektams ir modeliams kurti
- Darbo konfigūravimas ir planavimas
- Paprasta ir patogi prieiga prie žurnalų
- Svetainė su jūsų projekto dokumentacija
- CI prijungimas (nuolatinis integravimas)
išvada
Ruošti ir vartoti DWH tampa taip pat malonu ir naudinga, kaip gerti kokteilius. DBT sudaro Jinja, vartotojo plėtiniai (moduliai), kompiliatorius, vykdytojas ir paketų tvarkyklė. Sujungę šiuos elementus kartu gausite visą savo duomenų saugyklos darbo aplinką. Vargu ar šiandien yra geresnis būdas valdyti DWH transformaciją.
Įsitikinimai, kuriais vadovaujasi DBT kūrėjai, suformuluoti taip:
- Kodas, o ne GUI, yra geriausia abstrakcija sudėtingai analitinei logikai išreikšti
- Darbas su duomenimis turėtų pritaikyti geriausią programinės įrangos inžinerijos praktiką (programinės įrangos inžinerija)
- Kritinių duomenų infrastruktūrą turėtų valdyti vartotojų bendruomenė kaip atvirojo kodo programinę įrangą
- Ne tik analizės įrankiai, bet ir kodas vis dažniau taps atvirojo kodo bendruomenės nuosavybe
Šie pagrindiniai įsitikinimai sukūrė produktą, kurį šiandien naudoja daugiau nei 850 įmonių, ir jie sudaro daugelio įdomių plėtinių, kurie bus sukurti ateityje, pagrindą.
Tiems, kurie domisi, yra vaizdo įrašas apie atvirą pamoką, kurią vedžiau prieš kelis mėnesius kaip atviros pamokos dalį OTUS - .
Be DBT ir duomenų saugyklos, kaip duomenų inžinieriaus kurso OTUS platformoje dalį, mano kolegos ir aš vedame pamokas daugeliu kitų aktualių ir šiuolaikiškų temų:
- Didžiųjų duomenų taikomųjų programų architektūrinės koncepcijos
- Treniruokitės naudodami „Spark“ ir „Spark Streaming“.
- Duomenų šaltinių įkėlimo metodų ir įrankių tyrinėjimas
- Analitinių vitrinų kūrimas DWH
- NoSQL sąvokos: HBase, Cassandra, ElasticSearch
- Stebėjimo ir orkestravimo principai
- Galutinis projektas: visų įgūdžių sujungimas pagal mentorystės pagalbą
Nuorodos:
- — Oficialūs dokumentai
- — Vieno iš DBT autorių apžvalginis straipsnis
- — „YouTube“, atviros OTUS pamokos įrašymas
- — Kita atvira pamoka 15 m. gegužės 2020 d
- – OTUS
- — Žvilgsnis į duomenų ir analizės ateitį
- — Analitikos raida ir atvirojo kodo įtaka
- — KI kūrimo naudojant DBT principai
- — Praktika, Žingsnis po žingsnio savarankiško darbo instrukcijos
- — Github, edukacinio projekto kodas
Šaltinis: www.habr.com