0

I'm trying to run airflow with Azure SQL database as backend using mssql+pyodbc connection string(all relevant drivers have been installed).

while airflow is able to connect to DB and create tables, i.e, airflow initdb runs successfully, I'm facing issues while running airflow scheduler, as a result, the tasks triggered are always in "running" state.

This is the error I get while running airflow scheduler:

*sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '1'. (102) (SQLExecDirectW)")
     [SQL: SELECT dag.dag_id AS dag_dag_id
    FROM dag
    WHERE dag.is_paused IS 1 AND dag.dag_id IN (?)]
    [parameters: ('example_http_operator',)]*

(Background on this error at: http://sqlalche.me/e/13/f405)

I'm using apache-airflow==1.10.11. If you were able to run airflow + azure SQL DB with any configuration please feel free to jump in.

Andrzej Sydor
  • 1,373
  • 4
  • 13
  • 28
sy0539
  • 1
  • airflow doesn't officially support mssql / azure as backend db. It's only tested against MySQL and PostgreSQL. – Elad Kalif Dec 16 '20 at 13:08
  • Is @Elad comment still true? The 2.2.0 release notes (end of 2021) still call MSSQL experimental. My DBAs only support MSSQL, would help me a ton. – yzorg Mar 01 '22 at 17:24
  • Airflow 2.2 does not support mssql. Future versions will support it. – Elad Kalif Mar 04 '22 at 17:48

2 Answers2

0

I found a document and talk the configuration about run airflow + azure SQL DB. Maybe it's helpful for you.

Ref: Setting up Airflow on Azure & connecting to MS SQL Server

This post also give some configurations about it: Apache Airflow - Connection issue to MS SQL Server using pymssql + SQLAlchemy

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
0

For MSSQL as backend DB, there is workaround in Airflow#10713. I using apache-airflow==1.10.15 and solved same error as yours.

The command suggested is attached, but I use vi update instead of run sed command.

RUN sed -i 's/import copy/import copy,sqlalchemy/g' /usr/local/lib/python3.6/site-packages/airflow/models/dag.py \ && sed -i 's/DagModel.is_paused.is_(True)/DagModel.is_paused == sqlalchemy.sql.expression.true()/g' /usr/local/lib/python3.6/site-packages/airflow/models/dag.py
Jesse
  • 3,243
  • 1
  • 22
  • 29