數(shù)據(jù)倉(cāng)庫(kù)維度模型設(shè)計(jì)

維度建模基本概念

維度模型是數(shù)據(jù)倉(cāng)庫(kù)領(lǐng)域大師Ralph Kimall所倡導(dǎo)筝蚕，他的《數(shù)據(jù)倉(cāng)庫(kù)工具箱》，是數(shù)據(jù)倉(cāng)庫(kù)工程領(lǐng)域最流行的數(shù)倉(cāng)建模經(jīng)典个束。維度建模以分析決策的需求出發(fā)構(gòu)建模型，構(gòu)建的數(shù)據(jù)模型為分析需求服務(wù)躬络，因此它重點(diǎn)解決用戶(hù)如何更快速完成分析需求尖奔，同時(shí)還有較好的大規(guī)模復(fù)雜查詢(xún)的響應(yīng)性能。

維度建模是專(zhuān)門(mén)應(yīng)用于分析型數(shù)據(jù)庫(kù) 數(shù)據(jù)倉(cāng)庫(kù) 數(shù)據(jù)集市建模的方法穷当。數(shù)據(jù)集市可以理解為是一種"小型數(shù)據(jù)倉(cāng)庫(kù)"提茁。

事實(shí)表

發(fā)生在現(xiàn)實(shí)世界中的操作型事件，其所產(chǎn)生的可度量數(shù)值馁菜，存儲(chǔ)在事實(shí)表中茴扁。從最低的粒度級(jí)別來(lái)看，事實(shí)表行對(duì)應(yīng)一個(gè)度量事件汪疮，反之亦然。

事實(shí)表表示對(duì)分析主題的度量。比如一次購(gòu)買(mǎi)行為我們就可以理解為是一個(gè)事實(shí)厘托。

事實(shí)表
事實(shí)表

圖中的訂單表就是一個(gè)事實(shí)表就珠，你可以理解他就是在現(xiàn)實(shí)中發(fā)生的一次操作型事件，我們每完成一個(gè)訂單剩晴，就會(huì)在訂單中增加一條記錄。

事實(shí)表的特征：表里沒(méi)有存放實(shí)際的內(nèi)容，他是一堆主鍵的集合载碌，這些ID分別能對(duì)應(yīng)到維度表中的一條記錄。事實(shí)表包含了與各維度表相關(guān)聯(lián)的外鍵衅枫，可與維度表關(guān)聯(lián)嫁艇。事實(shí)表的度量通常是數(shù)值類(lèi)型，且記錄數(shù)會(huì)不斷增加弦撩，表數(shù)據(jù)規(guī)模迅速增長(zhǎng)步咪。

維度表

每個(gè)維度表都包含單一的主鍵列。維度表的主鍵可以作為與之關(guān)聯(lián)的任何事實(shí)表的外鍵孤钦，當(dāng)然歧斟，維度表行的描述環(huán)境應(yīng)與事實(shí)表行完全對(duì)應(yīng)。維度表通常比較寬偏形，是扁平型非規(guī)范表静袖，包含大量的低粒度的文本屬性。

維度表示你要對(duì)數(shù)據(jù)進(jìn)行分析時(shí)所用的一個(gè)量,比如你要分析產(chǎn)品銷(xiāo)售情況, 你可以選擇按類(lèi)別來(lái)進(jìn)行分析,或按區(qū)域來(lái)分析俊扭。這樣的按..分析就構(gòu)成一個(gè)維度队橙。上圖中的用戶(hù)表 商家表 時(shí)間表這些都屬于維度表，這些表都有一個(gè)唯一的主鍵萨惑，然后在表中存放了詳細(xì)的數(shù)據(jù)信息捐康。

總的說(shuō)來(lái)，在數(shù)據(jù)倉(cāng)庫(kù)中不需要嚴(yán)格遵守規(guī)范化設(shè)計(jì)原則庸蔼。因?yàn)閿?shù)據(jù)倉(cāng)庫(kù)的主導(dǎo)功能就是面向分析解总，以查詢(xún)?yōu)橹鳎簧婕皵?shù)據(jù)更新操作姐仅。事實(shí)表的設(shè)計(jì)是以能夠正確記錄歷史信息為準(zhǔn)則花枫，維度表的設(shè)計(jì)是以能夠以合適的角度來(lái)聚合主題內(nèi)容為準(zhǔn)則。

維度建模三種模式

星型模型

星形模式(Star Schema)是最常用的維度建模方式掏膏。星型模式是以事實(shí)表為中心劳翰，所有的維度表直接連接在事實(shí)表上，像星星一樣馒疹。

星形模式的維度建模由一個(gè)事實(shí)表和一組維表成佳簸，且具有以下特點(diǎn)：

a. 維表只和事實(shí)表關(guān)聯(lián)，維表之間沒(méi)有關(guān)聯(lián)颖变；

b. 每個(gè)維表主鍵為單列生均，且該主鍵放置在事實(shí)表中听想，作為兩邊連接的外鍵；

c. 以事實(shí)表為核心疯特，維表圍繞核心呈星形分布哗魂；

星型模型
星型模型

雪花模式

雪花模式(Snowflake Schema)是對(duì)星形模式的擴(kuò)展。雪花模式的維度表可以擁有其他維度表的漓雅，雖然這種模型相比星型更規(guī)范一些录别，但是由于這種模型不太容易理解，維護(hù)成本比較高邻吞，而且性能方面需要關(guān)聯(lián)多層維表组题，性能也比星型模型要低。所以一般不是很常用抱冷。

雪花模型
雪花模型

星座模式

星座模式是星型模式延伸而來(lái)崔列，星型模式是基于一張事實(shí)表的，而星座模式是基于多張事實(shí)表的旺遮，而且共享維度信息赵讯。

前面介紹的兩種維度建模方法都是多維表對(duì)應(yīng)單事實(shí)表，但在很多時(shí)候維度空間內(nèi)的事實(shí)表不止一個(gè)耿眉，而一個(gè)維表也可能被多個(gè)事實(shí)表用到边翼。在業(yè)務(wù)發(fā)展后期，絕大部分維度建模都采用的是星座模式鸣剪。

星座模型
星座模型

數(shù)據(jù)倉(cāng)庫(kù)理論知識(shí)

為什么要分層

分層的主要原因是在管理數(shù)據(jù)的時(shí)候组底，能對(duì)數(shù)據(jù)有一個(gè)更加清晰的掌控，詳細(xì)來(lái)講筐骇，主要有下面幾個(gè)原因：

清晰數(shù)據(jù)結(jié)構(gòu)：

每一個(gè)數(shù)據(jù)分層都有它的作用域债鸡，這樣我們?cè)谑褂帽淼臅r(shí)候能更方便地定位和理解。

數(shù)據(jù)血緣追蹤：

簡(jiǎn)單來(lái)說(shuō)铛纬，我們最終給業(yè)務(wù)呈現(xiàn)的是一個(gè)能直接使用業(yè)務(wù)表厌均，但是它的來(lái)源有很多，如果有一張來(lái)源表出問(wèn)題了告唆，我們希望能夠快速準(zhǔn)確地定位到問(wèn)題莫秆，并清楚它的危害范圍。

減少重復(fù)開(kāi)發(fā)：

規(guī)范數(shù)據(jù)分層悔详，開(kāi)發(fā)一些通用的中間層數(shù)據(jù)，能夠減少極大的重復(fù)計(jì)算惹挟。

把復(fù)雜問(wèn)題簡(jiǎn)單化：

將一個(gè)復(fù)雜的任務(wù)分解成多個(gè)步驟來(lái)完成茄螃，每一層只處理單一的步驟，比較簡(jiǎn)單和容易理解连锯。而且便于維護(hù)數(shù)據(jù)的準(zhǔn)確性归苍，當(dāng)數(shù)據(jù)出現(xiàn)問(wèn)題之后用狱，可以不用修復(fù)所有的數(shù)據(jù)，只需要從有問(wèn)題的步驟開(kāi)始修復(fù)拼弃。

屏蔽原始數(shù)據(jù)的異常:

屏蔽業(yè)務(wù)的影響夏伊，不必改一次業(yè)務(wù)就需要重新接入數(shù)據(jù)

數(shù)倉(cāng)分層思想

數(shù)據(jù)分層每個(gè)企業(yè)根據(jù)自己的業(yè)務(wù)需求可以分成不同的層次，但是最基礎(chǔ)的分層思想吻氧，理論上數(shù)據(jù)分為三個(gè)層溺忧，數(shù)據(jù)運(yùn)營(yíng)層 數(shù)據(jù)倉(cāng)庫(kù)層和數(shù)據(jù)服務(wù)層《⑺铮基于這個(gè)基礎(chǔ)分層之上添加新的層次鲁森，來(lái)滿(mǎn)足不同的業(yè)務(wù)需求振惰。

數(shù)據(jù)運(yùn)營(yíng)層（ODS）

Operate data store歌溉，操作數(shù)據(jù)存儲(chǔ)，是最接近數(shù)據(jù)源中數(shù)據(jù)的一層骑晶，數(shù)據(jù)源中的數(shù)據(jù)痛垛，經(jīng)過(guò)抽取 洗凈 傳輸，也就說(shuō)傳說(shuō)中的ETL之后桶蛔，裝入本層匙头。本層的數(shù)據(jù)，總體上大多是按照源頭業(yè)務(wù)系統(tǒng)的分類(lèi)方式而分類(lèi)的羽圃。

例如：MySQL里面的一張表可以通過(guò)sqoop之間抽取到ODS層

ODS層數(shù)據(jù)的來(lái)源方式：

• 業(yè)務(wù)庫(kù)

  • 經(jīng)常會(huì)使用sqoop來(lái)抽取乾胶，比如我們每天定時(shí)抽取一次。在實(shí)時(shí)方面朽寞，可以考慮用canal監(jiān)聽(tīng)mysql的binlog识窿，實(shí)時(shí)接入即可。

• 埋點(diǎn)日志

  • 線(xiàn)上系統(tǒng)會(huì)打入各種日志脑融，這些日志一般以文件的形式保存喻频，我們可以選擇用flume定時(shí)抽取，也可以用用spark streaming或者Flink來(lái)實(shí)時(shí)接入肘迎，當(dāng)然甥温，kafka也會(huì)是一個(gè)關(guān)鍵的角色。

• 消息隊(duì)列

  • 來(lái)自ActiveMQ Kafka的數(shù)據(jù)等

數(shù)據(jù)倉(cāng)庫(kù)層（DW）：

Data warehouse妓布，數(shù)據(jù)倉(cāng)庫(kù)層姻蚓。在這里，從ODS層中獲得的數(shù)據(jù)按照主題建立各種數(shù)據(jù)模型匣沼。例如以研究人的旅游消費(fèi)為主題的數(shù)據(jù)集中狰挡，便可以結(jié)合航空公司的登機(jī)出行信息，以及銀聯(lián)系統(tǒng)的刷卡記錄，進(jìn)行結(jié)合分析加叁，產(chǎn)生數(shù)據(jù)集倦沧。在這里，我們需要了解四個(gè)概念：維（dimension） 事實(shí)（Fact） 指標(biāo)（Index）和粒度（ Granularity）它匕。

應(yīng)用層（ADS）：

該層主要是提供數(shù)據(jù)產(chǎn)品和數(shù)據(jù)分析使用的數(shù)據(jù)展融，一般會(huì)存放在ES MySQL等系統(tǒng)中供線(xiàn)上系統(tǒng)使用，也可能會(huì)存在Hive或者Druid中供數(shù)據(jù)分析和數(shù)據(jù)挖掘使用豫柬。

例如：我們經(jīng)常說(shuō)的報(bào)表數(shù)據(jù)告希，或者說(shuō)那種大寬表，一般就放在這里轮傍。

1 ODS 數(shù)據(jù)準(zhǔn)備層

功能

• ODS層是數(shù)據(jù)倉(cāng)庫(kù)準(zhǔn)備區(qū)暂雹，為DWD層提供基礎(chǔ)原始數(shù)據(jù)，可減少對(duì)業(yè)務(wù)系統(tǒng)的影響

建模方式及原則：

• 從業(yè)務(wù)系統(tǒng)增量抽取 保留時(shí)間由業(yè)務(wù)需求決定 可分表進(jìn)行周期存儲(chǔ) 數(shù)據(jù)不做清洗轉(zhuǎn)換與業(yè)務(wù)系統(tǒng)數(shù)據(jù)模型保持一致 按主題邏輯劃分

2 DWD 數(shù)據(jù)明細(xì)層

功能：

• 為DW層提供來(lái)源明細(xì)數(shù)據(jù)创夜，提供業(yè)務(wù)系統(tǒng)細(xì)節(jié)數(shù)據(jù)的長(zhǎng)期沉淀杭跪，為未來(lái)分析類(lèi)需求的擴(kuò)展提供歷史數(shù)據(jù)支撐

建模方式及原則：

• 數(shù)據(jù)模型與ODS層一致，不做清洗轉(zhuǎn)換處理 為支持?jǐn)?shù)據(jù)重跑可額外增加數(shù)據(jù)業(yè)務(wù)日期字段 可按年月日進(jìn)行分表 用增量ODS層數(shù)據(jù)和前一天DWD相關(guān)表進(jìn)行merge處理

3 DW(B/S) 數(shù)據(jù)匯總層

功能：

• 為DW ST層提供細(xì)粒度數(shù)據(jù)驰吓，細(xì)化成DWB和DWS涧尿；
• DWB是根據(jù)DWD明細(xì)數(shù)據(jù)進(jìn)行轉(zhuǎn)換，如維度轉(zhuǎn)代理鍵 身份證清洗 會(huì)員注冊(cè)來(lái)源清晰 字段合并 空值處理 臟數(shù)據(jù)處理 IP清晰轉(zhuǎn)換 賬號(hào)余額清洗 資金來(lái)源清洗等檬贰；
• DWS是根據(jù)DWB層數(shù)據(jù)按各個(gè)維度ID進(jìn)行高粒度匯總聚合姑廉，如按交易來(lái)源，交易類(lèi)型進(jìn)行匯合

建模方式及原則：

• 聚合 匯總增加派生事實(shí)翁涤；
• 關(guān)聯(lián)其它主題的事實(shí)表桥言，DW層可能會(huì)跨主題域；
• DWB保持低粒度匯總加工數(shù)據(jù)葵礼，DWS保持高粒度匯總數(shù)據(jù)号阿；
• 數(shù)據(jù)模型可能采用反范式設(shè)計(jì)，合并信息等鸳粉。

4 DM 數(shù)據(jù)集市層

功能：

• 可以是一些寬表扔涧，是根據(jù)DW層數(shù)據(jù)按照各種維度或多種維度組合把需要查詢(xún)的一些事實(shí)字段進(jìn)行匯總統(tǒng)計(jì)并作為單獨(dú)的列進(jìn)行存儲(chǔ)；

• 滿(mǎn)足一些特定查詢(xún) 數(shù)據(jù)挖掘應(yīng)用届谈；

• 應(yīng)用集市數(shù)據(jù)存儲(chǔ)

建模方式及原則：

• 盡量減少數(shù)據(jù)訪(fǎng)問(wèn)時(shí)計(jì)算枯夜，優(yōu)化檢索；

• 維度建模艰山，星型模型湖雹；

• 事實(shí)拉寬曙搬，度量預(yù)先計(jì)算摔吏；

• 分表存儲(chǔ)

5 ST 數(shù)據(jù)應(yīng)用層（ADS層）

功能：

• ST層面向用戶(hù)應(yīng)用和分析需求汤踏，包括前端報(bào)表 分析圖表 KPI 儀表盤(pán) OLAP 專(zhuān)題等分析，面向最終結(jié)果用戶(hù)舔腾；

• 適合作OLAP 報(bào)表模型，如ROLAP,MOLAP搂擦；

• 根據(jù)DW層經(jīng)過(guò)聚合匯總統(tǒng)計(jì)后的粗粒度事實(shí)表

  建模方式及原則：

• 保持?jǐn)?shù)據(jù)量形瘸稀；

• 維度建模瀑踢，星形模型扳还；

• 各位維度代理鍵+度量；

• 增加數(shù)據(jù)業(yè)務(wù)日期字段橱夭，支持?jǐn)?shù)據(jù)重跑氨距；

• 不分表存儲(chǔ)

數(shù)倉(cāng)中表的種類(lèi)及其概念

一般情況下表分為兩個(gè)類(lèi)型，分別維度表和事務(wù)表

維度表

維度表棘劣，一般是指對(duì)應(yīng)一些業(yè)務(wù)狀態(tài)俏让，代碼的解釋表。也可以稱(chēng)之為碼表茬暇。比如地區(qū)表首昔，訂單類(lèi)型，支付方式糙俗，商品分類(lèi)等等勒奇。
維度表可以分為兩類(lèi)：一般維度表和固定維度表
一般維度表的數(shù)據(jù)是不斷增加和變化的
固定維度表的數(shù)據(jù)是不變的

事實(shí)表

事實(shí)表分為兩類(lèi)：事務(wù)型事實(shí)表和周期型事實(shí)表
事務(wù)型事實(shí)表，一般指隨著業(yè)務(wù)發(fā)生不斷產(chǎn)生的數(shù)據(jù)巧骚。特點(diǎn)是一旦發(fā)生不會(huì)再變化赊颠。 一般比如，交易流水劈彪，操作日志竣蹦，出庫(kù)入庫(kù)記錄等等。
周期型事實(shí)表粉臊，一般指隨著業(yè)務(wù)發(fā)生不斷產(chǎn)生的數(shù)據(jù)草添。與事務(wù)型不同的是，數(shù)據(jù)會(huì)隨著業(yè)務(wù)周期性的推進(jìn)而變化扼仲。
比如訂單远寸，其中訂單狀態(tài)會(huì)周期性變化。 再比如屠凶，請(qǐng)假 貸款申請(qǐng)驰后，隨著批復(fù)狀態(tài)在周期性變化。

數(shù)倉(cāng)中表的同步策略

維度表

可能會(huì)有變化的數(shù)據(jù)可以存儲(chǔ)每日全量或者使用拉鏈表。（比如訂單類(lèi)型垫竞，審批狀態(tài)，商品分類(lèi)）

事實(shí)表

事務(wù)型事實(shí)表

• 每日增量： 因?yàn)閿?shù)據(jù)不會(huì)變化茸习，而且數(shù)據(jù)量巨大夜涕，所以每天只同步新增數(shù)據(jù)即可犯犁；每日分區(qū)。

周期型事實(shí)表

• 每日全量：首先這類(lèi)表從數(shù)據(jù)量的角度女器，存每日全量的話(huà)酸役，數(shù)據(jù)量太大，冗余也太大驾胆。

• 每日增量：如果用每日增量的話(huà)無(wú)法反應(yīng)數(shù)據(jù)變化涣澡；每日新增及變化量可以用，包括了當(dāng)日的新增和修改丧诺。一般來(lái)說(shuō)這個(gè)表入桂，足夠計(jì)算大部分當(dāng)日數(shù)據(jù)的。但是這種依然無(wú)法解決能夠得到某一個(gè)歷史時(shí)間點(diǎn)（時(shí)間切片）的切片數(shù)據(jù)驳阎。

• 拉鏈表：利用每日新增和變化表抗愁，制作一張拉鏈表，以方便的取到某個(gè)時(shí)間切片的快照數(shù)據(jù)搞隐。
  所以我們需要得到每日新增及變化量驹愚。

數(shù)據(jù)倉(cāng)庫(kù)開(kāi)發(fā)

業(yè)務(wù)系統(tǒng)表結(jié)構(gòu)介紹

訂單表itcast_orders

訂單明細(xì)表 itcast_order_goods

商品信息表 itcast_goods

店鋪表 itcast_shops

商品分類(lèi)表 itcast_goods_cats

組織結(jié)構(gòu)表 itcast_org

訂單退貨表 itcast_order_refunds

用戶(hù)表 itcast_users

用戶(hù)收貨地址表 itcast_user_address

支付方式表 itcast_payments

項(xiàng)目環(huán)境初始化

導(dǎo)入mysql模擬數(shù)據(jù)

2.資料\mysql建表語(yǔ)句\10tables.sql文件上傳到linux绷柒，登錄mysql使用source命令執(zhí)行該sql文件創(chuàng)建數(shù)據(jù)庫(kù)和表

mysql -uroot -p
source /root/sql/10tables.sql;

Hive分層說(shuō)明

• 分庫(kù)存放

  • ods層
  • dw層
  • ads層

• 命名規(guī)則

  • ods層表與原始數(shù)據(jù)庫(kù)表名稱(chēng)相同

  • dw層表

    • fact_前綴表示事實(shí)表

    • dim_前綴表示維度表

創(chuàng)建分層數(shù)據(jù)庫(kù)：

create database itcast_ods;
create database itcast_dw;
create database itcast_ads;

創(chuàng)建ods層數(shù)據(jù)表

• hive 分為外部表與內(nèi)部表志于，為便于管理，該部分均使用內(nèi)部表
• 執(zhí)行資料\hiveods層建表語(yǔ)句\ods_create_table.sql

數(shù)據(jù)采集

ods層全量數(shù)據(jù)抽取

步驟：

1 拖拽組件構(gòu)建Kettle作業(yè)結(jié)構(gòu)圖

全量采集配置圖

2 轉(zhuǎn)換結(jié)構(gòu)圖--》配置命名參數(shù)

配置轉(zhuǎn)換命名參數(shù)

3 配置Hive SQL腳本

msck repair table itcast_ods.itcast_orders;
msck repair table itcast_ods.itcast_goods;
msck repair table itcast_ods.itcast_order_goods;
msck repair table itcast_ods.itcast_shops;
msck repair table itcast_ods.itcast_goods_cats;
msck repair table itcast_ods.itcast_org;
msck repair table itcast_ods.itcast_order_refunds;
msck repair table itcast_ods.itcast_users;
msck repair table itcast_ods.itcast_user_address;
msck repair table itcast_ods.itcast_payments;

修復(fù)分區(qū)

4 配置表輸入

SELECT
*
FROM itcast_orders
WHERE DATE_FORMAT(createtime, '%Y%m%d') <= '${dt}';

組件圖

5 配置字段選擇指定日期格式废睦，配置parquet格式并設(shè)置snappy壓縮輸出

字段選擇
文件位置
parquet output配置

測(cè)試

測(cè)試數(shù)據(jù)是否都正確被加載伺绽。

select * from itcast_ods.itcast_orders limit 2;
select * from itcast_ods.itcast_goods limit 2;
select * from itcast_ods.itcast_order_goods limit 2;
select * from itcast_ods.itcast_shops limit 2;
select * from itcast_ods.itcast_goods_cats limit 2;
select * from itcast_ods.itcast_org limit 2;
select * from itcast_ods.itcast_order_refunds limit 2;
select * from itcast_ods.itcast_users limit 2;
select * from itcast_ods.itcast_user_address limit 2;
select * from itcast_ods.itcast_payments limit 2;

注意：

1：其中itcast_orders,itcast_order_goods,itcast_order_refunds表是根據(jù)時(shí)間抽取，其余表進(jìn)行全量抽仁扰取Ｄ斡Α！

2：注意使用字段選擇組件時(shí)要注意修改日期格式為UTF8９号杖挣！，parquet中fields中date類(lèi)型改為UTF8類(lèi)型８斩浮惩妇！

緩慢變化維

什么是緩慢變化維（SCD)

1 緩慢變化維簡(jiǎn)介

• 緩慢變化維株汉，簡(jiǎn)稱(chēng)SCD（Slowly Changing Dimensions）
• 一些維度表的數(shù)據(jù)不是靜態(tài)的，而是會(huì)隨著時(shí)間而緩慢地變化（這里的緩慢是相對(duì)事實(shí)表而言歌殃，事實(shí)表數(shù)據(jù)變化的速度比維度表快）
• 這種隨著時(shí)間發(fā)生變化的維度稱(chēng)之為緩慢變化維
• 把處理維度表數(shù)據(jù)歷史變化的問(wèn)題乔妈，稱(chēng)為緩慢變化維問(wèn)題，簡(jiǎn)稱(chēng)SCD問(wèn)題

2 舉例說(shuō)明

例如：用根據(jù)用戶(hù)維度氓皱，統(tǒng)計(jì)不同出生年份的消費(fèi)金額占比褒翰。（80后 90后 00后）。

而期間匀泊，用戶(hù)可能去修改用戶(hù)數(shù)據(jù)，例如：將出生日期改成了 1992年朵你。此時(shí)各聘，用戶(hù)維度表就發(fā)生了變化。當(dāng)然這個(gè)變化相對(duì)事實(shí)表的變換要慢抡医。但這個(gè)用戶(hù)維度表的變化躲因，就是緩慢變化維。

這個(gè)用戶(hù)的數(shù)據(jù)不是一直不變忌傻，而是有可能發(fā)生變化大脉。例如：用戶(hù)修改了出生日期 或者用戶(hù)修改了住址。

SCD問(wèn)題的幾種解決方案

以下為解決緩慢變化維問(wèn)題的幾種辦法：

• 保留原始值：指標(biāo)計(jì)算不符合最新維度數(shù)據(jù)

• 改寫(xiě)屬性值：無(wú)法獲取到歷史狀態(tài)

• 增加維度新行：拉鏈表

• 增加維度新列：成本太高

• 添加歷史表：增加維護(hù)難度

  對(duì)于歷史數(shù)據(jù)會(huì)變化的以及還有新增數(shù)據(jù)的表同步到數(shù)倉(cāng)中我們有兩個(gè)要求：

  1水孩、 數(shù)據(jù)的歷史狀態(tài)我們要保存镰矿，

  2、對(duì)于新增數(shù)據(jù)也保存

SCD解決方案 - 保留原始值

某一個(gè)屬性值絕不會(huì)變化俘种。事實(shí)表始終按照該原始值進(jìn)行分組秤标。例如：

• 出生日期的數(shù)據(jù)，始終按照用戶(hù)第一次填寫(xiě)的數(shù)據(jù)為準(zhǔn)

SCD解決方案 - 改寫(xiě)屬性值

• 對(duì)其相應(yīng)需要重寫(xiě)維度行中的舊值宙刘，以當(dāng)前值替換苍姜。因此其始終反映最近的情況
• 當(dāng)一個(gè)維度值的數(shù)據(jù)源發(fā)生變化，并且不需要在維度表中保留變化歷史時(shí)悬包，通常用新數(shù)據(jù)來(lái)覆蓋舊數(shù)據(jù)衙猪。這樣的處理使屬性所反映的中是最新的賦值。

用戶(hù)維度表

修改前：

修改后：

• 這種方法有個(gè)前提布近，用戶(hù)不關(guān)心這個(gè)數(shù)據(jù)的變化
• 這樣處理垫释，易于實(shí)現(xiàn)，但是沒(méi)有保留歷史數(shù)據(jù)吊输，無(wú)法分析歷史變化信息

SCD解決方案 - 增加維度新行

數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)的目標(biāo)之一是正確地表示歷史饶号。典型代表就是拉鏈表。

保留歷史的數(shù)據(jù)季蚂，并插入新的數(shù)據(jù)茫船。

用戶(hù)維度表

修改前：

修改后：

SCD解決方案 - 增加維度新列

用不同的字段來(lái)保存不同的值琅束，就是在表中增加一個(gè)字段，這個(gè)字段用來(lái)保存變化后的當(dāng)前值算谈，而原來(lái)的值則被稱(chēng)為變化前的值涩禀。總的來(lái)說(shuō)然眼，這種方法通過(guò)添加字段來(lái)保存變化后的痕跡艾船。

用戶(hù)維度表

修改前：

修改后

SCD解決方案 - 使用歷史表

另外建一個(gè)表來(lái)保存歷史記錄，這種方式就是將歷史數(shù)據(jù)與當(dāng)前數(shù)據(jù)完全分開(kāi)來(lái)高每，在維度中只保存當(dāng)前最新的數(shù)據(jù)屿岂。

用戶(hù)維度表

用戶(hù)維度歷史表

這種方式的優(yōu)點(diǎn)是可以同時(shí)分析當(dāng)前及前一次變化的屬性值，缺點(diǎn)是只保留了最后一次變化信息鲸匿。

數(shù)據(jù)采集-拉鏈表技術(shù)介紹

數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)模型設(shè)計(jì)過(guò)程中爷怀，經(jīng)常會(huì)遇到這樣的需求：

1. 表中的部分字段會(huì)被update，例如：
   • 用戶(hù)的地址带欢，產(chǎn)品的描述信息运授，品牌信息等等;
2. 需要查看某一個(gè)時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息，例如：
   • 查看某一個(gè)產(chǎn)品在歷史某一時(shí)間點(diǎn)的狀態(tài)
   • 查看某一個(gè)用戶(hù)在過(guò)去某一段時(shí)間內(nèi)乔煞，更新過(guò)幾次等等
3. 變化的比例和頻率不是很大吁朦，例如：
   • 總共有1000萬(wàn)的會(huì)員，每天新增和發(fā)生變化的有10萬(wàn)左右

商品歷史快照案例

需求：

有一個(gè)商品表：

2019年12月20日的數(shù)據(jù)如下所示：

商品的狀態(tài)空骚，會(huì)隨著時(shí)間推移而變化锦溪，我們需要將商品的所有變化的歷史信息都保存下來(lái)。如何實(shí)現(xiàn)呢府怯？

方案一：快照每一天的數(shù)據(jù)到數(shù)倉(cāng)

該方案為：

• 每一天都保存一份全量刻诊，將所有數(shù)據(jù)同步到數(shù)倉(cāng)中
• 很多記錄都是重復(fù)保存，沒(méi)有任何變化

12月20日（4條數(shù)據(jù)）

12月21日（10條數(shù)據(jù)）

12月22日（18條數(shù)據(jù)）

方案一：MySQL到Hive數(shù)倉(cāng)代碼實(shí)現(xiàn)

MySQL&Hive初始化

1 在MySQL demo庫(kù)中 創(chuàng)建表

-- 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE demo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 創(chuàng)建商品表
create table if not exists `demo`.`t_product`(
    goods_id varchar(50),       -- 商品編號(hào)
    goods_status varchar(50),   -- 商品狀態(tài)
    createtime varchar(50),     -- 商品創(chuàng)建時(shí)間
    modifytime varchar(50)      -- 商品修改時(shí)間
);

2 在Hive中 demo庫(kù)創(chuàng)建表

-- 創(chuàng)建表
create database if not exists `demo`;

-- 創(chuàng)建ods層表
create table if not exists `demo`.`ods_product`(
    goods_id string,        -- 商品編號(hào)
    goods_status string,    -- 商品狀態(tài)
    createtime string,      -- 商品創(chuàng)建時(shí)間
    modifytime string       -- 商品修改時(shí)間
)
partitioned by (dt string)
row format delimited fields terminated by ',' stored as TEXTFILE;

-- 創(chuàng)建dw層表
create table if not exists `demo`.`dw_product`(
    goods_id string,        -- 商品編號(hào)
    goods_status string,    -- 商品狀態(tài)
    createtime string,      -- 商品創(chuàng)建時(shí)間
    modifytime string       -- 商品修改時(shí)間
)
partitioned by (dt string)
row format delimited fields terminated by ',' stored as TEXTFILE;

增量導(dǎo)入12月20日數(shù)據(jù)

1 MySQL數(shù)據(jù)庫(kù)導(dǎo)入12月20日數(shù)據(jù)（4條數(shù)據(jù)）

insert into `demo`.`t_product`(goods_id, goods_status, createtime, modifytime) values
('001', '待審核', '2019-12-18', '2019-12-20'),
('002', '待售', '2019-12-19', '2019-12-20'),
('003', '在售', '2019-12-20', '2019-12-20'),
('004', '已刪除', '2019-12-15', '2019-12-20');

mysql圖示

2 使用Kettle將MySQL數(shù)據(jù)導(dǎo)出牺丙，并導(dǎo)入到分區(qū)HDFS位置

Kettle轉(zhuǎn)換流程圖

創(chuàng)建Hive分區(qū)

-- 創(chuàng)建分區(qū)
alter table `demo`.`ods_product` add if not exists partition (dt='2019-12-20');

增加分區(qū)
表輸入
Hadoop File output

3 Hive中查詢(xún)數(shù)據(jù)

select * from `demo`.`ods_product`

4 數(shù)據(jù)導(dǎo)入維度表

insert overwrite table `demo`.`dw_product` partition(dt='2019-12-20')
select  
    goods_id,
    goods_status,
    createtime,
    modifytime
from `demo`.`ods_product` where dt='2019-12-20';

增量導(dǎo)入12月21日數(shù)據(jù)

1 MySQL數(shù)據(jù)庫(kù)導(dǎo)入12月21日數(shù)據(jù)（6條數(shù)據(jù)）

UPDATE `demo`.`t_product` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `demo`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待審核', '2019-12-21', '2019-12-21'),
('006', '待審核', '2019-12-21', '2019-12-21');

2 運(yùn)行Kettle轉(zhuǎn)換则涯，導(dǎo)入2019年12月21日數(shù)據(jù)

執(zhí)行kettle轉(zhuǎn)換

3 Hive查詢(xún)數(shù)據(jù)

select * from `demo`.`ods_product` where dt='2019-12-21';

mysql數(shù)據(jù)展示

4 數(shù)據(jù)導(dǎo)入dw層表

insert overwrite table `demo`.`dw_product` partition(dt='2019-12-21')
select  
    goods_id,
    goods_status,
    createtime,
    modifytime
from `demo`.`ods_product` where dt='2019-12-21';

最終數(shù)據(jù)展示

增量導(dǎo)入12月22日數(shù)據(jù)

1 MySQL數(shù)據(jù)庫(kù)導(dǎo)入12月22日數(shù)據(jù)（6條數(shù)據(jù)）

UPDATE `demo`.`t_product` SET goods_status = '已刪除', modifytime = '2019-12-22' WHERE goods_id = '003';
UPDATE `demo`.`t_product` SET goods_status = '已刪除', modifytime = '2019-12-22' WHERE goods_id = '006';
INSERT INTO `demo`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES
('007', '待審核', '2019-12-22', '2019-12-22'),
('008', '待審核', '2019-12-22', '2019-12-22');

2 運(yùn)行Kettle轉(zhuǎn)換，導(dǎo)入2019年12月22日數(shù)據(jù)

導(dǎo)入2019年12月22日數(shù)據(jù)

3 Hive查詢(xún)數(shù)據(jù)

select * from ods_product where dt='2019-12-22';

hive數(shù)據(jù)

4 數(shù)據(jù)導(dǎo)入dw層表

insert overwrite table `demo`.`dw_product` partition(dt='2019-12-22')
select  
    goods_id,
    goods_status,
    createtime,
    modifytime
from `demo`.`ods_product` where dt='2019-12-22';

2019-12-22數(shù)據(jù)

從上述案例冲簿，可以看到：

• 表每天保留一份全量粟判，每次全量中會(huì)保存很多不變的信息，如果數(shù)據(jù)量很大的話(huà)峦剔，對(duì)存儲(chǔ)是極大的浪費(fèi)

可以將表設(shè)計(jì)為拉鏈表档礁，既能滿(mǎn)足反應(yīng)數(shù)據(jù)的歷史狀態(tài)，又可以最大限度地節(jié)省存儲(chǔ)空間

方案二：使用拉鏈表保存歷史快照

拉鏈表

• 拉鏈表不存儲(chǔ)冗余的數(shù)據(jù)吝沫，只有某行的數(shù)據(jù)發(fā)生變化呻澜，才需要保存下來(lái)递礼，相比每次全量同步會(huì)節(jié)省存儲(chǔ)空間
• 能夠查詢(xún)到歷史快照
• 額外的增加了兩列（dw_start_date dw_end_date），為數(shù)據(jù)行的生命周期

12月20日商品拉鏈表的數(shù)據(jù)：

• 12月20日的數(shù)據(jù)是全新的數(shù)據(jù)導(dǎo)入到dw表
• dw_start_date表示某一條數(shù)據(jù)的生命周期起始時(shí)間羹幸，即數(shù)據(jù)從該時(shí)間開(kāi)始有效（即生效日期）
• dw_end_date表示某一條數(shù)據(jù)的生命周期結(jié)束時(shí)間脊髓，即數(shù)據(jù)到這一天失效（即失效日期）
• dw_end_date為9999-12-31，表示當(dāng)前這條數(shù)據(jù)是最新的數(shù)據(jù)栅受，數(shù)據(jù)到9999-12-31才過(guò)期

12月21日商品拉鏈表的數(shù)據(jù)

• 拉鏈表中沒(méi)有存儲(chǔ)冗余的數(shù)據(jù)将硝，只要數(shù)據(jù)沒(méi)有變化，無(wú)需同步
• 001編號(hào)的商品數(shù)據(jù)的狀態(tài)發(fā)生了變化（從待審核 → 待售）屏镊，需要將原有的dw_end_date變?yōu)?019-12-21依疼，表示待審核狀態(tài)，在2019/12/20（包含） - 2019/12/21有效
• 001編號(hào)新的狀態(tài)重新保存了一條記錄而芥，dw_start_date為2019/12/21涛贯，dw_end_date為9999/12/31

12月22日商品拉鏈表的數(shù)據(jù)

查詢(xún)拉鏈表

1 獲取2019-12-20日的歷史快照數(shù)據(jù)

select * from demo.dw_product_2 where dw_start_date <= '2019-12-20' and dw_end_date >= '2019-12-20' order by goods_id;

2 獲取最新的商品快照數(shù)據(jù)

select * from demo.dw_product_2 where dw_end_date = '9999-12-31' order by goods_id;

方案二：拉鏈表存儲(chǔ)歷史快照代碼實(shí)現(xiàn)

操作步驟：

1. 在原有dw層表上，添加額外的兩列

   • 生效日期（dw_start_date）
   • 失效日期（dw_end_date）

2. 只同步當(dāng)天修改的數(shù)據(jù)到ods層

3. 拉鏈表算法實(shí)現(xiàn)

   • 編寫(xiě)SQL處理當(dāng)天最新的數(shù)據(jù)

   • 編寫(xiě)SQL處理dw層歷史數(shù)據(jù)蔚出，重新計(jì)算之前的dw_end_date

   • 拉鏈表的數(shù)據(jù)為：當(dāng)天最新的數(shù)據(jù) UNION ALL 歷史數(shù)據(jù)

4. 拉鏈表的數(shù)據(jù)為：當(dāng)天最新的數(shù)據(jù) UNION ALL 歷史數(shù)據(jù)

代碼實(shí)現(xiàn)：

1 MySQL&Hive表初始化

MySQL創(chuàng)建商品表2

-- 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE demo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 創(chuàng)建商品表
create table if not exists `demo`.`t_product_2`(
    goods_id varchar(50),       -- 商品編號(hào)
    goods_status varchar(50),   -- 商品狀態(tài)
    createtime varchar(50),     -- 商品創(chuàng)建時(shí)間
    modifytime varchar(50)      -- 商品修改時(shí)間
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

Hive ODS層建表

-- 創(chuàng)建表
create database if not exists `demo`;

-- 創(chuàng)建ods層表
create table if not exists `demo`.`ods_product_2`(
    goods_id string,        -- 商品編號(hào)
    goods_status string,    -- 商品狀態(tài)
    createtime string,      -- 商品創(chuàng)建時(shí)間
    modifytime string       -- 商品修改時(shí)間
)
partitioned by (dt string)   --按照天分區(qū)
row format delimited fields terminated by ',' stored as TEXTFILE;

Hive dw層創(chuàng)建拉鏈表

-- 創(chuàng)建拉鏈表
create table if not exists `demo`.`dw_product_2`(
    goods_id string,        -- 商品編號(hào)
    goods_status string,    -- 商品狀態(tài)
    createtime string,      -- 商品創(chuàng)建時(shí)間
    modifytime string,       -- 商品修改時(shí)間
    dw_start_date string,   -- 生效日期
    dw_end_date string      -- 失效日期
)
row format delimited fields terminated by ',' stored as TEXTFILE;

全量導(dǎo)入2019年12月20日數(shù)據(jù)

1 MySQL數(shù)據(jù)庫(kù)導(dǎo)入12月20日數(shù)據(jù)（4條數(shù)據(jù)）

insert into `demo`.`t_product_2`(goods_id, goods_status, createtime, modifytime) values
('001', '待審核', '2019-12-18', '2019-12-20'),
('002', '待售', '2019-12-19', '2019-12-20'),
('003', '在售', '2019-12-20', '2019-12-20'),
('004', '已刪除', '2019-12-15', '2019-12-20');

mysql中數(shù)據(jù)

2 使用Kettle進(jìn)行全量同步MySQL數(shù)據(jù)到Hive ods層表

Kettle組件圖
設(shè)置命名參數(shù)

創(chuàng)建Hive分區(qū)

-- 創(chuàng)建分區(qū)
alter table `demo`.`ods_product_2` add if not exists partition (dt='${dt}');

hive增加分區(qū)
image-20191223204006267.png

表輸入

SELECT
*
FROM t_product_2
where modifytime <= '${dt}'

表輸入組件
image-20191223204738790.png
Hadoop File Ouput

3 編寫(xiě)SQL從ods導(dǎo)入dw當(dāng)天最新的數(shù)據(jù)

-- 從ods層導(dǎo)入dw當(dāng)天最新數(shù)據(jù)
insert overwrite table `demo`.`dw_product_2`
select
    goods_id,                -- 商品編號(hào)
    goods_status,            -- 商品狀態(tài)
    createtime,              -- 商品創(chuàng)建時(shí)間
    modifytime,              -- 商品修改時(shí)間
    modifytime as dw_start_date,    -- 生效日期
    '9999-12-31' as dw_end_date     -- 失效日期
from
    `demo`.`ods_product_2`
where
    dt = '2019-12-20';

當(dāng)天最新的數(shù)據(jù)

增量導(dǎo)入2019年12月21日數(shù)據(jù)

1 MySQL數(shù)據(jù)庫(kù)導(dǎo)入12月21日數(shù)據(jù)（6條數(shù)據(jù)）

UPDATE `demo`.`t_product_2` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `demo`.`t_product_2`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待審核', '2019-12-21', '2019-12-21'),
('006', '待審核', '2019-12-21', '2019-12-21');

2 使用Kettle開(kāi)發(fā)增量同步MySQL數(shù)據(jù)到Hive ods層表

Hive創(chuàng)建分區(qū)

-- 創(chuàng)建分區(qū)
alter table `demo`.`ods_product_2` add if not exists partition (dt='${dt}');

表輸入讀取MySQL數(shù)據(jù)

SELECT
*
FROM t_product_2
where modifytime = '${dt}'

ods中數(shù)據(jù)

3 編寫(xiě)SQL處理dw層歷史數(shù)據(jù)，重新計(jì)算之前的dw_end_date

-- 重新計(jì)算dw層拉鏈表中的失效時(shí)間
select
    t1.goods_id,                -- 商品編號(hào)
    t1.goods_status,            -- 商品狀態(tài)
    t1.createtime,              -- 商品創(chuàng)建時(shí)間
    t1.modifytime,              -- 商品修改時(shí)間
    t1.dw_start_date,           -- 生效日期（生效日期無(wú)需重新計(jì)算）
    case when (t2.goods_id is not null and t1.dw_end_date > '2019-12-21')
    then '2019-12-21'
    else t1.dw_end_date
    end as dw_end_date       -- 更新生效日期（需要重新計(jì)算）
from
    `demo`.`dw_product_2` t1
    left join
    (select * from `demo`.`ods_product_2` where dt='2019-12-21') t2
    on t1.goods_id = t2.goods_id

6 合并當(dāng)天最新的數(shù)據(jù)和歷史數(shù)據(jù)到

insert overwrite table `demo`.`dw_product_2`
select
    t1.goods_id,                -- 商品編號(hào)
    t1.goods_status,            -- 商品狀態(tài)
    t1.createtime,              -- 商品創(chuàng)建時(shí)間
    t1.modifytime,              -- 商品修改時(shí)間
    t1.dw_start_date,           -- 生效日期（生效日期無(wú)需重新計(jì)算）
    case when (t2.goods_id is not null and t1.dw_end_date > '2019-12-21')
    then '2019-12-21'
    else t1.dw_end_date
    end as dw_end_date       -- 更新生效日期（需要重新計(jì)算）
from
    `demo`.`dw_product_2` t1
    left join
    (select * from `demo`.`ods_product_2` where dt='2019-12-21') t2
    on t1.goods_id = t2.goods_id
union all
select 
    goods_id,                -- 商品編號(hào)
    goods_status,            -- 商品狀態(tài)
    createtime,              -- 商品創(chuàng)建時(shí)間
    modifytime,              -- 商品修改時(shí)間
    modifytime as dw_start_date,  -- 生效日期
    '9999-12-31' as dw_end_date   -- 失效日期
from
    `demo`.`ods_product_2` where dt='2019-12-21'
order by dw_start_date, goods_id;

最終數(shù)據(jù)

數(shù)據(jù)采集- 商品維度數(shù)據(jù)加載

使用拉鏈表解決商品表SCD問(wèn)題

dw層建表

-- dw層建表
DROP TABLE IF EXISTS `itcast_dw`.`dim_goods`;
CREATE TABLE `itcast_dw`.`dim_goods`(
    goodsId bigint,
    goodsSn string,
    productNo string,
    goodsName string,
    goodsImg string,
    shopId bigint,
    goodsType bigint,
    marketPrice double,
    shopPrice double,
    warnStock bigint,
    goodsStock bigint,
    goodsUnit string,
    goodsTips string,
    isSale bigint,
    isBest bigint,
    isHot bigint,
    isNew bigint,
    isRecom bigint,
    goodsCatIdPath string,
    goodsCatId bigint,
    shopCatId1 bigint,
    shopCatId2 bigint,
    brandId bigint,
    goodsDesc string,
    goodsStatus bigint,
    saleNum bigint,
    saleTime string,
    visitNum bigint,
    appraiseNum bigint,
    isSpec bigint,
    gallery string,
    goodsSeoKeywords string,
    illegalRemarks string,
    dataFlag bigint,
    createTime string,
    isFreeShipping bigint,
    goodsSerachKeywords string,
    modifyTime string,
    dw_start_date string,
    dw_end_date string
)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

具體步驟

拉鏈表設(shè)計(jì)一共分為以下幾個(gè)步驟：

1 第一次全量導(dǎo)入

• 所有的ODS數(shù)據(jù)全部導(dǎo)入到拉鏈歷史記錄表中

2 增量導(dǎo)入（某天虫腋，舉例：2019-09-10）

• 增量導(dǎo)入某天的數(shù)據(jù)到ODS分區(qū)
• 合并歷史數(shù)據(jù)
  • 通過(guò)連接查詢(xún)方式更新

全量導(dǎo)入

• 將所有 2019年09月09日以前創(chuàng)建的商品以及修改的數(shù)據(jù)全部導(dǎo)入到拉鏈歷史記錄表中

操作步驟：

1 使用Kettle將20190909以前的數(shù)據(jù)抽取到ods

SELECT *
FROM itcast_ods.itcast_goods
WHERE  DATE_FORMAT(modifyTime, '%Y%m%d') <= '20190909';

2 使用spark sql將全量數(shù)據(jù)導(dǎo)入到dw層維度表

set spark.sql.shuffle.partitions=1; --shuffle時(shí)的分區(qū)數(shù)骄酗，默認(rèn)是200個(gè)
-- 使用spark sql將全量數(shù)據(jù)導(dǎo)入到dw層維度表
insert overwrite table `itcast_dw`.`dim_goods`
select
    goodsId,
    goodsSn,
    productNo,
    goodsName,
    goodsImg,
    shopId,
    goodsType,
    marketPrice,
    shopPrice,
    warnStock,
    goodsStock,
    goodsUnit,
    goodsTips,
    isSale,
    isBest,
    isHot,
    isNew,
    isRecom,
    goodsCatIdPath,
    goodsCatId,
    shopCatId1,
    shopCatId2,
    brandId,
    goodsDesc,
    goodsStatus,
    saleNum,
    saleTime,
    visitNum,
    appraiseNum,
    isSpec,
    gallery,
    goodsSeoKeywords,
    illegalRemarks,
    dataFlag,
    createTime,
    isFreeShipping,
    goodsSerachKeywords,
    modifyTime,
     case when modifyTime is not null
        then from_unixtime(unix_timestamp(modifyTime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd')
        else from_unixtime(unix_timestamp(createTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd') 
        end as dw_start_date,
    '9999-12-31' as dw_end_date
from
    `itcast_ods`.`itcast_goods` t
where dt='20190909';

增量導(dǎo)入

• 將2019年09月10日創(chuàng)建的 修改的數(shù)據(jù)全部導(dǎo)入到歷史拉鏈表中

操作步驟：

1 使用Kettle將20190910創(chuàng)建的 或者修改的數(shù)據(jù)抽取到ods

SELECT *
FROM itcast_goods
WHERE  DATE_FORMAT(modifyTime, '%Y%m%d') = '${dt}';

2 編寫(xiě)spark-sql更新歷史數(shù)據(jù)

-- 更新歷史數(shù)據(jù)
select
    dw.goodsId,
    dw.goodsSn,
    dw.productNo,
    dw.goodsName,
    dw.goodsImg,
    dw.shopId,
    dw.goodsType,
    dw.marketPrice,
    dw.shopPrice,
    dw.warnStock,
    dw.goodsStock,
    dw.goodsUnit,
    dw.goodsTips,
    dw.isSale,
    dw.isBest,
    dw.isHot,
    dw.isNew,
    dw.isRecom,
    dw.goodsCatIdPath,
    dw.goodsCatId,
    dw.shopCatId1,
    dw.shopCatId2,
    dw.brandId,
    dw.goodsDesc,
    dw.goodsStatus,
    dw.saleNum,
    dw.saleTime,
    dw.visitNum,
    dw.appraiseNum,
    dw.isSpec,
    dw.gallery,
    dw.goodsSeoKeywords,
    dw.illegalRemarks,
    dw.dataFlag,
    dw.createTime,
    dw.isFreeShipping,
    dw.goodsSerachKeywords,
    dw.modifyTime,
    dw.dw_start_date,
    case when dw.dw_end_date = '9999-12-31' and ods.goodsId is not null
        then '2019-09-09'
        else dw.dw_end_date
        end as dw_end_date
from
    `itcast_dw`.`dim_goods` dw
    left join 
    (select * from `itcast_ods`.`itcast_goods` where dt='20190910') ods
    on dw.goodsId = ods.goodsId ;

3 編寫(xiě)spark-sql獲取當(dāng)日數(shù)據(jù)

-- 今日數(shù)據(jù)
select
    goodsId,
    goodsSn,
    productNo,
    goodsName,
    goodsImg,
    shopId,
    goodsType,
    marketPrice,
    shopPrice,
    warnStock,
    goodsStock,
    goodsUnit,
    goodsTips,
    isSale,
    isBest,
    isHot,
    isNew,
    isRecom,
    goodsCatIdPath,
    goodsCatId,
    shopCatId1,
    shopCatId2,
    brandId,
    goodsDesc,
    goodsStatus,
    saleNum,
    saleTime,
    visitNum,
    appraiseNum,
    isSpec,
    gallery,
    goodsSeoKeywords,
    illegalRemarks,
    dataFlag,
    createTime,
    isFreeShipping,
    goodsSerachKeywords,
    modifyTime,
    case when modifyTime is not null
        then from_unixtime(unix_timestamp(modifyTime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd')
        else from_unixtime(unix_timestamp(createTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd') 
        end as dw_start_date,
    '9999-12-31' as dw_end_date
from
    `itcast_ods`.`itcast_goods`
where dt = '20190910';

4 將歷史數(shù)據(jù) 當(dāng)日數(shù)據(jù)合并加載到臨時(shí)表

-- 將歷史數(shù)據(jù) 當(dāng)日數(shù)據(jù)合并加載到臨時(shí)表
drop table if exists `itcast_dw`.`tmp_dim_goods_history`;
create table `itcast_dw`.`tmp_dim_goods_history`
as
select
    dw.goodsId,
    dw.goodsSn,
    dw.productNo,
    dw.goodsName,
    dw.goodsImg,
    dw.shopId,
    dw.goodsType,
    dw.marketPrice,
    dw.shopPrice,
    dw.warnStock,
    dw.goodsStock,
    dw.goodsUnit,
    dw.goodsTips,
    dw.isSale,
    dw.isBest,
    dw.isHot,
    dw.isNew,
    dw.isRecom,
    dw.goodsCatIdPath,
    dw.goodsCatId,
    dw.shopCatId1,
    dw.shopCatId2,
    dw.brandId,
    dw.goodsDesc,
    dw.goodsStatus,
    dw.saleNum,
    dw.saleTime,
    dw.visitNum,
    dw.appraiseNum,
    dw.isSpec,
    dw.gallery,
    dw.goodsSeoKeywords,
    dw.illegalRemarks,
    dw.dataFlag,
    dw.createTime,
    dw.isFreeShipping,
    dw.goodsSerachKeywords,
    dw.modifyTime,
    dw.dw_start_date,
    case when dw.dw_end_date >= '9999-12-31' and ods.goodsId is not null
        then '2019-09-09'
        else dw.dw_end_date
        end as dw_end_date
from
    `itcast_dw`.`dim_goods` dw
    left join 
    (select * from `itcast_ods`.`itcast_goods` where dt='20190910') ods
    on dw.goodsId = ods.goodsId
union all
select
    goodsId,
    goodsSn,
    productNo,
    goodsName,
    goodsImg,
    shopId,
    goodsType,
    marketPrice,
    shopPrice,
    warnStock,
    goodsStock,
    goodsUnit,
    goodsTips,
    isSale,
    isBest,
    isHot,
    isNew,
    isRecom,
    goodsCatIdPath,
    goodsCatId,
    shopCatId1,
    shopCatId2,
    brandId,
    goodsDesc,
    goodsStatus,
    saleNum,
    saleTime,
    visitNum,
    appraiseNum,
    isSpec,
    gallery,
    goodsSeoKeywords,
    illegalRemarks,
    dataFlag,
    createTime,
    isFreeShipping,
    goodsSerachKeywords,
    modifyTime,
    case when modifyTime is not null
        then from_unixtime(unix_timestamp(modifyTime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd')
        else from_unixtime(unix_timestamp(createTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd') 
        end as dw_start_date,
    '9999-12-31' as dw_end_date
from
    `itcast_ods`.`itcast_goods`
where dt = '20190910';

5 將歷史數(shù)據(jù) 當(dāng)日數(shù)據(jù)導(dǎo)入到歷史拉鏈表

-- 將歷史數(shù)據(jù) 當(dāng)日數(shù)據(jù)導(dǎo)入到歷史拉鏈表
insert overwrite table `itcast_dw`.`dim_goods`
select * from `itcast_dw`.`tmp_dim_goods_history`;

-- 獲取2019-09-09日的商品數(shù)據(jù)
select * from `itcast_dw`.`dim_goods` where dw_start_date <= '2019-09-09' and dw_end_date >= '2019-09-09' limit 10;
--查看對(duì)應(yīng)商品id的歷史拉鏈數(shù)據(jù)
select * from `itcast_dw`.`dim_goods` where goodsId = 100134;

數(shù)據(jù)采集-周期性事實(shí)表

因?yàn)橛唵伪砗陀唵瓮丝畋矶加袪顟B(tài)變化的特點(diǎn)，所以他們作為周期性事實(shí)表在進(jìn)行同步操作也就是采集數(shù)據(jù)到數(shù)倉(cāng)中時(shí)需要我們能記錄下訂單的狀態(tài)變化悦冀。因次依然使用拉鏈表來(lái)解決這類(lèi)周期性事實(shí)表的同步需求趋翻。訂單明細(xì)表并不會(huì)隨著時(shí)間而變化，所以不需要使用拉鏈表進(jìn)行同步盒蟆。

訂單表踏烙、訂單退款表 拉鏈表具體實(shí)現(xiàn)步驟：

創(chuàng)建dw層訂單拉鏈表、訂單退款拉鏈表

-- 創(chuàng)建dw層訂單事實(shí)表--帶有分區(qū)字段
DROP TABLE IF EXISTS itcast_dw.fact_orders;
create  table itcast_dw.fact_orders(
    orderId            bigint,
    orderNo            string,
    shopId             bigint,
    userId             bigint,
    orderStatus        bigint,
    goodsMoney         double,
    deliverType        bigint,
    deliverMoney       double,
    totalMoney         double,
    realTotalMoney     double,
    payType            bigint,
    isPay              bigint,
    areaId             bigint,
    userAddressId      bigint,
    areaIdPath         string,
    userName           string,
    userAddress        string,
    userPhone          string,
    orderScore         bigint,
    isInvoice          bigint,
    invoiceClient      string,
    orderRemarks       string,
    orderSrc           bigint,
    needPay            double,
    payRand            bigint,
    orderType          bigint,
    isRefund           bigint,
    isAppraise         bigint,
    cancelReason       bigint,
    rejectReason       bigint,
    rejectOtherReason  string,
    isClosed           bigint,
    goodsSearchKeys    string,
    orderunique        string,
    receiveTime        string,
    deliveryTime       string,
    tradeNo            string,
    dataFlag           bigint,
    createTime         string,
    settlementId       bigint,
    commissionFee      double,
    scoreMoney         double,
    useScore           bigint,
    orderCode          string,
    extraJson          string,
    orderCodeTargetId  bigint,
    noticeDeliver      bigint,
    invoiceJson        string,
    lockCashMoney      double,
    payTime            string,
    isBatch            bigint,
    totalPayFee        bigint,
    modifiedTime        string,
    dw_start_date       string,
    dw_end_date         string
)
partitioned by (dt string) --按照天分區(qū)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');
--臨時(shí)訂單表
DROP TABLE IF EXISTS itcast_dw.tmp_fact_orders;
create  table itcast_dw.tmp_fact_orders(
    orderId            bigint,
    orderNo            string,
    shopId             bigint,
    userId             bigint,
    orderStatus        bigint,
    goodsMoney         double,
    deliverType        bigint,
    deliverMoney       double,
    totalMoney         double,
    realTotalMoney     double,
    payType            bigint,
    isPay              bigint,
    areaId             bigint,
    userAddressId      bigint,
    areaIdPath         string,
    userName           string,
    userAddress        string,
    userPhone          string,
    orderScore         bigint,
    isInvoice          bigint,
    invoiceClient      string,
    orderRemarks       string,
    orderSrc           bigint,
    needPay            double,
    payRand            bigint,
    orderType          bigint,
    isRefund           bigint,
    isAppraise         bigint,
    cancelReason       bigint,
    rejectReason       bigint,
    rejectOtherReason  string,
    isClosed           bigint,
    goodsSearchKeys    string,
    orderunique        string,
    receiveTime        string,
    deliveryTime       string,
    tradeNo            string,
    dataFlag           bigint,
    createTime         string,
    settlementId       bigint,
    commissionFee      double,
    scoreMoney         double,
    useScore           bigint,
    orderCode          string,
    extraJson          string,
    orderCodeTargetId  bigint,
    noticeDeliver      bigint,
    invoiceJson        string,
    lockCashMoney      double,
    payTime            string,
    isBatch            bigint,
    totalPayFee        bigint,
    modifiedTime        string,
    dw_start_date       string,
    dw_end_date         string
)
partitioned by (dt string)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

--創(chuàng)建訂單退款表--帶有分區(qū)字段
drop table if exists `itcast_dw`.`fact_order_refunds`;
create  table `itcast_dw`.`fact_order_refunds`(
    id                bigint,
    orderId           bigint,
    goodsId           bigint,
    refundTo          bigint,
    refundReson       bigint,
    refundOtherReson  string,
    backMoney         double,
    refundTradeNo     string,
    refundRemark      string,
    refundTime        string,
    shopRejectReason  string,
    refundStatus      bigint,
    createTime        string,
    modifiedTime        string,
    dw_start_date string,
  dw_end_date string
)
partitioned by (dt string) --按照天分區(qū)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');
--臨時(shí)表
drop table if exists `itcast_dw`.`tmp_fact_order_refunds`;
create  table `itcast_dw`.`tmp_fact_order_refunds`(
    id                bigint,
    orderId           bigint,
    goodsId           bigint,
    refundTo          bigint,
    refundReson       bigint,
    refundOtherReson  string,
    backMoney         double,
    refundTradeNo     string,
    refundRemark      string,
    refundTime        string,
    shopRejectReason  string,
    refundStatus      bigint,
    createTime        string,
    modifiedTime        string,
    dw_start_date string,
  dw_end_date string
)
partitioned by (dt string)
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');

第一次全量數(shù)據(jù)導(dǎo)入拉鏈表

我們開(kāi)啟hive的動(dòng)態(tài)分區(qū)历等，并根據(jù)數(shù)據(jù)的createtime字段進(jìn)行分區(qū)劃分讨惩，同一天創(chuàng)建的訂單放在同一分區(qū)！寒屯！

#開(kāi)啟動(dòng)態(tài)分區(qū)荐捻，默認(rèn)是false
#開(kāi)啟允許所有分區(qū)都是動(dòng)態(tài)的，否則必須要有靜態(tài)分區(qū)才能使用
set hive.exec.dynamici.partition=true;  
set hive.exec.dynamic.partition.mode=nonstrict; 

訂單表數(shù)據(jù)：ods層導(dǎo)入dw層

insert overwrite table itcast_dw.fact_orders 
select
orderId            ,
orderNo            ,
shopId             ,
userId             ,
orderStatus        ,
goodsMoney         ,
deliverType        ,
deliverMoney       ,
totalMoney         ,
realTotalMoney     ,
payType            ,
isPay              ,
areaId             ,
userAddressId      ,
areaIdPath         ,
userName           ,
userAddress        ,
userPhone          ,
orderScore         ,
isInvoice          ,
invoiceClient      ,
orderRemarks       ,
orderSrc           ,
needPay            ,
payRand            ,
orderType          ,
isRefund           ,
isAppraise         ,
cancelReason       ,
rejectReason       ,
rejectOtherReason  ,
isClosed           ,
goodsSearchKeys    ,
orderunique        ,
receiveTime        ,
deliveryTime       ,
tradeNo            ,
dataFlag           ,
createTime         ,
settlementId       ,
commissionFee      ,
scoreMoney         ,
useScore           ,
orderCode          ,
extraJson          ,
orderCodeTargetId  ,
noticeDeliver      ,
invoiceJson        ,
lockCashMoney      ,
payTime            ,
isBatch            ,
totalPayFee        ,
modifiedTime       ,
--增加開(kāi)始時(shí)間
date_format(modifiedTime,'yyyy-MM-dd') as dw_start_date,
--增加結(jié)束時(shí)間
'9999-12-31' as dw_end_date,
--指定動(dòng)態(tài)分區(qū)使用的字段寡夹，動(dòng)態(tài)分區(qū)的用法：就是查詢(xún)字段的最后一個(gè)字段hive表進(jìn)行解析然后存入指定分區(qū)
--此次數(shù)據(jù)分區(qū)按照訂單的創(chuàng)建時(shí)間
date_format(createtime,'yyyyMMdd') 
from itcast_ods.itcast_orders where dt="20190909";

訂單退款表：ods層導(dǎo)入dw層

insert overwrite table itcast_dw.fact_order_refunds 
select
id,
orderId,
goodsId,
refundTo,
refundReson,
refundOtherReson,
backMoney,
refundTradeNo,
refundRemark,
refundTime,
shopRejectReason,
refundStatus,
createTime,
modifiedTime,
date_format(modifiedTime,'yyyy-MM-dd') as dw_start_date,
'9999-12-31' as dw_end_date,
--此次數(shù)據(jù)分區(qū)按照訂單退款的創(chuàng)建時(shí)間
date_format(createTime,'yyyyMMdd')
from itcast_ods.itcast_order_refunds where dt="20190909"; 

增量數(shù)據(jù)導(dǎo)入拉鏈表

kettle抽取增量數(shù)據(jù)導(dǎo)入ods層

抽取20190910這一天的數(shù)據(jù)处面，查詢(xún)條件為modifiedTime等于20190910這天的訂單數(shù)據(jù)和訂單退款數(shù)據(jù)！菩掏！

表輸入組件sql語(yǔ)句：

SELECT *
FROM itcast_orders

WHERE DATE_FORMAT(modifiedTime, '%Y%m%d') = '${dt}';

字段選擇組件
parquet output組件

ods層數(shù)據(jù)合并到dw層拉鏈表中

insert overwrite table itcast_dw.tmp_fact_orders  
select
dw.orderId            ,
dw.orderNo            ,
dw.shopId             ,
dw.userId             ,
dw.orderStatus        ,
dw.goodsMoney         ,
dw.deliverType        ,
dw.deliverMoney       ,
dw.totalMoney         ,
dw.realTotalMoney     ,
dw.payType            ,
dw.isPay              ,
dw.areaId             ,
dw.userAddressId      ,
dw.areaIdPath         ,
dw.userName           ,
dw.userAddress        ,
dw.userPhone          ,
dw.orderScore         ,
dw.isInvoice          ,
dw.invoiceClient      ,
dw.orderRemarks       ,
dw.orderSrc           ,
dw.needPay            ,
dw.payRand            ,
dw.orderType          ,
dw.isRefund           ,
dw.isAppraise         ,
dw.cancelReason       ,
dw.rejectReason       ,
dw.rejectOtherReason  ,
dw.isClosed           ,
dw.goodsSearchKeys    ,
dw.orderunique        ,
dw.receiveTime        ,
dw.deliveryTime       ,
dw.tradeNo            ,
dw.dataFlag           ,
dw.createTime         ,
dw.settlementId       ,
dw.commissionFee      ,
dw.scoreMoney         ,
dw.useScore           ,
dw.orderCode          ,
dw.extraJson          ,
dw.orderCodeTargetId  ,
dw.noticeDeliver      ,
dw.invoiceJson        ,
dw.lockCashMoney      ,
dw.payTime            ,
dw.isBatch            ,
dw.totalPayFee        ,
dw.modifiedTime ,
dw.dw_start_date,
--修改end_date
case when ods.orderid is not null and dw.dw_end_date ='9999-12-31'
then '2019-09-09'
else dw.dw_end_date
end as dw_end_date,
--動(dòng)態(tài)分區(qū)需要的字段
dw.dt
from 
itcast_dw.fact_orders  dw 
left join 
(select * from itcast_ods.itcast_orders where dt ='20190910') ods
on dw.orderid=ods.orderid 
union all
--今天新增數(shù)據(jù)的插入動(dòng)作
select
orderId            ,
orderNo            ,
shopId             ,
userId             ,
orderStatus        ,
goodsMoney         ,
deliverType        ,
deliverMoney       ,
totalMoney         ,
realTotalMoney     ,
payType            ,
isPay              ,
areaId             ,
userAddressId      ,
areaIdPath         ,
userName           ,
userAddress        ,
userPhone          ,
orderScore         ,
isInvoice          ,
invoiceClient      ,
orderRemarks       ,
orderSrc           ,
needPay            ,
payRand            ,
orderType          ,
isRefund           ,
isAppraise         ,
cancelReason       ,
rejectReason       ,
rejectOtherReason  ,
isClosed           ,
goodsSearchKeys    ,
orderunique        ,
receiveTime        ,
deliveryTime       ,
tradeNo            ,
dataFlag           ,
createTime         ,
settlementId       ,
commissionFee      ,
scoreMoney         ,
useScore           ,
orderCode          ,
extraJson          ,
orderCodeTargetId  ,
noticeDeliver      ,
invoiceJson        ,
lockCashMoney      ,
payTime            ,
isBatch            ,
totalPayFee        ,
modifiedTime       ,
--增加開(kāi)始時(shí)間
date_format(modifiedTime,'yyyy-MM-dd') as dw_start_date,
--增加結(jié)束時(shí)間
'9999-12-31' as dw_end_date,
--指定動(dòng)態(tài)分區(qū)使用的字段魂角，動(dòng)態(tài)分區(qū)的用法：就是查詢(xún)字段的最后一個(gè)字段hive表進(jìn)行解析然后存入指定分區(qū)
--此次數(shù)據(jù)分區(qū)按照訂單的創(chuàng)建時(shí)間
date_format(createtime,'yyyyMMdd') 
from itcast_ods.itcast_orders where dt="20190910";
--從臨時(shí)表再插入itcast_dw.fact_orders
insert overwrite table itcast_dw.fact_orders
select
* from
itcast_dw.tmp_fact_orders;
--驗(yàn)證數(shù)據(jù)查詢(xún)拉鏈表數(shù)據(jù)
select * from itcast_dw.fact_orders limit 5;


--訂單退款表增量數(shù)據(jù)與歷史數(shù)據(jù)合并覆蓋插入dw層臨時(shí)拉鏈表中
insert overwrite table itcast_dw.tmp_fact_order_refunds
select
dw.id,
dw.orderId,
dw.goodsId,
dw.refundTo,
dw.refundReson,
dw.refundOtherReson,
dw.backMoney,
dw.refundTradeNo,
dw.refundRemark,
dw.refundTime,
dw.shopRejectReason,
dw.refundStatus,
dw.createTime,
dw.modifiedTime,
dw.dw_start_date,
case when ods.id is not null and dw.dw_end_date ='9999-12-31'
then '2019-09-09'
else dw.dw_end_date
end as dw_end_date,
dw.dt
from  itcast_dw.fact_order_refunds  dw
left join (select * from itcast_ods.itcast_order_refunds where dt="20190910") ods
on dw.id =ods.id 
union all 
select
id,
orderId,
goodsId,
refundTo,
refundReson,
refundOtherReson,
backMoney,
refundTradeNo,
refundRemark,
refundTime,
shopRejectReason,
refundStatus,
createTime,
modifiedTime,
date_format(modifiedTime,'yyyy-MM-dd') as dw_start_date,
'9999-12-31' as dw_end_date,
date_format(createTime,'yyyyMMdd')
from itcast_ods.itcast_order_refunds where dt="20190910";
--合并數(shù)據(jù)插入臨時(shí)表
insert overwrite table itcast_dw.fact_order_refunds
select * from itcast_dw.tmp_fact_order_refunds;
--驗(yàn)證數(shù)據(jù)
select * from itcast_dw.fact_order_refunds limit 5;

拉鏈表分區(qū)意義

--合并11號(hào)數(shù)據(jù)
insert overwrite table itcast_dw.tmp_fact_orders  
select
dw.orderId            ,
dw.orderNo            ,
dw.shopId             ,
dw.userId             ,
dw.orderStatus        ,
dw.goodsMoney         ,
dw.deliverType        ,
dw.deliverMoney       ,
dw.totalMoney         ,
dw.realTotalMoney     ,
dw.payType            ,
dw.isPay              ,
dw.areaId             ,
dw.userAddressId      ,
dw.areaIdPath         ,
dw.userName           ,
dw.userAddress        ,
dw.userPhone          ,
dw.orderScore         ,
dw.isInvoice          ,
dw.invoiceClient      ,
dw.orderRemarks       ,
dw.orderSrc           ,
dw.needPay            ,
dw.payRand            ,
dw.orderType          ,
dw.isRefund           ,
dw.isAppraise         ,
dw.cancelReason       ,
dw.rejectReason       ,
dw.rejectOtherReason  ,
dw.isClosed           ,
dw.goodsSearchKeys    ,
dw.orderunique        ,
dw.receiveTime        ,
dw.deliveryTime       ,
dw.tradeNo            ,
dw.dataFlag           ,
dw.createTime         ,
dw.settlementId       ,
dw.commissionFee      ,
dw.scoreMoney         ,
dw.useScore           ,
dw.orderCode          ,
dw.extraJson          ,
dw.orderCodeTargetId  ,
dw.noticeDeliver      ,
dw.invoiceJson        ,
dw.lockCashMoney      ,
dw.payTime            ,
dw.isBatch            ,
dw.totalPayFee        ,
dw.modifiedTime ,
dw.dw_start_date,
--修改end_date
case when ods.orderid is not null and dw.dw_end_date ='9999-12-31'
then '2019-09-10'
else dw.dw_end_date
end as dw_end_date,
--動(dòng)態(tài)分區(qū)需要的字段
dw.dt
from 
(select * from itcast_dw.fact_orders  where dt > '20190801')  dw left join 
(select * from itcast_ods.itcast_orders where dt ='20190911') ods
on dw.orderid=ods.orderid 
union all
--今天新增數(shù)據(jù)的插入動(dòng)作
select
orderId            ,
orderNo            ,
shopId             ,
userId             ,
orderStatus        ,
goodsMoney         ,
deliverType        ,
deliverMoney       ,
totalMoney         ,
realTotalMoney     ,
payType            ,
isPay              ,
areaId             ,
userAddressId      ,
areaIdPath         ,
userName           ,
userAddress        ,
userPhone          ,
orderScore         ,
isInvoice          ,
invoiceClient      ,
orderRemarks       ,
orderSrc           ,
needPay            ,
payRand            ,
orderType          ,
isRefund           ,
isAppraise         ,
cancelReason       ,
rejectReason       ,
rejectOtherReason  ,
isClosed           ,
goodsSearchKeys    ,
orderunique        ,
receiveTime        ,
deliveryTime       ,
tradeNo            ,
dataFlag           ,
createTime         ,
settlementId       ,
commissionFee      ,
scoreMoney         ,
useScore           ,
orderCode          ,
extraJson          ,
orderCodeTargetId  ,
noticeDeliver      ,
invoiceJson        ,
lockCashMoney      ,
payTime            ,
isBatch            ,
totalPayFee        ,
modifiedTime       ,
--增加開(kāi)始時(shí)間
date_format(modifiedTime,'yyyy-MM-dd') as dw_start_date,
--增加結(jié)束時(shí)間
'9999-12-31' as dw_end_date,
--指定動(dòng)態(tài)分區(qū)使用的字段，動(dòng)態(tài)分區(qū)的用法：就是查詢(xún)字段的最后一個(gè)字段hive表進(jìn)行解析然后存入指定分區(qū)
--此次數(shù)據(jù)分區(qū)按照訂單的創(chuàng)建時(shí)間
date_format(createtime,'yyyyMMdd') 
from itcast_ods.itcast_orders where dt="20190911";

剩余表增量抽取

使用kettle完成剩余表的增量抽取到ods層