Passing Jinja variables in airflow params dict

Question

I am trying to use templated params dict in SnwoflakeOperator. I have 2 tasks one to create a S3 object and then delete and replace the content of the table with s3 object content

execution_date = "{{ execution_date.int_timestamp | int }}"  # built in macro
task1 = PythonOperator(task_id='s3_create', 
          op_kwargs={'s3_key': f'{execution_date}_{file}'})

task_2 = SnowflakeOperator(task_id='load_data', 
            sql=["""copy into {{params.table}} from {{ params.stage }} 
                    files = ('{{ params.files }}') 
                    file_format = 'csv'
                 """]
            params={'table':'test_table', 
                    'files'=f'{execution_date}_{file}'}
)
task_1 >> task_2

It is not rendering as expected and have tried almost all formatting combinations.

Note: I cannot use S3ToSnowflakeOperator as I have do more than just copying the contents into the table.

No, you have `f'{execution_date}_{file}'` what is file in this string? — Elad Kalif, Jun 27 '22 at 16:27
I'm not quite familiar with the syntax of the query that you set. is it 1 string? it seems like a wrong syntax? — Elad Kalif, Jun 27 '22 at 16:33
yes, it is the one string (sorry for formatting) and the intention is to use jinja to render templated SQL. — mad_, Jun 27 '22 at 16:35

Elad Kalif · Accepted Answer · 2022-06-27T17:02:10.470

0

sql is templated filed thus it can automatically render execution_date (since it's Airflow build-in macro) there is no need to set it via params. The params is used to render python variables or just strings that you want to pass.

You can just set it directly as:

file = "test_file.csv"
SnowflakeOperator(
    task_id='load_data',
    sql=""" copy into {{ params.table }} from {{ params.stage }}
         files = ('{{ execution_date }}_{{ params.file }}')
         file_format = csv
         """,
    params={
        'table': 'test_table',
        'stage': 'test_stage',
        'file': file
    }
)

It will be rendered as:

I'm not sure this is the file name you wanted but you can change execution_date to any other macro you wish.

Edit: You can replace {{ execution_date }} with {{ execution_date.int_timestamp | int }} it will produce:

edited Jun 27 '22 at 17:02

answered Jun 27 '22 at 16:27

Elad Kalif

14,110
2
17
49

I am converting execution_date into int. In the example I have `execution_date = "{{ execution_date.int_timestamp | int }}" # built in macro` – mad_ Jun 27 '22 at 16:48
So in my answer just replace `{{ execution_date }}` with `{{ execution_date.int_timestamp | int }}` – Elad Kalif Jun 27 '22 at 16:51
I think it will work. I am testing it out with a small tweak but it should work. Thanks for the answer. I will mark as accepted after testing – mad_ Jun 27 '22 at 17:17
Hello @EladKalif. How do you suggest templating the `params` if the SQL is a query file not a query string? – ndrwnaguib May 09 '23 at 21:20

Passing Jinja variables in airflow params dict

1 Answers1