據(jù)中臺四段實戰(zhàn)：埋點采集、ODS到ADS分層與標(biāo)簽打通)
簡介《數(shù)據(jù)中臺實戰(zhàn)手把手教你搭建數(shù)據(jù)中臺》電子書面向企業(yè)管理者、產(chǎn)品經(jīng)理、運營設(shè)計開發(fā)人員及數(shù)據(jù)領(lǐng)域?qū)W習(xí)者聚焦數(shù)據(jù)中臺從概念認(rèn)知到落地搭建的完整路徑幫助讀者判斷企業(yè)是否需要中臺并掌握采集、存儲、打通、應(yīng)用四個核心環(huán)節(jié)。壓縮包為PDF格式僅含1個文件體積約11.3MB便于在電腦或移動設(shè)備上閱讀與檢索。目前已有402人學(xué)習(xí)下載具備一定關(guān)注度。書中結(jié)合作者在大型企業(yè)參與中臺從0到1建設(shè)的實戰(zhàn)經(jīng)歷圍繞數(shù)據(jù)埋點、指標(biāo)管理、分層建模、標(biāo)簽平臺、用戶與商品流量交易分析、自助分析平臺及自動化營銷等模塊展開配有較多案例與方法論可幫助讀者理解中臺建設(shè)的適用條件、實施流程和常見陷阱也為中臺產(chǎn)品經(jīng)理及企業(yè)數(shù)據(jù)化轉(zhuǎn)型項目提供可操作的實踐參考。1. 先別急著買引擎這份大數(shù)據(jù)中臺資料該在什么階段翻開很多團(tuán)隊一上大數(shù)據(jù)中臺先買計算引擎、再拉埋點最后發(fā)現(xiàn)指標(biāo)口徑對不上看板越做越多決策卻越來越慢。這份數(shù)據(jù)中臺實戰(zhàn)資料的價值不在概念而在它把采集、存儲、打通、應(yīng)用四段拆成了能照著做的工序埋點表怎么設(shè)計、ODS 到 ADS 怎么分層、標(biāo)簽平臺怎么從寬表長出來、交易看板怎么給不同角色用。適合正在做數(shù)據(jù)化轉(zhuǎn)型的產(chǎn)品、運營、開發(fā)和準(zhǔn)備面試數(shù)據(jù)中臺崗位的人不適合只有一條產(chǎn)品線、數(shù)據(jù)量還沒起來的初創(chuàng)團(tuán)隊。讀的時候別從第一頁順讀先拿自己公司的一條業(yè)務(wù)線做對照缺哪段補哪段。2. 數(shù)據(jù)采集埋點表、業(yè)務(wù)庫同步與 T-1 鏈路的落地拆解采集做不扎實后面所有分層建模都是空中樓閣。資料里反復(fù)強調(diào)業(yè)務(wù)中臺和數(shù)據(jù)中臺是兩條腿前者把通用業(yè)務(wù)模塊收口后者把跨產(chǎn)品線數(shù)據(jù)收口沒有業(yè)務(wù)中臺也能做數(shù)據(jù)中臺但采集階段要多跑幾條產(chǎn)品線做調(diào)研溝通成本會高很多。落到執(zhí)行采集不是“把日志丟進(jìn)數(shù)倉”這么簡單至少要同時管住三件事埋點事件有沒有統(tǒng)一 schema、業(yè)務(wù)庫同步有沒有可重跑的 T-1 鏈路、采集結(jié)果有沒有驗收標(biāo)準(zhǔn)。2.1 埋點事件表先定 schema再談采集埋點最容易翻車的地方是事件名和屬性名各寫各的同一個“提交訂單”A 產(chǎn)品線叫submit_orderB 產(chǎn)品線叫order_commit屬性里一個用order_id一個用orderNo。資料里的做法是先定事件命名規(guī)范再讓開發(fā)按表填報。常見命名是“業(yè)務(wù)域_對象_動作”比如trade_order_submit、goods_detail_view、cart_add_click屬性分公共屬性和業(yè)務(wù)屬性公共屬性由 SDK 統(tǒng)一帶上業(yè)務(wù)屬性由埋點方案聲明。事件表不要一上來就寬表化先保證一行一個事件、事件 ID 可去重、時間字段統(tǒng)一到服務(wù)端時間。-- 埋點事件明細(xì)表按天分區(qū)properties 存業(yè)務(wù)屬性 CREATE TABLE IF NOT EXISTS ods_user_event ( event_id STRING COMMENT 事件唯一ID前端生成服務(wù)端補發(fā), event_name STRING COMMENT 事件名域_對象_動作, user_id STRING COMMENT 登錄用戶ID未登錄為空, anonymous_id STRING COMMENT 設(shè)備匿名ID未登錄時必填, session_id STRING COMMENT 會話ID, page_id STRING COMMENT 頁面ID, module_id STRING COMMENT 模塊ID, platform STRING COMMENT 端web/app/mini, app_version STRING COMMENT 客戶端版本, server_ts BIGINT COMMENT 服務(wù)端接收時間戳毫秒, properties STRING COMMENT JSON屬性限制2KB以內(nèi), dt STRING COMMENT 分區(qū)日期 yyyy-MM-dd ) PARTITIONED BY (dt) STORED AS PARQUET;這段建表語句的關(guān)鍵不是語法而是把“可去重”和“可分區(qū)”提前寫進(jìn) schema。event_id用來處理網(wǎng)絡(luò)重試導(dǎo)致的重復(fù)上報user_id和anonymous_id至少有一個不為空否則后續(xù)無法做用戶行為串聯(lián)server_ts不要用客戶端時間代替客戶端時間容易被改properties限制大小避免有人把整個商品對象塞進(jìn)埋點。分區(qū)字段dt按服務(wù)端接收日期落跨天補發(fā)的事件要單獨標(biāo)記否則日報會對不上。埋點方案評審時我一般會強迫產(chǎn)品把下面這張表填完填不完就不排期。事件名、觸發(fā)時機、必填屬性、屬性類型、責(zé)任人、驗收指標(biāo)這六列缺一不可。尤其是“觸發(fā)時機”要寫到“點擊提交按鈕且接口返回成功后上報”不能寫“用戶下單時”。很多轉(zhuǎn)化率異常不是數(shù)倉算錯而是前端在按鈕點擊時就上報接口失敗也算了一筆。字段示例約束事件名trade_order_submit小寫字母下劃線最長 48 字符觸發(fā)時機提交訂單接口返回成功不能寫“用戶下單時”必填屬性order_id、pay_amount、goods_id空值率超過 1% 阻斷發(fā)布屬性類型pay_amount為 decimal(16,2)金額統(tǒng)一單位元責(zé)任人某業(yè)務(wù)線產(chǎn)品負(fù)責(zé)人變更需同步數(shù)據(jù)團(tuán)隊驗收指標(biāo)日事件量、UV、空值率、重復(fù)率發(fā)布后 24 小時看板核對埋點驗收不要靠人眼看日志直接跑 SQL 看四個數(shù)事件量、去重后事件量、必填屬性空值率、event_id重復(fù)率。下面這段查詢按事件名聚合適合放進(jìn)每日調(diào)度超過閾值就告警。參數(shù)上dt取業(yè)務(wù)日期event_name可以先限定核心轉(zhuǎn)化事件不用全量跑重復(fù)率閾值我一般設(shè) 0.5%空值率設(shè) 1%超過就回滾 SDK 版本或修埋點方案不要先去改數(shù)倉清洗邏輯。-- 埋點驗收核心事件量、空值率、重復(fù)率 SELECT event_name, COUNT(1) AS pv, COUNT(DISTINCT event_id) AS pv_dedup, ROUND(1 - COUNT(DISTINCT event_id) / COUNT(1), 4) AS dup_rate, ROUND(SUM(CASE WHEN user_id IS NULL AND anonymous_id IS NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS id_null_rate, ROUND(SUM(CASE WHEN properties IS NULL OR properties THEN 1 ELSE 0 END) / COUNT(1), 4) AS prop_null_rate FROM ods_user_event WHERE dt ${biz_date} AND event_name IN (trade_order_submit, goods_detail_view, cart_add_click) GROUP BY event_name;提示埋點驗收不通過時先回滾 SDK 版本再查網(wǎng)絡(luò)重試和頁面曝光重復(fù)上報不要直接改數(shù)倉邏輯把異常藏掉。2.2 業(yè)務(wù)庫同步與日志采集的 T-1 鏈路業(yè)務(wù)庫數(shù)據(jù)是交易、訂單、商品、用戶這些“結(jié)果數(shù)據(jù)”埋點日志是瀏覽、點擊、加購這些“過程數(shù)據(jù)”。資料里把采集鏈路分成兩條業(yè)務(wù)庫走實時同步加 T-1 全量校對日志走消息隊列落地后按天分區(qū)。常見做法是業(yè)務(wù)庫變更用 binlog 追增量每天凌晨再跑一次全量或增量校對防止漏數(shù)日志采集在前端埋點后先落消息隊列再離線落到對象存儲或分布式文件系統(tǒng)按dt分區(qū)。兩條鏈路最后都收斂到 ODS 層表名和責(zé)任人登記到元數(shù)據(jù)表里。調(diào)度腳本不要寫成“一把梭”至少把業(yè)務(wù)日期、任務(wù)名、重跑標(biāo)記、失敗告警四個參數(shù)留出來。下面是一個通用 T-1 同步腳本骨架job_config指向同步任務(wù)配置biz_date由調(diào)度系統(tǒng)傳入rerun用來控制是否覆蓋分區(qū)。腳本里先做源表行數(shù)探查再執(zhí)行同步最后寫一條元數(shù)據(jù)日志這樣出問題時能查到是源表空了還是同步任務(wù)失敗了。#!/bin/bash set -euo pipefail BIZ_DATE${1:?業(yè)務(wù)日期不能為空格式 yyyy-MM-dd} JOB_CONFIG${2:?同步任務(wù)配置不能為空} RERUN${3:-false} # 1. 同步前探查源表行數(shù)結(jié)果寫入臨時文件 python /opt/sync/bin/check_source.py \ --config ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --output /tmp/source_count_${BIZ_DATE}.json # 2. 執(zhí)行同步reruntrue 時覆蓋目標(biāo)分區(qū) python /opt/sync/bin/run_job.py \ --config ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --mode overwrite \ --rerun ${RERUN} # 3. 寫元數(shù)據(jù)日志任務(wù)名、業(yè)務(wù)日期、源行數(shù)、目標(biāo)行數(shù)、狀態(tài) python /opt/sync/bin/write_meta.py \ --job ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --status success參數(shù)說明上BIZ_DATE是業(yè)務(wù)日期不是執(zhí)行日期很多團(tuán)隊凌晨跑數(shù)時把執(zhí)行日期當(dāng)業(yè)務(wù)日期導(dǎo)致 T-1 數(shù)據(jù)錯位一天。RERUN只在補數(shù)時傳true日常調(diào)度傳false避免誤覆蓋。同步任務(wù)配置里要明確源表、目標(biāo)表、主鍵、增量字段、分區(qū)字段、字段映射主鍵用于去重增量字段用于追變更分區(qū)字段用于重跑。日志采集側(cè)則要在 ODS 建外部表或分區(qū)表按dt掛載不要每天改表結(jié)構(gòu)。采集完成后必須登記元數(shù)據(jù)不然三個月后沒人知道ods_order是誰負(fù)責(zé)、幾點更新、下游有誰用。資料里把中臺團(tuán)隊拆成十類角色從負(fù)責(zé)人、產(chǎn)品、數(shù)據(jù)開發(fā)、分析師到測試和運維采集階段至少要有業(yè)務(wù)責(zé)任人、數(shù)據(jù)開發(fā)責(zé)任人、運維責(zé)任人三方簽字。元數(shù)據(jù)表不用復(fù)雜下面這幾列就夠用表名、來源系統(tǒng)、更新頻率、SLA、主鍵、責(zé)任人、下游任務(wù)。缺元數(shù)據(jù)的表我一般不允許進(jìn) DWD寧可先卡在 ODS。元數(shù)據(jù)項示例用途表名ods_order唯一標(biāo)識來源系統(tǒng)某業(yè)務(wù)線訂單庫排查源端問題更新頻率每日 T-109:00 前約定 SLA主鍵order_id去重與對賬責(zé)任人某數(shù)據(jù)開發(fā)變更聯(lián)系人下游任務(wù)dwd_trade_order影響面分析2.3 采集驗收量、質(zhì)、時三層卡點采集驗收要卡三層量、質(zhì)、時。量看日環(huán)比和周同比核心事件量突然掉 30% 或漲 50% 都要查質(zhì)看主鍵重復(fù)、必填字段空值、枚舉值越界時看 SLA比如每天 09:00 前 ODS 分區(qū)必須就緒晚了就觸發(fā)告警。資料里很多實戰(zhàn)案例都指向一個結(jié)論采集階段放過的小問題到應(yīng)用層會變成大問題。比如訂單表里混入測試訂單交易看板會多算埋點里platform出現(xiàn)unknown流量分析會少一塊日志補發(fā)沒有標(biāo)記日報和實時看板會對不上。采集驗收 SQL 可以固定成模板每個 ODS 表接進(jìn)來時改表名和主鍵即可。下面這段檢查主鍵重復(fù)、分區(qū)行數(shù)、關(guān)鍵字段空值結(jié)果輸出到質(zhì)量表調(diào)度系統(tǒng)根據(jù)status決定是否阻斷下游。參數(shù)上pk換成實際主鍵dt用業(yè)務(wù)日期min_rows設(shè)一個業(yè)務(wù)可接受的下限比如訂單表日行數(shù)不低于 1000低于就告警而不是直接阻斷避免節(jié)假日誤殺。-- 采集質(zhì)量檢查主鍵重復(fù)、行數(shù)下限、關(guān)鍵字段空值 SELECT ods_order AS table_name, ${biz_date} AS dt, COUNT(1) AS row_cnt, COUNT(1) - COUNT(DISTINCT order_id) AS pk_dup_cnt, SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS pk_null_cnt, SUM(CASE WHEN pay_amount IS NULL THEN 1 ELSE 0 END) AS amount_null_cnt, CASE WHEN COUNT(1) 1000 THEN WARN WHEN COUNT(1) - COUNT(DISTINCT order_id) 0 THEN FAIL WHEN SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) 0 THEN FAIL ELSE PASS END AS status FROM ods_order WHERE dt ${biz_date};這一層做完采集才算有“黑匣子”可查。后面指標(biāo)對不上時先看 ODS 行數(shù)和主鍵再看 DWD 清洗規(guī)則最后才懷疑匯總邏輯。順序反了會浪費很多時間。3. 存儲與計算ODS 到 ADS 的分層建模和指標(biāo)口徑怎么落表分層建模最怕做成表名游戲ODS、DWD、DWS 建了一堆業(yè)務(wù)一問“昨天支付金額為什么和財務(wù)對不上”還是答不出來。資料里的分層不是照搬概念而是把“原始數(shù)據(jù)、清洗明細(xì)、主題匯總、應(yīng)用結(jié)果”分開讓每層只干一件事。ODS 保留原樣方便回溯DWD 做清洗、去重、統(tǒng)一字段DWS 按主題做輕度匯總ADS 面向看板和接口。層級越往下越穩(wěn)定越往上越貼近業(yè)務(wù)變更頻率也越高。3.1 分層邊界每層只做該做的事ODS 層不要做復(fù)雜清洗尤其不要在這里過濾測試訂單或修改金額否則回溯時找不到原始值。DWD 層負(fù)責(zé)把 ODS 的臟數(shù)據(jù)變成可信明細(xì)去重、過濾測試賬號、統(tǒng)一金額單位、補全維度、解析 JSON 屬性。DWS 層按主題匯總比如交易主題按日、品類、渠道匯總支付金額和退款金額用戶主題按日匯總活躍、下單、復(fù)購。ADS 層直接服務(wù)看板和接口可以寬表化但要在指標(biāo)口徑固定之后再做。選型上離線鏈路優(yōu)先保證可重跑和可對賬實時鏈路只承接真正需要秒級響應(yīng)的場景。資料里把采集、存儲、打通、應(yīng)用串起來但并沒有鼓勵所有表都實時化。交易看板 T1 足夠?qū)崟r大屏才需要流式計算標(biāo)簽平臺大部分標(biāo)簽 T1 更新只有“最近 1 小時活躍”這類標(biāo)簽走實時。分層表建議統(tǒng)一按天分區(qū)小文件多的任務(wù)加合并步驟冷數(shù)據(jù)按保留周期歸檔避免存儲成本失控。層級職責(zé)典型表更新頻率禁止事項ODS原樣落地、可回溯ods_order、ods_user_event每日/實時不做業(yè)務(wù)過濾DWD清洗、去重、統(tǒng)一口徑dwd_trade_order每日不直接出看板DWS主題匯總、輕度聚合dws_trade_day每日不堆過多維度ADS看板、接口、人群包ads_trade_board每日/按需不反寫明細(xì)注意指標(biāo)口徑變更必須留版本否則歷史看板會集體失真。DWD 的過濾條件一改DWS 和 ADS 都要重跑不能只改最上層。3.2 指標(biāo)管理體系一個指標(biāo)一條 YAML指標(biāo)口徑對不上根因通常不是 SQL 寫錯而是同一個詞在不同團(tuán)隊嘴里含義不同。資料里強調(diào)指標(biāo)要有業(yè)務(wù)口徑、技術(shù)口徑、計算周期、計算方式、責(zé)任人。我的做法是一個指標(biāo)一條 YAML進(jìn) Git 管理變更走評審。指標(biāo)碼用“域_主題_度量_周期”比如trade_order_pay_amt_1d業(yè)務(wù)口徑寫清楚“支付成功訂單金額扣除退款不含測試訂單”技術(shù)口徑寫清楚源表、過濾條件、去重主鍵、金額單位維度列出常用切分比如日期、品類、渠道、產(chǎn)品線。metric_code: trade_order_pay_amt_1d name: 日支付訂單金額 biz_caliber: 支付成功訂單金額扣除當(dāng)日退款不含測試訂單和風(fēng)控攔截訂單 tech_caliber: dwd_trade_order 按 order_id 去重 pay_status PAID order_type TEST pay_amount 單位為元保留兩位小數(shù) owner: 某交易數(shù)據(jù)負(fù)責(zé)人 period: 1d dimensions: - dt - category_id - channel_id - product_line source_table: dwd_trade_order filter: pay_status PAID AND order_type TEST version: v1.2這份 YAML 的關(guān)鍵是“可執(zhí)行”。biz_caliber不是寫給領(lǐng)導(dǎo)看的而是給開發(fā)和分析師對照的tech_caliber要能直接翻譯成 SQLdimensions決定 DWS 建哪些匯總維度version讓歷史看板能追到口徑版本。指標(biāo)變更時不要直接改原文件新增版本并保留舊版本ADS 表按版本號切換。否則一個“支付金額”改了口徑歷史報表全部對不上業(yè)務(wù)方會直接不信數(shù)據(jù)。指標(biāo)管理還有一條血淚經(jīng)驗不要讓每個看板自己寫 SQL 算指標(biāo)?？窗逯幌M ADS 或指標(biāo)服務(wù)指標(biāo)服務(wù)背后統(tǒng)一走 YAML 定義。這樣新看板接入時不用重新討論口徑直接選指標(biāo)碼和維度即可。自助分析平臺也一樣業(yè)務(wù)人員可以拖拽維度但指標(biāo)必須是注冊過的不能讓他們在頁面上隨便寫sum(pay_amount)。3.3 從 ODS 到 ADS 的 SQL 骨架先看 DWD 清洗。下面這段 SQL 從 ODS 訂單表取數(shù)按order_id去重過濾測試訂單統(tǒng)一金額單位補上品類和渠道維度。參數(shù)上dt是業(yè)務(wù)日期row_number()按update_time倒序取最新一條處理業(yè)務(wù)庫同一訂單多次變更pay_amount如果源表是分要除以 100這里假設(shè)源表已經(jīng)是元。測試訂單的識別條件用order_type TEST不要用訂單號前綴猜規(guī)則要寫在指標(biāo) YAML 里。-- DWD 訂單明細(xì)去重、過濾測試單、統(tǒng)一金額單位 INSERT OVERWRITE TABLE dwd_trade_order PARTITION (dt ${biz_date}) SELECT order_id, user_id, product_line, category_id, channel_id, pay_status, pay_amount, refund_amount, create_time, update_time FROM ( SELECT order_id, user_id, product_line, category_id, channel_id, pay_status, CAST(pay_amount AS DECIMAL(16,2)) AS pay_amount, CAST(refund_amount AS DECIMAL(16,2)) AS refund_amount, create_time, update_time, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn FROM ods_order WHERE dt ${biz_date} AND order_type TEST ) t WHERE rn 1;DWS 匯總按日、品類、渠道、產(chǎn)品線聚合支付金額和退款金額分開算最后在 ADS 里做凈額。這樣退款政策變化時不用重跑 DWD只改匯總邏輯。下面這段 SQL 的pay_amt只統(tǒng)計支付成功訂單refund_amt統(tǒng)計當(dāng)日退款net_amt是凈額。參數(shù)上dt分區(qū)對齊category_id和channel_id允許為空空值單獨歸到“未知”維度不要直接丟掉否則大盤對不上。-- DWS 交易日匯總按品類、渠道、產(chǎn)品線 INSERT OVERWRITE TABLE dws_trade_day PARTITION (dt ${biz_date}) SELECT ${biz_date} AS dt, product_line, COALESCE(category_id, UNKNOWN) AS category_id, COALESCE(channel_id, UNKNOWN) AS channel_id, COUNT(DISTINCT CASE WHEN pay_status PAID THEN order_id END) AS pay_order_cnt, SUM(CASE WHEN pay_status PAID THEN pay_amount ELSE 0 END) AS pay_amt, SUM(CASE WHEN refund_amount 0 THEN refund_amount ELSE 0 END) AS refund_amt, SUM(CASE WHEN pay_status PAID THEN pay_amount ELSE 0 END) - SUM(CASE WHEN refund_amount 0 THEN refund_amount ELSE 0 END) AS net_amt FROM dwd_trade_order WHERE dt ${biz_date} GROUP BY product_line, COALESCE(category_id, UNKNOWN), COALESCE(channel_id, UNKNOWN);ADS 看板表可以直接從 DWS 取數(shù)按角色拆指標(biāo)。領(lǐng)導(dǎo)層看大盤凈額、同比環(huán)比運營看品類、渠道、退款率產(chǎn)品看轉(zhuǎn)化漏斗。下面這段只做一張交易看板樣例net_amt和refund_rate是核心指標(biāo)。參數(shù)上dt用業(yè)務(wù)日期refund_rate分母為 0 時要返回 0避免除零同比環(huán)比可以在調(diào)度里另算不要在看板查詢時實時算否則數(shù)據(jù)量大時頁面會卡。-- ADS 交易看板大盤核心指標(biāo) INSERT OVERWRITE TABLE ads_trade_board PARTITION (dt ${biz_date}) SELECT dt, SUM(pay_amt) AS pay_amt, SUM(refund_amt) AS refund_amt, SUM(net_amt) AS net_amt, SUM(pay_order_cnt) AS pay_order_cnt, CASE WHEN SUM(pay_amt) 0 THEN 0 ELSE ROUND(SUM(refund_amt) / SUM(pay_amt), 4) END AS refund_rate FROM dws_trade_day WHERE dt ${biz_date} GROUP BY dt;跑完還要做對賬。至少查三項DWD 主鍵是否重復(fù)、DWS 匯總金額是否等于 DWD 明細(xì)金額、ADS 展示金額是否等于 DWS 匯總金額。下面這段對賬 SQL 輸出差異行差異不為 0 就阻斷下游看板發(fā)布。參數(shù)上${biz_date}同一天金額比較允許 0.01 的浮點誤差超過就查清洗規(guī)則或匯總維度是否丟數(shù)。-- 交易鏈路對賬DWD 明細(xì) vs DWS 匯總 SELECT d.dt, d.pay_amt AS dwd_pay_amt, s.pay_amt AS dws_pay_amt, d.pay_amt - s.pay_amt AS diff_amt FROM ( SELECT dt, SUM(pay_amount) AS pay_amt FROM dwd_trade_order WHERE dt ${biz_date} AND pay_status PAID GROUP BY dt ) d LEFT JOIN ( SELECT dt, SUM(pay_amt) AS pay_amt FROM dws_trade_day WHERE dt ${biz_date} GROUP BY dt ) s ON d.dt s.dt WHERE ABS(COALESCE(d.pay_amt,0) - COALESCE(s.pay_amt,0)) 0.01;4. 數(shù)據(jù)打通標(biāo)簽平臺從寬表到人群圈選的最小閉環(huán)標(biāo)簽平臺不是把用戶屬性拼成一張大寬表就完事真正難的是身份映射和標(biāo)簽更新節(jié)奏。資料里把標(biāo)簽平臺放在“打通”環(huán)節(jié)是因為用戶行為數(shù)據(jù)和業(yè)務(wù)數(shù)據(jù)往往存在不同產(chǎn)品線、不同系統(tǒng)里行為數(shù)據(jù)知道用戶點了什么業(yè)務(wù)數(shù)據(jù)知道用戶買了什么兩邊對不上用戶畫像就是殘缺的。打通的目標(biāo)不是把所有字段都塞進(jìn)一張表而是讓“用戶是誰、干了什么、買了什么”能在同一個用戶 ID 下被查詢和圈選。4.1 標(biāo)簽體系先分三層基礎(chǔ)、行為、業(yè)務(wù)標(biāo)簽體系建議先分三層基礎(chǔ)標(biāo)簽、行為標(biāo)簽、業(yè)務(wù)標(biāo)簽?；A(chǔ)標(biāo)簽是注冊天數(shù)、會員等級、所在城市等級這類變化慢的屬性行為標(biāo)簽是近 30 天點擊品類數(shù)、近 7 天訪問頻次、最近一次活躍時間業(yè)務(wù)標(biāo)簽是首單金額、近 90 天訂單數(shù)、退款次數(shù)。每類標(biāo)簽都要有元數(shù)據(jù)標(biāo)簽碼、名稱、類型、更新方式、源表、責(zé)任人、有效期。不要一上來做幾百個標(biāo)簽先做 20 個業(yè)務(wù)真用的跑通“寬表生成—標(biāo)簽計算—人群圈選—營銷調(diào)用”閉環(huán)。寬表按天分區(qū)只放常用標(biāo)簽長尾標(biāo)簽按需計算。下面建表語句把用戶 ID、基礎(chǔ)標(biāo)簽、行為標(biāo)簽、業(yè)務(wù)標(biāo)簽放在一張日分區(qū)表里dt是標(biāo)簽日期user_id是主鍵。參數(shù)上標(biāo)簽列允許為空空值代表“未知”而不是“否”更新方式用全量覆蓋避免增量 merge 寫錯導(dǎo)致標(biāo)簽漂移。寬表字段不要無限擴超過 100 列就拆成基礎(chǔ)寬表和行為寬表否則查詢和跑批都會變慢。-- 用戶標(biāo)簽寬表按天分區(qū)常用標(biāo)簽放一張表 CREATE TABLE IF NOT EXISTS dws_user_tag_wide ( user_id STRING COMMENT 統(tǒng)一用戶ID, register_days INT COMMENT 注冊天數(shù), member_level STRING COMMENT 會員等級, last_active_time STRING COMMENT 最近活躍時間, click_category_cnt_30d INT COMMENT 近30天點擊品類數(shù), visit_cnt_7d INT COMMENT 近7天訪問頻次, first_order_amt DECIMAL(16,2) COMMENT 首單金額, order_cnt_90d INT COMMENT 近90天訂單數(shù), refund_cnt_90d INT COMMENT 近90天退款次數(shù), dt STRING COMMENT 標(biāo)簽日期 yyyy-MM-dd ) PARTITIONED BY (dt) STORED AS PARQUET;標(biāo)簽計算不要每個標(biāo)簽一條獨立任務(wù)按源表分組批量算。行為標(biāo)簽從 DWD 事件表算業(yè)務(wù)標(biāo)簽從 DWD 訂單表算基礎(chǔ)標(biāo)簽從用戶維度表算最后按user_id全外連接。連接時注意空值行為表里沒有行為的用戶也要保留否則寬表只?；钴S用戶圈選“沉默用戶”就圈不出來。標(biāo)簽有效期也要寫進(jìn)元數(shù)據(jù)比如“近 30 天點擊品類數(shù)”每天重算“會員等級”每天同步過期標(biāo)簽要標(biāo)記不可用。4.2 身份映射打通產(chǎn)品線數(shù)據(jù)的硬骨頭跨產(chǎn)品線打通最難的是身份映射。同一個用戶在產(chǎn)品線 A 用手機號注冊在產(chǎn)品線 B 用設(shè)備號瀏覽在 product_line C 用會員 ID 下單如果沒有映射表三個 ID 就是三個人。常見做法是建一張身份映射表字段包括統(tǒng)一用戶 ID、手機號哈希、設(shè)備 ID、會員 ID、來源產(chǎn)品線、最近活躍時間、分區(qū)日期。優(yōu)先級一般是會員 ID 高于手機號哈希手機號哈希高于設(shè)備 ID沖突時按最近活躍時間保留人工可修正。手機號只存哈希不落明文設(shè)備 ID 也要做脫敏。-- 身份映射表多產(chǎn)品線 ID 歸一到 union_id CREATE TABLE IF NOT EXISTS dim_user_identity ( union_id STRING COMMENT 統(tǒng)一用戶ID, user_id STRING COMMENT 產(chǎn)品線內(nèi)用戶ID, phone_hash STRING COMMENT 手機號哈希不存明文, device_id STRING COMMENT 設(shè)備ID脫敏后存儲, member_id STRING COMMENT 會員ID, product_line STRING COMMENT 來源產(chǎn)品線, last_active STRING COMMENT 最近活躍時間, dt STRING COMMENT 分區(qū)日期 ) PARTITIONED BY (dt) STORED AS PARQUET;映射邏輯分兩步先按強 ID 配對手機號哈希相同或會員 ID 相同就歸到同一個union_id再用設(shè)備 ID 補弱關(guān)聯(lián)但設(shè)備 ID 沖突多不能單獨作為唯一依據(jù)。同一設(shè)備多賬號、同一手機號多產(chǎn)品線都要保留映射明細(xì)不能直接覆蓋。下面這段 SQL 是歸一的骨架ROW_NUMBER()按last_active倒序取最近一條COALESCE按優(yōu)先級選 ID。參數(shù)上dt用映射日期phone_hash要統(tǒng)一哈希算法和鹽值否則跨產(chǎn)品線對不上。-- 身份歸一強ID優(yōu)先設(shè)備ID兜底 INSERT OVERWRITE TABLE dim_user_identity PARTITION (dt ${biz_date}) SELECT CONCAT(U, MD5(CONCAT(COALESCE(phone_hash, member_id, device_id), ${salt}))) AS union_id, user_id, phone_hash, device_id, member_id, product_line, last_active, ${biz_date} AS dt FROM ( SELECT user_id, phone_hash, device_id, member_id, product_line, last_active, ROW_NUMBER() OVER ( PARTITION BY COALESCE(phone_hash, member_id, device_id) ORDER BY last_active DESC ) AS rn FROM ods_user_identity WHERE dt ${biz_date} ) t WHERE rn 1;提示身份映射表一定要保留來源產(chǎn)品線和最近活躍時間后續(xù)本文還有配套的精品資源點擊獲取 大數(shù)據(jù)中臺四段實戰(zhàn)：埋點采集、ODS到ADS分層與標(biāo)簽打通
簡介《數(shù)據(jù)中臺實戰(zhàn)手把手教你搭建數(shù)據(jù)中臺》電子書面向企業(yè)管理者、產(chǎn)品經(jīng)理、運營設(shè)計開發(fā)人員及數(shù)據(jù)領(lǐng)域?qū)W習(xí)者聚焦數(shù)據(jù)中臺從概念認(rèn)知到落地搭建的完整路徑幫助讀者判斷企業(yè)是否需要中臺并掌握采集、存儲、打通、應(yīng)用四個核心環(huán)節(jié)。壓縮包為PDF格式僅含1個文件體積約11.3MB便于在電腦或移動設(shè)備上閱讀與檢索。目前已有402人學(xué)習(xí)下載具備一定關(guān)注度。書中結(jié)合作者在大型企業(yè)參與中臺從0到1建設(shè)的實戰(zhàn)經(jīng)歷圍繞數(shù)據(jù)埋點、指標(biāo)管理、分層建模、標(biāo)簽平臺、用戶與商品流量交易分析、自助分析平臺及自動化營銷等模塊展開配有較多案例與方法論可幫助讀者理解中臺建設(shè)的適用條件、實施流程和常見陷阱也為中臺產(chǎn)品經(jīng)理及企業(yè)數(shù)據(jù)化轉(zhuǎn)型項目提供可操作的實踐參考。1. 先別急著買引擎這份大數(shù)據(jù)中臺資料該在什么階段翻開很多團(tuán)隊一上大數(shù)據(jù)中臺先買計算引擎、再拉埋點最后發(fā)現(xiàn)指標(biāo)口徑對不上看板越做越多決策卻越來越慢。這份數(shù)據(jù)中臺實戰(zhàn)資料的價值不在概念而在它把采集、存儲、打通、應(yīng)用四段拆成了能照著做的工序埋點表怎么設(shè)計、ODS 到 ADS 怎么分層、標(biāo)簽平臺怎么從寬表長出來、交易看板怎么給不同角色用。適合正在做數(shù)據(jù)化轉(zhuǎn)型的產(chǎn)品、運營、開發(fā)和準(zhǔn)備面試數(shù)據(jù)中臺崗位的人不適合只有一條產(chǎn)品線、數(shù)據(jù)量還沒起來的初創(chuàng)團(tuán)隊。讀的時候別從第一頁順讀先拿自己公司的一條業(yè)務(wù)線做對照缺哪段補哪段。2. 數(shù)據(jù)采集埋點表、業(yè)務(wù)庫同步與 T-1 鏈路的落地拆解采集做不扎實后面所有分層建模都是空中樓閣。資料里反復(fù)強調(diào)業(yè)務(wù)中臺和數(shù)據(jù)中臺是兩條腿前者把通用業(yè)務(wù)模塊收口后者把跨產(chǎn)品線數(shù)據(jù)收口沒有業(yè)務(wù)中臺也能做數(shù)據(jù)中臺但采集階段要多跑幾條產(chǎn)品線做調(diào)研溝通成本會高很多。落到執(zhí)行采集不是“把日志丟進(jìn)數(shù)倉”這么簡單至少要同時管住三件事埋點事件有沒有統(tǒng)一 schema、業(yè)務(wù)庫同步有沒有可重跑的 T-1 鏈路、采集結(jié)果有沒有驗收標(biāo)準(zhǔn)。2.1 埋點事件表先定 schema再談采集埋點最容易翻車的地方是事件名和屬性名各寫各的同一個“提交訂單”A 產(chǎn)品線叫submit_orderB 產(chǎn)品線叫order_commit屬性里一個用order_id一個用orderNo。資料里的做法是先定事件命名規(guī)范再讓開發(fā)按表填報。常見命名是“業(yè)務(wù)域_對象_動作”比如trade_order_submit、goods_detail_view、cart_add_click屬性分公共屬性和業(yè)務(wù)屬性公共屬性由 SDK 統(tǒng)一帶上業(yè)務(wù)屬性由埋點方案聲明。事件表不要一上來就寬表化先保證一行一個事件、事件 ID 可去重、時間字段統(tǒng)一到服務(wù)端時間。-- 埋點事件明細(xì)表按天分區(qū)properties 存業(yè)務(wù)屬性 CREATE TABLE IF NOT EXISTS ods_user_event ( event_id STRING COMMENT 事件唯一ID前端生成服務(wù)端補發(fā), event_name STRING COMMENT 事件名域_對象_動作, user_id STRING COMMENT 登錄用戶ID未登錄為空, anonymous_id STRING COMMENT 設(shè)備匿名ID未登錄時必填, session_id STRING COMMENT 會話ID, page_id STRING COMMENT 頁面ID, module_id STRING COMMENT 模塊ID, platform STRING COMMENT 端web/app/mini, app_version STRING COMMENT 客戶端版本, server_ts BIGINT COMMENT 服務(wù)端接收時間戳毫秒, properties STRING COMMENT JSON屬性限制2KB以內(nèi), dt STRING COMMENT 分區(qū)日期 yyyy-MM-dd ) PARTITIONED BY (dt) STORED AS PARQUET;這段建表語句的關(guān)鍵不是語法而是把“可去重”和“可分區(qū)”提前寫進(jìn) schema。event_id用來處理網(wǎng)絡(luò)重試導(dǎo)致的重復(fù)上報user_id和anonymous_id至少有一個不為空否則后續(xù)無法做用戶行為串聯(lián)server_ts不要用客戶端時間代替客戶端時間容易被改properties限制大小避免有人把整個商品對象塞進(jìn)埋點。分區(qū)字段dt按服務(wù)端接收日期落跨天補發(fā)的事件要單獨標(biāo)記否則日報會對不上。埋點方案評審時我一般會強迫產(chǎn)品把下面這張表填完填不完就不排期。事件名、觸發(fā)時機、必填屬性、屬性類型、責(zé)任人、驗收指標(biāo)這六列缺一不可。尤其是“觸發(fā)時機”要寫到“點擊提交按鈕且接口返回成功后上報”不能寫“用戶下單時”。很多轉(zhuǎn)化率異常不是數(shù)倉算錯而是前端在按鈕點擊時就上報接口失敗也算了一筆。字段示例約束事件名trade_order_submit小寫字母下劃線最長 48 字符觸發(fā)時機提交訂單接口返回成功不能寫“用戶下單時”必填屬性order_id、pay_amount、goods_id空值率超過 1% 阻斷發(fā)布屬性類型pay_amount為 decimal(16,2)金額統(tǒng)一單位元責(zé)任人某業(yè)務(wù)線產(chǎn)品負(fù)責(zé)人變更需同步數(shù)據(jù)團(tuán)隊驗收指標(biāo)日事件量、UV、空值率、重復(fù)率發(fā)布后 24 小時看板核對埋點驗收不要靠人眼看日志直接跑 SQL 看四個數(shù)事件量、去重后事件量、必填屬性空值率、event_id重復(fù)率。下面這段查詢按事件名聚合適合放進(jìn)每日調(diào)度超過閾值就告警。參數(shù)上dt取業(yè)務(wù)日期event_name可以先限定核心轉(zhuǎn)化事件不用全量跑重復(fù)率閾值我一般設(shè) 0.5%空值率設(shè) 1%超過就回滾 SDK 版本或修埋點方案不要先去改數(shù)倉清洗邏輯。-- 埋點驗收核心事件量、空值率、重復(fù)率 SELECT event_name, COUNT(1) AS pv, COUNT(DISTINCT event_id) AS pv_dedup, ROUND(1 - COUNT(DISTINCT event_id) / COUNT(1), 4) AS dup_rate, ROUND(SUM(CASE WHEN user_id IS NULL AND anonymous_id IS NULL THEN 1 ELSE 0 END) / COUNT(1), 4) AS id_null_rate, ROUND(SUM(CASE WHEN properties IS NULL OR properties THEN 1 ELSE 0 END) / COUNT(1), 4) AS prop_null_rate FROM ods_user_event WHERE dt ${biz_date} AND event_name IN (trade_order_submit, goods_detail_view, cart_add_click) GROUP BY event_name;提示埋點驗收不通過時先回滾 SDK 版本再查網(wǎng)絡(luò)重試和頁面曝光重復(fù)上報不要直接改數(shù)倉邏輯把異常藏掉。2.2 業(yè)務(wù)庫同步與日志采集的 T-1 鏈路業(yè)務(wù)庫數(shù)據(jù)是交易、訂單、商品、用戶這些“結(jié)果數(shù)據(jù)”埋點日志是瀏覽、點擊、加購這些“過程數(shù)據(jù)”。資料里把采集鏈路分成兩條業(yè)務(wù)庫走實時同步加 T-1 全量校對日志走消息隊列落地后按天分區(qū)。常見做法是業(yè)務(wù)庫變更用 binlog 追增量每天凌晨再跑一次全量或增量校對防止漏數(shù)日志采集在前端埋點后先落消息隊列再離線落到對象存儲或分布式文件系統(tǒng)按dt分區(qū)。兩條鏈路最后都收斂到 ODS 層表名和責(zé)任人登記到元數(shù)據(jù)表里。調(diào)度腳本不要寫成“一把梭”至少把業(yè)務(wù)日期、任務(wù)名、重跑標(biāo)記、失敗告警四個參數(shù)留出來。下面是一個通用 T-1 同步腳本骨架job_config指向同步任務(wù)配置biz_date由調(diào)度系統(tǒng)傳入rerun用來控制是否覆蓋分區(qū)。腳本里先做源表行數(shù)探查再執(zhí)行同步最后寫一條元數(shù)據(jù)日志這樣出問題時能查到是源表空了還是同步任務(wù)失敗了。#!/bin/bash set -euo pipefail BIZ_DATE${1:?業(yè)務(wù)日期不能為空格式 yyyy-MM-dd} JOB_CONFIG${2:?同步任務(wù)配置不能為空} RERUN${3:-false} # 1. 同步前探查源表行數(shù)結(jié)果寫入臨時文件 python /opt/sync/bin/check_source.py \ --config ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --output /tmp/source_count_${BIZ_DATE}.json # 2. 執(zhí)行同步reruntrue 時覆蓋目標(biāo)分區(qū) python /opt/sync/bin/run_job.py \ --config ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --mode overwrite \ --rerun ${RERUN} # 3. 寫元數(shù)據(jù)日志任務(wù)名、業(yè)務(wù)日期、源行數(shù)、目標(biāo)行數(shù)、狀態(tài) python /opt/sync/bin/write_meta.py \ --job ${JOB_CONFIG} \ --biz-date ${BIZ_DATE} \ --status success參數(shù)說明上BIZ_DATE是業(yè)務(wù)日期不是執(zhí)行日期很多團(tuán)隊凌晨跑數(shù)時把執(zhí)行日期當(dāng)業(yè)務(wù)日期導(dǎo)致 T-1 數(shù)據(jù)錯位一天。RERUN只在補數(shù)時傳true日常調(diào)度傳false避免誤覆蓋。同步任務(wù)配置里要明確源表、目標(biāo)表、主鍵、增量字段、分區(qū)字段、字段映射主鍵用于去重增量字段用于追變更分區(qū)字段用于重跑。日志采集側(cè)則要在 ODS 建外部表或分區(qū)表按dt掛載不要每天改表結(jié)構(gòu)。采集完成后必須登記元數(shù)據(jù)不然三個月后沒人知道ods_order是誰負(fù)責(zé)、幾點更新、下游有誰用。資料里把中臺團(tuán)隊拆成十類角色從負(fù)責(zé)人、產(chǎn)品、數(shù)據(jù)開發(fā)、分析師到測試和運維采集階段至少要有業(yè)務(wù)責(zé)任人、數(shù)據(jù)開發(fā)責(zé)任人、運維責(zé)任人三方簽字。元數(shù)據(jù)表不用復(fù)雜下面這幾列就夠用表名、來源系統(tǒng)、更新頻率、SLA、主鍵、責(zé)任人、下游任務(wù)。缺元數(shù)據(jù)的表我一般不允許進(jìn) DWD寧可先卡在 ODS。元數(shù)據(jù)項示例用途表名ods_order唯一標(biāo)識來源系統(tǒng)某業(yè)務(wù)線訂單庫排查源端問題更新頻率每日 T-109:00 前約定 SLA主鍵order_id去重與對賬責(zé)任人某數(shù)據(jù)開發(fā)變更聯(lián)系人下游任務(wù)dwd_trade_order影響面分析2.3 采集驗收量、質(zhì)、時三層卡點采集驗收要卡三層量、質(zhì)、時。量看日環(huán)比和周同比核心事件量突然掉 30% 或漲 50% 都要查質(zhì)看主鍵重復(fù)、必填字段空值、枚舉值越界時看 SLA比如每天 09:00 前 ODS 分區(qū)必須就緒晚了就觸發(fā)告警。資料里很多實戰(zhàn)案例都指向一個結(jié)論采集階段放過的小問題到應(yīng)用層會變成大問題。比如訂單表里混入測試訂單交易看板會多算埋點里platform出現(xiàn)unknown流量分析會少一塊日志補發(fā)沒有標(biāo)記日報和實時看板會對不上。采集驗收 SQL 可以固定成模板每個 ODS 表接進(jìn)來時改表名和主鍵即可。下面這段檢查主鍵重復(fù)、分區(qū)行數(shù)、關(guān)鍵字段空值結(jié)果輸出到質(zhì)量表調(diào)度系統(tǒng)根據(jù)status決定是否阻斷下游。參數(shù)上pk換成實際主鍵dt用業(yè)務(wù)日期min_rows設(shè)一個業(yè)務(wù)可接受的下限比如訂單表日行數(shù)不低于 1000低于就告警而不是直接阻斷避免節(jié)假日誤殺。-- 采集質(zhì)量檢查主鍵重復(fù)、行數(shù)下限、關(guān)鍵字段空值 SELECT ods_order AS table_name, ${biz_date} AS dt, COUNT(1) AS row_cnt, COUNT(1) - COUNT(DISTINCT order_id) AS pk_dup_cnt, SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS pk_null_cnt, SUM(CASE WHEN pay_amount IS NULL THEN 1 ELSE 0 END) AS amount_null_cnt, CASE WHEN COUNT(1) 1000 THEN WARN WHEN COUNT(1) - COUNT(DISTINCT order_id) 0 THEN FAIL WHEN SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) 0 THEN FAIL ELSE PASS END AS status FROM ods_order WHERE dt ${biz_date};這一層做完采集才算有“黑匣子”可查。后面指標(biāo)對不上時先看 ODS 行數(shù)和主鍵再看 DWD 清洗規(guī)則最后才懷疑匯總邏輯。順序反了會浪費很多時間。3. 存儲與計算ODS 到 ADS 的分層建模和指標(biāo)口徑怎么落表分層建模最怕做成表名游戲ODS、DWD、DWS 建了一堆業(yè)務(wù)一問“昨天支付金額為什么和財務(wù)對不上”還是答不出來。資料里的分層不是照搬概念而是把“原始數(shù)據(jù)、清洗明細(xì)、主題匯總、應(yīng)用結(jié)果”分開讓每層只干一件事。ODS 保留原樣方便回溯DWD 做清洗、去重、統(tǒng)一字段DWS 按主題做輕度匯總ADS 面向看板和接口。層級越往下越穩(wěn)定越往上越貼近業(yè)務(wù)變更頻率也越高。3.1 分層邊界每層只做該做的事ODS 層不要做復(fù)雜清洗尤其不要在這里過濾測試訂單或修改金額否則回溯時找不到原始值。DWD 層負(fù)責(zé)把 ODS 的臟數(shù)據(jù)變成可信明細(xì)去重、過濾測試賬號、統(tǒng)一金額單位、補全維度、解析 JSON 屬性。DWS 層按主題匯總比如交易主題按日、品類、渠道匯總支付金額和退款金額用戶主題按日匯總活躍、下單、復(fù)購。ADS 層直接服務(wù)看板和接口可以寬表化但要在指標(biāo)口徑固定之后再做。選型上離線鏈路優(yōu)先保證可重跑和可對賬實時鏈路只承接真正需要秒級響應(yīng)的場景。資料里把采集、存儲、打通、應(yīng)用串起來但并沒有鼓勵所有表都實時化。交易看板 T1 足夠?qū)崟r大屏才需要流式計算標(biāo)簽平臺大部分標(biāo)簽 T1 更新只有“最近 1 小時活躍”這類標(biāo)簽走實時。分層表建議統(tǒng)一按天分區(qū)小文件多的任務(wù)加合并步驟冷數(shù)據(jù)按保留周期歸檔避免存儲成本失控。層級職責(zé)典型表更新頻率禁止事項ODS原樣落地、可回溯ods_order、ods_user_event每日/實時不做業(yè)務(wù)過濾DWD清洗、去重、統(tǒng)一口徑dwd_trade_order每日不直接出看板DWS主題匯總、輕度聚合dws_trade_day每日不堆過多維度ADS看板、接口、人群包ads_trade_board每日/按需不反寫明細(xì)注意指標(biāo)口徑變更必須留版本否則歷史看板會集體失真。DWD 的過濾條件一改DWS 和 ADS 都要重跑不能只改最上層。3.2 指標(biāo)管理體系一個指標(biāo)一條 YAML指標(biāo)口徑對不上根因通常不是 SQL 寫錯而是同一個詞在不同團(tuán)隊嘴里含義不同。資料里強調(diào)指標(biāo)要有業(yè)務(wù)口徑、技術(shù)口徑、計算周期、計算方式、責(zé)任人。我的做法是一個指標(biāo)一條 YAML進(jìn) Git 管理變更走評審。指標(biāo)碼用“域_主題_度量_周期”比如trade_order_pay_amt_1d業(yè)務(wù)口徑寫清楚“支付成功訂單金額扣除退款不含測試訂單”技術(shù)口徑寫清楚源表、過濾條件、去重主鍵、金額單位維度列出常用切分比如日期、品類、渠道、產(chǎn)品線。metric_code: trade_order_pay_amt_1d name: 日支付訂單金額 biz_caliber: 支付成功訂單金額扣除當(dāng)日退款不含測試訂單和風(fēng)控攔截訂單 tech_caliber: dwd_trade_order 按 order_id 去重 pay_status PAID order_type TEST pay_amount 單位為元保留兩位小數(shù) owner: 某交易數(shù)據(jù)負(fù)責(zé)人 period: 1d dimensions: - dt - category_id - channel_id - product_line source_table: dwd_trade_order filter: pay_status PAID AND order_type TEST version: v1.2這份 YAML 的關(guān)鍵是“可執(zhí)行”。biz_caliber不是寫給領(lǐng)導(dǎo)看的而是給開發(fā)和分析師對照的tech_caliber要能直接翻譯成 SQLdimensions決定 DWS 建哪些匯總維度version讓歷史看板能追到口徑版本。指標(biāo)變更時不要直接改原文件新增版本并保留舊版本ADS 表按版本號切換。否則一個“支付金額”改了口徑歷史報表全部對不上業(yè)務(wù)方會直接不信數(shù)據(jù)。指標(biāo)管理還有一條血淚經(jīng)驗不要讓每個看板自己寫 SQL 算指標(biāo)?？窗逯幌M ADS 或指標(biāo)服務(wù)指標(biāo)服務(wù)背后統(tǒng)一走 YAML 定義。這樣新看板接入時不用重新討論口徑直接選指標(biāo)碼和維度即可。自助分析平臺也一樣業(yè)務(wù)人員可以拖拽維度但指標(biāo)必須是注冊過的不能讓他們在頁面上隨便寫sum(pay_amount)。3.3 從 ODS 到 ADS 的 SQL 骨架先看 DWD 清洗。下面這段 SQL 從 ODS 訂單表取數(shù)按order_id去重過濾測試訂單統(tǒng)一金額單位補上品類和渠道維度。參數(shù)上dt是業(yè)務(wù)日期row_number()按update_time倒序取最新一條處理業(yè)務(wù)庫同一訂單多次變更pay_amount如果源表是分要除以 100這里假設(shè)源表已經(jīng)是元。測試訂單的識別條件用order_type TEST不要用訂單號前綴猜規(guī)則要寫在指標(biāo) YAML 里。-- DWD 訂單明細(xì)去重、過濾測試單、統(tǒng)一金額單位 INSERT OVERWRITE TABLE dwd_trade_order PARTITION (dt ${biz_date}) SELECT order_id, user_id, product_line, category_id, channel_id, pay_status, pay_amount, refund_amount, create_time, update_time FROM ( SELECT order_id, user_id, product_line, category_id, channel_id, pay_status, CAST(pay_amount AS DECIMAL(16,2)) AS pay_amount, CAST(refund_amount AS DECIMAL(16,2)) AS refund_amount, create_time, update_time, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time DESC) AS rn FROM ods_order WHERE dt ${biz_date} AND order_type TEST ) t WHERE rn 1;DWS 匯總按日、品類、渠道、產(chǎn)品線聚合支付金額和退款金額分開算最后在 ADS 里做凈額。這樣退款政策變化時不用重跑 DWD只改匯總邏輯。下面這段 SQL 的pay_amt只統(tǒng)計支付成功訂單refund_amt統(tǒng)計當(dāng)日退款net_amt是凈額。參數(shù)上dt分區(qū)對齊category_id和channel_id允許為空空值單獨歸到“未知”維度不要直接丟掉否則大盤對不上。-- DWS 交易日匯總按品類、渠道、產(chǎn)品線 INSERT OVERWRITE TABLE dws_trade_day PARTITION (dt ${biz_date}) SELECT ${biz_date} AS dt, product_line, COALESCE(category_id, UNKNOWN) AS category_id, COALESCE(channel_id, UNKNOWN) AS channel_id, COUNT(DISTINCT CASE WHEN pay_status PAID THEN order_id END) AS pay_order_cnt, SUM(CASE WHEN pay_status PAID THEN pay_amount ELSE 0 END) AS pay_amt, SUM(CASE WHEN refund_amount 0 THEN refund_amount ELSE 0 END) AS refund_amt, SUM(CASE WHEN pay_status PAID THEN pay_amount ELSE 0 END) - SUM(CASE WHEN refund_amount 0 THEN refund_amount ELSE 0 END) AS net_amt FROM dwd_trade_order WHERE dt ${biz_date} GROUP BY product_line, COALESCE(category_id, UNKNOWN), COALESCE(channel_id, UNKNOWN);ADS 看板表可以直接從 DWS 取數(shù)按角色拆指標(biāo)。領(lǐng)導(dǎo)層看大盤凈額、同比環(huán)比運營看品類、渠道、退款率產(chǎn)品看轉(zhuǎn)化漏斗。下面這段只做一張交易看板樣例net_amt和refund_rate是核心指標(biāo)。參數(shù)上dt用業(yè)務(wù)日期refund_rate分母為 0 時要返回 0避免除零同比環(huán)比可以在調(diào)度里另算不要在看板查詢時實時算否則數(shù)據(jù)量大時頁面會卡。-- ADS 交易看板大盤核心指標(biāo) INSERT OVERWRITE TABLE ads_trade_board PARTITION (dt ${biz_date}) SELECT dt, SUM(pay_amt) AS pay_amt, SUM(refund_amt) AS refund_amt, SUM(net_amt) AS net_amt, SUM(pay_order_cnt) AS pay_order_cnt, CASE WHEN SUM(pay_amt) 0 THEN 0 ELSE ROUND(SUM(refund_amt) / SUM(pay_amt), 4) END AS refund_rate FROM dws_trade_day WHERE dt ${biz_date} GROUP BY dt;跑完還要做對賬。至少查三項DWD 主鍵是否重復(fù)、DWS 匯總金額是否等于 DWD 明細(xì)金額、ADS 展示金額是否等于 DWS 匯總金額。下面這段對賬 SQL 輸出差異行差異不為 0 就阻斷下游看板發(fā)布。參數(shù)上${biz_date}同一天金額比較允許 0.01 的浮點誤差超過就查清洗規(guī)則或匯總維度是否丟數(shù)。-- 交易鏈路對賬DWD 明細(xì) vs DWS 匯總 SELECT d.dt, d.pay_amt AS dwd_pay_amt, s.pay_amt AS dws_pay_amt, d.pay_amt - s.pay_amt AS diff_amt FROM ( SELECT dt, SUM(pay_amount) AS pay_amt FROM dwd_trade_order WHERE dt ${biz_date} AND pay_status PAID GROUP BY dt ) d LEFT JOIN ( SELECT dt, SUM(pay_amt) AS pay_amt FROM dws_trade_day WHERE dt ${biz_date} GROUP BY dt ) s ON d.dt s.dt WHERE ABS(COALESCE(d.pay_amt,0) - COALESCE(s.pay_amt,0)) 0.01;4. 數(shù)據(jù)打通標(biāo)簽平臺從寬表到人群圈選的最小閉環(huán)標(biāo)簽平臺不是把用戶屬性拼成一張大寬表就完事真正難的是身份映射和標(biāo)簽更新節(jié)奏。資料里把標(biāo)簽平臺放在“打通”環(huán)節(jié)是因為用戶行為數(shù)據(jù)和業(yè)務(wù)數(shù)據(jù)往往存在不同產(chǎn)品線、不同系統(tǒng)里行為數(shù)據(jù)知道用戶點了什么業(yè)務(wù)數(shù)據(jù)知道用戶買了什么兩邊對不上用戶畫像就是殘缺的。打通的目標(biāo)不是把所有字段都塞進(jìn)一張表而是讓“用戶是誰、干了什么、買了什么”能在同一個用戶 ID 下被查詢和圈選。4.1 標(biāo)簽體系先分三層基礎(chǔ)、行為、業(yè)務(wù)標(biāo)簽體系建議先分三層基礎(chǔ)標(biāo)簽、行為標(biāo)簽、業(yè)務(wù)標(biāo)簽?；A(chǔ)標(biāo)簽是注冊天數(shù)、會員等級、所在城市等級這類變化慢的屬性行為標(biāo)簽是近 30 天點擊品類數(shù)、近 7 天訪問頻次、最近一次活躍時間業(yè)務(wù)標(biāo)簽是首單金額、近 90 天訂單數(shù)、退款次數(shù)。每類標(biāo)簽都要有元數(shù)據(jù)標(biāo)簽碼、名稱、類型、更新方式、源表、責(zé)任人、有效期。不要一上來做幾百個標(biāo)簽先做 20 個業(yè)務(wù)真用的跑通“寬表生成—標(biāo)簽計算—人群圈選—營銷調(diào)用”閉環(huán)。寬表按天分區(qū)只放常用標(biāo)簽長尾標(biāo)簽按需計算。下面建表語句把用戶 ID、基礎(chǔ)標(biāo)簽、行為標(biāo)簽、業(yè)務(wù)標(biāo)簽放在一張日分區(qū)表里dt是標(biāo)簽日期user_id是主鍵。參數(shù)上標(biāo)簽列允許為空空值代表“未知”而不是“否”更新方式用全量覆蓋避免增量 merge 寫錯導(dǎo)致標(biāo)簽漂移。寬表字段不要無限擴超過 100 列就拆成基礎(chǔ)寬表和行為寬表否則查詢和跑批都會變慢。-- 用戶標(biāo)簽寬表按天分區(qū)常用標(biāo)簽放一張表 CREATE TABLE IF NOT EXISTS dws_user_tag_wide ( user_id STRING COMMENT 統(tǒng)一用戶ID, register_days INT COMMENT 注冊天數(shù), member_level STRING COMMENT 會員等級, last_active_time STRING COMMENT 最近活躍時間, click_category_cnt_30d INT COMMENT 近30天點擊品類數(shù), visit_cnt_7d INT COMMENT 近7天訪問頻次, first_order_amt DECIMAL(16,2) COMMENT 首單金額, order_cnt_90d INT COMMENT 近90天訂單數(shù), refund_cnt_90d INT COMMENT 近90天退款次數(shù), dt STRING COMMENT 標(biāo)簽日期 yyyy-MM-dd ) PARTITIONED BY (dt) STORED AS PARQUET;標(biāo)簽計算不要每個標(biāo)簽一條獨立任務(wù)按源表分組批量算。行為標(biāo)簽從 DWD 事件表算業(yè)務(wù)標(biāo)簽從 DWD 訂單表算基礎(chǔ)標(biāo)簽從用戶維度表算最后按user_id全外連接。連接時注意空值行為表里沒有行為的用戶也要保留否則寬表只?；钴S用戶圈選“沉默用戶”就圈不出來。標(biāo)簽有效期也要寫進(jìn)元數(shù)據(jù)比如“近 30 天點擊品類數(shù)”每天重算“會員等級”每天同步過期標(biāo)簽要標(biāo)記不可用。4.2 身份映射打通產(chǎn)品線數(shù)據(jù)的硬骨頭跨產(chǎn)品線打通最難的是身份映射。同一個用戶在產(chǎn)品線 A 用手機號注冊在產(chǎn)品線 B 用設(shè)備號瀏覽在 product_line C 用會員 ID 下單如果沒有映射表三個 ID 就是三個人。常見做法是建一張身份映射表字段包括統(tǒng)一用戶 ID、手機號哈希、設(shè)備 ID、會員 ID、來源產(chǎn)品線、最近活躍時間、分區(qū)日期。優(yōu)先級一般是會員 ID 高于手機號哈希手機號哈希高于設(shè)備 ID沖突時按最近活躍時間保留人工可修正。手機號只存哈希不落明文設(shè)備 ID 也要做脫敏。-- 身份映射表多產(chǎn)品線 ID 歸一到 union_id CREATE TABLE IF NOT EXISTS dim_user_identity ( union_id STRING COMMENT 統(tǒng)一用戶ID, user_id STRING COMMENT 產(chǎn)品線內(nèi)用戶ID, phone_hash STRING COMMENT 手機號哈希不存明文, device_id STRING COMMENT 設(shè)備ID脫敏后存儲, member_id STRING COMMENT 會員ID, product_line STRING COMMENT 來源產(chǎn)品線, last_active STRING COMMENT 最近活躍時間, dt STRING COMMENT 分區(qū)日期 ) PARTITIONED BY (dt) STORED AS PARQUET;映射邏輯分兩步先按強 ID 配對手機號哈希相同或會員 ID 相同就歸到同一個union_id再用設(shè)備 ID 補弱關(guān)聯(lián)但設(shè)備 ID 沖突多不能單獨作為唯一依據(jù)。同一設(shè)備多賬號、同一手機號多產(chǎn)品線都要保留映射明細(xì)不能直接覆蓋。下面這段 SQL 是歸一的骨架ROW_NUMBER()按last_active倒序取最近一條COALESCE按優(yōu)先級選 ID。參數(shù)上dt用映射日期phone_hash要統(tǒng)一哈希算法和鹽值否則跨產(chǎn)品線對不上。-- 身份歸一強ID優(yōu)先設(shè)備ID兜底 INSERT OVERWRITE TABLE dim_user_identity PARTITION (dt ${biz_date}) SELECT CONCAT(U, MD5(CONCAT(COALESCE(phone_hash, member_id, device_id), ${salt}))) AS union_id, user_id, phone_hash, device_id, member_id, product_line, last_active, ${biz_date} AS dt FROM ( SELECT user_id, phone_hash, device_id, member_id, product_line, last_active, ROW_NUMBER() OVER ( PARTITION BY COALESCE(phone_hash, member_id, device_id) ORDER BY last_active DESC ) AS rn FROM ods_user_identity WHERE dt ${biz_date} ) t WHERE rn 1;提示身份映射表一定要保留來源產(chǎn)品線和最近活躍時間后續(xù)本文還有配套的精品資源點擊獲取