على أي مبادئ تم بناء مستودع بيانات مثالي؟
ركز على قيمة الأعمال والتحليلات في حالة عدم وجود رمز معياري. إدارة DWH كقاعدة بيانات: الإصدار والمراجعة والاختبار الآلي و CI. نمطية وقابلية للتوسع ومفتوحة المصدر ومجتمع. توثيق سهل للمستخدم وتصور التبعية (Data Lineage).
حول كل هذا بمزيد من التفصيل وحول دور DBT في النظام البيئي للبيانات الضخمة والتحليلات - مرحبًا بك ضمن cat.
مرحبا بالجميع
أرتمي كوزير على اتصال. لأكثر من 5 سنوات ، كنت أعمل مع مستودعات البيانات ، وبناء ETL / ELT ، بالإضافة إلى تحليلات البيانات والتصور. أنا أعمل حاليا في ، أقوم بالتدريس في OTUS في الدورة ، واليوم أود أن أشارككم مقالاً كتبته عشية الإطلاق تسجيل جديد للدورة.
ملخص
يدور إطار عمل DBT حول الحرف T في اختصار ELT (استخراج - تحويل - تحميل).
مع ظهور قواعد بيانات تحليلية منتجة وقابلة للتطوير مثل BigQuery و Redshift و Snowflake ، لم يكن هناك جدوى من إجراء تحويلات خارج مستودع البيانات.
لا يقوم DBT بتحميل البيانات من المصادر ، ولكنه يوفر فرصًا رائعة للعمل مع البيانات التي تم تحميلها بالفعل إلى التخزين (التخزين الداخلي أو الخارجي).
الغرض الرئيسي من DBT هو أخذ الكود وتجميعه إلى SQL وتنفيذ الأوامر بالتسلسل الصحيح في المستودع.
هيكل مشروع DBT
يتكون المشروع من أدلة وملفات من نوعين فقط:
- النموذج (.sql) - وحدة التحويل المعبر عنها باستعلام SELECT
- ملف التكوين (.yml) - معلمات ، إعدادات ، اختبارات ، وثائق
في المستوى الأساسي ، يتم بناء العمل على النحو التالي:
- يقوم المستخدم بإعداد رمز النموذج في أي بيئة تطوير متكاملة ملائمة
- باستخدام CLI ، يسمى إطلاق النماذج ، يقوم DBT بتجميع كود النماذج في SQL
- يتم تنفيذ كود SQL المترجم في التخزين بالتسلسل المحدد (الرسم البياني)
هذا ما قد يبدو عليه الإطلاق من CLI:
كل شيء هو SELECT
هذه ميزة قاتلة في إطار عمل أداة إنشاء البيانات. بعبارة أخرى ، يقوم DBT بإزالة جميع التعليمات البرمجية المتعلقة بتجسيد استفساراتك في المستودع (الاختلافات من أوامر إنشاء ، وإدراج ، وتحديث ، وحذف التغيير ، والمنح ، ...).
يتضمن أي نموذج كتابة استعلام SELECT واحد يحدد مجموعة البيانات الناتجة.
في الوقت نفسه ، يمكن أن يكون منطق التحويل متعدد المستويات ويدمج البيانات من عدة نماذج أخرى. مثال على نموذج سيبني عرضًا للأوامر (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
ما الأشياء الشيقة التي يمكننا رؤيتها هنا؟
أولاً: استخدام CTE (تعبيرات الجدول المشترك) - لتنظيم وفهم الكود الذي يحتوي على الكثير من التحولات ومنطق الأعمال
ثانيًا: كود النموذج هو خليط من SQL واللغة (لغة النمذجة).
يستخدم المثال حلقة For لتوليد المبلغ لكل طريقة دفع محددة في التعبير طقم. يتم استخدام الوظيفة أيضًا المرجع - إمكانية الرجوع داخل الكود إلى طرز أخرى:
- في وقت الترجمة المرجع سيتم تحويلها إلى مؤشر هدف إلى جدول أو عرض في التخزين
- المرجع يسمح لك ببناء رسم بياني تبعية للنماذج
بالضبط يضيف إمكانيات غير محدودة تقريبًا إلى DBT. الأكثر شيوعًا هي:
- عبارات if / else - بيانات الفرع
- للحلقات - الحلقات
- المتغيرات
- ماكرو - إنشاء وحدات ماكرو
تجسيد: جدول ، عرض ، تزايدي
استراتيجية التجسيد هي نهج يتم بموجبه تخزين المجموعة الناتجة من بيانات النموذج في التخزين.
بعبارات أساسية ، هذا هو:
- الجدول - الجدول المادي في التخزين
- عرض - عرض ، جدول افتراضي في التخزين
هناك أيضًا استراتيجيات تجسيد أكثر تعقيدًا:
- زيادة - تحميل تزايدي (جداول حقائق كبيرة) ؛ يتم إضافة سطور جديدة ، يتم تحديث الأسطر المتغيرة ، مسح الأسطر المحذوفة
- سريع الزوال - النموذج لا يتجسد بشكل مباشر ، لكنه يشارك باعتباره CTE في نماذج أخرى
- أي استراتيجيات أخرى يمكنك إضافتها بنفسك
بالإضافة إلى استراتيجيات التجسيد ، هناك فرص لتحسين أوضاع معينة ، على سبيل المثال:
- ندفة الثلج: الجداول العابرة ، سلوك الدمج ، تجميع الجداول ، نسخ المنح ، طرق العرض الآمنة
- الانزياح نحو الأحمر: Distkey ، Sortkey (معشق ، مركب) ، طرق عرض الربط المتأخر
- الاستعلام الشامل: تقسيم الجدول والتجميع ، سلوك الدمج ، تشفير KMS ، الملصقات والعلامات
- شرارة: تنسيق ملف (باركيه ، csv ، json ، orc ، delta) ، partition_by ، clustered_by ، buckets ، incremental_strategy
المستودعات التالية مدعومة حاليًا:
- بوستجرس
- الانزياح نحو الأحمر
- الاستعلام الشامل
- ندفة الثلج
- المعزوفة (جزئيًا)
- شرارة (جزئية)
- Microsoft SQL Server (محول المجتمع)
دعنا نحسن نموذجنا:
- لنجعل التعبئة تزايدي (تزايدي)
- أضف تجزئة وفرز المفاتيح للانزياح الأحمر
-- Конфигурация модели:
-- Инкрементальное наполнение, уникальный ключ для обновления записей (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
نموذج التبعية الرسم البياني
إنها شجرة تبعية. وهو أيضًا DAG (رسم بياني دوري موجه - رسم بياني دوري موجه).
يُنشئ DBT رسمًا بيانيًا بناءً على تكوين جميع نماذج المشروع ، أو بالأحرى روابط المرجع () داخل النماذج بنماذج أخرى. يتيح لك الرسم البياني القيام بالأمور التالية:
- النماذج الجارية في التسلسل الصحيح
- عرض موازاة التشكيل
- تشغيل رسم بياني فرعي تعسفي
مثال على تصور الرسم البياني:
كل عقدة في الرسم البياني عبارة عن نموذج ، يتم إعطاء حواف الرسم البياني بواسطة التعبير المرجع.
جودة البيانات والتوثيق
بالإضافة إلى تشكيل النماذج نفسها ، يسمح لك DBT باختبار عدد من الافتراضات (التأكيدات) حول مجموعة البيانات الناتجة ، مثل:
- غير فارغة
- التفرُّد
- تكامل المرجع - التكامل المرجعي (على سبيل المثال ، customer_id في جدول الطلبات يتوافق مع المعرف في جدول العملاء)
- مطابقة قائمة القيم الصالحة
من الممكن إضافة الاختبارات الخاصة بك (اختبارات البيانات المخصصة) ، مثل ، على سبيل المثال ، النسبة المئوية للانحراف في الإيرادات مع مؤشرات يوم أو أسبوع أو شهر مضى. أي افتراض تمت صياغته كاستعلام SQL يمكن أن يصبح اختبارًا.
بهذه الطريقة ، يمكن اكتشاف الانحرافات والأخطاء غير المرغوب فيها في البيانات في واجهات المحلات بالمستودع.
من حيث التوثيق ، يوفر DBT آليات لإضافة وإصدار وتوزيع البيانات الوصفية والتعليقات على النموذج وحتى مستويات السمات.
هذه هي الطريقة التي تبدو بها إضافة الاختبارات والوثائق على مستوى ملف التكوين:
- 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']
وإليك كيف تبدو هذه الوثائق بالفعل على موقع الويب الذي تم إنشاؤه:
وحدات الماكرو والوحدات النمطية
لا يتمثل الغرض من DBT في أن تكون مجموعة من نصوص SQL ، ولكن لتزويد المستخدمين بوسائل قوية وغنية بالميزات لبناء تحويلاتهم الخاصة وتوزيع هذه الوحدات.
وحدات الماكرو هي مجموعات من التركيبات والتعبيرات التي يمكن استدعاؤها كوظائف داخل النماذج. تسمح لك وحدات الماكرو بإعادة استخدام SQL بين النماذج والمشاريع وفقًا للمبدأ الهندسي DRY (لا تكرر نفسك).
مثال ماكرو:
{% 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 %}
واستخداماته:
{% set column_name = 'product' %}
select
product,
{{ rename_category(column_name) }} -- вызов макроса
from my_table
يأتي DBT مع مدير الحزم الذي يسمح للمستخدمين بنشر وإعادة استخدام الوحدات النمطية ووحدات الماكرو الفردية.
هذا يعني القدرة على تنزيل واستخدام المكتبات مثل:
- : العمل مع التاريخ / الوقت ، والمفاتيح البديلة ، واختبارات المخطط ، والمحور / Unpivot وغيرها
- قوالب عرض جاهزة لخدمات مثل и
- مكتبات لمخازن بيانات محددة ، على سبيل المثال
- - وحدة لتسجيل العمل DBT
يمكن العثور على قائمة كاملة بالحزم على .
المزيد من الميزات
سأصف هنا بعض الميزات والتطبيقات الأخرى المثيرة للاهتمام التي استخدمها أنا والفريق لبناء مستودع البيانات فيه .
فصل بيئات التنفيذ DEV - TEST - PROD
حتى داخل نفس مجموعة DWH (في ظل مخططات مختلفة). على سبيل المثال ، بالتعبير التالي:
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 -%}
)
يقول هذا الرمز حرفياً: للبيئات dev ، test ، ci خذ بيانات آخر 3 أيام فقط وليس أكثر. أي أن التشغيل في هذه البيئات سيكون أسرع بكثير وسيتطلب موارد أقل. عندما تعمل على البيئة همز سيتم تجاهل شرط التصفية.
التجسيد باستخدام ترميز العمود البديل
Redshift عبارة عن نظام DBMS عمودي يسمح لك بتعيين خوارزميات ضغط البيانات لكل عمود على حدة. يمكن أن يؤدي اختيار الخوارزميات المثلى إلى تقليل مساحة القرص المشغولة بنسبة 20-50٪.
ماكرو سيقوم بتنفيذ أمر ANALYZE COMPRESSION ، وإنشاء جدول جديد باستخدام خوارزميات ترميز العمود الموصى بها ، ومفاتيح التجزئة المحددة (مفتاح التوزيع) والفرز (مفتاح الفرز) ، ونقل البيانات إليها ، وإذا لزم الأمر ، حذف النسخة القديمة.
توقيع الماكرو:
{{ 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) }}
نموذج تشغيل التسجيل
لكل تنفيذ للنموذج ، يمكنك تعليق الخطافات (الخطافات) التي سيتم تنفيذها قبل الإطلاق أو فورًا بعد اكتمال إنشاء النموذج:
pre-hook: "{{ logging.log_model_start_event() }}"
post-hook: "{{ logging.log_model_end_event() }}"
ستسمح لك وحدة التسجيل بكتابة جميع البيانات الوصفية الضرورية في جدول منفصل ، بحيث يمكنك لاحقًا تدقيق وتحليل مناطق المشكلات (الاختناقات).
إليك كيف تبدو لوحة القيادة على بيانات التسجيل في Looker:
أتمتة التخزين
إذا كنت تستخدم بعض امتدادات وظائف التخزين المستخدمة ، مثل UDF (وظائف محددة من قبل المستخدم) ، فإن إصدار هذه الوظائف ، والتحكم في الوصول ، والطرح الآلي للإصدارات الجديدة يكون مناسبًا جدًا لتنفيذ DBT.
نستخدم Python UDF لحساب قيم التجزئة ونطاقات عناوين البريد وفك تشفير قناع البت.
مثال على الماكرو الذي ينشئ UDF في أي بيئة وقت تشغيل (dev ، test ، prod):
{% 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 ، نستخدم Amazon Redshift الذي يعتمد على PostgreSQL. من المهم أن يقوم Redshift بجمع إحصائيات حول الجداول بانتظام وإخلاء مساحة على القرص - الأمران ANALYZE و VACUUM ، على التوالي.
للقيام بذلك ، يتم تنفيذ الأوامر من الماكرو redshift_maintenance كل ليلة:
{% 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
من الممكن استخدام DBT كخدمة (خدمة مُدارة). يشمل:
- IDE الويب لتطوير المشاريع والنماذج
- تكوين الوظيفة وإعداد الجدول الزمني
- سهولة الوصول إلى السجلات
- موقع ويب مع توثيق مشروعك
- توصيل CI (التكامل المستمر)
اختتام
يصبح تحضير واستهلاك DWH ممتعًا وصحيًا مثل شرب عصير. يتكون DBT من Jinja والإضافات المخصصة (الوحدات النمطية) والمترجم والمحرك (المنفذ) ومدير الحزم. من خلال تجميع هذه العناصر معًا ، تحصل على بيئة عمل كاملة لمخزن البيانات الخاص بك. بالكاد توجد طريقة أفضل لإدارة التحولات داخل DWH اليوم.
تتم صياغة المعتقدات التي يتبعها مطورو DBT على النحو التالي:
- الكود ، وليس واجهة المستخدم الرسومية ، هو أفضل تجريد للتعبير عن المنطق التحليلي المعقد
- يجب أن يتكيف العمل مع البيانات مع أفضل ممارسات هندسة البرمجيات (هندسة البرمجيات)
- يجب أن يتحكم مجتمع المستخدمين في البنية التحتية للبيانات الهامة كبرنامج مفتوح المصدر
- ليس فقط أدوات التحليل ، ولكن أيضًا ستصبح الشفرة ملكًا لمجتمع المصادر المفتوحة بشكل متزايد
أدت هذه المعتقدات الأساسية إلى ظهور منتج تستخدمه أكثر من 850 شركة اليوم ، وهي تشكل الأساس للعديد من الإضافات المثيرة التي سيتم إنشاؤها في المستقبل.
بالنسبة لأولئك المهتمين ، هناك تسجيل فيديو لدرس مفتوح أجريته قبل بضعة أشهر كجزء من درس مفتوح في OTUS - .
بالإضافة إلى DBT ومستودعات البيانات ، كجزء من دورة مهندس البيانات على منصة OTUS ، أقوم أنا وزملائي بإجراء فصول دراسية حول عدد من الموضوعات الأخرى ذات الصلة والحديثة:
- المفاهيم المعمارية لتطبيقات البيانات الضخمة
- تدرب مع Spark و Spark Streaming
- طرق وأدوات التعلم لتحميل مصادر البيانات
- بناء واجهات المحلات التحليلية في DWH
- مفاهيم NoSQL: HBase و Cassandra و ElasticSearch
- مبادئ تنظيم المراقبة والتنسيق
- المشروع النهائي: وضع جميع المهارات معًا تحت دعم التوجيه
المراجع:
- - الوثائق الرسمية
- - مراجعة المقال من قبل أحد مؤلفي DBT
- - يوتيوب ، OTUS فتح تسجيل الدروس
- - أقرب درس مفتوح هو 15 مايو 2020
- - أوتوس
- - نظرة على مستقبل البيانات والتحليلات
- - تطور التحليلات وتأثير المصادر المفتوحة
- - مبادئ بناء CI باستخدام DBT
- - الممارسة ، تعليمات خطوة بخطوة للدراسة الذاتية
- - Github ، رمز المشروع التعليمي
المصدر: www.habr.com