Идеал деректер қоймасы қандай принциптерге негізделген?
Белгіленген код болмаған кезде бизнес құндылығы мен аналитикаға назар аударыңыз. DWH кодтық база ретінде басқару: нұсқалау, шолу, автоматтандырылған тестілеу және CI. Модульдік, кеңейтілетін, ашық бастапқы коды және қауымдастық. Пайдаланушыға ыңғайлы құжаттама және тәуелділік визуализациясы (Data Lineage).
Мұның бәрі және Big Data & Analytics экожүйесіндегі DBT рөлі туралы толығырақ - мысыққа қош келдіңіз.
Бәріңе сәлем
Артемий Козыр байланыста. Мен 5 жылдан астам деректер қоймаларымен, ETL/ELT құрастырумен, сондай-ақ деректерді талдау және визуализациямен жұмыс істеймін. Мен қазір жұмыс істеймін , Мен OTUS курсында сабақ беремін , ал бүгін мен сіздермен басталуын күтіп жазған мақаламмен бөліскім келеді курсқа жаңа жазылу.
Шолу
DBT құрылымының барлығы ELT (Extract - Transform - Load) аббревиатурасындағы T туралы.
BigQuery, Redshift, Snowflake сияқты өнімді және масштабталатын аналитикалық дерекқорлардың пайда болуымен Деректер қоймасынан тыс түрлендірулер жасаудың қажеті болмады.
DBT дереккөздерден деректерді жүктеп алмайды, бірақ Сақтауға (ішкі немесе сыртқы жадта) жүктелген деректермен жұмыс істеуге үлкен мүмкіндіктер береді.
DBT-тің негізгі мақсаты – кодты алу, оны SQL-ге компиляциялау, Репозиторийдегі командаларды дұрыс реттілікпен орындау.
DBT жобасының құрылымы
Жоба тек 2 түрдегі каталогтар мен файлдардан тұрады:
- Модель (.sql) – SELECT сұранысымен көрсетілген түрлендіру бірлігі
- Конфигурация файлы (.yml) – параметрлер, баптаулар, сынақтар, құжаттама
Негізгі деңгейде жұмыс келесідей құрылымдалған:
- Пайдаланушы кез келген ыңғайлы IDE-де үлгі кодын дайындайды
- CLI көмегімен модельдер іске қосылады, DBT модель кодын SQL-ге құрастырады
- Құрастырылған SQL коды Сақтау қоймасында берілген реттілікпен орындалады (график)
Міне, CLI жүйесінен іске қосу келесідей болуы мүмкін:
Барлығы ТАҢДАУ
Бұл Деректерді құрастыру құралының негізгі мүмкіндігі. Басқаша айтқанда, DBT Дүкенде сұрауларыңызды жүзеге асырумен байланысты барлық кодты абстракциялайды (CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ... пәрмендеріндегі нұсқалар).
Кез келген үлгі нәтиже деректер жиынын анықтайтын бір ТАҢДАУ сұрауын жазуды қамтиды.
Бұл жағдайда түрлендіру логикасы көп деңгейлі болуы мүмкін және бірнеше басқа модельдердің деректерін біріктіреді. Тапсырыс витринасын құрастыратын үлгінің мысалы (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 және тілдің қоспасы (үлгілеу тілі).
Мысал циклды пайдаланады үшін өрнекте көрсетілген әрбір төлем әдісі үшін соманы жасау жиынтық. функциясы да қолданылады Ref — код ішіндегі басқа үлгілерге сілтеме жасау мүмкіндігі:
- Құрастыру кезінде Ref жадтағы кестеге немесе көрініске мақсатты көрсеткішке түрлендіріледі
- Ref модельдік тәуелділік графигін құруға мүмкіндік береді
Бұл болды DBT-ге дерлік шексіз мүмкіндіктер қосады. Ең жиі қолданылатындары:
- If / else операторлары – салалық операторлар
- Ілмектер үшін
- Айнымалылар
- Макрос - макростарды құру
Материалдандыру: Кесте, Көрініс, Инкрементті
Материалдандыру стратегиясы - бұл үлгі деректерінің нәтижесінде алынған жинағы Сақтау орнында сақталатын тәсіл.
Негізгі мағынада бұл:
- Кесте – сақтаудағы физикалық кесте
- Көрініс - көрініс, Сақтаудағы виртуалды кесте
Материалдандырудың күрделі стратегиялары да бар:
- Инкрементті – ұлғаймалы жүктеу (үлкен фактілер кестелерінің); жаңа жолдар қосылады, өзгертілген жолдар жаңартылады, жойылған жолдар тазартылады
- Эфемерлі – модель тікелей жүзеге аспайды, бірақ басқа модельдерде КТЖ ретінде қатысады
- Кез келген басқа стратегияларды өзіңіз қоса аласыз
Материалдандыру стратегияларынан басқа, белгілі бір қоймалар үшін оңтайландыру мүмкіндіктері бар, мысалы:
- қар бүршігі: Өтпелі кестелер, Біріктіру әрекеті, Кестені кластерлеу, Гранттарды көшіру, Қауіпсіз көріністер
- Redshift: Diskkey, Sortkey (аралас, құрама), Кеш байлау көріністері
- BigQuery: кестені бөлу және кластерлеу, біріктіру әрекеті, KMS шифрлау, белгілер және тегтер
- ұшқын: Файл пішімі (паркет, csv, json, orc, delta), бөлім_байланысы, кластерлік_байланыс, шелектер, қосымша_стратегия
Қазіргі уақытта келесі жадтарға қолдау көрсетіледі:
- Пострегтер
- Redshift
- BigQuery
- қар бүршігі
- Presto (ішінара)
- Ұшқын (ішінара)
- 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 (бағытталған циклдік график) ретінде белгілі.
DBT барлық жоба үлгілерінің конфигурациясына негізделген графикті құрастырады, дәлірек айтсақ, модельдер ішіндегі басқа үлгілерге ref() сілтемелері. Графикке ие болу келесі әрекеттерді орындауға мүмкіндік береді:
- Модельдерді дұрыс ретпен іске қосу
- Дүкен сөресін қалыптастырудың параллельділігі
- Ерікті субграфты іске қосу
Графикті визуализациялау мысалы:
Графиктің әрбір түйіні үлгі болып табылады; графиктің жиектері ref өрнегі арқылы көрсетіледі.
Деректер сапасы және құжаттама
Модельдердің өздерін жасаудан басқа, DBT алынған деректер жиыны туралы бірқатар болжамдарды тексеруге мүмкіндік береді, мысалы:
- Нөл емес
- бірегей
- Анықтама тұтастығы - анықтамалық тұтастық (мысалы, тапсырыстар кестесіндегі customer_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 сценарийлерінің жиынтығына айналу емес, пайдаланушыларға өздерінің түрлендірулерін құру және осы модульдерді тарату үшін қуатты және мүмкіндіктері мол құралдармен қамтамасыз ету.
Макростар - модельдер ішінде функциялар ретінде шақырылатын құрылымдар мен өрнектер жиыны. Макростар DRY (Өзіңізді қайталамау) инженерлік принципіне сәйкес үлгілер мен жобалар арасында SQL тілін қайта пайдалануға мүмкіндік береді.
Макромысал:
{% 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 операциясын тіркеу модулі
Пакеттердің толық тізімін мына жерден табуға болады .
Одан да көп мүмкіндіктер
Мұнда мен команда мен деректер қоймасын құру үшін қолданатын бірнеше басқа қызықты мүмкіндіктер мен енгізулерді сипаттаймын .
Орындау орталарын бөлу 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 -%}
)
Бұл код сөзбе-сөз айтады: орталар үшін әзірлеу, сынақ, ci тек соңғы 3 күндегі деректерді алыңыз және одан көп емес. Яғни, бұл орталарда жұмыс істеу әлдеқайда жылдамырақ болады және аз ресурстарды қажет етеді. Қоршаған ортамен жүгіру кезінде өнім сүзгі жағдайы еленбейді.
Баламалы баған кодтауымен материалдандыру
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() }}"
Тіркеу модулі барлық қажетті метадеректерді бөлек кестеге жазуға мүмкіндік береді, оларды кейіннен кедергілерді тексеру және талдау үшін пайдалануға болады.
Бақылау тақтасы Looker жүйесіндегі журнал деректеріне негізделгендей көрінеді:
Сақтауға техникалық қызмет көрсетуді автоматтандыру
Егер сіз UDF (Пайдаланушы анықтайтын функциялар) сияқты пайдаланылған репозиторийдің функционалдық мүмкіндіктерінің кейбір кеңейтімдерін пайдалансаңыз, бұл функциялардың нұсқасын жасау, қатынасты басқару және жаңа шығарылымдарды автоматтандырылған шығару DBT жүйесінде өте ыңғайлы.
Біз хэштерді, электрондық пошта домендерін және бит маскасының декодтауын есептеу үшін Python тілінде UDF пайдаланамыз.
Кез келген орындау ортасында (дев, сынақ, өнім) UDF жасайтын макростың мысалы:
{% 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-де біз PostgreSQL негізіндегі Amazon Redshift-ті қолданамыз. 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, пайдаланушы кеңейтімдері (модульдер), компилятор, орындаушы және пакет менеджерінен тұрады. Осы элементтерді біріктіру арқылы сіз деректер қоймасы үшін толық жұмыс ортасын аласыз. Бүгінгі күні DWH ішіндегі трансформацияны басқарудың жақсы жолы жоқ.
DBT әзірлеушілері ұстанатын сенімдер келесідей тұжырымдалған:
- GUI емес, код күрделі аналитикалық логиканы білдіру үшін ең жақсы абстракция болып табылады
- Деректермен жұмыс істеу бағдарламалық жасақтамадағы ең жақсы тәжірибелерді бейімдеу керек (Software Engineering)
- Маңызды деректер инфрақұрылымын пайдаланушы қауымдастығы ашық бастапқы бағдарламалық құрал ретінде басқаруы керек
- Тек аналитикалық құралдар ғана емес, сонымен қатар код барған сайын Ашық бастапқы қауымдастықтың меншігіне айналады
Бұл негізгі сенімдер бүгінде 850-ден астам компания қолданатын өнімді тудырды және олар болашақта жасалатын көптеген қызықты кеңейтімдердің негізін құрайды.
Қызығушылық танытқандар үшін бірнеше ай бұрын OTUS-те ашық сабақ аясында өткізген ашық сабағымның видеосы бар - .
DBT және деректер қоймасына қоса, OTUS платформасындағы Data Engineer курсының бөлігі ретінде мен және менің әріптестерім басқа да бірқатар өзекті және заманауи тақырыптар бойынша сабақ береміз:
- Үлкен деректер қолданбаларына арналған архитектуралық тұжырымдамалар
- Spark және Spark Streaming көмегімен жаттығу
- Деректер көздерін жүктеу әдістері мен құралдарын зерттеу
- DWH-де аналитикалық витриналарды құру
- NoSQL концепциялары: HBase, Cassandra, ElasticSearch
- Мониторинг және оркестрлеу принциптері
- Қорытынды жоба: тәлімгерлік қолдау аясында барлық дағдыларды біріктіру
сілтемелер:
- — Ресми құжаттама
- — DBT авторларының бірінің шолу мақаласы
- — YouTube, OTUS ашық сабағының жазбасы
- — Келесі ашық сабақ 15 жылдың 2020 мамыры
- —OTUS
- — Деректер мен аналитиканың болашағына көзқарас
- — Аналитиканың эволюциясы және Open Source әсері
- — DBT көмегімен CI құру принциптері
- — Тәжірибе, Өздік жұмысқа арналған қадамдық нұсқаулар
- — Github, білім беру жобасының коды
Ақпарат көзі: www.habr.com