def get_monitor(): n = Custom_MySQL(using='center_app') return n.query('select public_ip as ip,22 as port,"playcrab" as user,pwd \ from assets \ where \ public_ip ="115.29.10.48" \ ')
def get_assets(): n = Custom_MySQL(using='center_app') return n.query('select public_ip as ip,22 as port,"playcrab" as user,pwd \ from assets \ where \ public_ip !="" and public_ip !="NULL" \ and public_ip not in("115.29.12.230","115.29.12.219","49.213.111.2","49.213.111.3","49.213.111.4","49.213.111.5","49.213.111.6") \ order by id')
def get_result(self): ''' 获取子进程执行结果 ''' db = Custom_MySQL(using='log') result = db.query('select ip,result as data,IF(flag=2,1,0) as flag \ from batch_detail \ where batch_id ="%s"'% self.batch_id ) db.close() print '===========', len(result),'================' self.result = result
class AddPartition(): def __init__(self): #self.mysql= {'report_ares': Custom_MySQL(using='report_ares'),'report_hebe': Custom_MySQL(using='report_hebe'),'report_crius': Custom_MySQL(using='report_crius')} self.mysql=Custom_MySQL(using='hadoops2') def execPartitons(self,games,tables,start_day,end_day): ##conv db for game in games: db="report_"+game for table in tables: self.mysql.begin(); do_date=start_day i=0; exec_partions_sql="ALTER TABLE "+db+"."+table+" ADD PARTITION (" patition_sql=""; while do_date <= end_day: i = i + 1 partition_name="p"+str(do_date).replace('-',''); is_exist=self.find_partition(db,table,partition_name) if not is_exist: patition_sql=patition_sql+"PARTITION %s VALUES LESS THAN (to_days('%s')),"%(partition_name,do_date) #print patition_sql do_date = start_day + datetime.timedelta(days = i) if len(patition_sql)>0: replace_reg = re.compile(r',$') print "add partition db:%s table:%s ,start_day:%s,end_day:%s"%(db,table,start_day,end_day) sql=exec_partions_sql+replace_reg.sub('', patition_sql)+");" print sql self.mysql.execute(sql) self.mysql.commit(); def add_months(self,sourcedate,months): month = sourcedate.month - 1 + months year = int(sourcedate.year + month / 12 ) month = month % 12 + 1 day = min(sourcedate.day,calendar.monthrange(year,month)[1]) return datetime.date(year,month,day) def find_partition(self,db,table_name,partition_name): # exis_row=self.mysql.query("select partition_name,partition_expression, partition_description,table_rows from information_schema.partitions"\ # " where table_schema = schema() and table_schema='%s' and table_name='%s' and partition_name='%s';"%(db,table_name,partition_name)) exis_row=self.mysql.query("select partition_name,partition_expression, partition_description,table_rows from information_schema.partitions"\ " where table_schema='%s' and table_name='%s' and partition_name='%s';"%(db,table_name,partition_name)) if len(exis_row)>0: print "exis partitons db:%s,table:%s,p_name:%s"%(db,table_name,partition_name) return True return False
def query_mysql(sql): mysql = Custom_MySQL(using='etl_manage') try: mysql.begin() result = mysql.query(sql) mysql.commit() mysql.close() return result except Exception as exc: #回滚 mysql.rollback() print(exc) mysql.close()
def query_mysql(sql): mysql = Custom_MySQL(using='etl_manage') try: mysql.begin() result = mysql.query(sql) mysql.commit() mysql.close() return result except Exception as exc: #回滚 mysql.rollback() print(exc) mysql.close()
class CenterApp(): def __init__(self): self.center_mysql = Custom_MySQL(using='center_app') # def get_server_list(self): # ''' # 获取执行任务 # ''' # try: # ip_list = [] # if param['flag'] == 'basic': # ip_list.append('120.26.1.250') # # if param['flag'] == 'most': # ip_list.append('120.26.13.150') # # if param['flag'] == 'log': # s_sql = "select t1.* from \ # (select a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ # on a.main_category_id = m.id \ # where a.is_del = 0 and (a.hostname like '%%%%web_balance%%%%' or a.hostname like '%%%%web_admin%%%%') ) t1 \ # left join platform t2 on t1.platform_id = t2.id \ # where t1.source_ip is not null group by source_ip" # # if param['game'] == 'kof': # s_sql = "select t1.* from \ # (select a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ # on a.main_category_id = m.id \ # where a.is_del = 0 and a.hostname like '%%%%gameserver%%%%' ) t1 \ # left join platform t2 on t1.platform_id = t2.id \ # where t1.source_ip is not null group by source_ip" # # ip_list = self.exec_sql(s_sql) # # if param['flag'] == 'snap': # s_sql = "select s.prefix as source_ip from sub_category s inner join main_category m \ # on s.main_category_id = m.id \ # where s.platform = '%s' and m.prefix = '%s'" % (param['platform'],param['game']) # ip_list = self.exec_sql(s_sql) # # return ip_list # except Exception as exc: # print exc # #异常回滚 # self.center_mysql.rollback() def get_log_ip(self): ''' 获取所有log的IP ''' ip_list = {} sql = "select t1.*,t2.prefix as platform from \ (select m.prefix as gamename,a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ on a.main_category_id = m.id \ where a.is_del = 0 and (a.hostname like '%%%%web_balance%%%%' or a.hostname like '%%%%web_admin%%%%') ) t1 \ left join platform t2 on t1.platform_id = t2.id \ where t1.source_ip is not null group by source_ip\ union all \ select t1.*,t2.prefix as platform from \ (select m.prefix as gamename,a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ on a.main_category_id = m.id \ where a.is_del = 0 and a.hostname like '%%%%gameserver%%%%' ) t1 \ left join platform t2 on t1.platform_id = t2.id \ where t1.source_ip is not null group by source_ip" result = self.center_mysql.query(sql) for info in result: if info['gamename'] is None or info['platform'] is None: continue if info['gamename'].encode('utf8') not in ip_list: ip_list[info['gamename'].encode('utf8')] = {} if info['platform'].encode('utf8') not in ip_list[info['gamename'].encode('utf8')]: ip_list[info['gamename'].encode('utf8')][info['platform'].encode('utf8')] = [] ip_list[info['gamename'].encode('utf8')][info['platform'].encode('utf8')].append(info['source_ip'].encode('utf8')) return ip_list def get_snap_ip(self): ''' 获取所有快照的IP ''' ip_list = {} s_sql = "select f.prefix as platform,s.prefix as source_ip,m.prefix as gamename from sub_category s inner join main_category m \ on s.main_category_id = m.id \ left join platform f on f.id = s.platform_id" result = self.center_mysql.query(s_sql) for info in result: if info['gamename'] is None or info['platform'] is None: continue if info['gamename'].encode('utf8') not in ip_list: ip_list[info['gamename'].encode('utf8')] = {} if info['platform'].encode('utf8') not in ip_list[info['gamename'].encode('utf8')]: ip_list[info['gamename'].encode('utf8')][info['platform'].encode('utf8')] = [] ip_list[info['gamename'].encode('utf8')][info['platform'].encode('utf8')].append(info['source_ip'].encode('utf8')) # print ip_list return ip_list def get_ip_list(self): ''' 整理IP列表 ''' try: ip_list = {} ip_list['basic'] = ['120.26.1.250'] ip_list['mostsdk'] = ['120.26.13.150'] ip_list['wanpay'] = ['112.124.116.44'] log_list = self.get_log_ip(); ip_list['log'] = log_list snap_list = self.get_snap_ip() ip_list['snap'] = snap_list return ip_list except Exception,e: print e print "异常" #异常回滚 self.center_mysql.rollback()
class CopyConfig(): def __init__(self): self.mysql = Custom_MySQL(using='etl_manage') self.source_game = 'ares' def get_all_task(self, task_name): condition = 'game = "%s" ' % self.source_game if task_name is not None: condition += 'and task_name="%s"' % task_name ##appstoremix is_delete = 0 and task_list = self.mysql.query( "select * from dw2dm where platform='all' and %s" % (condition)) return task_list def get_structure(self, id, game, plat_form): ''' 获取当前游戏的,参数structure.如不存在则会添加 :param id: :param game: :param plat_form: :return: ''' structure = self.mysql.get( "select * from structure where is_delete=0 and id=%s", id) if structure != None: t_structure = [ structure['type'], structure['flag'], structure['db_type'], game, plat_form, #structure['platform'], #'db_name':structure['db_name'], structure['table_name'], structure['column_name'], ##structure['partition_name'], ##structure['partition_rule'], ##structure['index_name'], structure['create_table_sql'], structure['user_id'], 0, datetime.datetime.today().strftime("%Y-%m-%d") ] game_db = None if structure['type'] != None and str( structure['type']).__eq__('dw'): game_db = '%s_dw' % game t_structure.append(game_db) elif structure['type'] != None and str( structure['type']).__eq__('dm'): game_db = '%s_dm' % game t_structure.append(game_db) elif structure['type'] != None and str( structure['type']).__eq__('report'): game_db = 'report_%s' % game t_structure.append(game_db) exis_row = self.mysql.query( "select id from structure where platform='%s' and is_delete=0 and db_name='%s' and platform='all' and table_name='%s' and db_type='%s'" % (plat_form, game_db, str( structure['table_name']), str(structure['db_type']))) if len(exis_row) > 0: return int(exis_row[0]['id']) else: return self.save_newstructure(t_structure) def save_new_task(self, task): self.mysql.insert("dw2dm", **task) self.mysql.commit() def save_newstructure(self, structure): query = 'INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' rowNum = self.mysql.execute(query, *tuple(structure)) self.mysql.commit() return rowNum def run(self, game, task_name=None, plat_form="all"): print "start copy" task_list = self.get_all_task(task_name) for task in task_list: form_ids = "" for form_id_str in task['from_id'].split(","): if len(str(form_ids)) > 0: form_ids = form_ids + "," + str( self.get_structure(int(form_id_str), game, plat_form)) else: form_ids = str( self.get_structure(int(form_id_str), game, plat_form)) target_id = self.get_structure(int(task['target_id']), game, plat_form) t_task = { 'game': game, ##'platform':task['platform'], 'platform': plat_form, 'log_name': task['log_name'], 'do_rate': task['do_rate'], 'priority': task['priority'], 'prefix_sql': task['prefix_sql'], 'exec_sql': task['exec_sql'].replace("%s_dw" % self.source_game, "%s_dw" % game).replace( "%s_dm" % self.source_game, "%s_dm" % game), 'post_sql': task['post_sql'], 'from_id': form_ids, 'target_id': target_id, 'create_date': datetime.datetime.today().strftime("%Y-%m-%d"), 'comment': task['comment'], 'grouped': task['grouped'], 'is_delete': task['is_delete'], 'user_id': task['user_id'] } self.save_new_task(t_task) self.mysql.close() print "over" def add_structure(self, game, plat_form): platforms_str = plat_form.split(",") structures = self.mysql.query( "select * from structure where platform='all' and is_delete=0 and flag='log' and game='ares' and type in ('report','dm')" ) for structure in structures: for platform in platforms_str: t_structure = [ structure['type'], structure['flag'], structure['db_type'], game, platform, #structure['platform'], #'db_name':structure['db_name'], structure['table_name'], structure['column_name'], ##structure['partition_name'], ##structure['partition_rule'], ##structure['index_name'], structure['create_table_sql'], structure['user_id'], 0, datetime.datetime.today().strftime("%Y-%m-%d") ] game_db = None if structure['type'] != None and str( structure['type']).__eq__('dw'): game_db = '%s_dw' % game elif structure['type'] != None and str( structure['type']).__eq__('dm'): game_db = '%s_dm' % game elif structure['type'] != None and str( structure['type']).__eq__('report'): game_db = 'report_%s' % game t_structure.append(game_db) self.save_newstructure(t_structure)
class CopyConfig(): def __init__(self): self.mysql = Custom_MySQL(using='etl_manage') self.source_game = 'ares' def get_all_task(self,task_name): condition = 'game = "%s" ' % self.source_game if task_name is not None: condition += 'and task_name="%s"' % task_name ##appstoremix is_delete = 0 and task_list = self.mysql.query("select * from dw2dm where platform='all' and %s" % (condition)) return task_list def get_structure(self,id,game,plat_form): ''' 获取当前游戏的,参数structure.如不存在则会添加 :param id: :param game: :param plat_form: :return: ''' structure=self.mysql.get("select * from structure where is_delete=0 and id=%s",id) if structure!=None: t_structure=[ structure['type'], structure['flag'], structure['db_type'], game, plat_form, #structure['platform'], #'db_name':structure['db_name'], structure['table_name'], structure['column_name'], ##structure['partition_name'], ##structure['partition_rule'], ##structure['index_name'], structure['create_table_sql'], structure['user_id'], 0, datetime.datetime.today().strftime("%Y-%m-%d") ] game_db=None if structure['type']!=None and str(structure['type']).__eq__('dw'): game_db='%s_dw' % game t_structure.append(game_db) elif structure['type']!=None and str(structure['type']).__eq__('dm'): game_db='%s_dm' % game t_structure.append(game_db) elif structure['type']!=None and str(structure['type']).__eq__('report'): game_db='report_%s' % game t_structure.append(game_db) exis_row=self.mysql.query("select id from structure where platform='%s' and is_delete=0 and db_name='%s' and platform='all' and table_name='%s' and db_type='%s'"%(plat_form,game_db,str(structure['table_name']),str(structure['db_type']))) if len(exis_row)>0: return int(exis_row[0]['id']) else: return self.save_newstructure(t_structure) def save_new_task(self,task): self.mysql.insert("dw2dm",**task) self.mysql.commit() def save_newstructure(self,structure): query='INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' rowNum=self.mysql.execute(query,*tuple(structure)) self.mysql.commit() return rowNum def run(self,game,task_name=None,plat_form="all"): print "start copy" task_list = self.get_all_task(task_name) for task in task_list: form_ids="" for form_id_str in task['from_id'].split(","): if len(str(form_ids))>0: form_ids=form_ids+","+str(self.get_structure(int(form_id_str),game,plat_form)) else: form_ids=str(self.get_structure(int(form_id_str),game,plat_form)) target_id=self.get_structure(int(task['target_id']),game,plat_form) t_task = { 'game':game, ##'platform':task['platform'], 'platform':plat_form, 'log_name':task['log_name'], 'do_rate':task['do_rate'], 'priority':task['priority'], 'prefix_sql':task['prefix_sql'], 'exec_sql':task['exec_sql'].replace("%s_dw" % self.source_game,"%s_dw" % game).replace("%s_dm" % self.source_game,"%s_dm" % game), 'post_sql':task['post_sql'], 'from_id':form_ids, 'target_id':target_id, 'create_date':datetime.datetime.today().strftime("%Y-%m-%d"), 'comment':task['comment'], 'grouped':task['grouped'], 'is_delete':task['is_delete'], 'user_id':task['user_id'] } self.save_new_task(t_task) self.mysql.close() print "over" def add_structure(self,game,plat_form): platforms_str=plat_form.split(",") structures=self.mysql.query("select * from structure where platform='all' and is_delete=0 and flag='log' and game='ares' and type in ('report','dm')") for structure in structures: for platform in platforms_str: t_structure=[ structure['type'], structure['flag'], structure['db_type'], game, platform, #structure['platform'], #'db_name':structure['db_name'], structure['table_name'], structure['column_name'], ##structure['partition_name'], ##structure['partition_rule'], ##structure['index_name'], structure['create_table_sql'], structure['user_id'], 0, datetime.datetime.today().strftime("%Y-%m-%d") ] game_db=None if structure['type']!=None and str(structure['type']).__eq__('dw'): game_db='%s_dw' % game elif structure['type']!=None and str(structure['type']).__eq__('dm'): game_db='%s_dm' % game elif structure['type']!=None and str(structure['type']).__eq__('report'): game_db='report_%s' % game t_structure.append(game_db) self.save_newstructure(t_structure)
from dm2report.tasks import run_task from custom.db.mysql import Custom_MySQL mysql = Custom_MySQL(using='etl_manage') game = sys.argv[1] platform = sys.argv[2] do_rate = sys.argv[3] now_time = sys.argv[4] now_time = datetime.datetime.strptime(now_time, '%Y-%m-%d %H:%M:%S') #数据日期,格式如:20151015 log_date = now_time.strftime('%Y%m%d') tasks = mysql.query( 'select * from dm2report_log where do_rate="%s" and task_date="%s" and game="%s" ' 'and platform="%s" and exec_num<4 and in_queue=0 and status=0' '' % (do_rate, log_date, game, platform)) for task in tasks: try: mysql.begin() where = {'id': int(task['id'])} #result = mysql.get('select f.*,s.db_name,s.table_name from dm2report_log as f left join structure as s ' result = mysql.get( 'select f.*,s.db_name,s.table_name from dm2report_log as f left join structure as s ' 'on f.target_id=s.id where f.id = %(id)s ' % where) if result is not None: exec_num = int(result['exec_num'])
is_rely = sys.argv[5] except Exception as exc: is_rely = "onrely" #数据日期,格式如:20151015 task_date = now_time.strftime('%Y%m%d') #调用处理时差工具类,获取对应游戏有时差的区服信息 game_platform_jetlag = cmd.run('/usr/bin/python /data/etl_manage/handle_jetlag.py -g'+game+'')['output'] # 查找当天未开始的任务 sql = "select id,from_id,log_date,grouped,priority from dw2dm_log " \ "where task_date='%s' and game='%s' and platform='%s' " \ "and do_rate='%s' and status=0 and in_queue=0 and exec_num < 3" \ "" % (task_date, game, platform, do_rate) tasks = mysql.query(sql) for task in tasks: try: mysql.begin() pid = task['id'] from_id = task['from_id'] log_date = task['log_date'] grouped = task['grouped'] priority = task['priority'] sql = "" if is_rely == "onrely": if platform == 'all': # 查找该条任务所依赖的上层mergefile2dw 或是 本层dw2dm 任务是否完成 sql = "(select distinct a.* from file2dw_log f,(select s.game gamee,s.platform platformm," \
'where game="%s" and platform="%s" and do_rate="%s" and log_name in (%s) and etl_status=0 and task_date="%s" ' \ 'and in_etl_queue =0 and etl_exec_num < 4 and etl_retry_num < 6 and log_time <="%s"' \ '' % (game, platform, do_rate, tables, log_date, log_time) if log_time <= '0100': log_time_old = '2400' log_date_old = (now_time-datetime.timedelta(days=1)).strftime('%Y%m%d') sql = '(select * from etl_data_log ' \ 'where game="%s" and platform="%s" and do_rate="%s" and log_name in (%s) and etl_status=0 and task_date="%s" ' \ 'and in_etl_queue =0 and etl_exec_num < 4 and etl_retry_num < 6 and log_time <="%s") ' \ 'union (select * from etl_data_log ' \ 'where game="%s" and platform="%s" and do_rate="%s" and log_name in (%s) and etl_status=0 and task_date="%s" ' \ 'and in_etl_queue =0 and etl_exec_num < 4 and etl_retry_num < 6 and log_time <="%s")' \ '' % (game, platform, do_rate, tables, log_date, log_time, game, platform, do_rate, tables, log_date_old, log_time_old) tasks = mysql.query(sql) for task in tasks: #print(task) try: mysql.begin() where = {'id': int(task['id'])} ''' 照理说for里面不会有人抢快照数据,以防万一起动排他锁(使用主键启动行锁),兄弟们最好别瞎用 ''' #result = mysql.get('select * from etl_data_log where etl_status = 0 and id = %(id)s for update' % where) #result = mysql.get('select * from etl_data_log where etl_status = 0 and id = %(id)s ' % where) result = mysql.get('select f.*,s.flag from etl_data_log as f left join structure as s ' 'on f.target_id=s.id where f.id = %(id)s ' % where)
is_rely = "" try: is_rely = sys.argv[5] except Exception as exc: is_rely = "onrely" #数据日期,格式如:20151015 task_date = now_time.strftime('%Y%m%d') # 查找当天未开始的任务 sql = "select id,from_id,log_date,grouped,priority from dm2report_new_log d " \ "where d.task_date='%s' and game='%s' and platform='%s' and do_rate='%s' " \ "and d.status=0 and d.in_queue=0 and d.exec_num < 3" \ "" % (task_date, game, platform, do_rate) tasks = mysql.query(sql) for task in tasks: try: mysql.begin() pid = task['id'] from_id = task['from_id'] log_date = task['log_date'] grouped = task['grouped'] priority = task['priority'] sql = "" result = "" if is_rely == "onrely": # 查找该条任务所依赖的上层dw2dm 或是 本层dm2report 任务是否完成 # dm层不再处理时差,所以此处仅查询platform='all'的 #and f.platform = a.platformm
class CopyConfig(): def __init__(self): self.mysql = Custom_MySQL(using='etl_manage') self.source_game = 'ares' def get_all_task(self,task_name): condition = 'game = "%s" ' % self.source_game if task_name is not None: condition += 'and task_name="%s"' % task_name task_list = self.mysql.query("select * from dm2report where is_delete = 0 and %s" % condition) return task_list def get_structure(self,id,game): structure=self.mysql.get("select * from structure where is_delete=0 and id=%s",id) if structure!=None: t_structure=[ structure['type'], structure['flag'], structure['db_type'], game, structure['platform'], #'db_name':structure['db_name'], structure['table_name'], structure['column_name'], ##structure['partition_name'], ##structure['partition_rule'], ##structure['index_name'], structure['create_table_sql'], structure['user_id'], 0, datetime.datetime.today().strftime("%Y-%m-%d") ] game_db=None if structure['db_type']!=None and str(structure['db_type']).__eq__('hive'): game_db='%s_dw' % game t_structure.append(game_db) elif structure['db_type']!=None and str(structure['db_type']).__eq__('mysql'): game_db='report_%s' % game t_structure.append(game_db) exis_row=self.mysql.query("select id from structure where platform='all' and user_id='wxx' and is_delete=0 and db_name='%s' and table_name='%s' and db_type='%s'"%(game_db,str(structure['table_name']),str(structure['db_type']))) if len(exis_row)>0: return int(exis_row[0]['id']) else: return self.save_newstructure(t_structure) def save_new_task(self,task): self.mysql.insert("dm2report",**task) self.mysql.commit() def save_newstructure(self,structure): query='INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' rowNum=self.mysql.execute(query,*tuple(structure)) self.mysql.commit() return rowNum def run(self,game,task_name=None): print "start copy" task_list = self.get_all_task(task_name) for task in task_list: form_id=self.get_structure(int(task['from_id']),game) target_id=self.get_structure(int(task['target_id']),game) t_task = { 'game':game, 'platform':task['platform'], 'task_name':task['task_name'], 'date_cycle':task['date_cycle'], 'do_rate':task['do_rate'], 'group':task['group'], 'priority':task['priority'], 'prefix_sql':task['prefix_sql'], 'exec_sql':task['exec_sql'].replace("%s_dw" % self.source_game,"%s_dw" % game), 'post_sql':task['post_sql'], 'from_id':form_id, 'target_id':target_id, 'create_date':datetime.datetime.today().strftime("%Y-%m-%d"), 'comment':task['comment'] } self.save_new_task(t_task) self.mysql.close() print "over"
class CenterApp(): def __init__(self): self.center_mysql = Custom_MySQL(using='center_app') # def get_server_list(self): # ''' # 获取执行任务 # ''' # try: # ip_list = [] # if param['flag'] == 'basic': # ip_list.append('120.26.1.250') # # if param['flag'] == 'most': # ip_list.append('120.26.13.150') # # if param['flag'] == 'log': # s_sql = "select t1.* from \ # (select a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ # on a.main_category_id = m.id \ # where a.is_del = 0 and (a.hostname like '%%%%web_balance%%%%' or a.hostname like '%%%%web_admin%%%%') ) t1 \ # left join platform t2 on t1.platform_id = t2.id \ # where t1.source_ip is not null group by source_ip" # # if param['game'] == 'kof': # s_sql = "select t1.* from \ # (select a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ # on a.main_category_id = m.id \ # where a.is_del = 0 and a.hostname like '%%%%gameserver%%%%' ) t1 \ # left join platform t2 on t1.platform_id = t2.id \ # where t1.source_ip is not null group by source_ip" # # ip_list = self.exec_sql(s_sql) # # if param['flag'] == 'snap': # s_sql = "select s.prefix as source_ip from sub_category s inner join main_category m \ # on s.main_category_id = m.id \ # where s.platform = '%s' and m.prefix = '%s'" % (param['platform'],param['game']) # ip_list = self.exec_sql(s_sql) # # return ip_list # except Exception as exc: # print exc # #异常回滚 # self.center_mysql.rollback() def get_log_ip(self): ''' 获取所有log的IP ''' ip_list = {} sql = "select t1.*,t2.prefix as platform from \ (select m.prefix as gamename,a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ on a.main_category_id = m.id \ where a.is_del = 0 and (a.hostname like '%%%%web_balance%%%%' or a.hostname like '%%%%web_admin%%%%') ) t1 \ left join platform t2 on t1.platform_id = t2.id \ where t1.source_ip is not null group by source_ip\ union all \ select t1.*,t2.prefix as platform from \ (select m.prefix as gamename,a.public_ip as source_ip,a.platform_id from assets a inner join main_category m \ on a.main_category_id = m.id \ where a.is_del = 0 and a.hostname like '%%%%gameserver%%%%' ) t1 \ left join platform t2 on t1.platform_id = t2.id \ where t1.source_ip is not null group by source_ip" result = self.center_mysql.query(sql) for info in result: if info['gamename'] is None or info['platform'] is None: continue if info['gamename'].encode('utf8') not in ip_list: ip_list[info['gamename'].encode('utf8')] = {} if info['platform'].encode('utf8') not in ip_list[ info['gamename'].encode('utf8')]: ip_list[info['gamename'].encode('utf8')][ info['platform'].encode('utf8')] = [] ip_list[info['gamename'].encode('utf8')][info['platform'].encode( 'utf8')].append(info['source_ip'].encode('utf8')) return ip_list def get_snap_ip(self): ''' 获取所有快照的IP ''' ip_list = {} s_sql = "select f.prefix as platform,s.prefix as source_ip,m.prefix as gamename from sub_category s inner join main_category m \ on s.main_category_id = m.id \ left join platform f on f.id = s.platform_id" result = self.center_mysql.query(s_sql) for info in result: if info['gamename'] is None or info['platform'] is None: continue if info['gamename'].encode('utf8') not in ip_list: ip_list[info['gamename'].encode('utf8')] = {} if info['platform'].encode('utf8') not in ip_list[ info['gamename'].encode('utf8')]: ip_list[info['gamename'].encode('utf8')][ info['platform'].encode('utf8')] = [] ip_list[info['gamename'].encode('utf8')][info['platform'].encode( 'utf8')].append(info['source_ip'].encode('utf8')) # print ip_list return ip_list def get_ip_list(self): ''' 整理IP列表 ''' try: ip_list = {} ip_list['basic'] = ['120.26.1.250'] ip_list['mostsdk'] = ['120.26.13.150'] ip_list['wanpay'] = ['112.124.116.44'] log_list = self.get_log_ip() ip_list['log'] = log_list snap_list = self.get_snap_ip() ip_list['snap'] = snap_list return ip_list except Exception, e: print e print "异常" #异常回滚 self.center_mysql.rollback()
def get_idcs(idc_name): db = Custom_MySQL(using='center_app') sql ="select id as idc_id,prefix from idc where name like %s and is_del = 0" p=(idc_name+'%',) return db.query(sql,*p)
def run_task(self, task_param): redis = Custom_Redis(using='etl_manage') mysql = Custom_MySQL(using='hadoops2') mysql_etl = Custom_MySQL(using='etl_manage') mysql.begin() mysql_etl.begin() where = {'id': int(task_param['id'])} datas = {'load_status': 0} try: ''' 业务代码块放下方 ''' dir_param = {'game': task_param['game'], 'platform': task_param['platform'], 'log_date': task_param['log_date'], 'log_name': task_param['log_name']} filename_dict = {'log_name': task_param['log_name'], 'log_time': task_param['log_time'], 'source_ip': task_param['source_ip']} prefix_sql = task_param['prefix_sql'] post_sql = task_param['post_sql'] log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param file_name = "%(log_name)s_%(source_ip)s_%(log_time)s.txt" % filename_dict #从redis中,获取当前数据对应存储到哪块磁盘 if redis.get("disk_xml") is None: disk_tmp = open('/data/etl_manage/conf/disk_game.xml', 'r') redis.set("disk_xml", str(disk_tmp.read())) disk_list = str(redis.get("disk_xml")) root = ET.fromstring(disk_list) disk = "" for gameinfo in root.findall('game'): if gameinfo.get('name') == task_param['game']: disk = gameinfo.get('disk') continue #local_log_dir = '/disk1/tmp_data'+log_dir local_log_dir = '/'+disk+'/data'+log_dir #判断要录入的文件是否存在,如果存在则执行,否则不执行 if os.path.exists('%s%s' % (local_log_dir, file_name)): ''' 将任务标识为开始执行:1 ''' datas['load_status'] = 1 mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas) mysql_etl.commit() logger.info('start load data') #执行前置sql if prefix_sql is not None and prefix_sql != '': mysql.query(prefix_sql) ''' 执行load数据进mysql ''' load_sql = task_param['load_sql'] load_sql = load_sql.replace('{dir_path}', local_log_dir+file_name) load_sql = load_sql.replace('{table_name}', task_param['table_name']) load_sql = load_sql.replace('{db_name}', task_param['db_name']) result = mysql.load(load_sql) logger.info('load data to mysql: {0}'.format(result['output'])) #判断录入mysql是否成功 if result['status'] == 0: #执行后置sql if post_sql is not None and post_sql != '': post_sql = post_sql.replace('{table_name}', task_param['table_name']) post_sql = post_sql.replace('{db_name}', task_param['db_name']) mysql.query(post_sql) ''' 将任务标识为录入mysql完成:3 ''' datas['load_status'] = 3 else: logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], load_sql)) ''' 录入mysql失败,将任务标示为未执行:0 ''' datas['load_status'] = 0 ''' 将任务标示为:(模拟) 已从任务队列中移除 ''' datas['in_queue'] = 0 update_result = mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas) # 如果更新失败,则再调用一次,如果还是失败,则等待自动修复机制,但这种概率很小了 if update_result != 1: mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas) mysql_etl.commit() mysql.commit() mysql_etl.close() mysql.close() return True except Exception as exc: logger.error('file2mysql error: %s' % exc) mysql_etl.rollback() mysql.rollback() datas = {'in_queue': 0, 'load_status': 0} mysql_etl.update('file2mysql_log', ' id = %(id)d' % where, **datas) mysql_etl.commit() mysql_etl.close() mysql.close() raise self.retry(exc=exc, countdown=60)
sql = "(select distinct a.* from (select * from file2mysql_log where game='%s' and platform='%s' " \ "and load_status=0 and do_rate='%s' and task_date='%s' and in_queue=0 and exec_num<4 and retry_num <6 and log_time<='%s') " \ "as a left outer join (select * from etl_data_log where game='%s' and platform='%s' and etl_status=6 " \ "and download_status=3 and do_rate='%s' and task_date='%s' and log_time<='%s') as b on a.game=b.game " \ "and a.platform=b.platform and a.log_name=b.log_name and a.task_date = b.task_date and a.do_rate=b.do_rate " \ "and a.log_time=b.log_time) union (select distinct a.* from (select * from file2mysql_log where game='%s' " \ "and platform='%s' and load_status=0 and do_rate='%s' and task_date='%s' and in_queue=0 and exec_num<4 and retry_num <6 " \ "and log_time<='%s') as a left outer join (select * from etl_data_log where game='%s' and platform='%s' " \ "and etl_status=6 and download_status=3 and do_rate='%s' and task_date='%s' and log_time<='%s') as b " \ "on a.game=b.game and a.platform=b.platform and a.log_name=b.log_name and a.task_date = b.task_date " \ "and a.do_rate=b.do_rate and a.log_time=b.log_time)" \ "" % (game, platform, do_rate, log_date, log_time, game, platform, do_rate, log_date, log_time, game, platform, do_rate, log_date_old, log_time_old, game, platform, do_rate, log_date_old, log_time_old) tasks = mysql.query(sql) for task in tasks: try: mysql.begin() where = {'id': int(task['id'])} ''' 照理说for里面不会有人抢快照数据,以防万一起动排他锁(使用主键启动行锁), 业务上 1.压缩完毕 2.下载到本地 3.验证md5完毕 4.未录入到hive的dw库中 ''' #result = mysql.get('select f.*,s.db_name,s.table_name from file2mysql_log as f left join structure as s ' result = mysql.get( 'select f.*,s.db_name,s.table_name from file2mysql_log as f left join structure as s ' 'on f.target_id=s.id where f.id = %(id)s ' % where)
class CopyConfig(): def __init__(self): self.mysql = Custom_MySQL(using='etl_manage') self.source_game = 'ares' def get_all_task(self, task_name): condition = 'game = "%s" ' % self.source_game if task_name is not None: condition += 'and task_name="%s"' % task_name task_list = self.mysql.query( "select * from dm2report where is_delete = 0 and %s" % condition) return task_list def get_structure(self, id, game): structure = self.mysql.get( "select * from structure where is_delete=0 and id=%s", id) if structure != None: t_structure = [ structure['type'], structure['flag'], structure['db_type'], game, structure['platform'], #'db_name':structure['db_name'], structure['table_name'], structure['column_name'], ##structure['partition_name'], ##structure['partition_rule'], ##structure['index_name'], structure['create_table_sql'], structure['user_id'], 0, datetime.datetime.today().strftime("%Y-%m-%d") ] game_db = None if structure['db_type'] != None and str( structure['db_type']).__eq__('hive'): game_db = '%s_dw' % game t_structure.append(game_db) elif structure['db_type'] != None and str( structure['db_type']).__eq__('mysql'): game_db = 'report_%s' % game t_structure.append(game_db) exis_row = self.mysql.query( "select id from structure where platform='all' and user_id='wxx' and is_delete=0 and db_name='%s' and table_name='%s' and db_type='%s'" % (game_db, str( structure['table_name']), str(structure['db_type']))) if len(exis_row) > 0: return int(exis_row[0]['id']) else: return self.save_newstructure(t_structure) def save_new_task(self, task): self.mysql.insert("dm2report", **task) self.mysql.commit() def save_newstructure(self, structure): query = 'INSERT INTO structure(type,flag,db_type,game,platform,table_name,column_name,create_table_sql,user_id,is_delete,create_date,db_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' rowNum = self.mysql.execute(query, *tuple(structure)) self.mysql.commit() return rowNum def run(self, game, task_name=None): print "start copy" task_list = self.get_all_task(task_name) for task in task_list: form_id = self.get_structure(int(task['from_id']), game) target_id = self.get_structure(int(task['target_id']), game) t_task = { 'game': game, 'platform': task['platform'], 'task_name': task['task_name'], 'date_cycle': task['date_cycle'], 'do_rate': task['do_rate'], 'group': task['group'], 'priority': task['priority'], 'prefix_sql': task['prefix_sql'], 'exec_sql': task['exec_sql'].replace("%s_dw" % self.source_game, "%s_dw" % game), 'post_sql': task['post_sql'], 'from_id': form_id, 'target_id': target_id, 'create_date': datetime.datetime.today().strftime("%Y-%m-%d"), 'comment': task['comment'] } self.save_new_task(t_task) self.mysql.close() print "over"