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 main():
tables = excel_table_byindex()
db = Custom_MySQL(using='center_app')
for row in tables:
if row['pub'] != "":
param ={}
param['pwd'] = row['pwd']
db.update('assets','public_ip="%s"'%row['pub'],**param)
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
def assets2appinfo(self):
db = Custom_MySQL(using='center_app')
#将assets中有的，但是在app_info没有的，插入到app_info中
sql ='''
INSERT into app_info (assets_id,name,public_ip,inner_ip,main_category_id)
SELECT id,hostname as name,public_ip,inner_ip,main_category_id
from
assets
where public_ip
not in (select public_ip from app_info )
'''
db.execute(sql)
db.commit()
def call_back(self,data=[]):
db = Custom_MySQL(using='log')
sql = "insert into last_ip (id,ip,login_ip) values(null,%s,%s)"
for result in data:
if int(result['flag']) == 1:
ips = json.loads(result['data'])
host = result['ip']
set_param =[]
for ip in ips:
set_param.append(tuple([host,ip]))
db.executemany(sql,set_param)
db.commit()
else:
print result
def call_back(self,data=[]):
db = Custom_MySQL(using='center_app')
for result in data:
if result !=[] and result['flag'] == '1':
param = {}
param['is_manage'] = 1
db.update('assets', 'public_ip="%s"' % result['ip'], **param)
db.commit()
else:
print result
param = {}
param['is_manage'] = 0
db.update('assets', 'public_ip="%s"' % result['ip'], **param)
db.commit()
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)
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 call_back(self,data=[]):
db = Custom_MySQL(using='log')
last_insert_id = db.insert('cpu_batch',**{})
db.commit()
sql = "insert into cpu_detail (id,ip,cpu_used) values(null,%s,%s)"
for result in data:
if int(result['flag']) == 1:
ips = json.loads(result['data'])
host = result['ip']
set_param =[]
for ip in ips:
set_param.append(tuple([host,ip]))
db.executemany(sql,set_param)
db.commit()
else:
print result
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 save_idcs(self):
if self.data == None:
return False
db = Custom_MySQL(using='center_app')
for param in self.data:
sql = 'select count(*) as count from idc where prefix = %s'
p = (param['prefix'],)
count = db.count(sql, *p)
# 检查是否存在
if count['count'] == 0:
db.insert('idc', **param)
else:
db.update('idc', 'prefix="%s"' % param['prefix'], **param)
def save_hosts(self):
if self.data == None:
return False
db = Custom_MySQL(using='center_app')
for param in self.data:
sql ='select count(*) as count from assets where wxsn= %s'
p =(param['wxsn'],)
count = db.count(sql,*p)
if count['count'] == 0:
db.insert('assets',**param)
else:
db.update('assets','wxsn="%s"'%param['wxsn'],**param)
def run(self):
db = Custom_MySQL(using='log')
status = {'flag':1}
db.update('batch_detail',
'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),
**status)
db.commit()
db.close()
try:
#建立连接
self.ssh=paramiko.SSHClient()
#如果没有密码就走public key
if self.host.get('pwd',True) == True:
privatekeyfile = os.path.expanduser('/root/.ssh/id_rsa')
paramiko.RSAKey.from_private_key_file(privatekeyfile)
#缺失host_knows时的处理方法
known_host = "/root/.ssh/known_hosts"
self.ssh.load_system_host_keys(known_host)
self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
#os.system('/opt/local/junos/junos')
#连接远程客户机器
self.ssh.connect(
hostname =self.host['ip'],
port =int(self.host['port']),
username =self.host['user'],
password =self.host['pwd'],
compress =True,
timeout =20
)
#获取远程命令执行结果
stdin, stdout, stderr = self.ssh.exec_command(self.host['cmd'],bufsize=65535, timeout=10)
temp = stdout.readlines()
db = Custom_MySQL(using='log')
status = {'flag':2,'result':''.join(temp)}
db.update('batch_detail',
'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),**status)
db.commit()
db.close()
if temp ==[]:
self.grandchild.put({'flag':'1','ip':self.host['ip'],'data':temp})
else:
self.grandchild.put({'flag':'0','ip':self.host['ip'],'data':temp})
#输出执行结果
self.ssh.close()
except :
#print trace_back()
#以防paramiko本身出问题，这里再用shell运行一次，如果还出问题再确认为问题
cmd ="ssh -p %s -o StrictHostKeyChecking=no %s@%s %s"%(self.host['port'],self.host['user'],self.host['ip'],self.host['cmd'])
(status,output) = commands.getstatusoutput(cmd)
if status == 0:
db = Custom_MySQL(using='log')
status = {'flag':2,'result':output}
db.update('batch_detail',
'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),
**status)
db.commit()
db.close()
self.grandchild.put({'flag':'1','ip':self.host['ip'],'data':output})
else:
db = Custom_MySQL(using='log')
status = {'flag':-1,'result':'faild'}
db.update('batch_detail',
'batch_id="%s" and ip ="%s"' % (self.host['batch_id'],self.host['ip']),
**status)
db.commit()
db.close()
self.grandchild.put({'flag':'0','ip':self.host['ip'],'data':trace_back()})
def run_task(self, task_param):
mysql = Custom_MySQL(using='etl_manage')
mysql.begin()
datas = {'etl_status': 0}
where = {'id': int(task_param['id'])}
try:
log_param = {
'game': task_param['game'],
'platform': task_param['platform'],
'log_date': task_param['log_date'],
'log_name': task_param['log_name'],
'log_dir': task_param['log_dir'],
'col_num': task_param['col_num']
}
log_name_param = {
'log_name': task_param['log_name'],
'source_ip': task_param['source_ip'],
'log_time': task_param['log_time']
}
do_rate = task_param['do_rate']
flag = task_param['flag']
log_dir = '%(log_dir)s/%(game)s/%(platform)s/%(log_date)s/%(log_name)s' % log_param
log_name = '%(log_name)s_%(source_ip)s_%(log_time)s' % log_name_param
log_name_notime = '%(log_name)s_%(source_ip)s' % log_name_param
col_num = task_param['col_num']
project_path = os.getcwd()
#判断如果有md5文件和数据文件同时存在，则开始执行
if (do_rate == "1day" and flag == "log") or (
os.path.exists('%s/%s.log.md5' % (log_dir, log_name)) is True
and os.path.exists('%s/%s.log' % (log_dir, log_name)) is True):
#排除同名文件存在的可能，同时为修复执行提供方便
if os.path.exists('%s/%s.txt' % (log_dir, log_name)):
cmd_remove = 'rm -f %s/%s.txt*' % (log_dir, log_name)
logger.info('remove history file: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(remove_result['status'],
remove_result['output'], cmd_remove))
'''
将任务标识为开始执行：1
'''
datas['etl_status'] = 1
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
校验数据
'''
#如果是“天”频次的日志数据，则特殊处理，直接将同一ip的合成一个文件，同时执行校验
if do_rate == "1day" and flag == "log":
cmd_merge = '/bin/bash %s/etl_data/merge_data.sh %s %s %s' % (
project_path, log_dir, log_name_notime, col_num)
logger.info('check data: {0}'.format(cmd_merge))
merge_result = cmd.run(cmd_merge)
else:
cmd_merge = '/bin/bash %s/etl_data/check_data.sh %s %s %s' % (
project_path, log_dir, log_name, col_num)
logger.info('check data: {0}'.format(cmd_merge))
merge_result = cmd.run(cmd_merge)
if merge_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(merge_result['status'], merge_result['output'],
cmd_merge))
datas['etl_status'] = 0
else:
'''
读取校验格式后的文件总条数
'''
row = open('%s/%s.txt.row' % (log_dir, log_name)).read()
'''
将文件总条数写入数据库，并将任务标识为为校验已完成：2
'''
datas['etl_status'] = 2
datas['row_num'] = int(row)
#datas = {'etl_status': 2, 'row_num': int(row)}
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
压缩数据
'''
cmd_compress = '/bin/bash %s/etl_data/compress_data.sh %s %s' % (
project_path, log_dir, log_name)
logger.info('compress data: {0}'.format(cmd_compress))
compress_result = cmd.run(cmd_compress)
if compress_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(compress_result['status'],
compress_result['output'], cmd_compress))
datas['etl_status'] = 0
else:
'''
将任务标识为压缩完成：4
'''
datas['etl_status'] = 4
datas.pop('row_num')
mysql.update('etl_data_log', ' id = %(id)d' % where,
**datas)
mysql.commit()
'''
生成MD5文件
'''
cmd_md5 = '/bin/bash %s/etl_data/md5_data.sh %s %s' % (
project_path, log_dir, log_name)
logger.info('md5 data: {0}'.format(cmd_md5))
md5_result = cmd.run(cmd_md5)
if md5_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(md5_result['status'],
md5_result['output'], cmd_md5))
datas['etl_status'] = 0
else:
'''
将任务标识为生成MD5完成(即为校验、合并、压缩均已完成)：6
'''
datas['etl_status'] = 6
'''
执行完毕，模拟从队列中清楚任务:0
'''
datas['in_etl_queue'] = 0
update_result = mysql.update('etl_data_log', ' id = %(id)d' % where,
**datas)
# 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
if update_result != 1:
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
logger.error('etl_data error: %s' % exc)
mysql.rollback()
datas = {'in_etl_queue': 0, 'etl_status': 0}
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
raise self.retry(exc=exc, countdown=30)
def run_task(self, task_param):
mysql = Custom_MySQL(using='hadoops2')
mysql_etl = Custom_MySQL(using='etl_manage')
redis = Custom_Redis(using='etl_task')
mysql.begin()
mysql_etl.begin()
datas = {'status': 0}
where = {'id': int(task_param['id'])}
task_key_exc = ""
try:
hive = Custom_Hive(using='ares_dw')
game = task_param['game']
platform = task_param['platform']
table_name = task_param['table_name']
log_date = task_param['log_date']
prefix_sql = task_param['prefix_sql']
exec_sql = task_param['exec_sql']
post_sql = task_param['post_sql']
date_cycle = task_param['date_cycle']
random_str = str(random.randint(0, 999999999))
stimes = str(int(time.time()))
task_date = task_param['task_date']
task_name = task_param['task_name']
do_rate = task_param['do_rate']
#task_key_tmp = str(game)+str(platform)+str(task_name)+str(date_cycle)+str(do_rate)+str(log_date)+str(task_date)
task_key_tmp = str(task_name) + str(date_cycle) + str(do_rate) + str(
log_date) + str(task_date)
task_key_md5 = hashlib.md5()
task_key_md5.update(task_key_tmp)
task_key_md5_result = task_key_md5.hexdigest()
task_key = "dm2report_new_" + str(game) + "_" + str(
platform) + "_" + str(task_key_md5_result)
task_key_exc = task_key
'''
celery 本身的机制存在漏洞，会将一个已经完成任务再次分配给其他的worker，致使同一个任务执行多次
为防止此种现象，在任务开始执行时，将任务的“唯一标示”写入redis中，标注已执行
'''
#如果task_key is None, 则表示该条任务没有执行过，正常执行即可
#如果task_key = 0, 则表示该条任务上次执行失败，允许重复执行
if redis.get(task_key) == "0" or redis.get(task_key) is None:
tmp_file_dir = "/tmp/tmp/%s/%s/%s" % (game, platform, log_date)
#创建本地目录
if not os.path.exists(tmp_file_dir):
os.makedirs(tmp_file_dir)
tmp_file = "%s/%s_%s_%s_%s.txt" % (tmp_file_dir, table_name,
date_cycle, random_str, stimes)
hql_conf = "SET hive.support.concurrency=false;" \
"SET hive.exec.compress.output=true;" \
"SET mapred.output.compression.codec=com.hadoop.compression.lzo.LzopCodec; "
'''
将任务标示为开始执行：1
'''
datas['status'] = 1
datas['start_time'] = str(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where,
**datas)
mysql_etl.commit()
#在redis标注 任务正在执行
redis.set(task_key, 1)
#执行前置sql，将数据临时写入本地，用于mysql load数据
if prefix_sql is not None:
result = mysql.delete_by_sql(prefix_sql)
logger.info('exec prefix_sql: delete old data {0}'.format(
result['output']))
if result['status'] != 0:
logger.error(
'Error Code %s : %s Cmd: %s' %
(result['status'], result['output'], prefix_sql))
'''
执行失败，将其状态标为未执行：0
'''
datas['status'] = 0
#在redis标注 任务未开始执行
redis.set(task_key, 0)
else:
'''
将任务标示为删除临时文件完成：2
'''
datas['status'] = 2
datas.pop('start_time')
mysql_etl.update('dm2report_new_log',
' id = %(id)d' % where, **datas)
mysql_etl.commit()
'''
开始执行hive ql,将数据dump到本地
'''
result = hive.dump(hql_conf + exec_sql, tmp_file)
logger.info('exec exec_sql: dump data {0}'.format(
result['output']))
if result['status'] != 0 or False == os.path.exists(
'%s' % tmp_file):
logger.error(
'Error Code %s : %s Cmd: %s' %
(result['status'], result['output'], exec_sql))
#在redis标注 任务未开始执行
redis.set(task_key, 0)
else:
'''
将任务标示为dump hive数据完成：3
'''
datas['status'] = 3
datas['tmp_file_name'] = tmp_file
mysql_etl.update('dm2report_new_log',
' id = %(id)d' % where, **datas)
mysql_etl.commit()
#执行后置sql
if post_sql is not None:
post_sql = post_sql.replace('{dir_path}', tmp_file)
post_sql = post_sql.replace(
'{table_name}', task_param['table_name'])
post_sql = post_sql.replace(
'{db_name}', task_param['db_name'])
result = mysql.load(post_sql)
logger.info(
'exec post_sql: load data to hdfs {0}'.format(
result['output']))
if result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(result['status'],
result['output'], post_sql))
#在redis标注 任务未开始执行
redis.set(task_key, 0)
else:
'''
将任务标识为录入mysql完成：4
'''
datas['status'] = 4
datas['end_time'] = str(
datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'))
datas.pop('tmp_file_name')
#在redis标注 任务已成功执行
redis.set(task_key, 2)
else:
logger.error('prefix_sql is null')
datas['status'] = 0
#在redis标注 任务未开始执行
redis.set(task_key, 0)
#如果task_key=2, 则标示该条任务已经运行成功
elif redis.get(task_key) == "2":
datas['status'] = 4
#该条任务正在运行中
else:
return True
'''
将任务标示为：(模拟) 已从任务队列中移除
'''
datas['in_queue'] = 0
update_result = mysql_etl.update('dm2report_new_log',
' id = %(id)d' % where, **datas)
# 如果数据库更新失败，再调用一次。 如果还是失败，等待自动修复机制，但这样的概率应该很小了。
if update_result != 1:
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where,
**datas)
mysql_etl.commit()
mysql.commit()
mysql_etl.close()
mysql.close()
return True
except Exception as exc:
logger.error('dm2report error: %s' % exc)
mysql_etl.rollback()
mysql.rollback()
redis.set(task_key_exc, 0)
datas = {'in_queue': 0, 'status': 0}
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql_etl.close()
mysql.close()
raise self.retry(exc=exc, countdown=60)
def run_task(self, task_param):
mysql = Custom_MySQL(using='etl_manage')
mysql.begin()
where = {'id': int(task_param['id'])}
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']}
index_dict = {'db_name': task_param['db_name'], 'table_name': task_param['table_name'], 'platform': task_param['platform'],
'log_date': datetime.datetime.strptime(task_param['log_date'], '%Y%m%d').strftime("%Y-%m-%d")}
partition = {'platform': task_param['platform'], 'log_date': datetime.datetime.strptime(task_param['log_date'], '%Y%m%d').strftime("%Y-%m-%d")}
log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param
lzo_file_name = "%(log_name)s_%(source_ip)s_%(log_time)s.txt.lzo" % filename_dict
index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict
partition_name = "plat_form='%(platform)s',log_date='%(log_date)s'" % partition
project_path = os.getcwd()
local_log_dir = '/disk1/tmp_data'+log_dir
logger.info('local_log_dir: {0}'.format(local_log_dir))
#判断要录入hive 中的文件知否存在，存在则执行
if os.path.exists('%s%s' % (local_log_dir, lzo_file_name)):
'''
将任务标识为开始执行：1
'''
datas = {'load_status': 1}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
#执行load之前，删除同名文件，防止同一个文件出现两次的可能
cmd_remove = '/bin/bash %s/file2dw/remove_damaged_file.sh %s %s' % (project_path, index_dir_name, lzo_file_name)
logger.info('remove damaged files: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (remove_result['status'], remove_result['output'], cmd_remove))
'''
文件加载到hive中
'''
hive = Custom_Hive(using='ares_dw')
load_sql = task_param['load_sql']
load_sql = load_sql.replace('{dir_path}', local_log_dir+lzo_file_name)
load_sql = load_sql.replace('{table_name}', task_param['table_name'])
load_sql = load_sql.replace('{partition_name}', '%s' % partition_name)
load_sql = load_sql.replace('{db_name}', task_param['db_name'])
logger.info('hive load SQL: {0}'.format(load_sql))
result = hive.load(load_sql)
logger.info('hive load result {0}'.format(result['output']))
if result['status'] == 0:
'''
将任务标识为加载文件完成：2
'''
datas = {'load_status': 2}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
'''
建立索引，否则lzo将不支持split
'''
#print index_dir_name
cmd_index = '/bin/bash %s/file2dw/create_lzo_indexer.sh %s %s' % (project_path, index_dir_name, lzo_file_name)
logger.info('create lzo index: {0}'.format(cmd_index))
index_result = cmd.run(cmd_index)
if index_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (index_result['status'], index_result['output'], cmd_index))
else:
if "create index success" in index_result['output']:
'''
将任务标识为建立lzo索引完成：3
'''
datas = {'load_status': 3}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
else:
'''
如果load数据失败，则删除半途出现错误的文件，方式hive查询的时候报错
'''
cmd_remove = '/bin/bash %s/file2dw/remove_damaged_file.sh %s %s' % (project_path, index_dir_name, lzo_file_name)
logger.info('remove damaged files: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (remove_result['status'], remove_result['output'], cmd_remove))
else:
'''
将任务标识为未启动，重新执行：0
'''
datas = {'load_status': 0}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
'''
将任务标示为：(模拟) 已从任务队列中移除
'''
datas = {'in_queue': 0}
update_result = mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
# 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
if update_result != 1:
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
print (exc)
mysql.rollback()
datas = {'in_queue': 0}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
raise self.retry(exc=exc, countdown=60)
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)
#coding=utf-8 """ auth: suguoxin mail: [email protected] create_time: 2016-03-11 15:20:00 used: dm2report_new 任务执行 last_update:2016-04-28 14:48:00 """ import datetime import sys from dm2report_new.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') #用于判断，是否需要校验对于上层任务(dw2dm任务)的依赖，onrely: 需要，offrely: 不需要 is_rely = "" try: is_rely = sys.argv[5] except Exception as exc: is_rely = "onrely" #数据日期，格式如：20151015
def run_task(self, task_param):
mysql = Custom_MySQL(using='etl_manage')
mysql.begin()
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']}
'''
游戏＼平台＼日期＼业务日志名＼日志或者md5文件
'''
log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param
lzo_file_name = "%(log_name)s_%(log_time)s.txt"% filename_dict
local_log_dir = '/tmp'+log_dir
dump_sql = task_param['dump_sql']
dump_sql = dump_sql.replace('{table_name}',task_param['table_name'])
dump_sql = dump_sql.replace('{partition_name}',task_param['partition_name'])
dump_sql = dump_sql.replace('{db_name}',task_param['db_name'])
print(dump_sql)
result = mysql.dump(sql,local_log_dir+lzo_file_name)
#print(result)
'''
将任务标识为加载文件完成：2
'''
datas = {'load_status':2}
where = {}
where['id'] = int(task_param['id'])
mysql.update('etl_data_log',
' id = %(id)d' % where,
**datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
print (exc)
mysql.rollback()
raise self.retry(exc=exc, countdown=60)
def run_task(self, task_param):
mysql = Custom_MySQL(using='hadoops2')
mysql_etl = Custom_MySQL(using='etl_manage')
mysql.begin()
mysql_etl.begin()
where = {'id': int(task_param['id'])}
try:
hive = Custom_Hive(using='ares_dw')
game = task_param['game']
platform = task_param['platform']
table_name = task_param['table_name']
log_date = task_param['log_date']
prefix_sql = task_param['prefix_sql']
exec_sql = task_param['exec_sql']
post_sql = task_param['post_sql']
date_cycle = task_param['date_cycle']
random_str = str(random.randint(0, 999999999))
stimes = str(int(time.time()))
tmp_file_dir = "/tmp/tmp/%s/%s/%s" % (game, platform, log_date)
#创建本地目录
if not os.path.exists(tmp_file_dir):
os.makedirs(tmp_file_dir)
tmp_file = "%s/%s_%s_%s_%s.txt" % (tmp_file_dir, table_name, date_cycle, random_str, stimes)
hql_conf = "SET hive.support.concurrency=false;SET hive.exec.compress.output=true;" \
"SET mapred.output.compression.codec=com.hadoop.compression.lzo.LzopCodec; "
'''
将任务标示为开始执行：1
'''
datas = {'status': 1, 'start_time': str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
#执行前置sql，将数据临时写入本地，用于mysql load数据
if prefix_sql is not None:
result = mysql.delete_by_sql(prefix_sql)
logger.info('exec prefix_sql: delete old data {0}'.format(result['output']))
if result['status'] == 0:
'''
将任务标示为删除临时文件完成：2
'''
datas = {'status': 2}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
'''
开始执行hive ql,将数据dump到本地
'''
result = hive.dump(hql_conf+exec_sql, tmp_file)
logger.info('exec exec_sql: dump data {0}'.format(result['output']))
if result['status'] == 0 and True == os.path.exists('%s' % tmp_file):
'''
将任务标示为dump hive数据完成：3
'''
datas = {'status': 3, 'tmp_file_name': tmp_file}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
#执行后置sql
if post_sql is not None:
post_sql = post_sql.replace('{dir_path}', tmp_file)
post_sql = post_sql.replace('{table_name}', task_param['table_name'])
post_sql = post_sql.replace('{db_name}', task_param['db_name'])
result = mysql.load(post_sql)
logger.info('exec post_sql: load data to hdfs {0}'.format(result['output']))
if result['status'] == 0:
'''
将任务标识为录入mysql完成：4
'''
datas = {'status': 4, 'end_time': str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
else:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], post_sql))
else:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], exec_sql))
else:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], prefix_sql))
'''
执行失败，将其状态标为未执行：0
'''
datas = {'status': 0}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
'''
将任务标示为：(模拟) 已从任务队列中移除
'''
datas = {'in_queue': 0}
update_result = mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
# 如果数据库更新失败，再调用一次。 如果还是失败，等待自动修复机制，但这样的概率应该很小了。
if update_result != 1:
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql.commit()
mysql_etl.close()
mysql.close()
return True
except Exception as exc:
logger.error('dm2report error: %s' % exc)
mysql_etl.rollback()
mysql.rollback()
datas = {'in_queue': 0, 'status': 0}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql_etl.close()
mysql.close()
raise self.retry(exc=exc, countdown=60)
def run_task(self, task_param):
redis = Custom_Redis(using='etl_manage')
mysql = Custom_MySQL(using='etl_manage')
mysql.begin()
datas = {'download_status': 0}
where = {'id': int(task_param['id'])}
local_log_dir = ""
lzo_file_name = ""
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']
}
log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param
txt_file_name = "%(log_name)s_%(source_ip)s_%(log_time)s.txt" % filename_dict
lzo_file_name = "%(log_name)s_%(source_ip)s_%(log_time)s.txt.lzo" % filename_dict
md5_file_name = "%(log_name)s_%(source_ip)s_%(log_time)s.txt.lzo.md5" % filename_dict
lzo_download_url = task_param['download_url'].rstrip(
'/') + log_dir + lzo_file_name
md5_download_url = task_param['download_url'].rstrip(
'/') + log_dir + md5_file_name
#从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 not os.path.exists(local_log_dir):
os.makedirs(local_log_dir)
#排除同名文件存在的可能，同时为修复执行提供方便
if os.path.exists('%s%s' % (local_log_dir, txt_file_name)):
cmd_remove = 'rm -f %s%s*' % (local_log_dir, txt_file_name)
logger.info('remove history file: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(remove_result['status'], remove_result['output'],
cmd_remove))
'''
下载md5文件,如果md5文件不存在则退出，不再继续执行程序，同时不向数据库写入任何标示
'''
md5_line = ' wget -o /tmp/log/wget_log -O %s%s %s' % (
local_log_dir, md5_file_name, md5_download_url)
logger.info('md5 info: {0}'.format(md5_line))
md5_result = cmd.run(md5_line)
if md5_result['status'] != 0:
logger.error(
'Error Code %s : %s Cmd: %s' %
(md5_result['status'], md5_result['output'], md5_line))
else:
'''
将任务标识为开始执行：1
'''
datas['download_status'] = 1
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
下载数据文件
'''
lzo_line = ' wget -o /tmp/log/wget_log -O %s%s %s' % (
local_log_dir, lzo_file_name, lzo_download_url)
logger.info('file info: {0}'.format(lzo_line))
lzo_result = cmd.run(lzo_line)
if lzo_result['status'] != 0:
logger.error(
'Error Code %s : %s Cmd: %s' %
(lzo_result['status'], lzo_result['output'], lzo_line))
datas['download_status'] = 0
else:
'''
将任务标识为下载完成：2
'''
datas['download_status'] = 2
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
md5校验，如果未通过则不再继续执行程序
'''
check_line = "cat %s%s |grep `md5sum %s%s|cut -d ' ' -f 1`" % (
local_log_dir, md5_file_name, local_log_dir, lzo_file_name)
logger.info('md5 or md5 info: {0}'.format(check_line))
check_result = cmd.run(check_line)
if check_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(check_result['status'],
check_result['output'], check_line))
datas['download_status'] = 0
else:
'''
lzop解压缩
'''
cmd_line = ' lzop -dP %s%s' % (local_log_dir,
lzo_file_name)
logger.info('file info: {0}'.format(cmd_line))
cmd_result = cmd.run(cmd_line)
if cmd_result['status'] != 0:
logger.error('Lzop Code %s : %s Cmd: %s' %
(cmd_result['status'],
cmd_result['output'], cmd_line))
datas['download_status'] = 0
else:
'''
将任务标识md5一致，完成下载任务：3
'''
datas['download_status'] = 3
'''
将任务标示为：(模拟) 已从任务队列中移除
'''
datas['in_download_queue'] = 0
update_result = mysql.update('etl_data_log', ' id = %(id)d' % where,
**datas)
# 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
if update_result != 1:
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
print(exc)
logger.error('download error : %s' % exc)
mysql.rollback()
kill_proces = "kill -9 `ps -ef |grep wget |grep -v grep |grep '%s%s'|awk '{print $2}'`" % (
local_log_dir, lzo_file_name)
cmd.run(kill_proces)
datas = {'in_download_queue': 0, 'download_status': 0}
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
raise self.retry(exc=exc, countdown=60)
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 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)
#coding=utf-8 """ auth: wuqichao、suguoxin mail: [email protected]、[email protected] create_time: 2015-9-17 10:00:00 used: 执行下载任务 last_update: 2016-04-28 15:15:00 """ import datetime import sys from download.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') #数据时间点(每五分钟)，格式如：0005、2400 log_time = now_time.strftime("%H%M") sql = 'select * from etl_data_log ' \ 'where game="%s" and platform="%s" and do_rate="%s" and etl_status=6 and download_status=0 and task_date="%s" ' \
def run_task(self, task_param):
mysql = Custom_MySQL(using='hadoops2')
mysql_etl = Custom_MySQL(using='etl_manage')
redis = Custom_Redis(using='etl_task')
mysql.begin()
mysql_etl.begin()
datas = {'status': 0}
where = {'id': int(task_param['id'])}
task_key_exc = ""
try:
hive = Custom_Hive(using='ares_dw')
game = task_param['game']
platform = task_param['platform']
table_name = task_param['table_name']
log_date = task_param['log_date']
prefix_sql = task_param['prefix_sql']
exec_sql = task_param['exec_sql']
post_sql = task_param['post_sql']
date_cycle = task_param['date_cycle']
random_str = str(random.randint(0, 999999999))
stimes = str(int(time.time()))
task_date = task_param['task_date']
task_name = task_param['task_name']
do_rate = task_param['do_rate']
#task_key_tmp = str(game)+str(platform)+str(task_name)+str(date_cycle)+str(do_rate)+str(log_date)+str(task_date)
task_key_tmp = str(task_name)+str(date_cycle)+str(do_rate)+str(log_date)+str(task_date)
task_key_md5 = hashlib.md5()
task_key_md5.update(task_key_tmp)
task_key_md5_result = task_key_md5.hexdigest()
task_key = "dm2report_new_"+str(game)+"_"+str(platform)+"_"+str(task_key_md5_result)
task_key_exc = task_key
'''
celery 本身的机制存在漏洞，会将一个已经完成任务再次分配给其他的worker，致使同一个任务执行多次
为防止此种现象，在任务开始执行时，将任务的“唯一标示”写入redis中，标注已执行
'''
#如果task_key is None, 则表示该条任务没有执行过，正常执行即可
#如果task_key = 0, 则表示该条任务上次执行失败，允许重复执行
if redis.get(task_key) == "0" or redis.get(task_key) is None:
tmp_file_dir = "/tmp/tmp/%s/%s/%s" % (game, platform, log_date)
#创建本地目录
if not os.path.exists(tmp_file_dir):
os.makedirs(tmp_file_dir)
tmp_file = "%s/%s_%s_%s_%s.txt" % (tmp_file_dir, table_name, date_cycle, random_str, stimes)
hql_conf = "SET hive.support.concurrency=false;" \
"SET hive.exec.compress.output=true;" \
"SET mapred.output.compression.codec=com.hadoop.compression.lzo.LzopCodec; "
'''
将任务标示为开始执行：1
'''
datas['status'] = 1
datas['start_time'] = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
#在redis标注 任务正在执行
redis.set(task_key, 1)
#执行前置sql，将数据临时写入本地，用于mysql load数据
if prefix_sql is not None:
result = mysql.delete_by_sql(prefix_sql)
logger.info('exec prefix_sql: delete old data {0}'.format(result['output']))
if result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], prefix_sql))
'''
执行失败，将其状态标为未执行：0
'''
datas['status'] = 0
#在redis标注 任务未开始执行
redis.set(task_key, 0)
else:
'''
将任务标示为删除临时文件完成：2
'''
datas['status'] = 2
datas.pop('start_time')
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
'''
开始执行hive ql,将数据dump到本地
'''
result = hive.dump(hql_conf+exec_sql, tmp_file)
logger.info('exec exec_sql: dump data {0}'.format(result['output']))
if result['status'] != 0 or False == os.path.exists('%s' % tmp_file):
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], exec_sql))
#在redis标注 任务未开始执行
redis.set(task_key, 0)
else:
'''
将任务标示为dump hive数据完成：3
'''
datas['status'] = 3
datas['tmp_file_name'] = tmp_file
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
#执行后置sql
if post_sql is not None:
post_sql = post_sql.replace('{dir_path}', tmp_file)
post_sql = post_sql.replace('{table_name}', task_param['table_name'])
post_sql = post_sql.replace('{db_name}', task_param['db_name'])
result = mysql.load(post_sql)
logger.info('exec post_sql: load data to hdfs {0}'.format(result['output']))
if result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], post_sql))
#在redis标注 任务未开始执行
redis.set(task_key, 0)
else:
'''
将任务标识为录入mysql完成：4
'''
datas['status'] = 4
datas['end_time'] = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
datas.pop('tmp_file_name')
#在redis标注 任务已成功执行
redis.set(task_key, 2)
else:
logger.error('prefix_sql is null')
datas['status'] = 0
#在redis标注 任务未开始执行
redis.set(task_key, 0)
#如果task_key=2, 则标示该条任务已经运行成功
elif redis.get(task_key) == "2":
datas['status'] = 4
#该条任务正在运行中
else:
return True
'''
将任务标示为：(模拟) 已从任务队列中移除
'''
datas['in_queue'] = 0
update_result = mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
# 如果数据库更新失败，再调用一次。 如果还是失败，等待自动修复机制，但这样的概率应该很小了。
if update_result != 1:
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql.commit()
mysql_etl.close()
mysql.close()
return True
except Exception as exc:
logger.error('dm2report error: %s' % exc)
mysql_etl.rollback()
mysql.rollback()
redis.set(task_key_exc, 0)
datas = {'in_queue': 0, 'status': 0}
mysql_etl.update('dm2report_new_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql_etl.close()
mysql.close()
raise self.retry(exc=exc, countdown=60)
last_update: 2016-05-04 11:10:20
"""
import datetime
import sys
from etl_data.tasks import run_task
from custom.db.mysql import Custom_MySQL
from custom.db.redis_tools import Custom_Redis
try:
import xml.etree.cElementTree as ET
except ImportError:
import xml.etree.ElementTree as ET
mysql = Custom_MySQL(using='etl_manage')
redis = Custom_Redis(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')
#数据时间点(每五分钟)，格式如：0005、2400
log_time = now_time.strftime("%H%M")
#当前机器的外网ip，根据不同机器修改即可
machine = "120.26.1.224"
def __init__(self):
self.mysql = Custom_MySQL(using='etl_manage')
self.source_game = 'ares'
""" auth: suguoxin mail: [email protected] create_time: 2016-03-08 16:00:00 used: 启动dw2dm任务 last_update: 2016-04-28 15:00:00 """ import datetime import sys from dw2dm.tasks import run_task from custom.db.mysql import Custom_MySQL from custom.command import Custom_Command as cmd 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') #用于判断，是否需要校验对于上层任务(mergefile2dw任务)的依赖，onrely: 需要，offrely: 不需要 is_rely = "" try: is_rely = sys.argv[5] except Exception as exc: is_rely = "onrely" #数据日期，格式如：20151015
def __init__(self):
self.center_mysql = Custom_MySQL(using='center_app')
#coding=utf-8 """ auth: suguoxin mail: [email protected] create_time: 2016-06-23 17:03:00 used: 执行下载任务，为处理服务器过多的有游戏所建立 last_update: 2016-06-23 17:03:00 """ import datetime import sys from download_new.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') #数据时间点(每五分钟)，格式如：0005、2400 log_time = now_time.strftime("%H%M") def task_5min():
#!/usr/bin/python #coding=utf-8 """ auth:suguoxin mail:[email protected] createtime:2016-01-12 00:30:00 usege: 用于自动重置执行失败的任务 """ import datetime from custom.db.mysql import Custom_MySQL mysql = Custom_MySQL(using='etl_manage') #当前时间 now_time = datetime.datetime.now() task_date = now_time.strftime('%Y%m%d') log_time = now_time.strftime('%H%M') if log_time < '0200': task_date = (now_time-datetime.timedelta(days=1)).strftime('%Y%m%d') log_time_before_30min = (now_time-datetime.timedelta(seconds=1800)).strftime('%H%M') log_time_before_1hour = (now_time-datetime.timedelta(seconds=3600)).strftime('%H%M') now = now_time.strftime('%Y-%m-%d %H:%M:%S') try: mysql.begin()
def run_task(self, task_param):
mysql = Custom_MySQL(using='etl_manage')
hive = Custom_Hive(using='ares_dw')
redis = Custom_Redis(using='etl_manage')
mysql.begin()
where = {'id': int(task_param['id'])}
try:
log_date = datetime.datetime.strptime(task_param['log_date'], '%Y%m%d').strftime("%Y-%m-%d")
log_date_1 = (datetime.datetime.strptime(task_param['log_date'], '%Y%m%d')-datetime.timedelta(days=1)).strftime("%Y-%m-%d")
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']}
index_dict = {'db_name': task_param['db_name'], 'table_name': task_param['table_name'],
'platform': task_param['platform'], 'log_date': log_date}
partition = {'platform': task_param['platform'], 'log_date': log_date}
index_dict_1 = {'db_name': task_param['db_name'], 'table_name': task_param['table_name'],
'platform': task_param['platform'], 'log_date': log_date_1}
partition_1 = {'platform': task_param['platform'], 'log_date': log_date_1}
log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s" % dir_param
flag = task_param['flag']
file_name = "%(log_name)s" % filename_dict
file_name_txt = "%(log_name)s_%(log_time)s_result.txt" % filename_dict
file_name_lzo = "%(log_name)s_%(log_time)s_result.txt.lzo" % filename_dict
file_name_row = "%(log_name)s_%(log_time)s_result.txt.row" % filename_dict
index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict
partition_name = "plat_form='%(platform)s',log_date='%(log_date)s'" % partition
project_path = os.getcwd()
log_time = task_param['log_time']
do_rate = task_param['do_rate']
#if flag == "snap" or (do_rate == "1day" and flag == "log")
if flag == "snap":
index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict_1
partition_name = "plat_form='%(platform)s',log_date='%(log_date)s'" % partition_1
#从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
logger.info('local_log_dir: {0}'.format(local_log_dir))
#判断目录是否存在
if os.path.exists('%s' % local_log_dir):
#排除同名文件存在的可能，同时为修复执行提供方便
if os.path.exists('%s/%s' % (local_log_dir, file_name_txt)):
cmd_remove = 'rm -f %s/%s*' % (local_log_dir, file_name_txt)
logger.info('remove history file: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (remove_result['status'], remove_result['output'], cmd_remove))
datas = {'load_status': 1}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
cmd_merge = '/bin/bash %s/mergefile2dw/merge_data.sh %s %s %s %s %s' \
'' % (project_path, local_log_dir, local_log_dir, file_name, do_rate, log_time)
logger.info('merge data: {0}'.format(cmd_merge))
merge_result = cmd.run(cmd_merge)
logger.info('merge data result {0}'.format(merge_result['output']))
if merge_result['status'] == 0:
#读取总条数
row = open('%s/%s' % (local_log_dir, file_name_row)).read()
'''
合并数据完成：2
'''
datas = {'load_status': 2, 'row_num': int(row)}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
压缩数据
'''
cmd_compress = '/bin/bash %s/mergefile2dw/compress_data.sh %s %s' % (project_path, local_log_dir, file_name_txt)
logger.info('compress data: {0}'.format(cmd_compress))
compress_result = cmd.run(cmd_compress)
if compress_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (compress_result['status'], compress_result['output'], cmd_compress))
else:
'''
压缩数据完成：3
'''
datas = {'load_status': 3}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
#执行load之前，删除同名文件，防止同一个文件出现两次的可能
cmd_remove = '/bin/bash %s/mergefile2dw/remove_damaged_file.sh %s %s' % (project_path, index_dir_name, file_name_lzo)
logger.info('remove damaged files: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (remove_result['status'], remove_result['output'], cmd_remove))
'''
文件加载到hive中
'''
load_sql = task_param['load_sql']
load_sql = load_sql.replace('{dir_path}', local_log_dir+"/"+file_name_lzo)
load_sql = load_sql.replace('{table_name}', task_param['table_name'])
load_sql = load_sql.replace('{partition_name}', '%s' % partition_name)
load_sql = load_sql.replace('{db_name}', task_param['db_name'])
logger.info('hive load SQL: {0}'.format(load_sql))
result = hive.load(load_sql)
logger.info('hive load result {0}'.format(result['output']))
if result['status'] == 0:
'''
将任务标识为加载文件完成：4
'''
datas = {'load_status': 4}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
建立索引，否则lzo将不支持split
'''
#print index_dir_name
cmd_index = '/bin/bash %s/mergefile2dw/create_lzo_indexer.sh %s %s' % (project_path, index_dir_name, file_name_lzo)
logger.info('create lzo index: {0}'.format(cmd_index))
index_result = cmd.run(cmd_index)
if index_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (index_result['status'], index_result['output'], cmd_index))
else:
if "create index success" in index_result['output']:
'''
将任务标识为建立lzo索引完成：5
'''
datas = {'load_status': 5}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
else:
'''
如果load数据失败，则删除半途出现错误的文件，防止hive查询的时候报错
'''
cmd_remove = '/bin/bash %s/mergefile2dw/remove_damaged_file.sh %s %s' % (project_path, index_dir_name, file_name_lzo)
logger.info('remove damaged files: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (remove_result['status'], remove_result['output'], cmd_remove))
else:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], load_sql))
else:
'''
合并数据失败
'''
datas = {'load_status': 0}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
logger.error('Error Code %s : %s Cmd: %s' % (merge_result['status'], merge_result['output'], merge_result))
'''
执行完毕，模拟从队列中清楚任务:0
'''
datas = {'in_queue': 0}
update_result = mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
# 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
if update_result != 1:
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
logger.error('mergefile2dw error: %s' % exc)
mysql.rollback()
datas = {'in_queue': 0, 'load_status': 0}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
raise self.retry(exc=exc, countdown=30)
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 run(self):
db = Custom_MySQL(using='log')
status = {'flag': 1}
db.update(
'batch_detail', 'batch_id="%s" and ip ="%s"' %
(self.host['batch_id'], self.host['ip']), **status)
db.commit()
db.close()
#建立连接
self.ssh = paramiko.SSHClient()
try:
#如果没有密码就走public key
if self.host.get('pwd', True) == True:
privatekeyfile = os.path.expanduser('/root/.ssh/id_rsa')
paramiko.RSAKey.from_private_key_file(privatekeyfile)
#缺失host_knows时的处理方法
known_host = "/root/.ssh/known_hosts"
self.ssh.load_system_host_keys(known_host)
self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
#os.system('/opt/local/junos/junos')
#连接远程客户机器
self.ssh.connect(hostname=self.host['ip'],
port=int(self.host['port']),
username=self.host['user'],
password=self.host['pwd'],
compress=True,
timeout=60)
#获取远程命令执行结果
stdin, stdout, stderr = self.ssh.exec_command(self.host['cmd'],
bufsize=65535,
timeout=60)
temp = stdout.readlines()
db = Custom_MySQL(using='log')
status = {'flag': 2, 'result': json.dumps(temp)}
db.update(
'batch_detail', 'batch_id="%s" and ip ="%s"' %
(self.host['batch_id'], self.host['ip']), **status)
db.commit()
db.close()
#输出执行结果
self.ssh.close()
except:
self.ssh.close()
#print trace_back()
#以防paramiko本身出问题，这里再用shell运行一次，如果还出问题再确认为问题
cmd = "ssh -p %s -o StrictHostKeyChecking=no %s@%s '%s'" % (
self.host['port'], self.host['user'], self.host['ip'],
self.host['cmd'])
(status, output) = commands.getstatusoutput(cmd)
if status == 0:
db = Custom_MySQL(using='log')
status = {'flag': 2, 'result': json.dumps(output)}
db.update(
'batch_detail', 'batch_id="%s" and ip ="%s"' %
(self.host['batch_id'], self.host['ip']), **status)
db.commit()
db.close()
else:
db = Custom_MySQL(using='log')
status = {'flag': -1, 'result': 'faild'}
db.update(
'batch_detail', 'batch_id="%s" and ip ="%s"' %
(self.host['batch_id'], self.host['ip']), **status)
db.commit()
db.close()
def run_task(self, task_param):
mysql = Custom_MySQL(using='etl_manage')
mysql.begin()
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']
}
'''
游戏＼平台＼日期＼业务日志名＼日志或者md5文件
'''
log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s/" % dir_param
lzo_file_name = "%(log_name)s_%(log_time)s.txt" % filename_dict
local_log_dir = '/tmp' + log_dir
dump_sql = task_param['dump_sql']
dump_sql = dump_sql.replace('{table_name}', task_param['table_name'])
dump_sql = dump_sql.replace('{partition_name}',
task_param['partition_name'])
dump_sql = dump_sql.replace('{db_name}', task_param['db_name'])
print(dump_sql)
result = mysql.dump(sql, local_log_dir + lzo_file_name)
#print(result)
'''
将任务标识为加载文件完成：2
'''
datas = {'load_status': 2}
where = {}
where['id'] = int(task_param['id'])
mysql.update('etl_data_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
print(exc)
mysql.rollback()
raise self.retry(exc=exc, countdown=60)
def __init__(self):
self.center_mysql = Custom_MySQL(using='center_app')
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 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):
mysql = Custom_MySQL(using='hadoops2')
mysql_etl = Custom_MySQL(using='etl_manage')
mysql.begin()
mysql_etl.begin()
where = {'id': int(task_param['id'])}
try:
hive = Custom_Hive(using='ares_dw')
game = task_param['game']
platform = task_param['platform']
table_name = task_param['table_name']
log_date = task_param['log_date']
prefix_sql = task_param['prefix_sql']
exec_sql = task_param['exec_sql']
post_sql = task_param['post_sql']
date_cycle = task_param['date_cycle']
random_str = str(random.randint(0, 999999999))
stimes = str(int(time.time()))
tmp_file_dir = "/tmp/tmp/%s/%s/%s" % (game, platform, log_date)
#创建本地目录
if not os.path.exists(tmp_file_dir):
os.makedirs(tmp_file_dir)
tmp_file = "%s/%s_%s_%s_%s.txt" % (tmp_file_dir, table_name,
date_cycle, random_str, stimes)
hql_conf = "SET hive.support.concurrency=false;SET hive.exec.compress.output=true;" \
"SET mapred.output.compression.codec=com.hadoop.compression.lzo.LzopCodec; "
'''
将任务标示为开始执行：1
'''
datas = {
'status':
1,
'start_time':
str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
#执行前置sql，将数据临时写入本地，用于mysql load数据
if prefix_sql is not None:
result = mysql.delete_by_sql(prefix_sql)
logger.info('exec prefix_sql: delete old data {0}'.format(
result['output']))
if result['status'] == 0:
'''
将任务标示为删除临时文件完成：2
'''
datas = {'status': 2}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where,
**datas)
mysql_etl.commit()
'''
开始执行hive ql,将数据dump到本地
'''
result = hive.dump(hql_conf + exec_sql, tmp_file)
logger.info('exec exec_sql: dump data {0}'.format(
result['output']))
if result['status'] == 0 and True == os.path.exists(
'%s' % tmp_file):
'''
将任务标示为dump hive数据完成：3
'''
datas = {'status': 3, 'tmp_file_name': tmp_file}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where,
**datas)
mysql_etl.commit()
#执行后置sql
if post_sql is not None:
post_sql = post_sql.replace('{dir_path}', tmp_file)
post_sql = post_sql.replace('{table_name}',
task_param['table_name'])
post_sql = post_sql.replace('{db_name}',
task_param['db_name'])
result = mysql.load(post_sql)
logger.info(
'exec post_sql: load data to hdfs {0}'.format(
result['output']))
if result['status'] == 0:
'''
将任务标识为录入mysql完成：4
'''
datas = {
'status':
4,
'end_time':
str(datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'))
}
mysql_etl.update('dm2report_log',
' id = %(id)d' % where, **datas)
else:
logger.error(
'Error Code %s : %s Cmd: %s' %
(result['status'], result['output'], post_sql))
else:
logger.error(
'Error Code %s : %s Cmd: %s' %
(result['status'], result['output'], exec_sql))
else:
logger.error('Error Code %s : %s Cmd: %s' %
(result['status'], result['output'], prefix_sql))
'''
执行失败，将其状态标为未执行：0
'''
datas = {'status': 0}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where,
**datas)
'''
将任务标示为：(模拟) 已从任务队列中移除
'''
datas = {'in_queue': 0}
update_result = mysql_etl.update('dm2report_log',
' id = %(id)d' % where, **datas)
# 如果数据库更新失败，再调用一次。 如果还是失败，等待自动修复机制，但这样的概率应该很小了。
if update_result != 1:
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql.commit()
mysql_etl.close()
mysql.close()
return True
except Exception as exc:
logger.error('dm2report error: %s' % exc)
mysql_etl.rollback()
mysql.rollback()
datas = {'in_queue': 0, 'status': 0}
mysql_etl.update('dm2report_log', ' id = %(id)d' % where, **datas)
mysql_etl.commit()
mysql_etl.close()
mysql.close()
raise self.retry(exc=exc, countdown=60)
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 run_task(self, task_param):
mysql = Custom_MySQL(using='etl_manage')
hive = Custom_Hive(using='ares_dw')
redis = Custom_Redis(using='etl_manage')
mysql.begin()
where = {'id': int(task_param['id'])}
try:
log_date = datetime.datetime.strptime(task_param['log_date'],
'%Y%m%d').strftime("%Y-%m-%d")
log_date_1 = (
datetime.datetime.strptime(task_param['log_date'], '%Y%m%d') -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
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']
}
index_dict = {
'db_name': task_param['db_name'],
'table_name': task_param['table_name'],
'platform': task_param['platform'],
'log_date': log_date
}
partition = {'platform': task_param['platform'], 'log_date': log_date}
index_dict_1 = {
'db_name': task_param['db_name'],
'table_name': task_param['table_name'],
'platform': task_param['platform'],
'log_date': log_date_1
}
partition_1 = {
'platform': task_param['platform'],
'log_date': log_date_1
}
log_dir = "/%(game)s/%(platform)s/%(log_date)s/%(log_name)s" % dir_param
flag = task_param['flag']
file_name = "%(log_name)s" % filename_dict
file_name_txt = "%(log_name)s_%(log_time)s_result.txt" % filename_dict
file_name_lzo = "%(log_name)s_%(log_time)s_result.txt.lzo" % filename_dict
file_name_row = "%(log_name)s_%(log_time)s_result.txt.row" % filename_dict
index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict
partition_name = "plat_form='%(platform)s',log_date='%(log_date)s'" % partition
project_path = os.getcwd()
log_time = task_param['log_time']
do_rate = task_param['do_rate']
#if flag == "snap" or (do_rate == "1day" and flag == "log")
if flag == "snap":
index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict_1
partition_name = "plat_form='%(platform)s',log_date='%(log_date)s'" % partition_1
#从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
logger.info('local_log_dir: {0}'.format(local_log_dir))
#判断目录是否存在
if os.path.exists('%s' % local_log_dir):
#排除同名文件存在的可能，同时为修复执行提供方便
if os.path.exists('%s/%s' % (local_log_dir, file_name_txt)):
cmd_remove = 'rm -f %s/%s*' % (local_log_dir, file_name_txt)
logger.info('remove history file: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(remove_result['status'],
remove_result['output'], cmd_remove))
datas = {'load_status': 1}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
cmd_merge = '/bin/bash %s/mergefile2dw/merge_data.sh %s %s %s %s %s' \
'' % (project_path, local_log_dir, local_log_dir, file_name, do_rate, log_time)
logger.info('merge data: {0}'.format(cmd_merge))
merge_result = cmd.run(cmd_merge)
logger.info('merge data result {0}'.format(merge_result['output']))
if merge_result['status'] == 0:
#读取总条数
row = open('%s/%s' % (local_log_dir, file_name_row)).read()
'''
合并数据完成：2
'''
datas = {'load_status': 2, 'row_num': int(row)}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
压缩数据
'''
cmd_compress = '/bin/bash %s/mergefile2dw/compress_data.sh %s %s' % (
project_path, local_log_dir, file_name_txt)
logger.info('compress data: {0}'.format(cmd_compress))
compress_result = cmd.run(cmd_compress)
if compress_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(compress_result['status'],
compress_result['output'], cmd_compress))
else:
'''
压缩数据完成：3
'''
datas = {'load_status': 3}
mysql.update('file2dw_log', ' id = %(id)d' % where,
**datas)
mysql.commit()
#执行load之前，删除同名文件，防止同一个文件出现两次的可能
cmd_remove = '/bin/bash %s/mergefile2dw/remove_damaged_file.sh %s %s' % (
project_path, index_dir_name, file_name_lzo)
logger.info('remove damaged files: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(remove_result['status'],
remove_result['output'], cmd_remove))
'''
文件加载到hive中
'''
load_sql = task_param['load_sql']
load_sql = load_sql.replace(
'{dir_path}', local_log_dir + "/" + file_name_lzo)
load_sql = load_sql.replace('{table_name}',
task_param['table_name'])
load_sql = load_sql.replace('{partition_name}',
'%s' % partition_name)
load_sql = load_sql.replace('{db_name}',
task_param['db_name'])
logger.info('hive load SQL: {0}'.format(load_sql))
result = hive.load(load_sql)
logger.info('hive load result {0}'.format(
result['output']))
if result['status'] == 0:
'''
将任务标识为加载文件完成：4
'''
datas = {'load_status': 4}
mysql.update('file2dw_log', ' id = %(id)d' % where,
**datas)
mysql.commit()
'''
建立索引，否则lzo将不支持split
'''
#print index_dir_name
cmd_index = '/bin/bash %s/mergefile2dw/create_lzo_indexer.sh %s %s' % (
project_path, index_dir_name, file_name_lzo)
logger.info('create lzo index: {0}'.format(cmd_index))
index_result = cmd.run(cmd_index)
if index_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' %
(index_result['status'],
index_result['output'], cmd_index))
else:
if "create index success" in index_result[
'output']:
'''
将任务标识为建立lzo索引完成：5
'''
datas = {'load_status': 5}
mysql.update('file2dw_log',
' id = %(id)d' % where, **datas)
else:
'''
如果load数据失败，则删除半途出现错误的文件，防止hive查询的时候报错
'''
cmd_remove = '/bin/bash %s/mergefile2dw/remove_damaged_file.sh %s %s' % (
project_path, index_dir_name,
file_name_lzo)
logger.info('remove damaged files: {0}'.format(
cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error(
'Error Code %s : %s Cmd: %s' %
(remove_result['status'],
remove_result['output'], cmd_remove))
else:
logger.error(
'Error Code %s : %s Cmd: %s' %
(result['status'], result['output'], load_sql))
else:
'''
合并数据失败
'''
datas = {'load_status': 0}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
logger.error('Error Code %s : %s Cmd: %s' %
(merge_result['status'], merge_result['output'],
merge_result))
'''
执行完毕，模拟从队列中清楚任务:0
'''
datas = {'in_queue': 0}
update_result = mysql.update('file2dw_log', ' id = %(id)d' % where,
**datas)
# 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
if update_result != 1:
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
logger.error('mergefile2dw error: %s' % exc)
mysql.rollback()
datas = {'in_queue': 0, 'load_status': 0}
mysql.update('file2dw_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
raise self.retry(exc=exc, countdown=30)
def __init__(self):
self.mysql = Custom_MySQL(using='etl_manage')
self.source_game = 'ares'
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)
def run_task(self, task_param):
redis = Custom_Redis(using='etl_task')
mysql = Custom_MySQL(using='etl_manage')
mysql.begin()
datas = {'status': 0}
where = {'id': int(task_param['id'])}
hive = Custom_Hive(using='hadoops2')
task_key_exc = ""
try:
game = task_param['game']
platform = task_param['platform']
task_dict = {'log_name': task_param['log_name'], 'do_rate': task_param['do_rate'],
'log_date': task_param['log_date'], 'task_date': task_param['task_date']}
task_key_tmp = "%(log_name)s%(do_rate)s%(log_date)s%(task_date)s" % task_dict
task_key_md5 = hashlib.md5()
task_key_md5.update(task_key_tmp)
task_key_md5_result = task_key_md5.hexdigest()
task_key = "dw2dm_"+str(game)+"_"+str(platform)+"_"+str(task_key_md5_result)
task_key_exc = task_key
'''
celery 本身的机制存在漏洞，会将一个已经完成任务再次分配给其他的worker，致使同一个任务执行多次
为防止此种现象，在任务开始执行时，将任务的“唯一标示”写入redis中，标注已执行
'''
#如果task_key is None, 则表示该条任务没有执行过，正常执行即可
#如果task_key = 0, 则表示该条任务上次执行失败，允许重复执行
if redis.get(task_key) == "0" or redis.get(task_key) is None:
exec_sql = task_param['exec_sql']
log_date = (datetime.datetime.strptime(task_param['log_date'], '%Y%m%d')).strftime("%Y-%m-%d")
#prefix_sql = task_param['prefix_sql']
#post_sql = task_param['post_sql']
index_dict = {'db_name': task_param['db_name'], 'table_name': task_param['table_name'],
'platform': task_param['platform'], 'log_date': log_date}
index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict
#用于删除索引
del_index_dir_name = "%(db_name)s.db/%(table_name)s/plat_form=%(platform)s/log_date=%(log_date)s/" % index_dict
if platform == 'all':
index_dict = {'db_name': task_param['db_name'], 'table_name': task_param['table_name'],
'log_date': log_date}
#用于建立索引(建立索引时，不能使用*通配符，所以仅指定到表名)
index_dir_name = "%(db_name)s.db/%(table_name)s/" % index_dict
#用于删除索引，虽然建立的时候指定到表名，但删除的时候，仅删除当天的
del_index_dir_name = "%(db_name)s.db/%(table_name)s/*/log_date=%(log_date)s/*" % index_dict
hql_conf = "set hive.exec.dynamic.partition.mode=nonstrict;" \
"set hive.exec.compress.output=true;" \
"set mapred.output.compress=true;" \
"set mapred.output.compression.codec=com.hadoop.compression.lzo.LzopCodec;" \
"set io.compression.codecs=com.hadoop.compression.lzo.LzopCodec; "
#获取项目根路径：/data/etl_manage
project_path = os.getcwd()
'''
将任务标示为开始执行：1
'''
datas['status'] = 1
datas['start_time'] = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
mysql.update('dw2dm_log', ' id = %(id)d' % where, **datas)
mysql.commit()
#在redis标注 任务正在执行
redis.set(task_key, 1)
#执行正式的 select xx insert xx 语句
result = hive.select_insert(hql_conf+exec_sql)
logger.info('exec exec_sql: select xxx insert xxx {0}'.format(result['output']))
if result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (result['status'], result['output'], exec_sql))
'''
执行失败，将其状态标为未执行：0
'''
datas['status'] = 0
#在redis标注 任务未执行
redis.set(task_key, 0)
else:
'''
将任务标示为执行hive ql, select xxx insert xxx完成：2
'''
datas['status'] = 2
datas.pop('start_time')
mysql.update('dw2dm_log', ' id = %(id)d' % where, **datas)
mysql.commit()
'''
建立索引，否则lzo将不支持split
'''
cmd_index = '/bin/bash %s/dw2dm/create_lzo_indexer.sh %s' % (project_path, index_dir_name)
logger.info('create lzo index: {0}'.format(cmd_index))
index_result = cmd.run(cmd_index)
if index_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (index_result['status'], index_result['output'], cmd_index))
#在redis标注 任务未执行
redis.set(task_key, 0)
else:
if "create index success" in index_result['output']:
'''
将任务标识为建立lzo索引完成：3
'''
datas['status'] = 3
datas['end_time'] = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
#在redis标注 任务已完成且成功
redis.set(task_key, 2)
else:
'''
如果load数据失败，则删除半途出现错误的文件，防止hive查询的时候报错
'''
cmd_remove = '/bin/bash %s/dw2dm/remove_damaged_file.sh %s' % (project_path, del_index_dir_name)
logger.info('remove damaged files: {0}'.format(cmd_remove))
remove_result = cmd.run(cmd_remove)
if remove_result['status'] != 0:
logger.error('Error Code %s : %s Cmd: %s' % (remove_result['status'], remove_result['output'], cmd_remove))
#在redis标注 任务未执行
redis.set(task_key, 0)
#如果task_key=2, 则标示该条任务已经运行成功
elif redis.get(task_key) == "2":
datas['status'] = 3
#该条任务正在运行中
else:
return True
'''
执行完毕，模拟从队列中清除任务:0
'''
datas['in_queue'] = 0
update_result = mysql.update('dw2dm_log', ' id = %(id)d' % where, **datas)
# 如果更新失败，则再调用一次，如果还是失败，则等待自动修复机制，但这种概率很小了
if update_result != 1:
mysql.update('dw2dm_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
return True
except Exception as exc:
logger.error('dw2dm_log error: %s' % exc)
redis.set(task_key_exc, 0)
mysql.rollback()
datas = {'in_queue': 0, 'status': 0}
mysql.update('dw2dm_log', ' id = %(id)d' % where, **datas)
mysql.commit()
mysql.close()
raise self.retry(exc=exc, countdown=60)
#coding=utf-8 """ auth: suguoxin mail: [email protected] create_time: 2016-01-01 11:20:00 used: dm2report 任务执行 last_update:2016-04-28 14:48:00 """ import datetime import sys 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))
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)
#coding=utf-8 """ auth: suguoxin mail: [email protected] create_time: 2015-11-16 15:28:00 used: 执行file2mysql任务 last_update: 2016-04-28 14:57:00 """ import sys import datetime from file2mysql.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') #数据时间点(每五分钟)，格式如：0005、2400 log_time = now_time.strftime("%H%M") 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 " \
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"