Airflow parameters to Postgres Operator

Question

I am trying to pass the execution date as runtime parameter to the postgres operator

class MyPostgresOperator(PostgresOperator):
    template_fields = ('sql','parameters')

task = MyPostgresOperator(
  task_id='test_date',
  postgres_conn_id='redshift',
  sql="test_file.sql",
  parameters={'crunch_date':'{{ ds }}'},
  dag=dag
)

Then I try to use this parameter in the sql query to accept the value as passed by the dag

select 
{{ crunch_date }} as test1,

The dag sends the parameter correctly, however the query is just taking a null value instead of the execution date that is passed. Is there a way to have the postgresql with redshift accept the correct value for this parameter?

Answer 1

You will have to update your sql query as below:

select 
{{ ds }} as test1,

You won't be able to use one templated field in other. If you want to pass a param in task and use it in Jinja template, use params parameter.

UPDATE:

But do note that params is not a templated field. And if you template it, it won't render as nested templating won't work.

task = MyPostgresOperator(
  task_id='test_date',
  postgres_conn_id='redshift',
  sql="test_file.sql",
  params={'textstring':'abc'},
  dag=dag
)

where test_file.sql is:

select 
{{ params.textstring }} as test1,

Check 4th point in https://medium.com/datareply/airflow-lesser-known-tips-tricks-and-best-practises-cf4d4a90f8f to understand more about params.