قسمت 2: همگام سازی داده های نرمال PostgreSQL با استفاده از خانه Clickhouse با استفاده از Airbyte + DBT

از سنگرهای معامله ای گرفته تا صعود تحلیلی: postgresql برای کلیک کردن با Airbyte و DBT

در قسمت 1 ، ما به دلایل اساسی که چرا کفش کردن مدل داده PostgreSQL شما مستقیماً در Clickhouse است ، دستور العمل لاغری تحلیلی است. ما نقاط قوت متضاد پایگاه داده های OLTP ردیف گرا مانند PostgreSQL و قدرتهای OLAP ستون گرا مانند Clickhouse را برجسته کردیم.

حال ، بیایید آستین های خود را جمع کنیم و آن نظریه را به یک راه حل ملموس و واقعی ترجمه کنیم. در این مقاله ، ما برای ساختن یک خط لوله داده قوی که یکپارچه همگام سازی داده های پردازش معاملات آنلاین عادی شما (OLTP) که در PostgreSQL ساکن هستند ، به یک طرح بسیار عملکردی و بهینه سازی شده برای پردازش تحلیلی آنلاین (OLAP) در داخل Clickhouse همگام سازی می کنیم.

همراهان قابل اعتماد ما در این سفر Airbyte برای تغییر ضبط داده ها (CDC) مبتنی بر مصرف و DBT (ابزار ساخت داده) برای تحولات ظریف و تکامل طرحواره ای زیرکانه خواهند بود.

طرح معماری ما

در اینجا یک نمایش بصری از جریان داده ای که ما در حال ساخت خواهیم بود آورده شده است:

مرحله 1: تعیین پایه – تعریف طرح منبع خود در PostgreSQL

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

-- users
CREATE TABLE users (
  id UUID PRIMARY KEY,
  name TEXT,
  email TEXT
);

-- orders
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  total_amount NUMERIC,
  created_at TIMESTAMPTZ
);

این ساختار عادی ، با جداگانه users وت orders جداول مرتبط با کلیدهای خارجی ، برای انجام عملیات معامله ای ایده آل است.

مرحله 2: تنظیم بادبان با Airbyte – داده های مصرف از Postgres

Airbyte به عنوان کشتی قابل اعتماد ما برای مصرف داده ها قدم می گذارد. پشتیبانی قوی آن از CDC (تغییر ضبط داده ها) از طریق ورود به سیستم PostgreSQL Writ-Ahead (WAL) به ما امکان می دهد تا تغییرات را در زمان واقعی در Clickhouse پخش کنیم. این رویکرد تأخیر کم را تضمین می کند و هر اصلاح شده در داده های منبع ما را ضبط می کند.

برای به دست آوردن این کار ، باید Airbyte را با موارد زیر پیکربندی کنید:

• منبع: به نمونه postgresql خود وصل شوید. اطمینان حاصل کنید که شما تکثیر منطقی را فعال کرده اید و یک شکاف تکثیر ایجاد کرده اید ، زیرا این پیش نیازهای CDC است.
• مقصد: نمونه کلیک خود را به عنوان مقصد پیکربندی کنید. برای انتقال داده های کارآمد ، مقصد HTTP را با فشرده سازی اهرم کنید.
• حالت همگام سازی: یک حالت همگام سازی را انتخاب کنید که با ردیابی تغییر از به روزرسانی های افزایشی پشتیبانی می کند. “افزایشی + ضمیمه” یا یک حالت اختصاصی “CDC” (در صورت وجود برای اتصال Postgres) گزینه های مناسبی هستند.

پس از پیکربندی موفقیت آمیز ، Airbyte داده های خام را در Clickhouse در جدول هایی به نام چیزی مانند:

• _airbyte_raw_users
• _airbyte_raw_orders

داده های موجود در این جداول به طور معمول به عنوان حباب های خام JSON ساخته می شوند که هر ردیف حاوی ابرداده و داده های واقعی است:

{
  "_ab_id": "a unique identifier for the Airbyte record",
  "_ab_emitted_at": "timestamp of when Airbyte processed the record",
  "data": {
    "id": "...",
    "user_id": "...",
    "...": "..."
  }
}

مرحله 3: ساخت و ساز بینش با DBT – تحول و تجزیه و تحلیل

اکنون مرحله مهمی در شکل گیری این داده های خام به یک نیروگاه تحلیلی است. اینجاست که DBT می درخشد. با اتصال DBT به نمونه Clickhouse (استفاده از آداپتورها مانند dbt-clickhouse) ، می توانید مدل های مبتنی بر SQL را برای استخراج ، تبدیل و بارگذاری داده ها در طرحواره مورد نظر خود بنویسید.

بیایید به یک نمونه DBT نمونه نگاه کنیم ، orders_flat.sql، که orders داده ها با پیوستن به آن با اطلاعات مربوطه از users جدول:

WITH raw_orders AS (
  SELECT
    JSONExtractString(_airbyte_data, 'id') AS order_id,
    JSONExtractString(_airbyte_data, 'user_id') AS user_id,
    toDecimal128OrZero(JSONExtractString(_airbyte_data, 'total_amount')) AS total_amount,
    parseDateTimeBestEffort(JSONExtractString(_airbyte_data, 'created_at')) AS created_at
  FROM _airbyte_raw_orders
),

enriched_orders AS (
  SELECT
    o.order_id,
    o.user_id,
    u.name AS user_name,
    u.email AS user_email,
    o.total_amount,
    o.created_at
  FROM raw_orders o
  LEFT JOIN (
    SELECT
      JSONExtractString(_airbyte_data, 'id') AS user_id,
      JSONExtractString(_airbyte_data, 'name') AS name,
      JSONExtractString(_airbyte_data, 'email') AS email
    FROM _airbyte_raw_users
  ) u ON o.user_id = u.user_id
)

SELECT * FROM enriched_orders

در این مدل:

• ما ابتدا زمینه های مربوطه را از داده های Raw JSON که توسط Airbyte گرفته شده است با استفاده از توابع JSON Clickhouse مانند استخراج می کنیم JSONExtractStringبشر ما همچنین ریخته گری نوع اصلی را انجام می دهیم.
• سپس ، ما به استخراج شده می پیوندیم orders داده ها با زمینه های مربوطه از users داده های مبتنی بر user_idبشر این داده ها را از بین می برد و اطلاعات مرتبط را در یک جدول واحد قرار می دهد.

مرحله 4: بهینه سازی سرعت در Clickhouse – پارتیشن بندی و تحقق

برای اینکه واقعاً قدرت تحلیلی Clickhouse را باز کنید ، باید جداول خود را برای عملکرد بهینه پرس و جو بسازیم. پارتیشن بندی و سفارش تکنیک های کلیدی است. بیایید ما را تحقق بخشیم enriched_orders با این بهینه سازی ها به یک جدول کلیک کنید:

CREATE TABLE orders_flat
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (user_id, created_at)
AS
SELECT * FROM enriched_orders;

در اینجا به همین دلیل این مهم است:

• ENGINE = MergeTree: این خانواده ای از موتورهای میز قدرتمند در Clickhouse است که برای پردازش داده های با کارایی بالا و تجزیه و تحلیل طراحی شده است.
• PARTITION BY toYYYYMM(created_at): پارتیشن بندی داده ها بر اساس سال و ماه created_at ستون به Clickhouse اجازه می دهد تا از داده های بی ربط به طور مؤثر در طول نمایش داده شود که توسط محدوده تاریخ فیلتر می شوند.
• ORDER BY (user_id, created_at): مشخص کردن یک کلید سفارش به Clickhouse سازماندهی داده ها در هر پارتیشن کمک می کند و امکان بازیابی سریعتر داده ها را برای نمایش داده شده هایی که توسط این ستون ها فیلتر یا مرتب سازی می شوند ، امکان پذیر می کند.
• AS SELECT * FROM enriched_orders: این جدول را ایجاد می کند و آن را با نتایج تحول DBT ما جمع می کند.

مرحله 5: از بین بردن قدرت تحلیلی – پرس و جو از داده های denormalized خود

با توجه به داده های غیرمجاز و بهینه سازی شده ما که اکنون در Clickhouse ساکن هستند ، می توانیم نمایش داده های تحلیلی را اجرا کنیم که به طور ممنوع در پایگاه داده نرمال PostgreSQL ما ، به ویژه در مجموعه داده های بزرگ ، کند خواهد بود.

به عنوان مثال ، برای شمارش سفارشات روزانه و محاسبه کل درآمد طی 30 روز گذشته:

SELECT
  toDate(created_at) AS order_date,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue
FROM orders_flat
WHERE created_at >= now() - INTERVAL '30 day'
GROUP BY order_date
ORDER BY order_date;

این پرس و جو ، با استفاده از قابلیت های ذخیره سازی و فهرست بندی ستونی Clickhouse ، تقریباً فوراً اجرا می شود و بینش های ارزشمندی را ارائه می دهد.

اتوماسیون خط لوله داده خود را با CI/CD

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

• با استفاده از ابر DBT: این سرویس مدیریت شده یک رابط مبتنی بر وب برای توسعه ، برنامه ریزی و نظارت بر پروژه های DBT شما فراهم می کند.
• اجرای خطوط لوله CI/CD: یکپارچه DBT خود را در خطوط لوله مداوم/استقرار مداوم/استقرار مداوم (CI/CD) خود (به عنوان مثال ، با استفاده از Gitlab CI ، Actions GitHub) اجرا کنید تا هر زمان که کد جدید ادغام شود یا به صورت برنامه ریزی شده ، تحولات را به طور خودکار تحریک کنید.
• قراردادهای داده و ثبت شمای (اختیاری): برای محیط های پیچیده تر ، اجرای قراردادهای داده یا استفاده از رجیستری طرحواره برای ردیابی و مدیریت تغییرات طرحواره در سیستم های PostgreSQL و Clickhouse خود را در نظر بگیرید و از تغییر تغییرات جلوگیری کنید.

GOTCHAS برای حرکت

در حالی که این الگوی قدرتمند است ، در اینجا برخی از مشکلات مشترک و راه حل های آنها وجود دارد:

افکار نهایی: یک الگوی قدرتمند

این معماری ، با استفاده از نقاط قوت PostgreSQL برای یکپارچگی معامله و کلیک برای سرعت تحلیلی ، ارکستر شده توسط Airbyte برای مصرف یکپارچه و DBT برای تحول زیبا ، یک راه حل قانع کننده برای خطوط لوله داده مدرن ارائه می دهد.

این به شما امکان می دهد:

• از بارهای کاری OLTP خود محافظت کنید در postgreSQL بدون به خطر انداختن عملکرد تحلیلی.
• تجزیه و تحلیل خواستار به موتور ستونی رعد و برق سریع Clickhouse.
• جدایی روشن از نگرانی ها را حفظ کنید، جلوگیری از پیچیدگی های تلاش برای قرار دادن بار کاری تحلیلی بر روی یک پایگاه داده معامله ای.
• زیرساخت داده های خود را ضد آینده کنید با اتخاذ ابزارهای جداشده و تخصصی.

با استفاده از Airbyte و DBT به عنوان متحدین ، ​​داده های شما به یک دارایی سیال تبدیل می شود ، به راحتی تبدیل می شود و برای هدایت بینش های معنی دار تجزیه و تحلیل می شود.

Next Up: به اشتراک گذاشتن گنجینه های تحلیلی خود

در قسمت 3 ، ما چگونگی افشای لایه OLAP Clickhouse دقیق خود را به عنوان یک API یا داشبورد تعبیه شده برای مشتریان ، بررسی خواهیم کرد ، همه در حالی که کنترل های دسترسی قوی و استراتژی های انتساب هزینه را انجام می دهند. با ما همراه باشید!