欧美free性护士vide0shd,老熟女,一区二区三区,久久久久夜夜夜精品国产,久久久久久综合网天天,欧美成人护士h版

首頁綜合 正文
目錄

柚子快報(bào)邀請(qǐng)碼778899分享:大數(shù)據(jù) 離線數(shù)倉-數(shù)據(jù)倉庫系統(tǒng)

FNAC文化購綜合2025-05-05480

柚子快報(bào)邀請(qǐng)碼778899分享:大數(shù)據(jù) 離線數(shù)倉-數(shù)據(jù)倉庫系統(tǒng)

http://yzkb.51969.com/

1數(shù)據(jù)倉庫概述

數(shù)據(jù)倉庫核心架構(gòu)

ODS層(Operational Data Store):運(yùn)營數(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 營銷坑位表(全量表)ods_promotion_pos_full

7.2.17 營銷渠道表(全量表)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)。基于維度建模理論,我們要確定主維表,將商品維度相關(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 營銷坑位維度表dim_promotion_pos_full

③ 數(shù)據(jù)裝載

原始業(yè)務(wù)數(shù)據(jù)庫中只有promotion_pos表與營銷坑位維度相關(guān),從ods_promotion_pos_full表中篩選2022-06-08分區(qū)的數(shù)據(jù),選擇所須字段寫入dim_promotion_pos_full表的2022-06-08分區(qū)即可。

8.6 營銷渠道維度表dim_promotion_refer_full

③ 數(shù)據(jù)裝載

原始業(yè)務(wù)數(shù)據(jù)庫中只有promotion_refer表與營銷渠道維度相關(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)營商相關(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)化的效果或營銷推廣的效果,以及了解用戶行為偏好,時(shí)常要對(duì)訪問路徑進(jìn)行分析。

用戶訪問路徑的可視化通常使用桑基圖。如下圖所示,該圖可真實(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)

http://yzkb.51969.com/

精彩文章

評(píng)論可見,查看隱藏內(nèi)容

本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理,出于傳遞更多信息之目的,不代表金鑰匙跨境贊同其觀點(diǎn)和立場(chǎng)。

轉(zhuǎn)載請(qǐng)注明,如有侵權(quán),聯(lián)系刪除。

本文鏈接:http://gantiao.com.cn/post/18730153.html

發(fā)布評(píng)論

您暫未設(shè)置收款碼

請(qǐng)?jiān)谥黝}配置——文章設(shè)置里上傳

掃描二維碼手機(jī)訪問

文章目錄