د ډیټا مثالی ګودام په کومو اصولو جوړ شوی؟
د بویلر پلیټ کوډ په نشتوالي کې د سوداګرۍ ارزښت او تحلیلونو باندې تمرکز وکړئ. د کوډبیس په توګه د DWH اداره کول: نسخه کول، بیاکتنه، اتوماتیک ازموینه او CI. ماډلر، د توزیع وړ، خلاص سرچینه او ټولنه. د کارونکي دوستانه اسناد او د انحصار لید (د معلوماتو لړۍ).
د دې ټولو په اړه او د لوی ډیټا او تحلیلي ایکوسیستم کې د DBT رول په اړه نور - بلی ته ښه راغلاست.
ټولو ته سلام
آرټیمي کوزیر په تماس کې دی. د 5 کلونو څخه ډیر وخت لپاره زه د ډیټا ګودامونو سره کار کوم ، د ETL/ELT جوړول ، او همدارنګه د معلوماتو تحلیل او لید. زه اوس په کار بوخت یم ، زه په OTUS کې په کورس کې درس ورکوم ، او نن زه غواړم تاسو سره یوه مقاله شریکه کړم چې ما د پیل په تمه لیکلې وه د کورس لپاره نوی نوم لیکنه.
لنډه کتنه
د DBT چوکاټ ټول په ELT کې د T په اړه دی (استخراج - لیږد - بار) لنډیز.
د ورته تولیدي او توزیع وړ تحلیلي ډیټابیسونو لکه BigQuery، Redshift، Snowflake په راتګ سره، د ډیټا ګودام څخه بهر د بدلونونو ترسره کولو لپاره هیڅ معنی نه درلوده.
DBT د سرچینو څخه ډیټا ډاونلوډ نه کوي ، مګر د ډیټا سره کار کولو لپاره عالي فرصتونه چمتو کوي کوم چې دمخه ذخیره کې بار شوي (په داخلي یا بهرني ذخیره کې).
د DBT اصلي هدف دا دی چې کوډ واخلئ ، په SQL کې یې تالیف کړئ ، په ذخیره کې په سم ترتیب کې کمانډونه اجرا کړئ.
د DBT پروژې جوړښت
پروژه یوازې د 2 ډولونو لارښودونو او فایلونو څخه جوړه ده:
- ماډل (.sql) - د بدلون یو واحد چې د SELECT پوښتنې لخوا څرګند شوی
- د ترتیب فایل (.yml) - پیرامیټونه، ترتیبات، ازموینې، اسناد
په بنسټیزه کچه، کار په لاندې ډول جوړ شوی دی:
- کارونکي په هر مناسب IDE کې د ماډل کوډ چمتو کوي
- د CLI په کارولو سره، ماډلونه پیل شوي، DBT د ماډل کوډ په SQL کې تالیف کوي
- تالیف شوی SQL کوډ په ذخیرې کې په ورکړل شوي ترتیب (ګراف) کې اجرا کیږي
دلته هغه څه دي چې د CLI څخه چلول ممکن ورته ښکاري:
هرڅه SELECT دي
دا د ډیټا جوړونې وسیلې چوکاټ وژونکي ځانګړتیا ده. په بل عبارت، 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 ته نږدې لامحدود امکانات اضافه کوي. ترټولو عام کارول شوي دي:
- که / بل بیانونه - د څانګې بیانونه
- د لوګو لپاره
- متغیرات
- میکرو - میکرو جوړول
مواد کول: جدول، لید، زیاتوالی
د موادو کولو ستراتیژي یوه تګلاره ده چې له مخې یې د ماډل ډیټا پایله به په ذخیره کې زیرمه شي.
په بنسټیز ډول دا دی:
- جدول - په ذخیره کې فزیکي میز
- لید - لید، په ذخیره کې مجازی میز
د مادي کولو نورې پیچلې ستراتیژۍ هم شتون لري:
- زیاتوالی - زیاتیدونکي بار کول (د لوی حقیقت جدولونو څخه)؛ نوې لینونه اضافه شوي، بدل شوي کرښې تازه شوي، حذف شوي کرښې پاکې شوي
- Ephemeral - ماډل په مستقیم ډول نه جوړیږي، مګر په نورو ماډلونو کې د CTE په توګه برخه اخلي
- کوم بل ستراتیژۍ چې تاسو کولی شئ خپل ځان اضافه کړئ
د مادي کولو ستراتیژیو سربیره، د ځانګړو زیرمو لپاره د اصلاح کولو فرصتونه شتون لري، د بیلګې په توګه:
- Snowflake: انتقالي میزونه، د چلند ضمیمه کول، د میز کلستر کول، د مرستو کاپي کول، خوندي لیدونه
- ریډیټیټ: Distkey، Sortkey (interleaved, compound), Late Binding Views
- لویه پوښتنه: د جدول ویشل او کلستر کول، د چلند ضمیمه کول، د KMS کوډ کول، لیبلونه او ټاګونه
- سپرک: د فایل بڼه (پارکیټ، csv، json، orc، delta)، partition_by، clustered_by، buckets، incremental_strategy
لاندې ذخیره اوس مهال ملاتړ کیږي:
- پوسټګریسونه
- ریډیټیټ
- لویه پوښتنه
- Snowflake
- پریستو (په جزوي توګه)
- څراغ (په جزوي توګه)
- د مایکروسافټ SQL سرور (د ټولنې اډاپټر)
راځئ چې خپل ماډل ته وده ورکړو:
- راځئ چې د هغې ډکول زیاتیدونکي (زیاتوالی) کړو
- راځئ چې د 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() لینکونه. د ګراف درلودل تاسو ته اجازه درکوي چې لاندې کارونه ترسره کړئ:
- په سم ترتیب کې د ماډلونو چلول
- د پلورنځي د جوړښت موازي کول
- د خپل سري سبګراف چلول
د ګراف لید بیلګه:
د ګراف هر نوډ یو ماډل دی؛ د ګراف څنډې د بیان ریف لخوا مشخص شوي.
د معلوماتو کیفیت او اسناد
پخپله د ماډلونو رامینځته کولو سربیره ، 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 سکریپټونو سیټ شي ، مګر کاروونکو ته د دوی د خپلو بدلونونو رامینځته کولو او د دې ماډلونو توزیع کولو لپاره قوي او بډایه وسیله چمتو کول دي.
میکرو د ساختمانونو او بیانونو مجموعه ده چې په ماډلونو کې د فنکشن په توګه ویل کیدی شي. میکرو تاسو ته اجازه درکوي چې د 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 -%}
)
دا کوډ په لفظي ډول وايي: د چاپیریال لپاره dev، test، ci یوازې د تیرو 3 ورځو لپاره ډاټا واخلئ او نور نه. دا دی، په دې چاپیریال کې چلول به خورا ګړندي وي او لږو سرچینو ته اړتیا ولري. کله چې په چاپیریال کې روان وي پروډ د فلټر حالت به له پامه غورځول شي.
د بدیل کالم کوډ کولو سره مواد کول
Redshift یو کالم DBMS دی چې تاسو ته اجازه درکوي د هر انفرادي کالم لپاره د ډیټا کمپریشن الګوریتم تنظیم کړئ. د غوره الګوریتم غوره کول کولی شي د ډیسک ځای 20-50٪ کم کړي.
میکرو د تحلیل کمپریشن کمانډ به اجرا کړي ، د وړاندیز شوي کالم کوډ کولو الګوریتمونو سره یو نوی جدول رامینځته کړي ، د ځانګړي قطع کولو کیلي (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() }}"
د ننوتلو ماډل به تاسو ته اجازه درکړي چې ټول اړین میټاډاټا په جلا جدول کې ثبت کړئ، کوم چې وروسته د خنډونو د پلټنې او تحلیل لپاره کارول کیدی شي.
دا هغه څه دي چې ډشبورډ په لیکر کې د ننوتلو معلوماتو پراساس ښکاري:
د ذخیره کولو اتوماتیک ساتنه
که تاسو د کارول شوي ذخیره کولو فعالیت ځینې توسیعونه وکاروئ ، لکه 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 لپاره، دا مهمه ده چې په منظم ډول په میزونو کې احصایې راټول کړئ او د ډیسک ځای خالي کړئ - په ترتیب سره د تحلیل او ویکیوم کمانډونه.
د دې کولو لپاره، د 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 د خدمت په توګه وکاروئ (مدیر شوي خدمت). شامل دي:
- د پروژو او ماډلونو پراختیا لپاره ویب IDE
- د دندې ترتیب او مهال ویش
- لوګو ته ساده او اسانه لاسرسی
- ستاسو د پروژې اسنادو سره ویب پاڼه
- د CI سره نښلول (دوامداره ادغام)
پایلې
د DWH چمتو کول او مصرف کول هغومره خوندور او ګټور کیږي لکه د مسمو څښلو. DBT د جنجا، د کاروونکي توسیع (ماډولونه)، یو کمپیلر، یو اجرا کونکی، او د بسته بندي مدیر لري. د دې عناصرو په یوځای کولو سره تاسو د خپل ډیټا ګدام لپاره بشپړ کاري چاپیریال ترلاسه کوئ. نن ورځ د DWH دننه د بدلون اداره کولو لپاره خورا ښه لاره شتون نلري.
هغه باورونه چې د DBT پراختیا کونکو لخوا تعقیب شوي په لاندې ډول ترتیب شوي دي:
- کوډ، نه GUI، د پیچلي تحلیلي منطق څرګندولو لپاره غوره خلاصون دی
- د معلوماتو سره کار کول باید د سافټویر انجینرۍ (د سافټویر انجینرۍ) کې غوره تمرینونه تطبیق کړي
- د معلوماتو مهم زیربنا باید د کارونکي ټولنې لخوا د خلاصې سرچینې سافټویر په توګه کنټرول شي
- نه یوازې د تحلیلي وسیلې ، بلکې کوډ به په زیاتیدونکي ډول د خلاصې سرچینې ټولنې ملکیت شي
دې اصلي باورونو یو محصول رامینځته کړی چې نن ورځ د 850 څخه زیاتو شرکتونو لخوا کارول کیږي ، او دوی د ډیری په زړه پوري توسیعونو اساس جوړوي چې په راتلونکي کې به رامینځته شي.
د هغو کسانو لپاره چې لیوالتیا لري، د خلاص درس یوه ویډیو شتون لري چې ما څو میاشتې دمخه په OTUS کې د پرانیستې درس د یوې برخې په توګه ورکړ - .
د DBT او ډیټا ګدام سربیره ، د OTUS پلیټ فارم کې د ډیټا انجینر کورس د یوې برخې په توګه ، زه او زما همکاران په یو شمیر نورو اړوندو او عصري موضوعاتو ټولګیو ته درس ورکوم:
- د لوی ډیټا غوښتنلیکونو لپاره معماري مفکورې
- د سپارک او سپارک سټریمینګ سره تمرین وکړئ
- د معلوماتو سرچینو بارولو لپاره د میتودونو او وسیلو سپړنه
- په DWH کې د تحلیلي نندارتونونو جوړول
- د NoSQL مفکورې: HBase، Cassandra، ElasticSearch
- د څارنې او آرکیسټریشن اصول
- وروستنۍ پروژه: د لارښوونې ملاتړ لاندې ټول مهارتونه یوځای کول
سرچینې:
- - رسمي اسناد
- - د DBT د لیکوالانو څخه یوه مقاله بیاکتنه
- - یوټیوب، د OTUS خلاص درس ثبت کول
- - راتلونکی خلاص درس د می 15، 2020 دی
- – OTUS
- - د معلوماتو او تحلیلونو راتلونکي ته یوه کتنه
- - د تحلیلونو تحول او د خلاصې سرچینې نفوذ
- - د DBT په کارولو سره د CI جوړولو اصول
- - تمرین، د خپلواک کار لپاره ګام په ګام لارښوونې
- - ګیتوب، د تعلیمي پروژې کوډ
سرچینه: www.habr.com