How do we define select statement as a variable in dbt?

Question

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?

Crabbing

Answer 1

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

Dortheydorthy

Hey! Do you wrap it in a macro, and then refer to it as {{var}} inside a model .sql? – Goethite

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? – Hat

Hi, Is this solution applicable for superset also or only for dbt? – Cripps

Answer 2

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 %}

Tiltyard

How do you implemented run_query() ? – Cascio

Answer 3

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.

Graze

Answer 4

{% 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

Iams