簡介面向大數(shù)據開發(fā)工程師與數(shù)據倉庫工程師的用戶畫像標簽數(shù)據存儲完整方案PDF適合正在規(guī)劃或優(yōu)化畫像平臺的團隊參考。資源圍繞Hive、MySQL、Hbase、Elasticsearch四種存儲引擎展開講清各自在畫像場景中的定位與分工并給出用戶標簽表、標簽聚合表、人群計算表的字段設計與分區(qū)策略。壓縮包內共1個PDF文件大小約1.34MB內容全部集中在文檔中。目前已有211人學習使用。文檔覆蓋從Hive向Hbase、MySQL同步標簽數(shù)據的完整流程包括Sqoop落地方式、數(shù)據量校驗機制以及將圈定人群推送給廣告系統(tǒng)、Push系統(tǒng)、客服系統(tǒng)等業(yè)務方的調度思路。其中穿插tag表、tagmap表等真實表結構示例與執(zhí)行過程說明可幫助讀者對照設計自己的畫像存儲層規(guī)避表結構混亂與數(shù)據同步丟失等常見問題。1. 用戶畫像系統(tǒng)的標簽數(shù)據存儲為什么同一套數(shù)據要拆進四個數(shù)據庫很多剛接觸用戶畫像系統(tǒng)的人第一反應是“標簽不就是一張用戶表加幾個字段嗎MySQL 就夠了吧”。真正跑過生產環(huán)境的人都不會這么想。用戶畫像系統(tǒng)的核心是標簽數(shù)據存儲一套標簽從離線計算到線上服務至少要經過 Hive、MySQL、Hbase、Elasticsearch 四個庫每個庫承擔完全不同的角色少一個都會出問題。這個結論不是設計文檔里拍腦袋定的而是由數(shù)據量、查詢時延、寫入頻率和業(yè)務系統(tǒng)的接入方式共同逼出來的。本文拆解的這份解決方案把四種數(shù)據庫的存儲定位、表結構設計、同步鏈路和校驗機制都講得很清楚適合正在搭畫像平臺的數(shù)據工程師、數(shù)倉開發(fā)以及準備把標簽服務線上化的后端同學。新手可以照表結構復現(xiàn)熟手可以直接拿走里面的同步校驗方案。2. Hive 主存標簽結果集tag 表、tagmap 表與人群計算表的建表細節(jié)2.1 為什么畫像的“數(shù)據底座”必須放 Hive畫像相關的數(shù)據有一個共同特點數(shù)據量極大且計算邏輯復雜。一個中型的電商平臺每天活躍用戶百萬級每個用戶身上掛幾十個標簽再按時間分區(qū)累積單日新增數(shù)據就是千萬甚至億級行。這種計算量下標簽的生成作業(yè)跑的是 MapReduce 或者 Spark結果寫 HDFS。MySQL 扛不住這么大的寫入Hbase 雖然能扛寫入但跑批量作業(yè)時沒有 Hive 的 SQL 生態(tài)方便。所以 Hive 在畫像系統(tǒng)里的定位是“所有標簽相關計算結果集的默認落點”包括用戶標簽表、標簽聚合表、人群計算結果表全部先落在 Hive 數(shù)倉里。Hive 存儲的一個關鍵設計是分區(qū)。方案里明確提到tag 表按“日期 標簽主題”雙分區(qū)設計。日期分區(qū)解決的是每天全量標簽快照的隔離問題標簽主題分區(qū)解決的是 ETL 調度時同時計算多個標簽的并行插入問題。這里有一個細節(jié)值得注意如果只按日期分區(qū)那么同一天內不同主題的標簽寫入時需要反復動態(tài)分區(qū)或覆蓋寫調度上非常被動加了標簽主題分區(qū)后每個標簽作業(yè)可以獨立往自己的分區(qū)里寫互不干擾某個標簽的計算失敗也只會影響它自己的分區(qū)重跑成本極低。2.2 tag 表一條用戶一個標簽一行記錄用戶標簽表tag 表是畫像系統(tǒng)最底層的表記錄的是“用戶 id、標簽 id、標簽權重”的最小粒度對應關系。以下建表語句是這套方案的核心結構幾乎所有標簽結果都會落到這種格式里CREATE TABLE dw.profile_tag_userid ( user_id STRING COMMENT 用戶ID, tag_id STRING COMMENT 標簽ID, tag_weight DOUBLE COMMENT 標簽權重, tag_type STRING COMMENT 標簽類型, data_date STRING COMMENT 數(shù)據日期分區(qū) ) PARTITIONED BY (data_date STRING, tag_type STRING) STORED AS ORC;向 Hive 插入測試數(shù)據的常見做法是INSERT OVERWRITE TABLE dw.profile_tag_userid PARTITION (data_date2024-05-20, tag_typeuserid_all_paid_money) SELECT user_id, userid_all_paid_money AS tag_id, sum(pay_amount) AS tag_weight FROM dwd_order_detail WHERE data_date2024-05-20 GROUP BY user_id;這段 SQL 的邏輯是從訂單明細表按用戶匯總支付金額把匯總結果作為標簽權重寫入 tag 表分區(qū)字段 tag_type 取值為標簽主題名。注意這里用了 INSERT OVERWRITE 而不是 INSERT INTO因為同一分區(qū)每天重復計算時需要保證冪等。tag_type 分區(qū)字段還有一個非常實用的功能不同類型的標簽如消費能力標簽、活躍度標簽、偏好標簽可以并行向同一張表的不同分區(qū)寫入不需要鎖表也不會互相覆蓋。2.3 tagmap 表把同一個人身上的標簽聚合成一條tag 表的粒度是一用戶一標簽一行但查詢時往往是“一個用戶身上的全部標簽”。如果每次查詢都掃 tag 表全分區(qū)性能非常差。于是方案里設計了 tagmap 表將同一個用戶的所有標簽聚合到一行用 Map 或者拼接字符串的方式存儲。結構類似CREATE TABLE dw.profile_user_map_userid ( user_id STRING COMMENT 用戶ID, tag_map MAPSTRING, DOUBLE COMMENT 標簽ID到權重的映射, data_date STRING COMMENT 數(shù)據日期分區(qū) ) PARTITIONED BY (data_date STRING) STORED AS ORC;聚合執(zhí)行的思路是從 tag 表按 user_id 分組把 tag_id 和 tag_weight 收集成 Map。常見做法是寫一個 HiveQL用 collect_list 和 map 函數(shù)組合構造 Map或者直接用 Spark 的 mapFromEntries 函數(shù)處理。聚合的目的不是減少數(shù)據量而是把“用戶視角”的查詢從掃描 N 行變成掃描 1 行。這個表是后續(xù)人群圈選和畫像查詢的主力表。2.4 人群計算表從標簽圈人到業(yè)務系統(tǒng)的數(shù)據出口人群表記錄的是圈人結果字段包括用戶 id、人群名稱 id、推送到的業(yè)務系統(tǒng)。這個表的典型特征是它需要關聯(lián)訂單表和用戶收貨信息表得到用戶的手機號等聯(lián)系信息推送給外呼中心或短信系統(tǒng)。核心 join 邏輯是人群表先 join 訂單表拿到訂單編號再 join 收貨信息表拿到手機號。三步 join 下來本質上是從“標簽篩選”過渡到“運營觸達”。這個表的設計要點是它除了存 user_id還必須冗余業(yè)務系統(tǒng)需要的字段。因為下游系統(tǒng)不一定能通過 user_id 反查用戶信息直接把手機號、訂單號冗余在人群結果表里同步到業(yè)務庫時可以減少一次 join。方案中給到的表結構里人群名稱 id 和業(yè)務系統(tǒng)標識是必備字段業(yè)務系統(tǒng)標識決定了這條數(shù)據后面同步到 MySQL 還是 Hbase。3. MySQL 管元數(shù)據與校驗畫像系統(tǒng)的“控制面”搭建3.1 MySQL 在畫像系統(tǒng)里的三個職責MySQL 在畫像系統(tǒng)里不存明細標簽數(shù)據它管的是三類東西畫像標簽的元數(shù)據、結果集校驗信息、同步到業(yè)務系統(tǒng)的數(shù)據。一句話概括Hive 是數(shù)據面MySQL 是控制面。元數(shù)據維護著標簽的 id、名稱、主題、一級二級分類、標簽描述等。一個標簽從需求提出到上線先在元數(shù)據表里登記然后才進入 Hive 計算流程。結果集校驗信息包括當日標簽覆蓋用戶量、當日與前一日波動比例、當日標簽覆蓋用戶占活躍用戶比例、任務是否繼續(xù)執(zhí)行的標志位。這些校驗表的存在是為了避免 0 點調度任務跑完之后沒人檢查是否產出正常就直接同步線上。3.2 元數(shù)據表與校驗表的字段設計元數(shù)據表結構設計上至少要包含這些字段字段名類型說明tag_idvarchar(64)標簽唯一 ID與 Hive 中 tag_id 對齊tag_namevarchar(128)標簽名稱tag_themevarchar(64)標簽主題與 Hive 分區(qū)對應level1_categoryvarchar(64)一級分類level2_categoryvarchar(64)二級分類tag_desctext標簽描述包括口徑和計算邏輯ownervarchar(64)負責人用于標簽運維結果集校驗表的核心字段是標簽 id、數(shù)據日期、覆蓋用戶量、波動比例、校驗狀態(tài)。其中波動比例的計算邏輯是當日覆蓋用戶量 - 前日覆蓋用戶量/ 前日覆蓋用戶量超過閾值時就寫入告警標志位。這個標志位在調度系統(tǒng)里會被讀取決定下游任務是否繼續(xù)執(zhí)行。很多團隊的調度依賴只用“前一個任務是否成功”但畫像場景更嚴謹?shù)淖龇ㄊ恰扒耙粋€任務成功且數(shù)據質量校驗通過”否則會帶著錯誤數(shù)據一路同步到線上。3.3 從 Hive 同步 MySQLSqoop 命令與 Python 腳本的取舍同步到業(yè)務系統(tǒng)這一步方案里給了兩個路徑Sqoop 同步和 Python 腳本同步。Sqoop 適合一次性的、整表的同步命令示例sqoop export \ --connect jdbc:mysql://mysql-host:3306/user_profile \ --username root --password secret \ --table customer_push_list \ --export-dir /user/hive/warehouse/dw.db/customer_push_list \ --input-fields-terminated-by \001 \ --columns user_id,phone,order_id,campaign_id \ --batch \ --update-mode allowinsert \ --update-key user_id,campaign_id這段命令的邏輯說明從 Hive 的 customer_push_list 表導出數(shù)據到 MySQL 的 customer_push_list 表字段分隔符是 Hive 默認的 \001update-mode 設置為 allowinsert 表示已存在的主鍵更新、不存在則插入。這個參數(shù)很關鍵——業(yè)務系統(tǒng)推送場景里同一個用戶可能被多個活動圈中以 user_id 和 campaign_id 作為聯(lián)合主鍵才能避免數(shù)據互相覆蓋。日常維護中我更傾向于寫一個 Python 腳本同步。因為 Sqoop 的缺字段、字符集問題在畫像這種多口徑場景里比較常見Python 腳本可以同步過程里做數(shù)據量對比、格式轉換和異常重試。腳本的核心邏輯是查 Hive 當日分區(qū)總數(shù)查 MySQL 目標表當前總數(shù)兩數(shù)對不上就發(fā)告警不執(zhí)行寫入。4. Hbase 與 Elasticsearch圈人結果入庫與在線查詢的兩種路徑4.1 為什么圈人結果要推 Hbase線上服務的時延要求畫像系統(tǒng)產品化之后運營人員圈完一個人群這批人需要進入廣告系統(tǒng)、push 消息系統(tǒng)等線上服務。這類業(yè)務系統(tǒng)讀數(shù)據的特點是QPS 高、單次查詢要求毫秒級返回、數(shù)據量從幾十萬到幾千萬不等。Hive 完全扛不住這種查詢壓力MySQL 在千萬級數(shù)據 高并發(fā)場景下也容易出現(xiàn)連接打滿。Hbase 的隨機讀寫能力在這個場景里是剛需。同步鏈路一般是這樣運營在頁面上配置規(guī)則規(guī)則和圈出的人群標簽集存 MySQLSpark 作業(yè)讀取 MySQL 中的標簽集信息去 Hive 的標簽表計算具體人群計算結果寫入 Hive 當日分區(qū)然后再由同步作業(yè)把該分區(qū)數(shù)據寫入 Hbase 對應表。4.2 Hive 映射 Hbase 表的兩種建表方式同步 Hive 數(shù)據到 Hbase常見做法是創(chuàng)建 Hive 到 Hbase 的映射表。第一種方式是啟動 Hive創(chuàng)建一張映射到 Hbase 的 Hive 外部表然后向該映射表插入測試數(shù)據Hbase 側會自動建表。第二種方式是直接在 Hbase 側建表然后用 Hive 關聯(lián)查詢。我實際落地更推薦第一種因為可以直接復用 Hive 的 SQL 邏輯插入語句執(zhí)行起來就是一個 MapReduce 作業(yè)跑完數(shù)據自然落在 Hbase 里。如果非要用 Hbase 原生 API 寫批量導入常見做法是使用 Hbase 的 BulkLoad 工具先讓 MapReduce 作業(yè)生成 HFile再加載到 Hbase 表。這個方式跳過了 WAL 寫入速度比直接 put 快很多適合千萬級以上的初始導入。但注意 BulkLoad 只適合一次性大批量導入不適合頻繁的小批量同步因為生成 HFile 和加載 HFile 的過程需要額外的 HDFS 空間操作不當容易把 region 弄得不均勻。4.3 Hbase 同步的兩種數(shù)據校驗方案這是整套方案里工程含金量最高的一段。因為灌入 Hbase 的數(shù)據直接應用到線上反饋到用戶那里任何數(shù)據問題都會直接暴露所以同步必須加校驗。方案里給了兩種做法。第一種Hive 同步 Hbase 后先在 Hbase 里建一個 temp 臨時表數(shù)據寫入臨時表再校驗臨時表和 Hive 表的數(shù)據量差異。如果差異在可接受范圍內把 Hbase 臨時表 rename 成正式表。這利用了 Hbase 改表名的低成本特性但代價是同步期間線上讀不到當天最新數(shù)據。第二種Hive 同步 Hbase 后直接寫入正式表同時建立一張狀態(tài)表。同步完成觸發(fā)校驗校驗通過后在狀態(tài)表寫入當天日期和校驗狀態(tài)。線上接口請求時只讀取狀態(tài)表中最近日期的數(shù)據。如果同步異常狀態(tài)表不更新線上繼續(xù)讀取前一天的數(shù)據。這種方案的優(yōu)點是不影響線上讀取缺點是數(shù)據質量有問題時當天數(shù)據可能已經暴露給線上一段時間了所以校驗任務必須緊跟在同步任務之后立刻執(zhí)行。我在生產上更傾向第二種方案配合告警同步完成立刻校驗數(shù)量對不上馬上釘釘告警DBA 介入前線上可能只有幾分鐘的臟數(shù)據窗口但總比長時間沒有新鮮數(shù)據要好。4.4 Elasticsearch 在畫像里的真實定位不是替代 Hbase方案里對 Elasticsearch 的描述很務實一個開源的分布式全文檢索引擎近乎實時地存儲、檢索數(shù)據擴展性好可以處理 PB 級別數(shù)據。對于用戶標簽查詢、用戶人群計算、用戶群多維透視分析這類對響應時間要求較高的場景可以考慮選用 Elasticsearch。實際工程中ES 在畫像系統(tǒng)里主要干兩類事。第一類是標簽明細的快速查詢比如運營輸入一個用戶 id 或 cookie立刻看到這個人的全部標簽這個場景用 ES 的基于 user_id 的查詢毫秒級返回第二類是人群的多維透視分析比如圈選了“近 30 天有購買行為且客單價高于 500 元”的人群后想看這批人的年齡分布、城市分布ES 的聚合查詢aggs非常擅長這種場景一個 JSON 查詢就能返回多維統(tǒng)計結果而 Hive 跑這種即席分析至少要分鐘級。ES 存儲畫像數(shù)據的索引結構設計上常見做法是每個標簽一個字段或者用 nested 對象存標簽數(shù)組。前者適合標簽數(shù)量少、結構固定的場景后者適合標簽動態(tài)擴展的場景。索引設計時一定要給 tag_id 字段設置 keyword 類型否則分詞后無法精確匹配。5. 標簽數(shù)據同步避坑四個我實測過的生產環(huán)境問題5.1 Hive 同步 Hbase 后數(shù)據量對不上現(xiàn)象Hive 表統(tǒng)計有 5000 萬條同步到 Hbase 后 count 只有 1000 萬條。原因排查下來最常見的是 rowkey 設計沖突。Hbase 的 rowkey 如果只取 user_id那么同一用戶的多條標簽記錄會互相覆蓋導致數(shù)據行數(shù)變少。解決方式分兩步第一確認目標表的 rowkey 是否包含區(qū)分字段同步前先預估同一 user_id 最多有幾條記錄用 user_id 標簽類別或時間戳拼 rowkey第二同步完成后先查 Hbase 中目標表的行數(shù)再查源 Hive 表行數(shù)兩者偏差超過 1% 時觸發(fā)告警而不是等業(yè)務方反饋數(shù)據缺失。5.2 分區(qū)字段寫錯導致標簽數(shù)據覆蓋現(xiàn)象某天標簽計算結果異常發(fā)現(xiàn)大量用戶標簽丟失。原因同一個 tag 表按日期和標簽主題分區(qū)寫 SQL 時把分區(qū)字段 tag_type 寫成了另一個主題的值插入后覆蓋了同主題前一天的數(shù)據。尤其是使用了 INSERT OVERWRITE 的分區(qū)表一旦分區(qū)條件寫錯不會有任何報錯數(shù)據就靜默覆蓋了。解決所有分區(qū)表寫入腳本里強制校驗 data_date 和 tag_type 是否為當天預期的值腳本里寫死日期變量不允許用 current_date 之類的動態(tài)值上線前先 SELECT COUNT(*) 看一眼目標分區(qū)今天是否有數(shù)據有數(shù)據立刻停。5.3 MySQL 同步時主鍵沖突導致任務卡死現(xiàn)象Sqoop 同步 Hive 數(shù)據到 MySQL跑了一小時后任務失敗報 duplicate entry。原因MySQL 目標表主鍵設置不合理Hive 側同一 user_id 出現(xiàn)重復導致 MySQL 插入沖突。解決在 Sqoop 命令里加 update-key 參數(shù)把 sync 模式改為 upsert同時檢查源 Hive 表是否有重復有重復就在導出 SQL 里先去重。這條經驗也適用于 Python 腳本同步——腳本里必須寫 INSERT ... ON DUPLICATE KEY UPDATE 而不是 INSERT。5.4 校驗標志位與數(shù)據寫入不在同一事務里現(xiàn)象狀態(tài)表顯示當天數(shù)據校驗通過但線上接口讀到的還是舊數(shù)據。原因Hbase 數(shù)據先寫入正式表再寫狀態(tài)表兩步之間沒有原子性。如果中間有短暫的時間窗口狀態(tài)表里是當天最新日期但 Hbase 正式表還在寫入過程中線上的查詢就可能讀到半新半舊的數(shù)據。解決把寫入順序反過來先寫狀態(tài)表再寫 Hbase 正式表或者利用 Hbase 的 timestamp 版本控制線上讀取時只取 status 表標記的日期之前的數(shù)據。更穩(wěn)妥的方案是直接用 Hbase 的 coprocessor 或異步批處理框架把狀態(tài)更新和數(shù)據寫入包裝在同一個任務里失敗時統(tǒng)一回滾。6. 數(shù)據量校驗腳本與臨時表雙寫基線不重跑全量作業(yè)的妥協(xié)方案前面講了那么多表結構和同步鏈路最后分享一個我一直在用的具體技巧如何高效做 Hive 到 Hbase 的日常數(shù)據校驗而不用每次重跑全量作業(yè)。先說基線。畫像系統(tǒng)里Hive 中的用戶標簽表每天都在增量更新但 Hbase 中的線上標簽表通常是全量覆蓋邏輯。全量覆蓋的問題在于每次同步都要把全部用戶的數(shù)據推一遍隨著用戶量增長同步時間越來越長。我的習慣是建一張基線表Hive 里維護一張最基礎的“離線用戶全量表”記錄 user_id、最近活躍日期、核心標簽摘要。同步 Hbase 時只同步當天有變化的用戶沒變化的用戶繼續(xù)沿用前一天 Hbase 里的數(shù)據。這樣把全量同步變成了增量同步同步時間從兩小時降到二十分鐘。增量同步帶來的新問題是怎么確認 Hbase 里的最終數(shù)據等于 Hive 全量數(shù)據的期望結果我用的校驗策略是雙層比對import happybase from pyhive import hive # 1. 從 Hive 查詢當天應覆蓋的總用戶數(shù) conn hive.Connection(hosthive-server, port10000) cursor conn.cursor() cursor.execute( SELECT count(*) FROM dw.profile_tag_userid WHERE data_date 2024-05-20 ) hive_cnt cursor.fetchone()[0] # 2. 從 Hbase 查詢當天同步后實際覆蓋的用戶數(shù) pool happybase.ConnectionPool(size3, hosthbase-server) with pool.connection() as hbase_conn: table hbase_conn.table(profile_tag_user) # 當天同步的用戶都會帶上日期前綴 rowkey hbase_cnt 0 for _ in table.scan(row_prefixb2024-05-20): hbase_cnt 1 # 3. 計算偏差率超過閾值則寫告警狀態(tài)不更新狀態(tài)表 diff_ratio abs(hive_cnt - hbase_cnt) / max(hive_cnt, 1) if diff_ratio 0.01: print(f數(shù)據量偏差率達到 {diff_ratio:.2%}, 觸發(fā)告警)這段腳本的邏輯說明先從 Hive 拿到當天的應覆蓋用戶總量再根據 rowkey 前綴從 Hbase 統(tǒng)計實際寫入量偏差率超過 1% 就觸發(fā)告警。這種做法比單獨依賴 Sqoop 日志或 Hbase 的計數(shù)統(tǒng)計更可靠因為它是基于 SQL 語義和數(shù)據落盤結果的雙向驗證。參數(shù)上要關注兩個點。一個是 Hbase 的 scan 超時設置用戶量大時全表 scan 會很慢可以把 row_prefix 設計成按天加用戶 id 哈希前綴這樣每天的同步記錄分布在固定前綴下scan 范圍可控。另一個是校驗的觸發(fā)時機不要等 Hbase 同步作業(yè)完成之后立刻校驗建議加一個三分鐘的延遲確認 Hbase 的寫入已經完成且 region 沒有在分裂合并時再校驗減少誤報。這個方案不完美的地方在于臨時表雙寫會多耗一倍 Hbase 存儲空間。所以我在生產里不會天天用臨時表只在版本升級、計算口徑調整、Hbase 集群擴容后做一次全量雙寫校驗。日常增量同步就靠狀態(tài)表 數(shù)據量偏差告警兩條線兜底。從那以后我每次搭畫像系統(tǒng)都強制走一遍“Hive 明細落分區(qū)、校驗表盯波動、同步鏈路加狀態(tài)位、線下腳本做抽檢”這條流程少一步心里都不踏實。數(shù)據量對不上這種問題一旦漏到線上排查成本是同步成本的幾十倍。這套方案里最值得學的不是某個具體的 SQL而是那套“兩種校驗方案并行”的思路——寧可多寫一個臨時表也別讓臟數(shù)據在線上裸奔。希望幫到你。本文還有配套的精品資源點擊獲取