İdeal Məlumat Anbarı hansı prinsiplər əsasında qurulur?
Tələb kodu olmadıqda biznes dəyərinə və analitikaya diqqət yetirin. DWH-ni kod bazası kimi idarə etmək: versiya, nəzərdən keçirmə, avtomatlaşdırılmış sınaq və CI. Modul, genişləndirilə bilən, açıq mənbə və icma. İstifadəçi dostu sənədlər və asılılıq vizuallaşdırılması (Data Lineage).
Bütün bunlar və Big Data & Analytics ekosistemində DBT-nin rolu haqqında daha çox məlumat - pişiyə xoş gəlmisiniz.
Hörmətli hər kəs
Artemy Kozyr əlaqə saxlayır. 5 ildən artıqdır ki, mən məlumat anbarları ilə işləyirəm, ETL/ELT qurur, həmçinin verilənlərin analitikası və vizuallaşdırılması ilə məşğul oluram. Hal-hazırda işləyirəm , OTUS-da kursda dərs deyirəm , və bu gün başlanğıc ərəfəsində yazdığım bir məqaləni sizinlə bölüşmək istəyirəm kursa yeni qeydiyyat.
Referat
DBT çərçivəsi ELT (Extract - Transform - Load) akronimindəki T hərfinə aiddir.
BigQuery, Redshift, Snowflake kimi məhsuldar və genişləndirilə bilən analitik verilənlər bazalarının meydana çıxması ilə Data Anbarından kənarda transformasiyalar etməyin mənası yox idi.
DBT məlumatları mənbələrdən endirmir, lakin artıq Yaddaşa (Daxili və ya Xarici Yaddaşda) yüklənmiş məlumatlarla işləmək üçün böyük imkanlar təqdim edir.
DBT-nin əsas məqsədi kodu götürmək, onu SQL-də kompilyasiya etmək, Repository-də əmrləri düzgün ardıcıllıqla yerinə yetirməkdir.
DBT Layihə Strukturu
Layihə yalnız 2 növ qovluq və fayllardan ibarətdir:
- Model (.sql) - SELECT sorğusu ilə ifadə edilən transformasiya vahidi
- Konfiqurasiya faylı (.yml) - parametrlər, parametrlər, testlər, sənədlər
Əsas səviyyədə iş aşağıdakı kimi qurulur:
- İstifadəçi istənilən rahat IDE-də model kodunu hazırlayır
- CLI-dən istifadə edərək modellər işə salınır, DBT model kodunu SQL-ə tərtib edir
- Tərtib edilmiş SQL kodu Saxlamada verilmiş ardıcıllıqla (qrafik) icra olunur.
CLI-dən işləmək belə görünə bilər:
Hər şey SEÇİMDİR
Bu, Data Build Tool çərçivəsinin öldürücü xüsusiyyətidir. Başqa sözlə, DBT sorğularınızın Mağazada reallaşdırılması ilə bağlı bütün kodları mücərrəd edir (CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ... əmrlərindən varyasyonlar).
İstənilən model nəticədə əldə edilən məlumat dəstini müəyyən edən bir SELECT sorğunun yazılmasını nəzərdə tutur.
Bu halda, transformasiya məntiqi çoxsəviyyəli ola bilər və bir neçə digər modellərin məlumatlarını birləşdirə bilər. Sifariş vitrinini quracaq bir model nümunəsi (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
Burada hansı maraqlı şeyləri görə bilərik?
Birincisi: İstifadə olunan CTE (Ümumi Cədvəl İfadələri) - bir çox transformasiya və biznes məntiqini ehtiva edən kodu təşkil etmək və anlamaq üçün
İkincisi: Model kodu SQL və dilin qarışığıdır (şablon dili).
Nümunə bir döngədən istifadə edir üçün ifadədə göstərilən hər bir ödəniş üsulu üçün məbləği yaratmaq təyin etmək. Funksiyadan da istifadə olunur ref — kod daxilində digər modellərə istinad etmək imkanı:
- Kompilyasiya zamanı ref Yaddaşdakı cədvələ və ya görünüşə hədəf göstəriciyə çevriləcək
- ref modeldən asılılıq qrafikini qurmağa imkan verir
Dəqiq DBT-yə demək olar ki, qeyri-məhdud imkanlar əlavə edir. Ən çox istifadə olunanlar bunlardır:
- If / else ifadələri - filial ifadələri
- Döngələr üçün
- Dəyişənlər
- Makro - makro yaratmaq
Materiallaşdırma: Cədvəl, Görünüş, Artan
Materiallaşdırma strategiyası, nəticədə model məlumat dəstinin Yaddaşda saxlanacağı bir yanaşmadır.
Əsas mənada belədir:
- Cədvəl - Saxlamada fiziki cədvəl
- Görünüş - görünüş, Yaddaşda virtual masa
Daha mürəkkəb maddiləşdirmə strategiyaları da var:
- Artan - artımlı yükləmə (böyük fakt cədvəllərinin); yeni sətirlər əlavə olunur, dəyişdirilmiş sətirlər yenilənir, silinmiş sətirlər təmizlənir
- Efemer - model birbaşa reallaşmır, lakin digər modellərdə CTE kimi iştirak edir
- Özünüz əlavə edə biləcəyiniz başqa strategiyalar
Materiallaşdırma strategiyalarına əlavə olaraq, xüsusi Yaddaşlar üçün optimallaşdırma imkanları var, məsələn:
- Snowflake: Keçici cədvəllər, Birləşdirmə davranışı, Cədvəl qruplaşması, Qrantların kopyalanması, Təhlükəsiz görünüşlər
- Redshift: Diskkey, Sortkey (interleaved, mürəkkəb), Gecikmiş Bağlama Baxışları
- BigQuery: Cədvəl bölmə və qruplaşdırma, Birləşdirmə davranışı, KMS Şifrələmə, Etiketlər və Teqlər
- Qığılcım: Fayl formatı (parquet, csv, json, orc, delta), partition_by, clustered_by, buckets, incremental_strategy
Aşağıdakı Yaddaşlar hazırda dəstəklənir:
- postgres
- Redshift
- BigQuery
- Snowflake
- Presto (qismən)
- Qığılcım (qismən)
- Microsoft SQL Server (icma adapteri)
Modelimizi təkmilləşdirək:
- Onun doldurulmasını artımlı edək (artan)
- Redshift üçün seqmentləşdirmə və çeşidləmə düymələrini əlavə edək
-- Конфигурация модели:
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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
Modeldən asılılıq qrafiki
O, həm də asılılıq ağacıdır. O, həmçinin DAG (Directed Acyclic Graph) kimi də tanınır.
DBT bütün layihə modellərinin konfiqurasiyasına, daha doğrusu, modellər daxilində digər modellərə ref() keçidlərinə əsaslanan qrafik qurur. Qrafikin olması sizə aşağıdakıları etməyə imkan verir:
- Düzgün ardıcıllıqla işləyən modellər
- Vitrin formalaşmasının paralelləşdirilməsi
- İxtiyari subqrafın işlədilməsi
Qrafik vizuallaşdırma nümunəsi:
Qrafikin hər bir qovşağı bir modeldir; qrafikin kənarları ref ifadəsi ilə müəyyən edilir.
Məlumatların keyfiyyəti və sənədləşdirilməsi
Modellərin özlərini yaratmaqdan əlavə, DBT sizə əldə edilən məlumat dəsti ilə bağlı bir sıra fərziyyələri sınamağa imkan verir, məsələn:
- Null deyil
- Unique
- İstinad bütövlüyü - istinad bütövlüyü (məsələn, sifarişlər cədvəlindəki customer_id, müştərilər cədvəlindəki id-ə uyğundur)
- Məqbul dəyərlər siyahısına uyğunluq
Öz testlərinizi (xüsusi məlumat testləri) əlavə etmək mümkündür, məsələn, bir gün, bir həftə, bir ay əvvəlki göstəricilərlə gəlirin % sapması. SQL sorğusu kimi formalaşan hər hansı bir fərziyyə testə çevrilə bilər.
Beləliklə, siz Anbar pəncərələrində məlumatlarda arzuolunmaz kənarlaşmaları və səhvləri tuta bilərsiniz.
Sənədləşmə baxımından DBT model və hətta atribut səviyyələrində metadata və şərhlərin əlavə edilməsi, versiyalaşdırılması və paylanması mexanizmlərini təmin edir.
Testlər və sənədlərin əlavə edilməsi konfiqurasiya faylı səviyyəsində belə görünür:
- 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']
Yaradılmış veb-saytda bu sənədlərin necə göründüyü budur:
Makrolar və Modullar
DBT-nin məqsədi SQL skriptləri toplusuna çevrilmək deyil, istifadəçilərə öz transformasiyalarını qurmaq və bu modulları yaymaq üçün güclü və zəngin xüsusiyyətlərlə təmin etməkdir.
Makrolar modellər daxilində funksiyalar kimi adlandırıla bilən konstruksiyalar və ifadələr toplusudur. Makrolar DRY (Özünüzü Təkrar Etməyin) mühəndislik prinsipinə uyğun olaraq modellər və layihələr arasında SQL-dən təkrar istifadə etməyə imkan verir.
Makro nümunəsi:
{% 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 %}
Və onun istifadəsi:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
DBT istifadəçilərə fərdi modulları və makroları dərc etməyə və təkrar istifadə etməyə imkan verən paket meneceri ilə gəlir.
Bu, aşağıdakı kimi kitabxanaları yükləmək və istifadə etmək deməkdir:
- : Tarix/Vaxt, Surroqat Açarlar, Sxem testləri, Pivot/Unpivot və digərləri ilə işləmək
- kimi xidmətlər üçün hazır vitrin şablonları и
- Xüsusi Məlumat Mağazaları üçün kitabxanalar, məs.
- — DBT əməliyyatını qeyd etmək üçün modul
Paketlərin tam siyahısını burada tapa bilərsiniz .
Daha çox xüsusiyyət
Burada komandanın və mənim Məlumat Anbarı qurmaq üçün istifadə etdiyimiz bir neçə digər maraqlı xüsusiyyətləri və tətbiqləri təsvir edəcəyəm .
İş vaxtı mühitlərinin ayrılması DEV - TEST - PROD
Hətta eyni DWH klasterində (müxtəlif sxemlər daxilində). Məsələn, aşağıdakı ifadədən istifadə edərək:
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 -%}
)
Bu kod sözün əsl mənasında deyir: mühitlər üçün inkişaf, test, ci yalnız son 3 gün ərzində məlumat alın və daha çox deyil. Yəni bu mühitlərdə işləmək çox daha sürətli olacaq və daha az resurs tələb edəcək. Ətraf mühitdə qaçarkən prod filtr vəziyyəti nəzərə alınmayacaq.
Alternativ sütun kodlaşdırması ilə materiallaşdırma
Redshift, hər bir fərdi sütun üçün məlumatların sıxılma alqoritmlərini təyin etməyə imkan verən sütunlu DBMS-dir. Optimal alqoritmlərin seçilməsi disk yerini 20-50% azalda bilər.
Makro ANALYZE COMPRESSION əmrini yerinə yetirəcək, tövsiyə olunan sütun kodlaşdırma alqoritmləri, müəyyən edilmiş seqmentasiya açarları (dist_key) və çeşidləmə açarları (sort_key) ilə yeni cədvəl yaradacaq, məlumatları ona ötürəcək və lazım olduqda köhnə nüsxəni siləcək.
Makro imza:
{{ 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) }}
Giriş modeli işləyir
Başlamadan əvvəl və ya modelin yaradılması başa çatdıqdan dərhal sonra yerinə yetiriləcək modelin hər bir icrasına qarmaqlar əlavə edə bilərsiniz:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
Giriş modulu bütün lazımi metaməlumatları ayrıca cədvəldə qeyd etməyə imkan verəcək, sonradan darboğazları yoxlamaq və təhlil etmək üçün istifadə edilə bilər.
Looker-da məlumatların qeydiyyatı əsasında idarə paneli belə görünür:
Saxlama Xidmətinin Avtomatlaşdırılması
Əgər siz UDF (İstifadəçi tərəfindən müəyyən edilmiş funksiyalar) kimi istifadə olunan Repozitoriyanın funksionallığının bəzi genişləndirmələrindən istifadə edirsinizsə, bu funksiyaların versiyalaşdırılması, girişə nəzarət və yeni buraxılışların avtomatlaşdırılmış şəkildə yayılması DBT-də çox rahatdır.
Heshləri, e-poçt domenlərini və bit maskalarının dekodlanmasını hesablamaq üçün Python-da UDF istifadə edirik.
İstənilən icra mühitində (dev, test, prod) UDF yaradan makro nümunəsi:
{% 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-də biz PostgreSQL-ə əsaslanan Amazon Redshift-dən istifadə edirik. Redshift üçün cədvəllər üzrə mütəmadi olaraq statistik məlumatları toplamaq və diskdə yer boşaltmaq vacibdir - müvafiq olaraq ANALYZE və VACUUM əmrləri.
Bunun üçün redshift_maintenance makrosundan əmrlər hər gecə yerinə yetirilir:
{% 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-dən xidmət (Managed Service) kimi istifadə etmək mümkündür. Daxildir:
- Layihələr və modellər hazırlamaq üçün Web IDE
- İşin konfiqurasiyası və planlaşdırılması
- Qeydlərə sadə və rahat giriş
- Layihənizin sənədləri ilə veb sayt
- Qoşulma CI (Davamlı İnteqrasiya)
Nəticə
DWH hazırlamaq və istehlak etmək smoothie içmək qədər zövqlü və faydalı olur. DBT Jinja, istifadəçi genişləndirmələri (modullar), kompilyator, icraçı və paket menecerindən ibarətdir. Bu elementləri bir araya gətirməklə siz Data Anbarınız üçün tam iş mühiti əldə edirsiniz. Bu gün DWH daxilində transformasiyanı idarə etmək üçün daha yaxşı bir yol yoxdur.
DBT tərtibatçılarının izlədiyi inanclar aşağıdakı kimi formalaşdırılır:
- GUI deyil, kod mürəkkəb analitik məntiqi ifadə etmək üçün ən yaxşı abstraksiyadır
- Məlumatlarla işləmək proqram mühəndisliyində ən yaxşı təcrübələri uyğunlaşdırmalıdır (Proqram Mühəndisliyi)
- Kritik məlumat infrastrukturu açıq mənbə proqram təminatı kimi istifadəçi icması tərəfindən idarə olunmalıdır
- Təkcə analitik alətlər deyil, həm də kod getdikcə daha çox Açıq Mənbə cəmiyyətinin mülkiyyətinə çevriləcək
Bu əsas inanclar bu gün 850-dən çox şirkət tərəfindən istifadə edilən bir məhsul yaratdı və onlar gələcəkdə yaradılacaq bir çox maraqlı əlavələrin əsasını təşkil edir.
Maraqlananlar üçün bir neçə ay əvvəl OTUS-da açıq dərs çərçivəsində verdiyim açıq dərsin videosu var - .
DBT və Data Warehousing ilə yanaşı, OTUS platformasında Data Engineer kursunun bir hissəsi olaraq, həmkarlarım və mən bir sıra digər aktual və müasir mövzularda dərslər öyrədirik:
- Böyük Məlumat Tətbiqləri üçün Memarlıq Konseptləri
- Spark və Spark Streaming ilə məşq edin
- Məlumat mənbələrini yükləmək üçün üsul və vasitələrin araşdırılması
- DWH-də analitik vitrinlərin qurulması
- NoSQL anlayışları: HBase, Cassandra, ElasticSearch
- Monitorinq və orkestrləşdirmə prinsipləri
- Yekun Layihə: mentorluq dəstəyi altında bütün bacarıqları bir araya gətirmək
Referanslar:
- - Rəsmi sənədlər
- — DBT müəlliflərindən birinin icmalı məqaləsi
- — YouTube, OTUS açıq dərsinin səsyazması
- — Növbəti açıq dərs 15 may 2020-ci ildir
- -OTUS
- — Məlumat və analitikanın gələcəyinə baxış
- — Analitikanın təkamülü və Açıq Mənbənin təsiri
- — DBT istifadə edərək CI-nin qurulması prinsipləri
- — Təcrübə, müstəqil iş üçün addım-addım təlimat
- — Github, təhsil layihəsi kodu
Mənbə: www.habr.com