基于Hive數(shù)據(jù)倉庫的標簽畫像實戰(zhàn)

本期內(nèi)容主要介紹使用Hive作為數(shù)據(jù)倉庫的應(yīng)用場景時，相應(yīng)的庫表結(jié)構(gòu)如何設(shè)計。

Hive數(shù)據(jù)倉庫

建立用戶畫像首先需要建立數(shù)據(jù)倉庫，用于存儲用戶標簽數(shù)據(jù)。Hive是基于Hadoop的數(shù)據(jù)倉庫工具，依賴于HDFS存儲數(shù)據(jù)，提供的SQL語言可以查詢存儲在HDFS中的數(shù)據(jù)。開發(fā)時一般使用Hive作為數(shù)據(jù)倉庫，存儲標簽和用戶特征庫等相關(guān)數(shù)據(jù)。

"數(shù)據(jù)倉庫之父" W.H.Inmon 在《Building the Data Warehouse》一書中定義數(shù)據(jù)倉庫是"一個面向主題的、集成的、非易失的、隨時間變化的、用來支持管理人員決策的數(shù)據(jù)集合"。

• 面向主題: 業(yè)務(wù)數(shù)據(jù)庫中的數(shù)據(jù)主要針對事務(wù)處理，各個業(yè)務(wù)系統(tǒng)之間是相互分離的，而數(shù)據(jù)倉庫中的數(shù)據(jù)是按照一定主題進行組織的。

• 集成:數(shù)據(jù)倉庫中存儲的數(shù)據(jù)是從業(yè)務(wù)數(shù)據(jù)庫中提取出來的，但并不是對原有數(shù)據(jù)的簡單復(fù)制，而是經(jīng)過了抽取、清理、轉(zhuǎn)換（ETL）等工作。業(yè)務(wù)數(shù)據(jù)庫記錄的是每一項業(yè)務(wù)處理的流水賬。這些數(shù)據(jù)不適合進行分析處理，進入數(shù)據(jù)倉庫之前需要經(jīng)過一系列計算，同時拋棄一些無關(guān)分析處理的數(shù)據(jù)。

• 非易失:業(yè)務(wù)數(shù)據(jù)庫中一般只存儲短期數(shù)據(jù)，因此其數(shù)據(jù)是不穩(wěn)定的，記錄的是系統(tǒng)中數(shù)據(jù)變化的瞬態(tài)。數(shù)據(jù)倉庫中的數(shù)據(jù)大多表示過去某一時刻的數(shù)據(jù)，主要用于查詢、分析，不像業(yè)務(wù)系統(tǒng)中的數(shù)據(jù)庫一樣經(jīng)常修改，一般數(shù)據(jù)倉庫構(gòu)建完成后主要用于訪問，不進行修改和刪除。

• 隨時間變化:數(shù)據(jù)倉庫關(guān)注的是歷史數(shù)據(jù)，按時間順序定期從業(yè)務(wù)庫和日志庫里面載入新的數(shù)據(jù)進行追加，帶有時間屬性。

數(shù)據(jù)抽取到數(shù)據(jù)倉庫的流程如下圖所示。

在數(shù)據(jù)倉庫建模的過程中，主要涉及事實表和維度表的建模開發(fā)：

事實表主要圍繞業(yè)務(wù)過程設(shè)計，就應(yīng)用場景來看主要包括事務(wù)事實表，周期快照事實表和累計快照事實表:

• 事務(wù)事實表：用于描述業(yè)務(wù)過程，按業(yè)務(wù)過程的單一性或多業(yè)務(wù)過程可進一步分為單事務(wù)事實表和多事務(wù)事實表。其中單事務(wù)事實表分別記錄每個業(yè)務(wù)過程，如下單業(yè)務(wù)記入下單事實表，支付業(yè)務(wù)記入支付事實表。多事務(wù)事實表在同一個表中包含了不同業(yè)務(wù)過程，如下單、支付、簽收等業(yè)務(wù)過程記錄在一張表中，通過新增字段來判斷屬于哪一個業(yè)務(wù)過程。當不同業(yè)務(wù)過程有著相似性時可考慮將多業(yè)務(wù)過程放到多事務(wù)事實表中。

• 周期快照事實表:在一個確定的時間間隔內(nèi)對業(yè)務(wù)狀態(tài)進行度量。例如查看一個用戶的近1年付款金額、近1年購物次數(shù)、近30日登錄天數(shù)等。

• 累計快照事實表:用于查看不同事件之間的時間間隔，例如分析用戶從購買到支付的時長、從下單到訂單完結(jié)的時長等。一般適用于有明確時間周期的業(yè)務(wù)過程。

維度表主要用于對事實屬性的各個方面描述，例如，商品維度包括商品的價格、折扣、品牌、原廠家、型號等方面信息。維度表開發(fā)的過程中，經(jīng)常會遇到維度緩慢變化的情況，對于緩慢變化維一般會采用：①重寫維度值，對歷史數(shù)據(jù)進行覆蓋；②保留多條記錄，通過插入維度列字段加以區(qū)分；③開發(fā)日期分區(qū)表，每日分區(qū)數(shù)據(jù)記錄當日維度的屬性；④開發(fā)拉鏈表按時間變化進行全量存儲等方式進行處理。

在畫像系統(tǒng)中主要使用Hive作為數(shù)據(jù)倉庫，開發(fā)相應(yīng)的維度表和事實表來存儲標簽、人群、應(yīng)用到服務(wù)層的相關(guān)數(shù)據(jù)。

分區(qū)存儲

如果將用戶標簽開發(fā)成一張大的寬表，在這張寬表下放幾十種類型標簽，那么每天該畫像寬表的ETL作業(yè)將會花費很長時間，而且不便于向這張寬表中新增標簽類型。

要解決這種ETL花費時間較長的問題，可以從以下幾個方面著手：

• 將數(shù)據(jù)分區(qū)存儲，分別執(zhí)行作業(yè);
• 標簽?zāi)_本性能調(diào)優(yōu);
• 基于一些標簽共同的數(shù)據(jù)來源開發(fā)中間表。

下面介紹一種用戶標簽分表、分區(qū)存儲的解決方案。

根據(jù)標簽指標體系的人口屬性、行為屬性、用戶消費、風險控制、社交屬性等維度分別建立對應(yīng)的標簽表進行分表存儲對應(yīng)的標簽數(shù)據(jù)。如下圖所示。

• 人口屬性表：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等）的標簽數(shù)據(jù)存儲，也可以使用上面案例中的表結(jié)構(gòu)。

在上面的創(chuàng)建中通過設(shè)立人口屬性維度的寬表開發(fā)相關(guān)的用戶標簽，為了提高數(shù)據(jù)的插入和查詢效率，在Hive中可以使用分區(qū)表的方式，將數(shù)據(jù)存儲在不同的目錄中。在Hive使用select查詢時一般會掃描整個表中所有數(shù)據(jù)，將會花費很多時間掃描不是當前要查詢的數(shù)據(jù)，為了掃描表中關(guān)心的一部分數(shù)據(jù)，在建表時引入了partition的概念。在查詢時，可以通過Hive的分區(qū)機制來控制一次遍歷的數(shù)據(jù)量。

標簽匯聚

在上面一節(jié)提到的案例中，用戶的每個標簽都插入到相應(yīng)的分區(qū)下面，但是對一個用戶來說，打在他身上的全部標簽存儲在不同的分區(qū)下面。為了方便分析和查詢，需要將用戶身上的標簽做聚合處理。

標簽匯聚后將一個每個用戶身上的全量標簽匯聚到一個字段中，表結(jié)構(gòu)設(shè)計如下：

CREATE?TABLE?`dw.userprofile_userlabel_map_all`
(
????`userid`?????string?COMMENT?'userid',
????`userlabels`?map?COMMENT?'tagsmap',
)
????COMMENT?'userid?用戶標簽匯聚'
????PARTITIONED?BY?(?`data_date`?string?COMMENT?'數(shù)據(jù)日期')

開發(fā)udf函數(shù)“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

匯聚后用戶標簽的存儲格式如圖所示:

將用戶身上的標簽進行聚合便于查詢和計算。例如，在畫像產(chǎn)品中，輸入用戶id后通過直接查詢該表，解析標簽id和對應(yīng)的標簽權(quán)重后，即可在前端展示該用戶的相關(guān)信息

ID-MAP

開發(fā)用戶標簽的時候，有項非常重要的內(nèi)容——ID-MApping，即把用戶不同來源的身份標識通過數(shù)據(jù)手段識別為同一個主體。用戶的屬性、行為相關(guān)數(shù)據(jù)分散在不同的數(shù)據(jù)來源中，通過ID-MApping能夠把用戶在不同場景下的行為串聯(lián)起來，消除數(shù)據(jù)孤島。下圖展示了用戶與設(shè)備間的多對多關(guān)系。

下圖展示了同一用戶在不同平臺間的行為示意圖。

舉例來說，用戶在未登錄App的狀態(tài)下，在App站內(nèi)訪問、搜索相關(guān)內(nèi)容時，記錄的是設(shè)備id（即cookieid）相關(guān)的行為數(shù)據(jù)。而用戶在登錄App后，訪問、收藏、下單等相關(guān)的行為記錄的是賬號id（即userid）相關(guān)行為數(shù)據(jù)。雖然是同一個用戶，但其在登錄和未登錄設(shè)備時記錄的行為數(shù)據(jù)之間是未打通的。通過ID-MApping打通 userid 和 cookieid 的對應(yīng)關(guān)系，可以在用戶登錄、未登錄設(shè)備時都能捕獲其行為軌跡。

下面通過一個案例介紹如何通過Hive的ETL工作完成ID-Mapping的數(shù)據(jù)清洗工作。

緩慢變化維是在維表設(shè)計中常見的一種方式，維度并不是不變的，隨時間也會發(fā)生緩慢變化。如用戶的手機號、郵箱等信息可能會隨用戶的狀態(tài)變化而改變，再如商品的價格也會隨時間變化而調(diào)整上架的價格。因此在設(shè)計用戶、商品等維表時會考慮用緩慢變化維來開發(fā)。同樣，在設(shè)計ID-Mapping表時，由于一個用戶可以在多個設(shè)備上登錄，一個設(shè)備也能被多個用戶登錄，所以考慮用緩慢變化維表來記錄這種不同時間點的狀態(tài)變化。

拉鏈表是針對緩慢變化維表的一種設(shè)計方式，記錄一個事物從開始到當前狀態(tài)的全部狀態(tài)變化信息。

在上圖中，通過拉鏈表記錄了userid每一次關(guān)聯(lián)到不同cookieid的情況。如userid為44463729的用戶，在20190101這天登錄某設(shè)備，在6號那天變換了另一個設(shè)備登錄。其中start_date表示該記錄的開始日期，end_date表示該記錄的結(jié)束日期，當end_date為99991231時，表示該條記錄當前仍然有效。

首先需要從埋點表和訪問日志表里面獲取到cookieid和userid同時出現(xiàn)的訪問記錄。下面案例中，ods.page_event_log是埋點日志表，ods.page_view_log是訪問日志表，將獲取到的userid和cookieid信息插入cookieid-userid關(guān)系表（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表中的數(shù)據(jù)與拉鏈表歷史數(shù)據(jù)做比較，如果有變化或新增數(shù)據(jù)則進行更新。

CREATE?TABLE?`dw.cookie_user_zippertable`(
`userid`?string?COMMENT?'賬號ID',?
`cookieid`?string?COMMENT?'設(shè)備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調(diào)度將數(shù)據(jù)更新到ID-Mapping拉鏈表中，任務(wù)執(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

數(shù)據(jù)寫入表中，如上圖所示。

對于該拉鏈表，可查看某日（如20190801）的快照數(shù)據(jù)。

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

例如，目前存在一個記錄userid和cookieid關(guān)聯(lián)關(guān)系的表，但是為多對多的記錄（即一個userid對應(yīng)多條cookieid記錄，以及一條cookieid對應(yīng)多條userid記錄）。這里可以通過拉鏈表的日期來查看某個時間點userid對應(yīng)的cookieid。查看某個用戶（如32101029）在某天（如20190801）關(guān)聯(lián)到的設(shè)備id。

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

上圖可看出用戶'32101029'在歷史中曾登錄過3個設(shè)備，通過限定時間段可找到特定時間下用戶的登錄設(shè)備。

在開發(fā)中需要注意關(guān)于userid與cookieid的多對多關(guān)聯(lián)，如果不加條件限制就做關(guān)聯(lián)，很可能引起數(shù)據(jù)膨脹問題：

在實際應(yīng)用中，會遇到許多需要將userid和cookieid做關(guān)聯(lián)的情況。例如，需要在userid維度開發(fā)出該用戶近30日的購買次數(shù)、購買金額、登錄時長、登錄天數(shù)等標簽。前兩個標簽可以很容易地從相應(yīng)的業(yè)務(wù)數(shù)據(jù)表中根據(jù)算法加工出來，而登錄時長、登錄天數(shù)的數(shù)據(jù)存儲在相關(guān)日志數(shù)據(jù)中，日志數(shù)據(jù)表記錄的userid與cookieid為多對多關(guān)系。因此在結(jié)合業(yè)務(wù)需求開發(fā)標簽時，要確定好標簽口徑定義。

小結(jié)

本期內(nèi)容通過案例介紹了將userid 和 cookieid 打通的一種解決方案，實踐中還存在需要將用戶在不同平臺間（如Web端和App端）行為打通的應(yīng)用場景。