Su quali principi è costruito un Data Warehouse ideale?

Concentrarsi sul valore aziendale e sull'analisi in assenza di codice standard. Gestire DWH come base di codice: controllo delle versioni, revisione, test automatizzati e CI. Modulare, estensibile, open source e community. Documentazione intuitiva e visualizzazione delle dipendenze (Data Lineage).

Maggiori informazioni su tutto questo e sul ruolo del DBT nell'ecosistema Big Data & Analytics: benvenuti in cat.

Ciao

Artemy Kozyr è in contatto. Da più di 5 anni lavoro con data warehouse, creazione di ETL/ELT, nonché analisi e visualizzazione dei dati. Attualmente sto lavorando in Wheely, insegno presso OTUS in un corso Ingegnere dati, e oggi voglio condividere con voi un articolo che ho scritto in previsione dell'inizio nuova iscrizione al corso.

sinossi

Il framework DBT è incentrato sulla T nell'acronimo ELT (Extract - Transform - Load).

Con l'avvento di database analitici produttivi e scalabili come BigQuery, Redshift, Snowflake, non aveva senso effettuare trasformazioni al di fuori del Data Warehouse.

DBT non scarica i dati dalle origini, ma offre grandi opportunità per lavorare con i dati che sono già stati caricati nell'archivio (nell'archivio interno o esterno).

Lo scopo principale di DBT è prendere il codice, compilarlo in SQL, eseguire i comandi nella sequenza corretta nel Repository.

Struttura del progetto DBT

Il progetto è composto da directory e file di soli 2 tipi:

Modello (.sql) - un'unità di trasformazione espressa da una query SELECT
File di configurazione (.yml): parametri, impostazioni, test, documentazione

A livello base il lavoro è strutturato come segue:

L'utente prepara il codice del modello in qualsiasi IDE conveniente
Utilizzando la CLI, i modelli vengono avviati, DBT compila il codice del modello in SQL
Il codice SQL compilato viene eseguito nello Storage in una determinata sequenza (grafico)

Ecco come potrebbe apparire l'esecuzione dalla CLI:

Tutto è SELEZIONATO

Questa è una caratteristica fondamentale del framework Data Build Tool. In altre parole, DBT astrae tutto il codice associato alla materializzazione delle tue query nello Store (variazioni dei comandi CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).

Qualsiasi modello prevede la scrittura di una query SELECT che definisce il set di dati risultante.

In questo caso, la logica di trasformazione può essere multilivello e consolidare i dati provenienti da diversi altri modelli. Un esempio di modello che costruirà una vetrina di ordini (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

Quali cose interessanti possiamo vedere qui?

Primo: CTE (Common Table Expressions) utilizzato: per organizzare e comprendere il codice che contiene molte trasformazioni e logica aziendale

Secondo: il codice del modello è una miscela di SQL e linguaggio Jinja (linguaggio dei modelli).

L'esempio utilizza un ciclo per per generare l'importo per ciascun metodo di pagamento specificato nell'espressione set. Viene utilizzata anche la funzione ref — la possibilità di fare riferimento ad altri modelli all'interno del codice:

Durante la compilazione ref verrà convertito in un puntatore di destinazione in una tabella o vista in Archiviazione
ref consente di creare un grafico delle dipendenze del modello

Esattamente Jinja aggiunge possibilità quasi illimitate a DBT. Quelli più comunemente usati sono:

Istruzioni If / else - istruzioni di ramo
Per loop - cicli
Variabili
Macro: creazione di macro

Materializzazione: tabella, vista, incrementale

La strategia di materializzazione è un approccio in base al quale l'insieme risultante di dati del modello verrà archiviato nello Storage.

In termini fondamentali è:

Tabella: tabella fisica nello spazio di archiviazione
Visualizza: visualizza la tabella virtuale in Archiviazione

Esistono anche strategie di materializzazione più complesse:

Incrementale - caricamento incrementale (di tabelle dei fatti di grandi dimensioni); vengono aggiunte nuove righe, le righe modificate vengono aggiornate, le righe cancellate vengono cancellate
Effimero: il modello non si materializza direttamente, ma partecipa come CTE ad altri modelli
Eventuali altre strategie che puoi aggiungere tu stesso

Oltre alle strategie di materializzazione, esistono opportunità di ottimizzazione per magazzini specifici, ad esempio:

Fiocco di neve: Tabelle transitorie, Comportamento di unione, Clustering di tabelle, Concessioni di copia, Viste protette
redshift: Distkey, Sortkey (interleaved, compound), Viste con associazione tardiva
BigQuery: partizionamento e clustering delle tabelle, comportamento di unione, crittografia KMS, etichette e tag
Scintilla: Formato file (parquet, csv, json, orc, delta), partizione_per, clustered_by, bucket, incremental_strategy

Attualmente sono supportati i seguenti archivi:

Postgres
redshift
BigQuery
Fiocco di neve
Presto (parzialmente)
Scintilla (parzialmente)
Microsoft SQL Server (adattatore di comunità)

Miglioriamo il nostro modello:

Rendiamo incrementale il suo riempimento (Incrementale)
Aggiungiamo chiavi di segmentazione e ordinamento per 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

Grafico delle dipendenze del modello

È anche un albero delle dipendenze. È noto anche come DAG (grafico aciclico diretto).

DBT crea un grafico basato sulla configurazione di tutti i modelli di progetto, o meglio, ref() collega all'interno dei modelli ad altri modelli. Avere un grafico ti consente di fare le seguenti cose:

Esecuzione dei modelli nella sequenza corretta
Parallelizzazione della formazione di vetrine
Esecuzione di un sottografo arbitrario

Esempio di visualizzazione del grafico:

Ogni nodo del grafico è un modello; gli spigoli del grafico sono specificati dall'espressione rif.

Qualità dei dati e documentazione

Oltre a generare i modelli stessi, DBT consente di testare una serie di ipotesi sul set di dati risultante, come ad esempio:

Non nullo
Unico
Integrità di riferimento: integrità referenziale (ad esempio, customer_id nella tabella degli ordini corrisponde all'id nella tabella dei clienti)
Corrispondenza all'elenco dei valori accettabili

È possibile aggiungere i propri test (test dei dati personalizzati), come ad esempio la deviazione percentuale delle entrate con indicatori di un giorno, una settimana, un mese fa. Qualsiasi ipotesi formulata come query SQL può diventare un test.

In questo modo è possibile individuare deviazioni ed errori indesiderati nei dati nelle finestre del Magazzino.

In termini di documentazione, DBT fornisce meccanismi per l'aggiunta, il controllo delle versioni e la distribuzione di metadati e commenti a livello di modello e persino di attributo.

Ecco come appare l'aggiunta di test e documentazione a livello di file di configurazione:

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

Ed ecco come appare questa documentazione sul sito Web generato:

Macro e moduli

Lo scopo di DBT non è tanto quello di diventare un insieme di script SQL, ma di fornire agli utenti un mezzo potente e ricco di funzionalità per costruire le proprie trasformazioni e distribuire questi moduli.

Le macro sono insiemi di costrutti ed espressioni che possono essere chiamate come funzioni all'interno dei modelli. Le macro consentono di riutilizzare SQL tra modelli e progetti in conformità con il principio ingegneristico DRY (Don't Repeat Yourself).

Esempio macro:

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

E i suoi usi:

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

DBT viene fornito con un gestore di pacchetti che consente agli utenti di pubblicare e riutilizzare singoli moduli e macro.

Ciò significa poter caricare e utilizzare librerie come:

dbt_utils: lavorare con data/ora, chiavi surrogate, test di schema, Pivot/Unpivot e altri
Modelli di vetrine già pronti per servizi come Spazzaneve и Stripe
Librerie per archivi dati specifici, ad es. redshift
Registrazione — Modulo per la registrazione del funzionamento del DBT

Un elenco completo dei pacchetti può essere trovato su hub dbt.

Ancora più funzioni

Qui descriverò alcune altre funzionalità e implementazioni interessanti che io e il team utilizziamo per creare un data warehouse Wheely.

Separazione degli ambienti runtime DEV - TEST - PROD

Anche all'interno dello stesso cluster DWH (all'interno di schemi diversi). Ad esempio, utilizzando la seguente espressione:

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

Questo codice dice letteralmente: per ambienti sviluppatore, test, ci prendi i dati solo per gli ultimi 3 giorni e non di più. Cioè, l'esecuzione in questi ambienti sarà molto più veloce e richiederà meno risorse. Quando si esegue in ambiente pungolo la condizione del filtro verrà ignorata.

Materializzazione con codifica a colonne alternative

Redshift è un DBMS colonnare che consente di impostare algoritmi di compressione dei dati per ogni singola colonna. La selezione di algoritmi ottimali può ridurre lo spazio su disco del 20-50%.

Макрос redshift.compress_table eseguirà il comando ANALYZE COMPRESSION, creerà una nuova tabella con gli algoritmi di codifica delle colonne consigliati, le chiavi di segmentazione specificate (dist_key) e le chiavi di ordinamento (sort_key), trasferirà i dati in essa e, se necessario, eliminerà la vecchia copia.

Firma macro:

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

Viene eseguito il modello di registrazione

È possibile allegare degli hook a ciascuna esecuzione del modello, che verrà eseguita prima del lancio o immediatamente dopo il completamento della creazione del modello:

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

Il modulo di registrazione ti consentirà di registrare tutti i metadati necessari in una tabella separata, che potrà successivamente essere utilizzata per verificare e analizzare i colli di bottiglia.

Ecco come appare la dashboard in base ai dati di registrazione in Looker:

Automazione della manutenzione dello storage

Se si utilizzano alcune estensioni della funzionalità del repository utilizzato, come UDF (User Defined Functions), il controllo delle versioni di queste funzioni, il controllo degli accessi e l'implementazione automatizzata delle nuove versioni è molto conveniente da eseguire in DBT.

Utilizziamo UDF in Python per calcolare hash, domini di posta elettronica e decodifica di maschere di bit.

Un esempio di macro che crea una UDF su qualsiasi ambiente di esecuzione (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 %}

Noi di Wheely utilizziamo Amazon Redshift, che si basa su PostgreSQL. Per Redshift, è importante raccogliere regolarmente statistiche sulle tabelle e liberare spazio su disco: rispettivamente i comandi ANALYZE e VACUUM.

Per fare ciò, ogni notte vengono eseguiti i comandi della macro redshift_maintenance:

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

È possibile utilizzare DBT come servizio (Managed Service). Incluso:

IDE Web per lo sviluppo di progetti e modelli
Configurazione e pianificazione del lavoro
Accesso semplice e conveniente ai registri
Sito web con la documentazione del tuo progetto
Collegamento della CI (integrazione continua)

conclusione

Preparare e consumare DWH diventa piacevole e benefico come bere un frullato. DBT è costituito da Jinja, estensioni utente (moduli), un compilatore, un esecutore e un gestore di pacchetti. Mettendo insieme questi elementi ottieni un ambiente di lavoro completo per il tuo Data Warehouse. Oggi non esiste quasi un modo migliore per gestire la trasformazione all’interno di DWH.

Le convinzioni seguite dagli sviluppatori di DBT sono formulate come segue:

Il codice, non la GUI, è la migliore astrazione per esprimere una logica analitica complessa
Lavorare con i dati dovrebbe adattare le migliori pratiche nell'ingegneria del software (ingegneria del software)

L'infrastruttura dati critica dovrebbe essere controllata dalla comunità degli utenti come software open source
Non solo gli strumenti di analisi, ma anche il codice diventeranno sempre più proprietà della comunità Open Source

Queste convinzioni fondamentali hanno dato vita a un prodotto utilizzato oggi da oltre 850 aziende e costituiscono la base di molte interessanti estensioni che verranno create in futuro.

Per chi fosse interessato, c'è il video di una lezione aperta che ho tenuto qualche mese fa nell'ambito di una lezione aperta presso OTUS - Strumento di creazione dati per lo storage Amazon Redshift.

Oltre a DBT e Data Warehousing, nell'ambito del corso Data Engineer sulla piattaforma OTUS, io e i miei colleghi teniamo lezioni su una serie di altri argomenti rilevanti e moderni:

Concetti architettonici per applicazioni Big Data
Esercitati con Spark e Spark Streaming
Esplorare metodi e strumenti per caricare le origini dati
Costruire vetrine analitiche in DWH
Concetti NoSQL: HBase, Cassandra, ElasticSearch
Principi di monitoraggio e orchestrazione
Progetto finale: mettere insieme tutte le competenze con il supporto di mentoring

Data Build Tool o ciò che è comune tra Data Warehouse e Smoothie