I have a dbt macro where I am trying to load a stand-alone sql file. I would then like to execute the SQL statement that I loaded by calling my macro. I am attempting to use jinja's include
statement.
# test_sql_macro.sql
{% macro test_sql_macro() -%}
{%- if execute -%}
{%- call statement('do_stuff', fetch_result=True) -%}
{% include 'select.sql' %}
{%- endcall -%}
{%- set call_results = load_result('do_stuff') -%}
{{ log("Snowflake response: " ~ call_results, info=True) }}
{% endif %}
{%- endmacro %}
# select.sql
SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE
I am running the macro with
dbt run-operation test_sql_macro --project-dir . --profiles-dir .
but I am getting an error
Encountered an error while running operation: Compilation Error in macro statement (macros/etc/statement.sql)
no loader for this environment specified
After some search I came across setting template renderer in Python, however I am wondering if it is possible to set the renderer in the macro itself.
Is it possible to accomplish this in dbt?
dbt doesn't support jinja's include
tag.
As a workaround, you can use a macro to "import" other sql statements into the jinja context.
For example:
Use a macro to hold arbitrary sql statements.
{% macro my_query() %}
drop table if exists films cascade;
create table films (
film_id integer,
title varchar
);
insert into films (film_id, title) values
(1, 'The Godfather'),
(2, 'The Wizard of Oz'),
(3, 'Citizen Kane')
;
{% endmacro %}
Call the sql statement from another macro.
{% macro my_macro() %}
{# use a statement block #}
{% call statement('films', fetch_result=True, auto_begin=True) %}
{# sql query is wrapped in a macro #}
{{ my_query() }}
{% endcall %}
{# verify the results, print to stdout #}
{% set results = run_query('SELECT * FROM films') %}
{{ results.print_table() }}
{% endmacro %}
run the operation
$ dbt run-operation my_macro
Running with dbt=1.2.0
| film_id | title |
| ------- | ---------------- |
| 1 | The Godfather |
| 2 | The Wizard of Oz |
| 3 | Citizen Kane |