def __exec_step09_task(self, target_classify):
try:
daily_start_date = get_current_time()
exec_temp_api_bill_sql_by_target(target_classify=target_classify,
is_log=False)
process_status = 'sucess'
daily_end_date = get_current_time()
step_number = '9'
operate_desc = f'成功执行聚合临时表API表的目标分类为{target_classify}的SQL'
orgin_source = 'kudu分析表/落地表'
destin_source = '汇总API中间表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, target_classify,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
except Exception as e:
log.error(f'* 第9步，执行SQL报错')
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '9'
operate_desc = ''
orgin_source = 'kudu分析表/落地表'
destin_source = '汇总API中间表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, target_classify,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
def __exec_step07_task(self, records, cost_project):
daily_start_date = get_current_time()
for record in records:
unusual_shell = str(record[0])
isalgorithm = str(record[1])
unusual_id = str(record[2])
try:
# time.sleep(1)
if isalgorithm == '1':
### 执行 SQL ###
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=unusual_shell)
log.info(
f'* 第7步，成功执行 cost_project为 {cost_project}, unusual_id为 {unusual_id} 的SQL'
)
elif isalgorithm == '2':
### 执行算法 python 脚本 ###
exec(unusual_shell, globals())
log.info(
f'* 第7步，成功执行 cost_project为 {cost_project}, unusual_id为 {unusual_id} 的Python Shell'
)
process_status = 'sucess'
daily_end_date = get_current_time()
step_number = '7'
operate_desc = f'成功执行目标分类为 {cost_project} 的SQL'
orgin_source = 'kudu分析表'
destin_source = 'kudu落地表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, cost_project,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
except Exception as e:
daily_source = 'SQL' if isalgorithm == '1' else 'Python Shell'
log.error(
f'* 执行第7步，目标分类为 {cost_project} ,unusual_id为 {unusual_id} 的 {daily_source} 报错'
)
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '7'
operate_desc = str(e)
orgin_source = 'kudu分析表'
destin_source = 'kudu落地表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, cost_project,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
def execute_py_shell(unusual_shell, unusual_id, mode='activate'):
"""
执行检查点的 python shell 算法
:param unusual_shell:
:return:
"""
try:
# eval("print(1+2)")
# exec('1/0')
#log.info(unusual_shell)
#exec("print('执行算法 shell 开始')")
log.info('执行算法 shell 开始')
daily_start_date = get_current_time()
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point=unusual_id,
daily_source='python shell',
operate_desc=f'正在执行检查点{unusual_id}的Python Shell',
unusual_infor='',
task_status='doing',
daily_type='稽查点')
exec(unusual_shell, globals())
#exec("print('执行算法 shell 结束')")
log.info('执行算法 shell 结束')
daily_end_date = get_current_time()
operate_desc = f'成功执行检查点{unusual_id} 的Python Shell'
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except BaseException as e:
#log.info('***1111 execute_py_shell throw BaseException ---')
# print(e)
error_info = str(e)
log.info(error_info)
traceback.print_exc()
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(error_info)
except SyntaxError as e2:
log.info('***2222 execute_py_shell throw Exception ---')
print(e2)
def __exec_step08_task(self, records, target_classify):
daily_start_date = get_current_time()
try:
for record in records:
sql = str(record[0])
order_number = str(record[1])
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=sql)
log.info(
f'* 第8步，成功执行序号为 {order_number} ,target_classify为 {target_classify} 的SQL'
)
process_status = 'sucess'
daily_end_date = get_current_time()
step_number = '8'
operate_desc = f'成功执行绩效接口API表的目标分类为{target_classify}的SQL'
orgin_source = 'kudu分析表/落地表'
destin_source = '绩效API中间表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, target_classify,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
except Exception as e:
log.error(
f'* 执行第8步，序号为 {order_number} 的SQL报错,target_classify为 {target_classify} 的SQL'
)
# print(sql)
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '8'
operate_desc = str(e)
orgin_source = 'kudu分析表/落地表'
destin_source = '绩效API中间表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, target_classify,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
def exec_temp_performance_bill(performance_ids, is_log=True):
try:
daily_start_date = get_current_time()
if is_log:
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point='',
daily_source='sql',
operate_desc=f'doing',
unusual_infor='',
task_status='doing',
daily_type='绩效')
records = query_temp_performance_bill(performance_ids)
# print(len(records), records)
for idx, record in enumerate(records):
performance_sql = record[0]
# print(performance_sql)
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=performance_sql)
if is_log:
operate_desc = f'成功执行绩效表中的SQL'
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except Exception as e:
print(e)
if is_log:
error_info = str(e)
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(e)
def __exec_step06_task(self, records, target_classify):
try:
daily_start_date = get_current_time()
for idx, record in enumerate(records):
sql = str(record[0])
receipt_id = str(record[1])
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=sql)
log.info(
f'* 第6步，成功执行 target_classify为 {target_classify},receipt_id为 {receipt_id} 的SQL'
)
process_status = 'sucess'
daily_end_date = get_current_time()
step_number = '6'
operate_desc = f'成功执行目标分类为{target_classify}的SQL'
orgin_source = '发票信息hive表'
destin_source = 'kudu分析表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, target_classify,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
except Exception as e:
log.error(f'* 执行第6步，目标分类为{target_classify} ，序号为{receipt_id}的SQL报错')
log.info(sql)
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '6'
operate_desc = str(e)
orgin_source = '发票信息hive表'
destin_source = 'kudu分析表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, target_classify,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
def hello():
result = {'report_service_status': HTTPStatus.OK}
result['version'] = '1.0'
result['time'] = get_current_time()
status = HTTPStatus.INTERNAL_SERVER_ERROR if HTTPStatus.INTERNAL_SERVER_ERROR in result.values(
) else HTTPStatus.OK
#log.info("*********** hello ***************")
#print('time => ', get_current_time())
return jsonify(result), status
def check_execute_step05(self):
"""
判断流程表中已经执行了第5步
:return:
"""
try:
columns_ls = [
'process_id', 'process_status', 'daily_start_date',
'daily_end_date', 'step_number', 'operate_desc',
'orgin_source', 'destin_source', 'importdate'
]
columns_str = ",".join(columns_ls)
t = get_current_time()
data = t.split(' ')
year_month_day = str(data[0]).replace('-', '')
# year_month_day = '20220118'
sel_sql = f"""
select {columns_str} FROM 01_datamart_layer_007_h_cw_df.finance_data_process WHERE ( from_unixtime(unix_timestamp(to_date(importdate),'yyyy-MM-dd'),'yyyyMMdd') = '{year_month_day}' OR importdate = '{year_month_day}' ) AND
process_status = 'sucess' ORDER BY step_number ASC
"""
log.info(sel_sql)
records = prod_execute_sql(conn_type=CONN_TYPE,
sqltype='select',
sql=sel_sql)
is_execute_step05 = False
is_excute_step6789 = False
if len(records) > 0:
for record in records:
# print(record)
step_number = str(record[4])
if step_number == '5':
is_execute_step05 = True
if step_number in ['6', '7', '8', '9']:
is_excute_step6789 = True
else:
log.info('*** 没有查询数据 ***')
# 执行了前5步，但是没有执行过 6,7,8,9步，就开始执行任务
if is_execute_step05 and not is_excute_step6789:
self.task()
else:
log.info('*** 不执行第6,7,8,9 步的任务 ***')
except Exception as e:
print(e)
def execute_kudu_sql(unusual_shell, unusual_id):
try:
daily_start_date = get_current_time()
#print('*** begin execute_kudu_sql ')
#log.info(unusual_shell)
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point=unusual_id,
daily_source='sql',
operate_desc=f'正在执行检查点{unusual_id}的SQL',
unusual_infor='',
task_status='doing',
daily_type='稽查点')
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=unusual_shell)
daily_end_date = get_current_time()
operate_desc = f'成功执行检查点{unusual_id}的SQL'
#print('*** end execute_kudu_sql ***')
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except Exception as e:
print(e)
error_info = str(e)
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(error_info)
def show_time(self, text='task'):
# t = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
t = get_current_time()
data = t.split(' ')
hour_minute = data[1].split(':')
hour = hour_minute[0]
minute = hour_minute[1]
# print('hour_minute=> ', hour_minute)
# print('hour=> ', hour)
# print('minute=> ', minute)
log.info('*' * 30)
log.info('*** {} ---> {}'.format(text, t))
log.info('*' * 30)
print()
def exec_temp_api_bill_sql_by_target(target_classify, is_log=True):
"""
执行临时表API表,
:param target_classify : 目标分类，主要包括：差旅费、会议费、办公费、车辆使用费
:return:
"""
try:
log.info(
f'*** exec_temp_api_bill_sql_by_target, 执行 {target_classify} 的绩效SQL ***'
)
sql = f"""
select tem_api_id,target_classify,api_sql from 01_datamart_layer_007_h_cw_df.temp_api_bill
where target_classify="{target_classify}" order by order_number asc
"""
temp_api_sql_records = prod_execute_sql(conn_type=CONN_TYPE,
sqltype='select',
sql=sql)
#log.info(f'需要执行 {len(temp_api_sql_records)} 条SQL')
if is_log:
daily_start_date = get_current_time()
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point='',
daily_source='sql',
operate_desc=f'正在执行临时表API中类型为{target_classify}的SQL',
unusual_infor='',
task_status='doing',
daily_type='数据处理')
for idx, record in enumerate(temp_api_sql_records):
tem_api_id = str(record[0])
api_sql = str(record[2])
# log.info(api_sql)
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=api_sql)
log.info(
f'target_classify={target_classify},执行成功tem_api_id为 {tem_api_id} 的临时表的SQL,共有{len(temp_api_sql_records)}条SQL'
)
if is_log:
operate_desc = f'成功执行临时表API中类型为{target_classify}的SQL'
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except Exception as e:
log.error(
f'执行失败，target_classify={target_classify},tem_api_id为 {tem_api_id} 的临时表的SQL '
)
#print(e)
if is_log:
error_info = str(e)
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(e)
def exec_step07(self):
"""
执行第7步
7、稽查点sql将数据写到kudu落地表（脚本）
:return:
"""
travel_fee = '差旅费'
meeting_fee = '会议费'
office_fee = '办公费'
car_fee = '车辆使用费'
daily_start_date = get_current_time()
try:
travel_records = query_finance_unusual(cost_project=travel_fee)
except Exception as e:
log.error(f'* 执行第7步，查询目标分类为 {travel_fee} 的SQL报错')
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '7'
operate_desc = str(e)
orgin_source = 'kudu分析表'
destin_source = 'kudu落地表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, travel_fee,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
try:
meeting_records = query_finance_unusual(cost_project=meeting_fee)
except Exception as e:
log.error(f'* 执行第7步，查询目标分类为 {travel_fee} 的SQL报错')
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '7'
operate_desc = str(e)
orgin_source = 'kudu分析表'
destin_source = 'kudu落地表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, travel_fee,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
try:
office_records = query_finance_unusual(cost_project=office_fee)
except Exception as e:
log.error(f'* 执行第7步，查询目标分类为 {travel_fee} 的SQL报错')
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '7'
operate_desc = str(e)
orgin_source = 'kudu分析表'
destin_source = 'kudu落地表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, travel_fee,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
try:
car_records = query_finance_unusual(cost_project=car_fee)
except Exception as e:
log.error(f'* 执行第7步，查询目标分类为 {travel_fee} 的SQL报错')
print(e)
process_status = 'false'
daily_end_date = get_current_time()
step_number = '7'
operate_desc = str(e)
orgin_source = 'kudu分析表'
destin_source = 'kudu落地表'
importdate = get_yyyymmdd_date()
insert_finance_data_process(process_status, travel_fee,
daily_start_date, daily_end_date,
step_number, operate_desc,
orgin_source, destin_source,
importdate)
threadPool = ThreadPoolExecutor(max_workers=4,
thread_name_prefix="thr")
all_task = []
task1 = threadPool.submit(self.__exec_step07_task, travel_records,
travel_fee)
task2 = threadPool.submit(self.__exec_step07_task, meeting_records,
meeting_fee)
task3 = threadPool.submit(self.__exec_step07_task, office_records,
office_fee)
task4 = threadPool.submit(self.__exec_step07_task, car_records,
car_fee)
all_task.append(task1)
all_task.append(task2)
all_task.append(task3)
all_task.append(task4)
wait(all_task, return_when=ALL_COMPLETED)
threadPool.shutdown(wait=True)
