¿Sobre qué principios se construye un Data Warehouse ideal?
Céntrese en el valor empresarial y el análisis en ausencia de un código repetitivo. Gestión de DWH como base de código: control de versiones, revisión, pruebas automatizadas y CI. Modular, extensible, de código abierto y comunitario. Documentación fácil de usar y visualización de dependencias (Data Lineage).
Más sobre todo esto y sobre el papel de DBT en el ecosistema de Big Data & Analytics: bienvenido al cat.
¡Hola
Artemy Kozyr está en contacto. Durante más de 5 años he trabajado con almacenes de datos, construyendo ETL/ELT, así como análisis y visualización de datos. Actualmente estoy trabajando en , doy clases en OTUS en un curso , y hoy quiero compartir con ustedes un artículo que escribí anticipándome al inicio. nueva inscripción al curso.
Visión general
El marco DBT tiene que ver con la T en el acrónimo ELT (Extract - Transform - Load).
Con la llegada de bases de datos analíticas tan productivas y escalables como BigQuery, Redshift, Snowflake, no tenía sentido realizar transformaciones fuera del Data Warehouse.
DBT no descarga datos de fuentes, pero brinda grandes oportunidades para trabajar con datos que ya se han cargado en el Almacenamiento (en Almacenamiento Interno o Externo).
El objetivo principal de DBT es tomar el código, compilarlo en SQL y ejecutar los comandos en la secuencia correcta en el repositorio.
Estructura del proyecto DBT
El proyecto consta de directorios y archivos de solo 2 tipos:
- Modelo (.sql): una unidad de transformación expresada por una consulta SELECT
- Archivo de configuración (.yml): parámetros, configuraciones, pruebas, documentación
A nivel básico el trabajo se estructura de la siguiente manera:
- El usuario prepara el código del modelo en cualquier IDE conveniente.
- Usando la CLI, se lanzan los modelos, DBT compila el código del modelo en SQL
- El código SQL compilado se ejecuta en el Almacenamiento en una secuencia determinada (gráfico)
Así es como se vería ejecutar desde la CLI:
Todo es SELECCIONAR
Esta es una característica excelente del marco de la herramienta de compilación de datos. En otras palabras, DBT abstrae todo el código asociado con la materialización de sus consultas en la Tienda (variaciones de los comandos CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ...).
Cualquier modelo implica escribir una consulta SELECT que define el conjunto de datos resultante.
En este caso, la lógica de transformación puede ser multinivel y consolidar datos de varios otros modelos. Un ejemplo de un modelo que construirá 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
¿Qué cosas interesantes podemos ver aquí?
Primero: CTE usado (Expresiones de tabla comunes): para organizar y comprender el código que contiene muchas transformaciones y lógica empresarial.
Segundo: el código modelo es una mezcla de SQL y lenguaje (lenguaje de plantilla).
El ejemplo utiliza un bucle. para para generar el monto para cada método de pago especificado en la expresión set. La función también se utiliza ref. — la capacidad de hacer referencia a otros modelos dentro del código:
- Durante la compilación ref. se convertirá en un puntero de destino a una tabla o vista en Almacenamiento
- ref. le permite construir un gráfico de dependencia del modelo
Exactamente añade posibilidades casi ilimitadas a DBT. Los más utilizados son:
- Declaraciones if / else - declaraciones de rama
- Para bucles
- variables
- Macro: crear macros
Materialización: Tabla, Vista, Incremental
La estrategia de materialización es un enfoque según el cual el conjunto resultante de datos del modelo se almacenará en el Almacenamiento.
En términos básicos es:
- Tabla - tabla física en el Almacenamiento
- Ver - ver, tabla virtual en Almacenamiento
También existen estrategias de materialización más complejas:
- Incremental: carga incremental (de tablas de hechos grandes); se agregan nuevas líneas, las líneas modificadas se actualizan, las líneas eliminadas se borran
- Efímero - el modelo no se materializa directamente, sino que participa como CTE en otros modelos
- Cualquier otra estrategia que puedas agregar tú mismo.
Además de las estrategias de materialización, existen oportunidades de optimización para Storages específicos, por ejemplo:
- Copo de nieve: Tablas transitorias, Comportamiento de combinación, Agrupación de tablas, Concesiones de copia, Vistas seguras
- Desplazamiento hacia el rojo: Distkey, Sortkey (intercalado, compuesto), vistas de enlace tardío
- BigQuery: Partición y agrupación de tablas, comportamiento de combinación, cifrado KMS, etiquetas y rótulos
- Spark: Formato de archivo (parquet, csv, json, orc, delta), partición_por, clustered_by, cubos, estrategia_incremental
Actualmente se admiten los siguientes almacenamientos:
- Postgres
- Desplazamiento hacia el rojo
- BigQuery
- Copo de nieve
- Listo (parcialmente)
- Chispa (parcialmente)
- Microsoft SQL Server (adaptador comunitario)
Mejoremos nuestro modelo:
- Hagamos que su llenado sea incremental (Incremental)
- Agreguemos claves de segmentación y 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áfico de dependencia del modelo
También es un árbol de dependencia. También se le conoce como DAG (Gráfico Acíclico Dirigido).
DBT crea un gráfico basado en la configuración de todos los modelos del proyecto, o más bien, enlaces ref() dentro de los modelos a otros modelos. Tener un gráfico le permite hacer las siguientes cosas:
- Ejecutar modelos en la secuencia correcta
- Paralelización de la formación del escaparate.
- Ejecutando un subgrafo arbitrario
Ejemplo de visualización de gráficos:
Cada nodo del gráfico es un modelo; los bordes del gráfico están especificados por la expresión ref.
Calidad de datos y documentación
Además de generar los modelos en sí, DBT le permite probar una serie de suposiciones sobre el conjunto de datos resultante, como por ejemplo:
- No nulo
- Unique
- Integridad de referencia: integridad referencial (por ejemplo, customer_id en la tabla de pedidos corresponde a id en la tabla de clientes)
- Coincidir con la lista de valores aceptables
Es posible agregar sus propias pruebas (pruebas de datos personalizadas), como, por ejemplo, el porcentaje de desviación de ingresos con indicadores de hace un día, una semana o un mes. Cualquier suposición formulada como una consulta SQL puede convertirse en una prueba.
De esta manera, puede detectar desviaciones y errores no deseados en los datos de las ventanas del Almacén.
En términos de documentación, DBT proporciona mecanismos para agregar, versionar y distribuir metadatos y comentarios a nivel de modelo e incluso de atributo.
Así es como se ve agregar pruebas y documentación en el nivel del archivo 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']
Y así es como se ve esta documentación en el sitio web generado:
Macros y módulos
El propósito de DBT no es tanto convertirse en un conjunto de scripts SQL, sino proporcionar a los usuarios un medio potente y rico en funciones para construir sus propias transformaciones y distribuir estos módulos.
Las macros son conjuntos de construcciones y expresiones que se pueden llamar funciones dentro de los modelos. Las macros le permiten reutilizar SQL entre modelos y proyectos de acuerdo con el principio de ingeniería DRY (Don't Repite Yourself).
Ejemplo de macros:
{% 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 %}
Y su uso:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT viene con un administrador de paquetes que permite a los usuarios publicar y reutilizar módulos y macros individuales.
Esto significa poder cargar y utilizar bibliotecas como:
- : trabajar con fecha/hora, claves sustitutas, pruebas de esquema, pivote/despivote y otros
- Plantillas de escaparate listas para usar para servicios como и
- Bibliotecas para almacenes de datos específicos, p.
- — Módulo para registrar la operación DBT
Puede encontrar una lista completa de paquetes en .
Aún más características
Aquí describiré algunas otras características e implementaciones interesantes que el equipo y yo utilizamos para construir un almacén de datos en .
Separación de entornos de ejecución DEV - TEST - PROD
Incluso dentro del mismo cluster DWH (dentro de diferentes esquemas). Por ejemplo, usando la siguiente 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 dice literalmente: para entornos desarrollo, prueba, ci tome datos solo de los últimos 3 días y no más. Es decir, correr en estos entornos será mucho más rápido y requerirá menos recursos. Cuando se ejecuta en el entorno pinchar la condición del filtro será ignorada.
Materialización con codificación de columna alternativa
Redshift es un DBMS de columnas que le permite configurar algoritmos de compresión de datos para cada columna individual. La selección de algoritmos óptimos puede reducir el espacio en disco entre un 20% y un 50%.
Macro ejecutará el comando ANALYZE COMPRESSION, creará una nueva tabla con los algoritmos de codificación de columnas recomendados, claves de segmentación especificadas (dist_key) y claves de clasificación (sort_key), le transferirá los datos y, si es necesario, eliminará la copia anterior.
Firma de 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) }}
Ejecuciones del modelo de registro
Puede adjuntar ganchos a cada ejecución del modelo, que se ejecutará antes del lanzamiento o inmediatamente después de que se complete la creación del modelo:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
El módulo de registro le permitirá registrar todos los metadatos necesarios en una tabla separada, que posteriormente podrá usarse para auditar y analizar cuellos de botella.
Así es como se ve el panel según los datos de registro en Looker:
Automatización del Mantenimiento del Almacenamiento
Si utiliza algunas extensiones de la funcionalidad del Repositorio utilizado, como UDF (Funciones definidas por el usuario), entonces es muy conveniente realizar en DBT el control de versiones de estas funciones, el control de acceso y la implementación automatizada de nuevas versiones.
Usamos UDF en Python para calcular hashes, dominios de correo electrónico y decodificación de máscaras de bits.
Un ejemplo de una macro que crea una UDF en cualquier entorno de ejecució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 utilizamos Amazon Redshift, que está basado en PostgreSQL. Para Redshift, es importante recopilar periódicamente estadísticas sobre las tablas y liberar espacio en el disco: los comandos ANALIZAR y VACÍO, respectivamente.
Para ello, cada noche se ejecutan los comandos de la 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
Es posible utilizar DBT como servicio (Servicio Gestionado). Incluido:
- Web IDE para desarrollar proyectos y modelos.
- Configuración y programación de trabajos.
- Acceso simple y conveniente a los registros.
- Sitio web con documentación de su proyecto
- Conexión de CI (integración continua)
Conclusión
Preparar y consumir DWH se vuelve tan placentero y beneficioso como beber un batido. DBT consta de Jinja, extensiones de usuario (módulos), un compilador, un ejecutor y un administrador de paquetes. Al reunir estos elementos, obtienes un entorno de trabajo completo para tu Data Warehouse. Hoy en día, difícilmente existe una mejor manera de gestionar la transformación dentro de DWH.
Las creencias seguidas por los desarrolladores de DBT se formulan de la siguiente manera:
- El código, no la GUI, es la mejor abstracción para expresar una lógica analítica compleja
- Trabajar con datos debe adaptar las mejores prácticas en ingeniería de software (Ingeniería de software)
- La comunidad de usuarios debe controlar la infraestructura de datos críticos como software de código abierto.
- No sólo las herramientas de análisis, sino también el código serán cada vez más propiedad de la comunidad de código abierto.
Estas creencias fundamentales han generado un producto que utilizan más de 850 empresas en la actualidad y forman la base de muchas extensiones interesantes que se crearán en el futuro.
Para aquellos interesados, hay un video de una lección abierta que di hace unos meses como parte de una lección abierta en OTUS: .
Además de DBT y almacenamiento de datos, como parte del curso de ingeniero de datos en la plataforma OTUS, mis colegas y yo impartimos clases sobre otros temas relevantes y modernos:
- Conceptos arquitectónicos para aplicaciones de Big Data
- Practica con Spark y Spark Streaming
- Explorando métodos y herramientas para cargar fuentes de datos
- Construyendo vitrinas analíticas en DWH
- Conceptos NoSQL: HBase, Cassandra, ElasticSearch
- Principios de monitoreo y orquestación.
- Proyecto final: reunir todas las habilidades bajo el apoyo de una tutoría
Enlaces:
- — Documentación oficial
- — Artículo de revisión de uno de los autores de DBT
- — YouTube, grabación de una lección abierta de OTUS
- — La próxima lección abierta es el 15 de mayo de 2020.
- —OTUS
- — Una mirada al futuro de los datos y el análisis
- — La evolución de la analítica y la influencia del Open Source
- — Principios de construcción de CI utilizando DBT
- — Práctica, Instrucciones paso a paso para el trabajo independiente.
- — Github, código de proyecto educativo
Fuente: habr.com