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)