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"