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()
