En que principios se constrúe un Data Warehouse ideal?
Céntrese no valor comercial e as analíticas en ausencia de código estándar. Xestionar DWH como base de código: versións, revisión, probas automatizadas e CI. Modular, extensible, de código aberto e comunidade. Documentación fácil de usar e visualización de dependencias (Data Lineage).
Máis información sobre todo isto e sobre o papel da DBT no ecosistema de Big Data & Analytics: benvido a cat.
Ola a todos
Artemy Kozyr está en contacto. Durante máis de 5 anos estiven traballando con almacéns de datos, construíndo ETL/ELT, así como a análise e visualización de datos. Actualmente estou traballando en , ensino en OTUS nun curso , e hoxe quero compartir con vós un artigo que escribín á espera do comezo nova matrícula para o curso.
Revisión breve
O marco DBT trata sobre a T no acrónimo ELT (Extract - Transform - Load).
Coa chegada de bases de datos analíticas tan produtivas e escalables como BigQuery, Redshift, Snowflake, non tiña sentido facer transformacións fóra do Data Warehouse.
DBT non descarga datos das fontes, pero ofrece grandes oportunidades para traballar con datos que xa se cargaron no Almacenamento (en Almacenamento interno ou externo).
O propósito principal de DBT é tomar o código, compilalo en SQL, executar os comandos na secuencia correcta no Repositorio.
Estrutura do proxecto DBT
O proxecto consta de directorios e ficheiros de só 2 tipos:
- Modelo (.sql): unha unidade de transformación expresada por unha consulta SELECT
- Ficheiro de configuración (.yml) - parámetros, configuración, probas, documentación
A nivel básico, o traballo estrutúrase do seguinte xeito:
- O usuario prepara o código do modelo en calquera IDE conveniente
- Usando a CLI, lánzanse modelos, DBT compila o código do modelo en SQL
- O código SQL compilado execútase no Almacenamento nunha secuencia determinada (gráfico)
Aquí tes como pode ser executado desde a CLI:
Todo é SELECCIONAR
Esta é unha característica extraordinaria do marco da ferramenta Data Build. Noutras palabras, DBT abstrae todo o código asociado coa materialización das túas consultas na Tenda (variacións dos comandos CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Calquera modelo implica escribir unha consulta SELECT que defina o conxunto de datos resultante.
Neste caso, a lóxica de transformación pode ser multinivel e consolidar datos de varios outros modelos. Un exemplo de modelo que creará un escaparate de pedidos (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
Que cousas interesantes podemos ver aquí?
Primeiro: usou CTE (Expresións de táboa comúns): para organizar e comprender o código que contén moitas transformacións e lóxica empresarial
Segundo: o código do modelo é unha mestura de SQL e linguaxe (linguaxe de modelos).
O exemplo usa un bucle para para xerar o importe para cada método de pago especificado na expresión conxunto. Tamén se usa a función ref — a capacidade de facer referencia a outros modelos dentro do código:
- Durante a compilación ref converterase nun punteiro de destino a unha táboa ou vista en Storage
- ref permítelle construír un gráfico de dependencia do modelo
Exactamente engade posibilidades case ilimitadas a DBT. Os máis utilizados son:
- Instruccións if / else - instrucións de rama
- Para bucles
- Variables
- Macro: creación de macros
Materialización: Táboa, Vista, Incremental
A estratexia de materialización é un enfoque segundo o cal o conxunto resultante de datos do modelo almacenarase no Almacenamento.
En termos básicos é:
- Táboa - táboa física no Almacenamento
- Ver - ver, táboa virtual en Almacenamento
Tamén hai estratexias de materialización máis complexas:
- Incremental - carga incremental (de grandes táboas de feitos); engádense novas liñas, actualízanse as liñas modificadas e borran as liñas eliminadas
- Efémero: o modelo non se materializa directamente, senón que participa como CTE noutros modelos
- Calquera outra estratexia pode engadir vostede mesmo
Ademais das estratexias de materialización, hai oportunidades de optimización para Almacenamentos específicos, por exemplo:
- Folerpa de neve: Táboas transitorias, Comportamento de combinación, Agrupación de táboas, Concesións de copia, Vistas seguras
- Redshift: Distkey, Sortkey (intercalado, composto), Vistas de vinculación tardía
- bigquery: Partición e agrupación de táboas, Comportamento de combinación, Cifrado KMS, Etiquetas e etiquetas
- Faísca: Formato de ficheiro (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
Actualmente son compatibles os seguintes almacenamentos:
- postgres
- Redshift
- bigquery
- Folerpa de neve
- Presto (parcialmente)
- Faísca (parcialmente)
- Microsoft SQL Server (adaptador comunitario)
Imos mellorar o noso modelo:
- Fagamos o seu recheo incremental (Incremental)
- Engademos claves de segmentación e clasificación para 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
Gráfica de dependencia do modelo
Tamén é unha árbore de dependencias. Tamén se coñece como DAG (Directed Acyclic Graph).
DBT constrúe un gráfico baseado na configuración de todos os modelos de proxecto, ou mellor dito, enlaza ref() dentro dos modelos a outros modelos. Ter un gráfico permíteche facer as seguintes cousas:
- Execución de modelos na secuencia correcta
- Paralelización da formación de escaparates
- Execución dun subgrafo arbitrario
Exemplo de visualización gráfica:
Cada nodo do gráfico é un modelo; os bordos do gráfico están especificados pola expresión ref.
Calidade dos datos e documentación
Ademais de xerar os propios modelos, DBT permítelle probar unha serie de suposicións sobre o conxunto de datos resultante, como:
- Non nulo
- Único
- Integridade de referencia: integridade referencial (por exemplo, customer_id na táboa de pedidos corresponde ao id da táboa de clientes)
- Coincidir coa lista de valores aceptables
É posible engadir as súas propias probas (probas de datos personalizados), como, por exemplo, o % de desviación dos ingresos con indicadores desde hai un día, unha semana ou un mes. Calquera suposición formulada como unha consulta SQL pode converterse nunha proba.
Deste xeito, pode detectar desviacións e erros non desexados nos datos nas ventás do almacén.
En canto á documentación, DBT proporciona mecanismos para engadir, controlar e distribuír metadatos e comentarios nos niveis de modelo e mesmo de atributos.
Aquí tes como se engaden probas e documentación no nivel do ficheiro de configuració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']
E aquí está o aspecto desta documentación no sitio web xerado:
Macros e módulos
O propósito de DBT non é tanto converterse nun conxunto de scripts SQL, senón proporcionar aos usuarios un medio poderoso e rico en funcións para construír as súas propias transformacións e distribuír estes módulos.
As macros son conxuntos de construcións e expresións que se poden chamar funcións dentro dos modelos. As macros permítenche reutilizar SQL entre modelos e proxectos de acordo co principio de enxeñería DRY (Don't Repeat Yourself).
Exemplo 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 %}
E os seus usos:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT inclúe un xestor de paquetes que permite aos usuarios publicar e reutilizar módulos e macros individuais.
Isto significa poder cargar e usar bibliotecas como:
- : traballar con Data/Hora, Teclas substitutivas, Probas de esquema, Pivot/Unpivot e outros
- Modelos de presentación listos para servizos como и
- Bibliotecas para almacéns de datos específicos, p.
- — Módulo para rexistrar a operación DBT
Pódese atopar unha lista completa de paquetes en .
Aínda máis características
Aquí describirei algunhas outras características e implementacións interesantes que o equipo e eu usamos para construír un Data Warehouse en .
Separación de ambientes de execución DEV - TEST - PROD
Incluso dentro do mesmo clúster DWH (dentro de diferentes esquemas). Por exemplo, usando a seguinte expresión:
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 -%}
)
Este código di literalmente: para ambientes dev, proba, ci tomar datos só dos últimos 3 días e non máis. É dicir, correr nestes ambientes será moito máis rápido e requirirá menos recursos. Cando se executa en ambiente produción ignorarase a condición do filtro.
Materialización con codificación de columnas alternas
Redshift é un DBMS columnar que che permite establecer algoritmos de compresión de datos para cada columna individual. A selección de algoritmos óptimos pode reducir o espazo no disco nun 20-50%.
Macro executará o comando ANALYZE COMPRESSION, creará unha nova táboa cos algoritmos de codificación de columnas recomendados, chaves de segmentación especificadas (dist_key) e claves de clasificación (sort_key), transferirá os datos a ela e, se é necesario, eliminará a copia antiga.
Sinatura 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) }}
Execución do modelo de rexistro
Podes conectar ganchos a cada execución do modelo, que se executarán antes do lanzamento ou inmediatamente despois de que se complete a creación do modelo:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
O módulo de rexistro permítelle rexistrar todos os metadatos necesarios nunha táboa separada, que posteriormente pode utilizarse para auditar e analizar os pescozos de botella.
Este é o aspecto do panel baseado nos datos de rexistro en Looker:
Automatización do mantemento do almacenamento
Se usa algunhas extensións da funcionalidade do repositorio usado, como UDF (Funcións definidas polo usuario), entón a versión destas funcións, o control de acceso e a posta en marcha automatizada de novas versións son moi convenientes para facer en DBT.
Usamos UDF en Python para calcular hash, dominios de correo electrónico e decodificación de máscara de bits.
Un exemplo de macro que crea unha UDF en calquera ambiente de execución (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 %}
En Wheely usamos Amazon Redshift, que está baseado en PostgreSQL. Para Redshift, é importante recompilar regularmente estatísticas sobre táboas e liberar espazo no disco: os comandos ANALIZAR e VACUAR, respectivamente.
Para iso, execútanse todas as noites os comandos da 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 %}
Nube DBT
É posible utilizar DBT como servizo (Servizo Xestionado). Incluído:
- IDE web para desenvolver proxectos e modelos
- Configuración e programación do traballo
- Acceso sinxelo e cómodo aos rexistros
- Páxina web coa documentación do teu proxecto
- Conectando CI (integración continua)
Conclusión
Preparar e consumir DWH faise tan agradable e beneficioso como beber un batido. DBT consta de Jinja, extensións de usuario (módulos), un compilador, un executor e un xestor de paquetes. Ao xuntarse estes elementos, obtén un ambiente de traballo completo para o seu Data Warehouse. Non hai un xeito mellor de xestionar a transformación dentro de DWH hoxe.
As crenzas seguidas polos desenvolvedores de DBT formúlanse do seguinte xeito:
- O código, non a GUI, é a mellor abstracción para expresar lóxica analítica complexa
- Traballar con datos debe adaptar as mellores prácticas en enxeñaría de software (Enxeñaría de software)
- A infraestrutura de datos crítica debe ser controlada pola comunidade de usuarios como software de código aberto
- Non só as ferramentas de análise, senón tamén o código pasará a ser propiedade da comunidade de código aberto
Estas crenzas fundamentais xeraron un produto que son utilizados por máis de 850 empresas na actualidade e constitúen a base de moitas extensións interesantes que se crearán no futuro.
Para os interesados, hai un vídeo dunha lección aberta que dei hai uns meses como parte dunha lección aberta na OTUS - .
Ademais de DBT e Data Warehousing, como parte do curso de Data Engineer na plataforma OTUS, os meus compañeiros e eu impartimos clases sobre outros temas relevantes e modernos:
- Conceptos arquitectónicos para aplicacións de Big Data
- Practica con Spark e Spark Streaming
- Explorar métodos e ferramentas para cargar fontes de datos
- Construción de escaparates analíticos en DWH
- Conceptos NoSQL: HBase, Cassandra, ElasticSearch
- Principios de vixilancia e orquestración
- Proxecto Final: xuntar todas as habilidades baixo o apoio de titoría
Referencias:
- - Documentación oficial
- — Artigo de revisión dun dos autores de DBT
- — YouTube, gravación dunha lección aberta de OTUS
- - A próxima lección aberta será o 15 de maio de 2020
- - OTUS
- — Unha ollada ao futuro dos datos e da analítica
- — A evolución da analítica e a influencia do Open Source
- — Principios de construción de CI utilizando DBT
- — Práctica, instrucións paso a paso para o traballo independente
- — Github, código do proxecto educativo
Fonte: www.habr.com