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