hive增量表和全量表_你需要了解的全量表,增量表及拉链表

mysql测试数据准备第一天 9月10号数据

1

2

31,待支付,2020-09-10 12:20:11,2020-09-10 12:20:11

2,待支付,2020-09-10 14:20:11,2020-09-10 14:20:11

3,待支付,2020-09-10 16:20:11,2020-09-10 16:20:11

第二天 9月11号数据

1

2

3

4

51,待支付,2020-09-10 12:20:11,2020-09-10 12:20:11

2,已支付,2020-09-10 14:20:11,2020-09-11 14:21:11

3,已支付,2020-09-10 16:20:11,2020-09-11 16:21:11

4,待支付,2020-09-11 12:20:11,2020-09-11 12:20:11

5,待支付,2020-09-11 14:20:11,2020-09-11 14:20:11

对比mysql第一天和第二天的数据发现，第二天新增了订单id为4和5这两条数据，并且订单id为2和3的状态更新为了已支付

全量表

每天所有的最新状态的数据，都要全量抽取到ods层，每个分区保留历史全量快照。

1、全量表，有无变化，都要报

2、每次上报的数据都是所有的数据(变化的 + 没有变化的)

9月10号全量抽取到ods层1

2

3

4

5

6

7

8create table 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 ','

;

1

2

3

4

5

6

7

8

9create table 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 ','

;

1

2

3

4

5

6

7

8# 把ods_order_info_20200910数据全量插到dwd层2020-09-10分区

insert overwrite table dwd_order_info_df partition(date_id = '2020-09-10')

select

order_id

,order_status

,create_time

,update_time

from ods_order_info_20200910;

9月11号全量抽取到ods层

1

2

3

4

5

6

7create table 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 ',';

1

2

3

4

5

6

7

8# 把ods_order_info_20200911数据全量插到dwd层2020-09-11分区

insert overwrite table dwd_order_info_df partition(date_id = '2020-09-11')

select

order_id

,order_status

,create_time

,update_time

from wedw_ods.order_info_20200911;

增量表

增量表：新增数据，增量数据是上次导出之后的新数据。

1、记录每次增加的量，而不是总量；

2、增量表，只报变化量，无变化不用报

3、业务库表中需有主键及创建时间，修改时间

9月10号全量抽取到ods层(全量初始化)

1

2

3

4

5

6

7

8# 把ods——order_info_20200910数据全量插到dwd层2020-09-10分区

insert overwrite table dwd_order_info_di partition(date_id = '2020-09-10')

select

order_id

,order_status

,create_time

,update_time

from ods_order_info_20200910;

9月11号抽取更新的数据及当天新增的数据，即订单id为2,3,4,5的数据

dwd_order_info_di表9月10号的分区数据与ods_order_info_20200911增量抽取的数据合并，有2种方案:

a.两个表通过主键关联，dwd表存在并且ods表不存在的数据union all一下ods_order_info_20200911表所有的数据，即全量数据插入到dwd表的9月11号的分区

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22insert overwrite table dwd_order_info_di partition(date_id = '2020-09-11')

select

t1.order_id

,t1.order_status

,t1.create_time

,t1.update_time

from

dwd_order_info_di t1

left join

ods_order_info_20200911 t2

on t1.order_id = t2.order_id

where t1.date_id = '2020-09-10'

and t2.order_id is null

union all

select

order_id

,order_status

,create_time

,update_time

from

ods_order_info_20200911

;

b.两个表数据union all一下，再根据order_id去重(根据order分组，更新时间降序，取第一条)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38insert overwrite table 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

dwd_order_info_di

where date_id = '2020-09-10'

union all

select

order_id

,order_status

,create_time

,update_time

from

ods_order_info_20200911

) t1

) t2

where t2.rn = 1

;

特殊增量表：da表，每天的分区就是当天的数据，其数据特点就是数据产生后就不会发生变化，如日志表。

拉链表

拉链表维护历史状态，以及最新状态数据适用一下情况：

数据量比较大

表中的部分字段会被更新

需要查看某一个时间点或者时间段的历史快照信息

更新的比例和频率不是很大，如果表中信息变化不是很大，每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费优点

9月10号全量抽取到ods层1

2

3

4

5

6

7create table 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’ 表示该条记录目前处于有效状态

1

2

3

4

5

6

7

8

9

10create table 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

1

2

3

4

5

6

7

8

9

10insert overwrite table 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

from

ods_order_info_20200910;

9月11号抽取更新的数据及当天新增的数据到ods层，即订单id为2,3,4,5的数据

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23insert overwrite table 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_dt

from

dwd_order_info_dz t1

left join ods_order_info_20200911 t2

on t1.order_id = t2.order_id

where t1.date_id = '2020-09-10'

union all

SELECT

t1.order_id

,t1.order_status

,t1.create_time

,t1.update_time

,to_date(update_time) as start_dt

,'9999-12-31' as end_dt

FROM ods_order_info_20200911 t1

;

查询当前的所有有效记录1

2

3

4

5

6

7select

*

from

dwd_order_info_dz

where

date_id = '2020-09-11'

and end_dt ='9999-12-31';

查询9月10号历史快照1

2

3

4

5

6

7

8select

*

from

dwd_order_info_dz

where

date_id = '2020-09-10'

and start_dt <= '2020-09-10'

and end_dt >='2020-09-10';

查询9月11号历史快照1

2

3

4

5

6

7

8select

*

from

dwd_order_info_dz

where

date_id = '2020-09-11'

and start_dt <= '2020-09-11'

and end_dt >='2020-09-11';

总结

1、如果数据量不是很大(不超过20W)且预估后续增长的非常慢，可以考虑全量表抽取，这是最简便的方法

2、如果数据量目前来说不是很大，但是业务发展很快，数据量一段时间后就会上来，建议增量抽取

3、目前数据量本身就非常大，肯定是需要增量抽取的，比如现在有10亿数据，如果你每天全量抽取一遍，相信我，你会抽哭的

4、对于历史状态需要保存的，这个时候就需要使用拉链表了，实际工作中，使用拉链表的场景并不会太多，比如订单表，保存订单历史状态，维表(缓慢变化维的处理)