def get_view_profile_rowkeys(begin, end, mode='news'):
# topic一个月大约30w，news一个大约80w，不需要快
print 'begin', begin, timestamp2datetime(begin)
print 'end', end, timestamp2datetime(end)
# 确定solr表
solr = solr_np if mode == 'news' else solr_tp
# 调整时间戳为毫秒格式
begin = ensure_m_timestamp(begin)
end = ensure_m_timestamp(end)
print begin
print end
# 估算rows
days = (end - begin) / 86400 / 1000
rows = days * every_day_topic_cnt if mode == 'topic' else days * every_day_news_cnt
print 'rows', rows
# 构建索引
solr_query = SolrQuery()
q = '*:*'
solr_query.set('q', q)
solr_query.add('fq', 'event_time:[%s TO %s]' % (begin, end))
solr_query.set('rows', rows)
solr_query.set('fl', ['id'])
rowkey_list = [
item['id'] for item in solr.search(**solr_query.get_query_dict())
]
print len(rowkey_list)
return rowkey_list
def get_user_qa_content(uid, begin, end):
# 不要了，改用从hbase取数据
# 获取用户在一段时间内所有qa的全文
begin_ds = timestamp2datetime(ensure_second_timestamp(begin))
end_ds = timestamp2datetime(ensure_second_timestamp(end))
all_qa_text = []
sql1 = 'select id from ask_problem where user_id=%s and created_time>"%s" and created_time<"%s";' \
% (
uid, begin_ds, end_ds
)
o1 = get_medicaldb_handler().do_one(sql1)
if o1 is None or len(o1) == 0:
return all_qa_text
for item in o1:
problem_id = item[0]
sql = 'select content from ask_problemcontent where problem_id=%s;' % problem_id
o = get_medicaldb_handler().do_one(sql)
if o is None or len(o) == 0:
continue
content = o[0][0]
content_dict = json.loads(content)[0]
if content_dict['type'] != 'text':
continue
text = content_dict['text']
all_qa_text.append(text)
return all_qa_text
def test1():
uid = sys.argv[2]
end = sys.argv[3]
interval = sys.argv[4]
end = ensure_second_timestamp(end)
begin = end - int(interval) * 61.0
end += int(interval) * 61.0
print "begin", timestamp2datetime(begin)
print "end", timestamp2datetime(end)
user_time_event(uid, begin, end)
def get_qa_uids(begin, end):
# 获取begin-end之间所有qa对应的user_id
begin_dt = timestamp2datetime(ensure_second_timestamp(begin))
end_dt = timestamp2datetime(ensure_second_timestamp(end))
sql = 'select distinct user_id from ask_problem where created_time>"%s" and created_time<"%s";' % (
begin_dt, end_dt)
o = get_medicaldb_handler().dbhandler.do_one(sql)
uids = set()
for item in o:
uid = item[0]
uids.add(int(uid))
return uids
def get_view_news_data(row_prefix):
connection = happybase.Connection('hbase_server',
compat='0.90',
port=19090,
timeout=SMALL_TIMEOUT)
table = connection.table("cy_event")
news_viwers = defaultdict(set)
cnt = 0
print time.time()
last_ts = None
now = time.time()
start = now - 86400 * 180
focused_type = ('view_news', 'view_topic')
if row_prefix not in focused_type:
return
all_types = defaultdict(int)
for key, data in table.scan(row_prefix=row_prefix):
try:
action_type, ts, uid = key.split('|')
except:
continue
all_types[action_type] += 1
if action_type not in focused_type:
continue
last_ts = ensure_second_timestamp(ts)
if last_ts < start:
continue
news_id = data[CY_REAL_TIME_EVENT_ATTR_MAP[action_type]]
news_viwers[news_id].add(uid)
cnt += 1
if cnt % 1000 == 0:
print timestamp2datetime(time.time()), cnt, len(news_viwers)
print time.time()
print 'last_ts', last_ts
print len(news_viwers)
for x in all_types:
print x, all_types[x]
with open('cy_event_%s.json' % row_prefix, 'w') as f:
for news_id in news_viwers:
str = json.dumps({
'id': news_id,
'uids': list(news_viwers[news_id]),
'len': len(news_viwers[news_id])
}) + '\n'
f.write(str)
def get_ask(self, uid, begin, end):
# 获取用户一段时间内的qa首问，begin,end都是时间戳，需要转换成datetime
begin = timestamp2datetime(begin)
end = timestamp2datetime(end)
sql = 'select ask from ask_problem where user_id=%s and created_time > "%s" and created_time < "%s";' % (
uid, begin, end)
print "get_ask sql", sql
o = self.dbhandler.do_one(sql)
all_ask = []
if o is None:
return all_ask
for item in o:
ask = item[0] # unicode
all_ask.append(ask)
return all_ask
def user_time_event(uid, begin, end):
begin = ensure_second_timestamp(begin)
end = ensure_second_timestamp(end)
connection = happybase.Connection('hbase_server',
compat='0.90',
port=19090,
timeout=30000)
table = connection.table("cy_real_time_event")
for key, value in table.scan(row_prefix=str(uid) + '|'):
print "all key", key
_, ts, event_type = key.split('|')
ts = ensure_second_timestamp(ts)
print "all time", timestamp2datetime(ts)
if ts >= begin and ts <= end:
print "shoot key", key, value
print "shoot time", timestamp2datetime(ts)
def get_ask_by_timestamp(self, uid, timestamp):
if isinstance(timestamp, int):
timestamp /= 1000.0
datetime = timestamp2datetime(timestamp)
sql = 'select id,ask from ask_problem where user_id=%s and created_time="%s";' % (
uid, datetime)
o = self.dbhandler.do_one(sql)
if o is None or len(o) == 0:
return None, None
return o[0][0], o[0][1]
def get_user_qa_content2(uid, begin, end):
# 从habse problem2表中 获取用户在一段时间内所有qa的全文
begin_ds = timestamp2datetime(ensure_second_timestamp(begin))
end_ds = timestamp2datetime(ensure_second_timestamp(end))
all_qa_text = []
sql1 = 'select id from ask_problem where user_id=%s and created_time>"%s" and created_time<"%s";' \
% (
uid, begin_ds, end_ds
)
o1 = get_medicaldb_handler().do_one(sql1)
if o1 is None or len(o1) == 0:
return all_qa_text
for item in o1:
problem_id = item[0]
qa_texts = get_qa_texts_by_pid(problem_id)
all_qa_text.extend(qa_texts)
return all_qa_text
def test_insert():
fields = [
"created_date", "news_all_input_qa", "news_all_output_qa",
"news_no_info_qa", "news_filtered_by_preprocessing_qa",
"news_empty_res_qa", "news_bad_res_qa", "news_all_input_bs",
"news_all_output_bs", "news_no_info_bs",
"news_filtered_by_preprocessing_bs", "news_empty_res_bs",
"news_bad_res_bs"
]
test_ds = timestamp2datetime(0)
data = [[
test_ds, 22968, 912, 8197, 1039, 185, 674, 22960, 812, 8197, 1039, 185,
674
]]
bdp_manager = BDPManager(TABLE_NAME)
bdp_manager.insert_data(fields, data)
bdp_manager.commit()
def insert_one_day():
# 必须加id，否则默认为''，会覆盖之前的数据
log_file_name1, ana_file_name1, bdp_file_name1, log_file_name2, ana_file_name2, bdp_file_name2, bdp_file_name = get_yesterday_log_filename(
)
bdp_data = pickle_from_file(bdp_file_name)
now = time.time()
yesterday = now - 86400.0
ds = timestamp2datetime(now)
date_int = int(timestamp2date(yesterday))
fields = ["created_date", "id"]
data0 = [ds, date_int]
for field in bdp_data:
cnt = bdp_data[field]
print field, type(field)
print cnt, type(cnt)
fields.append(field)
data0.append(cnt)
print "fields", fields
print "data0", data0
data = [data0]
insert_kernel(fields, data)
def test19():
import time
from general_utils.db_utils import get_medicaldb_handler
from general_utils.time_utils import timestamp2datetime
uid = sys.argv[2]
print 'uid', uid
t1 = time.time()
sql = 'select id from ask_problem where user_id=%s and created_time>"%s";' % (
uid, timestamp2datetime(time.time() - 180 * 86400))
o = get_medicaldb_handler().do_one(sql)
if o is None or len(o) == 0:
print 'nothing'
return
all_content = []
for item in o:
id = item[0]
print id
sql1 = 'select content from ask_problemcontent where problem_id=%s;' % id
o1 = get_medicaldb_handler().do_one(sql1)
all_content.append(o1)
t2 = time.time()
print 'time', t2 - t1
def main1():
uids = []
# 获取所有uid
for i in (0, 1, 2, 3):
uid_filename = get_parti_uid_filename(part=i, mode='news')
with open(uid_filename, 'r') as f:
ls = f.readlines()
t_uids = [int(item.strip('\n')) for item in ls]
uids.extend(t_uids)
#
output_filename = '20180312_user_event_and_recommend_news.csv'
yesterday_begin, yesterday_end = get_yesterday_timestamp()
yesterday_begin = int(yesterday_begin * 1000)
yesterday_end = int(yesterday_end * 1000)
#
fo = open(output_filename, 'w')
csvwriter = csv.writer(fo)
first_line = [
'uid', 'is_app_user', 'event_datetime', 'event_type', 'event_obj',
'recommended_news'
]
csvwriter.writerow(first_line)
all_cnt = 0
good_cnt = 0
shuffle(uids)
for uid in uids[:1000]:
all_cnt += 1
is_app = is_app_user(uid)
print '+' * 10, uid, '+' * 10
user_action_list = cy_time_event_one_user_kernel2(
uid, yesterday_begin, yesterday_end)
recommended_news_ids = get_caled_user_topn_news(uid)
recommended_news_ids = new_newsids_check(recommended_news_ids, 2)
if recommended_news_ids:
good_cnt += 1
cnt = 0
for i in range(max([len(user_action_list),
len(recommended_news_ids)])):
if cnt == 0:
user_id = str(uid)
else:
user_id = ''
is_app = ''
try:
event_datetime = timestamp2datetime(user_action_list[i][0] /
1000.0)
event_type = user_action_list[i][2]
event_obj = user_action_list[i][1]
if event_type == 'vn':
title = nat_get_title('news_' + str(event_obj))
event_obj_str = str(event_obj) + '|' + title
elif event_type == 'vt':
title = nat_get_title('topic_' + str(event_obj))
event_obj_str = str(event_obj) + '|' + title
else:
event_obj_str = event_obj
except:
event_datetime = ''
event_obj_str = ''
event_type = ''
try:
recommended_news_id = recommended_news_ids[i]
title = nat_get_title('news_' + str(recommended_news_id))
recommend_str = str(recommended_news_id) + '|' + title
except:
recommend_str = ''
line = convert2gbk([
user_id,
str(is_app), event_datetime, event_type, event_obj_str,
recommend_str
])
csvwriter.writerow(line)
cnt += 1
line = ['all', 'good']
csvwriter.writerow(line)
csvwriter.writerow([str(all_cnt), str(good_cnt)])
fo.close()
def main():
today_zero, today_end = get_today_timestamp()
ds = timestamp2datetime(0)
print ds, type(ds)
def a1(log_file_name, ana_file_name, bdp_file_name):
START = "==========start======="
# 2017 11 08 日，被成功推荐的，用户uid，触发时间，推送文章id，推送时间，
# 当天推送时间后是否浏览过该文章，以及浏览时间
today_zero, today_end = get_today_timestamp(time.time() - 86400.0)
def get_uid(l):
return l.split("=uid=")[1].split('=')[0]
fi = open(log_file_name, 'r')
fo = open(ana_file_name, "w")
csvwriter = csv.writer(fo, dialect='excel')
first_line = [u"uid", u"触发时间", u"触发类型", u"用户全文", u"用户tag", u"用户人群",
u"文章id", u"文章标题", u"文章tag", u"文章分类", u"返回时间", u"点击时间"]
csvwriter.writerow(convert2gbk(first_line))
uid = None
uni_key0 = None
trigger_time = None
trigger_type = None
caled = set()
all = set() # 所有触发的请求
reason = None
all_qa = defaultdict(set)
all_bs = defaultdict(set)
cnt = 0
for l in fi:
# if not l.startswith("2017-11-08"):
# continue
cnt += 1
# if cnt > 10000:
# continue
if START in l:
# 记录上一个
if reason and uni_key0 and trigger_type:
if trigger_type == "bs":
all_bs["all"].add(uni_key0)
elif trigger_type == "qa":
all_qa["all"].add(uni_key0)
if uni_key0 and trigger_type and not reason:
if trigger_type == "bs":
all_bs["failed"].add(uni_key0)
elif trigger_type == "qa":
all_qa["failed"].add(uni_key0)
uid = get_uid(l)
trigger_time = l.split(',')[0]
uni_key0 = uid + '|' + trigger_time
if "pid=None" in l:
trigger_type = "bs"
else:
trigger_type = "qa"
all_qa["all"].add(uni_key0)
reason = None
# all.add(uni_key0)
trigger_ts = datetime_str2timestamp(trigger_time)
print "uni_key", uni_key0
print "ts", trigger_ts
# if "=trigger=" in l:
# trigger_type0 = l.split("=trigger=")[1].split('=')[0]
if "=special_population=" in l:
special_population0 = l.split("=special_population=")[1].split("=")[0]
if "=texts=" in l:
texts0 = l.split("=texts=")[1].split("=")[0]
if "=tags=" in l:
tags0 = l.split("=tags=")[1].split("=")[0]
if "failed in recommend==" in l:
reason = l.split("failed in recommend==")[1].split("=")[0]
if trigger_type == "qa":
if reason not in all_qa:
all_qa[reason] = set([uni_key0])
else:
all_qa[reason].add(uni_key0)
elif trigger_type == "bs":
if reason not in all_bs:
all_bs[reason] = set([uni_key0])
else:
all_bs[reason].add(uni_key0)
if "succeed in recommend==========" in l:
reason = "succeed"
if trigger_type == "qa":
if reason not in all_qa:
all_qa[reason] = set([uni_key0])
else:
all_qa[reason].add(uni_key0)
elif trigger_type == "bs":
if reason not in all_bs:
all_bs[reason] = set([uni_key0])
else:
all_bs[reason].add(uni_key0)
return_time = l.split(',')[0]
uni_key = uid + return_time
if uni_key in caled:
continue
print 'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'
caled.add(uni_key)
return_ts = datetime_str2timestamp(return_time)
nid = l.split("=====id=")[1].split("=")[0]
ntitle = l.split("===title=")[1].split("=")[0]
# news_title = get_db_data_local_handler().get_news_title(nid)
news_type = get_db_data_local_handler().get_news_type(nid)
news_tags = u'|||'.join(get_news_tags_from_solr("news_" + str(nid)))
print uid
print trigger_type
print nid
print return_ts
print type(ntitle)
# first_line = [u"uid", u"触发时间", u"触发类型",u"用户全文",u"用户tag",u"用户人群",
# u"文章id", u"文章标题",u"文章tag",u"文章分类",u"返回时间", u"点击时间"]
views = cy_time_event_one_user_viewnews(uid, begin=return_ts, end=today_end)
print views
rows = [str(uid), trigger_time, trigger_type, texts0, tags0, special_population0,
str(nid), ntitle, news_tags, news_type, return_time,
str(timestamp2datetime(views.get(nid, -1)))]
rows = convert2gbk(rows)
csvwriter.writerow(rows)
csvwriter.writerow([u"所有uid".encode("gbk"), u"推了的uid".encode("gbk")])
rows = [str(len(all)), str(len(caled))]
rows = convert2gbk(rows)
csvwriter.writerow(rows)
fi.close()
fo.close()
for x in all_qa:
print x + "|||" + str(len(all_qa[x]))
for x in all_bs:
print x + "|||" + str(len(all_bs[x]))
with open(bdp_file_name, "w") as f:
f.write("news_all_input_qa|||" + str(len(all_qa["all"])) + "\n")
f.write("news_all_output_qa|||" + str(len(all_qa["succeed"])) + "\n")
f.write("news_no_info_qa|||" + str(len(all_qa["user_info is None "])) + "\n")
f.write("news_filtered_by_preprocessing_qa|||" + str(len(all_qa["filter_user_info bad "])) + "\n")
f.write("news_empty_res_qa|||" + str(len(all_qa["topn_ids_scores empty"])) + "\n")
f.write("news_bad_res_qa|||" + str(len(all_qa["best_score so low"])) + "\n")
f.write("qa_failed|||" + str(len(all_qa["failed"])) + "\n")
f.write("news_all_input_bs|||" + str(len(all_bs["all"])) + "\n")
f.write("news_all_output_bs|||" + str(len(all_bs["succeed"])) + "\n")
f.write("news_no_info_bs|||" + str(len(all_bs["user_info is None "])) + "\n")
f.write("news_filtered_by_preprocessing_bs|||" + str(len(all_bs["filter_user_info bad "])) + "\n")
f.write("news_empty_res_bs|||" + str(len(all_bs["topn_ids_scores empty"])) + "\n")
f.write("news_bad_res_bs|||" + str(len(all_bs["best_score so low"])) + "\n")
f.write("bs_failed|||" + str(len(all_bs["failed"])) + "\n")
def g1():
'''
查看不使用热卖tag扩充的覆盖率，和使用热卖tag扩充的覆盖率
分子，能匹配上热卖tag的，分母，一天内有活动用户（cy_event
'''
from general_utils.hbase_utils import get_user_query, get_user_query2
from general_utils.solr_utils import get_last_login_uids
from recommend.manager.recommend_tags_data_helper import get_relation_plan3
from general_utils.db_utils import get_db_data_local_handler
from general_utils.hbase_utils import get_sp_duration_active_userid
from general_utils.time_utils import timestamp2datetime, ensure_second_timestamp
# 用户采样时间窗
# 用户采样命中率
end_ds0 = '2018-01-21 23:59:40'
end0 = datetime_str2timestamp(end_ds0)
begin0 = end0 - 86400 * 1
# 每个选中用户的数据采集时间窗
end_ds = '2018-01-22 23:59:40'
end = datetime_str2timestamp(end_ds)
begin = end - 86400 * 180.0 # 半年
# 最后登录时间在2018-01-21 23:59:40前一周的用户
# test_uids = get_last_login_uids(begin0, end0)
# test_uids = get_sp_duration_active_userid(begin0,end0)
test_uids = get_one_day_uid_from_file('log_event_20180122')
print "test_uids num", len(test_uids)
# 打乱顺序，取1000个样本
random.shuffle(test_uids)
selected_uids = test_uids[:3000]
all_good_cnt = 0
all_cnt = 0
app_cnt = 0
good_app_cnt = 0
text_empty_cnt = 0
fo = open('180129_rp_1.csv', 'w')
csvwriter = csv.writer(fo)
first_line = [
'uid', 'username', 'is_app', 'last_info_time', 'use_tags',
'systag_ids', 'tag_names', 't', 'is_tangsai'
]
csvwriter.writerow(first_line)
# status_dict = {
# 1: "qa and query",
# 2: "view actions",
# 3: "search_doctor clinic_no",
# 0: ""
# }
total_time = {}
for uid in selected_uids:
print '==============uid=%s=======================' % uid
username = get_username(uid)
is_app = is_app_user(uid)
all_cnt += 1
if is_app:
app_cnt += 1
t1 = time.time()
res = get_relation_plan3(uid, test=True)
t2 = time.time()
t = t2 - t1
total_time[uid] = t
status = res['status']
is_tangsai = False
if status:
all_good_cnt += 1
if is_app:
good_app_cnt += 1
systag_ids = res['ids']
if 96 in systag_ids:
is_tangsai = True
tagnames = [
get_db_data_local_handler().get_systagid_name(id)
for id in systag_ids
]
if status in (1, 2, 4):
info0 = res['systag_id_dict']
record_info = '~'.join(info0.keys())
elif status == 3:
info0 = res['clinic_no']
record_info = '~'.join(info0)
last_ts = res['last_ts']
last_info_time = timestamp2datetime(
ensure_second_timestamp(last_ts))
else:
systag_ids = []
tagnames = []
record_info = ''
last_info_time = ''
systag_ids_str = '~'.join([str(x) for x in systag_ids])
tagnames_str = '~'.join(tagnames)
line = convert2gbk([
str(uid), username,
str(is_app), last_info_time, record_info, systag_ids_str,
tagnames_str,
str(t),
str(is_tangsai)
])
csvwriter.writerow(line)
line = [str(all_cnt), str(all_good_cnt), str(app_cnt), str(good_app_cnt)]
csvwriter.writerow(line)
s_total_time = sorted(total_time.iteritems(),
key=lambda x: x[1],
reverse=True)
times = total_time.values()
line = [str(min(times)), str(max(times)), str(sum(times) / len(times))]
csvwriter.writerow(line)
for uid, t in s_total_time[:10]:
line = [str(uid), str(t)]
csvwriter.writerow(line)
fo.close()
print str(max(times))
print all_good_cnt
def format_content(res):
last_value = res['last_value']
return "最后时间戳 = %s" % timestamp2datetime(last_value / 1000)