Airflow - SQL Server connection

Question

I have a question for changing the backend connection from SQLite to SQL Server. After passing in the correct connection string for sql_alchemy_conn, I run this command: airflow initdb. I get the following error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]A table can only have one timestamp column. Because table 'task_reschedule' already has one, the column 'start_date' cannot be added. (2738) (SQLExecDirectW)") [SQL: '\nCREATE TABLE task_reschedule (\n\tid INTEGER NOT NULL IDENTITY(1,1), \n\ttask_id VARCHAR(250) NOT NULL, \n\tdag_id VARCHAR(250) NOT NULL, \n\texecution_date TIMESTAMP NOT NULL, \n\ttry_number INTEGER NOT NULL, \n\tstart_date TIMESTAMP NOT NULL, \n\tend_date TIMESTAMP NOT NULL, \n\tduration INTEGER NOT NULL, \n\treschedule_date TIMESTAMP NOT NULL, \n\tPRIMARY KEY (id), \n\tCONSTRAINT task_reschedule_dag_task_date_fkey FOREIGN KEY(task_id, dag_id, execution_date) REFERENCES task_instance (task_id, dag_id, execution_date)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)

Javiar Sandra Javiar Sandra · Accepted Answer · 2019-02-15T21:29:27

So this works for me: In the file: 0a2a5b66e19d_add_task_reschedule_table.py add this:

def mysql_datetime():
    return mysql.DATETIME(timezone=True)

and replace any lines which has timestamp() such as the below:

sa.Column('execution_date', timestamp(), nullable=False, server_default=None),

with this:

sa.Column('execution_date', mysql_datetime(), nullable=False, server_default=None),

Once I made this change, the above error disappears but I am not sure if there are any other unintended consequences. If so I will update here or just resort to using MySQL database.

Airflow - SQL Server connection

1 Answers