Suppose an Airflow DAG extracts hourly page-view counts and needs to store them in PostgreSQL. SQLExecuteQueryOperator runs the SQL that writes those counts. It is a specialized alternative to writing database authentication, connection handling, and query execution inside a PythonOperator. section 5.4 section 6.2.1
A file-backed workflow has three parts:
- Install the common SQL provider so the operator can be imported: pip install apache-airflow-providers-common-sql. section 5.4
- Have an upstream task write INSERT statements to a SQL file accessible to the task that will execute them. Airflow tasks may run on different machines, so they cannot pass an in-memory object between tasks. section 5.4
- Configure an Airflow database connection, then give the operator its task_id, conn_id, and SQL filename. Set the DAG’s template_searchpath to the file’s directory so the operator can find it. section 5.4
Execute generated INSERT statements
An upstream Python task writes statements such as INSERT INTO pageview_counts VALUES ('Google', 333, '2024-01-01T00:00:00'); to /tmp/postgres_query.sql. The downstream task uses SQLExecuteQueryOperator(task_id="write_to_postgres", conn_id="my_postgres", sql="postgres_query.sql") in a DAG with template_searchpath="/tmp". The my_postgres connection can be added with the Airflow CLI. section 5.4
The operator does not take CSV data directly as inserts in this workflow; generating SQL first bridges that gap. Ensure the SQL file is available wherever the executing task runs. Repeated DAG runs can also insert duplicate rows, so design the table’s key and INSERT conflict handling for retries or reruns. section 5.4
