def get_dimension_scd2_ddl(self, table, columns): target_engine = self._get_engine(self.settings.get("target_server"), self.settings.get("target_db")) target_meta = MetaData(bind=target_engine) dim_columns = [ Column( self.settings.get("dimension_id_column_name"), Integer, primary_key=True, autoincrement=True, ) ] dim_columns += list([ Column(x.name, x.type, primary_key=False, nullable=x.nullable) for x in self.temporal_table.columns if x.name in columns ]) dim_columns.append( Column("ValidFrom", mssql.DATETIME2(0), nullable=False)) dim_columns.append( Column("ValidTo", mssql.DATETIME2(0), nullable=False)) dim_table = Table( table, target_meta, *dim_columns, schema=self.settings.get("dimension_schema"), ) dim_table_ddl = self._get_table_ddl(dim_table, target_engine) scd2_sql = "--Type 2 SCD\n" + table_creation_template.render( dropfirst=self.settings.get("dropfirst"), table=table, schema=self.settings.get("dimension_schema"), create=dim_table_ddl, ) proc_name = f'BuildDim{table.replace(" ", "")}' sc2_proc_sql = scd2_load_template.render( dropfirst=self.settings.get("dropfirst"), procedurename=proc_name, dimension_schema=self.settings.get("dimension_schema"), dimension_table=table, selected_columns=columns, temporal_schema=self.settings.get("temporal_schema"), temporal_table=table, dimension_id_column_name=self.settings.get( "dimension_id_column_name"), ) return scd2_sql, sc2_proc_sql
def upgrade(): """Apply Add queued_at column to dagrun table""" conn = op.get_bind() if conn.dialect.name == "mssql": op.add_column('dag_run', sa.Column('queued_at', mssql.DATETIME2(precision=6), nullable=True)) else: op.add_column('dag_run', sa.Column('queued_at', sa.DateTime(), nullable=True))
def alter_mssql_datetime_column(conn, op, table_name, column_name, nullable): """Update the datetime column to datetime2(6)""" op.alter_column( table_name=table_name, column_name=column_name, type_=mssql.DATETIME2(precision=6), nullable=nullable, )
def _get_timestamp(conn): dialect_name = conn.dialect.name if dialect_name == "mssql": return mssql.DATETIME2(precision=6) if _use_date_time2(conn) else mssql.DATETIME elif dialect_name != "mysql": return sa.TIMESTAMP(timezone=True) else: return mysql.TIMESTAMP(fsp=6, timezone=True)
def _datetime_type(dialect_name): if dialect_name == "mssql": from sqlalchemy.dialects import mssql return mssql.DATETIME2(precision=6) elif dialect_name == "mysql": from sqlalchemy.dialects import mysql return mysql.DATETIME(fsp=6) return sa.TIMESTAMP(timezone=True)
def downgrade(): """Unapply Rename ``last_scheduler_run`` column in ``DAG`` table to ``last_parsed_time``""" conn = op.get_bind() if conn.dialect.name == "mssql": with op.batch_alter_table('dag') as batch_op: batch_op.alter_column('last_parsed_time', new_column_name='last_scheduler_run', type_=mssql.DATETIME2(precision=6)) else: with op.batch_alter_table('dag') as batch_op: batch_op.alter_column('last_parsed_time', new_column_name='last_scheduler_run', type_=sa.TIMESTAMP(timezone=True))
def upgrade(): """Apply rename last_scheduler_run column""" conn = op.get_bind() if conn.dialect.name == "mssql": with op.batch_alter_table('dag') as batch_op: batch_op.alter_column('last_scheduler_run', new_column_name='last_parsed_time', type_=mssql.DATETIME2(precision=6)) else: with op.batch_alter_table('dag') as batch_op: batch_op.alter_column('last_scheduler_run', new_column_name='last_parsed_time', type_=sa.TIMESTAMP(timezone=True))
def recreate_mssql_ts_column(conn, op, table_name, column_name): """ Drop the timestamp column and recreate it as datetime or datetime2(6) """ if _is_timestamp(conn, table_name, column_name) and is_table_empty(conn, table_name): with op.batch_alter_table(table_name) as batch_op: constraint_dict = get_table_constraints(conn, table_name) drop_column_constraints(batch_op, column_name, constraint_dict) batch_op.drop_column(column_name=column_name) if _use_date_time2(conn): batch_op.add_column(sa.Column(column_name, mssql.DATETIME2(precision=6), nullable=False)) else: batch_op.add_column(sa.Column(column_name, mssql.DATETIME, nullable=False)) create_constraints(batch_op, column_name, constraint_dict)
def upgrade(): """Apply Remove id column from xcom""" conn = op.get_bind() inspector = Inspector.from_engine(conn) with op.batch_alter_table('xcom') as bop: xcom_columns = [col.get('name') for col in inspector.get_columns("xcom")] if "id" in xcom_columns: if conn.dialect.name == 'mssql': pk_name = inspector.get_pk_constraint('xcom')['name'] bop.drop_constraint(pk_name, type_='primary') bop.drop_column('id') bop.drop_index('idx_xcom_dag_task_date') if conn.dialect.name == "mssql": bop.alter_column('key', existing_type=mssql.VARCHAR(512), nullable=False) bop.alter_column('execution_date', existing_type=mssql.DATETIME2(), nullable=False) bop.create_primary_key('pk_xcom', ['dag_id', 'task_id', 'key', 'execution_date'])
def _get_timestamp(conn): if _use_date_time2(conn): return mssql.DATETIME2(precision=6) else: return mssql.DATETIME
def upgrade(): """ Change datetime to datetime2(6) when using MSSQL as backend """ conn = op.get_bind() if conn.dialect.name == "mssql": result = conn.execute( """SELECT CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN '2000' WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN '2005' ELSE '2005Plus' END AS MajorVersion""" ).fetchone() mssql_version = result[0] if mssql_version in ("2000", "2005"): return with op.batch_alter_table( 'task_reschedule') as task_reschedule_batch_op: task_reschedule_batch_op.drop_index( 'idx_task_reschedule_dag_task_date') task_reschedule_batch_op.drop_constraint( 'task_reschedule_dag_task_date_fkey', type_='foreignkey') task_reschedule_batch_op.alter_column( column_name="execution_date", type_=mssql.DATETIME2(precision=6), nullable=False, ) task_reschedule_batch_op.alter_column( column_name='start_date', type_=mssql.DATETIME2(precision=6)) task_reschedule_batch_op.alter_column( column_name='end_date', type_=mssql.DATETIME2(precision=6)) task_reschedule_batch_op.alter_column( column_name='reschedule_date', type_=mssql.DATETIME2(precision=6)) with op.batch_alter_table('task_instance') as task_instance_batch_op: task_instance_batch_op.drop_index('ti_state_lkp') task_instance_batch_op.drop_index('ti_dag_date') modify_execution_date_with_constraint(conn, task_instance_batch_op, 'task_instance', mssql.DATETIME2(precision=6), False) task_instance_batch_op.alter_column( column_name='start_date', type_=mssql.DATETIME2(precision=6)) task_instance_batch_op.alter_column( column_name='end_date', type_=mssql.DATETIME2(precision=6)) task_instance_batch_op.alter_column( column_name='queued_dttm', type_=mssql.DATETIME2(precision=6)) task_instance_batch_op.create_index( 'ti_state_lkp', ['dag_id', 'task_id', 'execution_date'], unique=False) task_instance_batch_op.create_index('ti_dag_date', ['dag_id', 'execution_date'], unique=False) with op.batch_alter_table( 'task_reschedule') as task_reschedule_batch_op: task_reschedule_batch_op.create_foreign_key( 'task_reschedule_dag_task_date_fkey', 'task_instance', ['task_id', 'dag_id', 'execution_date'], ['task_id', 'dag_id', 'execution_date'], ondelete='CASCADE') task_reschedule_batch_op.create_index( 'idx_task_reschedule_dag_task_date', ['dag_id', 'task_id', 'execution_date'], unique=False) with op.batch_alter_table('dag_run') as dag_run_batch_op: modify_execution_date_with_constraint(conn, dag_run_batch_op, 'dag_run', mssql.DATETIME2(precision=6), None) dag_run_batch_op.alter_column(column_name='start_date', type_=mssql.DATETIME2(precision=6)) dag_run_batch_op.alter_column(column_name='end_date', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name="log", column_name="execution_date", type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='log', column_name='dttm', type_=mssql.DATETIME2(precision=6)) with op.batch_alter_table('sla_miss') as sla_miss_batch_op: modify_execution_date_with_constraint(conn, sla_miss_batch_op, 'sla_miss', mssql.DATETIME2(precision=6), False) sla_miss_batch_op.alter_column(column_name='timestamp', type_=mssql.DATETIME2(precision=6)) op.drop_index('idx_task_fail_dag_task_date', table_name='task_fail') op.alter_column(table_name="task_fail", column_name="execution_date", type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='task_fail', column_name='start_date', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='task_fail', column_name='end_date', type_=mssql.DATETIME2(precision=6)) op.create_index('idx_task_fail_dag_task_date', 'task_fail', ['dag_id', 'task_id', 'execution_date'], unique=False) op.drop_index('idx_xcom_dag_task_date', table_name='xcom') op.alter_column(table_name="xcom", column_name="execution_date", type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='xcom', column_name='timestamp', type_=mssql.DATETIME2(precision=6)) op.create_index('idx_xcom_dag_task_date', 'xcom', ['dag_id', 'task_id', 'execution_date'], unique=False) op.alter_column(table_name='dag', column_name='last_scheduler_run', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='dag', column_name='last_pickled', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='dag', column_name='last_expired', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='dag_pickle', column_name='created_dttm', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='import_error', column_name='timestamp', type_=mssql.DATETIME2(precision=6)) op.drop_index('job_type_heart', table_name='job') op.drop_index('idx_job_state_heartbeat', table_name='job') op.alter_column(table_name='job', column_name='start_date', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='job', column_name='end_date', type_=mssql.DATETIME2(precision=6)) op.alter_column(table_name='job', column_name='latest_heartbeat', type_=mssql.DATETIME2(precision=6)) op.create_index('idx_job_state_heartbeat', 'job', ['state', 'latest_heartbeat'], unique=False) op.create_index('job_type_heart', 'job', ['job_type', 'latest_heartbeat'], unique=False)
def load_dialect_impl(self, dialect): if dialect.name == 'mssql': return mssql.DATETIME2(precision=6) elif dialect.name == 'mysql': return mysql.TIMESTAMP(fsp=6) return super().load_dialect_impl(dialect)
def _mssql_datetime(): from sqlalchemy.dialects import mssql return mssql.DATETIME2(precision=6)
def _mssql_TIMESTAMP(): from sqlalchemy.dialects import mssql return mssql.DATETIME2( precision=6) if MSSQL_USE_DATE_TIME2 else mssql.DATETIME