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
