使用dbt宏实现跨数据库表复制时遇SQL编译错误求助

针对Object 'TABLES' does not exist or not authorized报错 #

该错误源于直接查询系统视图INFORMATION_SCHEMA.TABLES时权限不足，或是未适配不同数据库的元数据视图差异。改用dbt内置函数替代原生系统表查询即可解决：

• 重写get_dimension_names宏，使用get_relations_by_pattern自动适配数据库语法并规避权限问题：

{% macro get_dimension_names(schema_name, dry_run=false) %}
  {% set relations = dbt_utils.get_relations_by_pattern(schema_name, '%') %}
  {% set table_names = [] %}
  {% for rel in relations %}
    {% do table_names.append(rel.identifier) %}
  {% endfor %}
  {% if dry_run %}
    {{ log("Dry run: 检测到表 - " ~ table_names|join(', '), info=True) }}
  {% endif %}
  {{ return(table_names) }}
{% endmacro %}

针对生成创建语句后的SQL编译错误 #

编译错误多因跨库表路径不完整、语法未适配目标数据库导致。调整宏逻辑，明确指定源/目标库的完整路径：

• 重写statement_list宏，确保SQL包含完整的库.模式.表名，并适配跨库复制逻辑：

{% macro statement_list(source_db, source_schema, target_db, target_schema) %}
  {% set table_names = generate_dimension_names(source_schema) %}
  {% for table in table_names %}
    {% set source_table = source_db ~ '.' ~ source_schema ~ '.' ~ table %}
    {% set target_table = target_db ~ '.' ~ target_schema ~ '.' ~ table %}
    CREATE OR REPLACE TABLE {{ target_table }}
    AS SELECT * FROM {{ source_table }}
    WHERE __IS_ROW_CURRENT = TRUE;
  {% endfor %}
{% endmacro %}

• 额外注意事项：
  • 确保dbt执行角色同时拥有源库读权限和目标库写权限
  • 若需增量复制，可添加基于时间戳或主键的增量逻辑，避免全量同步的性能损耗

验证步骤 #

1. 执行dry_run模式确认表列表准确性：

dbt run-operation get_dimension_names --args '{schema_name: "你的源模式名", dry_run: true}'

2. 运行宏完成跨库表复制：

dbt run-operation statement_list --args '{source_db: "源数据库名", source_schema: "源模式名", target_db: "目标数据库名", target_schema: "目标模式名"}'

内容的提问来源于stack exchange，提问作者Abhinav