Ideal ma'lumotlar ombori qanday tamoyillar asosida qurilgan?

Kod bo'lmaganda biznes qiymati va tahliliga e'tibor qarating. DWH ni kod bazasi sifatida boshqarish: versiyalar yaratish, ko'rib chiqish, avtomatlashtirilgan test va CI. Modulli, kengaytiriladigan, ochiq manba va hamjamiyat. Foydalanuvchi uchun qulay hujjatlar va qaramlik vizualizatsiyasi (Data Lineage).

Bularning barchasi va Big Data & Analytics ekotizimidagi DBT roli haqida batafsilroq - mushukka xush kelibsiz.

Hammaga salom

Artemi Kozyr aloqada. 5 yildan ortiq vaqt davomida men ma'lumotlar omborlari, ETL/ELT qurish, shuningdek, ma'lumotlarni tahlil qilish va vizualizatsiya qilish bilan ishlayman. Hozirda ishlayapman g'ildirakli, Men OTUSda kursda dars beraman Ma'lumotlar muhandisi, va bugun men sizlar bilan boshlanishini kutgan holda yozgan maqolamni baham ko'rmoqchiman kursga yangi ro'yxatga olish.

Umumiy nuqtai

DBT ramkasi ELT (Extract - Transform - Load) qisqartmasidagi T haqidadir.

BigQuery, Redshift, Snowflake kabi samarali va kengaytiriladigan analitik ma'lumotlar bazalarining paydo bo'lishi bilan Ma'lumotlar omboridan tashqarida transformatsiyalar qilishning ma'nosi yo'q edi.

DBT ma'lumotlarni manbalardan yuklamaydi, lekin allaqachon xotiraga (ichki yoki tashqi xotirada) yuklangan ma'lumotlar bilan ishlash uchun katta imkoniyatlar beradi.

DBT ning asosiy maqsadi kodni olish, uni SQL ga kompilyatsiya qilish, omborda buyruqlarni to'g'ri ketma-ketlikda bajarishdir.

DBT loyihasining tuzilishi

Loyiha faqat 2 turdagi katalog va fayllardan iborat:

Model (.sql) - SELECT so'rovi bilan ifodalangan transformatsiya birligi
Konfiguratsiya fayli (.yml) - parametrlar, sozlamalar, testlar, hujjatlar

Asosiy darajada ish quyidagi tarzda tuzilgan:

Foydalanuvchi istalgan qulay IDEda model kodini tayyorlaydi
CLI-dan foydalanib, modellar ishga tushiriladi, DBT model kodini SQL-ga kompilyatsiya qiladi
Kompilyatsiya qilingan SQL kodi ma'lum bir ketma-ketlikda (grafik) Saqlashda bajariladi.

CLI-dan ishga tushirish quyidagicha ko'rinishi mumkin:

Hammasi SELECT

Bu Data Build Tool ramkasining qotil xususiyati. Boshqacha qilib aytganda, DBT sizning so'rovlaringizni Do'konda amalga oshirish bilan bog'liq bo'lgan barcha kodlarni abstrakt qiladi (CREATE, INSERT, UPDATE, DELETE ALTER, GRANT, ... buyruqlaridan o'zgarishlar).

Har qanday model olingan ma'lumotlar to'plamini belgilaydigan bitta SELECT so'rovini yozishni o'z ichiga oladi.

Bunday holda, transformatsiya mantig'i ko'p darajali bo'lishi va bir nechta boshqa modellarning ma'lumotlarini birlashtirishi mumkin. Buyurtma vitrini (f_orders) quradigan modelga misol:

{% 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

Bu erda qanday qiziqarli narsalarni ko'rishimiz mumkin?

Birinchisi: CTE (Common Table Expressions) - juda ko'p o'zgarishlar va biznes mantiqini o'z ichiga olgan kodni tashkil qilish va tushunish uchun ishlatiladi

Ikkinchidan: Model kodi SQL va tilning aralashmasidir Jinja (qoliplash tili).

Misol loopdan foydalanadi uchun ifodada ko'rsatilgan har bir to'lov usuli uchun summani yaratish o'rnatilgan. Funktsiyadan ham foydalaniladi Ref — kod ichidagi boshqa modellarga murojaat qilish imkoniyati:

Kompilyatsiya paytida Ref Saqlashdagi jadval yoki ko'rinishga maqsadli ko'rsatgichga aylantiriladi
Ref modelga bog'liqlik grafigini qurish imkonini beradi

Bu edi Jinja DBTga deyarli cheksiz imkoniyatlar qo'shadi. Eng ko'p ishlatiladiganlar quyidagilardir:

If / else iboralari - filial bayonotlari
Looplar uchun
O'zgaruvchilar
Makro - makroslarni yaratish

Materiallashtirish: Jadval, Ko'rish, O'sish

Materiallashtirish strategiyasi - bu model ma'lumotlarining natijaviy to'plami Saqlashda saqlanadigan yondashuv.

Asosiy ma'noda bu:

Jadval - saqlashdagi jismoniy jadval
Ko'rish - ko'rish, saqlashdagi virtual jadval

Bundan tashqari, yanada murakkab moddiylashtirish strategiyalari mavjud:

Incremental - incremental yuklash (katta faktlar jadvallari); yangi satrlar qo'shiladi, o'zgartirilgan satrlar yangilanadi, o'chirilgan chiziqlar tozalanadi
Efemer - model to'g'ridan-to'g'ri amalga oshmaydi, lekin boshqa modellarda CTE sifatida ishtirok etadi
O'zingiz qo'shishingiz mumkin bo'lgan boshqa strategiyalar

Moddiylashtirish strategiyalariga qo'shimcha ravishda, muayyan saqlash uchun optimallashtirish imkoniyatlari mavjud, masalan:

qor: Vaqtinchalik jadvallar, Birlashtirish harakati, Jadvallarni klasterlash, Grantlarni nusxalash, Xavfsiz ko'rinishlar
Redshift: Diskkey, Sortkey (aralashtirilgan, birikma), Kechiktirilgan bog'lanish ko'rinishlari
katta so'rov: Jadvalni qismlarga ajratish va klasterlash, Birlashtirish harakati, KMS shifrlash, Yorliqlar va teglar
uchqun: Fayl formati (parket, csv, json, orc, delta), bo'lim_by, clustered_by, chelaklar, incremental_strategy

Hozirgi vaqtda quyidagi xotiralar qo'llab-quvvatlanadi:

Postgres
Redshift
katta so'rov
qor
Presto (qisman)
Uchqun (qisman)
Microsoft SQL Server (jamoa adapteri)

Keling, modelimizni yaxshilaymiz:

Keling, uni to'ldirishni bosqichma-bosqich qilaylik (qo'shimcha)
Redshift uchun segmentatsiya va saralash tugmachalarini qo'shamiz

-- Конфигурация модели: 
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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

Modelga bog'liqlik grafigi

Bu ham qaramlik daraxti. U DAG (yo'naltirilgan asiklik grafik) sifatida ham tanilgan.

DBT barcha loyiha modellarining konfiguratsiyasiga, aniqrog'i, modellar ichida boshqa modellarga ref() havolalari asosida grafik tuzadi. Grafikga ega bo'lish sizga quyidagi ishlarni bajarishga imkon beradi:

To'g'ri ketma-ketlikda ishlaydigan modellar
Vitrin shakllanishining parallelligi
Ixtiyoriy subgrafni ishga tushirish

Grafik vizualizatsiyasiga misol:

Grafikning har bir tuguni modeldir; grafik qirralari ref ifodasi bilan belgilanadi.

Ma'lumotlar sifati va hujjatlashtirish

Modellarning o'zini shakllantirishdan tashqari, DBT sizga olingan ma'lumotlar to'plamiga oid bir qator taxminlarni (tasdiqlarni) sinab ko'rish imkonini beradi, masalan:

Null emas
noyob
Malumot yaxlitligi - havolaning yaxlitligi (masalan, buyurtmalar jadvalidagi customer_id mijoz jadvalidagi id ga mos keladi)
Qabul qilinadigan qiymatlar ro'yxatiga mos kelish

O'zingizning testlaringizni (maxsus ma'lumotlar testlarini) qo'shishingiz mumkin, masalan, bir kun, bir hafta, bir oy oldingi ko'rsatkichlar bilan daromadning% og'ishi. SQL so'rovi sifatida tuzilgan har qanday taxmin sinovga aylanishi mumkin.

Shunday qilib, siz Ombor oynalarida ma'lumotlardagi kiruvchi og'ishlar va xatolarni qo'lga kiritishingiz mumkin.

Hujjatlar nuqtai nazaridan, DBT model va hatto atribut darajasida metama'lumotlar va sharhlarni qo'shish, versiyalash va tarqatish mexanizmlarini taqdim etadi.

Testlar va hujjatlarni qo'shish konfiguratsiya fayli darajasida qanday ko'rinishga ega:

 - 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']

Va bu hujjat yaratilgan veb-saytda qanday ko'rinishga ega:

Makroslar va modullar

DBT ning maqsadi SQL skriptlari to'plamiga aylanish emas, balki foydalanuvchilarga o'zlarining transformatsiyalarini yaratish va ushbu modullarni tarqatish uchun kuchli va xususiyatlarga boy vositalarni taqdim etishdir.

Makroslar - bu modellar ichida funktsiyalar sifatida chaqirilishi mumkin bo'lgan konstruktsiyalar va ifodalar to'plami. Makroslar DRY (O'zingizni takrorlamang) muhandislik tamoyiliga muvofiq modellar va loyihalar o'rtasida SQL-ni qayta ishlatishga imkon beradi.

Ibratli misol:

{% 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 %}

Va uning qo'llanilishi:

{% set column_name = 'product' %}
select
 product,
 {{ rename_category(column_name) }} -- вызов макроса
from my_table

DBT foydalanuvchilarga alohida modullar va makroslarni nashr qilish va qayta ishlatish imkonini beruvchi paket menejeri bilan birga keladi.

Bu quyidagi kabi kutubxonalarni yuklash va ulardan foydalanish imkoniyatini anglatadi:

dbt_utils: Sana/Vaqt, Surrogat kalitlar, sxema testlari, Pivot/Unpivot va boshqalar bilan ishlash
kabi xizmatlar uchun tayyor vitrin shablonlari Qor tozalash mashinasi и chiziq
Muayyan ma'lumotlar do'konlari uchun kutubxonalar, masalan. Redshift
Ro'yxatga olish — DBT operatsiyasini qayd qilish moduli

Paketlarning to'liq ro'yxatini quyidagi manzilda topishingiz mumkin dbt markazi.

Yana ko'proq xususiyatlar

Bu erda men jamoa va men ma'lumotlar omborini qurishda foydalanadigan bir nechta qiziqarli xususiyatlar va ilovalarni tasvirlab beraman g'ildirakli.

Ish vaqti muhitlarini ajratish DEV - TEST - PROD

Hatto bir xil DWH klasterida (turli sxemalar ichida). Masalan, quyidagi ifodadan foydalanish:

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 -%}
 
)

Ushbu kod so'zma-so'z aytadi: muhitlar uchun dev, test, ci faqat oxirgi 3 kun uchun ma'lumotlarni oling va undan ko'p emas. Ya'ni, bu muhitlarda ishlash ancha tez bo'ladi va kamroq resurslarni talab qiladi. Atrof muhitda yugurishda prod filtr holati e'tiborga olinmaydi.

Muqobil ustun kodlash bilan materiallashtirish

Redshift - bu har bir alohida ustun uchun ma'lumotlarni siqish algoritmlarini o'rnatish imkonini beruvchi ustunli DBMS. Optimal algoritmlarni tanlash disk maydonini 20-50% ga qisqartirishi mumkin.

Ibratli redshift.compress_table ANALYZE COMPRESSION buyrug'ini bajaradi, tavsiya etilgan ustun kodlash algoritmlari, ko'rsatilgan segmentatsiya kalitlari (dist_key) va saralash kalitlari (sort_key) bilan yangi jadval yaratadi, unga ma'lumotlarni uzatadi va kerak bo'lganda eski nusxani o'chiradi.

Ibratli imzo:

{{ 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) }}

Ro'yxatga olish modeli ishlaydi

Modelning har bir bajarilishiga ilgaklar ulashingiz mumkin, ular ishga tushirishdan oldin yoki modelni yaratish tugagandan so'ng darhol amalga oshiriladi:

   pre-hook: "{{ logging.log_model_start_event() }}"
   post-hook: "{{ logging.log_model_end_event() }}"

Ro'yxatga olish moduli sizga barcha kerakli metama'lumotlarni alohida jadvalga yozib qo'yish imkonini beradi, keyinchalik ular to'siqlarni tekshirish va tahlil qilish uchun ishlatilishi mumkin.

Looker-da ma'lumotlarni qayd qilish asosida asboblar paneli shunday ko'rinadi:

Saqlashda texnik xizmat ko'rsatishni avtomatlashtirish

Agar siz UDF (User Defined Functions) kabi foydalanilgan ombor funksionalligining ba'zi kengaytmalaridan foydalansangiz, DBTda ushbu funksiyalarning versiyalarini yaratish, kirishni boshqarish va yangi relizlarni avtomatlashtirilgan tarzda chiqarish juda qulay.

Xeshlarni, elektron pochta domenlarini va bit maskalarini dekodlashni hisoblash uchun Python-da UDF dan foydalanamiz.

Har qanday ijro muhitida (dev, test, prod) UDF yaratuvchi makrosga misol:

{% 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-da biz PostgreSQL-ga asoslangan Amazon Redshift-dan foydalanamiz. Redshift uchun jadvallar bo'yicha statistik ma'lumotlarni muntazam ravishda to'plash va diskda bo'sh joy bo'shatish muhim - mos ravishda ANALYZE va VACUUM buyruqlari.

Buning uchun har kecha redshift_maintenance makrosidagi buyruqlar bajariladi:

{% 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 buluti

DBT dan xizmat sifatida foydalanish mumkin (Managed Service). Shu jumladan:

Loyiha va modellarni ishlab chiqish uchun Web IDE
Ishni sozlash va rejalashtirish
Jurnallarga oddiy va qulay kirish
Loyihangizning hujjatlari bilan veb-sayt
Ulanish CI (uzluksiz integratsiya)

xulosa

DWHni tayyorlash va iste'mol qilish smetana ichish kabi yoqimli va foydali bo'ladi. DBT Jinja, foydalanuvchi kengaytmalari (modullar), kompilyator, ijrochi va paket boshqaruvchisidan iborat. Ushbu elementlarni birlashtirib, siz ma'lumotlar omboringiz uchun to'liq ish muhitiga ega bo'lasiz. Bugungi kunda DWH ichida transformatsiyani boshqarishning yaxshiroq yo'li deyarli yo'q.

DBT ni ishlab chiquvchilarning e'tiqodlari quyidagicha shakllantirilgan:

GUI emas, kod murakkab analitik mantiqni ifodalash uchun eng yaxshi abstraksiyadir
Ma'lumotlar bilan ishlash dasturiy ta'minot injiniringidagi eng yaxshi amaliyotlarni moslashtirishi kerak (Dasturiy ta'minot muhandisligi)

Muhim ma'lumotlar infratuzilmasi ochiq kodli dasturiy ta'minot sifatida foydalanuvchilar hamjamiyati tomonidan nazorat qilinishi kerak
Nafaqat tahlil vositalari, balki kod ham borgan sari Ochiq manbalar hamjamiyatining mulkiga aylanadi

Ushbu asosiy e'tiqodlar bugungi kunda 850 dan ortiq kompaniyalar tomonidan foydalaniladigan mahsulotni yaratdi va ular kelajakda yaratiladigan ko'plab qiziqarli kengaytmalarning asosini tashkil qiladi.

Qiziqqanlar uchun bir necha oy oldin OTUSda ochiq dars doirasida o'tkazgan ochiq dars videosi bor - Amazon Redshift Storage uchun ma'lumotlarni yaratish vositasi.

DBT va ma'lumotlar omboridan tashqari, OTUS platformasidagi Data Engineer kursining bir qismi sifatida men va mening hamkasblarim boshqa bir qator dolzarb va zamonaviy mavzular bo'yicha darslar o'tkazamiz:

Katta ma'lumotlar ilovalari uchun arxitektura tushunchalari
Spark va Spark Streaming bilan mashq qiling
Ma'lumotlar manbalarini yuklash usullari va vositalarini o'rganish
DWH da analitik vitrinalar yaratish
NoSQL tushunchalari: HBase, Cassandra, ElasticSearch
Monitoring va orkestratsiya tamoyillari
Yakuniy loyiha: barcha ko'nikmalarni murabbiylik yordami ostida birlashtirish

Manbalar:

DBT hujjatlari - Kirish - Rasmiy hujjatlar
Aynan dbt nima? — DBT mualliflaridan birining sharh maqolasi
Amazon Redshift Storage uchun ma'lumotlarni yaratish vositasi — YouTube, OTUS ochiq darsini yozib olish
Greenplum bilan tanishish — Keyingi ochiq dars 15-yil 2020-may
Ma'lumotlar muhandisligi kursi -OTUS
Yetuk tahliliy ish oqimini yaratish — Maʼlumotlar va tahlillar kelajagiga qarash
Ochiq manbali tahlillar vaqti keldi — Analitikaning evolyutsiyasi va Open Source taʼsiri
dbtCloud bilan uzluksiz integratsiya va avtomatlashtirilgan qurilish sinovlari — DBT yordamida CI qurish tamoyillari
DBT qo'llanmasini ishga tushirish — Amaliyot, Mustaqil ish uchun bosqichma-bosqich ko'rsatmalar
Jaffle do'koni - Github DBT qo'llanmasi - Github, ta'lim loyihasi kodi

Kurs haqida ko'proq bilib oling.

Manba: www.habr.com

Ma'lumotlarni yaratish vositasi yoki Data Warehouse va Smoothie o'rtasidagi umumiy narsa