Declarative Automation Bundles: Replace variables in an SQL file

[Daniel_dlh]

Hi all,

I want to deploy a workflow that has an SQL task. The SQL in this tasks needs to be parametrized (as e.g. the catalog name is dependent on the environment).

I have this so far:

In .src/mysql.sql

SELECT * FROM {{ catalog }}.schema.table;

 And my resource definition:

(...)

variables:
  catalog:
    description: The catalog to use
    default: my_catalog

(...)

resources:
  jobs:
    test:
      name: "[test] SQL Varibles"
      tasks:
        - task_key: run_sql
          sql_task:
            warehouse_id: 123456789abc
            file:
              path: ../src/mysql.sql

Unfortunately the variables is not being replaced during deployment and/or execution.

Is that the expected behaviour?

If no, what could be the issue?

If yes, what would be a suggested work-around?

Thanks for your support!

[Ashwin_DSA]

Hi @Daniel_dlh,

I had recently responded to a similar query on this topic. You may want to check this. 

At a high level, you’re combining two separate mechanisms. 

Bundle variables (variables: + ${var.name}) are resolved only in the bundle config (YAML/Python) at deploy/validate time.

SQL task parameters (sql_task.parameters used as {{param}} in SQL) are what actually get substituted inside the .sql file at run time.

So {{ catalog }} in the SQL file will only be replaced if catalog is defined under sql_task.parameters.

Does this help?

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[Daniel_dlh]

Hi @Ashwin_DSA ,

thanks for the quick answer!

This is actually working, great!

However, when the variable gets replace, the value is put into ' (single quotes) making the example statement I used above an incorrect SQL statement. 
Any idea how to get rid of those single quotes?

Regards,
Daniel

[Ashwin_DSA · Accepted solution]

Hi @Daniel_dlh,

No problem. Glad it works. 

That quoting is expected. SQL task parameters are bound as values, so string params are always inserted as '...' literals. You can’t turn that off, but you can work around it by using IDENTIFIER() and building a full name as a string.

For example, instead of:

SELECT *
FROM {{ catalog }}.schema.table;

do:

SELECT *
FROM IDENTIFIER({{ catalog }} || '.schema.table');

with catalog = my_catalog in your parameters. The engine sees IDENTIFIER('my_catalog.schema.table'), which is valid and resolves to the correct object.

Let me know if that works.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.