CODEWITHSUNDEEP
amazon-redshiftairflow
Saurav Ganguli
Saurav Ganguli

Reputation: 416

Airflow parameters to Postgres Operator

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?

Upvotes: 8

Views: 10704

Answers (2)

kaxil
kaxil

Reputation: 18844

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.

Upvotes: 5

Nitin Pandey
Nitin Pandey

Reputation: 719

You can use the airflow macros inside the query string - which needs to be passed to the redshift.

Example:

PostgresOperator(task_id="run_on_redshift",
                 dag=dag,
                 postgres_conn_id=REDSHIFT_CONN_ID,
                 sql="""
                        UNLOAD ('select * from abc.xyz') TO 's3://path/{{ds}}/' iam_role 's3_iam_role' DELIMITER AS '^' ALLOWOVERWRITE addquotes ESCAPE HEADER parallel off;
                     """
                 )

Upvotes: 0

Related Questions