Анбори идеалии додаҳо дар кадом принсипҳо сохта мешавад?
Таваҷҷӯҳ ба арзиши тиҷорат ва таҳлил дар сурати мавҷуд набудани рамзи таблиғ. Идоракунии DWH ҳамчун пойгоҳи код: нусхабардорӣ, баррасӣ, санҷиши автоматӣ ва CI. Модулият, васеъшавӣ, манбаи кушода ва ҷомеа. Ҳуҷҷатҳои барои корбар дӯстона ва визуализатсияи вобастагӣ (Data Lineage).
Бештар дар бораи ҳамаи ин ва дар бораи нақши DBT дар экосистемаи Big Data & Analytics - хуш омадед ба гурба.
Ҳама бимонед
Артеми Козир дар тамос аст. Зиёда аз 5 сол ман бо анборҳои додаҳо, сохтани ETL/ELT, инчунин таҳлили додаҳо ва визуализатсия кор мекунам. Ман дар айни замон кор мекунам , Ман дар OTUS курсро таълим медиҳам , ва имруз мехохам бо шумо як матлаберо пешкаш намоям, ки дар интизори огози он навишта будам бақайдгирии нав барои курс.
Шарҳи муфассал
Чаҳорчӯбаи DBT ҳама дар бораи T дар ихтисороти ELT (Extract - Transform - Load) аст.
Бо пайдоиши чунин пойгоҳи додаҳои таҳлилии пурмаҳсул ва миқёспазир, ба монанди BigQuery, Redshift, Snowflake, тағир додани берун аз анбори додаҳо маъное надошт.
DBT маълумотро аз манбаъҳо зеркашӣ намекунад, балки барои кор бо маълумоте, ки аллакай ба анбор бор карда шудааст (дар нигаҳдории дохилӣ ё берунӣ) имкониятҳои бузург фароҳам меорад.
Мақсади асосии DBT гирифтани код, тартиб додани он ба SQL, иҷро кардани фармонҳо бо пайдарпаии дуруст дар Репозиторий мебошад.
Сохтори лоиҳаи DBT
Лоиҳа аз директорияҳо ва файлҳои танҳо 2 намуд иборат аст:
- Модели (.sql) - воҳиди табдилдиҳӣ, ки бо дархости SELECT ифода шудааст
- Файли конфигуратсия (.yml) - параметрҳо, танзимот, санҷишҳо, ҳуҷҷатҳо
Дар сатҳи асосӣ, кор ба таври зерин сохта шудааст:
- Истифодабаранда рамзи моделиро дар ҳама гуна IDE мувофиқ омода мекунад
- Бо истифода аз CLI, моделҳо оғоз карда мешаванд, DBT рамзи моделро ба SQL тартиб медиҳад
- Рамзи тартибдодашудаи SQL дар анбор бо пайдарпаии додашуда иҷро карда мешавад (график)
Ин аст он чизе ки аз CLI кор мекунад, метавонад чунин бошад:
Ҳама чиз SELECT аст
Ин як хусусияти қотилони чаҳорчӯбаи Tool Build Data мебошад. Ба ибораи дигар, 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 (Ифодаҳои Ҷадвали умумӣ) - барои ташкил ва фаҳмидани код, ки дорои бисёр тағирот ва мантиқи тиҷорӣ мебошад
Дуюм: Рамзи намунавӣ омехтаи SQL ва забон аст (забони шаблонсозӣ).
Мисоли ҳалқаро истифода мебарад барои ба тавлиди маблағи барои ҳар як усули пардохт дар ифодаи зикршуда маҷмӯи. Функсия низ истифода мешавад ш - қобилияти истинод ба моделҳои дигар дар дохили код:
- Дар рафти тартиб додан ш ба нишондиҳандаи мақсаднок ба ҷадвал ё намоиш дар Анбор табдил дода мешавад
- ш ба шумо имкон медиҳад, ки графики вобастагии моделро созед
Ин буд ба DBT имкониятҳои қариб номаҳдуд илова мекунад. Аз ҳама бештар истифодашаванда инҳоянд:
- If / else изҳорот - изҳороти филиал
- Барои ҳалқаҳо - давраҳо
- Тағйирёбандаҳо
- Макрос - эҷоди макросҳо
Маводсозӣ: Ҷадвал, Намоиш, афзоянда
Стратегияи маводсозӣ равишест, ки тибқи он маҷмӯи маълумотҳои намунавӣ дар Анбор нигоҳ дошта мешавад.
Дар истилоҳҳои асосӣ ин аст:
- Ҷадвал - ҷадвали физикӣ дар анбор
- Намоиш - намоиш, ҷадвали виртуалӣ дар нигаҳдорӣ
Инчунин стратегияҳои мураккабтари моддӣ вуҷуд доранд:
- Инкременталӣ - боркунии афзоянда (аз ҷадвалҳои фактҳои калон); сатрҳои нав илова карда мешаванд, сатрҳои ивазшуда нав карда мешаванд, хатҳои ҳазфшуда тоза карда мешаванд
- Эфемерӣ - модел мустақиман амалӣ намешавад, балки ҳамчун CTE дар дигар моделҳо иштирок мекунад
- Ҳар як стратегияи дигаре, ки шумо метавонед худатон илова кунед
Илова ба стратегияҳои моддӣ, имкониятҳо барои оптимизатсия барои анборҳои мушаххас мавҷуданд, масалан:
- Snowflake: Ҷадвалҳои муваққатӣ, Рафтори якҷоякунӣ, Кластерсозии ҷадвал, Нусхабардории грантҳо, Намоишҳои бехатар
- Redshift: Distkey, Sortkey (ҳамбастагӣ, мураккаб), Намоишҳои ҳатмӣ дер
- пурсиш: Тақсимкунӣ ва гурӯҳбандии ҷадвал, рафтори якҷоякунӣ, рамзгузории KMS, нишонаҳо ва барчаспҳо
- Инфраст: Формати файл (паркет, csv, json, orc, delta), partition_by, clustered_by, сатилҳо, incremental_strategy
Дар айни замон захираҳои зерин дастгирӣ карда мешаванд:
- постгресс
- Redshift
- пурсиш
- Snowflake
- 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 дар ҷадвали фармоишҳо ба 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 бо менеҷери бастае меояд, ки ба корбарон имкон медиҳад, ки модулҳо ва макросҳои инфиродӣ нашр ва аз нав истифода баранд.
Ин маънои онро дорад, ки қобилияти бор кардан ва истифода бурдани китобхонаҳо ба монанди:
- : кор бо сана/вақт, калидҳои ивазкунанда, санҷишҳои схема, 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 -%}
)
Ин рамз аслан мегӯяд: барои муҳитҳо таҳия, озмоиш, ci танҳо барои 3 рӯзи охир маълумот гиред ва на бештар. Яъне кор дар ин муҳитҳо хеле тезтар хоҳад буд ва захираҳои камтарро талаб мекунад. Ҳангоми давидан дар муҳити атроф маҳсулот ҳолати филтр сарфи назар карда мешавад.
Маводсозӣ бо рамзгузории сутуни алтернативӣ
Redshift як DBMS сутунӣ мебошад, ки ба шумо имкон медиҳад алгоритмҳои фишурдани маълумотро барои ҳар як сутуни инфиродӣ муқаррар кунед. Интихоби алгоритмҳои оптималӣ метавонад фазои дискро 20-50% кам кунад.
макро фармони ANALYZE COMPRESSION -ро иҷро мекунад, ҷадвали навро бо алгоритмҳои рамзгузории сутуни тавсияшуда, калидҳои муайяншудаи сегментатсия (диst_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 хеле қулай аст.
Мо UDF-ро дар Python барои ҳисоб кардани хэшҳо, доменҳои почтаи электронӣ ва рамзкушоии ниқоб истифода мебарем.
Намунаи макрос, ки дар ҳама гуна муҳити иҷро (dev, test, prod) 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 мо 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 ҳамчун хидмат (Хизмати идорашаванда) имконпазир аст. Дар бар мегирад:
- Web IDE барои таҳияи лоиҳаҳо ва моделҳо
- Конфигуратсия ва банақшагирии кор
- Дастрасии оддӣ ва қулай ба гузоришҳо
- Вебсайт бо ҳуҷҷатҳои лоиҳаи шумо
- Пайваст кардани CI (Интегратсияи муттасил)
хулоса
Тайёр ва истеъмоли DWH мисли нӯшидани smoothie лаззатбахш ва фоидаовар мегардад. DBT аз Jinja, васеъшавии корбар (модулҳо), компилятор, иҷрокунанда ва мудири бастаҳо иборат аст. Бо якҷоя кардани ин унсурҳо шумо барои анбори додаҳои худ муҳити мукаммали корӣ ба даст меоред. Имрӯз роҳи беҳтари идоракунии трансформатсия дар дохили DWH вуҷуд надорад.
Эътиқодҳое, ки таҳиягарони DBT пайравӣ мекунанд, ба таври зерин ифода карда мешаванд:
- Код, на GUI, беҳтарин абстраксия барои ифодаи мантиқи мураккаби таҳлилӣ мебошад
- Кор бо додаҳо бояд таҷрибаҳои беҳтаринро дар муҳандисии нармафзор мутобиқ созад (Муҳандисии нармафзор)
- Инфрасохтори муҳими додаҳо бояд аз ҷониби ҷомеаи корбар ҳамчун нармафзори кушодаасос назорат карда шавад
- На танҳо абзорҳои таҳлилӣ, балки код низ ҳарчи бештар моликияти ҷомеаи кушодаасос мегардад
Ин эътиқодҳои аслӣ маҳсулотеро ба вуҷуд оварданд, ки имрӯз аз ҷониби зиёда аз 850 ширкат истифода мешавад ва онҳо асоси бисёр васеъшавии ҷолибе, ки дар оянда сохта мешаванд, ташкил медиҳанд.
Барои хоҳишмандон видеои дарси кушоде мавҷуд аст, ки ман чанд моҳ пеш дар доираи дарси кушод дар OTUS дода будам - .
Илова ба DBT ва анбори додаҳо, дар доираи курси муҳандиси маълумот дар платформаи OTUS, ман ва ҳамкорони ман дар як қатор дигар мавзӯъҳои муҳим ва муосир дарсҳо меомӯзем:
- Консепсияҳои меъморӣ барои барномаҳои Big Data
- Бо Spark ва Spark Streaming машқ кунед
- Омӯзиши усулҳо ва асбобҳо барои боркунии манбаъҳои маълумот
- Сохтани витринаҳои таҳлилӣ дар DWH
- Консепсияҳои NoSQL: HBase, Cassandra, ElasticSearch
- Принсипҳои мониторинг ва оркестрсозӣ
- Лоиҳаи ниҳоӣ: якҷоя кардани ҳама малакаҳо дар зери дастгирии мураббӣ
Истинодҳо:
- - ҳуҷҷатҳои расмӣ
- — Мақолаи баррасии яке аз муаллифони DBT
- — YouTube, Сабти дарси кушоди OTUS
- — Дарси кушоди навбатӣ 15 майи соли 2020 мебошад
- —ОТУС
- - Нигоҳе ба ояндаи маълумот ва таҳлил
- — Эволютсияи аналитика ва таъсири манбаи кушода
- — Принсипҳои сохтани CI бо истифода аз DBT
- — Тачриба, Дастурхои кадам ба кадам барои кори мустакилона
- — Github, рамзи лоиҳаи таълимӣ
Манбаъ: will.com