基于hive數(shù)倉(cāng)的標(biāo)簽畫像實(shí)戰(zhàn)

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

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

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

集成:數(shù)據(jù)倉(cāng)庫中存儲(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ù)倉(cāng)庫之前需要經(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ù)倉(cāng)庫中的數(shù)據(jù)大多表示過去某一時(shí)刻的數(shù)據(jù)，主要用于查詢、分析，不像業(yè)務(wù)系統(tǒng)中的數(shù)據(jù)庫一樣經(jīng)常修改，一般數(shù)據(jù)倉(cāng)庫構(gòu)建完成后主要用于訪問，不進(jìn)行修改和刪除。

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

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

251512-1.png
在數(shù)據(jù)倉(cāng)庫建模的過程中，主要涉及事實(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年購(gòu)物次數(shù)、近30日登錄天數(shù)等。

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

251512-2.png
維度表主要用于對(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ù)倉(cāng)庫，開發(fā)相應(yīng)的維度表和事實(shí)表來存儲(chǔ)標(biāo)簽、人群、應(yīng)用到服務(wù)層的相關(guān)數(shù)據(jù)。HiveSQL高級(jí)進(jìn)階技巧。
分區(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
251512-3.png
例如創(chuàng)建用戶的人口屬性寬表：

251512-4.png
同樣的，用戶其他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ù)量。Hive SQL優(yōu)化思路。






標(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<string,string> COMMENT 'tagsmap',
)
    COMMENT 'userid 用戶標(biāo)簽匯聚'
    PARTITIONED BY ( `data_date` string COMMENT '數(shù)據(jù)日期')


251512-5.png
開發(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ǔ)格式如圖所示:



251512-6.png


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

251512-7.png
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)系。解密One ID中的核心技術(shù)ID-Mapping。

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

251512-9.png
舉例來說，用戶在未登錄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)變化。

251512-10.png
拉鏈表是針對(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'
251512-11.png
上圖可看出用戶'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日的購(gòu)買次數(shù)、購(gòu)買金額、登錄時(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)景。





作者：大數(shù)據(jù)球球


歡迎關(guān)注微信公眾號(hào) ：大數(shù)據(jù)球球