dbt - 传递字典时，DBT 宏将变量作为字符串接收

Question

我是 DBT 世界的新手，面临一个奇怪的问题。数据库 - 雪花。附上两个测试模型，一个测试csv数据文件和一个测试宏文件。

seed data file : sample_data.csv
----------------------------------------------------------------

subdimension,datasource,datasource_label,scoring_metric,weight
facebook_impressions,pathmatics_facebook,Pathmatics Facebook,total_year_impressions,5
facebook_engagement,facebook,Facebook,total_year_interactions,2.5
facebook_engagement,facebook,Facebook,interactions_per_post,2.5

-------------------------------------------------------------------

我正在使用调用语句从种子 csv 文件中读取数据，并使用 fromjson(query_result) 创建字典。如果你看一下 TEST1 模型，这个作为字典的读取数据是在那里编码的。然后将该字典传递给宏评分，宏将其作为字典接收。现在看看 TEST2 模型。这是一样的，但唯一的区别是，以字典格式读取数据是通过一个名为 get_scoring_metrics 的宏来完成的。这里 TEST2 从 get_scoring_metrics 接收字典并将其传递给宏评分。但是这次宏评分接收数据作为字符串而不是字典并抛出异常 str object has no attribute items。如果你编译模型，你会看到它。这怎么可能？这两种情况我都使用相同的代码。我需要字典格式的数据，以便于开发复杂的模型。任何解决方案将不胜感激。谢谢你。

模型 TEST1（正常工作）

-- depends_on: {{ ref('sample_data') }}

{%- set datasource = 'facebook' -%}

    -- DICTIONARY THAT MAPS METRICS TO THEIR SUB-DIMENSIONS

{%- call statement('scoring_metric_query', fetch_result=True) -%}

    SELECT TO_JSON(PARSE_JSON('{'||LISTAGG(diq_subdim,',')||'}')) AS diq_metric
    FROM
        (
            SELECT subdim_name||':'||'['||scoring_metric||']' AS diq_subdim
            FROM
                (
                    SELECT '"'||subdimension ||'"' AS subdim_name,
                    LISTAGG(''''||scoring_metric ||'''' , ',') AS scoring_metric
                    FROM {{ ref('sample_data') }}
                    WHERE lower(datasource) = lower('{{ datasource }}')
                    GROUP BY subdimension
                ) T1
        ) T2

{%- endcall -%}

{% if execute %}

  {%- set query_result = load_result('scoring_metric_query')['data'][0][0] -%}
  {% set metric_dict = fromjson(query_result) %}

{% endif %}

{{
    scoring(
      datasource = datasource,
      metric_dict = metric_dict
    )
}}

---

MODEL TEST2（即使我使用与模型 TEST1 相同的代码，在编译期间也会引发异常）

{%- set datasource = 'facebook' -%}

{%- set metric_dict = get_scoring_metrics(datasource) -%}

{{
    scoring(
      datasource = datasource,
      metric_dict = metric_dict
    )
}}

---

宏观评分

{% macro scoring(datasource, metric_dict={} ) %}
--the query below means nothing. It's just an example to show that when this macro is called from test2.sql model, the metric_dict does not
--work as a dictionary but this macro receives metric_dict as a string (error- str objet has no attribute items).
--But when it's called from test1.sql model it receives metric_dict as a python dictionary.

  {% for i , j in  metric_dict.items() %}
        select {{ i }} , {{ j }}
  {% endfor %}

{% endmacro %}

---

宏 get_scoring_metrics

{% macro get_scoring_metrics(datasource) %}

    -- DICTIONARY THAT MAPS METRICS TO THEIR SUB-DIMENSIONS

{%- call statement('scoring_metric_query', fetch_result=True) -%}

    SELECT TO_JSON(PARSE_JSON('{'||LISTAGG(diq_subdim,',')||'}')) AS diq_metric
    FROM
        (
            SELECT subdim_name||':'||'['||scoring_metric||']' AS diq_subdim
            FROM
                (
                    SELECT '"'||subdimension ||'"' AS subdim_name,
                    LISTAGG(''''||scoring_metric ||'''' , ',') AS scoring_metric
                    FROM {{ ref('sample_data') }}
                    WHERE lower(datasource) = lower('{{ datasource }}')
                    GROUP BY subdimension
                ) T1
        ) T2

{%- endcall -%}

{% if execute %}

  {%- set query_result = load_result('scoring_metric_query')['data'][0][0] -%}
  {% set metric_dict = fromjson(query_result) %}

  {{ return(metric_dict) }}

{% endif %}

{% endmacro %}


-----------------------------------------------------------------

标签： dbt