class Calendar: def __init__(self, country): self.country = country self._dbmgr = DBMgr(db=DB_NAME) self.df = self.read_calendar() def read_calendar(self): sql = "SELECT * FROM `{}`".format(self.country) args = {} status, row, result = self._dbmgr.query(sql, args, fetch='all') df = pd.DataFrame(result) df = df.drop(columns=['index']) return df
class FactorAnalysisTask: def __init__(self, task_list): self._task_list = task_list self._cfg = Config() self._dbmgr = DBMgr() self._general = General() # 將task_list中每一筆子任務的細節抓出來 self.task_list_detail = self._get_task_list_detail() self.factor_list = self._get_factor_list() def _get_task_list_detail(self): sql = " SELECT * \ FROM `task_status` \ WHERE " args = {} for task_status in self._task_list: label = 'task_status_id_' + str(task_status) if task_status == self._task_list[-1]: sql = sql + "`task_status_id`=%({})s".format(label) else: sql = sql + "`task_status_id`=%({})s OR ".format(label) args[label] = str(task_status) status, row, data = self._dbmgr.query(sql, args, fetch='all') return data if data else {} def _get_factor_list(self): factor_list = [] # 找出不重複的因子 for i, task_detail in enumerate(self.task_list_detail): self.task_list_detail[i]['factor'] = self._general.string_to_list( task_detail['factor']) for factor in task_detail['factor']: if factor not in factor_list: factor_list.append(factor) return factor_list
class HostMsgHandler: def __init__(self): self._cfg = Config() self._dbmgr = DBMgr() self._server_name = getpass.getuser() # 伺服器使用者名稱 self._ip = socket.gethostbyname(socket.gethostname()) self._host_IP = self._cfg.get_value('IP', 'host_IP') self.client_ID = self._server_name + "_" + self._ip self._mqtt_account = self._cfg.get_value('MQTT', 'account') self._mqtt_password = self._cfg.get_value('MQTT', 'password') def active_mqtt(self): mqtt_client = self.client_ID + " StatusRespond and FinishTask" # 設定節點名稱 client = mqtt.Client(client_id=mqtt_client) client.on_connect = self._on_connect client.on_message = self._on_message client.username_pw_set(self._mqtt_account, self._mqtt_password) client.connect(self._host_IP, 1883) # 開始連線 執行設定的動作和處理重新連線問題 client.loop_start() def _on_connect(self, client, userdata, flag, rc): print("Connected with result code {}".format(str(rc))) # 0: 連接成功 # 1: 協議版本錯誤 # 2: 無效的客戶端標示 # 3: 伺服器無法使用 # 4: 使用者帳號或密碼錯誤 # 5: 未經授權 # 將訂閱主題寫在 on_connect 中,當重新連線時將會重新訂閱 client.subscribe("Analysis/StatusRespond", qos=2) client.subscribe("Analysis/HealthResponse", qos=2) client.subscribe("Analysis/FinishTask", qos=2) def _on_message(self, client, userdata, msg): if msg.topic == "Analysis/StatusRespond": self._handle_status_update(client, userdata, msg) elif msg.topic == "Analysis/HealthResponse": self._handle_health_update(client, userdata, msg) elif msg.topic == "Analysis/FinishTask": self._handle_finish_task(client, userdata, msg) # (Publish) 發送節點狀態確認訊息 def publish_status_check(self): mqtt_client = self.client_ID + " StatusCheck" # 設定節點名稱 # 送出狀態確認訊息, payload = {"message": "StatusCheck"} payload = json.dumps(payload) publish.single(qos=2, keepalive=60, payload=payload, client_id=mqtt_client, topic="Analysis/StatusCheck", hostname=self._host_IP, auth={ 'username': self._mqtt_account, 'password': self._mqtt_password }) print("[HostMsgHandler] Host Status Check.....") # (CallBack) 處理狀態確認任務完成訊息 # input: client : 發送訊息的節點ID # userdata : 資料型態 # msg : 訊息內容 def _handle_status_update(self, client, userdata, msg): node_msg = self._phase_mqtt_msg(msg) print("[HostMsgHandler] get {} Status Check Respond".format( node_msg['node_name'])) current_time = str(datetime.now()) sql = "SELECT * FROM `node` WHERE name = %(name)s" args = {"name": str(node_msg['node_name'])} status, row, result = self._dbmgr.query(sql, args) # 新增節點 if row == 0: sql = " INSERT INTO `node` \ VALUES ('0', %(name)s, %(cpu_status)s, %(core_num)s, %(health)s, %(health_time)s)" args = { "name": str(node_msg['node_name']), "cpu_status": str(node_msg['node_cpu_status']), "core_num": str(node_msg['node_core_number']), "health": '0', "health_time": current_time } status, row, result = self._dbmgr.insert(sql, args) print("[HostMsgHandler] add a new node: {}".format( node_msg['node_name'])) # 更新節點 else: sql = " UPDATE `node` \ SET `cpu_status`=%(cpu_status)s, `core_num`=%(core_num)s, `health`=%(health)s, \ `health_time`=%(health_time)s \ WHERE `name`=%(name)s" args = { "name": str(node_msg['node_name']), "cpu_status": str(node_msg['node_cpu_status']), "core_num": str(node_msg['node_core_number']), "health": '0', "health_time": current_time } status, row, result = self._dbmgr.update(sql, args) print("[HostMsgHandler] update node: {}".format( node_msg['node_name'])) # (Publish) 發送節點健康狀態確認訊息 def publish_health_check(self): mqtt_client = self.client_ID + " HealthCheck" # 設定節點名稱 # 送出狀態確認訊息, payload = {"message": "HealthCheck"} payload = json.dumps(payload) publish.single(qos=2, keepalive=60, payload=payload, client_id=mqtt_client, topic="Analysis/HealthCheck", hostname=self._host_IP, auth={ 'username': self._mqtt_account, 'password': self._mqtt_password }) print("[HostMsgHandler] Host Health Check.....") # (CallBack) 處理健康狀態確認任務完成訊息 # input: client : 發送訊息的節點ID # userdata : 資料型態 # msg : 訊息內容 def _handle_health_update(self, client, userdata, msg): node_msg = self._phase_mqtt_msg(msg) current_time = str(datetime.now()) sql = "SELECT * FROM `node` WHERE name = %(name)s" args = {"name": str(node_msg['node_name'])} status, row, result = self._dbmgr.query(sql, args) # 新增節點 if row == 0: sql = " INSERT INTO `node` \ VALUES ('0', %(name)s, %(cpu_status)s, %(core_num)s, %(health)s, %(health_time)s)" args = { "name": str(node_msg['node_name']), "cpu_status": str(node_msg['node_cpu_status']), "core_num": str(node_msg['node_core_number']), "health": '1', "health_time": current_time } status, row, result = self._dbmgr.insert(sql, args) # 更新節點 else: sql = " UPDATE `node` \ SET `cpu_status`=%(cpu_status)s, `core_num`=%(core_num)s, `health`=%(health)s, \ `health_time`=%(health_time)s \ WHERE `name`=%(name)s" args = { "name": str(node_msg['node_name']), "cpu_status": str(node_msg['node_cpu_status']), "core_num": str(node_msg['node_core_number']), "health": '1', "health_time": current_time } status, row, result = self._dbmgr.update(sql, args) def publish_processing_task(self, node_name, task_id, task_list): mqtt_client = self.client_ID + "FactorAnalysisTaskPublish" # 設定節點名稱 payload = { 'owner': node_name, # 負責任務之節點ID 'task_list': task_list, # 任務細節編號 } payload = json.dumps(payload) publish.single(qos=2, keepalive=60, payload=payload, client_id=mqtt_client, topic="Analysis/FactorAnalysisTask", hostname=self._host_IP, auth={ 'username': self._mqtt_account, 'password': self._mqtt_password }) print("[HostMsgHandler] send Task...", "Node: ", node_name, " taskID: ", task_id, " task_list:", task_list) def _handle_finish_task(self, client, userdata, msg): # status # 0 - undo # 1 - success # 2 - error # MQTT CallBack參數取出 node_msg = self._phase_mqtt_msg(msg) current_time = str(datetime.now()) sql = " UPDATE `task_status` \ SET `finish_time`=%(finish_time)s, `owner`=%(owner)s, `status`=%(status)s \ WHERE `task_status_id`=%(task_status_id)s" args = { "finish_time": current_time, "owner": str(node_msg['node_name']), "status": str(node_msg['status']), "task_status_id": str(node_msg['task_status_id']), } status, row, result = self._dbmgr.update(sql, args) print("[HostMsgHandler] {} Node is Finsh {} has been updated to DB". format(node_msg['node_name'], node_msg['task_status_id'])) def _phase_mqtt_msg(self, msg): node_msg = str(msg.payload, encoding="utf-8") node_msg = json.loads(node_msg) return node_msg
def __init__(self): self._cfg = Config() self._dbmgr = DBMgr()
class NodeHandler: def __init__(self): self._cfg = Config() self._dbmgr = DBMgr() def clean_all_node_data(self): # TRUNCATE 移除資料表上的資料列 sql = "TRUNCATE `node`" args = {} status, row, status = self._dbmgr.delete(sql, args) def check_node_status(self): HostMsgHandler().publish_status_check() time_out = int(self._cfg.get_value('time', 'status_check')) time.sleep(time_out) def publish_node_health_check(self): HostMsgHandler().publish_health_check() time_out = int(self._cfg.get_value('time', 'status_check')) time.sleep(time_out) def get_node_status(self): sql = "SELECT * FROM `node` ORDER BY `cpu_status`" args = {} status, row, result = self._dbmgr.query(sql, args) return result def check_node_health(self): status = False nodes = self.get_node_status() for node in nodes: current_time = datetime.now() nodes_health_time = datetime.strptime(node['health_time'], '%Y-%m-%d %H:%M:%S.%f') # 判定超過健康時間 if float(str( (current_time - nodes_health_time).total_seconds())) > float( self._cfg.get_value('time', 'node_health_time')): # 更改節點為死亡 self.update_node_status(node['name'], 2) # 還是有活著的 Node elif node['health'] != 2: status = True return status def check_all_node_finish(self): status = True nodes = self.get_node_status() for node in nodes: if node['health'] == 1: status = False return status def update_node_status(self, node, health): # 發完一輪後檢查節點是否健康 (health) # 0:無任務 # 1:執行中 # 2:節點已死亡 sql = "UPDATE `node` SET `health` = %(health)s WHERE `name`=%(owner)s" args = {"owner": str(node), "health": str(health)} status, row, result = self._dbmgr.update(sql, args) def distribute_batch_task(self, task_num, node_num): # 將任務平分給各個節點 未整除的部分都給最後一個節點 batch_task_num = task_num // node_num # 整除的時候 = 0 over_task = task_num % node_num batch_task_list = [] for i in range(node_num): if i == 0: # 第一個 batch_task_list.append([0, batch_task_num]) elif i + 1 == node_num: # 最後一個 batch_task_list.append( [batch_task_num * i, batch_task_num * (i + 1) + over_task]) else: batch_task_list.append( [batch_task_num * i, batch_task_num * (i + 1)]) return batch_task_list
def __init__(self): self.dbmgr = DBMgr(db=DB_NAME)
def __init__(self): self._dbmgr = DBMgr() self._general = General()
class FactorAnalysisHandler: def __init__(self): self._dbmgr = DBMgr() self._general = General() def add_task_to_db(self, request): current_time = str(datetime.now()) sql = "INSERT INTO `task` \ VALUES ('0', %(factor_list)s, %(strategy_list)s, %(window_list)s, %(method_list)s, \ %(group_list)s, %(position_list)s, %(begin_time)s)" args = { "factor_list": self._general.factor_list_to_string(request['factor_list']), "strategy_list": self._general.list_to_string(request['strategy_list']), "window_list": self._general.list_to_string(request['window_list']), "method_list": self._general.list_to_string(request['method_list']), "group_list": self._general.list_to_string(request['group_list']), "position_list": self._general.list_to_string(request['position_list']), "begin_time": str(current_time), } status, row, result = self._dbmgr.insert(sql, args) task_id = self.get_last_task_id(current_time) return task_id def add_task_detail_to_db(self, task_id, combination): task_result = self.get_task_by_id(task_id) args = [] for task_list in combination: factor = task_list[0] strategy = task_list[1] window = task_list[2] method = task_list[3] group = task_list[4] position = task_list[5] args.append({ "task_id": str(task_id), "factor": self._general.list_to_string(factor), "strategy": str(strategy), "window": str(window), "method": str(method), "group": str(group), "position": str(position), "finish_time": str(''), "owner": str(''), "status": str('0'), }) sql = " INSERT INTO `task_status` \ VALUES ('0', %(task_id)s, %(factor)s, %(strategy)s, %(window)s, %(method)s, \ %(group)s, %(position)s, %(finish_time)s, %(owner)s, %(status)s)" status, row, result = self._dbmgr.insert(sql, args, multiple=True) def check_unfinished_task(self): sql = "SELECT `task_status_id`, `task_id` FROM `task_status` WHERE status=%(status)s" args = {"status": str('0')} status, row, result = self._dbmgr.query(sql, args) task_list = [] # 表示無未完成任務 if len(result) == 0: status = False task_id = -1 # 有未完成任務 else: status = True task_id = result[0]['task_id'] for sub_task in result: task_list.append(sub_task['task_status_id']) return status, task_id, task_list def check_exist_task(self, request): sql = " SELECT `task_id` \ FROM `task` \ WHERE `factor_list` = %(factor_list)s AND \ `strategy_list` = %(strategy_list)s AND \ `window_list` = %(window_list)s AND \ `method_list` = %(method_list)s AND \ `group_list` = %(group_list)s AND \ `position_list` = %(position_list)s" args = { "factor_list": self._general.factor_list_to_string(request['factor_list']), "strategy_list": self._general.list_to_string(request['strategy_list']), "window_list": self._general.list_to_string(request['window_list']), "method_list": self._general.list_to_string(request['method_list']), "group_list": self._general.list_to_string(request['group_list']), "position_list": self._general.list_to_string(request['position_list']), } status, row, data = self._dbmgr.query(sql, args, fetch='one') if status and data and len(data) == 1: return True, data['task_id'] else: return False, -1 def get_request(self, task_id): task_info = {} plateau_info = {} # 取回任務清單 sql = "SELECT * FROM `task` WHERE `task_id`=%(task_id)s" args = {"task_id": str(task_id)} status, row, task_info = self._dbmgr.query(sql, args, fetch='one') # 該任務不存在 if row == 0: print('[FactorAnalysisHandler] 該任務編號 {} 不存在!'.format(task_id)) return { 'task_id': task_id, 'factor_list': [], 'strategy_list': [], 'window_list': [], 'method_list': [], 'group_list': [], 'position_list': [], } else: return { 'task_id': task_id, 'factor_list': self._general.factor_string_to_list(task_info['factor_list']), 'strategy_list': self._general.string_to_list(task_info['strategy_list']), 'window_list': self._general.string_to_list(task_info['window_list']), 'method_list': self._general.string_to_list(task_info['method_list']), 'group_list': self._general.string_to_list(task_info['group_list']), 'position_list': self._general.string_to_list(task_info['position_list']), } def get_task_by_id(self, task_id): sql = "SELECT * FROM `task` WHERE task_id = %(task_id)s" args = {"task_id": str(task_id)} status, row, result = self._dbmgr.query(sql, args, fetch='one') return result def get_last_task_id(self, curr_time): sql = "SELECT * FROM `task` WHERE begin_time = %(begin_time)s" args = {"begin_time": str(curr_time)} status, row, result = self._dbmgr.query(sql, args, fetch='one') return result['task_id'] def get_all_task_list_by_status(self, task_id, status=0): sql = "SELECT `task_status_id` FROM `task_status` WHERE status=%(status)s and task_id=%(task_id)s" args = {"status": str(status), "task_id": str(task_id)} sql_status, row, result = self._dbmgr.query(sql, args) task_list = [] for sub_task in result: task_list.append(sub_task['task_status_id']) return task_list def get_all_task_list(self, task_id): sql = "SELECT `task_status_id` FROM `task_status` WHERE task_id=%(task_id)s" args = {"task_id": str(task_id)} status, row, result = self._dbmgr.query(sql, args) task_list = [] for sub_task in result: task_list.append(sub_task['task_status_id']) return task_list def check_node_have_task(self, node): # 檢查這個 node 目前是否有未完成的任務 sql = " SELECT * \ FROM `task_status` \ WHERE `owner` = %(node)s AND `status` = 0" args = {'node': node} status, row, result = self._dbmgr.query(sql, args) if status and row == 0: return False else: return True def update_task_owner(self, owner, task_list): for task in task_list: sql = " UPDATE `task_status` \ SET `owner`=%(owner)s \ WHERE `task_status_id` = %(task_status_id)s" args = {"owner": str(owner), "task_status_id": str(task)} status, row, result = self._dbmgr.update(sql, args)
class NodeMsgHandler: def __init__(self): self._cfg = Config() self._dbmgr = DBMgr() self._general = General() # 基本參數 (伺服器設定值) self._processes = os.cpu_count() # 伺服器CPU核心數 self._server_name = getpass.getuser() # 伺服器使用者名稱 self._IP = socket.gethostbyname(socket.gethostname()) self._host_IP = self._cfg.get_value('IP', 'host_IP') self.client_ID = self._server_name + "_" + self._IP self._mqtt_account = self._cfg.get_value('MQTT', 'account') self._mqtt_password = self._cfg.get_value('MQTT', 'password') def active_mqtt(self): mqtt_client = self.client_ID + " ProcessTask" # 設定節點名稱 client = mqtt.Client(client_id=mqtt_client) client.on_connect = self._on_connect client.on_message = self._on_message client.username_pw_set(self._mqtt_account, self._mqtt_password) client.connect(self._host_IP, 1883) # 開始連線 執行設定的動作和處理重新連線問題 client.loop_forever() def _on_connect(self, client, userdata, flag, rc): print("Connected with result code {}".format(str(rc))) # 0: 連接成功 # 1: 協議版本錯誤 # 2: 無效的客戶端標示 # 3: 伺服器無法使用 # 4: 使用者帳號或密碼錯誤 # 5: 未經授權 # 將訂閱主題寫在 on_connect 中,當重新連線時將會重新訂閱 client.subscribe("Analysis/FactorAnalysisTask", qos=2) client.subscribe("Analysis/StatusCheck", qos=2) client.subscribe("Analysis/HealthCheck", qos=2) def _on_message(self, client, userdata, msg): if msg.topic == "Analysis/FactorAnalysisTask": self._handle_factor_analysis_task(client, userdata, msg) elif msg.topic == "Analysis/StatusCheck": self._status_receive(client, userdata, msg) elif msg.topic == "Analysis/HealthCheck": self._health_receive(client, userdata, msg) # (CallBack) 處理狀態確認訊息 # input: client : 發送訊息的節點ID # userdata : 資料型態 # msg : 訊息內容 def _status_receive(self, client, userdata, msg): print("Receive Status Check and Respond...") self._publish_status_respond() # (Publish) 回傳節點系統狀態 def _publish_status_respond(self): mqtt_client = self.client_ID + " StatusRespond" # 設定節點名稱 # 轉換Json格式 payload = { 'node_name': self.client_ID, 'node_core_number': self._processes, 'node_cpu_status': psutil.cpu_percent() } payload = json.dumps(payload) # 送出訊息 publish.single(qos=2, keepalive=60, payload=payload, topic="Analysis/StatusRespond", client_id=mqtt_client, hostname=self._host_IP, auth={'username': self._mqtt_account, 'password': self._mqtt_password} ) print('%s publish status respond' % self.client_ID) # (CallBack) 處理健康狀態確認訊息 # input: client : 發送訊息的節點ID # userdata : 資料型態 # msg : 訊息內容 def _health_receive(self, client, userdata, msg): print("Receive Health Check and Respond...") self._publish_health_respond() # (Publish) 回傳節點系統狀態 def _publish_health_respond(self): mqtt_client = self.client_ID + " HealthResponse" # 設定節點名稱 # 轉換Json格式 payload = { 'node_name': self.client_ID, 'node_core_number': self._processes, 'node_cpu_status': psutil.cpu_percent(), } payload = json.dumps(payload) # 送出訊息 publish.single(qos=2, keepalive=60, payload=payload, topic="Analysis/HealthResponse", client_id=mqtt_client, hostname=self._host_IP, auth={'username': self._mqtt_account, 'password': self._mqtt_password} ) print('%s publish health request' % self.client_ID) # (CallBack) 處理接收到之任務 # input: client : 發送訊息的節點ID # userdata : 資料型態 # msg : 訊息內容 def _handle_factor_analysis_task(self, client, userdata, msg): # MQTT CallBack參數取出 payload = self._phase_mqtt_msg(msg) print("task_message = ", payload) # 收到指派之任務 print('task_message["owner"] = ', payload["owner"]) if payload["owner"] == self.client_ID: print("Processing...", str(payload['task_list'])) factor_analysis_task = FactorAnalysisTask(payload['task_list']) task_list_detail = factor_analysis_task.task_list_detail factor_list = factor_analysis_task.factor_list self._run_factor_analysis(task_list_detail, factor_list) time.sleep(10) # 全部工作完成 更新節點狀態 self._change_node_status('0') def _run_factor_analysis(self, task_list_detail, factor_list): # 預載交易日&因子資料 cal = Calendar('TW') get_factor_start = time.time() fac = Factor(factor_list) get_factor_end = time.time() print("Get factor time: %f second" % (get_factor_end - get_factor_start)) for task_detail in task_list_detail: try: start = time.time() strategy_config = { 'factor': task_detail['factor'], 'strategy': task_detail['strategy'], 'window': task_detail['window'], 'method': task_detail['method'], 'group': task_detail['group'], 'position': task_detail['position'], } factor_str = self._general.factor_to_string(task_detail['factor']) path = self._cfg.get_value('path', 'path_to_portfolio_performance') + factor_str file_name = "{}_{}_{}_{}_{}_{}".format( factor_str, task_detail['strategy'], task_detail['window'], task_detail['method'], task_detail['group'], task_detail['position'] ) file = pathlib.Path("{}/{}.csv".format(path, file_name)) if file.exists(): self._publish_factor_analysis_task_finish(task_detail['task_status_id'], 1) else: my_stra = MyAsset(strategy_config, cal, fac) end = time.time() print("Execution time: %f second" % (end - start)) # status: 0 - undo, 1 - success, 2 - error self._publish_factor_analysis_task_finish(task_detail['task_status_id'], 1) except Exception as e: # status: 0 - undo, 1 - success, 2 - error self._publish_factor_analysis_task_finish(task_detail['task_status_id'], 2) print(e) # (Publish) 回傳節點完成任務 # status # 0 - undo # 1 - success # 2 - error def _publish_factor_analysis_task_finish(self, task_status_id, status): mqtt_client = self.client_ID + " StatusRespond" # 設定節點名稱 payload = { 'node_name': self.client_ID, 'task_status_id': task_status_id, 'status': status } payload = json.dumps(payload) publish.single( qos=2, keepalive=60, payload=payload, topic="Analysis/FinishTask", client_id=mqtt_client, hostname=self._host_IP, auth={ 'username': self._mqtt_account, 'password': self._mqtt_password } ) def _change_node_status(self, health): current_time = str(datetime.now()) sql = " UPDATE `node` \ SET `cpu_status`=%(cpu_status)s, `core_num`=%(core_num)s, \ `health`=%(health)s, `health_time`=%(health_time)s \ WHERE `name`=%(name)s" args = { "name": str(self.client_ID), "cpu_status": str(psutil.cpu_percent()), "core_num": str(self._processes), "health": str(health), "health_time": current_time } status, row, result = self._dbmgr.update(sql, args) def _phase_mqtt_msg(self, msg): node_msg = str(msg.payload, encoding="utf-8") node_msg = json.loads(node_msg) return node_msg
def __init__(self, country): self.country = country self._dbmgr = DBMgr(db=DB_NAME) self.df = self.read_calendar()