Pass parameters to SQL query when using PostgresOperator in Airflow
Airflow

Pass parameters to SQL query when using PostgresOperator in Airflow

How to pass parameters to SQL template when using PostgresOperator in Airflow
Bartosz Mikulski
Bartosz Mikulski – 1 min read

PostgresOperator allows us to use a SQL file as the query. However, when we do that, the standard way of passing template parameters no longer works.

For example, if I have the following SQL query:

 SELECT column_a, column_b FROM table_name WHERE column_a = {{ some_value }}

Airflow will not automatically pass the some_value variable as the parameter.

In this situation, I have to use the parameters attribute of the PostgresOperator:

PostgresOperator(
    task_id='postgres_task',
    postgres_conn_id='postgress_sophi',
    params={"some_value":"'the value I want'"},
    sql='query.sql',
    dag=dag)

Note that, I had to wrap the value into single quotes! Because in Postgres double quotes are used to reference a columns, tables, or functions. String literals, on the other hand, are inside single quotes.

In the query.sql file, I have to change the template variable to params.some.value:

 SELECT column_a, column_b FROM table_name WHERE column_a = {{ params.some_value }}
Older post

Use regexp_replace to replace a matched string with a value of another column in PySpark

Use regex to replace the matched string with the content of another column in PySpark

Newer post

Speed up counting the distinct elements in a Spark DataFrame

Use HyperLogLog to calculate the approximate number of distinct elements in Apache Spark

Related Posts

Airflow Prophet

Anomaly detection in Airflow DAG using Prophet library

How to detect problems in Airflow pipeline using Prophet for time series anomaly detection
Published on: 12 Feb 2021

Airflow

How to restart a stuck Airflow DAG

What to do when an Airflow DAG gets stuck and does not want to run
Published on: 31 Dec 2020

Airflow

Why does the DayOfWeekSensor exist in Airflow?

How to make an Airflow DAG wait until a specified day of the week
Published on: 30 Dec 2020

Are you looking for an experienced AI consultant? Do you need assistance with your RAG or Agentic Workflow?
Schedule a call, send me a message on LinkedIn. Schedule a call or send me a message on LinkedIn

>