def consumer_offset(topic, time_str): ''' 获取指定topic指定时间的offset数据,下一步用于指定时间回补数据 :param time_str: format is %Y-%m-%d %H:%M:%S :return: ''' if type(time_str) == type(1) or type(time_str) == type(1.0): timestamp_sec = time_str - 60 else: timestamp_sec = time.mktime( time.strptime(time_str, '%Y-%m-%d %H:%M:%S')) - 60 time_begin = time.strftime("%Y-%m-%d %H:%M", time.localtime(timestamp_sec)) + ":00" time_end = time.strftime("%Y-%m-%d %H:%M", time.localtime(timestamp_sec + 60)) + ":00" client = MysqlClient("saas_server") con, cur = client.connection sql_format = "SELECT topic, tm, MAX(offset) FROM saas_server.product_record WHERE topic = '%(topic)s' AND (tm >= '%(time_begin)s' AND tm < '%(time_end)s') GROUP BY topic, tm" sql = sql_format % { "topic": topic, "time_begin": time_begin, "time_end": time_end } cur.execute(sql) for item in cur.fetchall(): topic, tm, offset = item[0], item[1], item[2] client.closeMysql() return offset + 1
def insert_d_log_collector(default_groupid=("JHSAASGroup_1", ), execute_groupid=(6, )): # 获取新增加的appkey sql_appkeys = "SELECT appkey, plat FROM saas_server.d_appkey WHERE enable = 1 AND appkey NOT IN(SELECT DISTINCT(appkey) FROM saas_server.d_log_collector) AND plat IN ('android', 'ios', 'h5')" # 获取汇总的 group_id sql_groupids = "SELECT DISTINCT(group_id) FROM saas_server.d_log_collector" sql_insert_format = "INSERT INTO saas_server.d_log_collector (insert_tm , group_id, client_id, appkey, plat, logpath, enable) VALUES(NOW(), '%(group_id)s', '%(client_id)s', '%(appkey)s', '%(plat)s', '%(logpath)s', 1)" groupids = set() client = MysqlClient("saas_server") con, cur = client.connection cur.execute(sql_groupids) for item in cur.fetchall(): groupid = item[0] groupids.add(groupid) cur.execute(sql_appkeys) for appkey, plat in [item for item in cur.fetchall()]: for groupid in groupids: if groupid in execute_groupid: continue sql_insert = sql_insert_format % { "group_id": groupid, "client_id": '', "appkey": appkey, "plat": plat, "logpath": plat, } print(sql_insert) cur.execute(sql_insert) con.commit() client.closeMysql()
def get_appkeys(self, ty): datatype_list = [] # collectorsaas if ty == "transformsaaslogs": try: from MysqlClient import MysqlClient client = MysqlClient("saas_meta") result = client.getAppkey() datatype_list = [item[1] for item in result if item[2] != "h5"] client.closeMysql() except: import traceback print traceback.print_exc() datatype_list.append( "hbtv") if "hbtv" not in datatype_list else None # collectorsaash5 elif ty == "transformh5": try: from MysqlClient import MysqlClient client = MysqlClient("saas_meta") # result = client.getAppkey() # datatype_list = [item[1] for item in result if item[2] == "h5"] result = client.getAppkey_H5() datatype_list = [item["appkey"] for item in result] client.closeMysql() except: import traceback print traceback.print_exc() # collectorh5 elif ty == "feeling_H5": datatype_list.append("feeling_H5") elif ty == "transformNew": datatype_list += ['guaeng', 'guagua', 'feeling'] return datatype_list
def getAppkey(): datatype_list = [] try: from MysqlClient import MysqlClient client = MysqlClient("saas_meta") result = client.getAppkey_app() datatype_list = [item["appkey"] for item in result] client.closeMysql() except: import traceback print traceback.print_exc() datatype_list.append("hbtv") if "hbtv" not in datatype_list else None return datatype_list
def getAppkey(): datatype_list = [] try: from MysqlClient import MysqlClient client = MysqlClient("saas_meta") # result = client.getAppkey() # datatype_list = [item[1] for item in result if item[2] == "h5"] result = client.getAppkey_H5() datatype_list = [item["appkey"] for item in result] client.closeMysql() except: import traceback print traceback.print_exc() return datatype_list
def producter_record(infos): ''' 解析数据记录入库 :param infos: :return: ''' config = GetConfigure() product_id = config.get("kafka", "product_id") client = MysqlClient("saas_server") con, cur = client.connection sql_format = "INSERT INTO saas_server.product_record (product_id, topic, tm, offset) VALUES (%s, %s, %s, %s)" for key in infos: topic = key values = infos[key] # tm = datetime.datetime.fromtimestamp(values["timestamp"]).strftime("%Y-%m-%d %H:%M:%S") tm = datetime.datetime.fromtimestamp(values["timestamp"]) offset = values["offset"] cur.execute(sql_format, (product_id, topic, tm, offset)) con.commit() client.closeMysql()
def insert_demojr(): client = MysqlClient() con, cur = client.connection earliest_date = (datetime.datetime.now() - datetime.timedelta(days=90)).strftime("%Y-%m-%d") # today_date = (datetime.datetime.now()).strftime("%Y-%m-%d") cur.execute( "drop table if exists jhd_demo.jhd_demojr_ios_charge_overall_custom") con.commit() # cur.execute("create table jhd_demo.jhd_demojr_ios_charge_overall_custom select * from ncf.ncf_360_h5_charge_overall_custom where tm >= '%s'" % earliest_date) cur.execute( "create table jhd_demo.jhd_demojr_ios_charge_overall_custom select " "tm, pub, ver, " "uv+FLOOR(uv * RAND()) uv, " "reg+FLOOR(reg * RAND()) reg, " "charge+FLOOR(charge * RAND()) charge, " "today_reg+FLOOR(today_reg * RAND()) today_reg, " "today_charge+FLOOR(today_charge * RAND()) today_charge, " "first_charge+FLOOR(first_charge * RAND()) first_charge, " "reg_charge+FLOOR(reg_charge * RAND()) reg_charge, " "recharge+FLOOR(recharge * RAND()) recharge, " "charge_7days+FLOOR(charge_7days * RAND()) charge_7days, " "checkfin+FLOOR(checkfin * RAND()) checkfin, " "auth+FLOOR(auth * RAND()) auth, " "cert+FLOOR(cert * RAND()) cert, " "bankcard+FLOOR(bankcard * RAND()) bankcard, " "charge_newer+FLOOR(charge_newer * RAND()) charge_newer, " "dig+FLOOR(dig * RAND()) dig, " "dig_uc+FLOOR(dig_uc * RAND()) dig_uc, " "dig_homepage+FLOOR(dig_homepage * RAND()) dig_homepage, " "dig_jump+FLOOR(dig_jump * RAND()) dig_jump, " "dig_cur+FLOOR(dig_cur * RAND()) dig_cur, " "dig_curpay+FLOOR(dig_curpay * RAND()) dig_curpay, " "uc_invite+FLOOR(uc_invite * RAND()) uc_invite, " "check_ucfinc+FLOOR(check_ucfinc * RAND()) check_ucfinc, " "paypage+FLOOR(paypage * RAND()) paypage, " "charge_success+FLOOR(charge_success * RAND()) charge_success " "from ncf.ncf_360_h5_charge_overall_custom where tm >= '%s'" % earliest_date) client.closeMysql()
def consumer_record(infos): ''' 消费数据记录入库 :param infos: :return: ''' config = GetConfigure() group_id = config.get("kafka", "group_id") client = MysqlClient("saas_server") con, cur = client.connection # sql_format = "REPLACE INTO saas_server.custom_record(group_id, topic, tm, record_num, update_tm, update_num) VALUES (%s, %s, %s, %s, %s, %s)" sql_format = "INSERT INTO saas_server.custom_record(group_id, topic, tm, record_num, update_tm) VALUES (%s, %s, %s, %s, %s)" for topic in infos: for timestamp in infos[topic]: tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(timestamp)) update_tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) record_num = infos[topic][timestamp]["record_num"] cur.execute(sql_format, (group_id, topic, tm, record_num, update_tm)) con.commit() client.closeMysql()
def update_d_appkey(): client = MysqlClient("saas_server") con, cur = client.connection # sql_1 = '''SELECT a.appkey, a.plat, b.cdkey, a.enable*b.enable FROM (SELECT * FROM saas_meta.d_app) a LEFT JOIN (SELECT * FROM saas_meta.d_account) b on a.own = b.name_uid''' # sql_2 = '''SELECT a.appkey, a.plat, b.cdkey, b.enable FROM (SELECT * FROM customize.d_app) a LEFT JOIN (SELECT * FROM customize.d_account) b on a.own = b.name_uid''' # 合并 d_appkey # saas_server.d_appkey主键:appkey, plat, mongo_id, cdkey sql_replace = '''REPLACE INTO saas_server.d_appkey(inserttm, appkey, plat, mongo_id, cdkey, enable) \ SELECT NOW(), a.appkey, a.plat, \ CASE WHEN (c.mongo_id IS NULL OR c.mongo_id = '') THEN (SELECT MAX(id) FROM saas_server.d_mongo_server) ELSE c.mongo_id END AS mongo_id, b.cdkey, \ CASE WHEN c.enable IS NULL THEN a.enable*b.enable ELSE a.enable*b.enable*c.enable END as enable \ FROM (SELECT * FROM saas_meta.d_app WHERE enable = 1) a \ LEFT JOIN (SELECT * FROM saas_meta.d_account WHERE enable = 1) b on a.own = b.name_uid \ LEFT JOIN (SELECT * FROM saas_server.d_appkey) c ON a.appkey = c.appkey AND a.plat = c.plat AND c.cdkey = b.cdkey \ UNION \ SELECT NOW(), a.appkey, a.plat, \ CASE WHEN c.mongo_id IS NULL OR c.mongo_id = '' THEN (SELECT MAX(id) FROM saas_server.d_mongo_server) ELSE c.mongo_id END AS mongo_id, b.cdkey, \ CASE WHEN c.enable IS NULL THEN b.enable ELSE b.enable*c.enable END as enable \ FROM (SELECT * FROM customize.d_app) a \ LEFT JOIN (SELECT * FROM customize.d_account WHERE enable = 1) b on a.own = b.name_uid \ LEFT JOIN (SELECT * FROM saas_server.d_appkey) c ON a.appkey = c.appkey AND a.plat = c.plat AND c.cdkey = b.cdkey''' cur.execute(sql_replace) con.commit() client.closeMysql()
# -*- coding: utf-8 -*- from MysqlClient import MysqlClient import datetime import time client = MysqlClient("saas_server") con, cur = client.connection sql_format = "INSERT INTO saas_server.product_record (topic, tm, offset) VALUES (%s, %s, %s)" topic = "test" tm = datetime.datetime.fromtimestamp(time.time()) offset = 666 cur.execute(sql_format, (topic, tm, offset)) con.commit() client.closeMysql()