個人中心
退出
基于hive數倉的標簽畫像實戰(zhàn)
建立用戶畫像首先需要建立數據倉庫，用于存儲用戶標簽數據。Hive是基于Hadoop的數據倉庫工具，依賴于HDFS存儲數據，提供的SQL語言可以查詢存儲在HDFS中的數據。開發(fā)時一般使用Hive作為數據倉庫，存儲標簽和用戶特征庫等相關數據。
"數據倉庫之父" W.H.Inmon 在《Building the Data Warehouse》（中文版《數據倉庫（原書第4版）》）一書中定義數據倉庫是"一個面向主題的、集成的、非易失的、隨時間變化的、用來支持管理人員決策的數據集合"。
面向主題: 業(yè)務數據庫中的數據主要針對事務處理，各個業(yè)務系統之間是相互分離的，而數據倉庫中的數據是按照一定主題進行組織的。
集成:數據倉庫中存儲的數據是從業(yè)務數據庫中提取出來的，但并不是對原有數據的簡單復制，而是經過了抽取、清理、轉換（ETL）等工作。業(yè)務數據庫記錄的是每一項業(yè)務處理的流水賬。這些數據不適合進行分析處理，進入數據倉庫之前需要經過一系列計算，同時拋棄一些無關分析處理的數據。
非易失:業(yè)務數據庫中一般只存儲短期數據，因此其數據是不穩(wěn)定的，記錄的是系統中數據變化的瞬態(tài)。數據倉庫中的數據大多表示過去某一時刻的數據，主要用于查詢、分析，不像業(yè)務系統中的數據庫一樣經常修改，一般數據倉庫構建完成后主要用于訪問，不進行修改和刪除。
隨時間變化:數據倉庫關注的是歷史數據，按時間順序定期從業(yè)務庫和日志庫里面載入新的數據進行追加，帶有時間屬性。
數據抽取到數據倉庫的流程如下圖所示。
在數據倉庫建模的過程中，主要涉及事實表和維度表的建模開發(fā)：
事實表主要圍繞業(yè)務過程設計，就應用場景來看主要包括事務事實表，周期快照事實表和累計快照事實表:
事務事實表：用于描述業(yè)務過程，按業(yè)務過程的單一性或多業(yè)務過程可進一步分為單事務事實表和多事務事實表。其中單事務事實表分別記錄每個業(yè)務過程，如下單業(yè)務記入下單事實表，支付業(yè)務記入支付事實表。多事務事實表在同一個表中包含了不同業(yè)務過程，如下單、支付、簽收等業(yè)務過程記錄在一張表中，通過新增字段來判斷屬于哪一個業(yè)務過程。當不同業(yè)務過程有著相似性時可考慮將多業(yè)務過程放到多事務事實表中。
周期快照事實表:在一個確定的時間間隔內對業(yè)務狀態(tài)進行度量。例如查看一個用戶的近1年付款金額、近1年購物次數、近30日登錄天數等。
累計快照事實表:用于查看不同事件之間的時間間隔，例如分析用戶從購買到支付的時長、從下單到訂單完結的時長等。一般適用于有明確時間周期的業(yè)務過程。
維度表主要用于對事實屬性的各個方面描述，例如，商品維度包括商品的價格、折扣、品牌、原廠家、型號等方面信息。維度表開發(fā)的過程中，經常會遇到維度緩慢變化的情況，對于緩慢變化維一般會采用：①重寫維度值，對歷史數據進行覆蓋；②保留多條記錄，通過插入維度列字段加以區(qū)分；③開發(fā)日期分區(qū)表，每日分區(qū)數據記錄當日維度的屬性；④開發(fā)拉鏈表按時間變化進行全量存儲等方式進行處理。
在畫像系統中主要使用Hive作為數據倉庫，開發(fā)相應的維度表和事實表來存儲標簽、人群、應用到服務層的相關數據。HiveSQL高級進階技巧。
分區(qū)存儲
如果將用戶標簽開發(fā)成一張大的寬表，在這張寬表下放幾十種類型標簽，那么每天該畫像寬表的ETL作業(yè)將會花費很長時間，而且不便于向這張寬表中新增標簽類型。
要解決這種ETL花費時間較長的問題，可以從以下幾個方面著手：
將數據分區(qū)存儲，分別執(zhí)行作業(yè);
標簽腳本性能調優(yōu);
基于一些標簽共同的數據來源開發(fā)中間表。
下面介紹一種用戶標簽分表、分區(qū)存儲的解決方案。
根據標簽指標體系的人口屬性、行為屬性、用戶消費、風險控制、社交屬性等維度分別建立對應的標簽表進行分表存儲對應的標簽數據。如下圖所示。
人口屬性表：dw.userprofile_attritube_all
行為屬性表：dw.userprofile_action_all
用戶消費表：dw.userprofile_consume_all
風險控制表：dw.userprofile_riskmanage_all
社交屬性表：dw.userprofile_social_all
例如創(chuàng)建用戶的人口屬性寬表：
同樣的，用戶其他id維度（如cookieid、deviceid、registerid等）的標簽數據存儲，也可以使用上面案例中的表結構。
在上面的創(chuàng)建中通過設立人口屬性維度的寬表開發(fā)相關的用戶標簽，為了提高數據的插入和查詢效率，在Hive中可以使用分區(qū)表的方式，將數據存儲在不同的目錄中。在Hive使用select查詢時一般會掃描整個表中所有數據，將會花費很多時間掃描不是當前要查詢的數據，為了掃描表中關心的一部分數據，在建表時引入了partition的概念。在查詢時，可以通過Hive的分區(qū)機制來控制一次遍歷的數據量。Hive SQL優(yōu)化思路。
標簽匯聚
在上面一節(jié)提到的案例中，用戶的每個標簽都插入到相應的分區(qū)下面，但是對一個用戶來說，打在他身上的全部標簽存儲在不同的分區(qū)下面。為了方便分析和查詢，需要將用戶身上的標簽做聚合處理。
標簽匯聚后將一個每個用戶身上的全量標簽匯聚到一個字段中，表結構設計如下：
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 '數據日期')
開發(fā)udf函數“cast_to_json”將用戶身上的標簽匯聚成json字符串，執(zhí)行命令將按分區(qū)存儲的標簽進行匯聚:
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后通過直接查詢該表，解析標簽id和對應的標簽權重后，即可在前端展示該用戶的相關信息
ID-MAP
開發(fā)用戶標簽的時候，有項非常重要的內容——ID-MApping，即把用戶不同來源的身份標識通過數據手段識別為同一個主體。用戶的屬性、行為相關數據分散在不同的數據來源中，通過ID-MApping能夠把用戶在不同場景下的行為串聯起來，消除數據孤島。下圖展示了用戶與設備間的多對多關系。解密One ID中的核心技術ID-Mapping。
下圖展示了同一用戶在不同平臺間的行為示意圖。
舉例來說，用戶在未登錄App的狀態(tài)下，在App站內訪問、搜索相關內容時，記錄的是設備id（即cookieid）相關的行為數據。而用戶在登錄App后，訪問、收藏、下單等相關的行為記錄的是賬號id（即userid）相關行為數據。雖然是同一個用戶，但其在登錄和未登錄設備時記錄的行為數據之間是未打通的。通過ID-MApping打通 userid 和 cookieid 的對應關系，可以在用戶登錄、未登錄設備時都能捕獲其行為軌跡。
下面通過一個案例介紹如何通過Hive的ETL工作完成ID-Mapping的數據清洗工作。
緩慢變化維是在維表設計中常見的一種方式，維度并不是不變的，隨時間也會發(fā)生緩慢變化。如用戶的手機號、郵箱等信息可能會隨用戶的狀態(tài)變化而改變，再如商品的價格也會隨時間變化而調整上架的價格。因此在設計用戶、商品等維表時會考慮用緩慢變化維來開發(fā)。同樣，在設計ID-Mapping表時，由于一個用戶可以在多個設備上登錄，一個設備也能被多個用戶登錄，所以考慮用緩慢變化維表來記錄這種不同時間點的狀態(tài)變化。
拉鏈表是針對緩慢變化維表的一種設計方式，記錄一個事物從開始到當前狀態(tài)的全部狀態(tài)變化信息。
在上圖中，通過拉鏈表記錄了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）中。代碼執(zhí)行如下：
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
創(chuàng)建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'
創(chuàng)建完成后，每天ETL調度將數據更新到ID-Mapping拉鏈表中，任務執(zhí)行如下。
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個設備，通過限定時間段可找到特定時間下用戶的登錄設備。
在開發(fā)中需要注意關于userid與cookieid的多對多關聯，如果不加條件限制就做關聯，很可能引起數據膨脹問題：
在實際應用中，會遇到許多需要將userid和cookieid做關聯的情況。例如，需要在userid維度開發(fā)出該用戶近30日的購買次數、購買金額、登錄時長、登錄天數等標簽。前兩個標簽可以很容易地從相應的業(yè)務數據表中根據算法加工出來，而登錄時長、登錄天數的數據存儲在相關日志數據中，日志數據表記錄的userid與cookieid為多對多關系。因此在結合業(yè)務需求開發(fā)標簽時，要確定好標簽口徑定義。
小結
本期內容通過案例介紹了將userid 和 cookieid 打通的一種解決方案，實踐中還存在需要將用戶在不同平臺間（如Web端和App端）行為打通的應用場景。
作者：大數據球球
歡迎關注微信公眾號 ：大數據球球
分類導航