如何在 PostGresOperator 气流中的 .sql 文件中传递参数? [英] How to pass paremeter in my .sql file in PostGresOperator airflow?
问题描述
我有 sql 文件,我想使用 PostGresOperator 将参数传递给该 sql 文件.
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)
我的 postGresOperator
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)
我怎样才能做同样的事情并在我的 .sql 文件中传递我的参数并仍然使用 .format(mytable,myID) ?
How can I do the same and pass my parameters in my .sql file and still use .format(mytable,myID) ?
以便我可以将它们传递到我引用的 .sql 文件中.
so that I can pass them into my referenced .sql file.
推荐答案
如 How-to Guide PostgresOperator
,您可以将 SQL 放在dag目录:
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 }};
使用 params
传入将在文件中的 SQL 中呈现的键/值对:
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)
或
sql=f"""select * from table_{table_name} where id > {myID} """
或者如果您想使用 jinja,请利用任何 默认变量,例如触发 DAG 时提供的参数,您可以这样做:
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} """
这篇关于如何在 PostGresOperator 气流中的 .sql 文件中传递参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!