理想的数据仓库是根据什么原则构建的？
在没有样板代码的情况下专注于业务价值和分析。 将 DWH 作为代码库进行管理：版本控制、审查、自动化测试和 CI。 模块化、可扩展、开源和社区。 用户友好的文档和依赖关系可视化（数据沿袭）。
有关这一切以及 DBT 在大数据和分析生态系统中的作用的更多信息 - 欢迎来到 cat。
大家好
Artemy Kozyr 已与您联系。 5 年多来，我一直致力于数据仓库、构建 ETL/ELT 以及数据分析和可视化。 我目前工作于 ，我在 OTUS 教授一门课程 ，今天我想跟大家分享一篇我为期待开学而写的文章 该课程的新注册人数.
简介
DBT 框架的核心就是 ELT（Extract - Transform - Load）首字母缩略词中的 T。
随着 BigQuery、Redshift、Snowflake 等高效且可扩展的分析数据库的出现，在数据仓库之外进行转换已经没有意义。
DBT 不会从源下载数据，但提供了处理已加载到存储（内部或外部存储）的数据的绝佳机会。
DBT的主要目的是获取代码，将其编译成SQL，在存储库中以正确的顺序执行命令。
DBT项目结构
该项目仅由两种类型的目录和文件组成：
- 模型 (.sql) - 由 SELECT 查询表示的转换单元
- 配置文件 (.yml) - 参数、设置、测试、文档
在基本层面上，工作结构如下：
- 用户在任何方便的 IDE 中准备模型代码
- 使用CLI启动模型，DBT将模型代码编译为SQL
- 编译后的SQL代码按照给定的顺序在Storage中执行（图）
从 CLI 运行可能如下所示：
一切都是选择
这是数据构建工具框架的杀手级功能。 换句话说，DBT 抽象了与将查询具体化到存储中相关的所有代码（命令 CREATE、INSERT、UPDATE、DELETE ALTER、GRANT 的变体，...）。
任何模型都涉及编写一个定义结果数据集的 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和语言的混合体 （模板语言）。
该示例使用循环 生成表达式中指定的每种付款方式的金额 集。 该函数也被使用 文献 — 在代码中引用其他模型的能力：
- 编译期间 文献 将被转换为指向存储中的表或视图的目标指针
- 文献 允许您构建模型依赖图
亦即 为 DBT 增添了几乎无限的可能性。 最常用的是：
- If / else 语句 - 分支语句
- For 循环
- 变量
- 宏——创建宏
物化：表、视图、增量
物化策略是一种将模型数据结果集存储在存储中的方法。
基本来说就是：
- Table——Storage中的物理表
- View - 查看、Storage 中的虚拟表
还有更复杂的物化策略：
- 增量 - 增量加载（大型事实表）； 添加新行，更新更改的行，清除删除的行
- Ephemeral - 模型不会直接实现，而是作为 CTE 参与其他模型
- 您可以自己添加任何其他策略
除了物化策略之外，还有针对特定存储进行优化的机会，例如：
- 雪花：临时表、合并行为、表集群、复制授权、安全视图
- 红移：Distkey、Sortkey（交错、复合）、后期绑定视图
- BigQuery的：表分区和集群、合并行为、KMS 加密、标签和标签
- 火花：文件格式（parquet、csv、json、orc、delta）、partition_by、clustered_by、bucket、incremental_strategy
目前支持以下存储：
- Postgres的
- 红移
- BigQuery的
- 雪花
- 急板（部分）
- 火花（部分）
- Microsoft SQL Server（社区适配器）
让我们改进我们的模型：
- 让我们使其填充增量（Incremental）
- 让我们为 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
模型依赖图
它也是一个依赖树。 它也称为 DAG（有向无环图）。
DBT 基于所有项目模型的配置构建一个图，或者更确切地说，模型内的 ref() 链接到其他模型。 有了图表，您可以执行以下操作：
- 按正确的顺序运行模型
- 店面形成的并行化
- 运行任意子图
图形可视化示例：
图的每个节点都是一个模型；图的边由表达式 ref 指定。
数据质量和文档
除了生成模型本身之外，DBT 还允许您测试有关结果数据集的许多假设，例如：
- 非空
- 独特
- Reference Integrity - 参照完整性（例如orders表中的customer_id对应customers表中的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 脚本，而是为用户提供强大且功能丰富的手段来构建自己的转换并分发这些模块。
宏是一组构造和表达式，可以在模型中作为函数调用。 宏允许您根据 DRY（不要重复自己）工程原则在模型和项目之间重用 SQL。
宏示例：
{% 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 附带一个包管理器，允许用户发布和重用单个模块和宏。
这意味着能够加载和使用以下库：
- ：使用日期/时间、代理键、架构测试、透视/逆透视等
- 现成的服务展示模板，例如 и
- 用于特定数据存储的库，例如
- — 记录 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 -%}
)
这段代码的字面意思是：对于环境 开发、测试、CI 仅获取最近 3 天的数据，不再获取更多数据。 也就是说，在这些环境中运行速度会更快并且需要更少的资源。 在环境上运行时 刺 过滤条件将被忽略。
使用备用列编码实现
Redshift 是一个列式 DBMS，允许您为每个列设置数据压缩算法。 选择最佳算法可以减少 20-50% 的磁盘空间。
宏 将执行 ANALYZE COMPRESSION 命令，使用推荐的列编码算法、指定的分段键 (dist_key) 和排序键 (sort_key) 创建一个新表，将数据传输到其中，并在必要时删除旧副本。
宏签名：
{{ 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 来计算哈希值、电子邮件域和位掩码解码。
在任何执行环境（dev、test、prod）上创建 UDF 的宏示例：
{% 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，我们使用基于 PostgreSQL 的 Amazon Redshift。 对于 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 作为服务（托管服务）使用。 包括：
- 用于开发项目和模型的 Web IDE
- 作业配置和调度
- 简单便捷的日志获取
- 包含您的项目文档的网站
- 连接 CI（持续集成）
结论
准备和食用 DWH 变得像喝冰沙一样令人愉快且有益。 DBT 由 Jinja、用户扩展（模块）、编译器、执行器和包管理器组成。 通过将这些元素组合在一起，您可以获得数据仓库的完整工作环境。 如今，几乎没有比 DWH 更好的管理转型的方法了。
DBT 开发者遵循的信念表述如下：
- 代码，而不是 GUI，是表达复杂分析逻辑的最佳抽象
- 处理数据应适应软件工程的最佳实践（软件工程）
- 关键数据基础设施应作为开源软件由用户社区控制
- 不仅是分析工具，代码也将日益成为开源社区的财产
这些核心信念催生了当今超过 850 家公司使用的产品，并且它们构成了未来将创建的许多令人兴奋的扩展的基础。
对于那些感兴趣的人，这里有一段我几个月前在 OTUS 公开课中提供的公开课视频 - .
除了 DBT 和数据仓库之外，作为 OTUS 平台上数据工程师课程的一部分，我和我的同事还教授许多其他相关的现代主题的课程：
- 大数据应用的架构概念
- Spark 和 Spark Streaming 练习
- 探索加载数据源的方法和工具
- 在 DWH 中构建分析展示
- NoSQL 概念：HBase、Cassandra、ElasticSearch
- 监控和编排原则
- 最终项目：在指导支持下将所有技能整合在一起
参考文献：
- — 官方文档
- — DBT 作者之一的评论文章
- — YouTube，OTUS 公开课录制
- — 下一堂公开课是15年2020月XNUMX日
- ——奥图斯
- — 数据和分析的未来展望
- — 分析的演变和开源的影响
- — 使用 DBT 构建 CI 的原理
- — 练习，独立工作的逐步说明
- — Github，教育项目代码
来源： habr.com