Sur kiuj principoj estas konstruita ideala Datuma Stokejo?
Fokuso pri komerca valoro kaj analizo sen manko de kodo de kodo. Administri DWH kiel kodbazo: versio, revizio, aŭtomatigita testado kaj CI. Modula, etendebla, malferma fonto kaj komunumo. Uzantamika dokumentaro kaj dependeca bildigo (Data Lineage).
Pli pri ĉio ĉi kaj pri la rolo de DBT en la ekosistemo Big Data & Analytics - bonvenon al kato.
Saluton al ĉiuj
Artemy Kozyr estas en kontakto. Dum pli ol 5 jaroj mi laboras kun datumstokejoj, konstruante ETL/ELT, kaj ankaŭ pri datuma analizo kaj bildigo. Mi nuntempe laboras en , mi instruas kurson ĉe OTUS , kaj hodiaŭ mi volas dividi kun vi artikolon, kiun mi verkis antaŭĝoje de la komenco nova aliĝo por la kurso.
Mallonga recenzo
La DBT-kadro temas pri la T en la ELT (Eltiraĵo - Transformo - Ŝarĝo) akronimo.
Kun la apero de tiaj produktivaj kaj skaleblaj analizaj datumbazoj kiel BigQuery, Redshift, Snowflake, ne estis senco fari transformojn ekster la Datuma Stokejo.
DBT ne elŝutas datumojn de fontoj, sed disponigas grandajn ŝancojn por labori kun datumoj kiuj jam estis ŝarĝitaj en la Stokado (en Interna aŭ Ekstera Stokado).
La ĉefa celo de DBT estas preni la kodon, kompili ĝin en SQL, ekzekuti la komandojn en la ĝusta sinsekvo en la Deponejo.
DBT-Projekta Strukturo
La projekto konsistas el dosierujoj kaj dosieroj de nur 2 tipoj:
- Modelo (.sql) - unuo de transformo esprimita per SELECT-demando
- Agorda dosiero (.yml) - parametroj, agordoj, testoj, dokumentado
Sur baza nivelo, la laboro estas strukturita jene:
- La uzanto preparas modelkodon en iu ajn oportuna IDE
- Uzante la CLI, modeloj estas lanĉitaj, DBT kompilas la modelkodon en SQL
- La kompilita SQL-kodo estas ekzekutita en la Stokado en donita sinsekvo (grafikaĵo)
Jen kiel povus aspekti kurado de la CLI:
Ĉio estas SELECT
Ĉi tio estas mortiga trajto de la kadro de Data Build Tool. Alivorte, DBT abstraktas la tutan kodon asociitan kun materiigado de viaj demandoj en la Vendejon (varioj de la ordonoj KREU, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Ajna modelo implikas verki unu SELECT-demandon, kiu difinas la rezultan datumaron.
En ĉi tiu kazo, la transformlogiko povas esti plurnivela kaj plifirmigi datumojn de pluraj aliaj modeloj. Ekzemplo de modelo, kiu konstruos mendan montrofenestron (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
Kiajn interesajn aferojn ni povas vidi ĉi tie?
Unue: Uzita CTE (Komunaj Tabelesprimoj) - por organizi kaj kompreni kodon kiu enhavas multajn transformojn kaj komercan logikon
Due: Modelkodo estas miksaĵo de SQL kaj lingvo (ŝablona lingvo).
La ekzemplo uzas buklon por por generi la kvanton por ĉiu pagmetodo specifita en la esprimo aro. La funkcio ankaŭ estas uzata ref - la kapablo referenci aliajn modelojn ene de la kodo:
- Dum kompilo ref estos konvertita al celmontrilo al tabelo aŭ vido en Stokado
- ref permesas vin konstrui modelan dependecgrafikon
Estis aldonas preskaŭ senlimajn eblecojn al DBT. La plej ofte uzataj estas:
- Se / else deklaroj - branĉo deklaroj
- Por bukloj
- Variabloj
- Makroo - kreante makroojn
Materiigo: Tabelo, Vido, Pliiga
Materiiga strategio estas aliro laŭ kiu la rezulta aro de modeldatenoj estos stokita en la Stokado.
En bazaj terminoj ĝi estas:
- Tablo - fizika tablo en la Stokado
- Vido - vido, virtuala tablo en Stokado
Ekzistas ankaŭ pli kompleksaj materiigstrategioj:
- Incremental - pliiga ŝarĝo (de grandaj faktotabeloj); novaj linioj estas aldonitaj, ŝanĝitaj linioj estas ĝisdatigitaj, forigitaj linioj estas purigitaj
- Efemera - la modelo ne konkretiĝas rekte, sed partoprenas kiel CTE en aliaj modeloj
- Ajnajn aliajn strategiojn, kiujn vi povas aldoni mem
Aldone al realigaj strategioj, ekzistas ŝancoj por optimumigo por specifaj Stokadoj, ekzemple:
- Neĝero: Transiraj tabeloj, Kunfandi konduto, Tabelgrupo, Kopiado de subvencioj, Sekuraj vidoj
- Redŝovo: Distkey, Sortkey (interplektita, kunmetita), Late Binding Views
- bigquery: Tabeldispartigo kaj grupigo, Kunfandi konduto, KMS-Ĉifrado, Etikedoj kaj Etikedoj
- Sparko: Dosierformato (parketo, csv, json, orc, delta), partition_by, clustered_by, siteloj, incremental_strategy
La sekvaj Stokado estas nuntempe subtenataj:
- PostgreSQL
- Redŝovo
- bigquery
- Neĝero
- Presto (parte)
- Fajrero (parte)
- Microsoft SQL Server (komunuma adaptilo)
Ni plibonigu nian modelon:
- Ni faru ĝian plenigon pliiga (Kreiga)
- Ni aldonu segmentajn kaj ordigajn klavojn por 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
Modela dependeca grafeo
Ĝi ankaŭ estas dependeca arbo. Ĝi ankaŭ estas konata kiel DAG (Directed Acyclic Graph).
DBT konstruas grafeon bazitan sur la agordo de ĉiuj projektmodeloj, aŭ pli ĝuste, ref() ligas ene de modeloj al aliaj modeloj. Havi grafeon permesas vin fari la jenajn aferojn:
- Kurante modelojn en la ĝusta sinsekvo
- Paraleleco de butikfasado-formado
- Kurante arbitra subgrafeo
Ekzemplo de grafika bildigo:
Ĉiu nodo de la grafeo estas modelo; la randoj de la grafeo estas precizigitaj per la esprimo ref.
Datumkvalito kaj Dokumentado
Krom formi la modelojn mem, DBT permesas testi kelkajn supozojn (asertojn) pri la rezulta datumaro, kiel ekzemple:
- Ne Nula
- Unika
- Referenca Integreco - referenca integreco (ekzemple, customer_id en la mendoj tabelo egalrilatas al id en la klientoj tabelo)
- Kongruante kun la listo de akcepteblaj valoroj
Eblas aldoni viajn proprajn testojn (propraj datumtestoj), kiel ekzemple % devio de enspezo kun indikiloj de antaŭ tago, semajno, monato. Ajna supozo formulita kiel SQL-demando povas fariĝi testo.
Tiamaniere, vi povas kapti nedeziratajn deviojn kaj erarojn en datumoj en la Magazenaj fenestroj.
Koncerne dokumentadon, DBT disponigas mekanismojn por aldono, versio kaj distribuado de metadatenoj kaj komentoj ĉe la modelo kaj eĉ atributniveloj.
Jen kiel aspektas aldoni testojn kaj dokumentaron ĉe la agorda dosiernivelo:
- 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']
Kaj jen kiel aspektas ĉi tiu dokumentaro en la generita retejo:
Makrooj kaj Moduloj
La celo de DBT estas ne tiom fariĝi aro de SQL-skriptoj, sed provizi uzantojn per potencaj kaj riĉaj rimedoj por konstrui siajn proprajn transformojn kaj distribui ĉi tiujn modulojn.
Makrooj estas aroj de konstrukcioj kaj esprimoj kiuj povas esti nomitaj kiel funkcioj ene de modeloj. Makrooj permesas reuzi SQL inter modeloj kaj projektoj laŭ la inĝenieristiko DRY (Ne Ripetu Vin).
Makroekzemplo:
{% 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 %}
Kaj ĝiaj uzoj:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT venas kun pakaĵmanaĝero kiu permesas al uzantoj publikigi kaj reuzi individuajn modulojn kaj makroojn.
Ĉi tio signifas povi ŝargi kaj uzi bibliotekojn kiel:
- : laborante kun Dato/Tempo, Anstataŭaj Ŝlosiloj, Skemaj testoj, Pivot/Unpivot kaj aliaj
- Pretaj montrofenestroŝablonoj por servoj kiel ekzemple и
- Bibliotekoj por specifaj Datenbutikoj, ekz.
- — Modulo por registri DBT-operacion
Kompleta listo de pakaĵoj troveblas ĉe .
Eĉ pli da funkcioj
Ĉi tie mi priskribos kelkajn aliajn interesajn funkciojn kaj efektivigojn, kiujn la teamo kaj mi uzas por konstrui Datuman Stokejon. .
Apartigo de rultempaj medioj DEV - TEST - PROD
Eĉ ene de la sama DWH-areto (ene de malsamaj kabaloj). Ekzemple, uzante la sekvan esprimon:
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 -%}
)
Ĉi tiu kodo laŭvorte diras: por medioj dev, testo, ci prenu datumojn nur dum la lastaj 3 tagoj kaj ne pli. Tio estas, kurado en ĉi tiuj medioj estos multe pli rapida kaj postulos malpli da rimedoj. Kiam kuras sur medio prod la filtrila kondiĉo estos ignorita.
Materiigo kun alterna kolumna kodigo
Redshift estas kolona DBMS, kiu ebligas al vi specifi datumajn kunpremajn algoritmojn por ĉiu individua kolumno. Elekti optimumajn algoritmojn povas redukti diskospacon je 20-50%.
Makro ekzekutos la komandon ANALYZE COMPRESSION, kreos novan tabelon kun la rekomenditaj kolumnaj kodaj algoritmoj, specifitaj segmentaj ŝlosiloj (dist_key) kaj ordigaj klavoj (sort_key), transdonos la datumojn al ĝi kaj, se necese, forigos la malnovan kopion.
Makrosignaturo:
{{ 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) }}
La registra modelo funkcias
Vi povas ligi hokojn al ĉiu ekzekuto de la modelo, kiu estos efektivigita antaŭ lanĉo aŭ tuj post la kreado de la modelo finiĝos:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
La registra modulo permesos al vi registri ĉiujn necesajn metadatumojn en aparta tabelo, kiu poste povas esti uzata por revizii kaj analizi botelojn.
Jen kiel aspektas la panelo surbaze de registradaj datumoj en Looker:
Aŭtomatigo de Stokado-Prizorgado
Se vi uzas iujn etendaĵojn de la funkcieco de la uzita Deponejo, kiel UDF (Uzant-Difinitaj Funkcioj), tiam versio de ĉi tiuj funkcioj, alirkontrolo kaj aŭtomatigita eligo de novaj eldonoj estas tre oportune fari en DBT.
Ni uzas UDF en Python por kalkuli haŝojn, retpoŝtajn domajnojn kaj malkodigon de bitmasko.
Ekzemplo de makroo kiu kreas UDF en iu ajn ekzekutmedio (dev, testo, 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 %}
Ĉe Wheely ni uzas Amazon Redshift, kiu baziĝas sur PostgreSQL. Por Redshift, gravas regule kolekti statistikojn pri tabeloj kaj liberigi diskspacon - la ordonojn ANALYZE kaj VACUUM, respektive.
Por fari tion, la komandoj de la makroo redshift_maintenance estas ekzekutitaj ĉiunokte:
{% 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 Nubo
Eblas uzi DBT kiel servon (Administrita Servo). Inkluditaj:
- Retejo IDE por evoluigado de projektoj kaj modeloj
- Laboragordo kaj programado
- Simpla kaj oportuna aliro al protokoloj
- Retejo kun dokumentado de via projekto
- Konektante CI (Kontinua Integriĝo)
konkludo
Prepari kaj konsumi DWH fariĝas tiel agrabla kaj utila kiel trinki smoothie. DBT konsistas el Jinja, uzantaj etendaĵoj (moduloj), kompililo, ekzekutisto kaj pakaĵmanaĝero. Kunigante ĉi tiujn elementojn, vi ricevas kompletan labormedion por via Datuma Stokejo. Apenaŭ ekzistas pli bona maniero administri transformon ene de DWH hodiaŭ.
La kredoj sekvitaj de la programistoj de DBT estas formulitaj jene:
- Kodo, ne GUI, estas la plej bona abstraktado por esprimi kompleksan analizan logikon
- Labori kun datumoj devus adapti plej bonajn praktikojn en softvara inĝenierado (Programaro-Inĝenierado)
- Kritika dateninfrastrukturo devus esti kontrolita de la uzantkomunumo kiel malfermfonta programaro
- Ne nur analizaj iloj, sed ankaŭ kodo fariĝos ĉiam pli la posedaĵo de la Malfermfonta komunumo
Ĉi tiuj kernaj kredoj generis produkton, kiu estas uzata de pli ol 850 kompanioj hodiaŭ, kaj ili formas la bazon de multaj ekscitaj etendaĵoj, kiuj estos kreitaj en la estonteco.
Por tiuj, kiuj interesiĝas, estas video de malfermita leciono, kiun mi donis antaŭ kelkaj monatoj kadre de malfermita leciono ĉe OTUS - .
Krom DBT kaj Data Warehousing, kadre de la Data Engineer-kurso sur la platformo OTUS, miaj kolegoj kaj mi instruas klasojn pri kelkaj aliaj gravaj kaj modernaj temoj:
- Arkitekturaj Konceptoj por Grandaj Datumaj Aplikoj
- Praktiku kun Spark kaj Spark Streaming
- Esplorado de metodoj kaj iloj por ŝargi datumfontojn
- Konstruante analizajn montrofenestrojn en DWH
- NoSQL-konceptoj: HBase, Cassandra, ElasticSearch
- Principoj de monitorado kaj instrumentado
- Fina Projekto: kunmeti ĉiujn kapablojn sub mentora subteno
Referencoj
- — Oficiala dokumentaro
- — Revizia artikolo de unu el la aŭtoroj de DBT
- — Jutubo, Registrado de malferma leciono de OTUS
- — La sekva malferma leciono estas la 15-a de majo 2020
- —OTUS
- — Rigardo al la estonteco de datumoj kaj analizoj
- — La evoluo de analitiko kaj la influo de Malferma Fonto
- — Principoj de konstruado de CI uzante DBT
- — Praktiko, paŝo post paŝo instrukcioj por sendependa laboro
- — Github, kodo pri eduka projekto
fonto: www.habr.com