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() sql_list = [] 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 sql_list.append(insert_sql) # 测试 # break # 批量插入数据 conn_db.insert_batch(sql_list)
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 update_mysql_batch(): global update_sql_list, start_time conn_db.insert_batch(update_sql_list) print update_sql_list print '更新数据量:', len(update_sql_list), '耗时', time.time() - start_time update_sql_list = [] start_time = 0
def send_sms(sms_info): now_time = str(date_time.datetime.now())[0:19] print '当前时间:', now_time # user_list = ['15264899856', '15936466867', '13665656104'] user_list = ['15936466867'] sms_info_list = [] sms_info = sms_info + ' 当前时间:' + now_time for i in user_list: insert_sql = "insert into tb_sys_sms_send_cur (serv_number,send_date,text,opt_user,area_code,sms_type,plan_id,temp_id) " \ "values ('%s','%s','%s','%s','%s','%s','%s','%s') " % \ (i, '2020-05-21 16:00:00', sms_info, 'mazhong', '999', '主机告警', 'hourt_alerm', '4649') sms_info_list.append(insert_sql) print 'sms_info_list:', sms_info_list conn_db.insert_batch(sms_info_list)
def nn_monitor(): hdfs1_sh = "hdfs haadmin -getServiceState nn1" hdfs2_sh = "hdfs haadmin -getServiceState nn2" hdfs3_sh = "hdfs haadmin -getServiceState nn3" result1 = os.popen(hdfs1_sh).readline().replace('\n', '') result2 = os.popen(hdfs2_sh).readline().replace('\n', '') result3 = os.popen(hdfs3_sh).readline().replace('\n', '') print type(result1), result1 print type(result2), result2 print type(result3), result3 if result1 == '': result1 = '节点异常' if result2 == '': result2 = '节点异常' if result3 == '': result3 = '节点异常' now_time = str(date_time.datetime.now())[0:19] print '当前时间:', now_time user_list = ['15264899856', '15936466867', '13665656104'] sms_info_list = [] # if result1 == '' or result2 == '': if result1 == '' or result2 == '': sms_info = "namenode服务异常,请及时处理!节点状态: nn1:%s;nn2:%s; 当前时间:%s" % ( result1, result2, now_time) for i in user_list: insert_sql = "insert into tb_sys_sms_send_cur (serv_number,send_date,text,opt_user,area_code,sms_type,plan_id,temp_id) " \ "values ('%s','%s','%s','%s','%s','%s','%s','%s') " % \ (i, '2020-05-21 16:00:00', sms_info, 'mazhong', '999', '主机告警', 'hourt_alerm', '4649') sms_info_list.append(insert_sql) print 'sms_info_list:', sms_info_list conn_db.insert_batch(sms_info_list)