優(yōu)雅的維度模型與多維分析設(shè)計(jì)實(shí)踐邱盛昌-OPPO-技術(shù)專家/數(shù)據(jù)團(tuán)隊(duì)主管DataFunSummit#2023計(jì)設(shè)型計(jì)設(shè)型模度維的 目錄CONTENT極致分區(qū)表的數(shù)據(jù)倉庫架構(gòu)ETL架構(gòu)01維度模型設(shè)計(jì)的必要性DataFunSummit#2023解決巨量的取數(shù)需求>產(chǎn)生巨量需求的本質(zhì)：數(shù)據(jù)建設(shè)缺失或者建設(shè)方向錯(cuò)誤，導(dǎo)致大量用數(shù)訴求無法滿足解決無窮無盡的報(bào)表需求>導(dǎo)致無窮無盡的報(bào)表需求原因：通用數(shù)據(jù)模型設(shè)計(jì)缺失，面向需求開發(fā)無抽象首先滿足取數(shù)需求，才是報(bào)表需求完全的面向需求開發(fā)，需求要什么字段就做什么，一個(gè)需求一張RPT表，到處打聽到表就直接取用，邏輯都堆在RPT層沒有模型表，或者模型抽象不好，具體表現(xiàn)為：不了解建模知識(shí)，SQL/Java能實(shí)現(xiàn)就行；對建模有些了解，有中間表但通用性較差，沒有系統(tǒng)性的全局建模沒有OLAP建設(shè)概念，表拆得又多又散，進(jìn)入無窮迭代陷阱，如：一個(gè)事件做一張事實(shí)表埋點(diǎn)的“挖礦石”行為，只抽取這個(gè)需求要的字段，只做現(xiàn)在要的埋點(diǎn)事件，持續(xù)按需求“挖礦”02極致分區(qū)表的數(shù)據(jù)倉庫架構(gòu)DataFunSummit#2023先開發(fā)好事件元數(shù)據(jù)，用于指導(dǎo)主題域劃分，模型表的分表和分區(qū)等11200★★★★11_2980★298★★★★★22_271★★3550★★★...1200★★★★1_2app_id...980★app_id...98★★★★★2_2user_id...71★★...50★★★>埋點(diǎn)元數(shù)據(jù)：枚舉元數(shù)據(jù)app_cat1120★★app_cat2110★★app_cat3100★★10198★★10290★★數(shù)據(jù)倉庫建設(shè)中，肯定會(huì)有許多的補(bǔ)錄數(shù)據(jù)，必須一開始就約束需求人通過系統(tǒng)錄入，補(bǔ)錄系統(tǒng)提緩存層只保留8天數(shù)據(jù)，只可以被一個(gè)ODS表引用ODS層不可以當(dāng)作緩存數(shù)據(jù)使用，直接抽取數(shù)據(jù)就到ODS有部分表不需要進(jìn)入CDM層，不需要構(gòu)建星形模型，則可以在ODS處理直接到應(yīng)用層>維度窮舉實(shí)現(xiàn)方法：極致分區(qū)表的體現(xiàn)，簡潔又省資源直接構(gòu)建維表，在ODS實(shí)現(xiàn)，出來的速度非常快【表A】左關(guān)聯(lián)【表C】（廣播）【表C】=【表B】內(nèi)關(guān)聯(lián)【表A.c1的維度窮舉表（廣播）】在ODS層翻譯碼值，統(tǒng)一全局的口徑，不可在應(yīng)用層處處轉(zhuǎn)換命名方式：原字段_name（若原字段以id，code結(jié)尾則去掉再拼接，如app_id為app_name）(caseelseq.real_styleend)real_style_name事實(shí)表：只有需要統(tǒng)一建模才能有cdm，絕不是中間表的概念，是建模的概念（不是為了落一個(gè)中間層方便共用邏輯，而是真正全局的構(gòu)建星形模型的表）維表：不是所有表都有資格成為dim表，很多表沒有通過抽象與建模也就是個(gè)ods，一定要考慮一致性維度！屬于一個(gè)維的內(nèi)容不要有多張表在模型中過濾數(shù)據(jù)對模型有巨大傷害，新手非常容易犯此錯(cuò)誤，舉例：.country='CN'--表中所有數(shù)據(jù)都是CN的，但這個(gè)過濾相當(dāng)于埋了一個(gè)雷，隨時(shí)會(huì)爆.cheat_flag=0--作弊數(shù)據(jù)過濾掉，且不說作弊會(huì)誤判，此條件將會(huì)讓你查問題異常困難過濾數(shù)據(jù)屬于個(gè)性化邏輯，應(yīng)該在應(yīng)用層中處理，模型中切記不可以做任何過濾當(dāng)一個(gè)邏輯在應(yīng)用層中出現(xiàn)3次及以上，無論這個(gè)邏輯多么小，都應(yīng)該壓在CDM層中處理，舉例：.解開數(shù)組或者jason，['app_id']--看似極小的邏輯，如果你要對app_id做規(guī)則，所有地方都要改.截?cái)嘧址?，substr(str,1,2)--同上，此字段如果在應(yīng)用層處理，一旦有變化會(huì)極其麻煩.count(1)--非常簡單的邏輯，但此處統(tǒng)一了口徑，如果在應(yīng)用層處處count(1)，維護(hù)成本會(huì)非常大應(yīng)用層一般只做合并、關(guān)聯(lián)或個(gè)性化邏輯處理，不做公共邏輯，此條規(guī)則一般人很難做到category_code="${vi_category_code}"if[-z"${category_code}"];thenelsefiinsertoverwritetablecdo_xxx_dw.ods_cdo_xxx_xxxx_wide_inc_dselect*>分區(qū)表實(shí)現(xiàn)方法：極致分區(qū)表的體現(xiàn)（注意只是一個(gè)主題域下的表）ETL架構(gòu)ETL架構(gòu)>分區(qū)的OLAP報(bào)表實(shí)現(xiàn)方法ETL架構(gòu)把分區(qū)表運(yùn)用到出神入化，從ods?dwd?dws?ads?h2ck一路全是分區(qū)表，對于分區(qū)表查詢，超快超省錢所有事件都加入建模，且新事件也自動(dòng)進(jìn)到模型，在后繼的CDM層的流程中一起處理公共邏輯極其優(yōu)美的三個(gè)下劃線代表著不同的分區(qū)，避免依賴選擇困難03優(yōu)雅的維度模型設(shè)計(jì)DataFunSummit#2023抽取涉及維度的所有表所有字段，構(gòu)造這些表的ER關(guān)系圖，從ER圖抽象出維表.dim_app，dim_app_v2，dim_ap_v99--同一個(gè)維有多張表盡量做成單一主鍵的表（但不采用kimball的代理鍵方式）如下圖抽象：維表盡可能寬，不管冗余，哪怕超過1000個(gè)字段強(qiáng)制保證主鍵唯一，直接在維表代碼中做row_number處理以一個(gè)APP上報(bào)的所有埋點(diǎn)為例：.所有事件理論上全部進(jìn)明細(xì)表，數(shù)據(jù)量大且冷門事件6+頭部字段100+業(yè)務(wù)字段600+2011-01-011.........2023-11-1221_2............熱門維表的熱門字段在明細(xì)表全部關(guān)聯(lián)到明細(xì)表中哪些維度退化極其考驗(yàn)工程師的設(shè)計(jì)功底，不可貪多，不能太少不同等級(jí)維度退化設(shè)計(jì).錯(cuò)誤方向：維度還在map/json中的如['.錯(cuò)誤方向：明細(xì)表中只有id，沒有中文名.錯(cuò)誤方向：只存應(yīng)用版本ID，SKU_ID等不常用字段，每次使用都要通過橋接表找應(yīng)用ID，商品ID明細(xì)表是一個(gè)標(biāo)準(zhǔn)的二維表，字段有原子性，所有非結(jié)構(gòu)化的、二維表化的做法都會(huì)帶來麻煩后端工程師可能更愿意使用json，map，切記不可將這個(gè)習(xí)慣帶到數(shù)據(jù)領(lǐng)域必要先做一張OLAP的dws表，大量非個(gè)性化的指標(biāo)在這里實(shí)現(xiàn)事件6+頭部字段100+業(yè)務(wù)字段600+公共指標(biāo)50+2011-01-011.........2023-11-1221_2............必要做一張從OLAP的dws表出的ads層表，作為OLAP報(bào)表的底層表事件6+篩選后的維度500+公共指標(biāo)50+2011-01-011......2023-11-1221_2.........周期快照比較常見的是用戶畫像的實(shí)現(xiàn)，就是給用戶打標(biāo)簽，實(shí)現(xiàn)的邏輯一般比較復(fù)雜用戶標(biāo)簽最好抽象成周期快照事實(shí)表，不要放在原子事實(shí)表中，不然有可能造成復(fù)用度下降所有與這個(gè)周期快照相關(guān)的，最后都應(yīng)該合并在一個(gè)表中，不要過于分散（根據(jù)產(chǎn)出時(shí)間適當(dāng)調(diào)整）是否高價(jià)值用戶2011-01-011...............2023-11-122...............留存事實(shí)表是運(yùn)營最為常用的事實(shí)表之一，一般不會(huì)做到OLAP表，形式上是一個(gè)梯形3，7，14，30天留存）如果成本允許，建議將用戶ID也帶到報(bào)表中，可以配置出2011-01-011............2023-11-122............歸因事實(shí)表是運(yùn)營最為常用的事實(shí)表之一，一般不會(huì)做到OLAP表，形式上是一個(gè)漏斗比如一個(gè)典型歸因的過程：啟動(dòng)?曝光?下載?打開?訪問頁歸因的各種方法：參數(shù)歸因、離線字段匹配歸因（有損）、實(shí)時(shí)數(shù)據(jù)流中染色打標(biāo)歸因事實(shí)表非常復(fù)雜，計(jì)算量巨大，容易產(chǎn)生性能問題，很考驗(yàn)工程師的技術(shù)水平歸因路徑組合較多，需要與業(yè)務(wù)充分溝通，選擇確定好場景，在dws層預(yù)先計(jì)算出來合并事實(shí)表與歸因事實(shí)表區(qū)別：04萬能的多維分析模型與報(bào)表DataFunSummit#2023大數(shù)據(jù)量報(bào)表查詢緩慢，使得開發(fā)者報(bào)表拆得太細(xì)做得過多，程序分散，維護(hù)困難，人效低Mysql數(shù)據(jù)庫整體容量太小，經(jīng)常滿，經(jīng)常要清理和遷移，運(yùn)維成本大大部分人不會(huì)使用分區(qū)表，推數(shù)粗暴導(dǎo)致推送主從延遲嚴(yán)重，被迫讀寫都使用主庫與Hadoop集群的Sqoop工具搭配使用時(shí)權(quán)限管理非常困難，經(jīng)常報(bào)權(quán)限問題>Clickhouse解決的問題無主從延遲煩惱，無權(quán)限煩惱，吞吐量大，推送數(shù)據(jù)快99%的報(bào)表都可以使用CK做為載體，穩(wěn)定性好，無卡死，鎖之類的問題天然支持TT