1

Mysql數據準備

第一天 9月10號數據

1,待支付,2020-09-10 12:20:11,2020-09-10 12:20:112,待支付,2020-09-10 14:20:11,2020-09-10 14:20:113,待支付,2020-09-10 16:20:11,2020-09-10 16:20:11

第二天 9月11號數據

1,待支付,2020-09-10 12:20:11,2020-09-10 12:20:112,已支付,2020-09-10 14:20:11,2020-09-11 14:21:113,已支付,2020-09-10 16:20:11,2020-09-11 16:21:114,待支付,2020-09-11 12:20:11,2020-09-11 12:20:115,待支付,2020-09-11 14:20:11,2020-09-11 14:20:11

對比mysql第一天和第二天的數據發現，第二天新增了訂單id為4和5這兩條數據，并且訂單id為2和3的狀態更新為了已支付

2

全量表

每天的所有的最新狀態的數據。

1、全量表，有無變化，都要報

2、每次上報的數據都是所有的數據(變化的 + 沒有變化的)

9月10號全量抽取到ods層

create table wedw_ods.order_info_20200910( order_id ? ? string ? ?COMMENT '訂單id',order_status string ? ?COMMENT '訂單狀態',create_time ?timestamp COMMENT '創建時間',update_time ?timestamp COMMENT '更新時間') COMMENT '訂單表'row format delimited fields terminated by ',';

create table wedw_dwd.order_info_df( order_id ? ? string ? ?COMMENT '訂單id',order_status string ? ?COMMENT '訂單狀態',create_time ?timestamp COMMENT '創建時間',update_time ?timestamp COMMENT '更新時間') COMMENT '訂單表'partitioned by (date_id string)row format delimited fields terminated by ',';# 把wedw_ods.order_info_20200910數據全量插到dwd層2020-09-10分區insert overwrite table wedw_dwd.order_info_df partition(date_id = '2020-09-10')selectorder_id,order_status,create_time,update_timefrom wedw_ods.order_info_20200910;

9月11號全量抽取到ods層

create?table?wedw_ods.order_info_20200911( order_id ? ? string ? ?COMMENT '訂單id',order_status string ? ?COMMENT '訂單狀態',create_time ?timestamp COMMENT '創建時間',update_time ?timestamp COMMENT '更新時間') COMMENT '訂單表'row format delimited fields terminated by ',';

#?把wedw_ods.order_info_20200911數據全量插到dwd層2020-09-11分區insert?overwrite?table?wedw_dwd.order_info_df?partition(date_id?=?'2020-09-11')selectorder_id,order_status,create_time,update_timefrom?wedw_ods.order_info_20200911;

全量抽取，每個分區保留歷史全量快照。

3

增量表

增量表：新增數據，增量數據是上次導出之后的新數據。

1、記錄每次增加的量，而不是總量；

2、增量表，只報變化量，無變化不用報

3、業務庫表中需有主鍵及創建時間，修改時間

9月10號全量抽取到ods層(全量初始化)

# 把wedw_ods.order_info_20200910數據全量插到dwd層2020-09-10分區insert?overwrite?table?wedw_dwd.order_info_di?partition(date_id?=?'2020-09-10')selectorder_id,order_status,create_time,update_timefrom wedw_ods.order_info_20200910;

9月11號抽取更新的數據及當天新增的數據，即訂單id為2,3,4,5的數據

wedw_dwd.order_info_di表9月10號的分區數據與wedw_ods.order_info_20200911增量抽取的數據合并，有2種方案

a.兩個表通過主鍵關聯，dwd表存在并且ods表不存在的數據

union all 一下wedw_ods.order_info_20200911表所有的數據，即全量數據插入到dwd表的9月11號的分區

insert overwrite table wedw_dwd.order_info_di partition(date_id = '2020-09-11')select t1.order_id,t1.order_status,t1.create_time,t1.update_timefromwedw_dwd.order_info_di t1left joinwedw_ods.order_info_20200911 t2on t1.order_id = t2.order_idwhere t1.date_id = '2020-09-10'and t2.order_id is nullunion allselect order_id,order_status,create_time,update_timefrom wedw_ods.order_info_20200911;

b.兩個表數據union all一下，再根據order_id去重(根據order分組，更新時間降序，取第一條)

insert overwrite table wedw_dwd.order_info_di partition(date_id = '2020-09-11')select t2.order_id,t2.order_status,t2.create_time,t2.update_time from( ? ?select ? ? t1.order_id ? ?,t1.order_status ? ?,t1.create_time ? ?,t1.update_time ? ?,row_number() over(partition by order_id order by update_time desc) as rn ? ?from ? ?( ? ? ? ?select ? ? ? ? order_id ? ? ? ?,order_status ? ? ? ?,create_time ? ? ? ?,update_time ? ? ? ?from ? ? ? ?wedw_dwd.order_info_di ? ? ? ?where date_id = '2020-09-10' ? ? ? ?union all ? ? ? ?select ? ? ? ? order_id ? ? ? ?,order_status ? ? ? ?,create_time ? ? ? ?,update_time ? ? ? ?from ? ? ? ?wedw_ods.order_info_20200911 ? ?) t1) t2where t2.rn = 1;

特殊增量表：da表，每天的分區就是當天的數據，其數據特點就是數據產生后就不會發生變化，如日志表。

4

拉鏈表

維護歷史狀態，以及最新狀態數據

適用情況：

1.數據量比較大

2.表中的部分字段會被更新

3.需要查看某一個時間點或者時間段的歷史快照信息

? ? 查看某一個訂單在歷史某一個時間點的狀態

? ? 某一個用戶在過去某一段時間，下單次數

4.更新的比例和頻率不是很大

? ? 如果表中信息變化不是很大，每天都保留一份全量，那么每次全量中會保存很多不變的信息，對存儲是極大的浪費

優點

1、滿足反應數據的歷史狀態

2、最大程度節省存儲

9月10號全量抽取到ods層

create table wedw_ods.order_info_20200910( order_id ? ? string ? ?COMMENT '訂單id',order_status string ? ?COMMENT '訂單狀態',create_time ?timestamp COMMENT '創建時間',update_time ?timestamp COMMENT '更新時間') COMMENT '訂單表'row format delimited fields terminated by ',';

建立dwd層拉鏈表

增加兩個字段：
start_dt(表示該條記錄的生命周期開始時間——周期快照時的狀態)
end_dt(該條記錄的生命周期結束時間)

end_dt= ‘9999-12-31’ 表示該條記錄目前處于有效狀態

create table wedw_dwd.order_info_dz( order_id ? ? string ? ?COMMENT '訂單id',order_status string ? ?COMMENT '訂單狀態',create_time ?timestamp COMMENT '創建時間',update_time ?timestamp COMMENT '更新時間',start_dt ? ? date ? ? ?COMMENT '開始生效日期',end_dt ? ? ? date ? ? ?COMMENT '結束生效日期') COMMENT '訂單表'partitioned by (date_id string)row format delimited fields terminated by ',';

注：第一次加工的時候需要初始化所有數據，start_time設置為數據日期2020-09-10?，end_time設置為9999-12-31

insert overwrite table wedw_dwd.order_info_dz partition(date_id = '2020-09-10')select order_id ? ?,order_status,create_time ,update_time ,to_date(update_time) as start_dt ? ,'9999-12-31' as end_dt ?fromwedw_ods.order_info_20200910;

9月11號抽取更新的數據及當天新增的數據到ods層，即訂單id為2,3,4,5的數據

insert overwrite table wedw_dwd.order_info_dz partition(date_id = '2020-09-11')select t1.order_id ? ?,t1.order_status,t1.create_time ,t1.update_time,t1.start_dt,case when t1.end_dt = '9999-12-31' and t2.order_id is not null then t1.date_id else t1.end_dt end as end_dtfromwedw_dwd.order_info_dz t1left join wedw_ods.order_info_20200911 t2on t1.order_id = t2.order_idwhere t1.date_id = '2020-09-10'union allSELECT t1.order_id ? ?,t1.order_status,t1.create_time ,t1.update_time,to_date(update_time) as start_dt,'9999-12-31' as end_dtFROM wedw_ods.order_info_20200911 t1;

查詢當前的所有有效記錄：

select * from wedw_dwd.order_info_dz where date_id = '2020-09-11'and?end_dt?='9999-12-31';

查詢9月10號歷史快照:

select * from wedw_dwd.order_info_dz where date_id = '2020-09-10' and start_dt <= '2020-09-10' and end_dt >='2020-09-10';

查詢9月11號歷史快照:

select * from wedw_dwd.order_info_dz where date_id = '2020-09-11' and start_dt <= '2020-09-11' and end_dt >='2020-09-11';

5

總結

不知道以上的一些例子大家有沒有看明白呢？

在工作中，其實上述3種表都是很有可能會用到的，那么我們應該怎么選擇呢？

• 如果數據量不是很大(不超過20W)且預估后續增長的非常慢，可以考慮全量表抽取，這是最簡便的方法

• 如果數據量目前來說不是很大，但是業務發展很快，數據量一段時間后就會上來，建議增量抽取哦

• 目前數據量本身就非常大，肯定是需要增量抽取的，比如現在有10億數據，如果你每天全量抽取一遍，相信我，你會抽哭的

• 對于歷史狀態需要保存的，這個時候就需要使用拉鏈表了，實際工作中，使用拉鏈表的場景并不會太多，比如訂單表，保存訂單歷史狀態，維表(緩慢變化維的處理)

2020大數據面試題真題總結(附答案)

微信交流群

緩慢變化維(SCD)常見解決方案

全方位解讀星型模型,雪花模型及星座模型

Sqoop or Datax

left join(on&where)

ID-Mapping

你們公司還在用SparkOnYan嗎？

大廠高頻面試題-連續登錄問題

朋友面試數據研發崗遇到的面試題

數據倉庫分層架構

clickhouse實踐篇-SQL語法

clickhouse實踐篇-表引擎

簡單聊一聊大數據學習之路

朋友面試數據專家崗遇到的面試題

HADOOP快速入門

數倉工程師的利器-HIVE詳解

Hive調優，每一個數據工程師必備技能

OLAP引擎—Kylin介紹

Hbase從入門到入坑

Kafka

Datax-數據抽取同步利器

Spark數據傾斜解決方案

Spark統一內存管理機制

數據治理之數據質量管理

數據治理之元數據管理

數據倉庫中的維表和事實表

知識點總結——數倉表一覽

總結

以上是生活随笔為你收集整理的update 两个表关联_你真的了解全量表,增量表及拉链表吗？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。