1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
from __future__ import absolute_import
from __future__ import division
from __future__ import print_function
import pymysql
import smtplib
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
import redis
import datetime
from pyspark import SparkConf
import time
from pyspark.sql import SparkSession
import json
import numpy as np
import pandas as pd
from pyspark.sql.functions import lit
from pyspark.sql.functions import concat_ws
from tool import get_data_by_mysql, get_all_word_tags, get_all_tag_tag_type, get_all_3tag_2tag, get_all_tags_name, get_tag2_from_tag3
from tool import get_user_log, compute_henqiang, get_action_tag_count, compute_jiaoqiang, compute_ai_scan, compute_ruoyixiang, compute_validate
def get_user_service_portrait(cl_id, all_word_tags, all_tag_tag_type, all_3tag_2tag, all_tags_name, size=None):
db_jerry_test = pymysql.connect(host='172.16.40.158', port=4000, user='st_user', passwd='aqpuBLYzEV7tML5RPsN1pntUzFy',
db='jerry_test', charset='utf8')
cur_jerry_test = db_jerry_test.cursor()
user_df_service = get_user_log(cl_id, all_word_tags)
# 增加df字段(days_diff_now, tag_type, tag2)
if not user_df_service.empty:
user_df_service["days_diff_now"] = round((int(time.time()) - user_df_service["time"].astype(float)) / (24 * 60 * 60))
user_df_service["tag_type"] = user_df_service.apply(lambda x: all_tag_tag_type.get(x["tag_id"]), axis=1)
user_df_service = user_df_service[user_df_service['tag_type'].isin(['2','3'])]
if not user_df_service.empty:
user_log_df_tag2_list = user_df_service[user_df_service['tag_type'] == '2']['tag_id'].unique().tolist()
user_df_service["tag2"] = user_df_service.apply(lambda x:
get_tag2_from_tag3(x.tag_id, all_3tag_2tag, user_log_df_tag2_list)
if x.tag_type == '3' else x.tag_id, axis=1)
user_df_service["tag2_type"] = user_df_service.apply(lambda x: all_tag_tag_type.get(x["tag2"]), axis=1)
# 算分及比例
user_df_service["tag_score"] = user_df_service.apply(
lambda x: compute_henqiang(x.days_diff_now, exponential=1, action_tag_count=get_action_tag_count(user_df_service, x.time)) if x.score_type == "henqiang" else (
compute_jiaoqiang(x.days_diff_now, exponential=1, action_tag_count=get_action_tag_count(user_df_service, x.time)) if x.score_type == "jiaoqiang" else (
compute_ai_scan(x.days_diff_now, exponential=1, action_tag_count=get_action_tag_count(user_df_service, x.time)) if x.score_type == "ai_scan" else (
compute_ruoyixiang(x.days_diff_now, exponential=1, action_tag_count=get_action_tag_count(user_df_service, x.time)) if x.score_type == "ruoyixiang" else
compute_validate(x.days_diff_now, exponential=1, action_tag_count=get_action_tag_count(user_df_service, x.time))))), axis=1)
tag_score_sum = user_df_service.groupby(by=["tag2", "tag2_type"]).agg(
{'tag_score': 'sum', 'cl_id': 'first', 'action': 'first'}).reset_index().sort_values(by=["tag_score"],
ascending=False)
tag_score_sum['weight'] = 100 * tag_score_sum['tag_score'] / tag_score_sum['tag_score'].sum()
tag_score_sum["pay_type"] = tag_score_sum.apply(
lambda x: 3 if x.action == "api/order/validate" else (
2 if x.action == "api/settlement/alipay_callback" else 1
), axis=1
)
gmkv_tag_score_sum = tag_score_sum[["tag2", "tag_score", "weight"]][:size].to_dict('record')
gmkv_tag_score2_sum = tag_score_sum[["tag2", "tag_score"]][:size].to_dict('record')
gmkv_tag_score2_sum_dict = {i["tag2"]: i["tag_score"] for i in gmkv_tag_score2_sum}
# 写redis
redis_client = redis.StrictRedis.from_url('redis://:ReDis!GmTx*0aN9@172.16.40.173:6379')
cl_id_portrait_key2 = "user:service_portrait_tags2:cl_id:" + str(cl_id)
# 如果画像的tag个数小于5,则补充热搜词
if len(gmkv_tag_score2_sum_dict) < 5:
hot_search_wordskey2 = "user:service_coldstart_tags2"
hot_search_words_score = redis_client.hgetall(hot_search_wordskey2)
for tag_id in hot_search_words_score:
if int(tag_id) in gmkv_tag_score2_sum_dict:
continue
else:
gmkv_tag_score2_sum_dict.update({int(tag_id): float(hot_search_words_score[tag_id])})
if len(gmkv_tag_score2_sum_dict) > 4:
break
redis_client.delete(cl_id_portrait_key2)
redis_client.hmset(cl_id_portrait_key2, gmkv_tag_score2_sum_dict)
redis_client.expire(cl_id_portrait_key2, time=30 * 24 * 60 * 60)
# 标签name写redis
cl_id_portrait_key3 = "user:service_portrait_tags3:cl_id:" + str(cl_id)
gmkv_tag_score3_sum_dict = {all_tags_name[i]: gmkv_tag_score2_sum_dict[i] for i in gmkv_tag_score2_sum_dict}
redis_client.delete(cl_id_portrait_key3)
redis_client.hmset(cl_id_portrait_key3, gmkv_tag_score3_sum_dict)
redis_client.expire(cl_id_portrait_key3, time=30 * 24 * 60 * 60)
# 记录用户画像更新了的flag,以及用户最新行为信息
device_latest_action_key = "device:latest:action:tag:names:update:flag" + str(cl_id)
set_dict = dict()
card_types = ['diary', 'qa', 'topic']
for card_type in card_types:
field_name = "user_portrait_is_change_" + card_type
set_dict.update({field_name: 1})
set_dict.update({'user_portrait_is_change': 1})
# 如果该用户不存在最新行为的标签,则用分数最低的标签代替,且更新该用户的最新行为flag
if not redis_client.hexists(device_latest_action_key, 'latest_action_tags'):
tag = [min(gmkv_tag_score3_sum_dict, key=gmkv_tag_score3_sum_dict.get)]
set_dict.update({"latest_action_tags": json.dumps(tag)})
for card_type in card_types:
action_field_name = "latest_action_is_change_" + card_type
set_dict.update({action_field_name: 1})
set_dict.update({'latest_action_is_change': 1})
redis_client.hmset(device_latest_action_key, set_dict)
redis_client.expire(device_latest_action_key, time=30 * 24 * 60 * 60)
# 写tidb,redis同步
stat_date = datetime.datetime.today().strftime('%Y-%m-%d')
gmkv_tag_score3_sum_dict = sorted(gmkv_tag_score3_sum_dict.items(), key=lambda i: i[1], reverse=True)[:5]
replace_sql = """insert into user_service_portrait_tags (stat_date, cl_id, portrait_tag_list) values("{stat_date}","{cl_id}","{portrait_tag_list}")"""\
.format(stat_date=stat_date, cl_id=cl_id, portrait_tag_list=gmkv_tag_score3_sum_dict)
cur_jerry_test.execute(replace_sql)
db_jerry_test.commit()
cur_jerry_test.close()
db_jerry_test.close()
return cl_id
if __name__ == '__main__':
try:
db_jerry_test = pymysql.connect(host='172.16.40.170', port=4000, user='st_user', passwd='aqpuBLYzEV7tML5RPsN1pntUzFy',
db='jerry_test', charset='utf8')
cur_jerry_test = db_jerry_test.cursor()
# 获取最近30天内的用户设备id
sql_device_ids = "select distinct cl_id from user_new_tag_log " \
"where time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day))"
cur_jerry_test.execute(sql_device_ids)
device_ids_lst = [i[0] for i in cur_jerry_test.fetchall()]
db_jerry_test.close()
cur_jerry_test.close()
redis_client = redis.StrictRedis.from_url('redis://:ReDis!GmTx*0aN9@172.16.40.173:6379')
# 获取搜索词及其近义词对应的tag
all_word_tags = get_all_word_tags()
all_tag_tag_type = get_all_tag_tag_type()
# 3级tag对应的2级tag
all_3tag_2tag = get_all_3tag_2tag()
# 标签id对应的中文名称
all_tags_name = get_all_tags_name()
# 搜索词tag
search_words_synonym_tags_key = "search:words:synonym:tags"
search_words_synonym_tags_json = json.dumps(all_word_tags)
# gm_kv_cli.set(search_words_synonym_tags_key, search_words_synonym_tags_json)
redis_client.set(search_words_synonym_tags_key, search_words_synonym_tags_json)
# rdd
sparkConf = SparkConf().set("spark.hive.mapred.supports.subdirectories", "true") \
.set("spark.hadoop.mapreduce.input.fileinputformat.input.dir.recursive", "true") \
.set("spark.tispark.plan.allow_index_double_read", "false") \
.set("spark.tispark.plan.allow_index_read", "true") \
.set("spark.sql.extensions", "org.apache.spark.sql.TiExtensions") \
.set("spark.tispark.pd.addresses", "172.16.40.170:2379").set("spark.io.compression.codec", "lzf") \
.set("spark.driver.maxResultSize", "8g").set("spark.sql.avro.compression.codec", "snappy")
spark = SparkSession.builder.config(conf=sparkConf).enableHiveSupport().getOrCreate()
spark.sparkContext.setLogLevel("WARN")
spark.sparkContext.addPyFile("/srv/apps/ffm-baseline_git/eda/smart_rank/tool.py")
device_ids_lst_rdd = spark.sparkContext.parallelize(device_ids_lst, numSlices=1000)
result = device_ids_lst_rdd.repartition(100).map(lambda x: get_user_service_portrait(x, all_word_tags, all_tag_tag_type, all_3tag_2tag, all_tags_name))
# result.foreach(print)
print("done")
result.collect()
spark.stop()
except Exception as e:
pass