آموزش DBT در ۷ گام؛ ساخت و مدیریت خط لوله داده با SQL

آموزش dbt مقدمه‌ای برای آشنایی با ابزاری محبوب در مهندسی داده است. اگر دانشجو، تحلیلگر یا مهندس داده هستید و می‌خواهید با نحوه استفاده از DBT آشنا شوید، این مطلب برای شماست. بیشتر نرم‌افزارهای امروزی از دیتابیس‌های رابطه‌ای یا NoSQL استفاده می‌کنند و زبان مشترک اغلب آن‌ها SQL است.

به‌دلیل حجم زیاد داده‌ها و کند شدن کوئری‌های تحلیلی روی جداول اصلی، شرکت‌ها معمولاً داده‌ها را به‌صورت دوره‌ای خوانده، پردازش کرده و نتایج را در جداول خلاصه یا انباره داده ذخیره می‌کنند. برای مثال می‌توان آمار فروش ساعتی یک فروشگاه را محاسبه و در جدول تجمیعی ذخیره کرد تا ابزارهای هوش تجاری به‌جای جدول اصلی از آن استفاده کنند. DBT دقیقاً برای مدیریت چنین فرایندهای مبتنی بر SQL توسعه یافته است.

در این مقاله از نیک آموز، قصد داریم به صورت کاربردی با این ابزار مفید حوزه زیرساخت داده، آشنا شویم.

DBT مرحله تبدیل در خط لوله ELT

در کاربردهای امروزی پردازش داده، به جای ETL گاهی اوقات از ELT استفاده می‌کنیم. یعنی ابتدا داده را دریافت می‌کنیم (E)، آنها را ذخیره کرده (L) و پس از آن به پردازش داده‌ها و به روز رسانی دیتابیس، اقدام می‌کنیم. (T) در خط لوله ELT، داده‌های خام در انبار داده بارگذاری می‌شوند که با این کار، دو مرحله استخراج (Extract) و بارگذاری داده‌ها (Load) یعنی مراحل E و L انجام شده است. در مرحله تبدیل (Trasform)، اگر مبدا و مقصد ما از SQL پشتیبانی کند، DBT می‌تواند وارد عمل شود. در این صورت، داده‌های خام که در انبار داده بارگذاری شده‌اند با استفاده از کوئری‌های SQL که روی داده‌های انبار داده اجرا می‌شود به جداول قابل استفاده تبدیل می‌شوند.

DBT یک راه آسان برای ایجاد، تبدیل و اعتبارسنجی داده‌ها در یک انبار داده ارائه می‌دهد. یعنی مرحله T در خط لوله ELT را انجام می‌دهد.

در DBT ما با مدل‌هایی کار می‌کنیم که یک فایل SQL با دستور Select است. این مدل‌ها می‌توانند به مدل‌های دیگر وابسته باشند، تست‌هایی بر روی آن‌ها تعریف شده باشد و همچنین می‌توانند به صورت جدول یا view  ایجاد شوند. نام مدل‌های ایجاد شده توسط DBT نام فایل آنهاست.

به عنوان مثال. فایل dim_customers.sql مدلی با نام dim_customers را نشان می‌دهد. این مدل به مدل‌های stg_eltool__customers و stg_eltool__state بستگی دارد. همچنین مدل dim_customers را می‌توان در سایر تعاریف مدل ارجاع داد.

قطعه کد زیر یک فرایند تبدیل ساده را در DBT نشان می‌دهد.

with customers as (
    select *
    from {{ ref('stg_eltool__customers') }}
),
state as (
    select *
    from {{ ref('stg_eltool__state') }}
)
select c.customer_id,
    c.zipcode,
    c.city,
    c.state_code,
    s.state_name,
    c.datetime_created,
    c.datetime_updated,
    c.dbt_valid_from::TIMESTAMP as valid_from,
    CASE
        WHEN c.dbt_valid_to IS NULL THEN '9999-12-31'::TIMESTAMP
        ELSE c.dbt_valid_to::TIMESTAMP
    END as valid_to
from customers c
    join state s on c.state_code = s.state_code

ما می‌توانیم تست‌هایی را برای اجرا روی داده‌های پردازش شده با استفاده از dbt تعریف کنیم. dbt  به ما امکان می‌دهد ۲ نوع تست ایجاد کنیم، تست‌ها عبارتند از:

تست‌های عمومی: Unique ،not_null ،accepted_values و relationships تست‌هایی هستند که روی هر ستون اعمال می‌شوند و در فایل YAML تعریف شده‌اند.

تست‌های سفارشی: اسکریپت های Sql که در فولدر تست‌ها ایجاد می‌شوند. آن‌ها می‌توانند هر کوئری‌ای باشند. اگر اسکریپت‌های sql هیچ ردیفی را برنگردانند، نتیجه تست موفقیت‌آمیز است و در غیر این صورت تست ناموفق است.

version: 2
models:
  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - not_null # checks if customer_id column in dim_customers is not null
  - name: fct_orders

پروژه

سناریوی زیر را در نظر بگیرید: تیم بازاریابی از ما درخواست می‌کند که یک جدول customer_orders عدم نرمال‌سازی شده با اطلاعات مربوط به هر سفارشی که توسط مشتریان ارسال می‌شود، ایجاد کنیم. فرض می‌کنیم که داده‌های مشتریان و سفارش‌ها توسط فرآیندی در انبار داده بارگذاری می‌شوند.

فرآیندی که برای آوردن این داده‌ها به انبار داده ما استفاده می‌شود، مراحل EL از خط پردازش داده ELT است. این قسمت را می‌توان با استفاده از یک سرویس مانند Fivetran، Stitch یا سرویس‌های منبع باز مانند Singer ،Airbyte یا با استفاده از یک سرویس سفارشی انجام داد.

در ادامه قصد داریم ببینیم که داده‌های ما با جدول غیرنرمال، چطور به داده‌های نهایی تبدیل می‌شوند.

 

 

پیش‌نیازها

برای پیاده‌سازی این سناریو و انجام این کارگاه عملی، به پیش‌نیازهای زیر نیاز خواهیم داشت.

• Docker and Docker compose
• dbt
• pgcli
• git

git clone https://github.com/josephmachado/simple_dbt_project.git
export DBT_PROFILES_DIR=$(pwd)
docker compose up -d
cd simple_dbt_project

به طور پیش‌فرض dbt به دنبال کانکشن‌های دیتابس، در فایل ~/.dbt/profiles.yml می‌گردد. متغیر محیطی DBT_PROFILES_DIR به dbt می گوید که فایل profiles.yml را در دایرکتوری کاری فعلی جستجو کند.

همچنین می‌توانید با استفاده از dbt init یک پروژه dbt ایجاد کنید. این روش یک نمونه پروژه را در اختیار شما قرار می‌دهد که می‌توانید آن را تغییر دهید.

در پوشه simple_dbt_project فولدرهای زیر را مشاهده خواهید کرد.

 

 

• فولدر analysis: هر فایل sql که در این پوشه یافت می‌شود، با اجرای dbt compile به sql خام کامپایل می‌شود. آن‌ها توسط dbt اجرا نمی‌شوند، اما می‌توان آنها را در هر ابزار دلخواه کپی کرد.
• فولدر data: می‌توانیم داده‌های خامی را که می‌خواهیم در انبار داده خود بارگذاری کنیم، در این فولدر ذخیره کنیم. گرچه معمولاً برای ذخیره داده‌های نقشه‌برداری[۱] کوچک استفاده می‌شود.
• ماکروها: Dbt به کاربران اجازه می‌دهد تا ماکروهایی ایجاد کنند که توابع مبتنی بر sql هستند. این ماکروها را می‌توان در پروژه‌های دیگر استفاده مجدد نمود.

در بخش‌های زیر به بررسی فولدرهای باقی مانده یعنی models، snapshots و tests می‌پردازیم.

تنظیمات و اتصالات

در این بخش اتصالات انبار و تنظیمات پروژه را بررسی می‌کنیم.

• yml

Dbt به فایل profiles.yml که حاوی جزئیات کانکشن به دیتابیس یا انبارداده باشد نیاز دارد. ما جزئیات اتصال به انبار داده را در /simple_dbt_project/profiles.yml  تعریف کرده ایم.

متغیر target محیط را تعریف می‌کند. پیش‌فرض dev است. ما می‌توانیم چندین target داشته باشیم که هنگام اجرای دستورات dbt می‌توان آن‌ها را مشخص کرد.

پروفایل sde_dbt_tutorial است. فایل profiles.yml می‌تواند حاوی چندین پروفایل برای زمانی که بیش از یک پروژه dbt دارید باشد.

 

💡 با دوره مهندسی داده، مهارت‌های کلیدی برای طراحی، پردازش و مدیریت داده‌های بزرگ را به‌صورت حرفه‌ای بیاموزید.

 

• yml

در این فایل می‌توانید پروفایل مورد استفاده و مسیرهای انواع مختلف فایل‌ها را تعریف کنید. Materialization متغیری است که نحوه ایجاد یک مدل توسط dbt را کنترل می‌کند. به طور پیش‌فرض، هر مدل یک view خواهد بود. ما مدل‌ها را در مسیر models/marts/core/ قرار داده‌ایم.

[۱] Mapping

# Configuring models
models:
    sde_dbt_tutorial:
        # Applies to all files under models/marts/core/
        marts:
            core:
                materialized: table

dbt snapshot

Dbt در اولین اجرا یک snapshot را ایجاد می‌کند و در اجراهای متوالی مقادیر تغییر یافته را بررسی می‌کند و ردیف‌های قدیمی‌تر را به روز رسانی می‌کند. این روال را به شکل زیر شبیه‌سازی می‌کنیم.

pgcli -h localhost -U dbt -p 5432 -d dbt
# password1234
COPY warehouse.customers(customer_id, zipcode, city, state_code, datetime_created, datetime_updated)
FROM '/input_data/customer_new.csv' DELIMITER ',' CSV HEADER;

مجدد دستور snapshot را اجرا کنید.

dbt snapshot

• داده خام

 

 

• جدول Snapshot

 

 

سطر با کد پستی ۵۹۶۵۵ ستون dbt_valid_to آن به‌روز‌رسانی شده است. ستون‌های dbt_valid_from و dbt_valid_to بازه زمانی را نشان می‌دهند که داده‌های آن ردیف معتبر بوده است. در صورتی که dbt_valid_to مقدار null داشته باشد به این معنا است که همچنان این ردیف معتبر است.

فایل customers.SQL را از آدرس زیر می‌توانید دریافت کنید.

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/snapshots/customers.sql

• مخزن داده میانی

مخزن داده میانی، یک مکان داده موقت است که داده‌ها را تا زمان مشخصی به عنوان مثال یک ماه یا بیشتر نگهداری می‌کند، با این هدف که هرگاه پردازش‌های تحلیلی برنامه به داده‌های پایگاه داده نیاز پیدا کرد، از داده‌های روی مخزن داده میانی استفاده کند. این کار باعث می‌شود پایگاه داده عملیاتی که بیشتر درگیر کوئری‌های درج، حذف و ویرایش قرار دارد، برای پردازش داده‌ها مورد مراجعه قرار نگیرد و وجود مخزن داده میانی، نقش کاهش بار روی پایگاه داده عملیاتی را بر عهده دارد.

 

💡 برای آشنایی با روش‌های کاربردی بهینه‌سازی سیستم‌های داده، مقاله «۵ گام برای مقیاس‌پذیر کردن خطوط پردازش داده» را مطالعه کنید.

 

شما ممکن است متوجه eltool در نام مدل‌های staging شده باشید. اگر از داده‌های Fivetran برای مرحله استخراج و مرحله بارگذاری دادها استفاده کنیم (مراحل EL)، مدل‌های ما stg_fivetran__orders نامیده می‌شوند و فایل  YAML آن به صورت stg_fivetran.yml خواهد بود.

در stg_eltool__customers.sql به جای تابع منبع از تابع ref استفاده می‌کنیم زیرا این مدل از مدل snapshot گرفته شده است. در dbt می‌توانیم از تابع ref برای اشاره به هر مدلی که توسط dbt ایجاد شده است استفاده کنیم.

تعاریف تست:

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/staging/stg_eltool.yml

تعاریف مدل:

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/staging/stg_eltool__customers.sql

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/staging/stg_eltool__orders.sql

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/staging/stg_eltool__state.sql

• Mart

Marts شامل جداول اصلی برای کاربران نهایی و جداول vertical-specific تجاری تشکیل شده است. در مثال ما، یک فولدر department-specific برای تعریف مدل‌های درخواست شده بخش بازاریابی داریم.

• Core

core، مدل‌های واقعیت و ابعادی را که باید توسط کاربران نهایی استفاده شود، تعریف می‌کند.

تعاریف تست:

https://github.com

تعاریف مدل:

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/marts/core/dim_customers.sql

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/marts/core/fct_orders.sql

Dbt چهار تست عمومی ارائه می‌دهد. عبارتند از: unique, not_null, accepted_values relationships. می‌توانیم تست‌های one-off را در فولدر Tests ایجاد کنیم.

تست‌های one-off

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/tests/assert_customer_dimension_has_no_row_loss.sql

• Marketing

در این بخش مدل‌هایی را برای بازاریابی کاربران نهایی تعریف می‌کنیم. یک پروژه می‌تواند چندین vertical  تجاری داشته باشد. داشتن یک فولدر برای هر vertical  تجاری راه آسانی را برای سازماندهی مدل‌ها فراهم می‌کند.

تعاریف تست:

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/marts/marketing/marketing.yml

تعاریف مدل:

https://github.com/josephmachado/simple_dbt_project/blob/master/sde_dbt_tutorial/models/marts/marketing/customer_orders.sql

• اجرای DBT

ما تعاریف مدل‌های مورد نیاز را داریم. مدل‌ها را به صورت زیر ایجاد می‌کنیم.

dbt snapshot
dbt run
...
Finished running 4 view models, 2 table models ...

مدل stg_eltool__customers به مدل snapshots.customers_snapshot نیاز دارد. دقت داشته باشید snapshotها با اجرای دستور dbt run ایجاد نمی شوند، بنابراین ابتدا dbt snapshot را اجرا می‌کنیم.

مدل‌های staging و marketing به ‌عنوان view ایجاد می‌شوند و دو مدل core به‌ عنوان جداول ایجاد می‌شوند.

دستور snapshot باید مستقل از دستور run اجرا شود تا جداول snapshot به روز بمانند. اگر جداول snapshot قدیمی باشد، مدل‌ها نادرست خواهند بود.

• test

روی مدل‌های تعریف شده می‌توانیم تست‌هایی را اجرا کنیم. توجه داشته باشید که بر خلاف تست استاندارد، این تست‌ها پس از پردازش داده‌ها اجرا می‌شوند. می‌توانید تست‌ها را مطابق دستور زیر اجرا کنید.

dbt test
...
Finished running 10 tests...

دستور بالا تمام تست‌های تعریف شده در پروژه را اجرا می‌کند. برای مشاهده مدل‌ها می‌توانید وارد انبار داده شوید.

pgcli -h localhost -U dbt -p 5432 -d dbt
# password is password1234
select * from warehouse.customer_orders limit 3;
q

dbt docs generate
dbt docs serve

پس از اجرای کد بالا برای مشاهده مستندات می‌توانید به آدرس http://localhost:8080 مراجعه کنید.

 

 

زمان‌بندی

تا اینجا نحوه ایجاد snapshotها، مدل‌ها، اجرای تست‌ها و تولید مستندات را دیده‌ایم. اینها همه دستوراتی هستند که از طریق cli اجرا می‌شوند. Dbt مدل‌ها را در کوئری‌های SQL در فولدر target  (که بخشی از git repo نیست) کامپایل می‌کند و آنها را در انبار داده اجرا می‌کند.

برای برنامه‌ریزی اجراهای dbt، snapshotها و تست‌ها باید از یک زمان‌بندی استفاده کنیم. ابر Dbt یک گزینه عالی برای انجام زمان‌بندی آسان است. دستورات dbt را می‌توان توسط زمان‌بندی‌های محبوب دیگر مانند cron، Airflow، Dagster و غیره برنامه‌ریزی کرد.

 

سوالات متداول آموزش dbt

۱. DBT چیست و چه کاربردی دارد؟

DBT ابزاری در حوزه مهندسی داده است که برای تبدیل، مدل‌سازی و اعتبارسنجی داده‌ها در انبار داده استفاده می‌شود و به کمک SQL فرآیند پردازش و سازماندهی داده‌ها را ساده‌تر می‌کند.

۲. DBT در کدام مرحله از خط لوله داده استفاده می‌شود؟

DBT در مرحله Transform در خط لوله ELT استفاده می‌شود؛ یعنی پس از بارگذاری داده‌های خام در انبار داده، عملیات تبدیل و آماده‌سازی داده‌ها را انجام می‌دهد.

۳. مدل‌ها در DBT چه هستند؟

مدل‌ها فایل‌های SQL هستند که معمولاً شامل دستور SELECT می‌باشند و برای تبدیل داده‌ها به جداول یا Viewهای قابل استفاده در انبار داده به کار می‌روند.

۴. چه نوع تست‌هایی در DBT وجود دارد؟

در DBT دو نوع تست وجود دارد: تست‌های عمومی مانند unique، not_null، accepted_values و relationships و همچنین تست‌های سفارشی که با اسکریپت‌های SQL تعریف می‌شوند.

۵. Snapshot در DBT چه کاربردی دارد؟

Snapshot برای ثبت تغییرات داده‌ها در طول زمان استفاده می‌شود و به ایجاد جداولی کمک می‌کند که تاریخچه تغییرات یک موجودیت را نگهداری می‌کنند.

۶. چرا از جداول تجمیعی در پردازش داده استفاده می‌شود؟

جداول تجمیعی باعث می‌شوند ابزارهای تحلیلی و هوش تجاری به‌جای استفاده از جداول بزرگ و اصلی، از داده‌های خلاصه‌شده استفاده کنند و در نتیجه فشار کمتری به پایگاه داده وارد شود.

۷. چگونه می‌توان اجرای دستورات DBT را زمان‌بندی کرد؟

اجرای دستورات DBT مانند snapshot، run و test می‌تواند با ابزارهایی مانند dbt Cloud، cron، Airflow یا Dagster زمان‌بندی و به صورت خودکار اجرا شود.