0

Can we pass array list in Airflow postgress params ?

I was trying something like

Select hotel_name from hoteldetails where id in ({{ params.hotel_ids }});

in 's3_to_redshift.sql'.

Below is my operator::

s3_to_redshift = PostgresOperator(
    task_id='s3_to_redshift',
    postgres_conn_id='redshift',
    sql='s3_to_redshift.sql',
    params={'hotel_ids': [1,2,3]},
    dag=dag
)
ESCoder
  • 15,431
  • 2
  • 19
  • 42
Tarun Bhorhari
  • 1
  • 1

2 Answers2

1

You can, but you have to worry about how your params will render. In this case you are passing the array and directly rendering it in the SQL statement. This will print [1, 2, 3] in the SQL statement, which is of course not valid SQL.

Airflow uses Jinja to render the templates. In order to get a correct SQL statement you can use for loops in Jinja to render the params. Airflow also supports adding your own functions to use in Jinja. This means you need to create an Airflow plugin. For example you could add the following macro:

def render_list_sql(list):
  return ', '.join(list)

If you have this properly imported as a plugin your sql template will turn into the following:

Select hotel_name from hoteldetails where id in ({{ macros.render_list_sql(params.hotel_ids) }});
Blokje5
  • 4,763
  • 1
  • 20
  • 37
  • where is this "macros.render_list_sql" from? can't seem to find more about it , having trouble rendering just strings .. – the pillow May 14 '20 at 19:46
  • @thepillow it is a custom jinja macro. Airflow allows you to extend it using the Airflow plugin system, which allows you to add custom macros like the one above. – Blokje5 May 15 '20 at 07:16
  • @Blokje5 can yo please help me with similar question in here: https://stackoverflow.com/questions/67631581/airflow-2-0-2-how-to-pass-parameter-within-postgres-tasks-using-xcom – pm1359 May 26 '21 at 11:34
0

You can also use this in the parameters:

params={
            'hotel_ids': ','.join([1, 2, 3])
}

If you want to have a list of string, for me works this:

params={
            'hotel_ids': "','".join(['a', 'b', 'c'])
}
Ana Isabel
  • 1