DBT Jinja循环Union多表补Null后全量NULL问题求助

场景描述 #

需要创建一张主事件表，合并多个结构近似的表：大部分列在所有表中都存在，但部分表会缺失1-2列。期望缺失列用NULL填充，但运行代码后输出表的所有单元格均为NULL。已知col1、col2存在于所有表，col3仅存在于table1和table2，不存在于table3。

原问题代码 #

{{ config(schema='MYSCHEMA', materialized='table') }}

{% set tables = ['table1', 'table2', 'table3'] %}

{% set possible_columns = ['col1', 'col2', 'col3'] %}

{% for table in tables %}
  {%- set table_columns = adapter.get_columns_in_relation( ref(table) ) -%}
  select
    {% for pc in possible_columns %}

      {% if not loop.last -%}

        {% if pc in table_columns %}
          {{ pc }},
        {% else %}
          null as {{ pc }},
        {%- endif %}

      {% else %}
        {% if pc in table_columns %}
          {{ pc }}
        {% else %}
          null as {{ pc }}
        {%- endif %}

      {% endif %}

    {%- endfor %}

  from
    {{ ref(table) }}

  {% if not loop.last -%}
    union all
  {%- endif %}
{% endfor %}

问题根源 #

adapter.get_columns_in_relation(ref(table))返回的是Column对象的列表，而非列名字符串的列表。直接用字符串pc判断是否存在于这个对象列表中，永远会返回false，导致所有列都走了null as {{ pc }}的分支，最终所有单元格都被设置为NULL。

修复方案 #

将获取到的Column对象列表转换为列名字符串的列表，通过提取每个Column对象的name属性来实现。修改后的核心代码片段：

{%- set table_columns = adapter.get_columns_in_relation( ref(table) ) | map(attribute='name') | list -%}

完整修复代码 #

{{ config(schema='MYSCHEMA', materialized='table') }}

{% set tables = ['table1', 'table2', 'table3'] %}

{% set possible_columns = ['col1', 'col2', 'col3'] %}

{% for table in tables %}
  {%- set table_columns = adapter.get_columns_in_relation( ref(table) ) | map(attribute='name') | list -%}
  select
    {% for pc in possible_columns %}

      {% if not loop.last -%}

        {% if pc in table_columns %}
          {{ pc }},
        {% else %}
          null as {{ pc }},
        {%- endif %}

      {% else %}
        {% if pc in table_columns %}
          {{ pc }}
        {% else %}
          null as {{ pc }}
        {%- endif %}

      {% endif %}

    {%- endfor %}

  from
    {{ ref(table) }}

  {% if not loop.last -%}
    union all
  {%- endif %}
{% endfor %}

验证逻辑 #

• 对于table1和table2：col1、col2、col3都存在，会直接选取原列值
• 对于table3：col1、col2正常选取原列值，col3因不存在，会生成null as col3，用NULL填充该列

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