DBT

介绍

dbt（Data Building Tool）是一个开源工具，数据分析师和工程师可以通过编写 SELECT 语句来转换其仓库中的数据。
ByteHouse dbt 连接器可让用户使用 dbt 和 ByteHouse 构建自己的数据仓库生态系统。

先决条件

已安装了dbt和python。如果没有，请按照此指南。

• dbt v1.3.0或更高版本
• python v3.8或更高版本
• 创建ByteHouse帐户：您可以通过火山引擎官网创建ByteHouse帐户。

安装

创建一个新的存储库，在其中实例化Python虚拟环境。

mkdir dbt_bytehouse_demo
cd dbt_bytehouse_demo

python -m venv venv
source venv/bin/activate

使用下面的命令安装最新版本：

pip install dbt-bytehouse

或使用下面的命令安装开发版本：

pip install git+https://github.com/bytehouse-cloud/driver-py@main#egg=bytehouse-driver

最后，可以下面的命令来检查安装是否成功。

dbt --version

DBT项目设置

dbt_project.yml

每个dbt项目都需要一个dbt_project.yml文件， dbt通过此文件来判断目录是否是dbt项目。 dbt_project.yml 的一些常见配置是：

profiles.yml

当您从命令行调用dbt时，dbt解析您的dbt_project.yml并获得配置文件名称。dbt然后 检查您的profiles.yml文件是否有同名的配置文件。配置文件包含所有详细信息/凭据 需要连接到ByteHouse.dbt将在当前工作目录中搜索profiles.yml文件。
如果找不到，默认为~/. dbt/目录。

<profile-name>: 
  target: <target-name> 
  outputs: 
    <target-name>: 
      type: bytehouse 
      schema: <database-name> 
      user: bytehouse 
      password: <bytehouse-api-key> 
      driver: native 
      #optional fields 
      host: <hostname> 
      port: <port> 
      region: <region-name> 
      warehouse: <warehouse-name> 
      retries: 1 
      secure: True 
      connect_timeout: 10 
      send_receive_timeout: 300 
      custom_settings: <empty> 

连接和身份验证配置

所需参数：主机 端口 用户 密码

bytehouse_profile: 
  target: dev 
  outputs: 
    dev: 
      type: bytehouse 
      driver: native 
 
      # database 
      schema: $DATABASE_NAME 
 
      # target server address 
      host: $HOST_ADDRESS   
      port: $PORT_NUMBER 
 
      # account credentials 
      user: bytehouse 
      password: $API_KEY 
 
      # additional settings 
      secure: True 

项目初始化

dbt init命令将提示输入项目名称和数据库适配器，您必须在其中选择bytehouse。这将创建 一个包含项目名称、示例文件和dbt_project.yml配置文件的新文件夹，足以让您开始使用dbt。

dbt init

使用所需的身份验证和目标服务器凭据更新您的profiles.yml。

nano ~/.dbt/profiles.yml

由于dbt创建了一个与项目名称同名的新文件夹，请更改当前项目文件夹的目录。

cd dbt_bytehouse_demo

更新dbt_project.yml文件，使其profile_name与~/. dbt/profiles.yml相同。

nano dbt_project.yml

测试仓库连接

使用dbt debug命令来验证所需的依赖关系和仓库连接。如果成功，它会向您显示 “所有检查都通过了！”

dbt debug

数据集摄取

为了展示不同的dbt功能，我们将使用以下模式摄取一个小型imdb电影数据集。 可以在这里找到DDL&插入查询https://github.com/bytehouse-cloud/bytehouse-dbt/examples/data_loading.sql。 您可以使用ByteHouse SQL工作表来创建架构并插入数据集。

为了验证数据集准备是否成功，我们将执行以下查询来总结每个参与者他们的电影出场总数。

SELECT id,
  any(actor_name) as name,
  uniqExact(movie_id)    as num_movies,
  avg(rating)                as avg_rating,
  max(created_at) as updated_at
FROM (
  SELECT actors.id as id,
       concat(actors.first_name, ' ', actors.last_name) as actor_name,
       movies.id as movie_id,
       movies.rating as rating,
       created_at
  FROM  imdb.actors
        JOIN imdb.roles ON roles.actor_id = actors.id
        LEFT OUTER JOIN imdb.movies ON movies.id = roles.movie_id
)
GROUP BY id

结果集应该是这样的：

DBT模型

在dbt项目中，模型是位于模型/目录中的sql文件，其中包含SELECT语句指的是一个转换。模型文件的名称将引用之后的未来表/视图的名称dbt执行。当我们执行dbt run命令时，dbt会通过包装将这个模型直接构建到ByteHouse中模型的物化类型将决定实际的SQL该dbt将用于在仓库中创建模型。

schema.yml

该schema.yml文件将通过别名引用来定义表和列通过source（）函数用于不同的模型/宏。在模型/目录下创建schema.yml文件。

touch models/schema.yml

像这样定义我们的模型模式

version: 2sources:- name: imdb
  tables:  - name: actors
  - name: roles
  - name: movies

模型的物化类型

查看实体化

在视图物化的情况下，模型被转换为CREATE VIEW AS语句在ByteHouse。视图实例化不会存储实际数据，因此查询速度会比表实例化慢。 让我们创建模型/actor_insight.sql作为视图物化。

touch models/actors_insight.sql

让我们执行dbt run命令在ByteHouse中构建这个模型。

查询此视图，我们可以使用更简单的语法复制之前查询的结果：

SELECT * FROM imdb.actors_insight ORDER BY num_movies DESC;

表物化

在表实例化的情况下，您的模型将在每次dbt运行通过CREATE TABLE AS语句。

touch models/actors_insight_table.sql

我们可以使用我们以前的视图物化sql和配置更改来实现表物化sql。

{{ config(order_by='(updated_at, id, name)', materialized='table') }}

我们可以验证视图和表实例化是否生成相同的响应。

SELECT * FROM imdb.actors_insight_table ORDER BY num_movies DESC;

增量物化

对于我们之前的表具体化，dbt每次都会构造一个表来具体化模型或者复杂的转换，这在计算能力方面是多余和昂贵的。增量物化解决了这个问题。 第一次运行模型时，通过转换源数据的所有行来构建表。在后续运行中，dbt仅转换源数据中您告诉dbt过滤的行，并将它们插入到目标中表是已经构建的表。
要告诉dbt它应该在增量运行中转换哪些行，请包装过滤这些行的有效SQL在is_incremental（）宏中。您的is_incremental（）代码将检查自上次dbt运行这个模型。

touch models/actors_insight_incremental.sql

我们可以验证视图、表和增量具体化都生成相同的响应。

SELECT * FROM imdb.actors_insight_table ORDER BY num_movies DESC;

让我们再插入几行来演示增量具体化的强大功能。现在最演员应该是"Chris Pratt"。

INSERT INTO imdb.movies VALUES (9, 'Passengers', 2016, 7);
INSERT INTO imdb.movies VALUES (10, 'The Tomorrow War', 2021, 6.5);

INSERT INTO imdb.roles (actor_id, movie_id, role_name) VALUES(4, 9, 'Jim Preston');
INSERT INTO imdb.roles (actor_id, movie_id, role_name) VALUES(4, 10, 'Dan Forester');

它是如何工作的

1. dbt将首先创建一个名为actors_insight_incremental_tmp的临时表&插入所有这些行 通过我们的is_incremental（）过滤器。
2. 将从旧表中创建一个新表actors_insight_incremental_new&行actors_insight_incremental 将在此处摄取。dbt将确保保持unique_key（如果在config中声明）约束，不允许与前一个临时表具有相同unique_key的行。
3. 临时表中的行将被摄取到新表中。
4. 我们以前的桌子（actors_insight_incremental）和新的桌子（actors_insight_new）将被交换。

直接写入 ByteHouse Unique Key 表

必备条件

1. dbt-bytehouse 连接器版本至少 1.7.1及以上。
2. 需要设置名称为 cnch_unique_key ，而不是 unique_key。
3. 需要设置 incremental_strategy 为 append。
4. 编写SQL表达式将VM的id追加到DBT模型末尾，例如SETTINGS virtual_warehouse='vw-id', virtual_warehouse_write='vw-id'。

举例

{{
    config(
        materialized='incremental',
        cnch_unique_key='id',
        incremental_strategy='append'    
    )
}}

SELECT
    id,
    transaction_date,
    user_id,
    amount,
    status
FROM 
    dbt_unique.transactions 

{% if is_incremental() %}

  WHERE transaction_date >= (SELECT max(transaction_date) FROM dbt_unique.transactions)

{% endif %}

SETTINGS virtual_warehouse='vw-id', virtual_warehouse_write='vw-id'

项目文档

dbt提供了一种为dbt项目生成留档并将其呈现为网站的方法。 创建模型/actors_insight_incremental.yml为我们的模型生成留档。

version: 2models:  - name: actors_insight_incremental
    description: Actor appearance summary based on roles
    columns:       - name: id
        description: The id number of actor
      - name: name
        description: The name of actor
      - name: num_movies
        description: The number of movies actor has appeared 
      - name: avg_rating
        description: Average rating
      - name: updated_at
        description: Latest update time

使用dbt文档生成为您的模型生成留档&dbt文档服务于您在端口8000上的本地浏览器。

本地开发

更新测试/集成/confest.py文件以包含您的连接凭据这些步骤：

pip install -r dev_requirements.txt
python -m pytest

许可证

该项目是根据Apache许可证（2.0版）的条款分发的。