def des_save(xml_file): """ 将描述文件内容存入hive表 """ # try: xml_root = Et.parse(xml_file).find('result') taskid = xml_root.find('taskid').text mfid = xml_root.find('mfid').text areacode = mfid[0:6] equid = xml_root.find('equid').text mfname, equname, equmodel, feature_list = ws.query_device(mfid, equid) feature = xml_root.find('feature').text paramxml_str, t_start_time, t_stop_time, status = ws.query_tasks(taskid) appid = xml_root.find('appid').text userid = xml_root.find('userid').text dataguid = os.path.basename(xml_file).split('.')[0] res = '<?xml version="1.0" encoding="utf-8"?>' \ '<datamapping>' \ '<mfid>{0}</mfid>' \ '<mfname>{1}</mfname>' \ '<areacode>{2}</areacode>' \ '<equipment>' \ '<equid>{3}</equid>' \ '<equname>{4}</equname>' \ '<equmodel>{5}</equmodel>' \ '{6}' \ '<task>' \ '<taskid>{7}</taskid>' \ '<taskname></taskname>' \ '<tasktype></tasktype>' \ '<starttime>{8}</starttime>' \ '<stoptime>{9}</stoptime>' \ '<appid>{10}</appid>' \ '<feature>{11}</feature>' \ '<userid>{12}</userid>' \ '<participateuser></participateuser>' \ '<taskarea>{13}</taskarea>' \ '<dataguid>{14}</dataguid>' \ '<paramxml>{15}</paramxml>' \ '</task>' \ '</equipment>' \ '</datamapping>'.format(mfid, mfname, areacode, equid, equname, equmodel, feature_list, taskid, t_start_time, t_stop_time, appid, feature, userid, areacode, dataguid, paramxml_str) cursor = hc.get_hive_cursor('172.39.8.60', 'db_data_store') sql = "insert into table des_file values ('{0}','{1}')".format( os.path.basename(xml_file), res) hc.execute_sql_insert(cursor, sql) return True
def file_index(file, file_des, return_type): """ 生成文件索引表内容, 地区返回内容表, 任务返回内容表插入数据库表中 """ des_result = xml_parser(file_des, return_type) file_result = get_file_info(file) if return_type == 'file_index': result = (des_result[0], des_result[1], des_result[6], des_result[2], des_result[5], file_result[0], file_result[1], des_result[3], des_result[4], file_result[3], '/data/ftp/' + os.path.basename(file), file_result[2]) sql = "insert into table file_index values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}','{10}','{11}')".format(*result) elif return_type == 'device_info': result = (des_result[0], des_result[5], des_result[1], des_result[2], des_result[3], des_result[4]) sql = "insert into table deviceinfo values ('{0}','{1}','{2}','{3}','{4}','{5}')".format( *result) sql_filter = 'select * from deviceinfo where mfid="{0}" and equid="{1}"'.format( des_result[5], des_result[2]) elif return_type == 'task_info': result = (des_result[0], des_result[1], des_result[9], des_result[2], des_result[3], des_result[4], des_result[5], des_result[6], des_result[7], des_result[8], des_result[9]) if des_result[9] == 'finished': sql = "insert into table taskinfo partition (status=1) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}', '{10}')".format(*result) else: result = list(result) result[5] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') sql = "insert into table taskinfo partition (status=0) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}', '{10}')".format(*result) sql_filter = 'select * from taskinfo where taskid="{0}" and equid="{1}"'.format( des_result[0], des_result[2]) cursor = hc.get_hive_cursor('172.39.8.60', 'db_data_store') # 去重 if return_type == 'device_info' or return_type == 'task_info': res = hc.execute_sql(cursor, sql_filter) if not res: hc.execute_sql_insert(cursor, sql) else: return 0 else: hc.execute_sql_insert(cursor, sql) return 1
def xml_parser(xml_file, return_type): """ 解析描述文件 """ xml_root = Et.parse(xml_file).find('result') equid = xml_root.find('equid').text mfid = xml_root.find('mfid').text areacode = mfid[0:6] mfname, equname, tmp, tmp = ws.query_device(mfid, equid) dataguid = os.path.basename(xml_file).split('.')[0] taskid = xml_root.find('taskid').text feature = xml_root.find('feature').text userid = xml_root.find('userid').text appid = xml_root.find('appid').text res_list = xml_root.find('list').find('segment') start_freq = float(res_list.find('startfreq').text) stop_freq = float(res_list.find('stopfreq').text) bid_tmp, flag = get_businessid(start_freq / 1000000, stop_freq / 1000000) businessid = bid_tmp[0][0] if isinstance(bid_tmp, list) else bid_tmp if not flag: cursor = hc.get_hive_cursor('172.18.140.8', 'rmdsd') sql = 'insert into table rmbt_service_freqdetail ' \ 'values ("{0}","00000000-0000-0000-0000-000000000000","{1}-{2}Mhz",{1},{2},25.0)'.format( businessid, start_freq/1000000, stop_freq/1000000) hc.execute_sql_insert(cursor, sql) paramxml_str, t_start_time, t_stop_time, status = ws.query_tasks(taskid) return_dict = { 'file_index': (dataguid, taskid, equid, t_start_time, t_stop_time, businessid, mfid), 'task_info': (taskid, feature, equid, t_start_time, t_stop_time, userid, paramxml_str, appid, dataguid, mfid, status), 'device_info': (areacode, mfname, equid, equname, feature, mfid), 'b_info': (mfid, start_freq, stop_freq) } result = return_dict[return_type] return result
def get_businessid(start_freq, stop_freq): """ 通过起始结束频率查询表获取监测业务编号 单位 hz """ cursor = hc.get_hive_cursor('172.18.140.8', 'rmdsd') sql = 'select servicedid from rmbt_service_freqdetail where startfreq=={0} and endfreq = {1}'.format( start_freq, stop_freq) res = hc.execute_sql(cursor, sql) # flag: 表中是否有当前业务编号 flag = True # 若表中无对应数据,生成自定义频段 if not res: res = uuid.uuid1() flag = False #sql = 'insert into table rmbt_service_freqdetail ' \ # 'values ("{0}","00000000-0000-0000-0000-000000000000","{1}-{2}Mhz",{1},{2},25.0)'.format( # res, start_freq, stop_freq) #hc.execute_sql_insert(cursor, sql) return res, flag
def update_tasktime(): """ 更新波尔接口中status不为finished的taskinfo """ cursor = hc.get_hive_cursor('172.39.8.60', 'db_data_store') sql = 'select * from taskinfo where status=0' task_list = hc.execute_sql(cursor, sql) hc.execute_sql_insert(cursor, 'alter table taskinfo drop partition (status=0)') for task in task_list: paramxml, start_time, stop_time, status = ws.query_tasks(task[0]) if status == 'finished': task = list(task) task[4] = start_time task[5] = stop_time sql = "insert into table taskinfo partition (status=1) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}') ".format(*task) hc.execute_sql_insert(cursor, sql) else: sql = "insert into table taskinfo partition (status=0) values ('{0}','{1}','{2}','{3}','{4}'," \ "'{5}','{6}','{7}','{8}','{9}') ".format(*task) hc.execute_sql_insert(cursor, sql)
# coding=utf-8 import hive_connector as hc from math import pow import sys cursor = hc.get_hive_cursor('172.39.8.60', 'spectrum_evaluation') sql = 'select * from ampdict' res = hc.execute_sql(cursor, sql) length = len(res) count = 0 illegal = 0 for i in res: count += 1 for k, v in eval(i[4]).items(): if v/i[3] > 0.03: if (pow(10, (eval(i[5])[k]/10 - 107)/10)) / 1000 > i[-1]: with open('/illegal', 'a') as f: f.write(str(k)+','+'1'+','+i[0]+','+i[2]+','+i[1]) f.write('\n') illegal += 1 sys.stdout.write('\r当前扫描: '+str(count)+' / '+str(length)+'\t违规频点数量: '+str(illegal)) sys.stdout.flush()
def file_resolve(file, mfid, start_freq, stop_freq, file_size_min, data_type): """ 生成信号分选结构及频谱数据按分钟中间值写入文件 """ print('file resolving...') frame_count = 0 fp_data_total = [] auto_total = [] sb_list = [] for frame_tmp in Read(file).header_payload(): if not frame_tmp[1]: continue frame = frame_tmp break # start_freq = frame[1][4]/1000 # stop_freq = frame[1][5]/1000 step = frame[1][7] # 通过起始结束频率查询监测业务编号 bid_tmp, flag = get_businessid(start_freq, stop_freq) businessid = bid_tmp[0][0] if isinstance(bid_tmp, list) else bid_tmp frame_list = [] for i in Read(file).header_payload(): if not i[1]: continue else: frame_list.append(i) # TODO: 待优化 """ 取第一帧时间的分钟数,但是freq_avg中有可能会抛掉第一帧,所以会出现对应不上的情况, """ frame_combined = [] for frame in freq_avg(frame_list, 10): frame_combined.append(frame) # time_tmp = time.localtime(time.mktime(time.strptime(next(Read(file).header_payload())[0][3], '%Y-%m-%d %H:%M:%S.%f'))) time_tmp = time.localtime( time.mktime( time.strptime(frame_combined[0][0][3], '%Y-%m-%d %H:%M:%S.%f'))) time_tmp = time_tmp.tm_min for frame in frame_combined: # break if not frame[1]: continue frame_count += 1 time_str = frame[0][3] time_ts = time.mktime( time.strptime(frame[0][3], '%Y-%m-%d %H:%M:%S.%f')) time_struct = time.localtime(time_ts) if time_struct.tm_min == time_tmp: fp_data = frame[1][-1] start_freq = frame[1][4] / 1000 stop_freq = frame[1][5] / 1000 step = frame[1][7] / 1000 so = CInvoker(fp_data, start_freq, stop_freq, step) sig_count, cf, cfi, cfa, snr, sb = so.signal_detect() if sig_count > 0: # 自动门限 auto = so.auto_threshold() auto_np = np.array(auto) fp_np = np.array(fp_data) auto_total.append(auto_np) fp_data_total.append(fp_np) sb_list = sb sigDetectResult = np.array([cf, cfi, cfa, snr, sb]) # 将信号写入文件 signal_to_csv(mfid, frame[0][3], sig_count, sigDetectResult) else: print('sig_count < 0') continue else: amp_struct_info = amp_info(fp_data_total, auto_total) print('{0} -- 总扫描帧数量 {1}'.format(time_str, frame_count)) sb_ = sb_list[0] fp_data_total = [] auto_total = [] sb_list = [] frame_count = 0 time_tmp = time_struct.tm_min # 幅度值字典写入文件 for amp_struct in amp_struct_info: scan_count = 0 for j in amp_struct.amp_dict.values(): scan_count += j amp_list = amp_struct.amp_dict.keys() amp_max = max(amp_list) amp_min = min(amp_list) with open('/var/dropzone/amp_info.min.tmp', 'a') as f: f.write( '{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}|{16}' .format(businessid, mfid, time_str.split('.')[0], 4, amp_struct.sig_index, start_freq, stop_freq, step, amp_struct.amp_dict, amp_struct.occupancy, scan_count, amp_struct.threshold_avg, file_size_min, data_type, sb_, amp_max, amp_min)) f.write('\n') if not flag: cursor = hc.get_hive_cursor('172.18.140.8', 'rmdsd') sql = 'insert into table rmbt_service_freqdetail ' \ 'values ("{0}","00000000-0000-0000-0000-000000000000","{1}-{2}Mhz",{1},{2},25.0'.format( businessid, start_freq, stop_freq) hc.execute_sql_insert(cursor, sql) try: cursor = hc.get_hive_cursor('172.18.140.8', 'rmdsd') shutil.move('/var/dropzone/amp_info.min.tmp', '/var/dropzone/amp_info.min') shutil.move('/var/dropzone/signal.tmp', '/var/dropzone/signal') hc.execute_sql_insert( cursor, "load data local inpath '/var/dropzone/amp_info.min' into table db_data_store.amp_info_load" ) hc.execute_sql_insert( cursor, "load data local inpath '/var/dropzone/signal' into table analysesystem.signal_load" ) os.remove('/var/dropzone/amp_info.min') os.remove('/var/dropzone/signal') except shutil.Error as msg: print(msg) print('file resolved...')
import numpy as np import hive_connector as hc import datetime import csv file = open('test.csv', 'a') writer = csv.writer(file) index = 0 dtype = [('index', int), ('time_start', datetime.datetime), ('time_stop', datetime.datetime), ('freq', float), ('unknown1', float), ('unknown2', float), ('unknown3', float), ('unknown4', int), ('mfid', np.int64)] cursor = hc.get_hive_cursor('172.39.8.60', 'analysesystem') values = hc.execute_sql( cursor, 'select * from combined_signal order by mfid,freq,firsttime') signal_array = np.array(values, dtype=dtype) res = np.sort(signal_array, order=['mfid', 'freq', 'time_start']) for i in range(len(res) - 1): # 同mfid做比较 first = res[i] second = res[i + 1] if first['mfid'] == second['mfid']: # 判断频率差值是否在指定条件内 if second['freq'] - first['freq'] > 50: index += 1 first[0] = index print(first)