Sur quels principes est construit un Data Warehouse idéal ?
Concentrez-vous sur la valeur commerciale et l’analyse en l’absence de code passe-partout. Gestion de DWH en tant que base de code : versioning, révision, tests automatisés et CI. Modulaire, extensible, open source et communautaire. Documentation conviviale et visualisation des dépendances (Data Lineage).
Pour en savoir plus sur tout cela et sur le rôle de DBT dans l'écosystème Big Data & Analytics - bienvenue sur cat.
Bonjour
Artemy Kozyr est en contact. Depuis plus de 5 ans, je travaille avec des entrepôts de données, la création d'ETL/ELT, ainsi que l'analyse et la visualisation de données. Je travaille actuellement dans , j'enseigne à l'OTUS dans le cadre d'un cursus , et aujourd'hui je souhaite partager avec vous un article que j'ai écrit en prévision du début nouvelle inscription au cours.
Synopsis
Le framework DBT est entièrement basé sur le T dans l'acronyme ELT (Extract - Transform - Load).
Avec l'avènement de bases de données analytiques aussi productives et évolutives que BigQuery, Redshift, Snowflake, il ne servait à rien d'effectuer des transformations en dehors du Data Warehouse.
DBT ne télécharge pas de données à partir de sources, mais offre d'excellentes opportunités de travailler avec des données déjà chargées dans le stockage (dans le stockage interne ou externe).
L'objectif principal de DBT est de prendre le code, de le compiler en SQL, d'exécuter les commandes dans le bon ordre dans le référentiel.
Structure du projet DBT
Le projet se compose de répertoires et de fichiers de seulement 2 types :
- Modèle (.sql) - une unité de transformation exprimée par une requête SELECT
- Fichier de configuration (.yml) - paramètres, réglages, tests, documentation
Au niveau de base, le travail est structuré comme suit :
- L'utilisateur prépare le code du modèle dans n'importe quel IDE pratique
- À l'aide de la CLI, les modèles sont lancés, DBT compile le code du modèle en SQL
- Le code SQL compilé est exécuté dans le Storage dans une séquence donnée (graphique)
Voici à quoi pourrait ressembler une exécution à partir de la CLI :
Tout est SELECT
Il s’agit d’une fonctionnalité phare du framework Data Build Tool. Autrement dit, DBT extrait tout le code associé à la matérialisation de vos requêtes dans le Store (variations des commandes CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Tout modèle implique l'écriture d'une requête SELECT qui définit l'ensemble de données résultant.
Dans ce cas, la logique de transformation peut être multi-niveaux et consolider les données de plusieurs autres modèles. Un exemple de modèle qui construira une vitrine de commandes (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
Quelles choses intéressantes pouvons-nous voir ici ?
Premièrement : utilisation de CTE (Common Table Expressions) - pour organiser et comprendre le code qui contient de nombreuses transformations et logique métier
Deuxièmement : le code modèle est un mélange de SQL et de langage (langage de modèles).
L'exemple utilise une boucle en pour générer le montant pour chaque mode de paiement spécifié dans l'expression set. La fonction est également utilisée ref — la possibilité de référencer d'autres modèles dans le code :
- Pendant la compilation ref sera converti en un pointeur cible vers une table ou une vue dans le stockage
- ref vous permet de créer un graphique de dépendances de modèle
A savoir ajoute des possibilités presque illimitées à DBT. Les plus couramment utilisés sont :
- Instructions if/else – instructions de branchement
- Pour les boucles
- Variables
- Macro - création de macros
Matérialisation : table, vue, incrémentielle
La stratégie de matérialisation est une approche selon laquelle l'ensemble de données de modèle résultant sera stocké dans le Storage.
En termes fondamentaux, c'est :
- Table - table physique dans le stockage
- Vue - vue, table virtuelle dans le stockage
Il existe également des stratégies de matérialisation plus complexes :
- Incrémentiel - chargement incrémentiel (de grandes tables de faits) ; de nouvelles lignes sont ajoutées, les lignes modifiées sont mises à jour, les lignes supprimées sont effacées
- Éphémère - le modèle ne se matérialise pas directement, mais participe en tant que CTE à d'autres modèles
- Toute autre stratégie que vous pouvez ajouter vous-même
En plus des stratégies de matérialisation, il existe des opportunités d'optimisation pour des stockages spécifiques, par exemple :
- Flocon : Tables transitoires, comportement de fusion, clustering de tables, autorisations de copie, vues sécurisées
- Redshift: Distkey, Sortkey (entrelacé, composé), vues à liaison tardive
- BigQuery: Partitionnement et clustering de tables, comportement de fusion, cryptage KMS, étiquettes et tags
- Spark: Format de fichier (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incrémental_strategy
Les stockages suivants sont actuellement pris en charge :
- Postgres
- Redshift
- BigQuery
- Flocon
- Presto (partiellement)
- Étincelle (partiellement)
- Microsoft SQL Server (adaptateur communautaire)
Améliorons notre modèle :
- Rendons son remplissage incrémentiel (Incrémental)
- Ajoutons des clés de segmentation et de tri pour 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
Graphique de dépendance du modèle
C'est aussi un arbre de dépendances. Il est également connu sous le nom de DAG (Directed Acyclic Graph).
DBT construit un graphique basé sur la configuration de tous les modèles de projet, ou plutôt, des liens ref() au sein des modèles vers d'autres modèles. Avoir un graphique vous permet de faire les choses suivantes :
- Exécuter les modèles dans le bon ordre
- Parallélisation de la formation des vitrines
- Exécuter un sous-graphe arbitraire
Exemple de visualisation graphique :
Chaque nœud du graphe est un modèle ; les arêtes du graphe sont spécifiées par l'expression ref.
Qualité des données et documentation
En plus de générer les modèles eux-mêmes, DBT vous permet de tester un certain nombre d'hypothèses sur l'ensemble de données résultant, telles que :
- Non nul
- Unique
- Intégrité de référence - intégrité référentielle (par exemple, customer_id dans la table des commandes correspond à l'identifiant dans la table des clients)
- Faire correspondre la liste des valeurs acceptables
Il est possible d'ajouter vos propres tests (tests de données personnalisés), comme par exemple le % d'écart des revenus avec des indicateurs d'il y a un jour, une semaine, un mois. Toute hypothèse formulée sous forme de requête SQL peut devenir un test.
De cette manière, vous pouvez détecter les écarts et erreurs indésirables dans les données dans les fenêtres Warehouse.
En termes de documentation, DBT fournit des mécanismes pour ajouter, gérer les versions et distribuer des métadonnées et des commentaires au niveau du modèle et même des attributs.
Voici à quoi ressemble l'ajout de tests et de documentation au niveau du fichier de configuration :
- 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']
Et voici à quoi ressemble cette documentation sur le site généré :
Macros et modules
Le but de DBT n'est pas tant de devenir un ensemble de scripts SQL, mais de fournir aux utilisateurs un moyen puissant et riche en fonctionnalités pour construire leurs propres transformations et distribuer ces modules.
Les macros sont des ensembles de constructions et d'expressions qui peuvent être appelées en tant que fonctions au sein de modèles. Les macros vous permettent de réutiliser SQL entre modèles et projets conformément au principe d'ingénierie DRY (Don't Repeat Yourself).
Exemple de 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 %}
Et son utilisation :
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT est livré avec un gestionnaire de packages qui permet aux utilisateurs de publier et de réutiliser des modules et des macros individuels.
Cela signifie être capable de charger et d'utiliser des bibliothèques telles que :
- : travailler avec la date/heure, les clés de substitution, les tests de schéma, Pivot/Unpivot et autres
- Modèles de vitrine prêts à l'emploi pour des services tels que и
- Bibliothèques pour des magasins de données spécifiques, par ex.
- — Module de journalisation des opérations DBT
Une liste complète des forfaits peut être trouvée sur .
Encore plus de fonctionnalités
Ici, je vais décrire quelques autres fonctionnalités et implémentations intéressantes que l'équipe et moi utilisons pour créer un entrepôt de données dans .
Séparation des environnements d'exécution DEV - TEST - PROD
Même au sein du même cluster DWH (au sein de différents schémas). Par exemple, en utilisant l'expression suivante :
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 -%}
)
Ce code dit littéralement : pour les environnements développement, test, ci prenez les données uniquement pour les 3 derniers jours et pas plus. Autrement dit, l'exécution dans ces environnements sera beaucoup plus rapide et nécessitera moins de ressources. Lors de l'exécution sur l'environnement poussée la condition du filtre sera ignorée.
Matérialisation avec codage de colonne alternatif
Redshift est un SGBD en colonnes qui vous permet de définir des algorithmes de compression de données pour chaque colonne individuelle. La sélection d'algorithmes optimaux peut réduire l'espace disque de 20 à 50 %.
Macro exécutera la commande ANALYZE COMPRESSION, créera une nouvelle table avec les algorithmes de codage de colonnes recommandés, les clés de segmentation spécifiées (dist_key) et les clés de tri (sort_key), y transférera les données et, si nécessaire, supprimera l'ancienne copie.
Signature des macros :
{{ 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) }}
Exécutions du modèle de journalisation
Vous pouvez attacher des hooks à chaque exécution du modèle, qui sera exécuté avant le lancement ou immédiatement après la fin de la création du modèle :
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Le module de journalisation vous permettra d'enregistrer toutes les métadonnées nécessaires dans un tableau séparé, qui pourra ensuite être utilisé pour auditer et analyser les goulots d'étranglement.
Voici à quoi ressemble le tableau de bord basé sur les données de journalisation dans Looker :
Automatisation de la maintenance du stockage
Si vous utilisez certaines extensions des fonctionnalités du référentiel utilisé, telles que UDF (User Defined Functions), alors la gestion des versions de ces fonctions, le contrôle d'accès et le déploiement automatisé des nouvelles versions sont très pratiques à réaliser dans DBT.
Nous utilisons UDF en Python pour calculer les hachages, les domaines de messagerie et le décodage des masques de bits.
Un exemple de macro qui crée une UDF sur n'importe quel environnement d'exécution (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 %}
Chez Wheely, nous utilisons Amazon Redshift, basé sur PostgreSQL. Pour Redshift, il est important de collecter régulièrement des statistiques sur les tables et de libérer de l'espace disque - les commandes ANALYZE et VACUUM, respectivement.
Pour ce faire, les commandes de la macro redshift_maintenance sont exécutées chaque nuit :
{% 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 %}
Nuage DBT
Il est possible d'utiliser DBT en tant que service (Managed Service). Inclus:
- IDE Web pour développer des projets et des modèles
- Configuration et planification des tâches
- Accès simple et pratique aux journaux
- Site Internet avec documentation de votre projet
- Connexion CI (intégration continue)
Conclusion
Préparer et consommer du DWH devient aussi agréable et bénéfique que boire un smoothie. DBT se compose de Jinja, d'extensions utilisateur (modules), d'un compilateur, d'un exécuteur et d'un gestionnaire de packages. En réunissant ces éléments, vous obtenez un environnement de travail complet pour votre Data Warehouse. Il n’existe aujourd’hui guère de meilleure façon de gérer la transformation au sein de DWH.
Les convictions suivies par les développeurs de DBT sont formulées comme suit :
- Le code, et non l'interface graphique, est la meilleure abstraction pour exprimer une logique analytique complexe
- Travailler avec des données doit adapter les meilleures pratiques en génie logiciel (Software Engineering)
- L'infrastructure de données critiques doit être contrôlée par la communauté des utilisateurs en tant que logiciel open source
- Non seulement les outils d'analyse, mais aussi le code deviendront de plus en plus la propriété de la communauté Open Source
Ces convictions fondamentales ont donné naissance à un produit utilisé aujourd'hui par plus de 850 entreprises et constituent la base de nombreuses extensions passionnantes qui seront créées à l'avenir.
Pour ceux que ça intéresse, il y a une vidéo d'un cours ouvert que j'ai donné il y a quelques mois dans le cadre d'un cours ouvert à l'OTUS - .
En plus du DBT et du Data Warehousing, dans le cadre du cours Data Engineer sur la plateforme OTUS, mes collègues et moi donnons des cours sur un certain nombre d'autres sujets pertinents et modernes :
- Concepts architecturaux pour les applications Big Data
- Entraînez-vous avec Spark et Spark Streaming
- Explorer les méthodes et outils de chargement des sources de données
- Construire des vitrines analytiques dans DWH
- Concepts NoSQL : HBase, Cassandra, ElasticSearch
- Principes de surveillance et d’orchestration
- Projet final : rassembler toutes les compétences sous accompagnement par un mentorat
Liens:
- — Documentation officielle
- — Article de synthèse de l'un des auteurs de DBT
- — YouTube, Enregistrement d'une leçon ouverte OTUS
- — La prochaine leçon ouverte est le 15 mai 2020
- —OTUS
- — Un regard sur l'avenir des données et de l'analyse
- — L'évolution de l'analytique et l'influence de l'Open Source
- — Principes de construction de CI à l'aide de DBT
- — Pratique, instructions étape par étape pour un travail indépendant
- — Github, code de projet pédagogique
Source: habr.com