Kho dữ liệu lý tưởng được xây dựng dựa trên những nguyên tắc nào?

Tập trung vào giá trị kinh doanh và phân tích khi không có mã soạn sẵn. Quản lý DWH dưới dạng cơ sở mã: lập phiên bản, đánh giá, kiểm tra tự động và CI. Mô-đun, mở rộng, nguồn mở và cộng đồng. Tài liệu thân thiện với người dùng và trực quan hóa phần phụ thuộc (Dòng dữ liệu).

Tìm hiểu thêm về tất cả những điều này và về vai trò của DBT trong hệ sinh thái Dữ liệu lớn & Phân tích - chào mừng bạn đến với cat.

Xin chào mọi người

Artemy Kozyr đang liên lạc. Trong hơn 5 năm, tôi đã làm việc với kho dữ liệu, xây dựng ETL/ELT, cũng như phân tích và trực quan hóa dữ liệu. Tôi hiện đang làm việc tại Bất kể, Tôi giảng dạy tại OTUS trong một khóa học Kỹ sư dữ liệu, và hôm nay tôi muốn chia sẻ với các bạn một bài báo mà tôi đã viết trước khi bắt đầu tuyển sinh mới cho khóa học.

Tổng quan

Khung DBT hoàn toàn là về chữ T trong từ viết tắt ELT (Trích xuất - Chuyển đổi - Tải).

Với sự ra đời của các cơ sở dữ liệu phân tích hiệu quả và có thể mở rộng như BigQuery, Redshift, Snowflake, việc thực hiện các chuyển đổi bên ngoài Kho dữ liệu chẳng ích gì.

DBT không tải xuống dữ liệu từ các nguồn nhưng cung cấp cơ hội tuyệt vời để làm việc với dữ liệu đã được tải vào Bộ nhớ (trong Bộ nhớ trong hoặc Bộ nhớ ngoài).

Mục đích chính của DBT là lấy mã, biên dịch thành SQL, thực thi các lệnh theo đúng trình tự trong Kho lưu trữ.

Cấu trúc dự án DBT

Dự án bao gồm các thư mục và tập tin chỉ có 2 loại:

Model (.sql) - một đơn vị chuyển đổi được biểu thị bằng truy vấn SELECT
Tệp cấu hình (.yml) - tham số, cài đặt, kiểm tra, tài liệu

Ở cấp độ cơ bản, công việc được cấu trúc như sau:

Người dùng chuẩn bị mã mô hình trong bất kỳ IDE thuận tiện nào
Sử dụng CLI, các mô hình được khởi chạy, DBT biên dịch mã mô hình thành SQL
Mã SQL đã biên dịch được thực thi trong Bộ lưu trữ theo một trình tự nhất định (biểu đồ)

Đây là giao diện chạy từ CLI:

Mọi thứ đều được CHỌN

Đây là một tính năng nổi bật của khung Công cụ xây dựng dữ liệu. Nói cách khác, DBT tóm tắt tất cả mã liên quan đến việc cụ thể hóa các truy vấn của bạn vào Cửa hàng (các biến thể từ các lệnh TẠO, CHÈN, CẬP NHẬT, XÓA ALTER, CẤP, ...).

Bất kỳ mô hình nào cũng liên quan đến việc viết một truy vấn CHỌN để xác định tập dữ liệu kết quả.

Trong trường hợp này, logic chuyển đổi có thể là dữ liệu đa cấp và hợp nhất từ một số mô hình khác. Một ví dụ về mô hình sẽ xây dựng nơi trưng bày đơn hàng (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

Chúng ta có thể thấy điều thú vị gì ở đây?

Đầu tiên: Đã sử dụng CTE (Biểu thức bảng chung) - để sắp xếp và hiểu mã chứa nhiều phép biến đổi và logic nghiệp vụ

Thứ hai: Mã mô hình là sự kết hợp giữa SQL và ngôn ngữ Jinja (ngôn ngữ khuôn mẫu).

Ví dụ sử dụng một vòng lặp cho để tạo số tiền cho mỗi phương thức thanh toán được chỉ định trong biểu thức định. Chức năng này cũng được sử dụng ref - khả năng tham chiếu các mô hình khác trong mã:

Trong quá trình biên soạn ref sẽ được chuyển đổi thành con trỏ đích thành bảng hoặc dạng xem trong Bộ lưu trữ
ref cho phép bạn xây dựng biểu đồ phụ thuộc mô hình

Chính xác Jinja thêm khả năng gần như không giới hạn cho DBT. Những cái được sử dụng phổ biến nhất là:

Câu lệnh if/else - câu lệnh nhánh
Đối với vòng lặp
Biến
Macro - tạo macro

Vật chất hóa: Bảng, Xem, Tăng dần

Chiến lược cụ thể hóa là một cách tiếp cận theo đó tập dữ liệu mô hình kết quả sẽ được lưu trữ trong Bộ lưu trữ.

Về cơ bản thì đó là:

Bảng - bảng vật lý trong Kho lưu trữ
View - xem, bảng ảo trong Storage

Ngoài ra còn có các chiến lược vật chất hóa phức tạp hơn:

Tăng dần - tải tăng dần (của các bảng thực tế lớn); dòng mới được thêm vào, dòng thay đổi được cập nhật, dòng bị xóa sẽ bị xóa
Phù du - mô hình không trực tiếp hiện thực hóa mà tham gia với tư cách là CTE trong các mô hình khác
Bất kỳ chiến lược nào khác bạn có thể tự thêm vào

Ngoài các chiến lược hiện thực hóa, còn có các cơ hội tối ưu hóa cho các Kho lưu trữ cụ thể, ví dụ:

Snowflake: Bảng tạm thời, Hành vi hợp nhất, Phân cụm bảng, Sao chép cấp phép, Chế độ xem an toàn
Redshift: Distkey, Sortkey (xen kẽ, ghép), Chế độ xem ràng buộc muộn
BigQuery: Phân vùng & phân cụm bảng, Hành vi hợp nhất, Mã hóa KMS, Nhãn & Thẻ
Spark: Định dạng tệp (parquet, csv, json, orc, delta), phân vùng_by, clustered_by, xô, gia tăng_strategy

Các kho lưu trữ sau hiện được hỗ trợ:

Bưu điện
Redshift
BigQuery
Snowflake
Presto (một phần)
Tia lửa (một phần)
Microsoft SQL Server (bộ điều hợp cộng đồng)

Hãy cải thiện mô hình của chúng tôi:

Hãy làm cho phần lấp đầy của nó tăng dần (Tăng dần)
Hãy thêm phím phân đoạn và sắp xếp cho Redshift

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

Biểu đồ phụ thuộc mô hình

Nó cũng là một cây phụ thuộc. Nó còn được gọi là DAG (Đồ thị tuần hoàn có hướng).

DBT xây dựng một biểu đồ dựa trên cấu hình của tất cả các mô hình dự án, hay nói đúng hơn là các liên kết ref() trong các mô hình với các mô hình khác. Việc có biểu đồ cho phép bạn thực hiện những việc sau:

Chạy mô hình theo đúng trình tự
Song song hóa việc hình thành mặt tiền cửa hàng
Chạy một sơ đồ con tùy ý

Ví dụ về trực quan hóa biểu đồ:

Mỗi nút của đồ thị là một mô hình; các cạnh của đồ thị được xác định bởi biểu thức ref.

Chất lượng dữ liệu và tài liệu

Ngoài việc tự tạo mô hình, DBT cho phép bạn kiểm tra một số giả định về tập dữ liệu kết quả, chẳng hạn như:

Có giá trị
Độc đáo
Tính toàn vẹn tham chiếu - tính toàn vẹn tham chiếu (ví dụ: customer_id trong bảng đơn hàng tương ứng với id trong bảng khách hàng)
Phù hợp với danh sách các giá trị được chấp nhận

Có thể thêm các thử nghiệm của riêng bạn (kiểm tra dữ liệu tùy chỉnh), chẳng hạn như % độ lệch doanh thu với các chỉ số so với một ngày, một tuần, một tháng trước. Bất kỳ giả định nào được xây dựng dưới dạng truy vấn SQL đều có thể trở thành một thử nghiệm.

Bằng cách này, bạn có thể phát hiện những sai lệch và lỗi không mong muốn trong dữ liệu trong cửa sổ Warehouse.

Về mặt tài liệu, DBT cung cấp các cơ chế để thêm, tạo phiên bản và phân phối siêu dữ liệu cũng như nhận xét ở cấp mô hình và thậm chí cả cấp thuộc tính.

Sau đây là cách thêm các bài kiểm tra và tài liệu ở cấp độ tệp cấu hình:

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

Và đây là giao diện của tài liệu này trên trang web được tạo:

Macro và mô-đun

Mục đích của DBT không phải là trở thành một tập hợp các tập lệnh SQL mà là cung cấp cho người dùng một phương tiện mạnh mẽ và giàu tính năng để xây dựng các phép biến đổi của riêng họ và phân phối các mô-đun này.

Macro là tập hợp các cấu trúc và biểu thức có thể được gọi là hàm trong mô hình. Macro cho phép bạn sử dụng lại SQL giữa các mô hình và dự án theo nguyên tắc kỹ thuật DRY (Đừng lặp lại chính mình).

Ví dụ vĩ mô:

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

Và công dụng của nó:

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

DBT đi kèm với trình quản lý gói cho phép người dùng xuất bản và sử dụng lại các mô-đun và macro riêng lẻ.

Điều này có nghĩa là có thể tải và sử dụng các thư viện như:

dbt_utils: làm việc với Ngày/Giờ, Khóa thay thế, kiểm tra lược đồ, Pivot/Unpivot và các thứ khác
Các mẫu giới thiệu được tạo sẵn cho các dịch vụ như Đồ ủi tuyết и Đường sọc
Thư viện dành cho các Kho dữ liệu cụ thể, ví dụ: Redshift
Logging — Mô-đun ghi nhật ký hoạt động DBT

Một danh sách đầy đủ các gói có thể được tìm thấy tại trung tâm dbt.

Thậm chí nhiều tính năng hơn

Ở đây tôi sẽ mô tả một số tính năng và cách triển khai thú vị khác mà tôi và nhóm sử dụng để xây dựng Kho dữ liệu trong Bất kể.

Tách môi trường thời gian chạy DEV - TEST - PROD

Ngay cả trong cùng một cụm DWH (trong các sơ đồ khác nhau). Ví dụ: sử dụng biểu thức sau:

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

Mã này có nghĩa đen là: dành cho môi trường dev, kiểm tra, ci chỉ lấy dữ liệu trong 3 ngày qua và không còn nữa. Nghĩa là, việc chạy trong những môi trường này sẽ nhanh hơn nhiều và cần ít tài nguyên hơn. Khi chạy trên môi trường sản phẩm điều kiện lọc sẽ bị bỏ qua.

Vật chất hóa với mã hóa cột thay thế

Redshift là một DBMS dạng cột cho phép bạn đặt thuật toán nén dữ liệu cho từng cột riêng lẻ. Việc lựa chọn các thuật toán tối ưu có thể giảm dung lượng ổ đĩa từ 20-50%.

Macro redshift.compress_table sẽ thực thi lệnh ANALYZE COMPRESSION, tạo một bảng mới với các thuật toán mã hóa cột được đề xuất, các khóa phân đoạn được chỉ định (dist_key) và các khóa sắp xếp (sort_key), chuyển dữ liệu sang bảng đó và nếu cần, hãy xóa bản sao cũ.

Chữ ký vĩ mô:

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

Chạy mô hình ghi nhật ký

Bạn có thể đính kèm các hook vào mỗi lần thực thi mô hình, việc này sẽ được thực thi trước khi khởi chạy hoặc ngay sau khi quá trình tạo mô hình hoàn tất:

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

Mô-đun ghi nhật ký sẽ cho phép bạn ghi lại tất cả siêu dữ liệu cần thiết vào một bảng riêng biệt, sau đó có thể được sử dụng để kiểm tra và phân tích các tắc nghẽn.

Đây là giao diện của trang tổng quan dựa trên dữ liệu ghi nhật ký trong Looker:

Tự động hóa bảo trì lưu trữ

Nếu bạn sử dụng một số phần mở rộng chức năng của Kho lưu trữ đã sử dụng, chẳng hạn như UDF (Chức năng do người dùng xác định), thì việc tạo phiên bản cho các chức năng này, kiểm soát quyền truy cập và tự động tung ra các bản phát hành mới sẽ rất thuận tiện khi thực hiện trong DBT.

Chúng tôi sử dụng UDF trong Python để tính toán giá trị băm, miền email và giải mã mặt nạ bit.

Một ví dụ về macro tạo UDF trên bất kỳ môi trường thực thi nào (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 %}

Tại Wheely, chúng tôi sử dụng Amazon Redshift, dựa trên PostgreSQL. Đối với Redshift, điều quan trọng là phải thường xuyên thu thập số liệu thống kê trên các bảng và giải phóng dung lượng ổ đĩa - tương ứng là các lệnh ANALYZE và VACUUM.

Để thực hiện việc này, các lệnh từ macro redshift_maintenance được thực thi hàng đêm:

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

Đám mây DBT

Có thể sử dụng DBT như một dịch vụ (Dịch vụ được quản lý). Bao gồm:

Web IDE để phát triển dự án và mô hình
Cấu hình và lập kế hoạch công việc
Truy cập nhật ký đơn giản và thuận tiện
Trang web có tài liệu về dự án của bạn
Kết nối CI (Tích hợp liên tục)

Kết luận

Việc chuẩn bị và tiêu thụ DWH trở nên thú vị và có lợi như uống một ly sinh tố. DBT bao gồm Jinja, phần mở rộng người dùng (mô-đun), trình biên dịch, trình thực thi và trình quản lý gói. Bằng cách kết hợp các yếu tố này lại với nhau, bạn sẽ có được một môi trường làm việc hoàn chỉnh cho Kho dữ liệu của mình. Hầu như không có cách nào tốt hơn để quản lý quá trình chuyển đổi trong DWH ngày nay.

Niềm tin mà các nhà phát triển DBT tuân theo được hình thành như sau:

Mã, không phải GUI, là cách trừu tượng tốt nhất để thể hiện logic phân tích phức tạp
Làm việc với dữ liệu phải điều chỉnh các phương pháp hay nhất trong công nghệ phần mềm (Kỹ thuật phần mềm)

Cơ sở hạ tầng dữ liệu quan trọng phải được cộng đồng người dùng kiểm soát dưới dạng phần mềm nguồn mở
Không chỉ các công cụ phân tích mà cả mã cũng sẽ ngày càng trở thành tài sản của cộng đồng Nguồn mở

Những niềm tin cốt lõi này đã tạo ra một sản phẩm được hơn 850 công ty sử dụng ngày nay và chúng tạo thành nền tảng cho nhiều tiện ích mở rộng thú vị sẽ được tạo ra trong tương lai.

Đối với những người quan tâm, có một video về bài học mở mà tôi đã đưa ra cách đây vài tháng như một phần của bài học mở tại OTUS - Công cụ xây dựng dữ liệu cho bộ lưu trữ Amazon Redshift.

Ngoài DBT và Kho dữ liệu, là một phần của khóa học Kỹ sư dữ liệu trên nền tảng OTUS, tôi và các đồng nghiệp còn dạy các lớp về một số chủ đề hiện đại và có liên quan khác:

Khái niệm kiến trúc cho các ứng dụng dữ liệu lớn
Thực hành với Spark và Spark Streaming
Khám phá các phương pháp và công cụ để tải nguồn dữ liệu
Xây dựng phòng trưng bày phân tích trong DWH
Các khái niệm NoSQL: HBase, Cassandra, ElasticSearch
Nguyên tắc giám sát và điều phối
Dự án cuối cùng: tập hợp tất cả các kỹ năng lại với nhau dưới sự hỗ trợ cố vấn

Công cụ xây dựng dữ liệu hoặc những điểm chung giữa Data Warehouse và Smoothie