def migrate_temp_table_to_tar_table(self):
"""
把数据从临时表迁移到正式表
分为两步：
1. 把更新的行同步过去
2. 把新增的行同步过去
"""
set_caluse = ",".join(["%s=b.%s" % (c, c) for c in self.tar_columns])
pkeys_caluse = " AND ".join(["a.%s=b.%s" % (c, c) for c in self.tar_pkeys])
pkeys_caluse2 = " AND ".join(["%s=tmp.%s" % (c, c) for c in self.tar_pkeys])
columns = ",".join(['"%s"' % c for c in self.tar_columns])
data = {
"table": self.tar_table,
"temp": self.tmp_tar_table,
"set_caluse": set_caluse,
"pkeys_caluse": pkeys_caluse,
"pkeys_caluse2": pkeys_caluse2,
"columns": columns,
}
update_sql = """UPDATE {table} a SET {set_caluse} FROM {temp} b WHERE {pkeys_caluse}"""
update_sql = update_sql.format(**data)
insert_sql = """INSERT INTO {table} ({columns}) (SELECT {columns} FROM {temp} tmp WHERE not exists (SELECT 1 FROM {table} WHERE {pkeys_caluse2}));"""
insert_sql = insert_sql.format(**data)
with create_external_session(self.tar_conn) as sess:
self.log.info("migrate_temp_table_to_tar_table start")
self.log.info("migrate_temp_table_to_tar_table update_sql: %s", update_sql)
t1 = time.time()
sess.execute(update_sql)
self.log.info("migrate_temp_table_to_tar_table insert_sql: %s", insert_sql)
sess.execute(insert_sql)
self.log.info("migrate_temp_table_to_tar_table end")
def get_columns(conn_id, table_name, session=None):
conn = session.query(Connection).filter_by(conn_id=conn_id).first()
if not conn:
return jsonify({
"code": -1,
"msg": "不存在名为%s的Connection" % conn_id,
})
with create_external_session(conn) as external_session:
columns = dbutil.get_cloumns(external_session, table_name)
return jsonify({
"code": 0,
"msg": "SUCCESS",
"tables": columns
})
def drop_temp_table(self):
"""
删掉临时表
"""
drop_sql = "DROP TABLE IF EXISTS {table}"
if self.tar_conn.conn_type.strip() in ["mssql", "sqlserver"]:
drop_sql = """
IF EXISTS
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{table}')
DROP TABLE {table}
"""
drop_sql = drop_sql.format(table=self.tmp_tar_table)
with create_external_session(self.tar_conn) as sess:
sess.execute(drop_sql)
def create_temp_table(self):
"""
创建用于增量同步的临时表
"""
create_sql = "CREATE TABLE {new_table} AS (SELECT * FROM {old_table} WHERE 1=2)"
if self.tar_conn.conn_type.strip() in ["mssql", "sqlserver"]:
create_sql = "Select * into {new_table} from {old_table} WHERE 1=2"
data = {
"new_table": self.tmp_tar_table,
"old_table": self.tar_table
}
create_sql = create_sql.format(**data)
with create_external_session(self.tar_conn) as sess:
sess.execute(create_sql)
def refresh_current_max_append_value(self):
"""
刷新本次增量字段的最大值
"""
where = ""
if self.tar_source_from_column:
where = "%s='%s'" % (self.tar_source_from_column, self.src_source_from)
sql = "SELECT max(%s) FROM %s " % (self.append_column, self.tmp_tar_table)
if where:
sql = sql + " WHERE " + where
with create_external_session(self.tar_conn) as sess:
result = sess.execute(sql)
record = result.fetchone()
if record[0]:
self.max_current_append_value = record[0].strftime("%Y-%m-%d %H:%M:%S")
return self.max_current_append_value
def refresh_max_append_column_value(self):
"""
刷新增量字段的最大值
"""
where = ""
if self.tar_source_from_column:
where = "%s='%s'" % (self.tar_source_from_column, self.src_source_from)
sql = "SELECT max(%s) FROM %s " % (self.append_column, self.tar_table)
if where:
sql = sql + " WHERE " + where
self.log.info("refresh_max_append_column_value sql: %s", sql)
with create_external_session(self.tar_conn) as sess:
result = sess.execute(sql)
record = result.fetchone()
if record:
self.max_append_column_value = record[0]
return self.max_append_column_value