dbt中如何通过for循环传递执行多条DROP TABLE语句

问题场景 #

需要在Snowflake中批量删除多张表，不想在dbt任务中逐条写删除命令传入表名，因此基于数据库INFORMATION_SCHEMA下的TABLES视图实现自动生成删除语句的方案。

最初构造DROP语句的SQL如下：

SELECT
    CONCAT('"DROP TABLE IF EXISTS ', CONCAT(CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME), '"'))
FROM "{{ var('db_name') }}"."INFORMATION_SCHEMA"."TABLES"
WHERE table_owner = '{{ var("table_owner") }}'
AND table_schema = '{{ var("schema_name") }}'
AND table_name LIKE '{{ var("table_name") }}'

第一个实现思路是把查询生成的所有DROP语句拼接成单个字符串赋值给变量，传给post_hook执行，但查阅社区说明可知post_hook仅接受字符串值，该方案无法实现批量执行逻辑。

之后调整实现逻辑：不拼接为单字符串，通过run_query拿到查询返回的语句列表，传入for循环逐行执行，完整代码如下：

{%- set generate_drop_table_statement -%}
SELECT
    CONCAT('"DROP TABLE IF EXISTS ', CONCAT(CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME), '"'))
FROM "{{ var('db_name') }}"."INFORMATION_SCHEMA"."TABLES"
WHERE table_owner = '{{ var("table_owner") }}'
AND table_schema = '{{ var("schema_name") }}'
AND table_name LIKE '{{ var("table_name") }}'
{%- endset -%}

{%- set get_drop_statements = run_query(generate_drop_table_statement) -%}

{%- if execute -%}
{%- set drop_statements = get_drop_statements.columns[0].values() -%}

{%- set exec_query -%}
    {{ log(drop_statements, info=True) }}
    
    {% for drop_statement in drop_statements %}
        {{ log(drop_statement, info=True) }}
        {%- do run_query(drop_statement) -%}
    {% endfor %}
{%- endset -%}

{%- else -%}
{%- set drop_statements = [] -%}
{%- endif -%}

{{ config (
    alias='DROP_STATEMENTS_TABLE',
    materialized='table',
    post_hook='{{ exec_query }}'
) }}

with statements as (
SELECT
    1 as id
)

SELECT *
FROM statements

通过日志打印代码{{ log(drop_statement, info=True) }}确认删除语句内容生成正确，但日志输出第一条语句后，执行到run_query步骤就触发失败，报错信息如下：

Tried to run an empty query on model 'model.learn_dbt.drop_table'. If you are conditionally running
sql, eg. in a model hook, make sure your `else` clause contains valid sql!

Provided SQL:
/* {"app": "dbt", "dbt_version": "1.1.0", "profile_name": "eric-snowflake-dbt", "target_name": "dev", "node_id": "model.learn_dbt.drop_table"} */

"DROP TABLE IF EXISTS ANALYTICS.DBT.TEST_X_TABLE"

问题修复 #

报错原因是生成的DROP语句外层错误包裹了双引号，传入run_query执行的SQL语句不需要额外用双引号包裹整段命令。
修改构造DROP语句的SQL，去掉首尾多余的双引号即可，修正后的生成SQL如下：

SELECT
    CONCAT('DROP TABLE IF EXISTS ', CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME))
FROM "{{ var('db_name') }}"."INFORMATION_SCHEMA"."TABLES"
WHERE table_owner = '{{ var("table_owner") }}'
AND table_schema = '{{ var("schema_name") }}'
AND table_name LIKE '{{ var("table_name") }}'

调整后批量删除逻辑可正常运行，该方案可供后续使用dbt操作Snowflake的开发者参考。

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