How to pass paremeter in my .sql file in PostGresOperator airflow?

Question

I have sql file and I want to pass parameters to that sql file using PostGresOperator.

"""select * from table_{} where id > ID """.format(mytable,myID)

My postGresOperator

mport_redshift_table = PostgresOperator(
            task_id='copy_data_from_redshift_{}'.format(country),
            postgres_conn_id='postgres_default',
            sql="""
                   select * from table_{} where id > {}
                """.format(mytable,myID)

How can I do the same and pass my parameters in my .sql file and still use .format(mytable,myID) ?

so that I can pass them into my referenced .sql file.

airflow.apache.org/docs/apache-airflow-providers-postgres/… — mechanical_meat
– mechanical_meat, Commented Aug 29, 2021 at 14:05

NicoE · Accepted Answer · 2021-08-29 15:28:56Z

2

As explained in the How-to Guide for PostgresOperator, you can place your SQL in a file within a subfolder in the dag directory:

-- dags/sql/birth_date.sql
SELECT * FROM pet WHERE birth_date BETWEEN SYMMETRIC {{ params.begin_date }} AND {{ params.end_date }};

Use params to pass in the key/value pairs that would be rendered within the SQL in your file:

get_birth_date = PostgresOperator(
    task_id="get_birth_date",
    postgres_conn_id="postgres_default",
    sql="sql/birth_date.sql",
    params={"begin_date": "2020-01-01", "end_date": "2020-12-31"},
)

Edit

If you want to do it inline, without using the file, just use any string interpolation mechanism:

sql="""select * from table_{} where id > {} """.format(mytable,myID)

or

sql=f"""select * from table_{table_name} where id > {myID} """

or if you want to use jinja, taking advantage of any of the default vairables, such as a param provided when the DAG was triggered you could do it like this:

sql=f"""select * from table_{{ params.param1 }} where id > {myID} """

edited Aug 29, 2021 at 15:28

answered Aug 29, 2021 at 14:25

NicoE

4,9333 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aditya Verma Over a year ago

Thanks , I read the document , is that the only way to pass the paramenters ? can't I use something like .format instead in my file and pass the parameters, just wondering.

NicoE Over a year ago

Yes you can also do it that way. You don't need to create and additional file, even if it's considered as a best practice. You could use format or f-String. I'll edit my answer with an example

Aditya Verma Over a year ago

yeah please I would love to know that as well! looking forward to the edit😃

Aditya Verma Over a year ago

@Nicole, No I actually meant using file only, but I understand that without using params parameter I cannot do the file so if I have to do .sql , The .format one i was wondering if we can use it in .sql file but seems like we cannot use the .format inside the .sql file we need to use params parameter.

NicoE Over a year ago

@AdityaVerma oh I see! now I get it! Thanks for the feedback. Absolutely, going with params parameter and using a .sql file is the preferred way.

|

Collectives™ on Stack Overflow

How to pass paremeter in my .sql file in PostGresOperator airflow?

1 Answer 1

Edit

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related