def check_exists(self, table_name):
# 如果存在先删除，表名，周期，库名
check_sql = "select id from tb_copy_data_count_check_log where table_name='%s' and db_name='%s'" % (
table_name, self.db_name)
print 'check_sql:', check_sql
check_result = conn_db.select(check_sql)
print 'check_result:', check_result
# 存在
if check_result:
condition = ''
if len(check_result) == 1:
condition = "'%s'" % (check_result[0])
else:
for j in check_result:
condition = condition + ',' + "'%s'" % (j[0])
if condition[0] == ',':
condition = condition[1:]
print 'condition:', condition
delete_sql = "delete from tb_copy_data_count_check_log where id in (%s)" % (
condition)
print 'delete_sql:', delete_sql
conn_db.insert(delete_sql)
def get_count_data(self, count_sql, id, table_name, partition_type):
get_count_sh = self.conn_hive_sh + count_sql + '\' > %s%s.txt' % (
self.file_path, table_name)
print 'get_count_sh', get_count_sh
result = os.system(get_count_sh)
check_error = ''
# 同步失败，更新数据库
if result != 0:
# 稽核失败，更新配置表，返回
check_error = '1'
update_task_error_sql = "update tb_copy_data_count_check_task set check_status=%s where id ='%s'" % (
config.check_status_2, id)
print 'update_task_error_sql:', update_task_error_sql
# 失败更新mysql
conn_db.insert(update_task_error_sql)
return
else:
# 稽核成功
check_error = '0'
self.insert_data(id, table_name, partition_type)
def copy_ok(self, table_name, partition_date, st_time, end_time):
# 全量表
if self.table_type == '1':
update_status_sql = "update tb_copy_data_log set copy_status ='" + config.copy_status_2 + "',start_time='" + str(
st_time)[0:19] + "',end_time='" + str(end_time)[
0:19] + "' where table_name='" + table_name + "\'"
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
else:
update_status_sql = "update tb_copy_data_log set copy_status ='" + config.copy_status_2 + "',start_time='" + str(
st_time
)[0:19] + "',end_time='" + str(
end_time
)[0:
19] + "' where table_name='" + table_name + "' and partition_time='" + partition_date + "\'"
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
print '数据迁移同步,更新同步状态完成:', table_name, partition_date
if str(date_time.datetime.now())[11:13] == '06':
print '到达早上6点,自动停止迁移任务,当前时间:', str(date_time.datetime.now())[0:19]
exit(0)
def read_table_name(self):
while True:
# 获取配置参数，带宽，map数
get_config_sql = "select network,map from tb_copy_data_config where migration_batch='" + input_batch + "'"
get_config_result = conn_db.select(get_config_sql)
self.bandwidth = get_config_result[0][0]
self.map_num = get_config_result[0][1]
print '获取配置参数', get_config_result, self.bandwidth, self.map_num
# 获取可以稽核表名列表，表里获取分区键
get_task_sql = "select id, table_name ,partition_type,partition_time from tb_copy_get_partition_task where copy_status='0' and migration_batch='" + input_batch + "' order by partition_time asc limit 10"
print '获取配置参数sql：', get_config_sql
print '获取任务sql：', get_task_sql
select_result = conn_db.select(get_task_sql)
print '获取任务：', select_result
# 取不到任务
if not select_result:
print '无迁移任务'
exit(0)
# 遍历集合，更新此批次状态,status=1
update_sql = "update tb_copy_get_partition_task set copy_status='1'"
in_condition = ''
for i in select_result:
in_condition = str(i[0]) + "," + in_condition
update_sql = update_sql + "where id in (" + in_condition + "0)"
print '更新此批次状态', update_sql
result = conn_db.insert(update_sql)
# 遍历表名，插日志
for i in select_result:
table_name = i[1]
partition_type = i[2]
partition_time = i[3]
# 当前时间
now_time = str(date_time.datetime.now())[0:19]
insert_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name, partition_type,
partition_time, config.copy_status_1, config.chk_status_0,
now_time, '')
result = conn_db.insert(insert_sql)
# 删除分区，添加分区
self.add_partition(table_name, partition_type, partition_time)
# 拷贝数据
self.copy_data(table_name, partition_type, partition_time)
def check_date(self, table_name, partition_date):
# 检测该表是否存在
check_table_sql = "select table_name from tb_copy_data_log where table_name='" + table_name + "\'"
print '检测该表是否存在:', check_table_sql
check_table_result = conn_db.select(check_table_sql)
# 当前时间
now_time = str(date_time.datetime.now())[0:19]
if not check_table_result:
# 初始化mysql同步状态
insert_table_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name, config.partition_statis_date,
partition_date, config.copy_status_1, config.chk_status_0,
now_time, '0')
print '插入记录初始化：', insert_table_sql
conn_db.insert(insert_table_sql)
# 测试
conn_db.insert("insert into test (id) values ('123')")
print '完成初始化'
# 检测该分区是否存在
check_partition_sql = "select table_name from tb_copy_data_log where table_name='" + table_name + "' and partition_time='%s\'" % (
partition_date)
print check_partition_sql
check_partition_result = conn_db.select(check_partition_sql)
if not check_partition_result:
# 插入分区
insert_table_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name, config.partition_statis_date,
partition_date, config.copy_status_1, config.chk_status_0,
now_time, '0')
print '插入记录初始化：', insert_table_sql
check_table_result = conn_db.insert(insert_table_sql)
# 检测同步状态
check_status_sql = " select table_name from tb_copy_data_log where table_name='" + table_name + "' and partition_time= '" + partition_date + "' and copy_status = '" + config.copy_status_0 + "\'"
print check_status_sql
select_result = conn_db.select(check_status_sql)
print '检测结果：', select_result
# 结果判空
if select_result:
return True
else:
return False
def copy_ok(self, table_name, partition_date, st_time, end_time):
update_status_sql = "update tb_copy_data_log set copy_status ='" + config.copy_status_2 + "',start_time='" + str(
st_time)[
0:19] + "',end_time='" + str(
end_time)[0:19] + "' where table_name='" + table_name + "' and partition_time='" + partition_date + "\'"
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
# 修复表
self.repair_table(table_name)
def copy_data(self, table_name, partition_type, partition_date):
print '迁移过程参数', self.bandwidth, self.map_num
# 记录开始迁移时间172.19.168.100
st_time = date_time.datetime.now()
print "[info]" + str(
st_time), ":表数据迁移开始:", table_name, "分区:", partition_date
# hadoop distcp -i hdfs://192.168.190.89:8020/apps/hive/warehouse/csap.db/tb_si_cu_voma_limit_whitelist_day/statis_date=20170617 hdfs://172.19.168.4:8020/warehouse/tablespace/managed/hive/tb_si_cu_voma_limit_whitelist_day
distcp_sh = "hadoop distcp -bandwidth " + self.bandwidth + " -m " + self.map_num + " -pb -i hdfs://192.168.190.88:8020/apps/hive/warehouse/csap.db/" + table_name + "/" + partition_type + "=" + partition_date + " hdfs://172.19.168.100:8020/warehouse/tablespace/managed/hive/csap.db/" + table_name + "/"
# distcp_sh = "hadoop distcp -bandwidth " + self.bandwidth + " -m " + self.map_num + " -pb -i hdfs://192.168.190.89:8020/apps/hive/warehouse/csap.db/" + table_name + "/* hdfs://172.19.168.4:8020/warehouse/tablespace/managed/hive/csap.db/" + table_name + "/"
result = os.system(distcp_sh)
print '#迁移命令:', distcp_sh
print '###################'
print '##### 迁移信息', result, type(result)
print '###################'
end_time = date_time.datetime.now()
# 同步失败，更新数据库
if result != 0:
update_status_sql = "update tb_copy_data_log set remark1='" + str(
result
) + "', copy_status ='" + config.copy_status_3 + "',start_time='" + str(
st_time
)[0:19] + "',end_time='" + str(
end_time
)[0:
19] + "' where table_name='" + table_name + "' and partition_time='" + partition_date + "\'"
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
return
else:
print "[info]" + str(
end_time), ":表数据迁移结束:", table_name, "分区:", partition_date
print '共耗时:', end_time - st_time, 'S'
self.copy_ok(table_name, partition_date, st_time, end_time)
def main(policy_type, database, table_name):
# 策略类型
if policy_type == '1':
result_policy_json = row_policy_json
else:
result_policy_json = policy_json
# 定义策略名
now_time = str(date_time.datetime.now())[0:19].replace('-', '').replace(
' ', '').replace(':', '')
if policy_type == '1':
result_policy_json['name'] = 'row_' + table_name + '_' + now_time
else:
result_policy_json['name'] = table_name + '_' + now_time
# 库名
result_policy_json['resources']['database']['values'] = [database]
# 表名
result_policy_json['resources']['table']['values'] = [table_name]
response = requests.request("POST",
url,
data=json.dumps(row_policy_json),
headers=headers)
create_status = ''
# 策略生成失败
if response.status_code != 200:
print '策略创建失败'
print response.text
print response.status_code
create_status = '1'
else:
print '策略创建成功', result_policy_json['name'], result_policy_json['resources']['database']['values'], \
result_policy_json['resources']['table']['values']
create_status = '0'
# 更新mysql
ranger_sql = "insert into tb_ranger_policy (policy_name,db_name,table_name,create_status,policy_type,create_time) values('%s','%s','%s','%s','%s','%s')" % (
result_policy_json['name'], database, table_name, create_status,
policy_type, now_time)
print 'ranger_sql:', ranger_sql
conn_db.insert(ranger_sql)
def insert_data(self, id, table_name, partition_type):
# 清理数据
self.check_exists(table_name)
count_result = open(self.file_path + table_name + '.txt', 'r').readlines()
for i in count_result:
table_info = i.replace('\n', '').split('\t')
partition_time, count_num = table_info[0], table_info[1]
insert_sql = "insert into tb_copy_data_count_check_log (table_name,partition_type,partition_time,db_name,count_num,update_time) values ('%s','%s','%s','%s','%s',now())" % (
table_name, partition_type, partition_time, self.db_name, count_num)
# print 'insert_sql:', insert_sql
conn_db.insert(insert_sql)
def read_table_name(self):
while True:
# 获取可以稽核表名列表，表里获取分区键，只稽核分区表
# get_task_sql = "select id, table_name ,partition_type from tb_copy_data_count_check_task where " + self.check_status + "='0' and batch_num ='" + self.batch_num + "' limit 10"
# get_task_sql = "select id,table_name,partition_type,partition_time from tb_copy_data_log where remark3 is null and start_time >'2020-08-13 10:00:51' and copy_status='2' and (%s is NULL or %s ='') order by partition_time desc,start_time asc limit 1000;" % (
# # self.count_col, self.count_col)
get_task_sql = "select id,table_name,partition_type,partition_time from tb_copy_data_log where remark3 is null and start_time >'2020-08-13 10:00:51' and copy_status='2' and (%s is NULL or %s ='') limit 1000;" % (
self.count_col, self.count_col)
print '获取任务sql：', get_task_sql
select_result = conn_db.select(get_task_sql)
# print '获取任务：', select_result
# 取不到任务
if not select_result:
print '无稽核任务'
exit(0)
# # 遍历集合，更新此批次状态,status=1
update_status_list = []
update_sql = "update tb_copy_data_log set remark3='checked' "
in_condition = ''
for i in select_result:
in_condition = str(i[0]) + "," + in_condition
update_sql = update_sql + "where id in (" + in_condition + "0)"
# print '更新此批次状态', update_sql
result = conn_db.insert(update_sql)
self.start_time = time.time()
# 遍历表名，插日志
for i in select_result:
id, table_name, partition_type, partition_time = i[0], i[1], i[
2], i[3]
# count_sql = "select %s,count(*) from %s group by %s " % (partition_type, table_name, partition_type)
count_sql = "select partition_type,partition_time,count_num from tb_copy_data_count_check_log where db_name='%s' and table_name='%s' and partition_time='%s' " % (
self.db_name, table_name, partition_time)
# print 'count_sql', count_sql
# 获取稽核结果
self.get_count_data(count_sql, id, table_name, partition_type,
partition_time)
# 更新mysql
# print 'update_sql_list', self.update_sql_list
conn_db.insert_batch(self.update_sql_list)
print 'sleep 5s,更新数据量:', len(
self.update_sql_list), '耗时:', time.time() - self.start_time
self.update_sql_list = []
time.sleep(3)
def get_task(self):
while True:
# 获取可以稽核表名列表，表里获取分区键
get_task_sql = "select id, table_name ,batch_num from tb_close_lock_get_task where copy_status='0' and batch_num='" + self.input_batch + "' order by id asc limit 1"
print '获取任务sql：', get_task_sql
select_result = conn_db.select(get_task_sql)
print '获取任务：', select_result
# 取不到任务
if not select_result:
print '无迁移任务'
exit(0)
# 遍历集合，更新此批次状态,status=1
update_sql = "update tb_close_lock_get_task set copy_status='1'"
in_condition = ''
for i in select_result:
in_condition = str(i[0]) + "," + in_condition
update_sql = update_sql + "where id in (" + in_condition + "0)"
print '更新此批次状态', update_sql
result = conn_db.insert(update_sql)
# 遍历表名，插日志，开始执行任务
for i in select_result:
table_name = i[1]
# 当前时间
now_time = str(date_time.datetime.now())[0:19]
insert_sql = "insert into tb_close_lock_log (table_name,rename_status,create_status,insert_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s')" % (
table_name, config.rename_status_0, config.create_status_0,
config.insert_status_0, now_time, '')
result = conn_db.insert(insert_sql)
self.check_name(table_name)
print '无任务'
def copy_ok(table_name, partition_date, st_time, end_time):
update_status_sql = "update tb_copy_data_log set copy_status ='" + copy_status_2 + "',start_time='" + str(st_time)[
0:19] + "',end_time='" + str(
end_time)[0:19] + "' where table_name='" + table_name + "' and partition_time='" + partition_date + "\'"
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
# 更新now_date
update_task_sql = "update tb_copy_get_task set now_partition ='" + partition_date + "' where table_name='" + table_name + "\'"
print '更新sql:', update_task_sql
conn_db.insert(update_task_sql)
print '数据迁移同步,更新同步状态完成:', table_name, partition_date
if str(date_time.datetime.now())[11:13] == '06':
print '到达早上6点,自动停止迁移任务,当前时间:', str(date_time.datetime.now())[0:19]
exit(0)
def copy_data(self, table_name):
print '### 复制数据'
update_status_sql = "update tb_close_lock_log set insert_status='" + str(
config.insert_status_1
) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
new_table_name = table_name + "_bakup"
insert_sql = "insert overwrite table " + table_name + " select * from " + new_table_name
insert_sql_sh = new_hive + insert_sql + "\""
print 'insert_sql_sh: ', insert_sql_sh
result = os.system(insert_sql_sh)
if result != 0:
# 失败
print '### error'
update_status_sql = "update tb_close_lock_log set insert_status='" + str(
config.insert_status_3
) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
else:
# 成功
update_status_sql = "update tb_close_lock_log set insert_status='" + str(
config.insert_status_2
) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
def read_table_name(self):
while True:
# 获取可以稽核表名列表，表里获取分区键，只稽核分区表
get_task_sql = "select id, table_name ,partition_type from tb_copy_data_count_check_task where " + self.check_status + "='0' and batch_num ='" + self.batch_num + "' limit 1"
print '获取任务sql：', get_task_sql
select_result = conn_db.select(get_task_sql)
print '获取任务：', select_result
# 取不到任务
if not select_result:
print '无稽核任务'
exit(0)
# 遍历集合，更新此批次状态,status=1
update_sql = "update tb_copy_data_count_check_task set %s='1'" % (
self.check_status)
in_condition = ''
for i in select_result:
in_condition = str(i[0]) + "," + in_condition
update_sql = update_sql + "where id in (" + in_condition + "0)"
print '更新此批次状态', update_sql
result = conn_db.insert(update_sql)
# 遍历表名，插日志
for i in select_result:
id, table_name, partition_type = i[0], i[1], i[2]
count_sql = "select %s,count(*) from %s group by %s " % (
partition_type, table_name, partition_type)
print 'count_sql', count_sql
# 获取稽核结果
self.get_count_data(count_sql, id, table_name, partition_type)
def get_error_log():
global start_time
try:
get_error_sql = "select id,table_name,partition_type,partition_time,copy_status from tb_copy_data_log where remark2 is null and remark5 ='' and partition_type<>'all' and ( copy_status='3' or sy_count<>ocdp_count or sy_count is null or ocdp_count is null) limit 200"
print 'get_error_sql', get_error_sql
get_error_result = conn_db.select(get_error_sql)
start_time = time.time()
# # 遍历集合，更新此批次状态,status=1
update_status_list = []
update_sql = "update tb_copy_data_log set remark5='checked' "
in_condition = ''
for i in get_error_result:
in_condition = str(i[0]) + "," + in_condition
update_sql = update_sql + "where id in (" + in_condition + "0)"
# print '更新此批次状态', update_sql
result = conn_db.insert(update_sql)
for i in get_error_result:
try:
table_name, partition_type, partition_time, copy_status = i[
1], i[2], i[3], i[4]
check_partition(table_name, partition_type, partition_time,
copy_status)
except Exception as e:
print e
print '异常2'
# 批量更新mysql
update_mysql_batch()
except Exception as e:
print e
print '异常1'
def ssh_check_sy_data(self, table_name, partition_type, partition_time):
check_sh = config.sy_ssh_sh + "select count(%s) from %s where %s='%s' ; \' \" > /home/ocdp/hyn/copy_hdfs_data/copy_data_sy_count/table_count/%s_%s.txt " % (
partition_type, table_name, partition_type, partition_time,
table_name, partition_time)
print 'check_sh:', check_sh
result = os.system(check_sh)
f = open(
'/home/ocdp/hyn/copy_hdfs_data/copy_data_sy_count/table_count/%s_%s.txt'
% (table_name, partition_time), 'r')
table_info = f.readlines()
f.close()
# table_info = open('./table_count/%s_%s.txt' % (table_name, partition_time), 'r').readlines()
print '### table_info', table_info
# 判断是否为空
if len(table_info) == 0:
# 不存在
print 'sy库不存在'
update_sql = "update tb_tmp_copy_data_log set result_not_exist='no' where table_name='%s' and partition_time='%s'" % (
table_name, partition_time)
conn_db.insert(update_sql)
return False
else:
table_count = int(table_info[0].replace('\n', ''))
if table_count > 0:
print '#sy库有数据', table_count
update_sql = "update tb_tmp_copy_data_log set sy_count='%s',result_not_exist='%s' where table_name='%s' and partition_time='%s'" % (
table_count, table_count, table_name, partition_time)
conn_db.insert(update_sql)
return True
else:
# 老库为0，是否更新到mysql
print 'sy库数量为0'
update_sql = "update tb_tmp_copy_data_log set result_not_exist='0' where table_name='%s' and partition_time='%s'" % (
table_name, partition_time)
conn_db.insert(update_sql)
return False
def rename_table(self, table_name):
print '重命名'
now_time = date_time.datetime.now()
# 开始重命名
update_status_sql = "update tb_close_lock_log set rename_status='" + str(
config.rename_status_1) + "', start_time='" + str(
now_time) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
new_table_name = table_name + "_bakup"
rename_sql = "ALTER TABLE " + table_name + " RENAME TO " + new_table_name + ";"
rename_sql_sh = new_hive + rename_sql + "\""
print 'rename_sql_sh', rename_sql_sh
result = os.system(rename_sql_sh)
if result != 0:
# 失败
print '### error'
update_status_sql = "update tb_close_lock_log set rename_status='" + str(
config.rename_status_3) + "', start_time='" + str(
now_time) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
else:
# 成功
update_status_sql = "update tb_close_lock_log set rename_status='" + str(
config.rename_status_2) + "', start_time='" + str(
now_time) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
self.create_table_like(table_name)
def check_sy_data(self, table_name, partition_type, partition_time):
# 检测老hive库数据量是为0
count_sql = "select count_num from tb_copy_data_count_check_log where table_name ='%s' and db_name='old' and partition_time='%s' " % (
table_name, partition_time)
result = conn_db.select(count_sql)
print 'result:', result
if result:
# 不为空
result_count = int(result[0][0])
if result_count > 0:
update_sql = "update tb_copy_data_log set sy_count='%s',result_not_exist='%s' where table_name='%s' and partition_time='%s'" % (
result_count,result_count,table_name, partition_time)
conn_db.insert(update_sql)
return True
else:
# 老库为0，是否更新到mysql
print 'sy库数量为0'
update_sql = "update tb_copy_data_log set result_not_exist='0' where table_name='%s' and partition_time='%s'" % (
table_name, partition_time)
conn_db.insert(update_sql)
return False
else:
# 不存在
print 'sy库不存在'
update_sql = "update tb_copy_data_log set result_not_exist='no' where table_name='%s' and partition_time='%s'" % (
table_name, partition_time)
conn_db.insert(update_sql)
return False
def create_table_like(self, table_name):
print '### 创建新表'
update_status_sql = "update tb_close_lock_log set create_status='" + str(
config.create_status_1
) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
new_table_name = table_name + "_bakup"
create_table_like_sql = "create table " + table_name + " like " + new_table_name
create_table_like_sql_sh = new_hive + create_table_like_sql + '\"'
print 'create_table_like_sql_sh', create_table_like_sql_sh
result = os.system(create_table_like_sql_sh)
if result != 0:
# 失败
print '### error'
update_status_sql = "update tb_close_lock_log set create_status='" + str(
config.create_status_3
) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
else:
# 成功
update_status_sql = "update tb_close_lock_log set create_status='" + str(
config.create_status_2
) + "' where table_name= '" + table_name + "\' "
print '更新sql:', update_status_sql
conn_db.insert(update_status_sql)
self.copy_data(table_name)
def check_date(self, table_name, partition_date, error):
print '初始化检测，更新同步状态'
# 检测该表是否存在日志表里，初始化准备
check_table_sql = "select table_name from tb_copy_data_log where table_name='" + table_name + "\'"
print '检测该表是否存在:', check_table_sql
check_table_result = conn_db.select(check_table_sql)
print '检查结果：', check_table_result
# 当前时间
now_time = str(date_time.datetime.now())[0:19]
# 如果表不存在,返回true，初始化，状态改为正在迁移，执行迁移
if not check_table_result:
# 全量表，检测是否在日志表里，日志表里状态是否已同步完成
if self.table_type == '1':
if not error:
# 初始化mysql同步状态
insert_table_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name, config.all_table,
partition_date, config.copy_status_1,
config.chk_status_0, now_time, '0')
print '插入记录初始化：', insert_table_sql
conn_db.insert(insert_table_sql)
# 测试
conn_db.insert("insert into test (id) values ('123')")
print '完成初始化'
# 同步失败
else:
# 初始化mysql同步状态
insert_table_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name, config.all_table,
partition_date, config.copy_status_3,
config.chk_status_0, now_time, '0')
print '插入记录同步失败：', insert_table_sql
conn_db.insert(insert_table_sql)
# 测试
conn_db.insert("insert into test (id) values ('123')")
print '完成失败状态同步'
# 非全量表,todo
else:
pass
return True
# 表存在日志表里，检测分区，判断是否已迁移
else:
# 全量表，检测是否在日志表里，日志表里状态是否已同步完成
if self.table_type == '1':
if not error:
# 获取迁移状态
copy_status_sql = "select * from tb_copy_data_log where table_name='" + table_name + "';"
copy_status = conn_db.select(copy_status_sql)
# 已同步完成
if copy_status[0][0] == '2':
return False
# 同步失败
else:
return True
# 更新失败同步状态
else:
# 更新失败同步状态
update_table_sql = "update tb_copy_data_log set copy_status='" + config.copy_status_3 + "' ,end_time='" + str(
date_time.datetime.now(
))[0:19] + "' where table_name='" + table_name + "\'"
print '插入记录同步失败：', update_table_sql
conn_db.insert(update_table_sql)
# 测试
conn_db.insert("insert into test (id) values ('123')")
print '完成失败状态同步'
# 非全量表,todo
else:
# 检测分区是否已存在
if not check_table_result:
# 初始化mysql同步状态
insert_table_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name,
config.partition_statis_date, partition_date,
config.copy_status_1, config.chk_status_0, now_time,
'0')
print '插入记录初始化：', insert_table_sql
conn_db.insert(insert_table_sql)
# 测试
conn_db.insert("insert into test (id) values ('123')")
print '完成初始化'
# 检测该分区是否存在
check_partition_sql = "select table_name from tb_copy_data_log where table_name='" + table_name + "' and partition_time='%s\'" % (
partition_date)
print check_partition_sql
check_partition_result = conn_db.select(check_partition_sql)
if not check_partition_result:
# 插入分区
insert_table_sql = "insert into tb_copy_data_log (data_source,table_name,partition_type,partition_time,copy_status,chk_status,start_time,end_time) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
config.data_source, table_name,
config.partition_statis_date, partition_date,
config.copy_status_1, config.chk_status_0, now_time,
'0')
print '插入记录初始化：', insert_table_sql
check_table_result = conn_db.insert(insert_table_sql)
# 检测同步状态
check_status_sql = " select table_name from tb_copy_data_log where table_name='" + table_name + "' and partition_time= '" + partition_date + "' and copy_status = '" + config.copy_status_0 + "\'"
print check_status_sql
select_result = conn_db.select(check_status_sql)
print '检测结果：', select_result
# 结果判空
if select_result:
return True
else:
return False
def update_mysql(table_name, partition_time):
update_sql = "update tb_copy_data_log set copy_status ='2' ,remark2='partition not exist' where table_name='" + table_name + "' and partition_time='" + partition_time + "\'"
conn_db.insert(update_sql)
print 'update_sql', update_sql
