На какви принципи е изграден идеалният Data Warehouse?

Фокусирайте се върху бизнес стойността и анализите при липсата на шаблонен код. Управление на DWH като кодова база: версии, преглед, автоматизирано тестване и CI. Модулност, разширяемост, отворен код и общност. Удобна за потребителя документация и визуализация на зависимости (Data Lineage).

За всичко това по-подробно и за ролята на DBT в екосистемата Big Data & Analytics – добре дошли под кат.

Здравейте

Артеми Козир е във връзка. Повече от 5 години работя със складове за данни, изграждане на ETL / ELT, както и с анализ и визуализация на данни. В момента работя в Wheely, аз преподавам в OTUS на курса Инженер на данни, а днес искам да споделя с вас една статия, която написах в навечерието на старта ново записване за курса.

резюме

DBT рамката е свързана с буквата T в акронима ELT (Extract - Transform - Load).

С появата на такива продуктивни и мащабируеми аналитични бази данни като BigQuery, Redshift, Snowflake, нямаше смисъл да се правят трансформации извън Data Warehouse. 

DBT не качва данни от източници, но предоставя големи възможности за работа с данни, които вече са качени в хранилището (вътрешно или външно хранилище).

Основната цел на 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 и език Jinja (език за шаблони).

Примерът използва цикъл за за генериране на сумата за всеки метод на плащане, посочен в израза определен. Използва се и функцията Ref - възможност за препращане в кода към други модели:

• По време на компилиране Ref ще бъдат преобразувани в целеви указател към таблица или изглед в хранилището
• Ref ви позволява да изградите графика на зависимост от модели

Точно Jinja добавя почти неограничени възможности към DBT. Най-често използваните са:

• If / else оператори - разклонителни оператори
• за бримки - бримки
• променливи
• Макро - създаване на макроси

Материализация: Таблица, Изглед, Инкрементално

Стратегията за материализиране е подход, според който полученият набор от данни на модела ще се съхранява в хранилището.

В основни линии това е:

• Таблица - физическа таблица в хранилището
• View - изглед, виртуална таблица в Storage

Има и по-сложни стратегии за материализиране:

• Incremental - постепенно зареждане (големи фактологични таблици); нови редове се добавят, променените се актуализират, изтритите се изчистват 
• Ефемерен – моделът не се материализира директно, а участва като CTE в други модели
• Всички други стратегии, които можете да добавите сами

В допълнение към стратегиите за материализиране, има възможности за оптимизация за конкретни хранилища, например:

• Снежинка: Преходни таблици, поведение при сливане, клъстериране на таблици, безвъзмездни средства за копиране, защитени изгледи
• Redshift: Diskey, Sorkey (с вмъкване, съставен), Late Binding Views
• BigQuery: Разделяне на таблици и групиране, поведение при сливане, KMS криптиране, етикети и етикети
• Искра: Файлов формат (паркет, csv, json, orc, делта), partition_by, clustered_by, кофи, incremental_strategy

В момента се поддържат следните хранилища:

• Postgres
• Redshift
• BigQuery
• Снежинка
• Престо (частично)
• Искра (частична)
• Microsoft SQL Server (адаптер на общността)

Нека подобрим нашия модел:

• Нека направим пълненето му постепенно (инкрементално)
• Добавяне на ключове за сегментиране и сортиране за 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 ви позволява да тествате редица предположения (твърдения) относно получения набор от данни, като например:

• Не е нула
• Уникална
• Референтна цялост - референтна цялост (например customer_id в таблицата с поръчки съответства на id в таблицата на клиентите)
• Съвпадение на списък с валидни стойности

Възможно е да добавите свои собствени тестове (тестове за персонализирани данни), като например % отклонение на приходите с показатели за ден, седмица, месец назад. Всяко предположение, формулирано като 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 (не повтаряйте себе си).

Пример за макрос:

{% 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 идва с мениджър на пакети, който позволява на потребителите да публикуват и използват повторно отделни модули и макроси.

Това означава възможност за изтегляне и използване на библиотеки като:

• dbt_utils: работа с дата/час, сурогатни ключове, тестове на схеми, Pivot/Unpivot и други
• Готови витрини шаблони за услуги като снегорин и Нашивка 
• Например библиотеки за конкретни хранилища за данни Redshift 
• Влизане - Модул за регистриране на работата на DBT

Пълен списък с пакети можете да намерите на dbt хъб.

Още повече функции

Тук ще опиша няколко други интересни функции и реализации, които аз и екипът използваме за изграждане на Data Warehouse Wheely.

Разделяне на средите за изпълнение 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 дни и не повече. Тоест работата в тези среди ще бъде много по-бърза и ще изисква по-малко ресурси. Когато работите върху околната среда ръгане условието на филтъра ще бъде игнорирано.

Материализация с алтернативно кодиране на колони

Redshift е колонна СУБД, която ви позволява да задавате алгоритми за компресиране на данни за всяка отделна колона. Изборът на оптимални алгоритми може да намали заеманото дисково пространство с 20-50%.

макрос redshift.compress_table ще изпълни командата 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() }}"

Модулът за регистриране ще ви позволи да запишете всички необходими метаданни в отделна таблица, според която по-късно можете да одитирате и анализирате проблемните области (тесни места).

Ето как изглежда таблото за регистриране на данните в Looker:

Автоматизация на съхранението

Ако използвате някои разширения на функционалността на използваното хранилище, като UDF (дефинирани от потребителя функции), тогава версията на тези функции, контролът на достъпа и автоматизираното внедряване на нови версии е много удобно за извършване в DBT.

Ние използваме Python UDF за изчисляване на хеш стойности, домейни на имейл адреси, декодиране на битова маска.

Пример за макрос, който създава 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 облак

Възможно е да използвате DBT като услуга (Управлявана услуга). Включено:

• Web IDE за разработване на проекти и модели
• Конфигуриране на работа и настройка на график
• Лесен и удобен достъп до дневници
• Уеб сайт с документация на вашия проект
• Свързване на CI (непрекъснато интегриране)

Заключение

Приготвянето и консумацията на DWH става толкова приятно и здравословно, колкото пиенето на смути. DBT се състои от Jinja, персонализирани разширения (модули), компилатор, двигател (изпълнител) и мениджър на пакети. Събирайки тези елементи заедно, вие получавате цялостна работна среда за вашия Data Warehouse. Едва ли има по-добър начин за управление на трансформациите в DWH днес.

Убежденията, следвани от разработчиците на DBT, са формулирани, както следва:

• Кодът, а не GUI, е най-добрата абстракция за изразяване на сложна аналитична логика
• Работата с данни трябва да адаптира най-добрите практики на софтуерното инженерство (Софтуерно инженерство)

• Инфраструктурата с критични данни трябва да се контролира от потребителската общност като софтуер с отворен код
• Не само инструментите за анализ, но и кодът все повече ще става собственост на общността с отворен код

Тези основни вярвания са довели до продукт, който се използва от повече от 850 компании днес, и те формират основата на много вълнуващи разширения, които ще бъдат създадени в бъдеще.

За тези, които се интересуват, има видео запис на открит урок, който проведох преди няколко месеца като част от открит урок в OTUS - Инструмент за изграждане на данни за Amazon Redshift Storage.

В допълнение към DBT и Data Warehouses, като част от курса Data Engineer на платформата OTUS, моите колеги и аз провеждаме уроци по редица други подходящи и модерни теми:

• Архитектурни концепции за приложения с големи данни
• Упражнявайте се със Spark и Spark Streaming
• Научаване на начини и инструменти за зареждане на източници на данни
• Изграждане на аналитични витрини в DWH
• NoSQL концепции: HBase, Cassandra, ElasticSearch
• Принципи на организация на мониторинга и оркестрацията 
• Окончателен проект: обединяване на всички умения под менторска подкрепа

за справка:

1. DBT документация — Официална документация
2. Какво точно е dbt? — Рецензия на един от авторите на DBT 
3. Инструмент за изграждане на данни за Amazon Redshift Storage — YouTube, OTUS Open Lesson Recording
4. Въведение в Greenplum — Най-близкият открит урок е 15 май 2020 г
5. Курс по инженерство на данни — ОТУС
6. Изграждане на зрял работен процес за анализ — Поглед към бъдещето на данните и анализите
7. Време е за анализи с отворен код — Еволюцията на анализа и въздействието на отворения код
8. Непрекъснато интегриране и автоматизирано тестване на компилация с dbtCloud — Принципи на изграждане на CI с помощта на DBT
9. Първи стъпки с урока за DBT — Практика, Инструкции стъпка по стъпка за самообучение
10. магазин за jaffle - Урок за Github DBT — Github, код за проекта за обучение

Научете повече за курса.

Източник: www.habr.com