طراحی یک سیستم ELTبا استفاده از Stitch و dbt

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

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

بیاید سؤالاتی را مهندسانی که تلاش می‌کنند پلتفرم داده شرکت خود را راه‌ اندازی کنند را با هم مرور کنیم.

• اطلاعات ما در حدود X گیگابایت است، از چه ابزارهایی باید استفاده کنیم؟
• ما تیم کوچکی از مهندسان و تحلیلگران هستیم، بنابراین می‌خواهیم همه چیز را ساده نگه داریم، اما ترجیح می‌دهیم از ابزارهای استاندارد صنعتی نیز استفاده کنیم. راه کار کدام است؟
  
  

در این مقاله می‌خواهیم به مقایسه ELT در مقابل ETL می‌پردازیم. مقاله ابزار های برتر ETL در سال ۲۰۲۴ را مطالعه کنید. و به سوالات بالا پاسخ می‌دهیم.

قبل از شروع ساختن یک سیستم ELT

هنگام ساختن یک سیستم ELT و ساخت انبار داده، چند نکته کلیدی وجود دارد که باید به خاطر بسپارید.

کسب و کار و داده‌ها را درک کنید

• بدانید که مدل کسب و کار شرکت چگونه کار می‌کند؟
• کاربران به چه داده‌های اهمیت می دهند و دلیل اهمیت آنها به این داده ها چیست؟

داکیومنت سازی

• مدل‌های داده خود، معنای هر ستون، هرگونه هشدار در داده‌های خود و نحوه تولید داده‌ها را مستند کنید.

کیفیت داده‌ها

• مهم‌ترین چیزی که کاربران داده به آن اهمیت می‌دهند کیفیت داده‌ها است، مطمئن شوید که سیستم‌های نظارتی و هشدار دهنده در صورت بروز مشکلات کیفیت به شما هشدار لازم را ارسال کنند.

اجزای یک پلتفرم مهندسی داده

همه پلتفرم‌ های مهندسی داده دارای ۳ بخش اصلی زیر هستند:

• استخراج: استخراج داده‌ها از سیستم‌ های منبع.
• تبدیل: تبدیل داده‌ها بر اساس الزامات مدل کسب و کار یا داده.
• بارگذاری: بارگذاری داده‌ها در جدول مقصد.

ما یک لایه دیگر اضافه می‌کنیم، که همان چیزی است که کاربران داده از آن استفاده می‌کنند. لایه ارائه که به نام داشبورد، می‌شناسند.

• داشبورد: کاربران برای دریافت یک دید کلی از داده‌های ارائه شده توسط تیم مهندسان داده، استفاده می کنند.

هنگامی که ما در محیط تولید کار می‌کنیم، بسیار مهم است که یک سیستم نظارت و هشدار داشته باشیم تا در صورت خراب شدن قسمتی یا شکست تست‌های کیفیت داده‌ها هشدار لازم را صادر کند. سیستم نظارت و هشدار شامل قسمت‌های زیر است:

• نظارت: توسط مهندسان و تحلیلگران برای بررسی وضعیت خط پردازش داده ((Pipeline استفاده می‌شود.
• هشدار: برای هشدار دادن به مهندسان و تحلیلگران در صورت خرابی استفاده می‌شود.
• زمان‌بندی: توسط مهندسان برای زمان‌بندی اجرای خط پردازش ETL استفاده می‌شود.

ETL در مقابل ELT

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

ELT مشابه ETL است، اما همان‌طور که از نام آن پیداست به ترتیبی متفاوت روال را انجام می‌دهد. با ظهور انبارهای داده قدرتمند مانند Snowflake، Bigquery، Redshift Spectrum که امکان جداسازی مرحله ذخیره‌سازی و مرحله اجرا را فراهم می‌کند. ذخیره داده‌ها در انبار داده و سپس تبدیل آن‌ها در صورت لزوم، مقرون به صرفه شده است. از آنجایی که این سیستم‌ها امکان ذخیره‌سازی راحت را فراهم می‌کنند و قادر به انجام طیف گسترده‌ای از تبدیل‌ها هستند. نیاز به سیستم تبدیل جداگانه نخواهد بود و به همین علت بسیار محبوب شده‌اند.

مفهوم (keep it simple, stupid) KISS

هنگام طراحی یک پلتفرم مهندسی داده، اکثر مهندسان به سراغ ابزارهای بسیار قدرتمندی می‌روند که ممکن است ضروری نباشد و به پیچیدگی طراحی و نگهداری می‌افزاید. در بیشتر موارد یک پلتفرم ساده مهندسی داده با ابزارهای راحت برای استفاده، بسیار کارآمدتر خواهد بود.

موارد زیر نکات مثبت استفاده از ابزارهای کارآمد بدون رفتن به سمت ابزار های پیچیده است:

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

سرویس منبع باز مدیریت شده در برابر ابزارهای منبع باز

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

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

ابزارهای Vendor lock-in

قطعا ابزارهای Vendor برای ELT بدون اشکال نیستند. Vendor lock-in زمانی است که خطوط پردازش داده شما بیش از حد به ابزارVendor وابسته می‌شود، به گونه‌ای که تغییرVendor یا انتقال به یک ابزار منبع باز، زمانی که نیازهای بسیار پیچیده‌ای دارید غیر عملی می‌شود. یک راه حل خوب این است که یک ارائه دهنده سرویس مدیریت شده را انتخاب کنید که مدیریت سرویس‌ها را انجام می‌دهد اما از یک سیستم منبع باز زیربنایی استفاده می‌کند. برای مثال stitch از song.io استفاده می‌کند، Astronomer سرویسAirflow مدیریت شده را پیشنهاد می‌دهد.

ساختن یک سیستم ELT ساده

در این بخش برای ساختن یک سیستم ELT از ارائه دهندگان سرویس‌هایی که ابزارهای منبع باز را برای ما مدیریت می‌کنند، یک سیستم ELT ساده می‌سازیم. هدف این پروژه این است که نشان دهد ساخت و مدیریت سریع یک پلتفرم مهندسی داده ساده بدون صرف زمان یا هزینه زیاد برای توسعه، عیب یابی یا مدیریت خدمات چقدر آسان است.

پیش‌ نیازها

• حساب کاربری AWS و راه‌اندازی باکت AWS S3.
• راه‌اندازی AWS RDS Postgres.
• حساب کاربری Stitch.
• حساب کاربری ابری DBT.
• Pgcli.

طراحی

شکل زیر طراحی سیستم ELT ما خواهد بود.

تنظیم داده‌ها

قبل از شروع به راه‌اندازی Stitch و تبدیل‌های dbt، باید داده‌های خود را تنظیم کنیم. در این مثال از یک AWS RDS Postgres به عنوان پایگاه داده منبع و همچنین انبار داده خود استفاده خواهیم کرد. در بیشتر موارد، Postgres همراه با مدل داده مناسب برای یک انبار داده به خوبی کار می‌کند، مگر اینکه داده‌های شما واقعاً بزرگ باشد (تقریباً بیش از ۲۰۰ گیگابایت، که ممکن است بسته به نوع ماشین مورد استفاده برای اجرای Postgres رفتار متفاوتی داشته باشد).

داده‌ها را در AWS S3 آپلود کنید

در پروژه ما فرض می‌کنیم که یک Vendor داده، اطلاعات مشتری را در یک باکت S3 می‌ریزد، به منظور ایجاد رونوشت آن، باید داده‌ها را در باکت S3 که ایجاد کرده‌اید آپلود کنیم. مسیر فایل S3 را بعداً وقتی Stitch را راه‌اندازی کردیم به آن نیاز خواهیم داشت.

شِماها و جداول order_status را در نمونه postgres خود ایجاد کنید

پس از تنظیم نمونه AWS RDS Postgres، می‌توانید شِماها و جدولorder را ایجاد کنید. با استفاده از ابزار pgcli وارد نمونه postgres خود شوید:

pgcli -h <your-postgres-endpoint> -U <your-username> -p 5432 -d <your-db-name>
CREATE SCHEMA app;
CREATE SCHEMA report;
CREATE TABLE IF NOT EXISTS app.order(
order_id varchar(50),
customer_order_id varchar(50),
order_status varchar(20),
order_purchase_timestamp timestamp
);
INSERT INTO app.order(order_id, customer_order_id, order_status, order_purchase_timestamp)
VALUES ('e481f51cbdc54678b7cc49136f2d6af7','9ef432eb6251297304e76186b10a928d','delivered','2020-07-01 10:56:33');
INSERT INTO app.order(order_id, customer_order_id, order_status, order_purchase_timestamp)
VALUES ('53cdb2fc8bc7dce0b6741e2150273451','b0830fb4747a6c6d20dea0b8c802d7ef','delivered','2020-07-02 20:41:37');
INSERT INTO app.order(order_id, customer_order_id, order_status, order_purchase_timestamp)
VALUES ('6942b8da583c2f9957e990d028607019','52006a9383bf149a4fb24226b173106f','shipped','2020-07-03 11:33:07');

راه‌اندازی ادغام EL

در این بخش ما یک خط پردازش داده EL (Extract and Load) را با استفاده از stitch راه‌اندازی می‌کنیم.

EL برای AWS S3 انبار داده

برای راه‌اندازی AWS S3 یکپارچه با استفاده از Stitch، مراحل را با پارامترهای زیر دنبال می‌کنیم:

• مسیر دایرکتوری S3 و جدا کننده فایل.
• جزئیات اتصال انبار داده (نقطه پایانی، پورت، نام کاربری، رمز عبور و نام پایگاه داده).
• نام شمای انبار داده مقصد را به عنوان vendordata و نام جدول را به عنوان customer در نظر می‌گیریم.
• فرکانس اجرا را می‌توان روی ۲ دقیقه تنظیم کرد.

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

پس از چند دقیقه منتظر ماندن، اطمینان حاصل کنید از اینکه بارگذاری به طور کامل انجام شده است و ستون‌ها را نمایش می‌دهد (این مورد را در داشبورد stitch بررسی کنید). سپس وارد انبار داده خود شده و دستور select را برای بررسی داده‌های بارگذاری شده اجرا کنید.

pgcli -h <your-pg-endpoint> -U <your-user-name> -p 5432 -d <your-DB-name>
select count(*) from vendordata.customer;

می‌توانید فرکانس اجرا را با انتخابintegration وsettings و استفاده از یک custom schedule تغییر دهید

EL برای پایگاه داده اپلیکیشن انبار داده

برای راه‌اندازی پایگاه داده اپلیکیشن برای ادغام با انبار داده با استفاده از Stitch، با پارامترهای زیر، مراحل را دنبال می‌کنیم.
• جزئیات اتصال پایگاه داده منبع (نقطه پایانی، پورت، نام کاربری، رمز عبور و نام پایگاه داده).
• جدول منبع را برای رونوشت داده‌ها به نام app.order در نظر می‌گیریم.
• نام شِمای مقصد را Operation در نظر می‌گیریم.
• جزئیات اتصال انبار داده (نقطه پایانی، پورت، نام کاربری، رمز عبور و نام پایگاه داده) که البته در قسمت قبل یک‌بار وارد کرده‌ایم.
• فرکانس اجرا را روی ۱۰ دقیقه تنظیم می‌کنیم.
هنگامی که خط پردازش داده stitch را روشن کردید، از طریق رابط کاربری مشاهده خواهید کرد که استخراج و بارگذاری داده‌ها کامل شده است.

پس از چند دقیقه منتظر ماندن و اطمینان از اینکه بخش بارگذاری داده‌ها کامل شده است، می‌توانید با ورود به انبار داده، داده‌های بارگذاری شده را بررسی کنید.

pgcli -h <your-pg-endpoint> -U <your-user-name> -p 5432 -d <your-DB-name>
select count(*) from operation.order;

راه‌اندازی مرحله تبدیل (T) در خط پردازش

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

راه‌اندازی یک پروژه dbt

در کنسول ابری dbt، روی آیکنhamburger در قسمت بالا سمت چپ کلیک کنید و سپس Account settings، Projets و در نهایت New Project را کلیک کنید.

Account settings -> Projects-> New Project

برای ساختن یک سیستم ELT یک پروژه در نمونه Postgres و یک مخزن کد برای پروژه، مراحل زیر را دنبال کنید:

این روال یک پروژه جدید با مخزن کد آن ایجاد می‌کند که توسط ابر dbt مدیریت می‌شود. اکنون روی دکمه Start Developing کلیک کنید تا کد نویسی پروژه شما آغاز شود. مراحل زیر را برای مقداردهی اولیه پروژه (initialize your project) و ایجاد فولدر مرحله‌بندی (staging) دنبال کنید.

 ایجاد مدل‌ها

dbt از کوئری‌های select برای ایجاد مدل‌ها استفاده می‌کند (مدل‌ها بسته به تنظیمات می‌توانند جداول، viewها، CTE ها باشند، برای مثال این مقاله از viewهای پیش‌فرض استفاده می‌کنیم).
عموماً داده‌های خام را به صورت staging نگه می‌داریم و روی این جداول تبدیل‌ها را انجام می‌دهیم تا جداول نهایی را بدست آوریم. فولدر staging را در قسمت قبل ایجاد کردیم، حالا یک فولدرfinal ایجاد کنید.
یک مدل برای داده‌های جدول customer ایجاد می‌کنیم، یک فایل به نام stg_customer.sql در مسیر location models/staging/stg_customer.sql با محتوای زیر ایجاد می‌کنیم.

with source as (
select * from vendordata.customer
),
stage_customer as (
select
customer_order_id,
customer_unique_id,
customer_zip_code_prefix,
customer_city,
customer_st
from source
)
select
*
from stage_customer

سپس در گام بعدی، مدلی برای داده‌های order ایجاد می‌کنیم، یک فایل به نام stg_order.sql در مسیر location models/staging/stg_order.sql با محتوای زیر ایجاد می‌کنیم.

with source as (
select
*
from operation.order
),
stage_orders as (
select distinct
order_id,
customer_order_id,
order_status,
order_purchase_timestamp
from source
)
select
*
from stage_orders

اکنون که مدل‌های staging را داریم، قبل از ایجاد مدل نهایی، کدی را برای بررسی کیفیت مدل‌های staging ایجاد می‌کنیم. فایلی به نام schema.yml را در مسیر models/staging/schema.yml با محتویات زیر ایجاد می‌کنیم.

version: 2
models:
- name: stg_customer
columns:
- name: customer_unique_id
tests:
- not_null
- name: stg_order
columns:
- name: order_id
tests:
- unique
- not_null
- name: customer_order_id
tests:
- unique
- not_null
- name: order_status
tests:
- accepted_values:
values:
[
'delivered',
'shipped'
]

فایل yml بالا موارد زیر را مورد بررسی قرار می‌دهد

• فیلد customer_unique_id از مدل stg_customer نباید مقادیرnull داشته باشد.
• فیلد order_id از مدل stg_order نباید مقادیر null داشته باشد و همچنین باید منحصر به فرد باشد.
• فیلد customer_order_id از مدل stg_order نباید مقادیر null داشته باشد و همچنین باید منحصر به فرد باشد.
• فیلد order_status از مدل stg_order فقط باید مقادیرdelivered یا shipped داشته باشد.

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

در گام بعدی در نهایت می‌توانیم مدل نهایی خود را ایجاد کنیم. یک فایل customer_order.sql در مسیر location models/final/customer_order.sql با محتوای زیر ایجاد کنید.

with customer as (
select
*
from {{ ref('stg_customer') }}
),
orders as (
select
*
from {{ ref('stg_order') }}
),
final as (
select
customer.customer_unique_id,
orders.order_id,
case
orders.order_status
when 'delivered' then 1
else 0
end
as order_delivered
from orders
inner join customer on orders.customer_order_id = customer.customer_order_id
)
select
*
from final

در اسکریپت SQL بالا، از تابع ref استفاده می‌کنیم که وابستگی را با استفاده از فایل‌های stg_customer و stg_order تشخیص می‌دهد. ما یک مدل customer_order ساده می‌سازیم که وضعیت سفارش را به عنوان یک flag ارائه می‌کند. ما می‌توانیم برخی از بررسی‌های کیفیت را نیز برای این کار اضافه کنیم. یک مورد آزمایشی ساده را با ایجاد فایل schema.yml در مسیر models/final/schema.yml با محتوای زیر ایجاد می‌کنیم.

version: 2
models:
- name: customer_order
columns:
- name: customer_unique_id
tests:
- not_null

ساختار نهایی دایرکتوری پروژه باید شبیه عکس زیر باشد:

همان‌طور که در زیر نشان داده شده است، می‌توانید با استفاده از دکمه run، دستورات select را امتحان کنید.

حتما دکمه save را کلیک کنید و سپس commit کنید.

راه‌اندازی سیستم زمان‌بندی، نظارت و هشدار

اکنون که خط پردازش داده ELT خود را تعریف کرده‌ایم و مراحل EL (استخراج و بارگذاری داده) را با فرکانس ۱۰ دقیقه اجرا می‌کنیم، زمان آن است که مرحله T (تبدیل داده) را نیز زمان‌بندی کنیم. اما ابتدا باید یک محیط تولید برای اجرای آن تعریف کنیم. روی آیکن hamburger کلیک کنید و روال زیر را پیگیری کنید.

hamburger icon -> Environments -> New Environment

کاری به نامsample_job ایجاد می‌کنیم، آن را طوری تنظیم می‌کنیم که هر ۱۵ دقیقه با استفاده از رابط کاربری اجرا شود، با زمان‌بندی cron، هر ۱۵ دقیقه به صورت */۵ * * * * نشان داده می‌دهد. روی نماد hamburger کلیک کنید و مسیر hamburger icon -> Jobs -> New Job را برای ایجاد کار جدید طی کنید. حتماً دستور dbt test را به بخش دستورات خود اضافه کنید و سپس ذخیره کنید.

می‌توانیم اجراهای خود را در رابط کاربری نظارت کنیم:

پس از اتمام اجرا، انبار داده را بررسی کنید، با استفاده از دستور زیر وارد سیستم شوید:

pgcli -h <your-pg-endpoint> -U <your-user-name> -p 5432 -d <your-DB-name>

با استفاده از دستور زیر گزارش را بررسی کنید:

select * from report.customer_order limit 3;

ما همچنین می‌توانیم هشدارها را در صورت شکست تست‌هایمان با تعیین ایمیل یا slack تنظیم کنیم. اعلان‌ها را با استفاده از مسیر Hamburger Icon -> Account settings -> Notifications تنظیم می‌کنید. در اینجا می‌توانید مشخص کنید که چه نوع اعلان‌هایی برای پروژه‌های خود می‌خواهید.ویژگی‌های اعلان را با ایجاد یک خطای آزمایشی می‌توانیم امتحان کنیم. وارد پایگاه داده برنامه خود شوید و دستورات زیر را اجرا کنید:

pgcli -h <your-pg-endpoint> -U <your-user-name> -p 5432 -d <your-DB-name>
INSERT INTO app.order(order_id, customer_order_id, order_status, order_purchase_timestamp)
VALUES ('f3e7c359154d965827355f39d6b1fdac', '62b423aab58096ca514ba6aa06be2f98','blah','2020-07-04 11:44:40');

صبر کنید تا داده‌ها در شِمای operations بارگذاری شوند و سپس اجرای dbt شروع شود، خواهید دید که با شکست مواجه می‌شوید زیرا مقدار ‘blah’ برای ستون order_status مجاز نیست و برای شما یک هشدار به ایمیل می‌فرستد یا اگر روی Slack تنظیم کرده‌اید هشدار می‌دهد.

بازبینی طرح پروژه

هدف از این پروژه ساختن یک سیستم ELT که به شما نشان دهد چقدر آسان است که یک خط پردازش داده ELT با استفاده از سرویس‌های مدیریت شده، راه‌اندازی کنید. بهینه‌سازی‌های زیادی وجود دارد که می‌توانید برای بهبود این خط پردازش داده ELT انجام دهید، که ممکن است بخواهید هنگام استفاده از آن برای پروژه‌های واقعی در نظر بگیرید.

Snapshot: اگر به جدول order دقت کنید، می‌توانیم متوجه شویم که یک بعد (ستون) است که تغییر می‌کند، یعنی shipped -> delivered. در چنین مواردی بهتر است از الگوی SCD2 استفاده کنیم. این کار را می‌توان با استفاده از ویژگی snapshot dbt انجام داد.
code repo: در مثال این مقاله ما مستقیماً از رابط کاربری برای ایجاد مدل‌ها و نوشتن موارد آزمایشی استفاده کردیم، که این روال، ایده ال نیست. بهتر است یک مخزن کد در گیت هاب و غیره داشته باشید و پیش از ادغام آن به شاخه اصلی که خطوط لوله داده تولید را کنترل می‌کند، از فرآیند انتشار ویژگی استاندارد[۱] پیروی کنید.
dbt best practices: هنگام استفاده از dbt بهترین روش‌ها را می‌توانید از لینک زیر تحقیق کنید:

سخن پایانی

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