Op welke principes is een ideaal Data Warehouse gebouwd?
Focus op bedrijfswaarde en analyses als er geen boilerplate-code is. Beheer van DWH als codebase: versiebeheer, beoordeling, geautomatiseerd testen en CI. Modulair, uitbreidbaar, open source en community. Gebruiksvriendelijke documentatie en visualisatie van afhankelijkheid (Data Lineage).
Meer over dit alles en over de rol van DBT in het Big Data & Analytics-ecosysteem - welkom bij cat.
hallo iedereen
Artemy Kozyr neemt contact op. Al meer dan 5 jaar werk ik met datawarehouses, het bouwen van ETL/ELT, maar ook met data-analyse en visualisatie. Ik ben momenteel werkzaam in , Ik geef les bij OTUS op een cursus , en vandaag wil ik een artikel met jullie delen dat ik schreef in afwachting van de start nieuwe inschrijving voor de cursus.
Synopsis
Het DBT-framework draait helemaal om de T in het acroniem ELT (Extract - Transform - Load).
Met de komst van productieve en schaalbare analytische databases als BigQuery, Redshift en Snowflake had het geen zin om transformaties buiten het Data Warehouse door te voeren.
DBT downloadt geen gegevens uit bronnen, maar biedt geweldige mogelijkheden voor het werken met gegevens die al in de opslag zijn geladen (in interne of externe opslag).
Het belangrijkste doel van DBT is om de code te nemen, deze in SQL te compileren en de opdrachten in de juiste volgorde in de Repository uit te voeren.
DBT-projectstructuur
Het project bestaat uit mappen en bestanden van slechts 2 typen:
- Model (.sql) - een transformatie-eenheid uitgedrukt door een SELECT-query
- Configuratiebestand (.yml) - parameters, instellingen, tests, documentatie
Op basisniveau is het werk als volgt opgebouwd:
- De gebruiker bereidt modelcode voor in elke handige IDE
- Met behulp van de CLI worden modellen gelanceerd, DBT compileert de modelcode in SQL
- De gecompileerde SQL-code wordt in een bepaalde volgorde (grafiek) in de opslag uitgevoerd
Zo zou het uitvoeren vanaf de CLI eruit kunnen zien:
Alles is SELECTEER
Dit is een geweldige functie van het Data Build Tool-framework. Met andere woorden, DBT abstraheert alle code die is gekoppeld aan het materialiseren van uw vragen in de Store (variaties van de opdrachten CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Elk model omvat het schrijven van één SELECT-query die de resulterende dataset definieert.
In dit geval kan de transformatielogica uit meerdere niveaus bestaan en gegevens uit verschillende andere modellen consolideren. Een voorbeeld van een model dat een ordershowcase bouwt (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
Welke interessante dingen kunnen we hier zien?
Ten eerste: CTE (Common Table Expressions) gebruikt - om code te organiseren en te begrijpen die veel transformaties en bedrijfslogica bevat
Ten tweede: Modelcode is een combinatie van SQL en taal (sjabloontaal).
In het voorbeeld wordt een lus gebruikt For om het bedrag te genereren voor elke betaalmethode die in de expressie is opgegeven reeks. De functie wordt ook gebruikt ref — de mogelijkheid om binnen de code naar andere modellen te verwijzen:
- Tijdens het compileren ref wordt geconverteerd naar een doelaanwijzer naar een tabel of weergave in Opslag
- ref Hiermee kunt u een modelafhankelijkheidsgrafiek bouwen
Precies voegt vrijwel onbeperkte mogelijkheden toe aan DGT. De meest gebruikte zijn:
- If / else-instructies - vertakkingsinstructies
- Voor lussen
- Variabelen
- Macro - macro's maken
Materialisatie: Tabel, Weergave, Incrementeel
Materialisatiestrategie is een aanpak waarbij de resulterende set modelgegevens wordt opgeslagen in de opslag.
In basistermen is het:
- Tabel - fysieke tabel in de opslag
- Bekijken - bekijken, virtuele tafel in opslag
Er zijn ook complexere materialisatiestrategieën:
- Incrementeel - incrementeel laden (van grote feitentabellen); nieuwe regels worden toegevoegd, gewijzigde regels worden bijgewerkt en verwijderde regels worden gewist
- Kortstondig - het model komt niet direct tot stand, maar neemt als CTE deel aan andere modellen
- Eventuele andere strategieën kunt u zelf toevoegen
Naast materialisatiestrategieën zijn er mogelijkheden voor optimalisatie voor specifieke Opslagplaatsen, bijvoorbeeld:
- Sneeuwvlok: Tijdelijke tabellen, samenvoeggedrag, tabelclustering, subsidies kopiëren, beveiligde weergaven
- Redshift: Distkey, Sortkey (interleaved, samengesteld), Late Binding Views
- BigQuery: Tabelpartitionering en -clustering, samenvoeggedrag, KMS-codering, labels en tags
- Vonk: Bestandsformaat (parket, csv, json, orc, delta), partitie_by, clustered_by, buckets, incrementele_strategie
De volgende opslagplaatsen worden momenteel ondersteund:
- postgres
- Redshift
- BigQuery
- Sneeuwvlok
- Presto (gedeeltelijk)
- Vonk (gedeeltelijk)
- Microsoft SQL Server (communityadapter)
Laten we ons model verbeteren:
- Laten we de vulling incrementeel maken (Incrementeel)
- Laten we segmentatie- en sorteersleutels toevoegen voor 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
Modelafhankelijkheidsgrafiek
Het is ook een afhankelijkheidsboom. Het is ook bekend als DAG (Directed Acyclic Graph).
DBT bouwt een grafiek op basis van de configuratie van alle projectmodellen, of beter gezegd, ref() koppelt binnen modellen naar andere modellen. Met een grafiek kunt u de volgende dingen doen:
- Modellen in de juiste volgorde uitvoeren
- Parallellisering van de vorming van winkelpuien
- Een willekeurige subgraaf uitvoeren
Voorbeeld van grafiekvisualisatie:
Elk knooppunt van de grafiek is een model; de randen van de grafiek worden gespecificeerd door de uitdrukking ref.
Gegevenskwaliteit en documentatie
Naast het genereren van de modellen zelf, kunt u met DBT een aantal aannames over de resulterende dataset testen, zoals:
- Niet nul
- Uniek
- Referentie-integriteit - referentiële integriteit (klant_id in de bestellingentabel komt bijvoorbeeld overeen met id in de klantentabel)
- Overeenkomend met de lijst met acceptabele waarden
Het is mogelijk om eigen testen toe te voegen (custom data testen), zoals bijvoorbeeld % afwijking van omzet met indicatoren van een dag, een week, een maand geleden. Elke aanname geformuleerd als een SQL-query kan een test worden.
Op deze manier kunt u ongewenste afwijkingen en fouten in gegevens in de Magazijnvensters onderkennen.
Op het gebied van documentatie biedt DBT mechanismen voor het toevoegen, versiebeheer en distribueren van metagegevens en commentaar op model- en zelfs attribuutniveau.
Zo ziet het toevoegen van tests en documentatie eruit op configuratiebestandsniveau:
- 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']
En zo ziet deze documentatie eruit op de gegenereerde website:
Macro's en modules
Het doel van DBT is niet zozeer om een set SQL-scripts te worden, maar om gebruikers te voorzien van een krachtige en veelzijdige manier om hun eigen transformaties te bouwen en deze modules te distribueren.
Macro's zijn sets van constructies en expressies die als functies binnen modellen kunnen worden aangeroepen. Met macro's kunt u SQL hergebruiken tussen modellen en projecten in overeenstemming met het DRY-principe (Don't Repeat Yourself).
Macrovoorbeeld:
{% 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 %}
En het gebruik ervan:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT wordt geleverd met een pakketbeheerder waarmee gebruikers individuele modules en macro's kunnen publiceren en hergebruiken.
Dit betekent dat u bibliotheken kunt laden en gebruiken, zoals:
- : werken met Datum/Tijd, Surrogaatsleutels, Schematests, Pivot/Unpivot en andere
- Kant-en-klare showcase-sjablonen voor diensten zoals и
- Bibliotheken voor specifieke datastores, b.v.
- — Module voor het loggen van DBT-werking
Een volledige lijst met pakketten vindt u op .
Nog meer functies
Hier zal ik een paar andere interessante functies en implementaties beschrijven die het team en ik gebruiken om een datawarehouse in te bouwen .
Scheiding van runtime-omgevingen DEV - TEST - PROD
Zelfs binnen hetzelfde DWH-cluster (binnen verschillende regelingen). Gebruik bijvoorbeeld de volgende expressie:
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 -%}
)
Deze code zegt letterlijk: voor omgevingen ontwikkelaar, test, ci neem alleen gegevens van de afgelopen 3 dagen en niet meer. Dat wil zeggen dat het draaien in deze omgevingen veel sneller zal zijn en minder bronnen zal vergen. Wanneer u op een omgeving draait por de filtervoorwaarde wordt genegeerd.
Materialisatie met alternatieve kolomcodering
Redshift is een kolomvormig DBMS waarmee u algoritmen voor gegevenscompressie voor elke afzonderlijke kolom kunt instellen. Het selecteren van optimale algoritmen kan de schijfruimte met 20-50% verminderen.
Makro voert de opdracht ANALYZE COMPRESSION uit, maakt een nieuwe tabel met de aanbevolen kolomcoderingsalgoritmen, gespecificeerde segmentatiesleutels (dist_key) en sorteersleutels (sort_key), brengt de gegevens ernaar over en verwijdert, indien nodig, de oude kopie.
Macrohandtekening:
{{ 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) }}
Registratiemodel wordt uitgevoerd
U kunt haken bevestigen aan elke uitvoering van het model, die wordt uitgevoerd vóór de lancering of onmiddellijk nadat het maken van het model is voltooid:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Met de loggingmodule kunt u alle benodigde metadata in een aparte tabel vastleggen, waarmee u vervolgens knelpunten kunt auditen en analyseren.
Zo ziet het dashboard eruit op basis van de loggegevens in Looker:
Automatisering van opslagonderhoud
Als u enkele uitbreidingen van de functionaliteit van de gebruikte Repository gebruikt, zoals UDF (User Defined Functions), dan is versiebeheer van deze functies, toegangscontrole en het automatisch uitrollen van nieuwe releases erg handig om te doen in DBT.
We gebruiken UDF in Python om hashes, e-maildomeinen en bitmask-decodering te berekenen.
Een voorbeeld van een macro die een UDF maakt in elke uitvoeringsomgeving (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 %}
Bij Wheely gebruiken we Amazon Redshift, dat gebaseerd is op PostgreSQL. Voor Redshift is het belangrijk om regelmatig statistieken over tabellen te verzamelen en schijfruimte vrij te maken - respectievelijk de opdrachten ANALYZE en VACUUM.
Om dit te doen, worden de opdrachten van de macro redshift_maintenance elke nacht uitgevoerd:
{% 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-wolk
Het is mogelijk om DBT als dienst (Managed Service) te gebruiken. Inbegrepen:
- Web IDE voor het ontwikkelen van projecten en modellen
- Taakconfiguratie en planning
- Eenvoudige en gemakkelijke toegang tot logboeken
- Website met documentatie van uw project
- CI verbinden (continue integratie)
Conclusie
Het bereiden en consumeren van DWH wordt net zo plezierig en nuttig als het drinken van een smoothie. DBT bestaat uit Jinja, gebruikersextensies (modules), een compiler, een uitvoerder en een pakketbeheerder. Door deze elementen samen te voegen krijgt u een complete werkomgeving voor uw Data Warehouse. Er is nauwelijks een betere manier om vandaag de dag transformatie binnen DWH te beheren.
De overtuigingen die de ontwikkelaars van DGT volgen, zijn als volgt geformuleerd:
- Code, en niet GUI, is de beste abstractie voor het uitdrukken van complexe analytische logica
- Het werken met data moet de beste praktijken op het gebied van software-engineering (Software Engineering) aanpassen
- Kritieke data-infrastructuur moet door de gebruikersgemeenschap worden beheerd als open source-software
- Niet alleen analysetools, maar ook code zal steeds meer eigendom worden van de Open Source-gemeenschap
Deze kernovertuigingen hebben geleid tot een product dat vandaag de dag door meer dan 850 bedrijven wordt gebruikt, en vormen de basis van vele opwindende uitbreidingen die in de toekomst zullen worden gecreëerd.
Voor de geïnteresseerden is er een video van een open les die ik een paar maanden geleden gaf als onderdeel van een open les bij OTUS - .
Naast DBT en Data Warehousing geven mijn collega's en ik, als onderdeel van de cursus Data Engineer op het OTUS-platform, lessen over een aantal andere relevante en moderne onderwerpen:
- Architecturale concepten voor big data-toepassingen
- Oefen met Spark en Spark Streaming
- Methoden en hulpmiddelen verkennen voor het laden van gegevensbronnen
- Analytische showcases bouwen in DWH
- NoSQL-concepten: HBase, Cassandra, ElasticSearch
- Principes van monitoring en orkestratie
- Eindproject: alle vaardigheden samenbrengen onder mentorondersteuning
referenties:
- — Officiële documentatie
- — Recensieartikel van een van de auteurs van DGT
- — YouTube, opname van een open les van OTUS
- — De volgende open les is 15 mei 2020
- —OTUS
- — Een blik op de toekomst van data en analytics
- — De evolutie van analytics en de invloed van Open Source
- — Principes voor het opbouwen van CI met behulp van DBT
- — Oefening, stapsgewijze instructies voor zelfstandig werken
- — Github, educatieve projectcode
Bron: www.habr.com