基於Hive數據倉庫的標簽畫像實戰

000X000 2022-07-24 00:19:58 阅读数:976

hive

Hive數據倉庫

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

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

  • 面向主題: 業務數據庫中的數據主要針對事務處理,各個業務系統之間是相互分離的,而數據倉庫中的數據是按照一定主題進行組織的。

  • 集成:數據倉庫中存儲的數據是從業務數據庫中提取出來的,但並不是對原有數據的簡單複制,而是經過了抽取、清理、轉換(ETL)等工作。業務數據庫記錄的是每一項業務處理的流水賬。這些數據不適合進行分析處理,進入數據倉庫之前需要經過一系列計算,同時拋弃一些無關分析處理的數據。

  • 非易失:業務數據庫中一般只存儲短期數據,因此其數據是不穩定的,記錄的是系統中數據變化的瞬態。數據倉庫中的數據大多錶示過去某一時刻的數據,主要用於查詢、分析,不像業務系統中的數據庫一樣經常修改,一般數據倉庫構建完成後主要用於訪問,不進行修改和删除。

  • 隨時間變化:數據倉庫關注的是曆史數據,按時間順序定期從業務庫和日志庫裏面載入新的數據進行追加,帶有時間屬性。

數據抽取到數據倉庫的流程如下圖所示。

在數據倉庫建模的過程中,主要涉及事實錶和維度錶的建模開發:

事實錶主要圍繞業務過程設計,就應用場景來看主要包括事務事實錶周期快照事實錶累計快照事實錶:

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

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

  • 累計快照事實錶:用於查看不同事件之間的時間間隔,例如分析用戶從購買到支付的時長、從下單到訂單完結的時長等。一般適用於有明確時間周期的業務過程。

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

在畫像系統中主要使用Hive作為數據倉庫,開發相應的維度錶和事實錶來存儲標簽、人群、應用到服務層的相關數據。HiveSQL高級進階技巧

分區存儲

如果將用戶標簽開發成一張大的寬錶,在這張寬錶下放幾十種類型標簽,那麼每天該畫像寬錶的ETL作業將會花費很長時間,而且不便於向這張寬錶中新增標簽類型。

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

  • 將數據分區存儲,分別執行作業;

  • 標簽脚本性能調優;

  • 基於一些標簽共同的數據來源開發中間錶。

下面介紹一種用戶標簽分錶、分區存儲的解决方案。

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

  • 人口屬性錶:dw.userprofile_attritube_all

  • 行為屬性錶:dw.userprofile_action_all

  • 用戶消費錶:dw.userprofile_consume_all

  • 風險控制錶:dw.userprofile_riskmanage_all

  • 社交屬性錶:dw.userprofile_social_all

例如創建用戶的人口屬性寬錶:

同樣的,用戶其他id維度(如cookieid、deviceid、registerid等)的標簽數據存儲,也可以使用上面案例中的錶結構。

在上面的創建中通過設立人口屬性維度的寬錶開發相關的用戶標簽,為了提高數據的插入和查詢效率,在Hive中可以使用分區錶的方式,將數據存儲在不同的目錄中。在Hive使用select查詢時一般會掃描整個錶中所有數據,將會花費很多時間掃描不是當前要查詢的數據,為了掃描錶中關心的一部分數據,在建錶時引入了partition的概念。在查詢時,可以通過Hive的分區機制來控制一次遍曆的數據量。Hive SQL優化思路

標簽匯聚

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

標簽匯聚後將一個每個用戶身上的全量標簽匯聚到一個字段中,錶結構設計如下:

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 '數據日期')

開發udf函數“cast_to_json”將用戶身上的標簽匯聚成json字符串,執行命令將按分區存儲的標簽進行匯聚:

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

開發用戶標簽的時候,有項非常重要的內容——ID-MApping,即把用戶不同來源的身份標識通過數據手段識別為同一個主體。用戶的屬性、行為相關數據分散在不同的數據來源中,通過ID-MApping能够把用戶在不同場景下的行為串聯起來,消除數據孤島。下圖展示了用戶與設備間的多對多關系。解密One ID中的核心技術ID-Mapping

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

舉例來說,用戶在未登錄App的狀態下,在App站內訪問、搜索相關內容時,記錄的是設備id(即cookieid)相關的行為數據。而用戶在登錄App後,訪問、收藏、下單等相關的行為記錄的是賬號id(即userid)相關行為數據。雖然是同一個用戶,但其在登錄和未登錄設備時記錄的行為數據之間是未打通的。通過ID-MApping打通 userid 和 cookieid 的對應關系,可以在用戶登錄、未登錄設備時都能捕獲其行為軌迹。

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

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

拉鏈錶是針對緩慢變化維錶的一種設計方式,記錄一個事物從開始到當前狀態的全部狀態變化信息。

在上圖中,通過拉鏈錶記錄了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)中。代碼執行如下:

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

創建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'

創建完成後,每天ETL調度將數據更新到ID-Mapping拉鏈錶中,任務執行如下。

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個設備,通過限定時間段可找到特定時間下用戶的登錄設備。

在開發中需要注意關於userid與cookieid的多對多關聯,如果不加條件限制就做關聯,很可能引起數據膨脹問題:

在實際應用中,會遇到許多需要將userid和cookieid做關聯的情况。例如,需要在userid維度開發出該用戶近30日的購買次數、購買金額、登錄時長、登錄天數等標簽。前兩個標簽可以很容易地從相應的業務數據錶中根據算法加工出來,而登錄時長、登錄天數的數據存儲在相關日志數據中,日志數據錶記錄的userid與cookieid為多對多關系。因此在結合業務需求開發標簽時,要確定好標簽口徑定義。

小結

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

版权声明:本文为[000X000]所创,转载请带上原文链接,感谢。 https://gsmany.com/2022/205/202207240018085765.html