На којим принципима је изграђено идеално складиште података?
Фокусирајте се на пословну вредност и аналитику у одсуству стандардног кода. Управљање ДВХ-ом као базом кода: управљање верзијама, преглед, аутоматизовано тестирање и ЦИ. Модуларно, прошириво, отвореног кода и заједница. Документација прилагођена кориснику и визуелизација зависности (Дата Линеаге).
Више о свему овоме и о улози ДБТ-а у екосистему Биг Дата & Аналитицс - добродошли у кат.
Всем привет
Артеми Козир је у контакту. Више од 5 година радим са складиштима података, изградњом ЕТЛ/ЕЛТ-а, као и аналитиком и визуализацијом података. Тренутно радим у , предајем на ОТУС-у на курсу , а данас желим да поделим са вама чланак који сам написао у ишчекивању почетка нови упис за курс.
Преглед
ДБТ оквир се односи на Т у акрониму ЕЛТ (Ектрацт - Трансформ - Лоад).
Са појавом таквих продуктивних и скалабилних аналитичких база података као што су БигКуери, Редсхифт, Сновфлаке, није било смисла радити трансформације ван складишта података.
ДБТ не преузима податке из извора, али пружа велике могућности за рад са подацима који су већ учитани у Складиште (у интерној или екстерној меморији).
Главна сврха ДБТ-а је да узме код, преведе га у СКЛ, изврши команде у исправном редоследу у Репозиторијуму.
Структура ДБТ пројекта
Пројекат се састоји од директоријума и датотека само 2 типа:
- Модел (.скл) - јединица трансформације изражена упитом СЕЛЕЦТ
- Конфигурациони фајл (.имл) - параметри, подешавања, тестови, документација
На основном нивоу, рад је структуриран на следећи начин:
- Корисник припрема код модела у било ком прикладном ИДЕ-у
- Користећи ЦЛИ, модели се покрећу, ДБТ компајлира код модела у СКЛ
- Преведени СКЛ код се извршава у Складишту у датом низу (графикон)
Ево како би покретање из ЦЛИ-а могло изгледати:
Све је СЕЛЕЦТ
Ово је убилачка карактеристика оквира алата за прављење података. Другим речима, ДБТ апстрахује сав код повезан са материјализацијом ваших упита у Сторе (варијације наредби ЦРЕАТЕ, ИНСЕРТ, УПДАТЕ, ДЕЛЕТЕ АЛТЕР, ГРАНТ, ...).
Сваки модел укључује писање једног СЕЛЕЦТ упита који дефинише резултујући скуп података.
У овом случају, логика трансформације може бити вишеслојна и консолидовати податке из неколико других модела. Пример модела који ће изградити излог поруџбине (ф_ордерс):
{% 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
Које занимљиве ствари можемо видети овде?
Прво: Користи се ЦТЕ (Цоммон Табле Екпрессионс) - за организовање и разумевање кода који садржи много трансформација и пословне логике
Друго: код модела је мешавина СКЛ-а и језика (језик шаблона).
Пример користи петљу за да генеришете износ за сваки начин плаћања наведен у изразу сет. Функција се такође користи Реф — могућност референцирања других модела унутар кода:
- Током компилације Реф биће конвертовано у циљни показивач на табелу или приказ у складишту
- Реф омогућава вам да направите графикон зависности модела
Тачно додаје скоро неограничене могућности ДБТ-у. Најчешће коришћени су:
- Иф / елсе искази - наредбе гранања
- За петље
- Променљиве
- Макро - креирање макроа
Материјализација: табела, поглед, инкрементално
Стратегија материјализације је приступ према коме ће резултујући скуп података модела бити ускладиштен у Складишту.
У основи то је:
- Табела - физичка табела у Складишту
- Поглед - приказ, виртуелна табела у Складишту
Постоје и сложеније стратегије материјализације:
- Инкрементално - инкрементално учитавање (великих табела чињеница); нове линије се додају, измењене линије се ажурирају, избрисане линије се бришу
- Ефемеран – модел се не материјализује директно, већ учествује као ЦТЕ у другим моделима
- Све друге стратегије које можете сами да додате
Поред стратегија материјализације, постоје могућности за оптимизацију за одређена складишта, на пример:
- Пахуљица: прелазне табеле, понашање спајања, груписање табела, одобрења за копирање, безбедни прикази
- РедСхифт: Дискеи, Сорткеи (преплетени, сложени), Лате Биндинг Виевс
- БигКуери: Партиционисање и груписање табела, понашање спајања, КМС шифровање, ознаке и ознаке
- Варница: Формат датотеке (паркет, цсв, јсон, орц, делта), партитион_би, цлустеред_би, буцкетс, инцрементал_стратеги
Тренутно су подржана следећа складишта:
- Постгрес
- РедСхифт
- БигКуери
- Пахуљица
- Престо (делимично)
- варница (делимично)
- Мицрософт СКЛ Сервер (адаптер за заједницу)
Хајде да побољшамо наш модел:
- Направимо његово пуњење инкременталним (инкременталним)
- Хајде да додамо кључеве за сегментацију и сортирање за Редсхифт
-- Конфигурация модели:
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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
График зависности модела
То је такође стабло зависности. Такође је познат као ДАГ (Дирецтед Ацицлиц Грапх).
ДБТ гради графикон на основу конфигурације свих модела пројекта, односно реф() веза унутар модела са другим моделима. Поседовање графикона вам омогућава да урадите следеће ствари:
- Покретање модела у исправном редоследу
- Паралелизација формирања излога
- Покретање произвољног подграфа
Пример визуелизације графикона:
Сваки чвор графа је модел; ивице графа су одређене изразом реф.
Квалитет података и документација
Поред генерисања самих модела, ДБТ вам омогућава да тестирате бројне претпоставке о резултујућем скупу података, као што су:
- Не Нулл
- Јединствен
- Референтни интегритет – референтни интегритет (на пример, цустомер_ид у табели налога одговара ИД-у у табели купаца)
- Поклапање са листом прихватљивих вредности
Могуће је додати сопствене тестове (тестове прилагођених података), као што је, на пример, % одступања прихода са индикаторима од пре дан, недељу, месец. Свака претпоставка формулисана као СКЛ упит може постати тест.
На овај начин можете ухватити нежељена одступања и грешке у подацима у прозорима складишта.
Што се тиче документације, ДБТ обезбеђује механизме за додавање, верзионисање и дистрибуцију метаподатака и коментара на нивоу модела, па чак и атрибута.
Ево како изгледа додавање тестова и документације на нивоу конфигурационе датотеке:
- 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']
А ево како ова документација изгледа на генерисаној веб страници:
Макрои и модули
Сврха ДБТ-а није толико да постане скуп СКЛ скрипти, већ да корисницима пружи моћна средства богата функцијама за изградњу сопствених трансформација и дистрибуцију ових модула.
Макрои су скупови конструкција и израза који се могу позвати као функције унутар модела. Макрои вам омогућавају да поново користите СКЛ између модела и пројеката у складу са ДРИ (Дон'т Репеат Иоурселф) инжењерским принципом.
Пример макроа:
{% 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
ДБТ долази са менаџером пакета који омогућава корисницима да објављују и поново користе појединачне модуле и макрое.
То значи да можете да учитате и користите библиотеке као што су:
- : рад са датумом/време, сурогат кључевима, тестовима шеме, заокретом/поништењем и другим
- Готови шаблони витрина за услуге као што су и
- Библиотеке за одређена складишта података, нпр.
- — Модул за евидентирање ДБТ рада
Комплетну листу пакета можете пронаћи на .
Још више карактеристика
Овде ћу описати неколико других занимљивих карактеристика и имплементација које тим и ја користимо за изградњу складишта података у .
Раздвајање рунтиме окружења ДЕВ - ТЕСТ - ПРОД
Чак и унутар истог ДВХ кластера (у оквиру различитих шема). На пример, користећи следећи израз:
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 -%}
)
Овај код дословно каже: за окружења дев, тест, ци узимајте податке само за последња 3 дана и не више. То јест, трчање у овим окружењима ће бити много брже и захтеваће мање ресурса. Када ради на окружењу прод услов филтера ће бити занемарен.
Материјализација са алтернативним кодирањем колона
Редсхифт је стубни ДБМС који вам омогућава да поставите алгоритме компресије података за сваку појединачну колону. Избор оптималних алгоритама може смањити простор на диску за 20-50%.
Макро ће извршити команду АНАЛИЗЕ ЦОМПРЕССИОН, креирати нову табелу са препорученим алгоритмима за кодирање колона, наведеним кључевима за сегментацију (дист_кеи) и кључевима за сортирање (сорт_кеи), пренети податке у њу и, ако је потребно, избрисати стару копију.
Макро потпис:
{{ 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() }}"
Модул за евидентирање ће вам омогућити да снимите све потребне метаподатке у посебну табелу, која се касније може користити за ревизију и анализу уских грла.
Овако изгледа контролна табла на основу података евидентирања у Лоокер-у:
Аутоматизација одржавања складишта
Ако користите нека проширења функционалности коришћеног Репозиторија, као што је УДФ (функције које дефинише корисник), онда је верзионисање ових функција, контрола приступа и аутоматизовано увођење нових издања веома згодно за ДБТ.
Користимо УДФ у Питхон-у за израчунавање хешева, домена е-поште и декодирања битмаске.
Пример макроа који креира УДФ у било ком окружењу за извршавање (дев, тест, прод):
{% 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 %}
У Вхеели-ју користимо Амазон Редсхифт, који је заснован на ПостгреСКЛ-у. За Редсхифт је важно редовно прикупљати статистику о табелама и ослобађати простор на диску – команде АНАЛИЗА и ВАЦУУМ, респективно.
Да бисте то урадили, наредбе из макроа редсхифт_маинтенанце се извршавају сваке ноћи:
{% 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 %}
ДБТ Цлоуд
Могуће је користити ДБТ као услугу (Манагед Сервице). Укључено:
- Веб ИДЕ за развој пројеката и модела
- Конфигурација посла и заказивање
- Једноставан и згодан приступ дневникима
- Сајт са документацијом вашег пројекта
- Повезивање ЦИ (континуирана интеграција)
Закључак
Припрема и конзумирање ДВХ постаје једнако пријатно и корисно као испијање смутија. ДБТ се састоји од Јиња, корисничких екстензија (модула), компајлера, извршиоца и менаџера пакета. Састављањем ових елемената добијате комплетно радно окружење за ваше складиште података. Тешко да данас постоји бољи начин да се управља трансформацијом унутар ДВХ-а.
Веровања која следе програмери ДБТ-а су формулисана на следећи начин:
- Код, а не ГУИ, је најбоља апстракција за изражавање сложене аналитичке логике
- Рад са подацима треба да прилагоди најбоље праксе у софтверском инжењерству (Софтверски инжењеринг)
- Критичну инфраструктуру података треба да контролише корисничка заједница као софтвер отвореног кода
- Не само алати за анализу, већ и код ће све више постати власништво заједнице отвореног кода
Ова основна уверења су изнедрила производ који данас користи преко 850 компанија, и они чине основу многих узбудљивих проширења која ће бити креирана у будућности.
За заинтересоване, ту је снимак отворене лекције коју сам одржао пре неколико месеци у оквиру отвореног часа у ОТУС-у - .
Поред ДБТ-а и складиштења података, у оквиру курса Дата Енгинеер на ОТУС платформи, моје колеге и ја држимо часове на низ других релевантних и савремених тема:
- Архитектонски концепти за апликације великих података
- Вежбајте уз Спарк и Спарк Стреаминг
- Истраживање метода и алата за учитавање извора података
- Изградња аналитичких витрина у ДВХ
- НоСКЛ концепти: ХБасе, Цассандра, ЕластицСеарцх
- Принципи праћења и оркестрације
- Финални пројекат: спајање свих вештина уз менторску подршку
Референце:
- — Службена документација
- — Прегледни чланак једног од аутора ДБТ-а
- — Јутјуб, Снимак отвореног часа ОТУС-а
- — Следећи отворени час је 15
- —ОТУС
- — Поглед у будућност података и аналитике
- — Еволуција аналитике и утицај отвореног кода
- — Принципи изградње ЦИ коришћењем ДБТ
- — Вежбање, Упутства корак по корак за самосталан рад
- — Гитхуб, код образовног пројекта
Извор: ввв.хабр.цом