11

Hi I am trying to define a select statement in a set variable in dbt, can any one suggest how to set sql query as a variable in dbt and how to access those variables in below CTEs?

Rob
  • 14,746
  • 28
  • 47
  • 65
Sweety
  • 191
  • 1
  • 2
  • 5

4 Answers4

16

You can use call statement and get the result in a variable with load_result

Here is an example of retrieving only one field from the select statement:

{%- call statement('my_statement', fetch_result=True) -%}
      SELECT my_field FROM my_table
{%- endcall -%}

{%- set my_var = load_result('my_statement')['data'][0][0] -%}

Then you can use {{ my_var }}

You can play with ['data'][0][0] depending on the rows and columns that your select returns

Javier Montón
  • 4,601
  • 3
  • 21
  • 29
  • 1
    Hey! Do you wrap it in a macro, and then refer to it as {{var}} inside a model .sql? – bellotto Oct 20 '22 at 17:09
  • When I try to access [data][0][0] I get 'None' has no attribute 'data', but if I log data only (without indexes), dbt compile runs without errors and I can see list a of tuples printed in the console as expected. Do you know the reason for this problem? – ozgenbaris Mar 07 '23 at 07:23
  • Hi, Is this solution applicable for superset also or only for dbt? – Pardeep Naik Apr 03 '23 at 15:12
9

You could consider embedding your SQL statement in a macro and call that macro in your models.

{% macro get_data() %}
    {% set query %}

        select
            column_a_boolean,
            column_b_varchar
        from my_table

    {% endset %}

    {% set results = run_query(query) %}
    {# execute is a Jinja variable that returns True when dbt is in "execute" mode i.e. True when running dbt run but False during dbt compile. #}
    {% if execute %}
    {% set results_list = results.rows %}
    {% else %}
    {% set results_list = [] %}
    {% endif %}

    {{ return(results_list) }}

{% endmacro %}

You can then use the above macro in a model. For example, in a model below, we UNION records returned by the macro if a value in a column column_a_boolean is equal true.

{% for record in get_data() %}
    {% if record.column_a_boolean == true %}
        {% if not loop.first %}
            UNION ALL
        {% endif %}
            SELECT
                record.column_b_varchar
    {% endif -%}
{% endfor %}
Pawel
  • 626
  • 5
  • 7
2

You can use dbt_utils.get_column_values:

{%- set my_var = dbt_utils.get_column_values(
    table = source('source_name', 'object_name'), 
    column = 'my_col',
    where = "my col != 'some_value") -%}

{%- for x in my_var %}
    select 
        '{{ x }}' as test
    from {{ source(x, 'object_name') }}
    {% if not loop.last -%} 
        union all 
    {%- endif -%}
{%- endfor -%}

Make sure your packages.yml file contains dbt-utils:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.0.0 # latest version as time of writing, change as needed

Then run dbt deps to initialize the package:

$ dbt deps

The run_query documentation has a nice example as well.

Marty_C137
  • 330
  • 1
  • 10
0
{% macro hello() %}

{% if execute %}
    {% set results = run_query("select 'hello'; ").columns[0].values()[0] %}
{% endif %}

select {{results}} as id;

{% endmacro %}

output 
results will contain hello
sume
  • 1
  • 1