def main():
tables = excel_table_byindex()
db = Custom_MySQL(using='center_app')
for row in tables:
print row
game = row['name'].split('_')[0]
sql ='select id from main_category where prefix= %s'
p =(game,)
result= db.get(sql,*p)
if result:
print result['id']
param={}
param['main_category_id']= result['id']
if row['inner'] !='' and row['pub']!="":
sql='select count(*) as count from assets where inner_ip="%s" or public_ip ="%s"'%(row['inner'],row['pub'])
count = db.count(sql)['count']
if count == 0:
param['inner_ip'] = row['inner']
param['public_ip'] = row['pub']
param['hostname'] = row['name']
param['wxsn'] = row['name']
db.insert('assets',**param)
else:
param['hostname'] = row['name']
db.update('assets','inner_ip="%s"'%row['inner'],**param)
elif row['inner'] !='':
sql='select count(*) as count from assets where inner_ip="%s"'%row['inner']
count = db.count(sql)['count']
if count == 0:
param['inner_ip'] = row['inner']
param['hostname'] = row['name']
param['wxsn'] = row['name']
db.insert('assets',**param)
else:
param['hostname'] = row['name']
db.update('assets','inner_ip="%s"'%row['inner'],**param)
elif row['pub']!="":
sql='select count(*) as count from assets where public_ip="%s"'%row['pub']
count = db.count(sql)['count']
if count ==0:
param['public_ip'] = row['pub']
param['hostname'] = row['name']
param['wxsn'] = row['name']
db.insert('assets',**param)
else:
param['hostname'] = row['name']
db.update('assets','public_ip="%s"'%row['pub'],**param)
else:
print 'pub and inner are both empty'
def run(self):
'''
业务入口
'''
try:
#资产就是资产吧 不做为一个应用出现了，运维管理的是资产服务器，也就是应用的公网
#self.assets2appinfo()
api_data = self.get_api_data()
if api_data == None:return
db = Custom_MySQL(using='center_app')
game = db.get('select id from main_category where prefix="%s"' % self.game_code)
main_category_id = game['id']
#获取区组信息
for dist in api_data['dists']:
print '========'+dist['name']+'+'+dist['code']
sql ='select id from sub_category where main_category_id ='+str(main_category_id)+ ' and name="'+dist['name']+'"'
sub_category_id = db.get(sql)['id']
#将各区组不共用信息入数据库
for app in dist['ips']:
self.insert_db(main_category_id,sub_category_id,self.platform,app)
#处理共用信息
#for app in api_data['global']:
#更新资产id
db.execute('update app_info as a left join assets as b on a.public_ip = b.public_ip set a.assets_id = b.id where a.public_ip is not NULL')
db.execute('update app_info as a left join assets as b on a.inner_ip = b.inner_ip set a.assets_id = b.id where a.inner_ip is not NULL')
except Exception as e:
print e
def run(self):
'''
业务入口
'''
api_data = self.get_api_data()
if api_data == None:return
db = Custom_MySQL(using='center_app')
#更新大类中的应用类型
app_type ={}
app_type ={'app_type':','.join(api_data['type'])}
db.update('main_category',' prefix="%s" ' % self.game_code,**app_type)
game = db.get('select id from main_category where prefix="%s"' % self.game_code)
main_category_id = game['id']
#获取区组信息
for dist in api_data['dists']:
print '========'+dist['name']+'+'+dist['code']
sql ='select count(id) as count from sub_category where main_category_id ='+str(main_category_id)+ ' and name="'+dist['name']+'"'
count = db.count(sql)
if count==None:
print 'SQL Error:%s'% sql
return False
#区组更新内容
dist_data ={}
dist_data ={'prefix':dist['code'],
'main_category_id':main_category_id,
'name':dist['name'],
'platform':self.platform}
#如果没有区组信息则保存
if count['count'] == 0:
db.insert('sub_category',**dist_data)
else:
db.update('sub_category',' main_category_id ='+str(main_category_id)+ ' and name="'+dist['name']+'"',**dist_data)
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)
if result is not None:
exec_num = int(result['exec_num'])
in_queue = int(result['in_queue'])
retry_num = int(result['retry_num'])
if retry_num < 5:
if in_queue == 0:
if exec_num < 3:
if exec_num == 0:
retry_num += 1
exec_num += 1
in_queue = 1
'' % (game, platform, do_rate, log_date, log_time, game, platform, do_rate, 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)
if result is not None:
etl_exec_num = int(result['etl_exec_num'])
in_etl_queue = int(result['in_etl_queue'])
etl_retry_num = int(result['etl_retry_num'])
if etl_retry_num < 5:
if in_etl_queue == 0:
if etl_exec_num < 3:
if etl_exec_num == 0:
etl_retry_num += 1
etl_exec_num += 1
in_etl_queue = 1
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)
else:
sql = ""
#result = mysql.query(sql)
result = mysql.query_by_sql(sql)
# 如果为空，则全部完成
if result['output'] is None or result['output'] == '':
# 查找该条任务
formal_sql = "select d.*,s.db_name,s.table_name from dw2dm_log d, structure s " \
"where d.id = %s and d.target_id = s.id" % pid
formal_result = ""
if grouped == 1 and priority == 1:
formal_result = mysql.get(formal_sql)
elif grouped == 1 and priority != 1:
# 查找组别为1且优先级小于该条任务优先级的其他dw2dm任务是否完成
gpsql = "select d.* from dw2dm_log d,(select priority from dw2dm_log where id = %s) a " \
"where d.log_date = '%s' and d.game = '%s' and d.platform = '%s' and d.grouped = 1 " \
"and d.priority<a.priority and d.status != 3" % (pid, log_date, game, platform)
gpresult = mysql.query(gpsql)
if not gpresult:
formal_result = mysql.get(formal_sql)
elif grouped != 1 and priority == 1:
# 查找组别小于该条任务组别的其他dw2dm任务是否完成
gpsql = "select d.* from dw2dm_log d,(select grouped from dw2dm_log where id = %s) a " \
"where d.log_date = '%s' and d.game = '%s' and d.platform = '%s' and d.grouped<a.grouped " \
"and d.status != 3" % (pid, log_date, game, platform)
gpresult = mysql.query(gpsql)
if not gpresult:
'' % (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)
if result is not None:
etl_exec_num = int(result['etl_exec_num'])
in_etl_queue = int(result['in_etl_queue'])
etl_retry_num = int(result['etl_retry_num'])
if etl_retry_num < 5:
if in_etl_queue == 0:
if etl_exec_num < 3:
if etl_exec_num == 0:
etl_retry_num += 1
etl_exec_num += 1
in_etl_queue = 1
result = mysql.query_by_sql(sql)
elif is_rely == "offrely":
result = {'output': ''}
else:
result = ""
# 如果为空，则全部完成
if result['output'] is None or result['output'] == '':
# 查找该条任务
formal_sql = "select d.*,s.db_name,s.table_name from dm2report_new_log d, structure s " \
"where d.id = %s and d.target_id = s.id" % pid
formal_result = ""
if grouped == 1 and priority == 1:
formal_result = mysql.get(formal_sql)
elif grouped == 1 and priority != 1:
# 查找组别为1且优先级小于该条任务优先级的其他dm2report任务是否完成
gpsql = "select d.* from dm2report_new_log d,(select priority from dm2report_new_log where id = %s) a " \
"where d.log_date = '%s' and d.game = '%s' and d.platform = '%s' and d.grouped = 1 " \
"and d.priority<a.priority and d.status != 3" % (pid, log_date, game, platform)
gpresult = mysql.query(gpsql)
if not gpresult:
formal_result = mysql.get(formal_sql)
elif grouped != 1 and priority == 1:
# 查找组别小于该条任务组别的其他dm2report任务是否完成
gpsql = "select d.* from dm2report_new_log d,(select grouped from dm2report_new_log where id = %s) a " \
"where d.log_date = '%s' and d.game = '%s' and d.platform = '%s' and d.grouped<a.grouped " \
"and d.status != 3" % (pid, log_date, game, platform)
gpresult = mysql.query(gpsql)
if not gpresult:
def insert_db(self,main_category_id,sub_category_id,platform,app):
temp_app ={}
temp_app['name']=app['type']+'['+app['memo']+']'
temp_app['platform'] = platform
temp_app['type']=app['type']
temp_app['port']=app['port']
temp_app['main_category_id'] = main_category_id
temp_app['sub_category_id'] = sub_category_id
if app.get('db_type',False):
temp_app['db_type'] = app['db_type']
sql ='select count(*) as count from app_info where '
#同一游戏同一区组
where ='type="%s" and port="%s" and main_category_id="%s" \
and sub_category_id="%s"'% (app['type'],app['port'],main_category_id,sub_category_id)
db = Custom_MySQL(using='center_app')
#处理内网
if app['ip'].split('.')[0] in ['10','172']:
inner_ip ='and inner_ip="%s"'%(app['ip'])
count = db.count(sql+where+inner_ip)
if count==None:
print 'SQL Error:%s'% sql+where+inner_ip
return False
count = count['count']
try:
temp_app['public_ip'] = db.get('select public_ip from assets where inner_ip="%s"'% app['ip'])['public_ip']
except:
pass
if count==0:
temp_app['inner_ip'] = app['ip']
db.insert('app_info',**temp_app)
else:
db.update('app_info',where+inner_ip,**temp_app)
else:
import re
if app['type']=='web':
app['ip'] = app['ip'].replace('http://','').split('/')[0]
p=r'(?<![\.\d])(?:\d{1,3}\.){3}\d{1,3}(?![\.\d])'
mo = re.search(p ,app['ip'])
if not mo:
domain = app['ip'].replace('http://','').split('/')[0]
app['ip'] = db.get('select ip from domain where domain="%s"'% domain)['ip']
temp_app['domain'] = domain
public_ip ='and (public_ip="%s") '%(app['ip'])
count = db.count(sql+where+public_ip)
if count==None:
print 'SQL Error:%s'% sql+where+public_ip
return False
count = count['count']
if count==0:
temp_app['public_ip'] = app['ip']
db.insert('app_info',**temp_app)
else:
db.update('app_info',where+public_ip,**temp_app)
'and in_queue =0 and exec_num < 4 and log_time <="%s"' \
'' % (game, platform, do_rate, log_date, log_time)
tasks = mysql.query(sql)
for task in tasks:
#print(task)
try:
mysql.begin()
where = {'id': int(task['id'])}
'''
照理说for里面不会有人抢快照数据，以防万一起动排他锁(使用主键启动行锁)，兄弟们最好别瞎用
'''
#result = mysql.get('select f.*,s.db_name,s.table_name from file2dw_log as f left join structure as s '
result = mysql.get('select f.*,s.db_name,s.table_name,s.flag from file2dw_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'])
in_queue = int(result['in_queue'])
if in_queue == 0:
if exec_num < 3:
exec_num += 1
in_queue = 1
datas = {'exec_num': exec_num, 'in_queue': in_queue}
update_result = mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
# 为防止修改数据库时出现异常，调整为：确认修改字段状态成功后，再加入队列
if update_result == 1:
run_task.apply_async((result,), queue='mergefile2dw')
else:
print "update data{'exec_num', 'in_queue'} error"
'and in_download_queue=0 and download_exec_num<4 and download_retry_num<6 and log_time<="%s")' \
'' % (game, platform, do_rate, log_date, log_time, game, platform, do_rate, 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 = 6 and id = %(id)s for update' % where)
result = mysql.get(
'select * from etl_data_log where etl_status = 6 and id = %(id)s '
% where)
if result is not None:
download_exec_num = int(result['download_exec_num'])
in_download_queue = int(result['in_download_queue'])
download_retry_num = int(result['download_retry_num'])
if download_retry_num < 5:
if in_download_queue == 0:
if download_exec_num < 3:
if download_retry_num == 0:
download_retry_num += 1
download_exec_num += 1
in_download_queue = 1
'and in_download_queue=0 and download_exec_num<4 and download_retry_num<6 and log_time<="%s")' \
'' % (game, platform, do_rate, log_date, log_time, game, platform, do_rate, 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 = 6 and id = %(id)s for update' % where)
result = mysql.get('select * from etl_data_log where etl_status = 6 and id = %(id)s ' % where)
if result is not None:
download_exec_num = int(result['download_exec_num'])
in_download_queue = int(result['in_download_queue'])
download_retry_num = int(result['download_retry_num'])
if download_retry_num < 5:
if in_download_queue == 0:
if download_exec_num < 3:
if download_retry_num == 0:
download_retry_num += 1
download_exec_num += 1
in_download_queue = 1
datas = {'download_exec_num': download_exec_num, 'in_download_queue': in_download_queue,
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 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"