柚子快報(bào)邀請(qǐng)碼778899分享:大數(shù)據(jù) 離線數(shù)倉-數(shù)據(jù)倉庫系統(tǒng)
1數(shù)據(jù)倉庫概述
數(shù)據(jù)倉庫核心架構(gòu)
ODS層(Operational Data Store):運(yùn)營(yíng)數(shù)據(jù)存儲(chǔ)層,用于存儲(chǔ)來自操作型系統(tǒng)的原始數(shù)據(jù),是數(shù)據(jù)倉庫中的第一層。 DWD層(Data Warehouse Detail):數(shù)據(jù)倉庫細(xì)節(jié)層,用于存儲(chǔ)經(jīng)過清洗和加工的詳細(xì)數(shù)據(jù),保留了原始數(shù)據(jù)的細(xì)節(jié)信息。 DWS層(Data Warehouse Summary):數(shù)據(jù)倉庫匯總層,用于存儲(chǔ)經(jīng)過匯總和聚合的數(shù)據(jù),通常用于報(bào)表和分析。 DIM層(Dimensional Data Warehouse):維度數(shù)據(jù)倉庫層,用于存儲(chǔ)維度數(shù)據(jù),如產(chǎn)品、用戶、時(shí)間等維度的信息。 ADS層(Application Data Store):應(yīng)用數(shù)據(jù)存儲(chǔ)層,用于存儲(chǔ)與具體業(yè)務(wù)應(yīng)用相關(guān)的數(shù)據(jù),通常是面向應(yīng)用的數(shù)據(jù)倉庫。 RDS層(Real-time Data Store):實(shí)時(shí)數(shù)據(jù)存儲(chǔ)層,用于存儲(chǔ)實(shí)時(shí)生成的數(shù)據(jù),支持實(shí)時(shí)數(shù)據(jù)分析和決策。 CDS層(Cold Data Store):冷數(shù)據(jù)存儲(chǔ)層,用于存儲(chǔ)不經(jīng)常訪問的歷史數(shù)據(jù),通常是低成本的存儲(chǔ)方式。
2數(shù)倉建模概述
2.1建模意義
數(shù)據(jù)模型就是數(shù)據(jù)組織和存儲(chǔ)方法,它強(qiáng)調(diào)從業(yè)務(wù)、數(shù)據(jù)存取和使用角度合理存儲(chǔ)數(shù)據(jù)。只有將數(shù)據(jù)有序的組織和存儲(chǔ)起來之后,數(shù)據(jù)才能得到高性能、低成本、高效率、高質(zhì)量的使用。
高性能:良好的數(shù)據(jù)模型能夠幫助我們快速查詢所需要的數(shù)據(jù)。低成本:良好的數(shù)據(jù)模型能減少重復(fù)計(jì)算,實(shí)現(xiàn)計(jì)算結(jié)果的復(fù)用,降低計(jì)算成本。高效率:良好的數(shù)據(jù)模型能極大的改善用戶使用數(shù)據(jù)的體驗(yàn),提高使用數(shù)據(jù)的效率。高質(zhì)量:良好的數(shù)據(jù)模型能改善數(shù)據(jù)統(tǒng)計(jì)口徑的混亂,減少計(jì)算錯(cuò)誤的可能性。
2.2數(shù)據(jù)倉庫建模方法論
ER模型
從全企業(yè)的高度,用實(shí)體關(guān)系(EntityRelationship)模型來描述企業(yè)業(yè)務(wù),并用規(guī)范化的方式表示出來,在范式理論上符合3NF。
1)實(shí)體關(guān)系模型
??????? 將復(fù)雜的數(shù)據(jù)抽象為兩個(gè)概念——實(shí)體和關(guān)系。實(shí)體表示一個(gè)對(duì)象,例如學(xué)生、班級(jí),關(guān)系是指兩個(gè)實(shí)體之間的關(guān)系,例如學(xué)生和班級(jí)之間的從屬關(guān)系。
2)數(shù)據(jù)庫規(guī)范化
????????是使用一系列范式設(shè)計(jì)數(shù)據(jù)庫(通常是關(guān)系型數(shù)據(jù)庫)的過程,其目的是減少數(shù)據(jù)冗余,增強(qiáng)數(shù)據(jù)的一致性。
????????這一系列范式就是指在設(shè)計(jì)關(guān)系型數(shù)據(jù)庫時(shí),需要遵從的不同的規(guī)范。關(guān)系型數(shù)據(jù)庫的范式一共有六種,分別是第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF)。遵循的范式級(jí)別越高,數(shù)據(jù)冗余性就越低。
3)三范式
??????? 1)函數(shù)依賴
完全函數(shù)依賴:通過AB能得出C,但AB單獨(dú)得不出C
部分函數(shù)依賴:通過AB能得出C,但通過A或B也能得出C
傳遞函數(shù)依賴:通過A得到B,通過B得到C,但C得不到A,即C傳遞依賴于A
??????? 2)第一范式1NF
?核心原則:屬性不可切割
3)第二范式2NF
核心原則:不能存在“部分函數(shù)依賴”
4)第三范式3NF
核心原則:不能存在傳遞函數(shù)依賴
ER模型結(jié)構(gòu)
出發(fā)點(diǎn):整合數(shù)據(jù),將數(shù)據(jù)進(jìn)行組合與合并減少數(shù)據(jù)的冗余性,但不適合直接用于分析統(tǒng)計(jì)
維度模型
將復(fù)雜的業(yè)務(wù)通過事實(shí)和維度兩個(gè)概念進(jìn)行呈現(xiàn),事實(shí)通常對(duì)應(yīng)業(yè)務(wù)過程,而維度通常對(duì)應(yīng)業(yè)務(wù)發(fā)生時(shí)所處的環(huán)境
注:業(yè)務(wù)過程可以概括為一個(gè)個(gè)不可拆分的行為事件,例如電商交易中的下單,取消訂單,付款,退單等,都是業(yè)務(wù)過程。
下圖為一個(gè)典型的維度模型,其中位于中心的SalesOrder為事實(shí)表,其中保存的是下單這個(gè)業(yè)務(wù)過程的所有記錄。位于周圍每張表都是維度表,包括Date(日期),Customer(顧客),Product(產(chǎn)品),Location(地區(qū))等,這些維度表就組成了每個(gè)訂單發(fā)生時(shí)所處的環(huán)境,即何人、何時(shí)、在何地下單了何種產(chǎn)品。從圖中可以看出,模型相對(duì)清晰、簡(jiǎn)潔。
維度建模以數(shù)據(jù)分析作為出發(fā)點(diǎn),為數(shù)據(jù)分析服務(wù),因此它關(guān)注的重點(diǎn)是用戶如何更快的完成需求分析以及如何實(shí)現(xiàn)較好的大規(guī)模復(fù)雜查詢的響應(yīng)性能。
3維度建模理論之事實(shí)表
3.1事實(shí)表概述
事實(shí)表作為數(shù)據(jù)倉庫維度建模的核心,緊緊圍繞著業(yè)務(wù)過程來設(shè)計(jì)。其包含與該業(yè)務(wù)過程有關(guān)的維度引用(維度表外鍵)以及該業(yè)務(wù)過程的度量(通常是可累加的數(shù)字類型字段)。
事實(shí)表特點(diǎn)
通常比較細(xì)長(zhǎng),即列少行多,且行的增速快
事實(shí)表分類
事務(wù)事實(shí)表,周期快照事實(shí)表和累積快照事實(shí)表
3.2事務(wù)性事實(shí)表
概述
事務(wù)型事實(shí)表用來記錄各業(yè)務(wù)過程,它保存的是各業(yè)務(wù)過程的原子操作事件,即最細(xì)粒度的操作事件。粒度是指事實(shí)表中一行數(shù)據(jù)所表達(dá)的業(yè)務(wù)細(xì)節(jié)程度。
事務(wù)型事實(shí)表可用于分析與各業(yè)務(wù)過程相關(guān)的各項(xiàng)統(tǒng)計(jì)指標(biāo),由于其保存了最細(xì)粒度的記錄,可以提供最大限度的靈活性,可以支持無法預(yù)期的各種細(xì)節(jié)層次的統(tǒng)計(jì)需求。
設(shè)計(jì)流程
設(shè)計(jì)事務(wù)事實(shí)表時(shí)一般可遵循以下四個(gè)步驟。
選擇業(yè)務(wù)過程→聲明粒度→確認(rèn)維度→確認(rèn)事實(shí)
1)選擇業(yè)務(wù)過程
在業(yè)務(wù)系統(tǒng)中,挑選我們感興趣的業(yè)務(wù)過程,業(yè)務(wù)過程可以概括為一個(gè)個(gè)不可拆分的行為事件,例如電商交易中的下單,取消訂單,付款,退單等,都是業(yè)務(wù)過程。通常情況下,一個(gè)業(yè)務(wù)過程對(duì)應(yīng)一張事務(wù)型事實(shí)表。
2)聲明粒度
業(yè)務(wù)過程確定后,需要為每個(gè)業(yè)務(wù)過程聲明粒度。即精確定義每張事務(wù)型事實(shí)表的每行數(shù)據(jù)表示什么,應(yīng)該盡可能選擇最細(xì)粒度,以此來響應(yīng)各種細(xì)節(jié)程度的需求。
典型的粒度聲明如下:
訂單事實(shí)表中一行數(shù)據(jù)表示的是一個(gè)訂單中的一個(gè)商品項(xiàng)。
3)確定維度
確定維度具體是指,確定與每張事務(wù)型事實(shí)表相關(guān)的維度有哪些。
確定維度時(shí)應(yīng)盡量多的選擇與業(yè)務(wù)過程相關(guān)的環(huán)境信息。因?yàn)榫S度的豐富程度就決定了維度模型能夠支持的指標(biāo)豐富程度。
4)確定事實(shí)
此處的“事實(shí)”一詞,指的是每個(gè)業(yè)務(wù)過程的度量值(通常是可累加的數(shù)字類型的值,例如:次數(shù)、個(gè)數(shù)、件數(shù)、金額等)。
經(jīng)過上述四個(gè)步驟,事務(wù)型事實(shí)表就基本設(shè)計(jì)完成了。第一步選擇業(yè)務(wù)過程可以確定有哪些事務(wù)型事實(shí)表,第二步可以確定每張事務(wù)型事實(shí)表的每行數(shù)據(jù)是什么,第三步可以確定每張事務(wù)型事實(shí)表的維度外鍵,第四步可以確定每張事務(wù)型事實(shí)表的度量值字段。
不足之處
事務(wù)型事實(shí)表可以保存所有業(yè)務(wù)過程的最細(xì)粒度的操作事件,故理論上其可以支撐與各業(yè)務(wù)過程相關(guān)的各種統(tǒng)計(jì)粒度的需求。但對(duì)于某些特定類型的需求,其邏輯可能會(huì)比較復(fù)雜,或者效率會(huì)比較低下。例如:
1)存量型指標(biāo)
例如商品庫存,賬戶余額等。此處以電商中的虛擬貨幣為例,虛擬貨幣業(yè)務(wù)包含的業(yè)務(wù)過程主要包括獲取貨幣和使用貨幣,兩個(gè)業(yè)務(wù)過程各自對(duì)應(yīng)一張事務(wù)型事實(shí)表,一張存儲(chǔ)所有的獲取貨幣的原子操作事件,另一張存儲(chǔ)所有使用貨幣的原子操作事件。
假定現(xiàn)有一個(gè)需求,要求統(tǒng)計(jì)截至當(dāng)日的各用戶虛擬貨幣余額。由于獲取貨幣和使用貨幣均會(huì)影響到余額,故需要對(duì)兩張事務(wù)型事實(shí)表進(jìn)行聚合,且需要區(qū)分兩者對(duì)余額的影響(加或減),另外需要對(duì)兩張表的全表數(shù)據(jù)聚合才能得到統(tǒng)計(jì)結(jié)果。
可以看到,不論是從邏輯上還是效率上考慮,這都不是一個(gè)好的方案。
2)多事務(wù)關(guān)聯(lián)統(tǒng)計(jì)
例如,現(xiàn)需要統(tǒng)計(jì)最近30天,用戶下單到支付的時(shí)間間隔的平均值。統(tǒng)計(jì)思路應(yīng)該是找到下單事務(wù)事實(shí)表和支付事務(wù)事實(shí)表,過濾出最近30天的記錄,然后按照訂單id對(duì)兩張事實(shí)表進(jìn)行關(guān)聯(lián),之后用支付時(shí)間減去下單時(shí)間,然后再求平均值。
邏輯上雖然并不復(fù)雜,但是其效率較低,應(yīng)為下單事務(wù)事實(shí)表和支付事務(wù)事實(shí)表均為大表,大表join大表的操作應(yīng)盡量避免。
可以看到,在上述兩種場(chǎng)景下事務(wù)型事實(shí)表的表現(xiàn)并不理想。下面要介紹的另外兩種類型的事實(shí)表就是為了彌補(bǔ)事務(wù)型事實(shí)表的不足的。
3.3周期型快照事實(shí)表
概述
周期快照事實(shí)表以具有規(guī)律性的、可預(yù)見的時(shí)間間隔來記錄事實(shí),主要用于分析一些存量型(例如商品庫存,賬戶余額)或者狀態(tài)型(空氣溫度,行駛速度)指標(biāo)。
對(duì)于商品庫存、賬戶余額這些存量型指標(biāo),業(yè)務(wù)系統(tǒng)中通常就會(huì)計(jì)算并保存最新結(jié)果,所以定期同步一份全量數(shù)據(jù)到數(shù)據(jù)倉庫,構(gòu)建周期型快照事實(shí)表,就能輕松應(yīng)對(duì)此類統(tǒng)計(jì)需求,而無需再對(duì)事務(wù)型事實(shí)表中大量的歷史記錄進(jìn)行聚合了。
對(duì)于空氣溫度、行駛速度這些狀態(tài)型指標(biāo),由于它們的值往往是連續(xù)的,我們無法捕獲其變動(dòng)的原子事務(wù)操作,所以無法使用事務(wù)型事實(shí)表統(tǒng)計(jì)此類需求。而只能定期對(duì)其進(jìn)行采樣,構(gòu)建周期型快照事實(shí)表。
設(shè)計(jì)流程
1)確定粒度
周期型快照事實(shí)表的粒度可由采樣周期和維度描述,故確定采樣周期和維度后即可確定粒度。
采樣周期通常選擇每日。
維度可根據(jù)統(tǒng)計(jì)指標(biāo)決定,例如指標(biāo)為統(tǒng)計(jì)每個(gè)倉庫中每種商品的庫存,則可確定維度為倉庫和商品。
確定完采樣周期和維度后,即可確定該表粒度為每日-倉庫-商品。
2)確認(rèn)事實(shí)
事實(shí)也可根據(jù)統(tǒng)計(jì)指標(biāo)決定,例如指標(biāo)為統(tǒng)計(jì)每個(gè)倉庫中每種商品的庫存,則事實(shí)為商品庫存。
事實(shí)類型
此處的事實(shí)類型是指度量值的類型,而非事實(shí)表的類型。事實(shí)(度量值)共分為三類,分別是可加事實(shí),半可加事實(shí)和不可加事實(shí)。
1)可加事實(shí)
可加事實(shí)是指可以按照與事實(shí)表相關(guān)的所有維度進(jìn)行累加,例如事務(wù)型事實(shí)表中的事實(shí)。
2)半可加事實(shí)
半可加事實(shí)是指只能按照與事實(shí)表相關(guān)的一部分維度進(jìn)行累加,例如周期型快照事實(shí)表中的事實(shí)。以上述各倉庫中各商品的庫存每天快照事實(shí)表為例,這張表中的庫存事實(shí)可以按照倉庫或者商品維度進(jìn)行累加,但是不能按照時(shí)間維度進(jìn)行累加,因?yàn)閷⒚刻斓膸齑胬奂悠饋硎菦]有任何意義的。
3)不可加事實(shí)
不可加事實(shí)是指完全不具備可加性,例如比率型事實(shí)。不可加事實(shí)通常需要轉(zhuǎn)化為可加事實(shí),例如比率可轉(zhuǎn)化為分子和分母。
3.4累積性快照事實(shí)表
概述
累計(jì)快照事實(shí)表是基于一個(gè)業(yè)務(wù)流程中的多個(gè)關(guān)鍵業(yè)務(wù)過程聯(lián)合處理而構(gòu)建的事實(shí)表,如交易流程中的下單、支付、發(fā)貨、確認(rèn)收貨業(yè)務(wù)過程。
累積型快照事實(shí)表通常具有多個(gè)日期字段,每個(gè)日期對(duì)應(yīng)業(yè)務(wù)流程中的一個(gè)關(guān)鍵業(yè)務(wù)過程(里程碑)。
累積型快照事實(shí)表主要用于分析業(yè)務(wù)過程(里程碑)之間的時(shí)間間隔等需求。例如前文提到的用戶下單到支付的平均時(shí)間間隔,使用累積型快照事實(shí)表進(jìn)行統(tǒng)計(jì),就能避免兩個(gè)事務(wù)事實(shí)表的關(guān)聯(lián)操作,從而變得十分簡(jiǎn)單高效。
設(shè)計(jì)流程
累積型快照事實(shí)表的設(shè)計(jì)流程同事務(wù)型事實(shí)表類似,也可采用以下四個(gè)步驟,下面重點(diǎn)描述與事務(wù)型事實(shí)表的不同之處。
選擇業(yè)務(wù)過程→聲明粒度→確認(rèn)維度→確認(rèn)事實(shí)。
1)選擇業(yè)務(wù)過程
選擇一個(gè)業(yè)務(wù)流程中需要關(guān)聯(lián)分析的多個(gè)關(guān)鍵業(yè)務(wù)過程,多個(gè)業(yè)務(wù)過程對(duì)應(yīng)一張累積型快照事實(shí)表。
2)聲明粒度
精確定義每行數(shù)據(jù)表示的是什么,盡量選擇最小粒度。
3)確認(rèn)維度
選擇與各業(yè)務(wù)過程相關(guān)的維度,需要注意的是,每各業(yè)務(wù)過程均需要一個(gè)日期維度。
4)確認(rèn)事實(shí)
選擇各業(yè)務(wù)過程的度量值。
4維度建模理論之維度表
4.1概述
維度表是維度建模的基礎(chǔ)和靈魂。前文提到,事實(shí)表緊緊圍繞業(yè)務(wù)過程進(jìn)行設(shè)計(jì),而維度表則圍繞業(yè)務(wù)過程所處的環(huán)境進(jìn)行設(shè)計(jì)。維度表主要包含一個(gè)主鍵和各種維度字段,維度字段稱為維度屬性。
4.2維度表設(shè)計(jì)步驟
1)確定維度(表)
在設(shè)計(jì)事實(shí)表時(shí),已經(jīng)確定了與每個(gè)事實(shí)表相關(guān)的維度,理論上每個(gè)相關(guān)維度均需對(duì)應(yīng)一張維度表。需要注意到,可能存在多個(gè)事實(shí)表與同一個(gè)維度都相關(guān)的情況,這種情況需保證維度的唯一性,即只創(chuàng)建一張維度表。另外,如果某些維度表的維度屬性很少,例如只有一個(gè)**名稱,則可不創(chuàng)建該維度表,而把該表的維度屬性直接增加到與之相關(guān)的事實(shí)表中,這個(gè)操作稱為維度退化。
2)確定主維表和相關(guān)維表
此處的主維表和相關(guān)維表均指業(yè)務(wù)系統(tǒng)中與某維度相關(guān)的表。例如業(yè)務(wù)系統(tǒng)中與商品相關(guān)的表有sku_info,spu_info,base_trademark,base_category3,base_category2,base_category1等,其中sku_info就稱為商品維度的主維表,其余表稱為商品維度的相關(guān)維表。維度表的粒度通常與主維表相同。
3)確定維度屬性
確定維度屬性即確定維度表字段。維度屬性主要來自于業(yè)務(wù)系統(tǒng)中與該維度對(duì)應(yīng)的主維表和相關(guān)維表。維度屬性可直接從主維表或相關(guān)維表中選擇,也可通過進(jìn)一步加工得到。
確定維度屬性時(shí),需要遵循以下要求:
(1)盡可能生成豐富的維度屬性
維度屬性是后續(xù)做分析統(tǒng)計(jì)時(shí)的查詢約束條件、分組字段的基本來源,是數(shù)據(jù)易用性的關(guān)鍵。維度屬性的豐富程度直接影響到數(shù)據(jù)模型能夠支持的指標(biāo)的豐富程度。
(2)盡量不使用編碼,而使用明確的文字說明,一般可以編碼和文字共存。
(3)盡量沉淀出通用的維度屬性
有些維度屬性的獲取需要進(jìn)行比較復(fù)雜的邏輯處理,例如需要通過多個(gè)字段拼接得到。為避免后續(xù)每次使用時(shí)的重復(fù)處理,可將這些維度屬性沉淀到維度表中。
4.3維度設(shè)計(jì)要點(diǎn)
規(guī)范化與反規(guī)范化
規(guī)范化是指使用一系列范式設(shè)計(jì)數(shù)據(jù)庫的過程,其目的是減少數(shù)據(jù)冗余,增強(qiáng)數(shù)據(jù)的一致性。通常情況下,規(guī)范化之后,一張表的字段會(huì)拆分到多張表。
反規(guī)范化是指將多張表的數(shù)據(jù)冗余到一張表,其目的是減少join操作,提高查詢性能。
在設(shè)計(jì)維度表時(shí),如果對(duì)其進(jìn)行規(guī)范化,得到的維度模型稱為雪花模型,如果對(duì)其進(jìn)行反規(guī)范化,得到的模型稱為星型模型。
數(shù)據(jù)倉庫系統(tǒng)的主要目的是用于數(shù)據(jù)分析和統(tǒng)計(jì),所以是否方便用戶進(jìn)行統(tǒng)計(jì)分析決定了模型的優(yōu)劣。采用雪花模型,用戶在統(tǒng)計(jì)分析的過程中需要大量的關(guān)聯(lián)操作,使用復(fù)雜度高,同時(shí)查詢性能很差,而采用星型模型,則方便、易用且性能好。所以出于易用性和性能的考慮,維度表一般是很不規(guī)范化的。
維度變化
維度屬性通常不是靜態(tài)的,而是會(huì)隨時(shí)間變化的,數(shù)據(jù)倉庫的一個(gè)重要特點(diǎn)就是反映歷史的變化,所以如何保存維度的歷史狀態(tài)是維度設(shè)計(jì)的重要工作之一。保存維度數(shù)據(jù)的歷史狀態(tài),通常有以下兩種做法,分別是全量快照表和拉鏈表。
1)全量快照表
離線數(shù)據(jù)倉庫的計(jì)算周期通常為每天一次,所以可以每天保存一份全量的維度數(shù)據(jù)。這種方式的優(yōu)點(diǎn)和缺點(diǎn)都很明顯。
優(yōu)點(diǎn)是簡(jiǎn)單而有效,開發(fā)和維護(hù)成本低,且方便理解和使用。
缺點(diǎn)是浪費(fèi)存儲(chǔ)空間,尤其是當(dāng)數(shù)據(jù)的變化比例比較低時(shí)。
2)拉鏈表
拉鏈表的意義就在于能夠更加高效的保存維度信息的歷史狀態(tài)。
(1)什么是拉鏈表
(2)為什么要做拉鏈表
(3)如何使用拉鏈表
多值維度
如果事實(shí)表中一條記錄在某個(gè)維度表中有多條記錄與之對(duì)應(yīng),稱為多值維度。例如,下單事實(shí)表中的一條記錄為一個(gè)訂單,一個(gè)訂單可能包含多個(gè)商品,所會(huì)商品維度表中就可能有多條數(shù)據(jù)與之對(duì)應(yīng)。
針對(duì)這種情況,通常采用以下兩種方案解決。
第一種:降低事實(shí)表的粒度,例如將訂單事實(shí)表的粒度由一個(gè)訂單降低為一個(gè)訂單中的一個(gè)商品項(xiàng)。
第二種:在事實(shí)表中采用多字段保存多個(gè)維度值,每個(gè)字段保存一個(gè)維度id。這種方案只適用于多值維度個(gè)數(shù)固定的情況。
建議盡量采用第一種方案解決多值維度問題。
多值屬性
維表中的某個(gè)屬性同時(shí)有多個(gè)值,稱之為“多值屬性”,例如商品維度的平臺(tái)屬性和銷售屬性,每個(gè)商品均有多個(gè)屬性值。
針對(duì)這種情況,通常有可以采用以下兩種方案。
第一種:將多值屬性放到一個(gè)字段,該字段內(nèi)容為key1:value1,key2:value2的形式,例如一個(gè)手機(jī)商品的平臺(tái)屬性值為“品牌:華為,系統(tǒng):鴻蒙,CPU:麒麟990”。
第二種:將多值屬性放到多個(gè)字段,每個(gè)字段對(duì)應(yīng)一個(gè)屬性。這種方案只適用于多值屬性個(gè)數(shù)固定的情況。
5數(shù)據(jù)倉庫設(shè)計(jì)
5.1數(shù)據(jù)倉庫分層規(guī)劃
本項(xiàng)目分層規(guī)劃:
5.2數(shù)據(jù)倉庫構(gòu)建流程
完整流程如下:
數(shù)據(jù)調(diào)研
業(yè)務(wù)調(diào)研和需求分析,直接影響數(shù)倉質(zhì)量
1)業(yè)務(wù)調(diào)研
業(yè)務(wù)調(diào)研的主要目標(biāo)是熟悉業(yè)務(wù)流程、熟悉業(yè)務(wù)數(shù)據(jù)。
熟悉業(yè)務(wù)流程要求做到,明確每個(gè)業(yè)務(wù)的具體流程,需要將該業(yè)務(wù)所包含的每個(gè)業(yè)務(wù)過程一一列舉出來。
熟悉業(yè)務(wù)數(shù)據(jù)要求做到,將數(shù)據(jù)(包括埋點(diǎn)日志和業(yè)務(wù)數(shù)據(jù)表)與業(yè)務(wù)過程對(duì)應(yīng)起來,明確每個(gè)業(yè)務(wù)過程會(huì)對(duì)哪些表的數(shù)據(jù)產(chǎn)生影響,以及產(chǎn)生什么影響。產(chǎn)生的影響,需要具體到,是新增一條數(shù)據(jù),還是修改一條數(shù)據(jù),并且需要明確新增的內(nèi)容或者是修改的邏輯。
2)需求分析
典型的需求指標(biāo)如,最近一天各省份手機(jī)品類訂單總額。
分析需求時(shí),需要明確需求所需的業(yè)務(wù)過程及維度,例如該需求所需的業(yè)務(wù)過程就是買家下單,所需的維度有日期,省份,商品品類。
3)總結(jié)
做完業(yè)務(wù)分析和需求分析之后,要保證每個(gè)需求都能找到與之對(duì)應(yīng)的業(yè)務(wù)過程及維度。若現(xiàn)有數(shù)據(jù)無法滿足需求,則需要和業(yè)務(wù)方進(jìn)行溝通,例如某個(gè)頁面需要新增某個(gè)行為的埋點(diǎn)。
明確數(shù)據(jù)域
便于數(shù)據(jù)的管理和應(yīng)用
通??梢愿鶕?jù)業(yè)務(wù)過程或者部門進(jìn)行劃分,本項(xiàng)目根據(jù)業(yè)務(wù)過程進(jìn)行劃分,需要注意的是一個(gè)業(yè)務(wù)過程只能屬于一個(gè)數(shù)據(jù)域。
下面是本數(shù)倉項(xiàng)目所需的所有業(yè)務(wù)過程及數(shù)據(jù)域劃分詳情。
數(shù)據(jù)域 業(yè)務(wù)過程 交易域 加購、下單、取消訂單、支付成功、退單、退款成功 流量域 頁面瀏覽、啟動(dòng)應(yīng)用、動(dòng)作、曝光、錯(cuò)誤 用戶域 注冊(cè)、登錄 互動(dòng)域 收藏、評(píng)價(jià) 工具域 優(yōu)惠券領(lǐng)取、優(yōu)惠券使用(下單)、優(yōu)惠券使用(支付)
構(gòu)建業(yè)務(wù)總線矩陣
業(yè)務(wù)總線矩陣中包含維度模型所需的所有事實(shí)(業(yè)務(wù)過程)以及維度,以及各業(yè)務(wù)過程與各維度的關(guān)系。矩陣的行是一個(gè)個(gè)業(yè)務(wù)過程,矩陣的列是一個(gè)個(gè)的維度,行列的交點(diǎn)表示業(yè)務(wù)過程與維度的關(guān)系
按照事務(wù)型事實(shí)表的設(shè)計(jì)流程,選擇業(yè)務(wù)過程——聲明粒度——確認(rèn)維度——確認(rèn)事實(shí),得到的最終的業(yè)務(wù)總線矩陣
后續(xù)的DWD層以及DIM層的搭建需參考業(yè)務(wù)總線矩陣。
明確統(tǒng)計(jì)指標(biāo)
具體的工作是,深入分析需求,構(gòu)建指標(biāo)體系。構(gòu)建指標(biāo)體系的主要意義就是指標(biāo)定義標(biāo)準(zhǔn)化。所有指標(biāo)的定義,都必須遵循同一套標(biāo)準(zhǔn),這樣能有效的避免指標(biāo)定義存在歧義,指標(biāo)定義重復(fù)等問題。
1)指標(biāo)體系相關(guān)概念
(1)原子指標(biāo)
原子指標(biāo)基于某一業(yè)務(wù)過程的度量值,是業(yè)務(wù)定義中不可再拆解的指標(biāo),原子指標(biāo)的核心功能就是對(duì)指標(biāo)的聚合邏輯進(jìn)行了定義。我們可以得出結(jié)論,原子指標(biāo)包含三要素,分別是業(yè)務(wù)過程、度量值和聚合邏輯。
例如訂單總額就是一個(gè)典型的原子指標(biāo),其中的業(yè)務(wù)過程為用戶下單、度量值為訂單金額,聚合邏輯為sum()求和。需要注意的是原子指標(biāo)只是用來輔助定義指標(biāo)一個(gè)概念,通常不會(huì)對(duì)應(yīng)有實(shí)際統(tǒng)計(jì)需求與之對(duì)應(yīng)。
(2)派生指標(biāo)
派生指標(biāo)基于原子指標(biāo),其與原子指標(biāo)的關(guān)系如下圖所示。
與原子指標(biāo)不同,派生指標(biāo)通常會(huì)對(duì)應(yīng)實(shí)際的統(tǒng)計(jì)需求。
(3)衍生指標(biāo)
衍生指標(biāo)是在一個(gè)或多個(gè)派生指標(biāo)的基礎(chǔ)上,通過各種邏輯運(yùn)算復(fù)合而成的。例如比率、比例等類型的指標(biāo)。衍生指標(biāo)也會(huì)對(duì)應(yīng)實(shí)際的統(tǒng)計(jì)需求。
2)指標(biāo)體系對(duì)于數(shù)倉建模的意義
通過上述兩個(gè)具體的案例可以看出,絕大多數(shù)的統(tǒng)計(jì)需求,都可以使用原子指標(biāo)、派生指標(biāo)以及衍生指標(biāo)這套標(biāo)準(zhǔn)去定義。同時(shí)能夠發(fā)現(xiàn)這些統(tǒng)計(jì)需求都直接的或間接的對(duì)應(yīng)一個(gè)或者是多個(gè)派生指標(biāo)。
當(dāng)統(tǒng)計(jì)需求足夠多時(shí),必然會(huì)出現(xiàn)部分統(tǒng)計(jì)需求對(duì)應(yīng)的派生指標(biāo)相同的情況。這種情況下,我們就可以考慮將這些公共的派生指標(biāo)保存下來,這樣做的主要目的就是減少重復(fù)計(jì)算,提高數(shù)據(jù)的復(fù)用性。
這些公共的派生指標(biāo)統(tǒng)一保存在數(shù)據(jù)倉庫的DWS層。因此DWS層設(shè)計(jì),就可以參考我們根據(jù)現(xiàn)有的統(tǒng)計(jì)需求整理出的派生指標(biāo)。
維度模型設(shè)計(jì)
維度模型的設(shè)計(jì)參照上述得到的業(yè)務(wù)總線矩陣即可。事實(shí)表存儲(chǔ)在DWD層,維度表存儲(chǔ)在DIM層。
匯總模型設(shè)計(jì)
匯總模型的設(shè)計(jì)參考上述整理出的指標(biāo)體系(主要是派生指標(biāo))即可。匯總表與派生指標(biāo)的對(duì)應(yīng)關(guān)系是,一張匯總表通常包含業(yè)務(wù)過程相同、統(tǒng)計(jì)周期相同、統(tǒng)計(jì)粒度相同的多個(gè)派生指標(biāo)。請(qǐng)思考:匯總表與事實(shí)表的對(duì)應(yīng)關(guān)系是?
????????匯總表和事實(shí)表之間的對(duì)應(yīng)關(guān)系是根據(jù)業(yè)務(wù)需求和數(shù)據(jù)分析的目的來確定的,通過建立合適的匯總表,可以提高數(shù)據(jù)查詢和分析的效率,同時(shí)減少對(duì)事實(shí)表的直接查詢壓力。
6數(shù)倉環(huán)境準(zhǔn)備
6.1 數(shù)據(jù)倉庫運(yùn)行環(huán)境
Hive環(huán)境搭建
1)Hive引擎簡(jiǎn)介
Hive引擎包括:默認(rèn)MR、Tez、Spark。
Hive on Spark:Hive既作為存儲(chǔ)元數(shù)據(jù)又負(fù)責(zé)SQL的解析優(yōu)化,語法是HQL語法,執(zhí)行引擎變成了Spark,Spark負(fù)責(zé)采用RDD執(zhí)行。
Spark on Hive : Hive只作為存儲(chǔ)元數(shù)據(jù),Spark負(fù)責(zé)SQL解析優(yōu)化,語法是Spark?SQL語法,Spark負(fù)責(zé)采用RDD執(zhí)行。
2)Hive?on?Spark配置
(1)兼容性說明
注意:官網(wǎng)下載的Hive3.1.3和Spark3.3.1默認(rèn)是不兼容的。因?yàn)镠ive3.1.3支持的Spark版本是2.3.0,所以需要我們重新編譯Hive3.1.3版本。
編譯步驟:官網(wǎng)下載Hive3.1.3源碼,修改pom文件中引用的Spark版本為3.3.1,如果編譯通過,直接打包獲取jar包。如果報(bào)錯(cuò),就根據(jù)提示,修改相關(guān)方法,直到不報(bào)錯(cuò),打包獲取jar包。
(2)在Hive所在節(jié)點(diǎn)部署Spark純凈版
(3)Hive on Spark測(cè)試
Yarn環(huán)境配置
1)增加ApplicationMaster資源比例
容量調(diào)度器對(duì)每個(gè)資源隊(duì)列中同時(shí)運(yùn)行的Application?Master占用的資源進(jìn)行了限制,該限制通過yarn.scheduler.capacity.maximum-am-resource-percent參數(shù)實(shí)現(xiàn),其默認(rèn)值是0.1,表示每個(gè)資源隊(duì)列上Application?Master最多可使用的資源為該隊(duì)列總資源的10%,目的是防止大部分資源都被Application?Master占用,而導(dǎo)致Map/Reduce?Task無法執(zhí)行。
生產(chǎn)環(huán)境該參數(shù)可使用默認(rèn)值。但學(xué)習(xí)環(huán)境,集群資源總數(shù)很少,如果只分配10%的資源給Application?Master,則可能出現(xiàn),同一時(shí)刻只能運(yùn)行一個(gè)Job的情況,因?yàn)橐粋€(gè)Application?Master使用的資源就可能已經(jīng)達(dá)到10%的上限了。故此處可將該值適當(dāng)調(diào)大。
具體操作 修改配置文件——分發(fā)——重啟
6.2 數(shù)據(jù)倉庫開發(fā)環(huán)境
數(shù)倉開發(fā)工具可選用DBeaver或者DataGrip。兩者都需要用到JDBC協(xié)議連接到Hive,故需要啟動(dòng)HiveServer2。
6.3 模擬數(shù)據(jù)準(zhǔn)備
通常企業(yè)在開始搭建數(shù)倉時(shí),業(yè)務(wù)系統(tǒng)中會(huì)存在歷史數(shù)據(jù),一般是業(yè)務(wù)數(shù)據(jù)庫存在歷史數(shù)據(jù),而用戶行為日志無歷史數(shù)據(jù)。假定數(shù)倉上線的日期為2022-06-08,為模擬真實(shí)場(chǎng)景,需準(zhǔn)備以下數(shù)據(jù)。
注:在執(zhí)行以下操作之前,先將HDFS上/origin_data路徑下之前的數(shù)據(jù)刪除。
1)啟動(dòng)采集通道
命令如下。
[atguigu@hadoop102 ~]$ cluster.sh start
停止Maxwell。 ?
[atguigu@hadoop102 bin]$ mxw.sh stop
停止Maxwell
2)數(shù)據(jù)準(zhǔn)備
(1)生成模擬數(shù)據(jù)
(2)全量表同步
(3)增量表首日全量同步
????????① 清除Maxwell斷點(diǎn)記錄
????????由于Maxwell支持?jǐn)帱c(diǎn)續(xù)傳,而上述重新生成業(yè)務(wù)數(shù)據(jù)的過程,會(huì)產(chǎn)生大量的binlog操作日志,這些日志我們并不需要。故此處需清除Maxwell的斷點(diǎn)記錄,令其從binlog最新的位置開始采集。
????????清空Maxwell數(shù)據(jù)庫,相當(dāng)于初始化Maxwell。
7數(shù)倉開發(fā)之ODS層
ODS層的設(shè)計(jì)要點(diǎn)如下:
(1)ODS層的表結(jié)構(gòu)設(shè)計(jì)依托于從業(yè)務(wù)系統(tǒng)同步過來的數(shù)據(jù)結(jié)構(gòu)。
(2)ODS層要保存全部歷史數(shù)據(jù),故其壓縮格式應(yīng)選擇壓縮比較高的,此處選擇gzip。
(3)ODS層表名的命名規(guī)范為:ods_表名_單分區(qū)增量全量標(biāo)識(shí)(inc/full)。
7.1日志表
1)建表語句
DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(
`common` STRUCT ba :STRING, ch :STRING, is_new :STRING, md :STRING, mid :STRING, os :STRING, sid :STRING, uid :STRING, vc :STRING> COMMENT '公共信息', `page` STRUCT item :STRING, item_type :STRING, last_page_id :STRING, page_id :STRING, from_pos_id :STRING, from_pos_seq :STRING, refer_id :STRING> COMMENT '頁面信息', `actions` ARRAY item:STRING, item_type:STRING, ts:BIGINT>> COMMENT '動(dòng)作信息', `displays` ARRAY item :STRING, item_type :STRING, `pos_seq` :STRING, pos_id :STRING>> COMMENT '曝光信息', `start` STRUCT first_open :BIGINT, loading_time :BIGINT, open_ad_id :BIGINT, open_ad_ms :BIGINT, open_ad_skip_ms :BIGINT> COMMENT '啟動(dòng)信息', `err` STRUCT msg:STRING> COMMENT '錯(cuò)誤信息', `ts` BIGINT COMMENT '時(shí)間戳' ) COMMENT '活動(dòng)信息表' PARTITIONED BY (`dt` STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe' LOCATION '/warehouse/gmall/ods/ods_log_inc/' TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec'); 2)數(shù)據(jù)裝載 load data inpath '/origin_data/gmall/log/topic_log/2022-06-08' into table ods_log_inc partition(dt='2022-06-08'); 3)每日數(shù)據(jù)裝載腳本 (1)在hadoop102的/home/atguigu/bin目錄下創(chuàng)建hdfs_to_ods_log.sh #!/bin/bash # 定義變量方便修改 APP=gmall # 如果是輸入的日期按照取輸入日期;如果沒輸入日期取當(dāng)前時(shí)間的前一天 if [ -n "$1" ] ;then do_date=$1 else do_date=`date -d "-1 day" +%F` fi echo ================== 日志日期為 $do_date ================== sql=" load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log_inc partition(dt='$do_date'); " hive -e "$sql" 7.2 業(yè)務(wù)表 7.2.1 活動(dòng)信息表(全量表)ods_activity_info_full DROP TABLE IF EXISTS ods_activity_info_full; CREATE EXTERNAL TABLE ods_activity_info_full ( `id` STRING COMMENT '活動(dòng)id', `activity_name` STRING COMMENT '活動(dòng)名稱', `activity_type` STRING COMMENT '活動(dòng)類型', `activity_desc` STRING COMMENT '活動(dòng)描述', `start_time` STRING COMMENT '開始時(shí)間', `end_time` STRING COMMENT '結(jié)束時(shí)間', `create_time` STRING COMMENT '創(chuàng)建時(shí)間', `operate_time` STRING COMMENT '修改時(shí)間' ) COMMENT '活動(dòng)信息表' PARTITIONED BY (`dt` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS '' LOCATION '/warehouse/gmall/ods/ods_activity_info_full/' TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec'); 7.2.2 活動(dòng)規(guī)則表(全量表)ods_activity_rule_full 7.2.3 一級(jí)品類表(全量表)ods_base_category1_full 7.2.4 二級(jí)品類表(全量表)ods_base_category2_full 7.2.5 三級(jí)品類表(全量表)ods_base_category3_full 7.2.6 編碼字典表(全量表)ods_base_dic_full 7.2.7 省份表(全量表)ods_base_province_full 7.2.8 地區(qū)表(全量表)ods_base_region_full 7.2.9 品牌表(全量表)ods_base_trademark_full 7.2.10 購物車表(全量表)ods_cart_info_full 7.2.11 優(yōu)惠券信息表(全量表)ods_coupon_info_full 7.2.12 商品平臺(tái)屬性表(全量表)ods_sku_attr_value_full 7.2.13 商品表(全量表)ods_sku_info_full 7.2.14 商品銷售屬性值表(全量表)ods_sku_sale_attr_value_full 7.2.15 SPU表(全量表)ods_spu_info_full 7.2.16 營(yíng)銷坑位表(全量表)ods_promotion_pos_full 7.2.17 營(yíng)銷渠道表(全量表)ods_promotion_refer_ful 7.2.18 購物車表(增量表)ods_cart_info_inc 7.2.19 評(píng)論表(增量表)ods_comment_info_inc 7.2.20 優(yōu)惠券領(lǐng)用表(增量表)ods_coupon_use_inc 7.2.21 收藏表(增量表)ods_favor_info_inc 7.2.22 訂單明細(xì)表(增量表)ods_order_detail_inc 7.2.23 訂單明細(xì)活動(dòng)關(guān)聯(lián)表(增量表)ods_order_detail_activity_inc 7.2.24 訂單明細(xì)優(yōu)惠券關(guān)聯(lián)表(增量表)ods_order_detail_coupon_inc 7.2.25 訂單表(增量表)ods_order_info_inc 7.2.26 退單表(增量表)ods_order_refund_info_inc 7.2.27 訂單狀態(tài)流水表(增量表)ods_order_status_log_inc 7.2.28 支付表(增量表)ods_payment_info_inc 7.2.29 退款表(增量表)ods_refund_payment_inc 7.2.30 用戶表(增量表)ods_user_info_inc 7.2.31 數(shù)據(jù)裝載腳本 1)在hadoop102的/home/atguigu/bin目錄下創(chuàng)建hdfs_to_ods_db.sh #!/bin/bash APP=gmall if [ -n "$2" ] ;then do_date=$2 else do_date=`date -d '-1 day' +%F` fi load_data(){ sql="" for i in $*; do #判斷路徑是否存在 hadoop fs -test -e /origin_data/$APP/db/${i:4}/$do_date #路徑存在方可裝載數(shù)據(jù) if [[ $? = 0 ]]; then sql=$sql"load data inpath '/origin_data/$APP/db/${i:4}/$do_date' OVERWRITE into table ${APP}.$i partition(dt='$do_date');" fi done hive -e "$sql" } case $1 in "ods_activity_info_full") load_data "ods_activity_info_full" ;; "ods_activity_rule_full") load_data "ods_activity_rule_full" ;; "ods_base_category1_full") load_data "ods_base_category1_full" ;; "ods_base_category2_full") load_data "ods_base_category2_full" ;; "ods_base_category3_full") load_data "ods_base_category3_full" ;; "ods_base_dic_full") load_data "ods_base_dic_full" ;; "ods_base_province_full") load_data "ods_base_province_full" ;; "ods_base_region_full") load_data "ods_base_region_full" ;; "ods_base_trademark_full") load_data "ods_base_trademark_full" ;; "ods_cart_info_full") load_data "ods_cart_info_full" ;; "ods_coupon_info_full") load_data "ods_coupon_info_full" ;; "ods_sku_attr_value_full") load_data "ods_sku_attr_value_full" ;; "ods_sku_info_full") load_data "ods_sku_info_full" ;; "ods_sku_sale_attr_value_full") load_data "ods_sku_sale_attr_value_full" ;; "ods_spu_info_full") load_data "ods_spu_info_full" ;; "ods_promotion_pos_full") load_data "ods_promotion_pos_full" ;; "ods_promotion_refer_full") load_data "ods_promotion_refer_full" ;; "ods_cart_info_inc") load_data "ods_cart_info_inc" ;; "ods_comment_info_inc") load_data "ods_comment_info_inc" ;; "ods_coupon_use_inc") load_data "ods_coupon_use_inc" ;; "ods_favor_info_inc") load_data "ods_favor_info_inc" ;; "ods_order_detail_inc") load_data "ods_order_detail_inc" ;; "ods_order_detail_activity_inc") load_data "ods_order_detail_activity_inc" ;; "ods_order_detail_coupon_inc") load_data "ods_order_detail_coupon_inc" ;; "ods_order_info_inc") load_data "ods_order_info_inc" ;; "ods_order_refund_info_inc") load_data "ods_order_refund_info_inc" ;; "ods_order_status_log_inc") load_data "ods_order_status_log_inc" ;; "ods_payment_info_inc") load_data "ods_payment_info_inc" ;; "ods_refund_payment_inc") load_data "ods_refund_payment_inc" ;; "ods_user_info_inc") load_data "ods_user_info_inc" ;; "all") load_data "ods_activity_info_full" "ods_activity_rule_full" "ods_base_category1_full" "ods_base_category2_full" "ods_base_category3_full" "ods_base_dic_full" "ods_base_province_full" "ods_base_region_full" "ods_base_trademark_full" "ods_cart_info_full" "ods_coupon_info_full" "ods_sku_attr_value_full" "ods_sku_info_full" "ods_sku_sale_attr_value_full" "ods_spu_info_full" "ods_promotion_pos_full" "ods_promotion_refer_full" "ods_cart_info_inc" "ods_comment_info_inc" "ods_coupon_use_inc" "ods_favor_info_inc" "ods_order_detail_inc" "ods_order_detail_activity_inc" "ods_order_detail_coupon_inc" "ods_order_info_inc" "ods_order_refund_info_inc" "ods_order_status_log_inc" "ods_payment_info_inc" "ods_refund_payment_inc" "ods_user_info_inc" ;; esac 8數(shù)倉開發(fā)之DIM層 DIM層設(shè)計(jì)要點(diǎn): (1)DIM層的設(shè)計(jì)依據(jù)是維度建模理論,該層存儲(chǔ)維度模型的維度表。 (2)DIM層的數(shù)據(jù)存儲(chǔ)格式為orc列式存儲(chǔ)+snappy壓縮。 (3)DIM層表名的命名規(guī)范為dim_表名_全量表或者拉鏈表標(biāo)識(shí)(full/zip)。 8.1 商品維度表dim_sku_full 1)建表語句 DROP TABLE IF EXISTS dim_sku_full; CREATE EXTERNAL TABLE dim_sku_full ( `id` STRING COMMENT 'SKU_ID', `price` DECIMAL(16, 2) COMMENT '商品價(jià)格', `sku_name` STRING COMMENT '商品名稱', `sku_desc` STRING COMMENT '商品描述', `weight` DECIMAL(16, 2) COMMENT '重量', `is_sale` BOOLEAN COMMENT '是否在售', `spu_id` STRING COMMENT 'SPU編號(hào)', `spu_name` STRING COMMENT 'SPU名稱', `category3_id` STRING COMMENT '三級(jí)品類ID', `category3_name` STRING COMMENT '三級(jí)品類名稱', `category2_id` STRING COMMENT '二級(jí)品類id', `category2_name` STRING COMMENT '二級(jí)品類名稱', `category1_id` STRING COMMENT '一級(jí)品類ID', `category1_name` STRING COMMENT '一級(jí)品類名稱', `tm_id` STRING COMMENT '品牌ID', `tm_name` STRING COMMENT '品牌名稱', `sku_attr_values` ARRAY value_id :STRING, attr_name :STRING, value_name:STRING>> COMMENT '平臺(tái)屬性', `sku_sale_attr_values` ARRAY sale_attr_value_id :STRING, sale_attr_name :STRING, sale_attr_value_name:STRING>> COMMENT '銷售屬性', `create_time` STRING COMMENT '創(chuàng)建時(shí)間' ) COMMENT '商品維度表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dim/dim_sku_full/' TBLPROPERTIES ('orc.compress' = 'snappy'); 2)數(shù)據(jù)裝載 我們知道,ODS層表的數(shù)據(jù)來源于原始業(yè)務(wù)表,只是剔除了某些不需要的字段。因此在考慮DIM和DWD層表的數(shù)據(jù)裝載時(shí),對(duì)原始表和ODS層表分析是一樣的。本項(xiàng)目可能會(huì)基于業(yè)務(wù)數(shù)據(jù)庫的原表分析,特此說明。下文不再贅述。 (1)思路分析 ① 分區(qū)規(guī)劃 為了避免下游查詢時(shí)執(zhí)行全表掃描,按天分區(qū),分區(qū)字段取值為當(dāng)天日期。 ② 數(shù)據(jù)流向 商品維度相關(guān)的原始表都做了全量采集,我們只需要獲取ODS層數(shù)據(jù)源表當(dāng)日分區(qū)的數(shù)據(jù),寫回當(dāng)日分區(qū)即可。 ③ 數(shù)據(jù)裝載 商品維度相關(guān)的業(yè)務(wù)表有八張:sku_info,spu_info,base_trademark,base_category1_info, base_category2_info,base_category3_info,sku_attr_value,sku_sale_attr_value。這些表都做了全量采集,在ODS層有相應(yīng)的原始表與之對(duì)應(yīng)?;诰S度建模理論,我們要確定主維表,將商品維度相關(guān)的原始表關(guān)聯(lián)起來。維度表的粒度與主維表保持一致,后者的主鍵就是維度表的唯一標(biāo)識(shí)。在下游的DWS層或ADS層,我們會(huì)用事實(shí)表去關(guān)聯(lián)維度表,補(bǔ)充維度信息,即維度關(guān)聯(lián)。顯然,如果事實(shí)表中保存了維度表的唯一標(biāo)識(shí),維度關(guān)聯(lián)更加易于實(shí)現(xiàn),如交易域下單事務(wù)事實(shí)表保留了sku_id,如果以sku_info作為主維表,可以很方便地通過sku_id完成關(guān)聯(lián)。因此,通常要保證主維表的主鍵字段存在于絕大多數(shù)相關(guān)事實(shí)表中。此外,在數(shù)據(jù)倉庫中,維度表的粒度越細(xì),保留的信息越多,下游可以做的分析就越豐富。 綜上,選擇ods_sku_info_full作為商品維度主表。 (2)執(zhí)行步驟 ① 通過CTE表達(dá)式定義臨時(shí)表 CTE 是 Common Table Expressions 的縮寫,是 SQL 中一種用于創(chuàng)建臨時(shí)結(jié)果集的語法結(jié)構(gòu)。CTE 提供了一種更清晰、更易讀的方式來組織復(fù)雜的 SQL 查詢語句,使得查詢邏輯更加模塊化和可維護(hù)。 CTE 主要用于兩個(gè)方面: 遞歸查詢:CTE 可以用于執(zhí)行遞歸查詢,即在一個(gè)查詢中引用自身,以處理具有層次結(jié)構(gòu)的數(shù)據(jù)。 復(fù)雜查詢:CTE 可以用于將復(fù)雜的查詢分解為多個(gè)步驟,并將每個(gè)步驟的結(jié)果存儲(chǔ)在臨時(shí)表中,以便后續(xù)查詢引用。 CTE 通常使用 WITH 關(guān)鍵字定義,并且可以在同一個(gè)查詢中定義多個(gè) CTE。每個(gè) CTE 可以在后續(xù)的查詢語句中被引用。 sku子查詢:從ods_sku_info_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)SKU的id、價(jià)格、名稱、描述、重量、是否上架、所屬的SPU的id、所屬的第三級(jí)品類的id、所屬的品牌的id、創(chuàng)建時(shí)間等字段。spu子查詢:從ods_spu_info_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)SPU的id和名稱。c3子查詢:從ods_base_category3_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)第三級(jí)品類的id、名稱和所屬的第二級(jí)品類的id。c2子查詢:從ods_base_category2_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)第二級(jí)品類的id、名稱和所屬的第一級(jí)品類的id。c1子查詢:從ods_base_category1_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)第一級(jí)品類的id和名稱。tm子查詢:從ods_base_trademark_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)品牌的id和名稱。attr子查詢:從ods_sku_attr_value_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)SKU的屬性及其取值,使用collect_set函數(shù)對(duì)相同的SKU的屬性進(jìn)行了合并,生成了一個(gè)名為attrs的數(shù)組。sale_attr子查詢:從ods_sku_sale_attr_value_full表中選取當(dāng)日('2022-06-08')數(shù)據(jù),篩選每個(gè)SKU的銷售屬性及其取值,使用collect_set函數(shù)對(duì)相同的SKU的銷售屬性進(jìn)行了合并,生成了一個(gè)名為sale_attrs的數(shù)組。 ② 最后,以sku子查詢?yōu)橹鞅恚瑢藗€(gè)臨時(shí)表關(guān)聯(lián)起來,關(guān)聯(lián)字段及條件如下。 與spu子查詢通過spu_id等值連接與c3子查詢通過category3_id關(guān)聯(lián)與c2子查詢通過category2_id關(guān)聯(lián)與c1子查詢通過category1_id關(guān)聯(lián)與tm子查詢通過tm_id關(guān)聯(lián)與attr子查詢通過sku_id關(guān)聯(lián)與sale_attr子查詢通過sku_id關(guān)聯(lián) 其中,與spu、c3、c2、c1、tm的關(guān)聯(lián)使用left?join和join是等價(jià)的,full?[outer] join和right?join等價(jià)的,因?yàn)閟ku一定存在對(duì)應(yīng)的spu、一級(jí)、二級(jí)、三級(jí)品類和品牌,主表中不存在不滿足關(guān)聯(lián)條件的數(shù)據(jù)。而這些從表中不滿足關(guān)聯(lián)條件的數(shù)據(jù)是無用的,因此應(yīng)選擇left?join或join。此處選擇left?join,這是因?yàn)?,?shù)據(jù)的數(shù)據(jù)經(jīng)歷多個(gè)環(huán)節(jié)的傳遞很可能丟失,如果使用join,當(dāng)從表數(shù)據(jù)丟失時(shí),與之匹配的主表數(shù)據(jù)會(huì)被舍棄,導(dǎo)致關(guān)聯(lián)結(jié)果的整條數(shù)據(jù)丟失。而使用left?join,則關(guān)聯(lián)結(jié)果中整條數(shù)據(jù)得以保留,只是取自從表的字段為null,可以減少數(shù)據(jù)丟失帶來的損失,且便于發(fā)現(xiàn)和定位問題。因而,left?join和join等價(jià)時(shí),選用left?join。下文同理,不再贅述。 接下來關(guān)聯(lián)最后兩個(gè)子查詢,sku可能不存在屬性或銷售屬性,在這兩個(gè)子查詢中未必有滿足關(guān)聯(lián)條件的數(shù)據(jù),此時(shí)主表數(shù)據(jù)需要保留,因此要選用left?join或full?join,而這兩個(gè)子查詢中不滿足關(guān)聯(lián)條件的數(shù)據(jù)不應(yīng)保留,只能選擇left?join。 最后選取所需字段,寫入dim_sku_full表的當(dāng)日分區(qū)。 (3)圖解 1分區(qū)規(guī)劃 2數(shù)據(jù)流向 3數(shù)據(jù)裝載 代碼實(shí)現(xiàn) collect_set 函數(shù)用于聚合操作,它將一個(gè)列中的所有不同的值收集到一個(gè)集合中,并且去重。collect_set 函數(shù)通常用于對(duì)某列中的數(shù)據(jù)進(jìn)行去重后的統(tǒng)計(jì)或者分析。 8.2 優(yōu)惠券維度表dim_coupon_full 只展示與前不同點(diǎn) ③ 數(shù)據(jù)裝載 我們只會(huì)用到字典表的編碼和名稱兩個(gè)字段,單獨(dú)建表意義不大,應(yīng)做維度退化。優(yōu)惠券相關(guān)的原始業(yè)務(wù)表只有coupon_info,不需要確定主維表和相關(guān)維表。因此,只須關(guān)聯(lián)ods_coupon_info_full與ods_base_dic_full。 (2)執(zhí)行步驟 ① 從ods_coupon_info_full表中篩選2022-06-08分區(qū)的數(shù)據(jù),選取需要的字段,如優(yōu)惠券名稱、優(yōu)惠券類型、使用條件等。 ② 將①中獲取的數(shù)據(jù)與ods_base_dic_full關(guān)聯(lián)獲取優(yōu)惠券類型名稱和優(yōu)惠范圍類型名稱。關(guān)聯(lián)字段分別為優(yōu)惠券類型和優(yōu)惠范圍類型,這兩種編碼在字典表中一定有對(duì)應(yīng)記錄,而字典表中其它編碼對(duì)應(yīng)記錄對(duì)我們是沒有意義的,因此以ods_coupon_info_full作為主表,應(yīng)使用內(nèi)連接或左外連接。 ③ 通過case?when… then… end語法根據(jù)不同的優(yōu)惠券類型,生成不同的優(yōu)惠規(guī)則字符串,記為benefit_rule字段。 ④ 最終,將查詢結(jié)果覆蓋寫入dim_coupon_full的2022-06-08分區(qū)。 8.3 活動(dòng)維度表dim_activity_full ③ 數(shù)據(jù)裝載 活動(dòng)相關(guān)的原始業(yè)務(wù)表有activity_rule、activity_info,此外,為了獲取活動(dòng)類型名稱,還需要關(guān)聯(lián)字典表。activity_rule中記錄了活動(dòng)的規(guī)則描述,activity_info記錄了活動(dòng)描述,用戶下單時(shí),每條明細(xì)記錄都可能參與活動(dòng),order_detail_activity(訂單活動(dòng)關(guān)聯(lián)表)記錄了這些信息,該表中記錄的是每個(gè)SKU具體參與了那次活動(dòng),滿足了該活動(dòng)的哪條規(guī)則,因此,要讓事實(shí)表與活動(dòng)維度進(jìn)行關(guān)聯(lián),活動(dòng)維度表的粒度應(yīng)細(xì)化至活動(dòng)規(guī)則粒度。綜上,以activity_rule作為主表。 (2)執(zhí)行步驟 ① 從ods_activity_rule_full表中篩選2022-06-08分區(qū)的數(shù)據(jù),并選取需要的字段,子查詢記為rule。 ② 從ods_activity_info_full 表中篩選2022-06-08分區(qū)的數(shù)據(jù),并選取需要的字段,子查詢記為info。 ③?篩選字典表2022-06-08分區(qū)的數(shù)據(jù)。 ④ 以rule作為主表,通過activity_id關(guān)聯(lián)info。活動(dòng)規(guī)則和活動(dòng)信息的每一條數(shù)據(jù)都可以在另一張表中找到對(duì)應(yīng)記錄,因此inner join、left?join、right?join、full?outer?join都是等價(jià)的,任選一種即可。此處選擇left?join。 ⑤ 與8.2同理,關(guān)聯(lián)字段表獲取編碼名稱字段選擇left?join。 ⑥ 篩選字段,寫入dim_activity_full的2022-06-08分區(qū) 8.4 地區(qū)維度表dim_province_full ③ 數(shù)據(jù)裝載 原始業(yè)務(wù)數(shù)據(jù)庫中與地區(qū)相關(guān)的表有base_province和base_region,二者通過region_id產(chǎn)生聯(lián)系。下單等業(yè)務(wù)過程相關(guān)的表中都通過province_id字段與地區(qū)維度產(chǎn)生關(guān)聯(lián),顯然base_province應(yīng)為主維表。 (2)執(zhí)行步驟 ① 篩選ods_base_province_full表2022-06-08分區(qū)的數(shù)據(jù),選取所須字段,子查詢記為province。 ② 篩選ods_base_region_full表2022-06-08分區(qū)的數(shù)據(jù),選取所須字段,子查詢記為region。 ③ 通過region_id將province和region關(guān)聯(lián)起來。兩張表不存在不滿足關(guān)聯(lián)條件的數(shù)據(jù),inner join、left?join、right?join、full?outer?join都是一樣的,此處選用left join。 ④ 選取所須字段,寫入dim_province_full表的2022-06-08分區(qū)。 8.5 營(yíng)銷坑位維度表dim_promotion_pos_full ③ 數(shù)據(jù)裝載 原始業(yè)務(wù)數(shù)據(jù)庫中只有promotion_pos表與營(yíng)銷坑位維度相關(guān),從ods_promotion_pos_full表中篩選2022-06-08分區(qū)的數(shù)據(jù),選擇所須字段寫入dim_promotion_pos_full表的2022-06-08分區(qū)即可。 8.6 營(yíng)銷渠道維度表dim_promotion_refer_full ③ 數(shù)據(jù)裝載 原始業(yè)務(wù)數(shù)據(jù)庫中只有promotion_refer表與營(yíng)銷渠道維度相關(guān),從ods_promotion_refer_full表中篩選2022-06-08分區(qū)的數(shù)據(jù),選取所須字段寫入dim_promotion_refer_full表的2022-06-08分區(qū)即可。 8.7 日期維度表dim_date 通常情況下,時(shí)間維度表的數(shù)據(jù)并不是來自于業(yè)務(wù)系統(tǒng),而是手動(dòng)寫入,并且由于時(shí)間維度表數(shù)據(jù)的可預(yù)見性,無須每日導(dǎo)入,一般可一次性導(dǎo)入一年的數(shù)據(jù)。 (1)創(chuàng)建臨時(shí)表 DROP TABLE IF EXISTS tmp_dim_date_info; CREATE EXTERNAL TABLE tmp_dim_date_info ( `date_id` STRING COMMENT '日', `week_id` STRING COMMENT '周ID', `week_day` STRING COMMENT '周幾', `day` STRING COMMENT '每月的第幾天', `month` STRING COMMENT '第幾月', `quarter` STRING COMMENT '第幾季度', `year` STRING COMMENT '年', `is_workday` STRING COMMENT '是否是工作日', `holiday_id` STRING COMMENT '節(jié)假日' ) COMMENT '時(shí)間維度表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/'; (2)將數(shù)據(jù)文件上傳到HFDS上臨時(shí)表路徑/warehouse/gmall/tmp/tmp_dim_date_info (3)執(zhí)行以下語句將其導(dǎo)入時(shí)間維度表 insert overwrite table dim_date select * from tmp_dim_date_info; (4)檢查數(shù)據(jù)是否導(dǎo)入成功 select * from dim_date; 8.8 用戶維度表dim_user_zip 2)數(shù)據(jù)裝載 (1)思路分析 ① 分區(qū)規(guī)劃 拉鏈表的分區(qū)有兩類:9999-12-31分區(qū)和普通日期分區(qū)。前者保存最新的維度數(shù)據(jù),后者保存有效期截至分區(qū)日期的數(shù)據(jù)。 ② 數(shù)據(jù)流向 a)首日 業(yè)務(wù)數(shù)據(jù)庫的user_info表中記錄了全量最新的用戶數(shù)據(jù),全部進(jìn)入9999-12-31分區(qū)。 b)每日 從數(shù)倉上線次日開始,采集user_info的變更數(shù)據(jù),通常業(yè)務(wù)數(shù)據(jù)庫的數(shù)據(jù)不會(huì)被刪除,因而用戶信息的變更只有新增和修改兩類。新增的用戶信息進(jìn)入9999-12-31分區(qū),修改的用戶信息覆蓋9999-12-31分區(qū)的記錄,并將歷史數(shù)據(jù)寫入前一分區(qū)(如某用戶信息在2022-06-09發(fā)生變化,當(dāng)日的歷史用戶信息有效期截至2022-06-08,進(jìn)入2022-06-08分區(qū))。 ③ 數(shù)據(jù)裝載 首日數(shù)據(jù)裝載:篩選所須字段,對(duì)敏感信息加密脫敏,寫入9999-12-31分區(qū)即可。 每日數(shù)據(jù)裝載較為復(fù)雜。首先要考慮到,用戶的數(shù)據(jù)可能在一天內(nèi)多次變化,而拉鏈表中對(duì)于同一個(gè)用戶每天至多只會(huì)維護(hù)一條數(shù)據(jù),因此只須保留同一用戶每天的最后一次更改。此處不需要區(qū)分新增和修改操作,我們只要獲取同一用戶當(dāng)天最晚的一次操作就可以獲取其最新狀態(tài)。 接下來,要將當(dāng)日發(fā)生變更的用戶信息與歷史所有用戶的最新信息(拉鏈表9999-12-31分區(qū)的數(shù)據(jù))合并起來。最后,在9999-12-31分區(qū)保留每個(gè)用戶最新的狀態(tài),并將過期數(shù)據(jù)寫入當(dāng)日分區(qū)。 (2)知識(shí)儲(chǔ)備 ①?regexp:用法:A?regexp?B,AB均為字符串,A是待匹配的字符串,B是正則表達(dá)式。若A、B其中之一為null則返回false。若A中的任意子串可以匹配B則返回true,否則返回false。如果要讓整個(gè)A串與B匹配才返回true,需要在B中通過^和$限定A開頭和結(jié)尾的字符。 ② rlike:等價(jià)于regexp。 (3)執(zhí)行步驟 ① 首日裝載 a)篩選ods_user_info_inc表當(dāng)日分區(qū)的數(shù)據(jù),對(duì)用戶姓名、電話號(hào)碼、郵箱地址做脫敏處理。其中電話號(hào)碼、郵箱地址在加密前要校驗(yàn)格式是否合法,若不合法則返回null。用戶的姓名可能被用于姓氏相關(guān)的統(tǒng)計(jì),保留姓氏,手機(jī)號(hào)可能用于運(yùn)營(yíng)商相關(guān)統(tǒng)計(jì),保留前三位,郵箱可能用于郵箱類型相關(guān)統(tǒng)計(jì),保留@及之后的內(nèi)容。 b)定義兩個(gè)字段:start_date和end_date。 start_date:用戶信息的生效起始日期,首日裝載時(shí)所有數(shù)據(jù)的start_date均為數(shù)倉上線起始日期。end_date:用戶信息的過期日期。首日裝載時(shí)所有用戶信息均為當(dāng)日最新,過期時(shí)間取極大值:9999-12-31。 c)篩選統(tǒng)計(jì)所須的其它字段,寫入9999-12-31分區(qū)。 ② 每日裝載 a)t1子查詢 篩選ods_user_info_inc表當(dāng)日分區(qū)的數(shù)據(jù),開窗,按照用戶ID分區(qū),數(shù)據(jù)變更時(shí)間降序排列,調(diào)用row_number()函數(shù)編號(hào),記為rn。顯然,同一用戶當(dāng)天最晚的操作記錄rn為1。 b)t2子查詢 從t1子查詢中篩選rn為1的數(shù)據(jù),獲取當(dāng)日信息發(fā)生新增或變更的所有用戶最新的狀態(tài),字段處理與首日相同。此時(shí)獲得的子查詢數(shù)據(jù)結(jié)構(gòu)與dim_user_zip表相同,記為tmp。 本項(xiàng)目約定的數(shù)倉上線首日為2022-06-08,假設(shè)統(tǒng)計(jì)日期為2022-06-11,此時(shí)過期分區(qū)2022-06-08、2022-06-09、2022-06-10分別存儲(chǔ)了當(dāng)日過期的用戶數(shù)據(jù),2022-06-11新增和變化的用戶數(shù)據(jù)不會(huì)影響到上述三個(gè)分區(qū)已過期的數(shù)據(jù),只會(huì)對(duì)9999-12-31分區(qū)保存的用戶最新狀態(tài)產(chǎn)生影響。因此,我們只須關(guān)心9999-12-31分區(qū)即可。篩選該分區(qū)數(shù)據(jù),與tmp子查詢union,即t2子查詢。 c)t3子查詢 t2子查詢包含了昨日和當(dāng)日最新的用戶數(shù)據(jù),它們將進(jìn)入兩個(gè)分區(qū):i)所有用戶的最新信息進(jìn)入9999-12-31分區(qū);ii)當(dāng)日發(fā)生變更因而過期的用戶歷史信息進(jìn)入昨日分區(qū),如統(tǒng)計(jì)日期為2022-06-09,則當(dāng)天過期的用戶數(shù)據(jù)有效日期截至2022-06-08,進(jìn)入2022-06-08分區(qū)。 那么,如何確定數(shù)據(jù)所屬分區(qū)?根據(jù)數(shù)據(jù)變更情況,可以將用戶分為三類,如下。 昨日存在且當(dāng)日信息未變更的用戶。 當(dāng)日新增的用戶。 當(dāng)日信息發(fā)生變更的用戶。 前兩類用戶只對(duì)應(yīng)一條數(shù)據(jù),這條數(shù)據(jù)就是用戶的最新信息,寫入9999-12-31分區(qū)即可。第三種用戶對(duì)應(yīng)兩條數(shù)據(jù),其中,當(dāng)日變更數(shù)據(jù)為最新的用戶信息,應(yīng)進(jìn)入9999-12-31分區(qū),而昨日9999-12-31分區(qū)的數(shù)據(jù)已過期,應(yīng)進(jìn)入昨日分區(qū)。 考慮下一個(gè)問題,如何實(shí)現(xiàn)數(shù)據(jù)分流?執(zhí)行如下操作:開窗,按照用戶ID分區(qū),start_date降序排列,調(diào)用row_number()編號(hào)。我們會(huì)發(fā)現(xiàn),t2子查詢中每個(gè)用戶最多只有兩條數(shù)據(jù),編號(hào)不超過2,因?yàn)閷?duì)于每一用戶當(dāng)日的變更操作只取最新的一條,9999-12-31分區(qū)也只會(huì)對(duì)每個(gè)用戶保留一條數(shù)據(jù)。編號(hào)為1的是用戶的最新信息,編號(hào)為2的是用戶的過期數(shù)據(jù)。將前者寫入9999-12-31分區(qū),后者寫入昨日分區(qū)即可。 最后,所有數(shù)據(jù)的start_date均為其新增或變化的日期。初始的end_date均為9999-12-31。過期數(shù)據(jù)的end_date變更為昨日,未過期數(shù)據(jù)的end_date仍為9999-12-31。不難發(fā)現(xiàn),每條數(shù)據(jù)的end_date與dt一致。end_date或dt的計(jì)算邏輯有兩種: 如果編號(hào)為1,記為9999-12-31,否則記為昨日。如果編號(hào)為2,記為昨日,否則為9999-12-31。 任選一種計(jì)算邏輯,按照dt字段動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入所屬分區(qū)。 圖解 1分區(qū)規(guī)劃 2數(shù)據(jù)流向 3數(shù)據(jù)裝載 首日裝載和每日裝載腳本分別寫 9 數(shù)倉開發(fā)之DWD層 DWD層設(shè)計(jì)要點(diǎn): (1)DWD層的設(shè)計(jì)依據(jù)是維度建模理論,該層存儲(chǔ)維度模型的事實(shí)表。 (2)DWD層的數(shù)據(jù)存儲(chǔ)格式為orc列式存儲(chǔ)+snappy壓縮。 (3)DWD層表名的命名規(guī)范為dwd_數(shù)據(jù)域_表名_單分區(qū)增量全量標(biāo)識(shí)(inc/full) 9.1 交易域加購事務(wù)事實(shí)表dwd_trade_cart_add_inc 1)建表語句 DROP TABLE IF EXISTS dwd_trade_cart_add_inc; CREATE EXTERNAL TABLE dwd_trade_cart_add_inc ( `id` STRING COMMENT '編號(hào)', `user_id` STRING COMMENT '用戶ID', `sku_id` STRING COMMENT 'SKU_ID', `date_id` STRING COMMENT '日期ID', `create_time` STRING COMMENT '加購時(shí)間', `sku_num` BIGINT COMMENT '加購物車件數(shù)' ) COMMENT '交易域加購事務(wù)事實(shí)表' PARTITIONED BY (`dt` STRING) STORED AS ORC LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/' TBLPROPERTIES ('orc.compress' = 'snappy'); 2)數(shù)據(jù)裝載 1)思路分析 ① 分區(qū)規(guī)劃 為了避免全表掃描,事務(wù)事實(shí)表也要按天分區(qū),數(shù)據(jù)應(yīng)進(jìn)入業(yè)務(wù)過程發(fā)生日期對(duì)應(yīng)的分區(qū)。如2022-06-08發(fā)生的加購操作進(jìn)入2022-06-08分區(qū)。 ② 數(shù)據(jù)流向 事務(wù)事實(shí)表的數(shù)據(jù)源表都做了增量采集,數(shù)倉上線首日?qǐng)?zhí)行一次全表掃描獲取歷史全量數(shù)據(jù),從第二日開始只采集每日變更數(shù)據(jù)。事實(shí)表上游ODS層表的首日分區(qū)可能包含多天的業(yè)務(wù)操作,這些數(shù)據(jù)應(yīng)進(jìn)入事實(shí)表的不同分區(qū),第二日及之后的分區(qū)只包含當(dāng)日的業(yè)務(wù)操作,應(yīng)進(jìn)入事實(shí)表的當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 首日和每日裝載數(shù)據(jù)集和數(shù)據(jù)流向的不同,導(dǎo)致了二者處理邏輯的差異,需要分開處理。加購操作發(fā)生時(shí),只會(huì)導(dǎo)致業(yè)務(wù)庫cart_info表的數(shù)據(jù)發(fā)生變化,因而本節(jié)事實(shí)表的數(shù)據(jù)來源于cart_info的變更記錄,應(yīng)從ods_cart_info_inc表中讀取數(shù)據(jù)。 a)首日裝載 獲取ods_cart_add_inc表首日分區(qū)的數(shù)據(jù),寫入加購操作發(fā)生日期對(duì)應(yīng)的分區(qū)即可。 b)每日裝載 獲取ods_cart_add_inc當(dāng)日分區(qū)的數(shù)據(jù),寫入事實(shí)表當(dāng)日分區(qū)。 (2)知識(shí)儲(chǔ)備 from_utc_timestamp({any primitive type} ts, string timezone):將UTC時(shí)間戳ts轉(zhuǎn)化為給定時(shí)區(qū)的timestamp類型數(shù)據(jù),展示為YYYY-MM-DD HH:MM:SS.fffffffff格式的日期字符串。ts可以是任意原生類型,包括timestamp/date,tinyint/smallint/init/bigint,float/double和decimal。如果ts是小數(shù)會(huì)被視為秒級(jí)時(shí)間戳,如果是整數(shù)會(huì)被視為毫秒時(shí)間戳。timezone是用于指明時(shí)區(qū)的字符串,可以用IANA時(shí)區(qū)數(shù)據(jù)中的時(shí)區(qū)名稱或時(shí)區(qū)偏移量表示,如東八區(qū)可以用GMT+8或Asia/Shanghai表示。 (3)執(zhí)行步驟 ① 首日裝載 從ods_cart_info_inc中篩選2022-06-08分區(qū)的數(shù)據(jù)。首日?qǐng)?zhí)行maxwell-bootstrap命令做全表掃描,將掃描到的數(shù)據(jù)交給Maxwell進(jìn)程封裝為JSON字符串,這些數(shù)據(jù)的操作類型分為bootstrap-start、bootstrap-insert、bootstrap-complete三類,第一類和第三類分別標(biāo)記了全表掃描任務(wù)的開始和結(jié)束,只有操作類型為第二類的JSON包含了統(tǒng)計(jì)所須的數(shù)據(jù),? 接下來,如何從ods_cart_info_inc中提取加購操作?加購操作發(fā)生時(shí),若加購表沒有該用戶對(duì)該sku的加購記錄,就會(huì)新增一條數(shù)據(jù),create_time為加購時(shí)間;否則修改數(shù)據(jù),將sku_num更改為加購之后的值,此時(shí)update_time為加購時(shí)間。業(yè)務(wù)數(shù)據(jù)庫沒有記錄cart_info的變更操作,首日?qǐng)?zhí)行全表掃描只能獲取第一種加購記錄,我們認(rèn)為每條記錄都對(duì)應(yīng)一次加購操作,create_time為加購時(shí)間,sku_num為加購商品數(shù)。這樣做存在誤差,不可避免。 最后,篩選所須字段,基于create_time計(jì)算加購日期,作為分區(qū)字段,通過動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入加購日期對(duì)應(yīng)分區(qū)即可。 ② 每日裝載 每日裝載相對(duì)簡(jiǎn)單。只需要獲取ods_cart_info_inc當(dāng)日分區(qū)的數(shù)據(jù)處理后寫入事實(shí)表當(dāng)日分區(qū)即可。需要注意的是,從第二日開始我們可以采集到兩類加購操作,如下。 操作類型為insert,加購時(shí)間為create_time。操作類型為update且sku_num大于變更前的sku_num。當(dāng)前的sku_num存儲(chǔ)在data字段下,變更前的sku_num存儲(chǔ)在old字段下。加購時(shí)間為update_time。 圖解 分區(qū)規(guī)劃 數(shù)據(jù)流向 9.2 交易域下單事務(wù)事實(shí)表dwd_trade_order_detail_inc ③ 數(shù)據(jù)裝載 用戶執(zhí)行一次下單操作時(shí),業(yè)務(wù)庫的order_info表會(huì)新增一條數(shù)據(jù),order_detail表新增一至多條數(shù)據(jù),如果參加了活動(dòng),order_detail_activity新增一至多條數(shù)據(jù),如果使用了優(yōu)惠券,order_detail_coupon新增一至多條數(shù)據(jù)。因此,本節(jié)的事實(shí)數(shù)據(jù)來源于ods_order_detail_inc、ods_order_info_inc、ods_order_detail_activity_inc、ods_order_detail_coupon_inc。下單業(yè)務(wù)過程的最細(xì)粒度是一個(gè)用戶對(duì)一個(gè)sku的下單記錄,order_detail的粒度與之相同,因而將ods_order_detail_inc作為主表關(guān)聯(lián)其余三張表,與各從表的關(guān)聯(lián)方式及關(guān)聯(lián)字段如下。 ods_order_info_inc:關(guān)聯(lián)字段為order_id。訂單明細(xì)記錄一定有對(duì)應(yīng)的訂單記錄,反之亦然,因此join、left?join、right?join和full?join都是等價(jià)的,此處選擇left?join。ods_order_detail_activity_inc:關(guān)聯(lián)字段為order_detail_id。下單未必參與活動(dòng),因此主表數(shù)據(jù)在該表可能并沒有滿足關(guān)聯(lián)條件的數(shù)據(jù),要保留主表獨(dú)有數(shù)據(jù),只能用left join或full?join。該表中并不存在不滿足關(guān)聯(lián)條件的數(shù)據(jù),因?yàn)橛唵蚊骷?xì)活動(dòng)關(guān)聯(lián)表的order_detail_id必然不為null,因而left?join等價(jià)于full?join,此處選擇left join。ods_order_detail_coupon_inc:關(guān)聯(lián)字段為order_detail_id。關(guān)聯(lián)方式的分析與ods_order_detail_activity_inc同理,選擇left?join,不再贅述。 a)首日裝載 篩選上文提到的四張ODS表首日分區(qū)數(shù)據(jù),關(guān)聯(lián)起來,根據(jù)下單日期動(dòng)態(tài)分區(qū)寫入交易域下單事務(wù)事實(shí)表。 b)每日裝載 篩選上述四張ODS表每日分區(qū)數(shù)據(jù),關(guān)聯(lián)后寫入當(dāng)日分區(qū)。 (2)執(zhí)行步驟 ① 首日裝載 a)子查詢 篩選上述ODS層表首日分區(qū)的數(shù)據(jù),其中,四張下單事實(shí)相關(guān)的表都做了增量采集,首日要做全量同步,僅保留操作類型為bootstrap-insert類型的數(shù)據(jù),不需要額外的過濾條件。從這些ODS層表中選擇需要的字段,獲得四個(gè)子查詢:od、oi、act和cou,分別對(duì)應(yīng)ods_order_detail_inc、ods_order_info_inc、ods_order_detail_activity_inc和ods_order_detail_coupon_inc。 b)按照上文提到的關(guān)聯(lián)方式和條件將a)中得到的子查詢關(guān)聯(lián)起來,取自ods_order_detail_inc的create_time字段即下單時(shí)間,格式化為日期字符串,作為分區(qū)字段dt,篩選所須字段,通過dt動(dòng)態(tài)分區(qū)寫入dwd_trade_order_detail_inc。 ② 每日裝載 篩選上述ODS層表首日分區(qū)的數(shù)據(jù)。通常業(yè)務(wù)數(shù)據(jù)庫的原始表不會(huì)執(zhí)行刪除操作,增量表采集到的數(shù)據(jù)操作只有兩種類型insert和update。由上文分析可知,上述四張表只有insert操作類型的數(shù)據(jù)與下單業(yè)務(wù)過程相關(guān),因此僅保留這些表操作類型為insert的數(shù)據(jù)?;谶@些表獲得子查詢(處理邏輯與首日相同),關(guān)聯(lián)在一起。需要注意的是,每日裝載無須動(dòng)態(tài)分區(qū),將數(shù)據(jù)寫入當(dāng)日分區(qū)即可。 9.3 交易域支付成功事務(wù)事實(shí)表dwd_trade_pay_detail_suc_inc ③ 數(shù)據(jù)裝載 支付成功操作發(fā)生時(shí),原始業(yè)務(wù)庫的order_info表和payment_info表會(huì)發(fā)生變化,因此,本節(jié)需要獲取ods_order_info_inc和ods_payment_info_inc的數(shù)據(jù)。此外,我們還需要訂單明細(xì)等下單業(yè)務(wù)過程相關(guān)的表。支付成功業(yè)務(wù)過程最細(xì)粒度為一個(gè)用戶對(duì)一個(gè)sku(商品項(xiàng))的支付成功操作,payment_info和order_info的粒度只能具體到訂單,要拿到原子操作,就必須關(guān)聯(lián)訂單明細(xì)表,要知道活動(dòng)和優(yōu)惠券參與情況,還要關(guān)聯(lián)訂單明細(xì)活動(dòng)關(guān)聯(lián)和訂單明細(xì)優(yōu)惠券關(guān)聯(lián)表。最后,我們只會(huì)用到字典表的編碼和名稱兩個(gè)字段,單獨(dú)建維度表意義不大。因此做維度退化,將編碼名稱退化到事實(shí)表中。此處關(guān)聯(lián)字典表將支付類型名稱退化到事實(shí)表中。 找到這些表對(duì)應(yīng)的ODS層表,將它們關(guān)聯(lián)起來,主表應(yīng)為粒度最細(xì)的ods_order_detail_inc,與9.2相比,本節(jié)新增了ods_payment_info_inc、ods_base_dic(用于退化支付類型名稱字段),其他表關(guān)聯(lián)方式和關(guān)聯(lián)條件與9.2相同,不再贅述。 ods_payment_info_inc:通過order_id關(guān)聯(lián)。訂單明細(xì)表中有很多未支付成功的數(shù)據(jù),應(yīng)舍棄,關(guān)聯(lián)方式應(yīng)為join或right?join。我們會(huì)從ods支付表中篩選支付成功的數(shù)據(jù)(下文詳述),這部分?jǐn)?shù)據(jù)一定有對(duì)應(yīng)的訂單明細(xì),因而join等價(jià)于right?join,此處選擇join。ods_base_dic_full:通過主表的payment_type和字段表的dic_code字段關(guān)聯(lián),補(bǔ)全支付類型名稱字段。字典表中有很多不滿足關(guān)聯(lián)條件的數(shù)據(jù),即與支付類型無關(guān)的編碼,這部分?jǐn)?shù)據(jù)與本節(jié)無關(guān),應(yīng)舍棄。此外,主表payment_type的取值一定是字典表dic_code的子集,left?join等價(jià)于join,選擇left?join(參見上文)。考慮一個(gè)問題:發(fā)生在2022-06-05的支付成功操作應(yīng)與同一天的字典表數(shù)據(jù)關(guān)聯(lián),也就是說關(guān)聯(lián)時(shí)還應(yīng)限制主從表的dt字段相同。為什么此處沒有這個(gè)條件?這是因?yàn)闃I(yè)務(wù)數(shù)據(jù)庫沒有記錄歷史數(shù)據(jù)的功能,我們無法獲取首日之前的字典表數(shù)據(jù),只能退而求其次,全部與字典表的最新狀態(tài)(當(dāng)日全量采集的字典表數(shù)據(jù))關(guān)聯(lián)。因此,這里是存在一定誤差的。 最后,選擇所須字段,寫入對(duì)應(yīng)分區(qū)即可。 a)首日裝載 篩選ODS表首日數(shù)據(jù),關(guān)聯(lián)后根據(jù)支付成功日期動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表。 b)每日裝載 篩選ODS表每日數(shù)據(jù),關(guān)聯(lián)后寫入本節(jié)事實(shí)表當(dāng)日分區(qū)。 (2)執(zhí)行步驟 ① 首日裝載 a)子查詢 支付成功業(yè)務(wù)過程發(fā)生時(shí),payment_info表數(shù)據(jù)會(huì)發(fā)生變化,payment_status字段的值由1601變更為1602。首日全量同步掃描全表,ods_payment_info_inc的首日分區(qū)沒有記錄變更操作,不能通過上述條件篩選。實(shí)際上,payment_status變更為1602之后數(shù)據(jù)就不會(huì)再修改了,只要篩選滿足這一條件的數(shù)據(jù)即可。該條數(shù)據(jù)的update_time或callback_time(回調(diào)時(shí)間)即支付成功時(shí)間,本項(xiàng)目選用callback_time作為支付成功時(shí)間。此外,還要限定操作類型為bootstrap-insert,分區(qū)為首日,獲取的子查詢記為pi。 用于退化支付類型名稱的子查詢記為pay_dic,支付類型編碼的父級(jí)編碼為11,根據(jù)該條件從字典表首日分區(qū)篩選與支付類型相關(guān)的記錄即可。 其余子查詢與9.2相同。 b)關(guān)聯(lián) 按照上文所述關(guān)聯(lián),選取所須字段即可。取自ods_payment_info_inc的callback_time即支付成功時(shí)間,格式化為支付成功日期,作為分區(qū)字段,動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表即可。 ② 每日裝載 a)子查詢 每日同步可以采集到增量表的數(shù)據(jù)變化,ods_payment_info_inc的過濾條件更改:操作類型為update、payment_status為1602,且更改字段包含了payment_status(判斷old字段下是否包含該字段即可)。實(shí)際上,第三個(gè)條件是可以省略的,因?yàn)閜ayment_status變更為1602之后該條數(shù)據(jù)就不會(huì)再發(fā)生改變了,所以若操作類型為update,且變更后的payment_status為1602,payment_status字段一定發(fā)生了更改。通過上述條件,篩選當(dāng)日分區(qū)的數(shù)據(jù),選擇所須字段即可得到pi子查詢。 字典表子查詢處理邏輯與首日相同。 其余四張表都是下單業(yè)務(wù)過程相關(guān)的表,過濾條件相同。通常,用戶下單后必須在30分鐘內(nèi)支付成功,否則訂單會(huì)被取消,因此支付成功和下單業(yè)務(wù)過程發(fā)生的時(shí)間差在0到 30分鐘之間。當(dāng)日的支付成功操作需要關(guān)聯(lián)的訂單明細(xì)數(shù)據(jù)可能屬于昨日分區(qū),因此我們要篩選當(dāng)日和昨日兩個(gè)分區(qū)的數(shù)據(jù)。操作類型為insert或bootstrap-insert,這是因?yàn)樽蛉湛赡転閿?shù)倉上線首日,后者的操作類型均為bootstrap-insert。這兩個(gè)條件要同時(shí)滿足。再限定分區(qū)為當(dāng)日,即可獲得四個(gè)下單相關(guān)的子查詢。 b)關(guān)聯(lián) 將a)中得到的六個(gè)子查詢關(guān)聯(lián)起來,關(guān)聯(lián)方式及條件與首日裝載相同,寫入當(dāng)日分區(qū)即可。 9.4?交易域購物車周期快照事實(shí)表dwd_trade_cart_full 1)思路分析 ① 分區(qū)規(guī)劃 周期快照事實(shí)表記錄原始業(yè)務(wù)表每日的全量狀態(tài),每日一分區(qū)。 ② 數(shù)據(jù)流向 數(shù)據(jù)從ods_cart_info_full每日分區(qū)進(jìn)入dwd_trade_cart_full的當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 從ods_cart_info_full當(dāng)日分區(qū)篩選未下單的數(shù)據(jù)(is_ordered為0,已下單的數(shù)據(jù)打標(biāo)記,該字段為1,不應(yīng)計(jì)入存量統(tǒng)計(jì)),選取所須字段寫入當(dāng)日分區(qū)即可。 9.5 交易域交易流程累積快照事實(shí)表dwd_trade_trade_flow_acc 2)數(shù)據(jù)裝載 (1)思路分析 累積快照事實(shí)表與拉鏈表的處理邏輯非常相似,可以互為參考。 ① 分區(qū)規(guī)劃 累積快照事實(shí)表是為了處理涉及同一業(yè)務(wù)流程多個(gè)業(yè)務(wù)過程的指標(biāo)而設(shè)計(jì)的。它的分區(qū)規(guī)劃與拉鏈表完全相同,其中,9999-12-31分區(qū)存儲(chǔ)的是業(yè)務(wù)流程尚未結(jié)束的所有數(shù)據(jù),普通分區(qū)存儲(chǔ)的是業(yè)務(wù)流程在當(dāng)日結(jié)束的數(shù)據(jù)。 ② 數(shù)據(jù)流向 a)首日裝載 與拉鏈表不同,累積快照事實(shí)表的首日裝載也需要?jiǎng)討B(tài)分區(qū)。因?yàn)闅v史數(shù)據(jù)中一定會(huì)有業(yè)務(wù)流程已結(jié)束的數(shù)據(jù),這部分?jǐn)?shù)據(jù)應(yīng)進(jìn)入流程結(jié)束當(dāng)日分區(qū),也會(huì)有未完成的數(shù)據(jù),進(jìn)入9999-12-31分區(qū)。 b)每日裝載 業(yè)務(wù)流程在當(dāng)日結(jié)束的數(shù)據(jù)進(jìn)入當(dāng)日分區(qū),未完成的數(shù)據(jù)進(jìn)入9999-12-31分區(qū)。 ③ 數(shù)據(jù)裝載 本節(jié)僅篩選交易流程的三個(gè)關(guān)鍵結(jié)點(diǎn):下單、支付、確認(rèn)收貨。下單時(shí)間通過order_info的create_time字段獲取,支付操作通過payment_info的callback_time字段獲取,參考上文,不再贅述。order_status_log記錄了訂單表狀態(tài)的變更明細(xì),確認(rèn)收貨時(shí)該表會(huì)插入一條狀態(tài)為1006的數(shù)據(jù),create_time即確認(rèn)收貨時(shí)間。 下單、支付、確認(rèn)收貨是按照時(shí)間順序依次發(fā)生的,因此,在任意時(shí)刻下單數(shù)據(jù)中order_id的集合包含已支付order_id的集合,后者又包含已確認(rèn)收貨order_id的集合,顯然,應(yīng)將取自order_info表的子查詢作為主表,通過order_id字段left?join支付成功數(shù)據(jù),通過order_id字段left?join確認(rèn)收貨數(shù)據(jù)。 最后,選擇所須字段寫入對(duì)應(yīng)分區(qū)即可。 a)首日裝載 篩選ODS層相關(guān)表首日分區(qū)數(shù)據(jù),關(guān)聯(lián)后寫入所屬分區(qū)。確認(rèn)收貨時(shí)間即業(yè)務(wù)流程結(jié)束時(shí)間,若該字段不為null則說明業(yè)務(wù)流程已完成,進(jìn)入收貨日期對(duì)應(yīng)分區(qū),否則進(jìn)入9999-12-31分區(qū)。 b)每日裝載 篩選ODS層相關(guān)表每日分區(qū)數(shù)據(jù),與9999-12-31分區(qū)數(shù)據(jù)union、關(guān)聯(lián)后(下文詳述)寫入所屬分區(qū)。 (2)執(zhí)行步驟 ① 首日裝載 a)子查詢 篩選上述ods_order_info_inc、ods_payment_info_inc、ods_order_status_log_inc表首日分區(qū)操作類型為bootstrap-insert類型的數(shù)據(jù)。其中,ods_payment_info_inc還要限定payment_status為1602(支付成功狀態(tài)),ods_order_status_log_inc要限定order_status為1006(已完成,即確認(rèn)收貨)。按照前文所述關(guān)聯(lián)。 b)關(guān)聯(lián) 篩選所須字段,處理時(shí)間字段。其中,下單時(shí)間為取自訂單表的create_time,支付成功時(shí)間為取自支付表的callback_time,確認(rèn)收貨時(shí)間取自訂單流水表的create_time。如果確認(rèn)收貨時(shí)間不為null,說明業(yè)務(wù)流程結(jié)束,將其格式化為yyyy-MM-dd格式,作為分區(qū)字段dt的值,否則業(yè)務(wù)流程未結(jié)束,dt取值為9999-12-31。按照dt動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表。 ② 每日裝載 a)子查詢 與首日裝載相比篩選條件有所不同,如下。 ods_order_info_inc:篩選首日分區(qū)操作類型為insert的數(shù)據(jù)。ods_payment_info_inc:篩選首日分區(qū)操作類型為update、payment_status為1602且變更字段包含了payment_status(該條件可省略,參見9.3節(jié)說明)的數(shù)據(jù)。所得子查詢記為pi。ods_order_status_log_inc:篩選首日分區(qū)操作類型為insert,且order_status為1006的數(shù)據(jù)。所得子查詢記為log。 與首日裝載不同,每日裝載要考慮前日的9999-12-31分區(qū),這部分?jǐn)?shù)據(jù)并沒有完成業(yè)務(wù)流程,仍可能發(fā)生變更。我們需要將該分區(qū)數(shù)據(jù)與上述三個(gè)業(yè)務(wù)過程當(dāng)日新增的記錄合并,有兩種實(shí)現(xiàn)思路,如下。 i)第一步,根據(jù)上文所述篩選條件將下單、支付、確認(rèn)收貨業(yè)務(wù)過程當(dāng)日數(shù)據(jù)分別作為子查詢,關(guān)聯(lián)起來,關(guān)聯(lián)方式及條件、字段處理邏輯與首日相同。 第二步,篩選9999-12-31分區(qū)數(shù)據(jù),該分區(qū)數(shù)據(jù)分為兩種:已下單未支付,已支付未收貨(已收貨則業(yè)務(wù)流程結(jié)束,進(jìn)入收貨當(dāng)日分區(qū))。第一類記錄和當(dāng)日新增的下單記錄處理邏輯完全相同,與pi、log子查詢left?join即可。第二類記錄已支付,因而在pi子查詢中一定不會(huì)有對(duì)應(yīng)記錄,left?join關(guān)聯(lián)log即可。根據(jù)上述分析,似乎我們需要將9999-12-31分區(qū)的數(shù)據(jù)分成兩部分處理,比較麻煩。實(shí)際上,這兩類數(shù)據(jù)的處理完全可以統(tǒng)一,對(duì)于第二類數(shù)據(jù),pi表中一定不存在滿足關(guān)聯(lián)條件的記錄,left?join pi得到的子查詢與關(guān)聯(lián)前的主表完全一致,因此對(duì)于這部分?jǐn)?shù)據(jù),分別與pi、log關(guān)聯(lián)的結(jié)果和僅關(guān)聯(lián)log的結(jié)果是一樣的。綜上,我們只需要把第一步子查詢的主表替換為9999-12-31分區(qū)的數(shù)據(jù)即可。 第一步和第二步獲得的子查詢數(shù)據(jù)結(jié)構(gòu)完全相同,union在一起,通過dt字段動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表即可。 ii)觀察方法i)的實(shí)現(xiàn)步驟,可以發(fā)現(xiàn)當(dāng)日新增下單記錄和昨日未完成數(shù)據(jù)(9999-12-31分區(qū)數(shù)據(jù))處理邏輯完全一致,可以用union運(yùn)算符將二者合并起來,作為oi子查詢,而后與pi及l(fā)og通過left?join關(guān)聯(lián)即可。需要注意的是,待union的兩部分子查詢數(shù)據(jù)結(jié)構(gòu)不同,新增的下單記錄缺少五個(gè)字段,需要用null值或0.0填充,處理如下。 payment_date_id:在當(dāng)日新增下單記錄子查詢中補(bǔ)null。對(duì)于9999-12-31分區(qū)未完成的歷史數(shù)據(jù)(以下簡(jiǎn)稱歷史數(shù)據(jù)),若前日已支付,則oi子查詢中的支付日期不為null,直接獲取,否則可能在當(dāng)日支付,也可能不支付,無論哪種情況此時(shí)獲取pi子查詢中的支付日期即可。對(duì)于當(dāng)日新增的下單記錄,oi子查詢中的支付日期為null,最終的支付日期與pi子查詢中的支付日期保持一致。綜上,關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.payment_date_id,pi.payment_date_id)。payment_time:在當(dāng)日新增下單記錄子查詢中補(bǔ)null,關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.payment_time,pi.payment_time)。思路同上。finish_date_id:在當(dāng)日新增下單記錄子查詢中補(bǔ)null,關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.finish_date_id,log.finish_date_id)。與支付日期同理。finish_time:在當(dāng)日新增下單記錄子查詢中補(bǔ)null,關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.finish_time,log.finish_time)。與支付時(shí)間同理。payment_amount:在當(dāng)日新增下單記錄子查詢中補(bǔ)0.0。對(duì)于9999-12-31分區(qū)未完成的歷史數(shù)據(jù)(以下簡(jiǎn)稱歷史數(shù)據(jù)),若前日已支付,則oi子查詢中的支付金額就是我們要的值,直接獲取。否則可能在當(dāng)日支付,也可能不支付,對(duì)于前者,獲取pi子查詢中的支付金額即可,對(duì)于后者,pi子查詢中的支付金額為null,應(yīng)置為0.0,因次要在獲取pi.payment_amount后做空值處理,若為null則置為0.0。對(duì)于當(dāng)日新增的下單記錄,oi子查詢中的支付金額為0.0,支付金額應(yīng)取自pi子查詢的payment_amount字段,若該字段不為null則直接獲取,否則做空值處理,同上。綜上,關(guān)聯(lián)后取數(shù)邏輯為nvl(if(oi.payment_amount = 0.0, pi.payment_amount, oi.payment_amount), 0.0)。 最后,篩選所須字段,生成分區(qū)字段,寫入事實(shí)表。分區(qū)字段取數(shù)邏輯如下。 oi子查詢的兩類數(shù)據(jù)的完成日期一定為null:第一類數(shù)據(jù)位于9999-12-31分區(qū),業(yè)務(wù)流程未完成,確認(rèn)收貨日期一定為null,第二類數(shù)據(jù)的完成日期是我們補(bǔ)充的null值。因而只需要判斷l(xiāng)og子查詢中的完成日期即可。若log. finish_date_id不為null,說明流程在當(dāng)日結(jié)束,數(shù)據(jù)寫入當(dāng)日分區(qū),否則流程未完成,寫入9999-12-31分區(qū)。綜上,分區(qū)字段計(jì)算邏輯為:nvl(log.finish_date_id,'9999-12-31')。 本節(jié)選用方法ii)完成每日裝載。 ③ 特殊情況說明 實(shí)際上,上述交易流程并不是線性的,從下單到確認(rèn)收貨的任何一步都可能取消訂單,確認(rèn)收貨和取消訂單都可以終結(jié)交易流程。對(duì)于這類非線性過程的處理,通常有兩種處理方式,如下。 a)將確認(rèn)收貨和取消訂單都作為業(yè)務(wù)流程結(jié)束的標(biāo)志,將數(shù)據(jù)寫入業(yè)務(wù)流程結(jié)束日期對(duì)應(yīng)的分區(qū)。 b)如果取消訂單,將數(shù)據(jù)永久地保留在9999-12-31分區(qū),未達(dá)到的業(yè)務(wù)過程對(duì)應(yīng)里程碑字段置空。 兩種方式均可,本節(jié)選擇的是Plan?B。生產(chǎn)環(huán)境如何處理需要綜合考慮下游需求、企業(yè)硬件資源等因素,選取合適的方案。 9.6?工具域優(yōu)惠券使用(支付)事務(wù)事實(shí)表dwd_tool_coupon_used_inc ③ 數(shù)據(jù)裝載 用戶使用優(yōu)惠券支付時(shí),業(yè)務(wù)數(shù)據(jù)庫的coupon_use表的used_time會(huì)由null值變?yōu)橹Ц稌r(shí)間,即優(yōu)惠券使用(支付)時(shí)間。根據(jù)這個(gè)條件,我們可以篩選目標(biāo)數(shù)據(jù),將數(shù)據(jù)寫入業(yè)務(wù)過程發(fā)生日期對(duì)應(yīng)分區(qū)。 (2)執(zhí)行步驟 ① 首日裝載 從ods_coupon_use_inc表篩選首日分區(qū)操作類型為bootstrap-insert,且date.used_time不為null的數(shù)據(jù),將used_time格式化為yyyy-MM-dd格式的日期字符串,生成date_id和dt字段。篩選所須字段,根據(jù)dt字段動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入事實(shí)表。 ② 每日裝載 從ods_coupon_use_inc表篩選每日分區(qū)操作類型為update,且old字段下的keys包含了used_time的數(shù)據(jù)(更改了used_time字段)。該條件等價(jià)于date.used_time不為null,因?yàn)橛脩羰褂脙?yōu)惠券支付后,coupon_use表中的對(duì)應(yīng)記錄就不會(huì)再發(fā)生改變,只要used_time字段不為null,且操作類型為update,則used_time字段一定發(fā)生了更改。 最后,將used_time格式化為yyyy-MM-dd格式的日期字符串,生成date_id字段。篩選所須字段,將數(shù)據(jù)寫入事實(shí)表當(dāng)日分區(qū)。 9.7?互動(dòng)域收藏商品事務(wù)事實(shí)表dwd_interaction_favor_add_inc ③ 數(shù)據(jù)裝載 用戶收藏商品時(shí),業(yè)務(wù)數(shù)據(jù)庫的favor_info表會(huì)新增一條收藏記錄,create_time即收藏操作發(fā)生時(shí)間。 (2)執(zhí)行步驟 ① 首日裝載 從ods_favor_info_inc表篩選首日分區(qū)操作類型為bootstrap-insert的數(shù)據(jù),將create_time格式化為yyyy-MM-dd格式的日期字符串,生成date_id和dt字段。篩選所須字段,根據(jù)dt字段動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入事實(shí)表。 ② 每日裝載 從ods_?favor_info_inc表篩選每日分區(qū)操作類型為insert的數(shù)據(jù),將used_time格式化為yyyy-MM-dd格式的日期字符串,生成date_id字段。篩選所須字段,將數(shù)據(jù)寫入事實(shí)表當(dāng)日分區(qū)。 9.8?流量域頁面瀏覽事務(wù)事實(shí)表dwd_traffic_page_view_inc (1)思路分析 ① 分區(qū)規(guī)劃 按天分區(qū),數(shù)據(jù)進(jìn)入頁面日志生成時(shí)間對(duì)應(yīng)分區(qū)。 ② 數(shù)據(jù)流向 埋點(diǎn)服務(wù)通常是在企業(yè)成立大數(shù)據(jù)部門之后開始的,因而數(shù)倉上線時(shí),日志一般沒有歷史數(shù)據(jù),每日生成的頁面日志數(shù)據(jù)進(jìn)入當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 因?yàn)闆]有歷史數(shù)據(jù),因而日志數(shù)據(jù)的首日和每日處理邏輯相同,不作區(qū)分。只有頁面日志的page字段不為null,據(jù)此篩選頁面日志。 (2)執(zhí)行步驟 篩選ods_log_inc當(dāng)日分區(qū)的頁面日志數(shù)據(jù),將時(shí)間戳字段轉(zhuǎn)換為東八區(qū)的日期格式化字符串,補(bǔ)充時(shí)間字段,選取所須字段寫入事實(shí)表當(dāng)日分區(qū)。 9.9 用戶域用戶注冊(cè)事務(wù)事實(shí)表dwd_user_register_inc ② 數(shù)據(jù)流向 a)首日裝載 本節(jié)部分?jǐn)?shù)據(jù)來源于業(yè)務(wù)系統(tǒng),部分?jǐn)?shù)據(jù)來源于日志。日志是沒有歷史數(shù)據(jù)的,數(shù)倉上線首日,獲取來自業(yè)務(wù)系統(tǒng)的所有注冊(cè)記錄與當(dāng)日的注冊(cè)日志關(guān)聯(lián)以補(bǔ)充維度信息,只有注冊(cè)時(shí)間在當(dāng)日注冊(cè)記錄存在對(duì)應(yīng)的日志數(shù)據(jù),維度字段不為null,其余注冊(cè)記錄的維度字段均為null。關(guān)聯(lián)后的數(shù)據(jù)根據(jù)注冊(cè)日期進(jìn)入注冊(cè)當(dāng)日分區(qū)。 b)每日裝載 獲取來源于業(yè)務(wù)系統(tǒng)的當(dāng)日注冊(cè)記錄,與當(dāng)日注冊(cè)日志關(guān)聯(lián)后寫入當(dāng)日分區(qū)即可。 ③ 數(shù)據(jù)裝載 用戶注冊(cè)時(shí),業(yè)務(wù)數(shù)據(jù)庫的user_info表會(huì)新增一條數(shù)據(jù),create_time即注冊(cè)時(shí)間。同時(shí),會(huì)生成uid不為null的注冊(cè)頁面日志。除uid外,日志的common字段還記錄了其它維度信息,如品牌、渠道、省份、操作系統(tǒng)等,我們可以通過用戶ID將業(yè)務(wù)庫的注冊(cè)數(shù)據(jù)和日志關(guān)聯(lián)起來,補(bǔ)充注冊(cè)時(shí)的相關(guān)維度信息。 a)首日裝載 篩選ods_user_info_inc首日分區(qū)的數(shù)據(jù),及ods_log_inc首日分區(qū)的注冊(cè)頁面日志,通過user_id將二者關(guān)聯(lián)起來。要注意,注冊(cè)日期在首日之前的注冊(cè)記錄無法拿到頁面數(shù)據(jù),對(duì)于這部分?jǐn)?shù)據(jù),取自日志的維度字段只能置空。 b)每日裝載 篩選ods_user_info_inc每日分區(qū)的數(shù)據(jù),ods_log_inc每日分區(qū)的注冊(cè)頁面日志,通過user_id關(guān)聯(lián)。日志在傳輸過程中可能丟失,因此取自日志的維度字段也可能為空。 (2)執(zhí)行步驟 ① 首日裝載 a)子查詢 篩選ods_user_info_inc首日分區(qū)操作類型為bootstrap-insert的數(shù)據(jù),作為ui子查詢。篩選ods_log_inc首日分區(qū)頁面ID為register,且uid不為null的數(shù)據(jù),作為log子查詢。 b)上文提到,注冊(cè)操作可能并沒有對(duì)應(yīng)的頁面日志,因此以u(píng)i為主表,left?join關(guān)聯(lián)log,篩選所須字段,根據(jù)用戶表的創(chuàng)建時(shí)間生成yyyy-MM-dd格式的日期字符串,作為分區(qū)字段dt,根據(jù)dt動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入注冊(cè)當(dāng)日分區(qū)。 ② 每日裝載 a)子查詢 篩選ods_user_info_inc每日分區(qū)操作類型為insert的數(shù)據(jù),作為ui子查詢。篩選ods_log_inc每日分區(qū)頁面ID為register,且uid不為null的數(shù)據(jù),作為log子查詢。 b)上文提到,注冊(cè)操作對(duì)應(yīng)的頁面日志可能丟失,因此以u(píng)i為主表,left?join關(guān)聯(lián)log,篩選所須字段,將數(shù)據(jù)寫入當(dāng)日分區(qū)。 9.10?用戶域用戶登錄事務(wù)事實(shí)表dwd_user_login_inc 2)數(shù)據(jù)裝載 (1)思路分析 ① 分區(qū)規(guī)劃 登錄操作取自日志。一次會(huì)話是指從打開App到關(guān)閉App的一次完整業(yè)務(wù)流程。會(huì)話內(nèi)第一個(gè)uid不為null的頁面就對(duì)應(yīng)一次登錄操作。分區(qū)規(guī)劃同9.8。 ? (2)執(zhí)行步驟 ① 篩選當(dāng)日分區(qū)page字段不為null的數(shù)據(jù),且user_id不為null的數(shù)據(jù),獲取用戶處于登錄狀態(tài)的頁面日志。開窗,按照會(huì)話ID(common.sid)分區(qū),ts升序排列,調(diào)用row_number()為每條數(shù)據(jù)編號(hào),記為rn,選取ts、user_id及其它感興趣的字段,記為t1子查詢。 ③ 從t1子查詢中篩選rn為1的數(shù)據(jù),即每個(gè)會(huì)話首個(gè)user_id不為null的頁面瀏覽記錄,對(duì)應(yīng)登錄操作,選取所須字段,記為t2子查詢。 ④ 根據(jù)t2子查詢中的ts字段生成登錄日期和登錄時(shí)間字段,與其它感興趣的字段一并寫入當(dāng)日分區(qū)。 9.11 數(shù)據(jù)裝載腳本 首日每日分別寫腳本 10數(shù)倉開發(fā)之DWS層 設(shè)計(jì)要點(diǎn): (1)DWS層的設(shè)計(jì)參考指標(biāo)體系。 (2)DWS層的數(shù)據(jù)存儲(chǔ)格式為orc列式存儲(chǔ)+snappy壓縮。 (3)DWS層表名的命名規(guī)范為dws_數(shù)據(jù)域_統(tǒng)計(jì)粒度_業(yè)務(wù)過程_統(tǒng)計(jì)周期(1d/nd/td)。 注:1d表示最近1日,nd表示最近n日,td表示歷史至今。 10.1 最近1日匯總表 10.1.1 交易域用戶商品粒度訂單最近1日匯總表dws_trade_user_sku_order_1d 2)數(shù)據(jù)裝載 (1)思路分析 ① 分區(qū)規(guī)劃 按天分區(qū),避免全表掃描。 ② 數(shù)據(jù)流向 1日匯總表需要對(duì)歷史數(shù)據(jù)做匯總,首日裝載數(shù)據(jù)取自事實(shí)表所有分區(qū)和維度表當(dāng)日分區(qū)(維度表沒有歷史數(shù)據(jù)),匯總后進(jìn)入下單日期對(duì)應(yīng)分區(qū),每日裝載數(shù)據(jù)取自事實(shí)表和維度表當(dāng)日分區(qū),匯總后寫入當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 本節(jié)須匯總各用戶各sku最近1日的下單次數(shù)、下單件數(shù)、下單原始金額、活動(dòng)優(yōu)惠金額、優(yōu)惠券優(yōu)惠金額、最終金額,匯總后要補(bǔ)充sku的品類、品牌等維度信息。事實(shí)數(shù)據(jù)取自dwd_trade_order_detail_inc,維度數(shù)據(jù)取自dim_sku_full。 a)首日裝載 需要對(duì)歷史全量數(shù)據(jù)做統(tǒng)計(jì),匯總截止到首日每一天的統(tǒng)計(jì)指標(biāo)。下文同理,不再贅述。 b)每日裝載 匯總當(dāng)日數(shù)據(jù),計(jì)算最近1日的統(tǒng)計(jì)指標(biāo)即可。下文同理,不再贅述。 (2)執(zhí)行步驟 ① 首日裝載 a)od子查詢 從數(shù)據(jù)源事實(shí)表首日分區(qū)取數(shù),按照dt、user_id、sku_id分組聚合,統(tǒng)計(jì)所求指標(biāo)。 b)sku子查詢 篩選dim_sku_full首日分區(qū)數(shù)據(jù)即可。 c)關(guān)聯(lián) od子查詢記錄了最關(guān)鍵的數(shù)據(jù)(統(tǒng)計(jì)結(jié)果),將之作為主表,關(guān)聯(lián)sku的目的是為了補(bǔ)全維度信息,顯然要通過sku_id字段關(guān)聯(lián)。理論上,訂單表中的sku_id在從表中一定有記錄,因此left?join和join是等價(jià)的,full?join和right?join是等價(jià)的,而從表中無法與主表關(guān)聯(lián)的數(shù)據(jù)沒有意義,因此選擇left?join或join,此處選用left?join。 最后,選取所須字段,按照od的dt字段動(dòng)態(tài)分區(qū)寫入本節(jié)匯總表即可。 ② 每日裝載 與首日相比只有三處不同,如下。 a)od子查詢只取當(dāng)日數(shù)據(jù),分組字段中沒有dt。 b)sku子查詢篩選當(dāng)日數(shù)據(jù)。 c)寫入?yún)R總表當(dāng)日分區(qū)。 b)每日裝載 與首日裝載邏輯基本一致,區(qū)別在于od子查詢只取dwd_trade_order_detail_inc當(dāng)日分區(qū)的數(shù)據(jù) 10.1.2 交易域用戶粒度訂單最近1日匯總表dws_trade_user_order_1d 10.1.3 交易域用戶粒度加購最近1日匯總表dws_trade_user_cart_add_1d 10.1.4 交易域用戶粒度支付最近1日匯總表dws_trade_user_payment_1d 10.1.5 交易域省份粒度訂單最近1日匯總表dws_trade_province_order_1d 10.1.6 工具域用戶優(yōu)惠券粒度優(yōu)惠券使用(支付)最近1日匯總表dws_tool_user_coupon_coupon_used_1d 10.1.7 互動(dòng)域商品粒度收藏商品最近1日匯總表dws_interaction_sku_favor_add_1d 10.1.8 流量域會(huì)話粒度頁面瀏覽最近1日匯總表dws_traffic_session_page_view_1d 10.1.9 流量域訪客頁面粒度頁面瀏覽最近1日匯總表dws_traffic_page_visitor_page_view_1d 10.1.10 數(shù)據(jù)裝載腳本 首日每日分別裝載 10.2 最近n日匯總表 10.2.1 交易域用戶商品粒度訂單最近n日匯總表dws_trade_user_sku_order_nd 2)數(shù)據(jù)裝載 (1)思路分析 ① 分區(qū)規(guī)劃 通常,我們不會(huì)做歷史某天最近n日的匯總統(tǒng)計(jì),因此,這些表的分區(qū)是從首日開始的,每日分區(qū)記錄當(dāng)日的匯總結(jié)果,此外,最近n日匯總表數(shù)據(jù)裝載選取的數(shù)據(jù)源時(shí)間范圍相同,處理邏輯相同,不區(qū)分首日裝載和每日裝載。下文同理,不再贅述。 ② 數(shù)據(jù)流向 n日匯總表的數(shù)據(jù)可以取自事實(shí)表,也可以取自統(tǒng)計(jì)粒度相同而統(tǒng)計(jì)周期不同的1日匯總表(前提是存在對(duì)應(yīng)的1日匯總表),顯然應(yīng)選擇后者,復(fù)用1日匯總表的統(tǒng)計(jì)結(jié)果,減少重復(fù)計(jì)算。 本節(jié)匯總表存在對(duì)應(yīng)的1日匯總表,篩選后者最近n日分的數(shù)據(jù)匯總后寫入n日匯總表當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 ????????本節(jié)匯總最近7日和最近30日各用戶各商品下單次數(shù)、件數(shù)、原始金額、活動(dòng)優(yōu)惠金額、優(yōu)惠券優(yōu)惠金額和最終金額。事實(shí)數(shù)據(jù)取自dwd_trade_order_detail_inc,維度數(shù)據(jù)取自dim_sku_full。我們知道,匯總表的構(gòu)建是基于指標(biāo)體系完成的,業(yè)務(wù)過程、統(tǒng)計(jì)周期、統(tǒng)計(jì)粒度相同的指標(biāo)會(huì)由同一張匯總表計(jì)算得到。業(yè)務(wù)過程相同則數(shù)據(jù)源表相同,統(tǒng)計(jì)周期相同篩選數(shù)據(jù)的時(shí)間范圍相同,統(tǒng)計(jì)粒度相同則統(tǒng)計(jì)時(shí)的分組字段相同,剩余的指標(biāo)構(gòu)成組分:度量、聚合邏輯、業(yè)務(wù)限定(通過聚合函數(shù)加條件判斷結(jié)合使用實(shí)現(xiàn),如sum(if()))均可通過不同的字段實(shí)現(xiàn)。綜上,業(yè)務(wù)過程、統(tǒng)計(jì)周期和統(tǒng)計(jì)粒度相同的指標(biāo),統(tǒng)計(jì)時(shí)SQL的數(shù)據(jù)源表(from)、過濾條件(where?dt?xxx)和分組字段(group?by?xxx)相同,可以通過一個(gè)SQL子查詢完成統(tǒng)計(jì)而不需要關(guān)聯(lián)其它的事實(shí)數(shù)據(jù),清晰明了。 本節(jié)將最近7日和最近30日的指標(biāo)放在了一張表中,違反了上述規(guī)范,為什么這么做?此處主要是考慮到這兩種指標(biāo)除統(tǒng)計(jì)周期外全部相同,計(jì)算邏輯一致,為了減少匯總表的數(shù)量,便于管理,就將它們放在了一張表中。 有兩種方式將兩類指標(biāo)整合到同一張表中:通過一個(gè)字段標(biāo)記統(tǒng)計(jì)周期,或者為每個(gè)統(tǒng)計(jì)周期生成一份指標(biāo)字段。此處選擇后者。 生產(chǎn)環(huán)境可能執(zhí)行補(bǔ)數(shù)操作(重跑歷史某天的任務(wù)),因此需要實(shí)現(xiàn)冪等寫入,即多次執(zhí)行裝載語句,表中數(shù)據(jù)和執(zhí)行一次的結(jié)果是一樣的,否則補(bǔ)數(shù)可能會(huì)導(dǎo)致數(shù)據(jù)重復(fù)。本項(xiàng)目通過insert?overwrite語法實(shí)現(xiàn)這樣的效果。n日匯總表的數(shù)據(jù)通過每日裝載寫入當(dāng)日分區(qū),如果使用了兩個(gè)及以上的裝載語句,后面的語句會(huì)覆蓋前面的寫入,最終只會(huì)保留最后一個(gè)SQL的執(zhí)行結(jié)果,和執(zhí)行一個(gè)裝載語句效果是一樣的。 因此,我們需要將兩類指標(biāo)的處理合并到一個(gè)裝載語句中,可以用兩個(gè)子查詢分別統(tǒng)計(jì)最近7日和最近30日的指標(biāo),再通過唯一鍵user_id?+ sku_id將二者關(guān)聯(lián)起來。也可以在一個(gè)子查詢中完成所有指標(biāo)的計(jì)算,思路如下:篩選最近30日的事實(shí)數(shù)據(jù),運(yùn)用聚合函數(shù)+條件判斷(如sum(if()))通過對(duì)分區(qū)字段dt的判斷,限制數(shù)據(jù)范圍在最近7日內(nèi)可得最近7日的指標(biāo),去掉條件判斷可得最近30日的指標(biāo)。此處選擇第二種思路。 (2)執(zhí)行步驟 篩選最近30日dws_trade_user_sku_order_1d表所有分區(qū)的數(shù)據(jù),按照user_id,sku_id及其余的維度字段分組聚合,通過sum(if())組合調(diào)用統(tǒng)計(jì)最近7日的指標(biāo),通過sum()統(tǒng)計(jì)最近30日指標(biāo),寫入n日匯總表當(dāng)日分區(qū)。 10.2.2 交易域省份粒度訂單最近n日匯總表dws_trade_province_order_nd 10.2.3 數(shù)據(jù)裝載腳本 無需區(qū)分首日、每日數(shù)據(jù)裝載腳本 10.3 歷史至今匯總表 10.3.1?交易域用戶粒度訂單歷史至今匯總表dws_trade_user_order_td (1)思路分析 ① 分區(qū)規(guī)劃 歷史至今匯總表按天分區(qū),每日分區(qū)記錄截止當(dāng)日的統(tǒng)計(jì)結(jié)果,分區(qū)從數(shù)倉上線首日開始。 ② 數(shù)據(jù)流向 基于截止當(dāng)日的所有業(yè)務(wù)數(shù)據(jù)和當(dāng)日維度數(shù)據(jù)匯總統(tǒng)計(jì),統(tǒng)計(jì)結(jié)果寫入當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 歷史至今匯總表的事實(shí)數(shù)據(jù)可以取自事實(shí)表,也可以取自統(tǒng)計(jì)粒度、業(yè)務(wù)過程相同的最近1日匯總表。顯然應(yīng)選擇后者,減少重復(fù)計(jì)算。 歷史至今匯總表首日裝載和每日裝載選取的數(shù)據(jù)范圍相同(均為歷史至今),我們可以運(yùn)用相同的計(jì)算邏輯處理,但是每日裝載可以依賴于前一天的匯總結(jié)果,這樣只需要對(duì)當(dāng)前表前日分區(qū)數(shù)據(jù)和當(dāng)日新增事實(shí)數(shù)據(jù)做計(jì)算,而不必加載歷史全量的事實(shí)數(shù)據(jù),節(jié)省大量資源。這樣一來,首日裝載和每日裝載的計(jì)算邏輯就不同了,需要分別處理。 本節(jié)須統(tǒng)計(jì)各用戶歷史至今首次下單日期、歷史至今末次下單日期、歷史至今下單次數(shù)、歷史至今購買商品件數(shù)、歷史至今下單原始金額、歷史至今下單活動(dòng)優(yōu)惠金額、歷史至今下單優(yōu)惠券優(yōu)惠金額、歷史至今下單最終金額,寫入當(dāng)日分區(qū)。 (2)執(zhí)行步驟 ① 首日裝載 讀取dws_trade_user_order_1d所有分區(qū)的數(shù)據(jù),按照usre_id分組聚合,統(tǒng)計(jì)上述指標(biāo),寫入首日分區(qū)。 ② 每日裝載 每日裝載需要將dws_trade_user_order_td前日分區(qū)的數(shù)據(jù)和dws_trade_user_order_1d當(dāng)日分區(qū)的數(shù)據(jù)合并起來,即相同user_id的數(shù)據(jù)合并為一條,通過關(guān)聯(lián)實(shí)現(xiàn)。 顯然,關(guān)聯(lián)字段為user_id,考慮關(guān)聯(lián)方式。根據(jù)下單情況可以將用戶分為三類:當(dāng)日之前下過單且當(dāng)日沒有下單的用戶,當(dāng)日下單且當(dāng)日之前沒有下過單的用戶,當(dāng)日及之前都下過單的用戶。如果要將上文提到的兩部分?jǐn)?shù)據(jù)關(guān)聯(lián)起來,這三類用戶對(duì)應(yīng)主表中不滿足關(guān)聯(lián)條件的數(shù)據(jù),從表中不滿足關(guān)聯(lián)條件的數(shù)據(jù)以及兩張表中滿足關(guān)聯(lián)條件的數(shù)據(jù),它們都是需要保留的,因而用full?outer?join關(guān)聯(lián)。 a)old子查詢 讀取本節(jié)匯總表昨日分區(qū)的數(shù)據(jù),記為old子查詢。 b)new子查詢 讀取dws_trade_user_order_1d當(dāng)日分區(qū)的數(shù)據(jù),記為new子查詢。 c)裝載 通過user_id字段full?outer?join關(guān)聯(lián)兩個(gè)子查詢。字段處理如下。 user_id:兩張表的user_id至少有一個(gè)不為null,均不為null時(shí)取誰都一樣,因此調(diào)用nvl()當(dāng)其中一個(gè)表的user_id為null時(shí)取另一張表的user_id即可。order_date_first:若old.user_id不為null,說明單日之前有過下單記錄,首次下單日期應(yīng)為old.order_date_first。否則new.user_id必不為null,當(dāng)日必有下單記錄,為首次下單日期。order_date_last:若new.user_id不為null,說明當(dāng)日有下單記錄,末次下單日期為當(dāng)日,否則old.order_date_last不為null,當(dāng)日未下單,末次下單日期與昨日保持一致。order_count_td等度量字段:度量字段的處理邏輯相同,若為null則賦默認(rèn)值0,對(duì)nvl()處理后的兩表度量字段求和即可。 最后將數(shù)據(jù)寫入每日分區(qū)。 10.3.2?用戶域用戶粒度登錄歷史至今匯總表dws_user_user_login_td (1)思路分析 ① 分區(qū)規(guī)劃 同10.3.1。 ② 數(shù)據(jù)流向 首日裝載獲取用戶登錄事實(shí)表首日分區(qū)的數(shù)據(jù)(該表沒有歷史分區(qū),因此數(shù)倉上線首日該表只有一個(gè)分區(qū)),與用戶拉鏈表9999-12-31分區(qū)的用戶數(shù)據(jù)合并后寫入本節(jié)匯總表首日分區(qū)。每日裝載獲取匯總表前日分區(qū)和用戶登錄事實(shí)表當(dāng)日分區(qū)的數(shù)據(jù),合并后寫入?yún)R總表當(dāng)日分區(qū)。 ③ 數(shù)據(jù)裝載 本節(jié)需要統(tǒng)計(jì)各用戶歷史至今首次、末次登錄日期和累計(jì)登錄次數(shù)。登錄數(shù)據(jù)存儲(chǔ)在dwd_traffic_user_login表中,該表數(shù)據(jù)取自日志,數(shù)據(jù)不全。用戶注冊(cè)成功后自動(dòng)登錄,因此,注冊(cè)日期與首日登錄日期相同。對(duì)于登錄事實(shí)表中沒有記錄的用戶,將注冊(cè)日期作為末次登錄日期,歷史至今登錄次數(shù)記為1。 (2)執(zhí)行步驟 ① 首日裝載 我們需要獲取所有用戶的注冊(cè)日期和首日所有用戶的登錄記錄。注冊(cè)日期可以從dim_user_zip或dwd_user_register_inc獲取,此處選擇用戶拉鏈表。 a)u子查詢 從dim_user_zip的9999-12-31分區(qū)獲取全量最新的用戶信息,篩選user_id,create_time字段(注冊(cè)時(shí)間)。 b)l子查詢 從dwd_user_login_inc首日分區(qū)取數(shù)(首日該表只有一個(gè)分區(qū),不需要篩選條件),按照用戶分組聚合,統(tǒng)計(jì)行數(shù)即用戶登錄次數(shù),dt最大值作為末次登錄日期(即當(dāng)日,沒有按照dt分組,要篩選該字段必須聚合)。 c)裝載 按照user_id關(guān)聯(lián)u和l子查詢。前者包含了全量的用戶信息,應(yīng)作為主表,關(guān)聯(lián)方式為left?join(分析過程與上文同理)。關(guān)聯(lián)后字段處理如下。 user_id:選擇u.id作為user_id。login_date_last:若右表的末次登錄日期不為null說明當(dāng)日登錄,末次登錄日期因?yàn)楫?dāng)日,否則格式化create_time為yyyy-MM-dd格式的字符串,將其作為末次登錄日期。login_date_first:格式化create_time為yyyy-MM-dd格式,作為首次登錄日期。login_count_td:如果取自l子查詢的登錄次數(shù)不為null則將其作為歷史至今登錄日期,否則記為0。 寫入當(dāng)日分區(qū)即可。 ② 每日裝載 首日分區(qū)記錄了歷史全量的用戶登錄信息,不必從dim_user_zip或dwd_user_register_inc取數(shù),只須匯總dws_user_user_login_td昨日分區(qū)和dwd_user_login當(dāng)日的登錄記錄即可。實(shí)現(xiàn)思路與10.3.1類似,不再贅述。 10.3.3 數(shù)據(jù)裝載腳本 首日每日分別編寫 11 數(shù)倉開發(fā)之ADS層 11.1 流量主題 11.1.1 各渠道流量統(tǒng)計(jì)ads_traffic_stats_by_channel 需求說明如下 統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近1/7/30日 渠道 訪客數(shù) 統(tǒng)計(jì)訪問人數(shù) 最近1/7/30日 渠道 會(huì)話平均停留時(shí)長(zhǎng) 統(tǒng)計(jì)會(huì)話平均停留時(shí)長(zhǎng) 最近1/7/30日 渠道 會(huì)話平均瀏覽頁面數(shù) 統(tǒng)計(jì)會(huì)話平均瀏覽頁面數(shù) 最近1/7/30日 渠道 會(huì)話總數(shù) 統(tǒng)計(jì)會(huì)話總數(shù) 最近1/7/30日 渠道 跳出率 只有一個(gè)頁面的會(huì)話的比例 2)數(shù)據(jù)裝載 (1)思路分析 在dws_traffic_session_page_view_1d中,我們對(duì)每個(gè)會(huì)話的頁面訪問情況做了匯總,本節(jié)基于該表統(tǒng)計(jì)。 ADS層報(bào)表數(shù)據(jù)量不大,分區(qū)意義不大,通過dt字段區(qū)分統(tǒng)計(jì)日期即可。每日裝載將歷史報(bào)表數(shù)據(jù)取出,與當(dāng)日統(tǒng)計(jì)結(jié)果union后寫回該表即可。要注意,為了避免數(shù)據(jù)重復(fù),此處要用union去重。 (2)執(zhí)行步驟 ① 數(shù)據(jù)膨脹 我們將統(tǒng)計(jì)周期為最近1/7/30日的指標(biāo)放到了同一張表中。慣用的實(shí)現(xiàn)思路是用三個(gè)子查詢分別匯總最近1/7/30日的數(shù)據(jù)然后union起來,這樣相同的SQL邏輯需要編寫三次,略顯冗長(zhǎng),我們可以通過側(cè)寫 + 炸裂的方式精簡(jiǎn)。 這一步運(yùn)用lateral?view語法結(jié)合explode(array(1,7,30))將數(shù)據(jù)膨脹為三倍,補(bǔ)充recent_days字段,用于標(biāo)識(shí)這三份數(shù)據(jù),區(qū)分統(tǒng)計(jì)周期。然后在where子句中通過date_add()函數(shù)針對(duì)不同的recent_days過濾相應(yīng)時(shí)間范圍的數(shù)據(jù)。 LATERAL VIEW 是 HiveQL 中的一種語法,用于將一個(gè)表的列轉(zhuǎn)換為多行。它通常與 explode() 函數(shù)一起使用,用于將一個(gè)數(shù)組列拆分成多行。 ② 聚合 按照recent_days和渠道分組聚合,字段處理邏輯如下。 dt:統(tǒng)計(jì)日期,當(dāng)日。recent_days:取自分組字段,標(biāo)識(shí)統(tǒng)計(jì)周期。channel:取自分組字段,唯一標(biāo)識(shí)渠道。uv_count:訪客數(shù),統(tǒng)計(jì)mid的數(shù)量,需要去重。強(qiáng)轉(zhuǎn)為bigint類型。avg_duration_sec:平均停留還長(zhǎng),dws_traffic_session_page_view_1d表的一行對(duì)應(yīng)一個(gè)會(huì)話,調(diào)用avg()統(tǒng)計(jì)during_time_1d即得各渠道下會(huì)話平均停留時(shí)長(zhǎng),此時(shí)時(shí)間為毫秒數(shù),除以1000轉(zhuǎn)為秒,強(qiáng)轉(zhuǎn)為bigint類型。avg_page_count:平均頁面瀏覽數(shù),與avg_duration_sec同理,求page_count_1d均值即可,強(qiáng)轉(zhuǎn)為bigint類型。sv_count:會(huì)話數(shù),一行對(duì)應(yīng)一個(gè)會(huì)話,行數(shù)即會(huì)話數(shù),強(qiáng)轉(zhuǎn)為bigint類型。bounce_rate:跳出率,跳出會(huì)話數(shù)除以會(huì)話總數(shù)。跳出會(huì)話是只有一個(gè)頁面的會(huì)話,統(tǒng)計(jì)page_count_1d為1的會(huì)話數(shù)即可。統(tǒng)計(jì)結(jié)果強(qiáng)轉(zhuǎn)為decimal(16, 2)類型。 與報(bào)表歷史數(shù)據(jù)union后寫回即可。下文同理,不再贅述。 11.1.2 路徑分析ads_page_path 用戶路徑分析,顧名思義,就是指用戶在APP或網(wǎng)站中的訪問路徑。為了衡量網(wǎng)站優(yōu)化的效果或營(yíng)銷推廣的效果,以及了解用戶行為偏好,時(shí)常要對(duì)訪問路徑進(jìn)行分析。 用戶訪問路徑的可視化通常使用?;鶊D。如下圖所示,該圖可真實(shí)還原用戶的訪問路徑,包括頁面跳轉(zhuǎn)和頁面訪問次序。 桑基圖需要我們提供每種頁面跳轉(zhuǎn)的次數(shù),每個(gè)跳轉(zhuǎn)由source/target表示,source指跳轉(zhuǎn)起始頁面,target表示跳轉(zhuǎn)終到頁面。 2)數(shù)據(jù)裝載 (1)思路分析 將同一會(huì)話的頁面瀏覽記錄按照日志生成時(shí)間升序排列,每個(gè)頁面都可以作為source,排在source之后的就是與之對(duì)應(yīng)的target。每次頁面瀏覽都對(duì)應(yīng)一次跳轉(zhuǎn),只要確定了source和target,統(tǒng)計(jì)頁面瀏覽次數(shù)即可得所有組合的跳轉(zhuǎn)次數(shù)。 (2)執(zhí)行步驟 頁面跳轉(zhuǎn)信息隱含在頁面瀏覽記錄中,本節(jié)數(shù)據(jù)源為dwd_traffic_page_view_inc。 ①?t1子查詢 統(tǒng)計(jì)下頁ID:開窗,按照會(huì)話ID分區(qū),頁面訪問時(shí)間升序排列,調(diào)用lead()函數(shù)取下一行的page_id,取不到記為null,可得下頁ID,記為next_page_id。 統(tǒng)計(jì)當(dāng)前頁在所屬會(huì)話中的序號(hào):如果按照跳轉(zhuǎn)時(shí)間對(duì)同一會(huì)話中的頁面升序排列,相同的頁面跳轉(zhuǎn)組合在各自會(huì)話中的序號(hào)可能不同,不應(yīng)混為一談。開窗,按照session_id分區(qū),訪問時(shí)間升序排列,調(diào)用row_number()對(duì)當(dāng)前記錄編號(hào),記為rn。 ② t2子查詢 拼接page_id和rn獲得source,拼接next_page_id和rn+1獲得target(目標(biāo)頁的編號(hào)應(yīng)比當(dāng)前頁大1)。 ③ 統(tǒng)計(jì) 按照source和target分組聚合,統(tǒng)計(jì)行數(shù)即每種頁面組合的跳轉(zhuǎn)次數(shù)。要注意,當(dāng)source頁為會(huì)話末頁時(shí),target為null。ADS層表的數(shù)據(jù)最終要導(dǎo)入MySQL報(bào)表,我們會(huì)以source和target唯一標(biāo)識(shí)報(bào)表的一條數(shù)據(jù),將其作為聯(lián)合主鍵,而MySQL中主鍵字段不可為null,因而對(duì)target做處理,為null時(shí)轉(zhuǎn)換為字符串'null'。 11.2 用戶主題 11.2.1 用戶變動(dòng)統(tǒng)計(jì)ads_user_change 該需求包括兩個(gè)指標(biāo),分別為流失用戶數(shù)和回流用戶數(shù),以下為對(duì)兩個(gè)指標(biāo)的解釋說明。 指標(biāo) 說明 流失用戶數(shù) 之前活躍過的用戶,最近一段時(shí)間未活躍,就稱為流失用戶。此處要求統(tǒng)計(jì)7日前(只包含7日前當(dāng)天)活躍,但最近7日未活躍的用戶總數(shù)。 回流用戶數(shù) 之前的活躍用戶,一段時(shí)間未活躍(流失),今日又活躍了,就稱為回流用戶。此處要求統(tǒng)計(jì)回流用戶總數(shù)。 2)數(shù)據(jù)裝載 (1)思路分析 ① 流失用戶數(shù)計(jì)算思路 末次登錄日期至少比當(dāng)日早七天即為流失用戶,過濾條件為login_last_dt<=date_add('cur_date', -7),然而,此處只考慮當(dāng)日流失的用戶,如果用<=篩選數(shù)據(jù),那么昨日及之前流失的用戶也會(huì)被統(tǒng)計(jì)在內(nèi),因此這里應(yīng)該用=,即過濾條件為login_last_dt=date_add('cur_date',-7)。 ② 回流用戶數(shù)計(jì)算思路 當(dāng)日回流用戶是指流失之后,當(dāng)日登錄的用戶。由此可得兩個(gè)過濾條件:當(dāng)日登錄且昨日為流失用戶。流失用戶的判定上文已作詳解,那么,如何判定當(dāng)日登錄?最容易想到的辦法是根據(jù)登錄事實(shí)表記錄獲得當(dāng)日登錄過的user_id集合,對(duì)于每個(gè)用戶判定其user_id是否在該集合中,但這樣太過浪費(fèi)性能,不取。實(shí)際上,用戶在當(dāng)日登錄等價(jià)于其末次登錄日期為當(dāng)日,二者互為充要條件。因此,滿足條件一的數(shù)據(jù)可以通過限定當(dāng)日的login_last_dt為'cur_date'篩選。將昨日分區(qū)的login_last_dt命名為login_last_previous,則滿足條件二的數(shù)據(jù)可以限定login_last_previous<=date_add('cur_date', -8)篩選。這兩個(gè)過濾條件可以合并為login_last_previous<=date_add(login_last_dt, -8)。 上述條件等價(jià)于datediff(login_last_dt,login_last_previous)>=8。 (2)執(zhí)行步驟 ①?t1子查詢 篩選dws_user_user_login_td當(dāng)日分區(qū)的數(shù)據(jù),獲取各用戶歷史至今末次登錄日期。經(jīng)過上述分析,我們可以通過對(duì)比日期差來篩選回流數(shù)據(jù),此處不需要額外的過濾條件,然而,如果只是通過dt篩選,t1子查詢會(huì)對(duì)每個(gè)用戶保留一條數(shù)據(jù),只有當(dāng)日登錄過的有可能是回流用戶,可以通過login_date_last = '2022-06-08'篩選這部分?jǐn)?shù)據(jù),這樣可以極大削減t1子查詢的數(shù)據(jù)量,節(jié)省資源。 ② t2子查詢 篩選dws_user_user_login_td昨日分區(qū)的數(shù)據(jù),獲取各用戶昨日末次登錄日期。 ③ 統(tǒng)計(jì) 根據(jù)user_id將t1、t2關(guān)聯(lián)起來。t2的user_id集合是t1中user_id的超集,多出的那部分user_id對(duì)應(yīng)當(dāng)日新增用戶。我們要統(tǒng)計(jì)回流用戶數(shù)不需要考慮新增用戶,因此,使用join關(guān)聯(lián)即可。根據(jù)datediff(login_last_dt,login_last_previous)>=8篩選回流記錄,統(tǒng)計(jì)行數(shù),即得回流用戶數(shù)。 11.2.2 用戶留存率ads_user_retention 留存分析一般包含新增留存和活躍留存分析。 新增留存分析是分析某天的新增用戶中,有多少人有后續(xù)的活躍行為?;钴S留存分析是分析某天的活躍用戶中,有多少人有后續(xù)的活躍行為。 留存分析是衡量產(chǎn)品對(duì)用戶價(jià)值高低的重要指標(biāo)。 此處要求統(tǒng)計(jì)新增留存率,新增留存率具體是指留存用戶數(shù)與新增用戶數(shù)的比值,例如2022-06-08新增100個(gè)用戶,1日之后(2022-06-09)這100人中有80個(gè)人活躍了,那2022-06-08的1日留存數(shù)則為80,2022-06-08的1日留存率則為80%。 要求統(tǒng)計(jì)每天的1至7日留存率,如下圖所示。 (1)思路分析 我們無法統(tǒng)計(jì)注冊(cè)日期在當(dāng)日的1-7日留存率,因?yàn)楫?dāng)日拿不到未來7天的登錄數(shù)據(jù),因此統(tǒng)計(jì)時(shí)限定登錄日期為當(dāng)日,統(tǒng)計(jì)當(dāng)日前7-1天的1-7日留存率。 留存率涉及兩個(gè)業(yè)務(wù)過程,用戶注冊(cè)和用戶登錄。上文提到,當(dāng)日登錄等價(jià)于末次登錄日期為當(dāng)日。我們需要獲取用戶注冊(cè)日期和用戶末次登錄日期。但實(shí)際上,在當(dāng)前的業(yè)務(wù)場(chǎng)景下,注冊(cè)后自動(dòng)跳轉(zhuǎn)至登錄頁面,可以認(rèn)為注冊(cè)日期等價(jià)于首次登錄日期。我們?cè)谏衔囊呀?jīng)統(tǒng)計(jì)了歷史至今首次和末次登錄日期,從dws_user_user_login_td表中獲取即可。 (2)執(zhí)行步驟 從dws_user_user_login_td中篩選首次登錄日期在過去七日范圍內(nèi)的數(shù)據(jù),按照login_date_first分組,統(tǒng)計(jì)各組行數(shù)即得新增用戶數(shù),組合調(diào)用sum(if())統(tǒng)計(jì)當(dāng)日登錄的用戶即得對(duì)應(yīng)天數(shù)的留存用戶數(shù)。二者作比即得留存率。 11.2.3 用戶新增活躍統(tǒng)計(jì)ads_user_stats 需求說明如下 統(tǒng)計(jì)周期 指標(biāo) 指標(biāo)說明 最近1、7、30日 新增用戶數(shù) 略 最近1、7、30日 活躍用戶數(shù) 略 2)數(shù)據(jù)裝載 (1)思路分析 本需求對(duì)活躍的定義是限定時(shí)間范圍內(nèi)有登錄記錄,可以通過限制末次登錄日期在最近1/7/30日篩選活躍用戶。注冊(cè)日期等價(jià)于首次登錄日期,可以根據(jù)首次登錄日期篩選新增用戶。 (2)執(zhí)行步驟 從dws_user_user_login_td表中篩選當(dāng)日數(shù)據(jù),為精簡(jiǎn)SQL,通過側(cè)寫+炸裂,補(bǔ)充recent_days字段,將數(shù)據(jù)炸裂為三份,recent_days字段取值分別為1、7、30,標(biāo)識(shí)統(tǒng)計(jì)周期。我們只需要末次登錄日期或注冊(cè)日期在date_add(cur_date, -recent_days)(下文以threshold代指)及之后的數(shù)據(jù),過濾條件為login_date_last >=?threshold?or login_date_first >= threshold。而注冊(cè)日期必定小于等于末次登錄日期,只要login_date_last >=?threshold成立,login_date_first >=threshold必定成立,因而過濾條件可以精簡(jiǎn)為login_date_last?>= threshold。 接下來,按照recent_days分組聚合通過sum(if())統(tǒng)計(jì)滿足login_date_first >= threshold條件的行數(shù),即新增用戶數(shù),統(tǒng)計(jì)各組行數(shù)即得活躍用戶數(shù),將當(dāng)天日期作為dt字段的值,完成統(tǒng)計(jì)。 11.2.4 用戶行為漏斗分析ads_user_action 漏斗分析是一個(gè)數(shù)據(jù)分析模型,它能夠科學(xué)反映一個(gè)業(yè)務(wù)流程從起點(diǎn)到終點(diǎn)各階段用戶轉(zhuǎn)化情況。由于其能將各階段環(huán)節(jié)都展示出來,故哪個(gè)階段存在問題,就能一目了然。 該需求要求統(tǒng)計(jì)一個(gè)完整的購物流程各個(gè)階段的人數(shù),具體說明如下: 統(tǒng)計(jì)周期 指標(biāo) 說明 最近1?日 首頁瀏覽人數(shù) 略 最近1?日 商品詳情頁瀏覽人數(shù) 略 最近1?日 加購人數(shù) 略 最近1?日 下單人數(shù) 略 最近1?日 支付人數(shù) 支付成功人數(shù) 2)數(shù)據(jù)裝載 (1)思路分析 漏斗分析由五個(gè)派生指標(biāo)構(gòu)成,從各自的匯總表取數(shù)統(tǒng)計(jì),整合后寫入報(bào)表即可。 (2)執(zhí)行步驟 ① page子查詢 dws_traffic_page_visitor_page_view_1d一行對(duì)應(yīng)當(dāng)日一名訪客對(duì)一個(gè)頁面的瀏覽情況,按照page_id分組聚合統(tǒng)計(jì)行數(shù)即得當(dāng)日各頁面的瀏覽人數(shù)。從該表當(dāng)日分區(qū)篩選page_id為home或good_detail的數(shù)據(jù),通過sum(if())分別統(tǒng)計(jì)page_id為home和good_detail的行數(shù)即得當(dāng)日首頁瀏覽人數(shù)和當(dāng)日商品詳情頁瀏覽人數(shù)。補(bǔ)充recent_days,賦值為1,下同。 ② cart子查詢 dws_trade_user_cart_add_1d一行對(duì)應(yīng)當(dāng)日一名用戶的加購情況,行數(shù)即當(dāng)日加購用戶數(shù)。 ③ ord子查詢 dws_trade_user_order_1d一行對(duì)應(yīng)當(dāng)日一名用戶的下單情況,行數(shù)即當(dāng)日下單用戶數(shù)。 ④ pay子查詢 dws_trade_user_payment_1d一行對(duì)應(yīng)當(dāng)日一名用戶的支付情況,行數(shù)即當(dāng)日支付用戶數(shù)。 ⑤ 統(tǒng)計(jì) 通過recent_days將上述四個(gè)子查詢關(guān)聯(lián)起來,所有子查詢的recent_days均相同,內(nèi)連接即可。統(tǒng)計(jì)完成。 11.2.5 新增下單用戶統(tǒng)計(jì)ads_new_order_user_stats 需求說明如下。 統(tǒng)計(jì)周期 指標(biāo) 說明 最近1、7、30日 新增下單人數(shù) 略 2)數(shù)據(jù)裝載 (1)思路分析 首次下單日期在指定時(shí)間范圍內(nèi)即相應(yīng)的新增下單用戶。從dws_trade_user_order_td獲取各用戶的首次下單日期。 (2)執(zhí)行步驟 通過側(cè)寫+炸裂的方式將dws_trade_user_order_td的數(shù)據(jù)擴(kuò)充為三倍,補(bǔ)充recent_days字段,取值為1、7、30。通過order_date_first>=date_add('$do_date',-recent_days+1)篩選數(shù)據(jù),按照recent_days分組聚合,行數(shù)即相應(yīng)時(shí)間范圍的新增下單人數(shù)。 11.2.6 最近7日內(nèi)連續(xù)3日下單用戶數(shù)ads_order_continuously_user_count 2)數(shù)據(jù)裝載 (1)思路分析 連續(xù)三日下單的記錄有這樣的特點(diǎn):按照日期升序排列,那么一定存在某一個(gè)日期與它后兩行的日期相差兩天。據(jù)此可以篩選連續(xù)下單用戶數(shù)。 (2)執(zhí)行步驟 我們要獲取最近7日內(nèi)用戶的下單日期,數(shù)據(jù)取自dws_trade_user_order_1d,篩選最近7日分區(qū)的數(shù)據(jù)即可。開窗,按照用戶分區(qū),dt(下單日期)升序排列,調(diào)用lead()函數(shù)獲取后兩行的dt取值,而后用datadiff計(jì)算與dt的天數(shù)差,記為diff,上文提到,如果是連續(xù)三日下單的記錄,一定存在某條數(shù)據(jù)的diff為2。篩選diff=2的數(shù)據(jù),即可獲得所有滿足條件的用戶,如果連續(xù)下單的天數(shù)為4天及以上,那么同一用戶滿足條件的數(shù)據(jù)就不止一條,需要去重,因此調(diào)用count(distinct user_id)完成統(tǒng)計(jì)。 LEAD() 函數(shù)是 SQL 中的一種窗口函數(shù),用于獲取當(dāng)前行之后的指定行的數(shù)據(jù)。它常用于在結(jié)果集中獲取當(dāng)前行后面的行的數(shù)據(jù),例如獲取下一個(gè)時(shí)間點(diǎn)的數(shù)據(jù),或者計(jì)算相鄰行之間的差值等。 LEAD(expression, offset, default_value) OVER (partition_clause ORDER BY order_clause) select * from ads_order_continuously_user_count union select '2022-06-08', 7, count(distinct(user_id)) from ( select user_id, datediff(lead(dt,2,'9999-12-31') over(partition by user_id order by dt),dt) diff from dws_trade_user_order_1d where dt>=date_add('2022-06-08',-6) )t1 where diff=2; 11.3 商品主題 1.3.1 最近30日各品牌復(fù)購率ads_repeat_purchase_by_tm 需求說明如下。 統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近30日 品牌 復(fù)購率 重復(fù)購買人數(shù)占購買人數(shù)比例 2)數(shù)據(jù)裝載 (1)思路分析 復(fù)購率可以基于支付或下單記錄統(tǒng)計(jì),具體如何選擇需要考慮具體的業(yè)務(wù)情況和數(shù)據(jù)特點(diǎn)。 如果希望更加準(zhǔn)確地反映用戶的購買行為,并且排除訂單取消和退款等因素的影響,那么以支付為基準(zhǔn)統(tǒng)計(jì)復(fù)購率是一個(gè)更好的選擇。這樣能夠更加準(zhǔn)確地反映用戶的實(shí)際購買行為。 如果希望更加全面地了解用戶的購買情況,以下單為基準(zhǔn)更加合理。這樣可以獲得更全面、準(zhǔn)確、詳細(xì)的客戶購買行為數(shù)據(jù),包括客戶的購買意向、購買渠道等,這些數(shù)據(jù)可以用于更深入的分析和挖掘,為企業(yè)提供更多有價(jià)值的商業(yè)洞察。 綜上所述,以支付為基準(zhǔn)或者以訂單為基準(zhǔn)統(tǒng)計(jì)復(fù)購率都有其優(yōu)缺點(diǎn),應(yīng)根據(jù)實(shí)際情況選擇合適的方法。在實(shí)際操作中,也可以采用多種統(tǒng)計(jì)方式綜合分析,以得到更加全面和準(zhǔn)確的結(jié)果。 此處選擇以下單為基準(zhǔn)統(tǒng)計(jì)復(fù)購率。 最近30日各品牌復(fù)購率是最近30日內(nèi)各品牌至少下單兩次的用戶占所有下單人數(shù)的比例。統(tǒng)計(jì)最近30日內(nèi)各用戶對(duì)各品牌的下單次數(shù),在此基礎(chǔ)上,按照tm_id分組聚合,統(tǒng)計(jì)各品牌下單至少兩次和至少一次的用戶數(shù),作比即得復(fù)購率。 (2)執(zhí)行步驟 ① t1子查詢 篩選dws_trade_user_sku_order_nd當(dāng)日分區(qū)的數(shù)據(jù),按照user_id,tm_id,tm_name分組聚合,對(duì)order_count_30d求和,統(tǒng)計(jì)最近30日各用戶各品牌下單次數(shù)。 ② 統(tǒng)計(jì) 按照tm_id,tm_name分組聚合,調(diào)用sum(if(order_count>=2,1,0))統(tǒng)計(jì)下單至少兩次的用戶數(shù),sum(if(order_count>=1,1,0))統(tǒng)計(jì)下單至少一次的用戶數(shù),作比即得復(fù)購率,統(tǒng)計(jì)完成。實(shí)際上,order_count一定大于0,因?yàn)槿绻麤]有下單,t1表中就不會(huì)有對(duì)應(yīng)記錄,因此,行數(shù)即為下單用戶數(shù)。 11.3.2 各品牌商品下單統(tǒng)計(jì) 需求說明如下 統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近1、7、30日 品牌 下單數(shù) 略 (2)執(zhí)行步驟 ① 最近1日指標(biāo) 從dws_trade_user_sku_order_1d篩選當(dāng)然數(shù)據(jù),按照tm_id,tm_name分組,對(duì)order_count_1d求和統(tǒng)計(jì)訂單數(shù),去重對(duì)user_id計(jì)數(shù)統(tǒng)計(jì)下單用戶數(shù)。 ② 最近n日指標(biāo) 通過側(cè)寫+炸裂方式的方式將dws_trade_user_sku_order_nd數(shù)據(jù)膨脹為兩份,補(bǔ)充字段recent_days標(biāo)識(shí)這兩部分?jǐn)?shù)據(jù)。篩選當(dāng)日分區(qū)數(shù)據(jù),通過case?when語法根據(jù)recent_days的不同取值獲取最近n日各用戶各sku的下單次數(shù),記為order_count。篩選recent_days、user_id、tm_id、tm_name、order_count字段,作為t1子查詢。 在t1的基礎(chǔ)上按照tm_id,tm_name分組聚合,統(tǒng)計(jì)下單數(shù)次數(shù)和下單用戶數(shù)即可。 ③ 統(tǒng)計(jì) 合并①、②的統(tǒng)計(jì)結(jié)果,兩部分查詢結(jié)果的recent_days一定不同,不存在重復(fù)數(shù)據(jù),去重?zé)o意義,因此通過union?all合并,作為odr子查詢。篩選需要的字段,補(bǔ)充dt(當(dāng)天日期),完成統(tǒng)計(jì)。 11.3.3 各品類商品下單統(tǒng)計(jì) 需求說明如下 統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近1、7、30日 品類 下單數(shù) 略 最近1、7、30日 品類 下單人數(shù) 略 11.3.4 各品類商品購物車存量Top3 (1)思路分析 本節(jié)需求是典型的分組TopN,統(tǒng)計(jì)當(dāng)日各品類商品購物車存量Top3。 (2)執(zhí)行步驟 ① cart子查詢 要對(duì)各品類商品購物車存量做排名,首先要獲取各商品的購物車存量。購物車相關(guān)數(shù)據(jù)存儲(chǔ)在業(yè)務(wù)數(shù)據(jù)庫的cart_info表中,這是一張事實(shí)相關(guān)的表,通常做增量采集,如果基于該表的增量數(shù)據(jù)統(tǒng)計(jì),先要將cart_info表的變更數(shù)據(jù)和首日全量數(shù)據(jù)累加得出當(dāng)日各用戶各商品的購物車存量,才能進(jìn)行后續(xù)統(tǒng)計(jì)。cart_info的變化對(duì)應(yīng)兩個(gè)業(yè)務(wù)過程:加購和減購操作,在DWD會(huì)對(duì)應(yīng)兩個(gè)事實(shí)表,缺一不可,這種統(tǒng)計(jì)方式非常繁瑣的。為了簡(jiǎn)化指標(biāo)計(jì)算,我們對(duì)cart_info做了全量同步,并在DWD層維護(hù)了一張周期快照事實(shí)表dwd_trade_cart_full。這樣一來,可以通過該表直接獲取各用戶各商品的存量信息,分組聚合獲取各品類商品購物車存量,分組排名取前三即可完成統(tǒng)計(jì)。 ② sku子查詢 篩選dim_sku_full當(dāng)日分區(qū)的數(shù)據(jù),獲取sku的詳細(xì)信息。 ③ t1子查詢 以sku_id作為關(guān)聯(lián)字段,將cart子查詢和sku子查詢關(guān)聯(lián)起來,關(guān)聯(lián)方式為left?join(分析過程參見上文)。開窗,按照品類分區(qū),購物車存量降序排列,調(diào)用rank()函數(shù)為商品排名,記為rk。篩選其他感興趣的字段,記為t1子查詢。 ④ 統(tǒng)計(jì) 篩選t1子查詢中rk<=3的數(shù)據(jù),完成統(tǒng)計(jì)。 11.3.5 各品牌商品收藏次數(shù)Top3 2)數(shù)據(jù)裝載 (1)思路分析 統(tǒng)計(jì)當(dāng)日各品牌商品收藏次數(shù)Top3。 (2)執(zhí)行步驟 ① t1子查詢 從dws_interaction_sku_favor_add_1d篩選當(dāng)日數(shù)據(jù),開窗,按照tm_id分區(qū),按照當(dāng)日收藏次數(shù)降序排列,調(diào)用rank()排名,記為rk。 ② 統(tǒng)計(jì) 篩選rk<=3的數(shù)據(jù),完成統(tǒng)計(jì)。 11.4 交易主題 11.4.1 下單到支付時(shí)間間隔平均值 2)數(shù)據(jù)裝載 (1)思路分析 本節(jié)需求涉及了電商交易流程的下單和支付業(yè)務(wù)過程。要統(tǒng)計(jì)下單到支付的時(shí)間間隔,最簡(jiǎn)單的思路是獲取當(dāng)日的所有支付記錄和最近2日的所有下單記錄(通常下單和支付的時(shí)間間隔在30min以內(nèi),如果下單時(shí)間在前一天的24點(diǎn)附近,支付操作很可能發(fā)生在第二天,因此和當(dāng)日支付記錄對(duì)應(yīng)的下單記錄可能屬于前一天分區(qū),因獲取最近2日的數(shù)據(jù)),按照order_id關(guān)聯(lián),用支付時(shí)間減去下單時(shí)間,求其均值即可。 上述思路計(jì)算邏輯非常簡(jiǎn)單,但通常不會(huì)這么做。下單和支付是電商系統(tǒng)的核心業(yè)務(wù),每日會(huì)有大量的記錄,大表join是我們應(yīng)極力避免的。它會(huì)帶來極大的資源開銷,一旦發(fā)生數(shù)據(jù)傾斜,甚至有可能執(zhí)行失敗。為了從根本上避免大表join,在DWD層構(gòu)建了累積快照事實(shí)表,該表記錄了一個(gè)業(yè)務(wù)流程多個(gè)業(yè)務(wù)過程的關(guān)鍵時(shí)間節(jié)點(diǎn)(里程碑),省去關(guān)聯(lián)操作。 (2)執(zhí)行步驟 dwd_trade_trade_flow_acc記錄了下單、支付、確認(rèn)收貨三個(gè)業(yè)務(wù)過程,當(dāng)確認(rèn)收貨后認(rèn)為業(yè)務(wù)流程結(jié)束,數(shù)據(jù)進(jìn)入確認(rèn)收貨日期對(duì)應(yīng)分區(qū)。當(dāng)日支付的記錄有兩種去向,如果確認(rèn)收貨則進(jìn)入當(dāng)日分區(qū),否則保存在9999-12-31分區(qū)。篩選這兩個(gè)分區(qū)支付日期為當(dāng)日的數(shù)據(jù),統(tǒng)計(jì)其下單到支付的平均時(shí)間間隔即可。 11.4.2 各省份交易統(tǒng)計(jì) 11.5 優(yōu)惠券主題 11.5.1?優(yōu)惠券使用統(tǒng)計(jì) 11.6 數(shù)據(jù)裝載腳本 12 報(bào)表數(shù)據(jù)導(dǎo)出 為方便報(bào)表應(yīng)用使用數(shù)據(jù),需將ads各指標(biāo)的統(tǒng)計(jì)結(jié)果導(dǎo)出到MySQL數(shù)據(jù)庫中。 12.1 MySQL建庫建表 12.1.1 創(chuàng)建數(shù)據(jù)庫 12.1.2 結(jié)合ADS統(tǒng)計(jì)數(shù)據(jù)分別創(chuàng)建表 12.2 數(shù)據(jù)導(dǎo)出 數(shù)據(jù)導(dǎo)出工具選用DataX,選用HDFSReader和MySQLWriter。 12.2.1 編寫DataX配置文件 { "job": { "content": [ { "reader": { "name": "hdfsreader", "parameter": { "column": [ "*" ], "defaultFS": "hdfs://hadoop102:8020", "encoding": "UTF-8", "fieldDelimiter": "\t", "fileType": "text", "nullFormat": "\\N", "path": "${exportdir}" } }, "writer": { "name": "mysqlwriter", "parameter": { "column": [ "dt", "recent_days", "channel", "uv_count", "avg_duration_sec", "avg_page_count", "sv_count", "bounce_rate" ], "connection": [ { "jdbcUrl": "jdbc:mysql://hadoop102:3306/gmall_report?useUnicode=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true", "table": [ "ads_traffic_stats_by_channel" ] } ], "password": "000000", "username": "root", "writeMode": "replace" } } } ], "setting": { "errorLimit": { "percentage": 0.02, "record": 0 }, "speed": { "channel": 3 } } } } 12.2.2 DataX配置文件生成腳本 使用采集部分提到的生成器生成DataX配置文件。 1)修改/opt/module/gen_datax_config/configuration.properties文件 mysql.username=root mysql.password=000000 mysql.host=hadoop102 mysql.port=3306 mysql.database.import=gmall # 從HDFS導(dǎo)出進(jìn)入的 MySQL 數(shù)據(jù)庫名稱 mysql.database.export=gmall_report mysql.tables.import=activity_info,activity_rule,base_trademark,cart_info,base_category1,base_category2,base_category3,coupon_info,sku_attr_value,sku_sale_attr_value,base_dic,sku_info,base_province,spu_info,base_region,promotion_pos,promotion_refer # MySQL 庫中需要導(dǎo)出的表,空串表示導(dǎo)出庫的所有表 mysql.tables.export= is.seperated.tables=0 hdfs.uri=hdfs://hadoop102:8020 import_out_dir=/opt/module/datax/job/import # DataX 導(dǎo)出配置文件存放路徑 export_out_dir=/opt/module/datax/job/export 2)執(zhí)行配置文件生成器 3)查看配置文件存放路徑 12.2.3 測(cè)試生成的DataX配置文件 12.2.4 編寫每日導(dǎo)出腳本 13 數(shù)據(jù)倉庫工作流調(diào)度 13.1 調(diào)度工具部署DolphinScheduler 13.2 新數(shù)據(jù)生成 1)啟動(dòng)采集通道,包括Kafka、Flume等 (1)啟動(dòng)Zookeeper [atguigu@hadoop102 ~]$ zk.sh start (2)啟動(dòng)Kafka [atguigu@hadoop102 ~]$ kf.sh start (3)啟動(dòng)Flume [atguigu@hadoop102 ~]$ f1.sh start [atguigu@hadoop102 ~]$ f2.sh start [atguigu@hadoop102 ~]$ f3.sh start 2)修改日志模擬器配置文件 修改hadoop102的/opt/module/applog/application.yml文件,修改mock.date參數(shù)如下 mock.date: "2022-06-09" 3)修改/opt/module/maxwell/config.properties文件 [atguigu@hadoop102 maxwell]$ vim /opt/module/maxwell/config.properties 4)修改mock_date參數(shù)設(shè)置如下 mock_date=2022-06-09 5)啟動(dòng)Maxwell [atguigu@hadoop102 ~]$ mxw.sh start 6)執(zhí)行日志生成腳本 [atguigu@hadoop102 ~]$ lg.sh 7)觀察HDFS上是否有2022-06-09的日志數(shù)據(jù)和增量表數(shù)據(jù)生成 13.3 工作流調(diào)度實(shí)操 13.3.1 DolphinScheduler集群模式 13.3.2 DolphinScheduler單機(jī)模式 針對(duì)虛擬機(jī)內(nèi)存不足情況使用 柚子快報(bào)邀請(qǐng)碼778899分享:大數(shù)據(jù) 離線數(shù)倉-數(shù)據(jù)倉庫系統(tǒng) 精彩文章
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理,出于傳遞更多信息之目的,不代表金鑰匙跨境贊同其觀點(diǎn)和立場(chǎng)。
轉(zhuǎn)載請(qǐng)注明,如有侵權(quán),聯(lián)系刪除。