Milyen elvek alapján épül fel egy ideális adattárház?
Összpontosítson az üzleti értékre és az elemzésekre az alapkód hiányában. A DWH kezelése kódbázisként: verziókészítés, áttekintés, automatizált tesztelés és CI. Moduláris, bővíthető, nyílt forráskódú és közösségi. Felhasználóbarát dokumentáció és függőségi megjelenítés (Data Lineage).
További információ minderről és a DBT szerepéről a Big Data & Analytics ökoszisztémában – üdvözöljük a cat.
Üdvözlet mindenkinek
Artemy Kozyr kapcsolatban áll. Több mint 5 éve foglalkozom adattárházakkal, ETL/ELT építéssel, valamint adatelemzéssel és vizualizációval. Jelenleg ben dolgozom , az OTUS-ban tanítok tanfolyamon , és ma egy cikket szeretnék megosztani veletek, amit a kezdésre várva írtam új jelentkezés a tanfolyamra.
Rövid áttekintés
A DBT keretrendszer az ELT (Extract – Transform – Load) betűszóban szereplő T-ről szól.
Az olyan hatékony és méretezhető analitikai adatbázisok megjelenésével, mint a BigQuery, Redshift, Snowflake, nem volt értelme az adattárházon kívüli átalakításokat végrehajtani.
A DBT nem tölt le adatokat a forrásokból, de nagyszerű lehetőségeket biztosít a Tárolóba (belső vagy külső tárolóban) már betöltött adatokkal való munkavégzésre.
A DBT fő célja a kód átvétele, SQL-be való fordítása, a parancsok megfelelő sorrendben történő végrehajtása a Repositoryban.
A DBT projekt felépítése
A projekt mindössze 2 típusú könyvtárat és fájlt tartalmaz:
- Modell (.sql) - a SELECT lekérdezéssel kifejezett átalakítási egység
- Konfigurációs fájl (.yml) - paraméterek, beállítások, tesztek, dokumentáció
Alapszinten a munka a következőképpen épül fel:
- A felhasználó bármilyen kényelmes IDE-ben elkészíti a modellkódot
- A CLI segítségével modellek indulnak el, a DBT a modellkódot SQL-be fordítja
- A lefordított SQL kód egy adott sorrendben (grafikonon) fut le a tárolóban.
Így nézhet ki a CLI-ből való futás:
Minden SELECT
Ez a Data Build Tool keretrendszer gyilkos funkciója. Más szóval, a DBT kivonatolja az összes kódot, amely a lekérdezéseknek a Store-ba való megvalósításához kapcsolódik (a CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ... parancsok változatai).
Bármely modell magában foglal egy SELECT lekérdezést, amely meghatározza az eredményül kapott adatkészletet.
Ebben az esetben az átalakítási logika lehet többszintű, és több más modell adatait is konszolidálja. Példa egy modellre, amely egy rendelési bemutatót (f_orders) készít:
{% 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
Milyen érdekességeket láthatunk itt?
Először is: CTE-t (Common Table Expressions) használtunk – sok átalakítást és üzleti logikát tartalmazó kód rendszerezésére és megértésére.
Másodszor: A modellkód SQL és nyelv keveréke (sablonnyelv).
A példa hurkot használ mert hogy előállítsa az összeget a kifejezésben megadott egyes fizetési módokhoz készlet. A funkciót is használják ref — más modellekre való hivatkozás képessége a kódon belül:
- Összeállítás közben ref célmutatóvá lesz konvertálva egy táblázatra vagy nézetre a Tárolásban
- ref lehetővé teszi egy modellfüggőségi gráf felépítését
Pontosan szinte korlátlan lehetőségeket ad a DBT-hez. A leggyakrabban használtak a következők:
- If / else utasítások - elágazási utasítások
- A hurkok - ciklusok
- Változók
- Makró – makrók létrehozása
Materializáció: Táblázat, Nézet, Növekményes
A materializációs stratégia egy olyan megközelítés, amely szerint az eredményül kapott modelladatok halmaza a Tárhelyen kerül tárolásra.
Alapvetően ez:
- Táblázat – fizikai tábla a Tárhelyben
- Nézet – nézet, virtuális asztal a Tárhelyben
Vannak bonyolultabb megvalósítási stratégiák is:
- Növekményes - növekményes betöltés (nagy ténytáblák); új sorok kerülnek hozzáadásra, a megváltozott sorok frissítésre kerülnek, a törölt sorok törlődnek
- Efemerális – a modell nem közvetlenül valósul meg, hanem CTE-ként vesz részt más modellekben
- Bármilyen más stratégia, amelyet saját maga is hozzáadhat
A megvalósítási stratégiákon túlmenően az egyes tárolók optimalizálására is van lehetőség, például:
- Hópehely: Átmeneti táblák, Egyesítési viselkedés, Táblafürtözés, Engedélyek másolása, Biztonságos nézetek
- vöröseltolódás: Distkey, Sortkey (interleaved, összetett), Késői kötési nézetek
- BigQuery: Táblaparticionálás és klaszterezés, Egyesítési viselkedés, KMS-titkosítás, Címkék és címkék
- Szikra: Fájlformátum (parkett, csv, json, orc, delta), partition_by, clustered_by, gyűjtők, inkrementális_stratégia
Jelenleg a következő tárolók támogatottak:
- postgres
- vöröseltolódás
- BigQuery
- Hópehely
- Presto (részben)
- Szikra (részben)
- Microsoft SQL Server (közösségi adapter)
Javítsuk a modellünket:
- Tegyük növekményessé a kitöltését (inkrementális)
- Adjunk hozzá szegmentációs és rendezési kulcsokat a Redshifthez
-- Конфигурация модели:
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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
Modellfüggőségi grafikon
Ez is egy függőségi fa. DAG (Directed Acyclic Graph) néven is ismert.
A DBT az összes projektmodell konfigurációja alapján grafikont készít, vagy inkább ref() hivatkozásokat a modelleken belül más modellekre. A grafikon birtokában a következőket teheti:
- Modellek futtatása a megfelelő sorrendben
- A kirakatképzés párhuzamosítása
- Tetszőleges részgráf futtatása
Példa a grafikon megjelenítésére:
A gráf minden csomópontja egy modell, a gráf éleit a ref kifejezés határozza meg.
Adatminőség és dokumentáció
A modellek generálása mellett a DBT lehetővé teszi számos feltételezés tesztelését az eredményül kapott adatkészlettel kapcsolatban, például:
- Nem nulla
- Egyedülálló
- Referencia integritás - hivatkozási integritás (például a rendelési táblában a customer_id megegyezik a vevőtáblában lévő azonosítóval)
- Az elfogadható értékek listájának megfeleltetése
Lehetőség van saját tesztek (egyedi adattesztek) hozzáadására, mint például a bevétel százalékos eltérése egy nappal, egy héttel, egy hónappal ezelőtti mutatókkal. Bármely SQL-lekérdezésként megfogalmazott feltevés tesztté válhat.
Ily módon elkaphatja a nem kívánt eltéréseket és hibákat az adatokban a Raktár ablakokban.
Ami a dokumentációt illeti, a DBT mechanizmusokat biztosít a metaadatok és megjegyzések hozzáadására, verziószámítására és elosztására a modell, sőt az attribútumok szintjén is.
Így néz ki a tesztek és a dokumentáció hozzáadása a konfigurációs fájl szintjén:
- 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']
És így néz ki ez a dokumentáció a létrehozott weboldalon:
Makrók és modulok
A DBT célja nem annyira az, hogy SQL-szkriptek készletévé váljon, hanem az, hogy hatékony és funkciókban gazdag eszközt biztosítson a felhasználóknak saját átalakításaik elkészítéséhez és ezen modulok terjesztéséhez.
A makrók konstrukciók és kifejezések halmazai, amelyek függvényként hívhatók meg a modelleken belül. A makrók lehetővé teszik az SQL újrafelhasználását a modellek és projektek között a DRY (Don't Repeat Yourself) mérnöki elvnek megfelelően.
Makró példa:
{% 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 %}
És a használata:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
A DBT csomagkezelővel érkezik, amely lehetővé teszi a felhasználók számára az egyes modulok és makrók közzétételét és újrafelhasználását.
Ez azt jelenti, hogy képes betölteni és használni a könyvtárakat, például:
- : Dátum/Idő, Helyettesítő kulcsok, Sématesztek, Pivot/Unpivot és egyebek használata
- Kész bemutatósablonok olyan szolgáltatásokhoz, mint pl и
- Adott adattárak könyvtárai, pl.
- — Modul a DBT működés naplózására
A csomagok teljes listája a címen található .
Még több funkció
Itt leírok néhány további érdekes funkciót és megvalósítást, amelyekben a csapat és én egy Data Warehouse-t építünk .
Futókörnyezetek szétválasztása DEV - TEST - PROD
Még ugyanazon a DWH klaszteren belül is (különböző sémákon belül). Például a következő kifejezés használatával:
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 -%}
)
Ez a kód szó szerint azt mondja: környezetekhez dev, teszt, ci csak az utolsó 3 nap adatait vegye fel, és ne többet. Vagyis ezekben a környezetekben a futás sokkal gyorsabb és kevesebb erőforrást igényel. Ha környezeten fut döf a szűrő állapotát figyelmen kívül hagyja.
Materializáció alternatív oszlopkódolással
A Redshift egy oszlopos DBMS, amely lehetővé teszi adattömörítési algoritmusok beállítását minden egyes oszlophoz. Az optimális algoritmusok kiválasztásával 20-50%-kal csökkenthető a lemezterület.
Makró végrehajtja az ANALYZE COMPRESSION parancsot, létrehoz egy új táblát az ajánlott oszlopkódolási algoritmusokkal, a megadott szegmentációs kulcsokkal (dist_key) és rendezési kulcsokkal (sort_key), átviszi rá az adatokat, és szükség esetén törli a régi másolatot.
Makró aláírás:
{{ 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) }}
A naplózási modell fut
A modell minden egyes végrehajtásához horgokat rögzíthet, amelyek végrehajtásra kerülnek az indítás előtt vagy közvetlenül a modell létrehozása után:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
A naplózó modul lehetővé teszi az összes szükséges metaadat rögzítését egy külön táblázatba, amelyet később a szűk keresztmetszetek auditálására és elemzésére használhatunk.
Így néz ki az irányítópult a Looker naplózási adatai alapján:
A tárolási karbantartás automatizálása
Ha a használt adattár funkcióinak néhány bővítményét használja, például UDF-et (User Defined Functions), akkor ezeknek a funkcióknak a verziószámítása, a hozzáférés-vezérlés és az új kiadások automatikus bevezetése nagyon kényelmes a DBT-ben.
A Pythonban az UDF-et használjuk a hash-ek, az e-mail-tartományok és a bitmaszk-dekódolás kiszámításához.
Példa egy makróra, amely UDF-et hoz létre bármilyen végrehajtási környezetben (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 %}
A Wheelynél az Amazon Redshiftet használjuk, amely PostgreSQL-en alapul. A Redshift esetében fontos, hogy rendszeresen gyűjtsön statisztikákat a táblákról, és szabadítson fel lemezterületet - az ANALYZE és a VACUUM parancsokat.
Ehhez a redshift_maintenance makró parancsai minden este végrehajtásra kerülnek:
{% 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 felhő
Lehetőség van a DBT szolgáltatásként való használatára (Managed Service). Beleértve:
- Webes IDE projektek és modellek fejlesztéséhez
- Munka konfigurálása és ütemezése
- Egyszerű és kényelmes hozzáférés a naplókhoz
- Weboldal a projekt dokumentációjával
- CI csatlakoztatása (folyamatos integráció)
Következtetés
A DWH elkészítése és fogyasztása ugyanolyan élvezetessé és hasznossá válik, mint egy turmix fogyasztása. A DBT Jindzsából, felhasználói kiterjesztésekből (modulokból), fordítóból, végrehajtóból és csomagkezelőből áll. Ezen elemek összeállításával teljes munkakörnyezetet kap az adattárháza számára. Aligha van ma jobb módszer a DWH-n belüli átalakulások kezelésére.
A DBT fejlesztői által követett hiedelmek a következőképpen fogalmazódnak meg:
- A kód, nem pedig a grafikus felhasználói felület, a legjobb absztrakció az összetett analitikai logika kifejezésére
- Az adatokkal való munka során hozzá kell igazítani a szoftverfejlesztés legjobb gyakorlatait (Szoftverfejlesztés)
- A kritikus adatinfrastruktúrát a felhasználói közösségnek nyílt forráskódú szoftverként kell felügyelnie
- Nemcsak az elemző eszközök, hanem a kód is egyre inkább a nyílt forráskódú közösség tulajdonába kerül
Ezek az alapvető hiedelmek olyan terméket szültek, amelyet ma több mint 850 vállalat használ, és ezek képezik az alapját számos izgalmas bővítménynek, amelyek a jövőben készülnek.
Az érdeklődők számára van egy videó egy nyílt leckéről, amelyet néhány hónappal ezelőtt tartottam az OTUS nyílt órájának részeként - .
A DBT és az adatraktározás mellett az OTUS platform Data Engineer tanfolyamának részeként kollégáimmal számos más releváns és modern témában tartunk órákat:
- Építészeti koncepciók Big Data alkalmazásokhoz
- Gyakoroljon a Spark és a Spark Streaming segítségével
- Adatforrások betöltésére szolgáló módszerek és eszközök feltárása
- Analitikai kirakat építése a DWH-ban
- NoSQL-fogalmak: HBase, Cassandra, ElasticSearch
- A monitorozás és hangszerelés elvei
- Végső projekt: az összes készség összeállítása mentori támogatás alatt
referenciák:
- — Hivatalos dokumentáció
- — A DBT egyik szerzőjének áttekintő cikke
- — YouTube, OTUS nyílt óra felvétele
- — A következő nyílt óra 15. május 2020-én lesz
- -OTUS
- – Betekintés az adatok és az elemzés jövőjébe
- — Az analitika fejlődése és a nyílt forráskód hatása
- — A CI DBT használatával történő felépítésének elvei
- — Gyakorlat, Lépésről-lépésre szóló utasítások az önálló munkához
- — Github, oktatási projekt kódja
Forrás: will.com