Search code examples
pythonjinja2dbt

run query with for loop dbt jinja

I have a list of tables and I want to run query using for ... in [] . After running code below I have an error. Where could be mistake?

I just want to print the result. Result I expect in a list for example [2,67,0,7]. enter image description here

tables = [
'table_1'
,'table_2'
,'table_3'
,'table_4'
,'table_5'
]



{%- call statement('my_statement', fetch_result=True) -%}
    select count(*) as cnt
    from {% for n in tables %} my_schema.n {% endfor %}
    where students = 'great' 
{%- endcall -%}
{%- set my_var = load_result('my_statement') -%}
{{my_var}}

OR I used it:

{%- set query -%}
    select count(*) as cnt
    from {% for n in tables %} my_schema.n {% endfor %}
    where students = 'great'
{%- endset -%}

{% set results = run_query(query) %}
{{ results }}
Solution

  • Does this do what you want?

    {% macro print_multi_tables() %}

    {% set tables = ['table_1', 'table_2', 'table_3', 'table_4', 'table_5'] %}
    {% set ns = namespace(query_results = [], final_result = '[') %}
    {% set query_results = [] %}

    {% for table_name in tables %}

        {% set query %}
            select count(*) from {{ ref(table_name) }} where students = 'great' 
        {% endset %}
        {{ log(query, true) }}

        {% set results = run_query(query) %}
        {% set count = results.rows[0][0] %}
        {% set query_results = query_results.append(count) %}

    {% endfor %}

    {# This gives a result like [Decimal('2'), Decimal('8')], so #}
    {# there is more code below to print the exact results you want #}
    {{ log(query_results, true) }}

    {# Print the results in the format [result_1, result_2, etc] #}
    {% for x in query_results %}
        {% set ns.final_result = ns.final_result ~ x %}
        {% if not loop.last %}
            {% set ns.final_result = ns.final_result ~ ', ' %}
        {% endif %}
    {% endfor %}
    {% set ns.final_result = ns.final_result ~ ']' %}
    {{ log(ns.final_result, true) }}

{% endmacro %}

    Results will look like

    16:44:08
            select count(*) from my_database.my_schema.table_1 where students = 'great'

16:44:08
            select count(*) from my_database.my_schema.table_2 where students = 'great'

16:44:08
            select count(*) from my_database.my_schema.table_3 where students = 'great'

16:44:08  [Decimal('2'), Decimal('6'), Decimal('8')]
16:44:08  [2, 6, 8]