用户画像笔记2-关于用户拉链表 微博用户画像

Hive 数据仓库：

建立用户画像需要建立数据仓库，用于存储用户标签数据。Hive是基于Hadoop的数据仓库工具，依赖于HDFS存储数据，提供的SQL语言可以查询存储在HDFS中的数据，开发时一般使用Hive作为数据仓库，存储标签和用户特征库等相关数据。

分区存储：（提速提效）

如果将用户标签开发成一张大的宽表，在这种宽表下放几时种类型标签，那么每天该画像宽表的ETL作业将会花费比较长时间，而且不便于向这张宽表中新增标签类型。

要解决这种ETL花费时间较长的问题，可以从一下几个方面着手：

1. 将数据分区存储，分别执行作业；

2. 标签脚本性能调优；

3. 基于一些标签共同的数据来源开发中间表；

分区存储的一个方案：

其中一种方案是把标签分表、分区存储,根据标签体系的人口属性、行为属性、用户消费、风险控制、社交属性等维度分别建立对应

的标签表进行分表存储对应的标签数据：

人口属性表： dw.userprofile_attribute_all;

行为属性表： dw.userprofile_action_all:

用户消费表： dw.userprofile_consume_all:

风险控制表： dw.userprofile_riskmanage_all

社交属性表： dw.userprofile_social_all

因为分表、分区存储导致需要做标签汇聚：

create table `dw.userprofile_userlabel_map_all` (

`userid` string COMMENT 'userid',

`userlabels` map<string,string> COMMENT 'tagsmap',)

COMMENT 'userid 用户标签汇聚'

PARTITIONED BY (`data_date` string COMMENT '数据日期')

insert overwrite table dw.userprofile_userlabel_map_all partition(data_date = "data_date")

select userid,

cast_to_json(concat_ws(',',collect_set(concat(labelid,':',labelweight)))) as userlabels

from "用户各维度的标签表“

where data_date = "data_date"

group by userid

ID-MAP：（把设备ID和用户ID按照时序关联）

开发用户标签的时候，需要把用户不同来源的身份标识通过数据手段识别为同一个主体。

用户的属性、行为相关数据分散在不同的数据来源中，通过ID-MAPPing 能够把用户在

不同场景下的行为串联起来，消除数据孤岛。

比如需要打通未登录时用户的cookieid 和登录会后的userid.

具体实现方法：从埋点表和访问日志表里面获取到cookieid 和 userid 同时出现的访问记录。

INSERT OVERWRITE TABLE ods.cookie_user_signin PARTITION(data_date = '$(data_date)')

select t.*

FROM (

select userid,

cookieid,

from_unixtime(eventtime,'yyyyMMdd') as signdate

FROM ods.page_event_log

WHERE data_date = '${data_date}'

UNION ALL

select userid,

cookieid,

from_unixtime（viewtime,'yyyyMMdd') as signdate

FROM ods.page_view_log

WHERE data_date = '${data_date}'

) t

创建一个ID—Map的拉链表：

创建语法

创建一个用户拉链表：

实时更新拉链表：

每天ETL 调度将数据更新到ID-Mapping 拉链表中，任务执行如下：

不同数据库在画像系统中的角色：

Hive 存储数据相关标签表、人群计算表的表结构设计以及ID-Mapping 的一种实现方式；

MySQL 存储标签元数据、监控数据、及结果集数据；

Hbase 存储线上接口实时调用的数据；

Elasticsearch 存储标签人群计算和人群多维透视分析；

相关表数据信息如下：

dw.userprofile_attribute_all: 存储人口属性维度的标签表；

dw.userprofile_action_all: 存储行为属性维度的标签表；

dw.userprofile_consume_all: 存储用户消费维度的标签表

dw.userprofile_riskmanage_all: 存储风险控制维度的标签表

dw.userprofile_social_all:存储社交属性维度的标签表

dw.userprofile_userlabel_map_all: 汇聚用户各维度标签的表。

dw.userprofile_usergroup_labels_all:存储计算后人群数据的表。

其它知识，left join 左连接，union all 全连接, union 内连接:

关于左连接：取出a表中所有数据，再加上与a,b 匹配的数据。

union : union 只取不同的数值。