CODEWITHSUNDEEP
python-3.xairflowsnowflake-cloud-data-platform
mad_
mad_

Reputation: 8273

Snowflake operator (airflow) not rendering SQL properly

I am trying to run a simple SQL Query using Airflow provider Snowflake (1.3.0)

 SnowflakeOperator(
        task_id=f'task',
        snowflake_conn_id='snowflake_conn',
        parameters={
            "table": "dummy_table",
        },
        sql=["delete from %(table)s"],
        autocommit=True,
        dag=dag,
    )

The SQL it is rendering is delete from ''dummy''. I want to get rid of '' but have tried everything and nothing seems to be working

Upvotes: 1

Views: 1132

Answers (2)

Elad Kalif
Elad Kalif

Reputation: 15961

If you are using parameters then it's up to SQLAlchemy. You can find more information about it in How to render a .sql file with parameters in MySqlOperator in Airflow?

Alternatively, you can use Airflow rendering (Jinja engine) with params:

 SnowflakeOperator(
        task_id=f'task',
        snowflake_conn_id='snowflake_conn',
        params={
            "table": "dummy_table",
        },
        sql=["delete from {{ params.table }}"],
        autocommit=True,
        dag=dag,
    )

This will be rendered as: enter image description here

thus the query that will be submitted to Snowflake is:

delete from dummy_table

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

To parametrize table name IDENFITIER should be used:

To use an object name specified in a literal or variable, use IDENTIFIER().

sql=["delete from IDENTIFIER(%(table)s)"],

The query DELETE FROM 'dummy' is not correct, but DELETE FROM IDENTIFIER('dummy') will work.

CREATE TABLE dummy(id INT);

DELETE FROM 'dummy';
-- Syntax error: unexpected ''dummy''. (line 4)

DELETE FROM IDENTIFIER('dummy');
-- number of rows deleted: 0

Upvotes: 2

Related Questions