如何通过DBT批量按日期分片BigQuery大表生成子表

方法一：自动生成单日期模型文件（推荐，便于后续维护单个分片表） #

步骤1：编写生成模型文件的宏 #

在你的DBT项目macros目录下新建generate_shard_models.sql，内容如下：

{% macro generate_shard_models(start_date, end_date) %}
    {% set date_list = dbt_utils.date_spine(
        datepart="day",
        start_date="cast('" ~ start_date ~ "' as date)",
        end_date="cast('" ~ end_date ~ "' as date)"
    ) %}

    {% for date_record in date_list %}
        {% set date_suffix = date_record.date_day | dateformat("%Y%m%d") %}
        {% set model_name = 'lt_' ~ date_suffix %}
        {% set model_path = models_path ~ '/' ~ model_name ~ '.sql' %}

        {% set model_content %}
{{ config(alias='{{ model_name }}') }}

WITH final AS (
    SELECT * FROM {{ ref('lt') }}
    WHERE FORMAT_DATE("%Y%m%d", date) = '{{ date_suffix }}'
)

{{ dbt_audit(cte_ref='final') }}
        {% endset %}

        {% do write_file(model_path, model_content) %}
    {% endfor %}
{% endmacro %}

注意：如果未安装dbt_utils包，先在packages.yml中添加依赖并执行dbt deps安装：

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1

步骤2：执行宏生成所有模型文件 #

在终端运行以下命令，替换起始、结束日期为你的需求范围：

dbt run-operation generate_shard_models --args '{start_date: "2021-01-01", end_date: "2023-12-31"}'

执行后，models目录下会生成730个左右以lt_YYYYMMDD命名的模型文件。

步骤3：批量运行所有分片表模型 #

直接运行以下命令生成所有分片表：

dbt run --select lt_*

方法二：单个模型动态创建所有分片表（无需生成大量模型文件） #

步骤1：编写动态创建表的模型 #

在models目录下新建lt_shards.sql，内容如下：

{% set start_date = var('start_date') %}
{% set end_date = var('end_date') %}

{% set date_list = dbt_utils.date_spine(
    datepart="day",
    start_date="cast('" ~ start_date ~ "' as date)",
    end_date="cast('" ~ end_date ~ "' as date)"
) %}

{% for date_record in date_list %}
    {% set date_suffix = date_record.date_day | dateformat("%Y%m%d") %}
    {% set table_name = target.schema ~ '.lt_' ~ date_suffix %}

    EXECUTE IMMEDIATE '''
        CREATE OR REPLACE TABLE {{ table_name }}
        AS
        SELECT * FROM {{ ref('lt') }}
        WHERE FORMAT_DATE("%Y%m%d", date) = "{{ date_suffix }}";
    ''';

    -- 若需保留审计逻辑，将dbt_audit的逻辑适配到动态SQL中，例如：
    EXECUTE IMMEDIATE '''
        INSERT INTO {{ target.schema }}.dbt_audit_log
        SELECT 
            "{{ table_name }}" AS table_name,
            CURRENT_TIMESTAMP() AS created_at,
            (SELECT COUNT(*) FROM {{ table_name }}) AS row_count;
    ''';
{% endfor %}

-- 模型需返回结果，这里返回创建状态
SELECT '所有分片表创建完成' AS status;

步骤2：配置日期变量 #

在dbt_project.yml中添加日期范围变量：

vars:
  start_date: '2021-01-01'
  end_date: '2023-12-31'

步骤3：运行模型生成所有分片表 #

执行以下命令即可一次性创建所有分片表：

dbt run --model lt_shards

内容的提问来源于stack exchange，提问作者ΑΘΩ