can I pass parameters to sql file with VerticaOperator in Airflow?

Question

I am using VerticaOperator in my dag, something like:

example_query = VerticaOperator(task_id="example",
                                sql='queries/example.sql',
                                vertica_conn_id="vertica", queue='default', dag=dag)

the query looks like that:

select * from table 
where id = '||&1||' -- may be where id = '{}'

How can I pass the parameter via the VerticaOperator?

example_query = VerticaOperator(task_id="example",
                                sql='queries/example.sql' 6, # 6 is the parameter for example
                                vertica_conn_id="vertica", queue='default', dag=dag)

If I can't, any other ideas how to use the sql file, passing the parameter and call it from the VerticaOperator?

Answer 1

You can use params. It's a reference to the user-defined params dictionary:

example_query = VerticaOperator(task_id="example",
                                sql='queries/example.sql',
                                params={'my_key': 6},
                                vertica_conn_id="vertica",
                                queue='default',
                                dag=dag)

The example.sql needs to be:

select * from table 
where id = '{{ params.my_key }}'