jinja2.exceptions.TemplateSyntaxError: expected token ':', got '}'

Lior 0
Viewed 53

I'm trying to use an xcom_pull inside an SQL phrase executed by a Snowflake operator in Airflow. I need the task_id name to use a variable since I want to support different tasks.

I tried this syntax but seems it is not being rendered ok. Anyone has an idea how to do it?

This is the Python code:

for product, val in PRODUCTS_TO_EXTRACT_INC.items():      
       product_indicator, prefix = val
      
       params['product_prefix'] = prefix
       
       calculate_to_date = SnowflakeOperator(
            dag=dag,
            task_id=f'calculate_to_date_{prefix}',
            snowflake_conn_id = SF_CONNECTION_ID,
            warehouse=SF_WAREHOUSE,
            database=BI_DB,
            schema=STG_SCHEMA,
            role=SF_ROLE,
            sql= [  """
                    {SQL_FILE}
                    """.format(SQL_FILE="{% include '" + QUERIES_DIR + ETL + "/calculate_to_date.sql'" + " %}")
                ],
            params=params
        )

This is the SQL code for calculate_to_date.sql:

select '{{{{ (ti.xcom_pull(key="return_value", task_ids=["calculate_from_date_{}"])[0][0]).get("FROM_DATE") }}}}'.format(params.product_prefix) AS TO_DATE

This is the error message:

File "/home/airflow/gcs/dags/Test/queries/fact_subscriptions_events/calculate_to_date.sql", line 11, in template select '{{{{ (ti.xcom_pull(key="return_value", task_ids=["calculate_from_date_{}"])[0][0]).get("FROM_DATE") }}}}'.format(params.product_prefix) jinja2.exceptions.TemplateSyntaxError: expected token ':', got '}'

jinja2 snowflake-cloud-data-platform airflow
1 Answers

the correct syntax is

select '{{ (ti.xcom_pull(key="return_value", task_ids="calculate_from_date_{}".format(params.product_prefix))[0]).get("FROM_DATE") }}' AS TO_DATE

it works like a charm

Lior0
Related