Unable to execute multiple SQL statements on one entry in hooks config #831
Comments
|
This is because our path for executing sql does not allow executing multiple statements separated by |
|
The flip side though is that you can put them into hooks as separate entries: |
|
Understood. Yes it does quite limit our ability to logically group multiple related statements together for execution into one macro for ease-of-use within config files e.g. For now, we are able to get this to work using a workaround which executes the query at compile time, returning no statements to the hook itself like below. {% macro grant_access(table_name) -%}
{%- set group_name = "group" -%}
{%- set catalog_identifier = "`catalog_name`" -%}
{%- set schema_identifier = catalog_identifier ~ "`schema_name`" -%}
{%- set schema_identifier = schema_identifier ~ table_name -%}
{%- if execute -%}
{%- set queries %}
GRANT USE CATALOG ON CATALOG {{ catalog_identifier }} TO `{{ group_name }}`;
GRANT USE SCHEMA ON SCHEMA {{ schema_identifier }} TO `{{ group_name }}`;
GRANT SELECT ON TABLE {{ table_identifier }} TO `{{ group_name }}`;
GRANT MODIFY ON TABLE {{ table_identifier }} TO `{{ group_name }}`;
{%- endset -%}
{{ run_queries(queries) }}
{%- endif -%}
{%- endmacro %}
{% macro run_queries(queries) -%}
{% for query in (queries | trim | trim(';')).split(';') -%}
{%- do run_query(query | trim) -%}
{%- endfor %}
{%- endmacro %}The issue here being that, as the execution happens when the macro is compiled, the logs appear to hang before executing the hook. The hook then appears to execute instantaneously as no statement was returned by the macro to execute. We will continue to use this workaround in the meantime, but it would be great to have this looked into in the future. |
Describe the bug
I am attempting to execute multiple SQL statements on various hooks and seem to be unable to execute multiple statements separated by
;within one entry.I am generating multiple
GRANTstatements with a macro. I've rewritten an abridged version of the macro and it's usage below showing how it is used in both theon-run-startandon-run-endhooks in thedbt_projectfile on one entry.I find that, if a macro generates multiple separate SQL statements, then the command fails. However if a macro generates just one SQL statement, the command executes with no error. I see this exact behaviour when configuring
on-run-start,on-run-end,pre-hook, andpost-hookhooks.As a workaround, I have set the macros to execute each statement when called by using the
run_querymacro within thegrant_accessmacro.Steps To Reproduce
A failing macro which generates multiple SQL statements:
{% macro grant_access(table_name) -%} {%- set group_name = "group" -%} {%- set catalog_identifier = "`catalog_name`" -%} {%- set schema_identifier = catalog_identifier ~ "`schema_name`" -%} {%- set schema_identifier = schema_identifier ~ table_name -%} {%- if execute -%} GRANT USE CATALOG ON CATALOG {{ catalog_identifier }} TO `{{ group_name }}`; GRANT USE SCHEMA ON SCHEMA {{ schema_identifier }} TO `{{ group_name }}`; GRANT SELECT ON TABLE {{ table_identifier }} TO `{{ group_name }}`; GRANT MODIFY ON TABLE {{ table_identifier }} TO `{{ group_name }}`; {%- endif -%} {%- endmacro %}A successful macro which generates only one SQL statement:
{% macro grant_access_catalog(table_name) -%} {%- set group_name = "group" -%} {%- set catalog_identifier = "`catalog_name`" -%} {%- set schema_identifier = catalog_identifier ~ "`schema_name`" -%} {%- set schema_identifier = schema_identifier ~ table_name -%} {%- if execute -%} GRANT USE CATALOG ON CATALOG {{ catalog_identifier }} TO `{{ group_name }}`; {%- endif -%} {%- endmacro %}dbt_project.yml:Whenever I try to execute
dbt run, these commands fail.Expected behavior
The statements should execute successfully. The documentation for dbt shows that it is possible to build and execute multiple statements with jinja within one entry on these config keys.
Screenshots and log output
System information
The output of
dbt --version:The operating system you're using:
MacOS Sequoia 15.0.1 (24A348)
The output of
python --version:Python 3.12.7
Additional context
I am not sure if this is an issue with the
dbt-databricksordbt-core. I am posting here first as it appears that, according to thedbt-coredocumentation noted above, this should be supported. My guess therefore is that the issue stems from the adapter, but I will be happy to forward this issue todbt-coreif I am wrong.The text was updated successfully, but these errors were encountered: