На яких принципах будується ідеальне сховище даних?
Фокус на бізнес-цінності та аналітиці за відсутності boilerplate code. Управління DWH як кодовою базою: версіонування, ревью, автоматичне тестування та CI. Модульність, розширюваність, відкритий вихідний код та спільнота. Дружня користувальницька документація та візуалізація залежностей (Data Lineage).
Про все це докладніше і про роль DBT в екосистемі Big Data & Analytics – ласкаво просимо під кат.
Всім привіт
На зв'язку Артемій Козир. Вже понад 5 років я працюю зі сховищами даних, займаюся побудовою ETL/ELT, а також аналітикою даних та візуалізацією. В даний час я працюю в , викладаю в OTUS на курсі , і сьогодні хочу поділитися з вами статтею, яку я написав напередодні старту нового набору на курс.
Короткий огляд
Фреймворк DBT - це все про літеру T в акронімі ELT (Extract - Transform - Load).
З появою таких продуктивних та масштабованих аналітичних баз даних як BigQuery, Redshift, Snowflake, зник будь-який сенс робити трансформації поза Сховищем Даних.
DBT не вивантажує дані з джерел, але надає величезні можливості роботи з тими даними, які вже завантажені в Сховище (в Internal або External Storage).
Основне призначення DBT - взяти код, скомпілювати його в SQL, виконати команди у правильній послідовності в Сховищі.
Структура проекту DBT
Проект складається з директорій та файлів всього 2-х типів:
- Модель (.sql) – одиниця трансформації, виражена SELECT-запитом
- Файл конфігурації (.yml) – параметри, налаштування, тести, документація
На базовому рівні робота будується так:
- Користувач готує код моделей у будь-якій зручній IDE
- За допомогою CLI викликається запуск моделей, DBT компілює код моделей SQL
- Скомпільований SQL-код виконується в Сховищі в заданій послідовності (граф)
Ось як може виглядати запуск із CLI:
Все є SELECT
Це кілер-фіча фреймворку Data Build Tool. Іншими словами, DBT абстрагує весь код, пов'язаний із матеріалізацією ваших запитів у Сховищі (варіації із команд CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, …).
Будь-яка модель має на увазі написання одного SELECT-запиту, який визначає результуючий набір даних.
При цьому логіка перетворень може бути багаторівневою та консолідувати дані з кількох інших моделей. Приклад моделі, яка збудує вітрину замовлень (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
Що цікавого ми можемо побачити тут?
По-перше: Використані CTE (Common Table Expressions) — для організації та розуміння коду, що містить багато перетворень та бізнес-логіки
По-друге: Код моделі - це суміш SQL та мови (Templating language).
У прикладі використаний цикл та цінності для формування суми за кожним методом платежу, зазначеним у виразі комплект. Також використовується функція посилання - Можливість посилатися всередині коду на інші моделі:
- Під час компіляції посилання буде перетворено на цільовий покажчик на таблицю або подання до Сховища
- посилання дозволяє побудувати граф залежностей моделей
Саме додає DBT майже необмежені можливості. Найчастіше використовувані з них:
- If / else statements - оператори розгалуження
- For loops - цикли
- Variables - змінні
- Macro - створення макросів
Матеріалізація: Table, View, Incremental
Стратегія Матеріалізації – підхід, згідно з яким результуючий набір даних моделі буде збережено у Сховищі.
У базовому розгляді це:
- Table - фізична таблиця в Сховищі
- View — представлення, віртуальна таблиця у Сховищі
Є й складніші стратегії матеріалізації:
- Incremental - інкрементальне завантаження (великих таблиць фактів); нові рядки додаються, змінені – оновлюються, видалені – вичищаються
- Ephemeral - модель не матеріалізується безпосередньо, але бере участь як CTE в інших моделях
- Будь-які інші стратегії, які ви можете додати самостійно
На додаток до стратегій матеріалізації відкриваються можливості для оптимізації під конкретні Сховища, наприклад:
- Сніжинка: Transient tables, Merge behavior, Table clustering, Copying grants, Secure views
- Червоне зміщення: Distkey, Sortkey (interleaved, compound), Late Binding Views
- BigQuery: Table partitioning & clustering, Merge behavior, KMS Encryption, Labels & Tags
- Іскритися: File format (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
На даний момент підтримуються такі сховища:
- Постгрес
- Червоне зміщення
- BigQuery
- Сніжинка
- Presto (частково)
- Spark (частково)
- Microsoft SQL Server (ком'юніті адаптер)
Давайте вдосконалимо нашу модель:
- Зробимо її наповнення інкрементальним (Incremental)
- Додамо ключі сегментації та сортування для 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
Граф залежностей моделей
Він же є дерево залежностей. Він же DAG (Directed Acyclic Graph – Спрямований Ациклічний Граф).
DBT будує граф з урахуванням конфігурації всіх моделей проекту, а точніше посилань ref() всередині моделей інші моделі. Наявність графа дозволяє робити такі речі:
- Запуск моделей у коректній послідовності
- Паралелізація формування вітрин
- Запуск довільної підграфи
Приклад візуалізації графа:
Кожен вузол графа це модель, ребра графа задаються виразом ref.
Якість даних та Документація
Крім формування самих моделей, DBT дозволяє протестувати ряд припущень (assertions) про результуючий набір даних, таких як:
- Не Null
- Унікальний
- Reference Integrity — цілісна цілість (наприклад, customer_id у таблиці orders відповідає id у таблиці customers)
- Відповідність списку допустимих значень
Можливе додавання своїх тестів (custom data tests), таких як, наприклад, % відхилення виручки з показниками день, тиждень, місяць тому. Будь-яке припущення, сформульоване як SQL-запиту, може стати тестом.
Таким чином можна відловлювати у вітринах Сховища небажані відхилення та помилки у даних.
Щодо документування, DBT надає механізми для додавання, версіонування та розповсюдження метаданих та коментарів на рівні моделей і навіть атрибутів.
Ось як виглядає додавання тестів та документації на рівні конфігураційного файлу:
- 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']
А ось як ця документація виглядає вже на згенерованому веб-сайті:
Макроси та Модулі
Призначення DBT полягає не стільки в тому, щоб стати набором SQL-скриптів, але надати користувачам потужні та багаті можливості для побудови власних трансформацій та розповсюдження цих модулів.
Макроси – це набори конструкцій та виразів, які можуть бути викликані як функції усередині моделей. Макроси дозволяють перевикористовувати SQL між моделями та проектами відповідно до інженерного принципу DRY (Don't Repeat Yourself).
Приклад макросу:
{% 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 %}
І його використання:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT поставляється з менеджером пакетів (packages), який дозволяє користувачам публікувати та перевикористовувати окремі модулі та макроси.
Це означає можливість завантажити та використовувати такі бібліотеки як:
- : робота з Date/Time,Surrogate Keys, Schema tests, Pivot/Unpivot та інші
- Готові шаблони вітрин для таких сервісів як и
- Бібліотеки для певних Сховищ Даних, наприклад
- - Модуль для логування роботи DBT
З повним списком пакетів можна ознайомитись на .
Ще більше можливостей
Тут я опишу кілька інших цікавих особливостей та реалізацій, які я та команда використовуємо для побудови Сховища Даних у .
Розподіл середовищ виконання DEV - TEST - PROD
Навіть усередині одного кластера DWH (у межах різних схем). Наприклад, за допомогою наступного виразу:
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 -%}
)
Цей код буквально каже: для середовищ dev, test, ci Візьми дані тільки за останні 3 дні і не більше. Тобто прогін у цих середовищах буде набагато швидшим і вимагатиме менше ресурсів. Під час запуску на середовищі prod умова фільтра буде проігноровано.
Матеріалізація з альтернативним кодуванням стовпців
Redshift - колонкова СУБД, що дозволяє задавати алгоритми компресії даних кожної окремої колонки. Вибір оптимальних алгоритмів може скоротити об'єм на диску на 20-50%.
Макрос виконає команду ANALYZE COMPRESSION, створить нову таблицю з рекомендованими алгоритмами кодування стовпців, вказаними ключами сегментації (dist_key) та сортування (sort_key), перенесе до неї дані, і за потреби видалити стару копію.
Сигнатура макросу:
{{ 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) }}
Логування запусків моделей
На кожне виконання моделі можна повісити хуки, які будуть виконуватися до запуску або відразу після закінчення створення моделі:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Модуль логування дозволить записувати всі необхідні метадані в окрему таблицю, за якою можна проводити аудит і аналіз проблемних місць (bottlenecks).
Ось як виглядає дашборд на даних логування в Looker:
Автоматизація обслуговування Сховища
Якщо ви використовуєте якісь розширення функціоналу використовуваного Сховища, такі як UDF (User Defined Functions), то версіонування цих функцій, управління доступами та автоматизовану викочування нових релізів дуже зручно здійснювати в DBT.
Ми використовуємо UDF на Python для розрахунку хеш-значень, доменів поштових адрес, декодування бітових масок (bitmask).
Приклад макросу, який створює UDF на будь-якому середовищі виконання (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 %}
У Wheely ми використовуємо Amazon Redshift, який базується на PostgreSQL. Для Redshift важливо регулярно збирати статистики за таблицями та вивільняти місце на диску – команди ANALYZE та VACUUM, відповідно.
Для цього щоночі виконуються команди з макросу 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 Cloud
Є можливість користуватись DBT як сервісом (Managed Service). В комплекті:
- Web IDE для розробки проектів та моделей
- Конфігурація джобів та встановлення на розклад
- Простий та зручний доступ до логів
- Веб Сайт із документацією вашого проекту
- Підключення CI (Continuous Integration)
Висновок
Готувати та вживати DWH стає так само приємно та благотворно, як і пити смузі. DBT складається з Jinja, користувацьких розширень (модулів), компілятора, движка (executor) та менеджера пакетів. Зібравши ці елементи докупи ви отримуєте повноцінне робоче оточення для вашого Сховища Даних. Чи сьогодні є найкращий спосіб управління трансформаціями всередині DWH.
Переконання, яким слідували розробники DBT, формулюються так:
- Код, а не GUI, є найкращою абстракцією для вираження складної аналітичної логіки
- Робота з даними повинна адаптувати найкращі практики розробки програмного забезпечення (Software Engineering)
- Найважливіша інфраструктура роботи з даними повинна контролюватись спільнотою користувачів як програмне забезпечення з відкритим вихідним кодом
- Не тільки інструменти аналітики, але й код все частіше ставатиме надбанням спільноти Open Source
Ці основні переконання породили продукт, який сьогодні використовується у понад 850 компаніях, і вони становлять основу багатьох цікавих розширень, які будуть створені в майбутньому.
Для тих, хто зацікавився, є відеозапис відкритого уроку, який я провів кілька місяців тому в рамках відкритого уроку в OTUS. .
Крім DBT та Сховищ Даних, в рамках курсу Data Engineer на платформі OTUS, я та мої колеги ведемо заняття з низки інших актуальних та сучасних тем:
- Архітектурні концепції додатків Великих Даних
- Практика зі Spark та Spark Streaming
- Вивчення способів та інструментів завантаження джерел даних
- Побудова аналітичних вітрин у DWH
- Концепції NoSQL: HBase, Cassandra, ElasticSearch
- Принципи організації моніторингу та оркестрації
- Фінальний Проект: збираємо всі скіли воєдино під менторською підтримкою
Посилання:
- - Офіційна документація
- - Оглядова стаття одного з авторів DBT
- - YouTube, Запис відкритого уроку OTUS
- — Найближчий відкритий урок 15 травня 2020
- - OTUS
- — Погляд на майбутнє роботи з даними та аналітику
- — Еволюція аналітики та вплив Open Source
- - Принципи побудова CI з використанням DBT
- - Практика, Покрокові інструкції для самостійної роботи
- - Github, код навчального проекту
Джерело: habr.com