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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

分區(qū)存儲(chǔ)

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

要解決這種ETL花費(fèi)時(shí)間較長(zhǎng)的問題，可以從以下幾個(gè)方面著手：

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

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

根據(jù)標(biāo)簽指標(biāo)體系的人口屬性、行為屬性、用戶消費(fèi)、風(fēng)險(xiǎn)控制、社交屬性等維度分別建立對(duì)應(yīng)的標(biāo)簽表進(jìn)行分表存儲(chǔ)對(duì)應(yīng)的標(biāo)簽數(shù)據(jù)。如下圖所示。

• 人口屬性表：dw.userprofile_attritube_all
• 行為屬性表：dw.userprofile_action_all
• 用戶消費(fèi)表：dw.userprofile_consume_all
• 風(fēng)險(xiǎn)控制表：dw.userprofile_riskmanage_all
• 社交屬性表：dw.userprofile_social_all

例如創(chuàng)建用戶的人口屬性寬表：

同樣的，用戶其他id維度（如cookieid、deviceid、registerid等）的標(biāo)簽數(shù)據(jù)存儲(chǔ)，也可以使用上面案例中的表結(jié)構(gòu)。

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

標(biāo)簽匯聚

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

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

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

開發(fā)udf函數(shù)“cast_to_json”將用戶身上的標(biāo)簽匯聚成json字符串，執(zhí)行命令將按分區(qū)存儲(chǔ)的標(biāo)簽進(jìn)行匯聚:

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?“用戶各維度的標(biāo)簽表”?
????where?data_date=?"?data_date?"?
group?by?userid

匯聚后用戶標(biāo)簽的存儲(chǔ)格式如圖所示:

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

ID-MAP

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

下圖展示了同一用戶在不同平臺(tái)間的行為示意圖。

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

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

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

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

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

首先需要從埋點(diǎn)表和訪問日志表里面獲取到cookieid和userid同時(shí)出現(xiàn)的訪問記錄。下面案例中，ods.page_event_log是埋點(diǎn)日志表，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??????--?埋點(diǎn)表
???????????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ù)則進(jìn)行更新。

CREATE?TABLE?`dw.cookie_user_zippertable`(
`userid`?string?COMMENT?'賬號(hào)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ù)寫入表中，如上圖所示。

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

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

例如，目前存在一個(gè)記錄userid和cookieid關(guān)聯(lián)關(guān)系的表，但是為多對(duì)多的記錄（即一個(gè)userid對(duì)應(yīng)多條cookieid記錄，以及一條cookieid對(duì)應(yīng)多條userid記錄）。這里可以通過拉鏈表的日期來查看某個(gè)時(shí)間點(diǎn)userid對(duì)應(yīng)的cookieid。查看某個(gè)用戶（如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個(gè)設(shè)備，通過限定時(shí)間段可找到特定時(shí)間下用戶的登錄設(shè)備。

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

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

小結(jié)

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