Hive数仓——标签画像实战

吵吵叭火

已于 2024-06-15 10:33:41 修改

阅读量716

点赞数 26

分类专栏： # 用户画像文章标签：数据仓库大数据

于 2024-06-15 06:34:42 首次发布

本文链接：https://blog.csdn.net/SHWAITME/article/details/139693137

版权

用户画像专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言

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

一、数仓建模

在数据仓库建模的过程中，主要涉及事实表和维度表的建模开发。

1.1 DWD事实表

其中事实表主要围绕业务过程来设计，就应用场景来看，主要包括事务事实表、周期快照事实表和累计快照事实表。

事务事实表：用于描述业务过程，如下单业务记入下单事实表，支付业务记入支付事实表。
周期快照事实表：在一个确定的时间间隔内，对业务状态进行度量。例如查看一个用户的近一年付款金额，近一年购物次数，近30日登陆天数等。（快照：状态）
累计快照事实表：用于查看不同事件之间的时间间隔，例如分析用户从购买到支付的时长，从下单到订单完结的时长等。一般适用于有明确时间周期的业务过程。

1.2 DIM维度表

维度表主要用于对事实属性的各个方面描述，例如，商品维度包括商品的价格，折扣，品牌，原厂家，型号等方面信息，维度表开发的过程中，经常会遇到维度缓慢变化的情况，对于缓慢变化维一般会采用：（1）重写维度值，对历史数据进行覆盖；（2）保留多条记录，通过插入维度列字段加以区分；（3）开发日期分区表，每日分区数据记录当日维度的属性；（4）开发拉链表按时间变化进行全量存储等方式进行处理。

在画像系统中主要使用Hive作为数据仓库，开发相应的维度表和事实表来存储标签、人群、应用到服务层的相关数据

二、标签开发

2.1 DWS宽表—分区存储

如果将用户标签开发成一张大的宽表，在这张宽表下放几十种类型标签，ETL作业将会花费很长时间，而且不便于向这张宽表中新增标签类型。解决这种ETL划花费时间较长的问题，可以从以下几个方面着手：

将数据分区存储，分别执行作业；
标签脚本性能调优；
基于一些标签共同的数据来源，开发对应的中间表；

列举一种用户标签分表，分区存储的解决方案。

根据标签指标体系的人口属性、行为属性、用户消费、风险控制、社交属性等维度分别建立对应的标签表，进行分表存储对应的标签数据。如下图所示：

人口属性表：dw.userprofile_attritube_all
行为属性表：dw.userprofile_action_all
用户消费表：dw.userprofile_consume_all
风险控制表：dw.userprofile_riskmanage_all
社交属性表：dw.userprofile_social_all

例如创建用户的人口属性宽表：

在上面的创建中通过设立人口属性维度的宽表开发相关的用户标签，为了提高数据的插入和查询效率，在Hive中可以使用分区表的方式，将数据存储在不同的目录中，在查询时，可以通过Hive的分区机制来控制一次遍历的数据量。

2.2 标签属性聚合

上述提到，用户的每个标签都插入到相应的分区下面，但是对于一个用户而言，打在他身上的全部标签存储在不同的分区下面。为了方便分析和查询，需要将标签做聚合处理。将每个用户身上的全量标签汇聚到一个字段中，表结构设计如下：

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 '数据日期')

开发udf函数“cast_to_json”将用户身上的标签汇聚成json字符串，执行命令将按分区存储的标签进行汇聚:

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

ps 上述代码用到的函数：

(1)concat_ws：带分隔符的字符串连接
   语法: concat_ws(string SEP, string A, string B…)
         select concat_ws('-','abc','def') // abc-def
 
 
(2)collect_list：收集并形成list集合，结果不去重
   语法:select id, collect_list(likes) from student group by id;
 
 
(2)collect_set：收集并形成set集合，结果去重
   语法:select id, collect_set(likes) from student group by id;

汇聚后用户标签的存储格式如图所示:

将用户身上的标签进行聚合便于查询和计算。例如，在画像产品中，输入用户id后通过直接查询该表，解析标签id和对应的标签权重后，即可在前端展示该用户的相关信息

2.3 ID-Mapping

开发用户标签的时候，有项非常重要的内容：ID-Mapping，即把用户不同来源的身份标识通过数据手段识别为同一个主体。用户的属性、行为相关数据分散在不同的数据源中，通过ID-Mapping的方式，能够把用户在不同场景下的行为串联起来，消除数据孤岛。下图展示了同一用户在不同平台间的行为示意图。

举例来说，用户在未登录App的状态下，在App站内访问、搜索相关内容时，记录的是设备id（即cookieid）相关的行为数据。而用户在登陆App后，访问，收藏，下单等相关的行为记录的是账号id（即userid）相关行为数据。虽然是同一个用户，但其在登录、未登录设备时记录的行为数据之间是未打通的。通过ID-MApping打通 userid 和 cookieid 的对应关系，就可以在用户登录、未登录设备时都能捕获其行为轨迹。

下面通过一个案例介绍如何通过Hive的ETL工作完成ID-Mapping的数据清洗工作。

缓慢变化维是在维表设计中的一种方式，整体数据量大，数据量会变化，但是变化的频率和变动占比不是特别高的情况。例如用户的手机号、邮箱等信息可能会随用户的状态变化而改变，再如商品的价格也会随时间变化而调整上架的价格。因此在设计用户维表、商品等维表时会考虑用缓慢变化维来开发。同样，在设计ID-Mapping表时，由于一个用户可以在多个设备上登录，一个设备也能被多个用户登录，可以考虑用缓慢变化维来记录这种不同时间点的状态变化。

拉链表是针对缓慢变化维表的一种设计方式，记录一个事物从开始到当前状态的全部状态变化信息。上图描述：通过拉链表记录了userid每一次关联到不同cookieid的情况。如userid为44463729的用户，在20190101这天登录某设备，在6号那天变换了另一个设备登录。其中start_date表示该记录的开始日期，end_date表示该记录的结束日期，当end_date为99991231时，表示该条记录当前仍然有效。

首先需要从埋点表和访问日志表里面获取到cookieid和userid同时出现的访问记录。下面案例中，ods.page_event_log是埋点日志表，ods.page_view_log是访问日志表，将获取到的userid和cookieid信息插入cookieid-userid关系表（ods.cookie_user_signin）中。代码执行如下：

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的拉链表，将每天新增到ods.cookie_user_signin表中的数据与拉链表历史数据做比较，如果有变化或新增数据则进行更新。

CREATE TABLE `dw.cookie_user_zippertable`(
`userid` string COMMENT '账号ID', 
`cookieid` string COMMENT '设备ID', 
`start_date` string COMMENT 'start_date', 
`end_date` string COMMENT 'end_date')
COMMENT 'id-map拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

创建完成后，每天ETL调度将数据更新到ID-Mapping拉链表中，任务执行如下。

INSERT OVERWRITE TABLE dw.cookie_user_zippertable
SELECT t.* 
  FROM (
      SELECT t1.user_num,
             t1.mobile,
             t1.reg_date,
             t1.start_date,
             CASE WHEN t1.end_date = '99991231' AND t2.userid IS NOT NULL THEN '${data_date}'
                  ELSE t1.end_date
             END AS end_date
       FROM dw.cookie_user_zippertable t1
    LEFT JOIN (  SELECT *
                 FROM ods.cookie_user_signin
                WHERE data_date='${data_date}'
              )t2
           ON t1.userid = t2.userid
UNION
       SELECT userid,
              cookieid,
              '${data_date}' AS start_date,
              '99991231' AS end_date
        FROM ods.cookie_user_signin
       WHERE data_date = '${data_date
       }'
          ) t

数据写入表中，如上图所示。对于该拉链表，可查看某日（如20190801）的快照数据。

select  * 
from dw.cookie_user_zippertable 
where start_date<='20190801' and end_date>='20190801'

例如，目前存在一个记录userid和cookieid关联关系的表，但是为多对多的记录（即一个userid对应多条cookieid记录，以及一条cookieid对应多条userid记录）。这里可以通过拉链表的日期来查看某个时间点userid对应的cookieid。查看某个用户（如32101029）在某天（如20190801）关联到的设备id。

select cookieid 
from dw.cookie_user_zippertable 
where userid='32101029' and start_date<='20190801' and end_date>='20190801'

上图可看出用户'32101029'在历史中曾登录过3个设备，通过限定时间段可找到特定时间下用户的登录设备。

在开发中需要注意关于userid与cookieid的多对多关联，如果不加条件限制就做关联，很可能引起数据膨胀问题。

在实际应用中，会遇到需要将userid与cookieid做关联的情况。例如，需要在userid维度开发出，该用户近30日的购买次数、购买金额、登录时长、登录天数等标签。前两个标签可以从相应的业务数据表中，根据算法加工出来，而登录时长、登录天数的数据存储在相关日志数据中，日志数据记录的userid与cookieid为多对多关系，因此在结合业务需求开发标签时，需要定义标签口径定义。

总结，上述案例介绍了将用户的账号id (userId)和用户的设备Id(cookieid )打通的一种解决方案，此外，可能还存在需要将用户在不同平台间（如Web端和App端）行为打通的应用场景。

参考文章：

基于hive数仓的标签画像实战