Auf welchen Prinzipien basiert ein ideales Data Warehouse?

Konzentrieren Sie sich auf den Geschäftswert und die Analyse, auch wenn es keinen Boilerplate-Code gibt. DWH als Codebasis verwalten: Versionierung, Überprüfung, automatisierte Tests und CI. Modular, erweiterbar, Open Source und Community. Benutzerfreundliche Dokumentation und Abhängigkeitsvisualisierung (Data Lineage).

Mehr darüber und über die Rolle von DBT im Big Data & Analytics-Ökosystem – willkommen bei cat.

Hallo

Artemy Kozyr ist in Kontakt. Seit mehr als 5 Jahren arbeite ich mit Data Warehouses, dem Aufbau von ETL/ELT sowie Datenanalyse und -visualisierung. Ich arbeite derzeit in WheelyIch unterrichte bei OTUS in einem Kurs Dateningenieur, und heute möchte ich mit Ihnen einen Artikel teilen, den ich im Vorgriff auf den Start geschrieben habe Neuanmeldung für den Kurs.

Inhaltsangabe

Im DBT-Framework dreht sich alles um das T im Akronym ELT (Extract – Transform – Load).

Mit dem Aufkommen produktiver und skalierbarer Analysedatenbanken wie BigQuery, Redshift und Snowflake war es sinnlos, Transformationen außerhalb des Data Warehouse durchzuführen.

DBT lädt keine Daten aus Quellen herunter, bietet aber großartige Möglichkeiten für die Arbeit mit Daten, die bereits in den Speicher (im internen oder externen Speicher) geladen wurden.

Der Hauptzweck von DBT besteht darin, den Code zu übernehmen, ihn in SQL zu kompilieren und die Befehle in der richtigen Reihenfolge im Repository auszuführen.

DBT-Projektstruktur

Das Projekt besteht aus Verzeichnissen und Dateien von nur 2 Typen:

Modell (.sql) – eine Transformationseinheit, ausgedrückt durch eine SELECT-Abfrage
Konfigurationsdatei (.yml) – Parameter, Einstellungen, Tests, Dokumentation

Grundsätzlich ist die Arbeit wie folgt aufgebaut:

Der Benutzer bereitet den Modellcode in jeder geeigneten IDE vor
Über die CLI werden Modelle gestartet, DBT kompiliert den Modellcode in SQL
Der kompilierte SQL-Code wird im Storage in einer vorgegebenen Reihenfolge ausgeführt (Graph)

So könnte die Ausführung über die CLI aussehen:

Alles ist AUSGEWÄHLT

Dies ist eine Killerfunktion des Data Build Tool-Frameworks. Mit anderen Worten: DBT abstrahiert den gesamten Code, der mit der Materialisierung Ihrer Abfragen im Store verbunden ist (Variationen von den Befehlen CREATE, INSERT, UPDATE, DELETE ALTER, GRANT usw.).

Bei jedem Modell muss eine SELECT-Abfrage geschrieben werden, die den resultierenden Datensatz definiert.

In diesem Fall kann die Transformationslogik mehrstufig sein und Daten aus mehreren anderen Modellen konsolidieren. Ein Beispiel für ein Modell, das eine Auftragsvitrine (f_orders) erstellt:

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

Welche interessanten Dinge können wir hier sehen?

Erstens: Verwendung von CTE (Common Table Expressions) – zum Organisieren und Verstehen von Code, der viele Transformationen und Geschäftslogik enthält

Zweitens: Modellcode ist eine Mischung aus SQL und Sprache Jinja (Vorlagensprache).

Das Beispiel verwendet eine Schleife für um den Betrag für jede im Ausdruck angegebene Zahlungsmethode zu generieren kompensieren. Die Funktion wird auch verwendet ref – die Möglichkeit, andere Modelle innerhalb des Codes zu referenzieren:

Während der Kompilierung ref wird in einen Zielzeiger auf eine Tabelle oder Ansicht im Speicher umgewandelt
ref ermöglicht Ihnen die Erstellung eines Modellabhängigkeitsdiagramms

Genau Jinja erweitert DBT um nahezu unbegrenzte Möglichkeiten. Die am häufigsten verwendeten sind:

If/else-Anweisungen – Verzweigungsanweisungen
Für Schleifen - Zyklen
Variablen
Makro – Erstellen von Makros

Materialisierung: Tabelle, Ansicht, Inkrementell

Die Materialisierungsstrategie ist ein Ansatz, nach dem der resultierende Satz von Modelldaten im Speicher gespeichert wird.

Grundsätzlich gilt:

Tabelle – physische Tabelle im Speicher
Ansicht – Ansicht, virtuelle Tabelle im Speicher

Es gibt auch komplexere Materialisierungsstrategien:

Inkrementell – inkrementelles Laden (von großen Faktentabellen); Neue Zeilen werden hinzugefügt, geänderte Zeilen werden aktualisiert, gelöschte Zeilen werden gelöscht
Vergänglich – das Modell entsteht nicht direkt, sondern nimmt als CTE an anderen Modellen teil
Alle anderen Strategien können Sie selbst hinzufügen

Neben Materialisierungsstrategien gibt es Optimierungsmöglichkeiten für bestimmte Speicher, zum Beispiel:

Schneeflocke: Transiente Tabellen, Zusammenführungsverhalten, Tabellen-Clustering, Kopierberechtigungen, sichere Ansichten
Redshift: Distkey, Sortkey (interleaved, zusammengesetzt), Late Binding Views
BigQuery: Tabellenpartitionierung und -clusterung, Zusammenführungsverhalten, KMS-Verschlüsselung, Etiketten und Tags
Spark: Dateiformat (Parkett, CSV, JSON, ORC, Delta), Partition_by, Clustered_By, Buckets, Inkrementelle Strategie

Die folgenden Speicher werden derzeit unterstützt:

Postgres
Redshift
BigQuery
Schneeflocke
Presto (teilweise)
Funke (teilweise)
Microsoft SQL Server (Community-Adapter)

Lassen Sie uns unser Modell verbessern:

Machen wir die Füllung inkrementell (inkrementell)
Fügen wir Segmentierungs- und Sortierschlüssel für Redshift hinzu

-- Конфигурация модели: 
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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

Modellabhängigkeitsdiagramm

Es ist auch ein Abhängigkeitsbaum. Es ist auch als DAG (Directed Asymmetric Graph) bekannt.

DBT erstellt ein Diagramm basierend auf der Konfiguration aller Projektmodelle bzw. ref()-Links innerhalb von Modellen zu anderen Modellen. Mit einem Diagramm können Sie Folgendes tun:

Modelle in der richtigen Reihenfolge ausführen
Parallelisierung der Schaufenstergestaltung
Ausführen eines beliebigen Untergraphen

Beispiel einer Diagrammvisualisierung:

Jeder Knoten des Diagramms ist ein Modell; die Kanten des Diagramms werden durch den Ausdruck ref angegeben.

Datenqualität und Dokumentation

Neben der Generierung der Modelle selbst ermöglicht Ihnen DBT das Testen einer Reihe von Annahmen über den resultierenden Datensatz, wie zum Beispiel:

Nicht null
Einzigartig
Referenzintegrität – referenzielle Integrität (z. B. entspricht customer_id in der Tabelle „Orders“ der ID in der Tabelle „customers“).
Entspricht der Liste der akzeptablen Werte

Es ist möglich, eigene Tests (benutzerdefinierte Datentests) hinzuzufügen, wie zum Beispiel die prozentuale Abweichung des Umsatzes mit Indikatoren von einem Tag, einer Woche oder einem Monat. Jede als SQL-Abfrage formulierte Annahme kann zu einem Test werden.

Auf diese Weise können Sie unerwünschte Abweichungen und Fehler in den Daten in den Warehouse-Fenstern erkennen.

Im Hinblick auf die Dokumentation bietet DBT Mechanismen zum Hinzufügen, Versionieren und Verteilen von Metadaten und Kommentaren auf Modell- und sogar Attributebene.

So sieht das Hinzufügen von Tests und Dokumentation auf Konfigurationsdateiebene aus:

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

Und so sieht diese Dokumentation auf der generierten Website aus:

Makros und Module

Der Zweck von DBT besteht nicht so sehr darin, eine Reihe von SQL-Skripten zu erstellen, sondern vielmehr darin, Benutzern ein leistungsstarkes und funktionsreiches Mittel zum Erstellen eigener Transformationen und zum Verteilen dieser Module bereitzustellen.

Makros sind Sätze von Konstrukten und Ausdrücken, die als Funktionen innerhalb von Modellen aufgerufen werden können. Mit Makros können Sie SQL zwischen Modellen und Projekten gemäß dem DRY-Engineering-Prinzip (Don't Repeat Yourself) wiederverwenden.

Makrobeispiel:

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

Und seine Verwendung:

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

DBT verfügt über einen Paketmanager, der es Benutzern ermöglicht, einzelne Module und Makros zu veröffentlichen und wiederzuverwenden.

Dies bedeutet, dass Sie Bibliotheken laden und verwenden können wie:

dbt_utils: Arbeiten mit Datum/Uhrzeit, Ersatzschlüsseln, Schematests, Pivot/Unpivot und anderen
Vorgefertigte Showcase-Vorlagen für Dienstleistungen wie Schneepflug и Stripe
Bibliotheken für bestimmte Datenspeicher, z.B. Redshift
Protokollierung — Modul zur Protokollierung des DBT-Betriebs

Eine vollständige Liste der Pakete finden Sie unter DBT-Hub.

Noch mehr Funktionen

Hier beschreibe ich einige weitere interessante Funktionen und Implementierungen, die das Team und ich zum Aufbau eines Data Warehouse verwenden Wheely.

Trennung der Laufzeitumgebungen DEV – TEST – PROD

Sogar innerhalb desselben DWH-Clusters (innerhalb verschiedener Systeme). Verwenden Sie beispielsweise den folgenden Ausdruck:

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

Dieser Code sagt wörtlich: für Umgebungen Entwickler, Test, CI Nehmen Sie nur die Daten der letzten 3 Tage und nicht mehr auf. Das heißt, die Ausführung in diesen Umgebungen ist viel schneller und erfordert weniger Ressourcen. Beim Ausführen in einer Umgebung Stoß Die Filterbedingung wird ignoriert.

Materialisierung mit alternativer Spaltenkodierung

Redshift ist ein spaltenorientiertes DBMS, mit dem Sie Datenkomprimierungsalgorithmen für jede einzelne Spalte festlegen können. Durch die Auswahl optimaler Algorithmen kann der Speicherplatz um 20–50 % reduziert werden.

Makro redshift.compress_table führt den Befehl ANALYZE COMPRESSION aus, erstellt eine neue Tabelle mit den empfohlenen Spaltenkodierungsalgorithmen, den angegebenen Segmentierungsschlüsseln (dist_key) und Sortierschlüsseln (sort_key), überträgt die Daten dorthin und löscht bei Bedarf die alte Kopie.

Makrosignatur:

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

Protokollierungsmodell wird ausgeführt

Sie können jeder Ausführung des Modells Hooks hinzufügen, die vor dem Start oder unmittelbar nach Abschluss der Modellerstellung ausgeführt werden:

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

Mit dem Protokollierungsmodul können Sie alle erforderlichen Metadaten in einer separaten Tabelle aufzeichnen, die anschließend zur Prüfung und Analyse von Engpässen verwendet werden kann.

So sieht das Dashboard basierend auf den Protokollierungsdaten in Looker aus:

Automatisierung der Speicherwartung

Wenn Sie einige Erweiterungen der Funktionalität des verwendeten Repositorys verwenden, wie z. B. UDF (User Defined Functions), ist die Versionierung dieser Funktionen, die Zugriffskontrolle und die automatisierte Einführung neuer Releases in DBT sehr praktisch.

Wir verwenden UDF in Python, um Hashes, E-Mail-Domänen und Bitmasken-Dekodierung zu berechnen.

Ein Beispiel für ein Makro, das eine UDF in einer beliebigen Ausführungsumgebung (dev, test, prod) erstellt:

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

Bei Wheely verwenden wir Amazon Redshift, das auf PostgreSQL basiert. Für Redshift ist es wichtig, regelmäßig Statistiken zu Tabellen zu sammeln und Speicherplatz freizugeben – die Befehle ANALYZE bzw. VACUUM.

Dazu werden jede Nacht die Befehle aus dem Makro redshift_maintenance ausgeführt:

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

Es besteht die Möglichkeit, DBT als Service (Managed Service) zu nutzen. Im Komplekt:

Web-IDE zur Entwicklung von Projekten und Modellen
Jobkonfiguration und -planung
Einfacher und bequemer Zugriff auf Protokolle
Website mit Dokumentation Ihres Projekts
Anbindung von CI (Continuous Integration)

Abschluss

Die Zubereitung und der Verzehr von DWH wird so angenehm und wohltuend wie das Trinken eines Smoothies. DBT besteht aus Jinja, Benutzererweiterungen (Modulen), einem Compiler, einem Executor und einem Paketmanager. Durch die Zusammenstellung dieser Elemente erhalten Sie eine vollständige Arbeitsumgebung für Ihr Data Warehouse. Es gibt heute kaum einen besseren Weg, die Transformation innerhalb des DWH zu bewältigen.

Die Überzeugungen der Entwickler von DBT sind wie folgt formuliert:

Code, nicht GUI, ist die beste Abstraktion, um komplexe analytische Logik auszudrücken
Die Arbeit mit Daten sollte Best Practices im Software-Engineering (Software Engineering) anpassen.

Kritische Dateninfrastrukturen sollten von der Benutzergemeinschaft als Open-Source-Software kontrolliert werden
Nicht nur Analysetools, sondern auch Code wird zunehmend Eigentum der Open-Source-Community

Diese Grundüberzeugungen haben ein Produkt hervorgebracht, das heute von über 850 Unternehmen verwendet wird, und sie bilden die Grundlage für viele spannende Erweiterungen, die in Zukunft entstehen werden.

Für Interessierte gibt es ein Video einer offenen Lektion, die ich vor ein paar Monaten im Rahmen einer offenen Lektion bei OTUS gegeben habe – Datenerstellungstool für Amazon Redshift Storage.

Neben DBT und Data Warehousing unterrichten meine Kollegen und ich im Rahmen des Data Engineer-Kurses auf der OTUS-Plattform Kurse zu einer Reihe weiterer relevanter und moderner Themen:

Architekturkonzepte für Big-Data-Anwendungen
Üben Sie mit Spark und Spark Streaming
Erkunden von Methoden und Tools zum Laden von Datenquellen
Erstellen analytischer Schaufenster im DWH
NoSQL-Konzepte: HBase, Cassandra, ElasticSearch
Prinzipien der Überwachung und Orchestrierung
Abschlussprojekt: Zusammenführung aller Fähigkeiten unter Mentoring-Unterstützung

Data Build Tool oder was zwischen Data Warehouse und Smoothie gemeinsam ist