文章目录
一 用户维度表(拉链表)
全量表,特殊表这些表的特点就是每天导入的数据互相不影响,现在导入的ods表对应的ods层表格为user_info表,导入的方式为“新增和变化”,6-14导入的数据为全量导入,因为是数仓的第一天,但6-15就不是全量导入了,6-15导入的数据是6-14修改的数据或者是新增的数据,在导入维度信息表的时候,ods层中的全量表完全放入到dim层,是一个完整的维度信息,所有的用户都能查得到。但到了6-15,这是一个增量及变化的表,就要结合6-14已知的数据和6-15新增的数据得到6-15新的数据,所以这个表的插入和以前就不一样了。
为了记录这个变化的过程,新的表格数据应该包括过期的数据和最新的数据两个部分,现在用的数据就是最新的数据,过期的数据是为了将数据变化的部分存档,将具有这种功能的表格称为拉链表。
1 什么是拉链表
拉链表,记录每条信息的生命周期,一旦一条记录的生命周期结束,就重新开始一条新的记录,并把当前日期放入生效开始日期。
如果当前信息至今有效,在生效结束日期中填入一个极大值(如9999-99-99 )。表现的就是数据的变化过程。
用户ID | 姓名 | 手机号码 | 开始日期 | 结束日期 |
---|---|---|---|---|
1 | 张三 | 136****9090 | 2019-01-01 | 2019-01-01 |
1 | 张三 | 137****8989 | 2019-01-02 | 2019-01-09 |
1 | 张三 | 147****1234 | 2019-01-10 | 9999-99-99 |
2 为什么要做拉链表
拉链表的数据冗余度非常小,将重复的信息尽量压缩成一条,如第二条数据,1-02 – 1-09在全量表中会将每一天的数据都存储下来,而在拉链表中就只会保存为一条数据。对于维度比较大,而数据变化又非常缓慢的数据,就非常适合用拉链表进行存储。比如:用户信息会发生变化,但是每天变化的比例不高。如果数据量有一定规模,按照每日全量的方式保存效率很低。 比如:1亿用户*365天,每天一份用户信息。(做每日全量效率低)
全量表:
用户ID | 姓名 | 手机号码 | dt |
---|---|---|---|
1 | 张三 | 136****9090 | 2019-01-01 |
1 | 张三 | 136****9090 | 2019-01-02 |
1 | 张三 | 136****9090 | 2019-01-03 |
··· | ··· | ··· | ··· |
1 | 张三 | 136****9090 | 2019-05-11 |
1 | 张三 | 155****1212 | 2019-05-12 |
拉链表:
用户ID | 姓名 | 手机号码 | 开始日期 | 结束日期 |
---|---|---|---|---|
1 | 张三 | 136****9090 | 2019-01-01 | 2019-05-12 |
1 | 张三 | 155****1212 | 2019-05-11 | 9999-99-99 |
那么反过来,数据量小的数据没有必要使用拉链表,数据量变化的历史全部都使用全量表记录下来了。对于数据量很大,变化很剧烈的数据,每一天都相当于一个新的表,也使用全量表。
3 如何使用拉链表
想要获取1-07 张三的电话号码,这时就可以通过切片去查询
通过,生效开始日期<=某个日期 且 生效结束日期>=某个日期 ,能够得到某个时间点的数据全量切片。
例如获取2019-01-01的历史切片:
select * from user_info where start_date<=’2019-01-01’ and end_date>=’2019-01-01’
例如获取2019-01-02的历史切片:
select * from order_info where start_date<=’2019-01-02’ and end_date>=’2019-01-02’
4 拉链表形成过程
昨日的全量表 + 今日的修改表 = 拉链表
(1)假设, 2019年 1月1日的用户全量表是最初始的用户表,如下
用户ID | 姓名 |
---|---|
1 | 张三 |
2 | 李四 |
3 | 王五 |
(2)初始的拉链表就等于最开始的2019年 1月1日的用户全量表
用户ID | 姓名 | 开始时间 | 结束时间 |
---|---|---|---|
1 | 张三 | 2019-01-01 | 9999-99-99 |
2 | 李四 | 2019-01-01 | 9999-99-99 |
3 | 王五 | 2019-01-01 | 9999-99-99 |
(3)第二天 1月2日 用户全量表(用户2发生状态修改;用户4、5增加)
用户ID | 姓名 |
---|---|
1 | 张三 |
2 | 李小四 |
3 | 王五 |
4 | 赵六 |
5 | 田七 |
(4)根据用户表的创建时间和操作时间,得到用户变化表。
用户ID | 姓名 |
---|---|
2 | 李小四 |
4 | 赵六 |
5 | 田七 |
(5)用户变化表与之前的拉链表合并得到
用户ID | 姓名 | 开始时间 | 结束时间 |
---|---|---|---|
1 | 张三 | 2019-01-01 | 9999-99-99 |
2 | 李四 | 2019-01-01 | 2019-01-01 |
2 | 李小四 | 2019-01-02 | 9999-99-99 |
3 | 王五 | 2019-01-01 | 9999-99-99 |
4 | 赵六 | 2019-01-02 | 9999-99-99 |
5 | 田七 | 2019-01-02 | 9999-99-99 |
那么得到今日全量表以后,今日的全量是所有结束时间等于9999-99-99的数据,再加上明日的变化数据,就等于明日的拉链表。其中封口的数据不会变化,因为其已经是历史数据了,如2。
5 制作拉链表
用户拉链表分区:
6-14当天的数据是一份全量数据,第一天数据的导入十分简单,直接将ods层的数据直接拿过来就可以了。第二天导入的ods数据就变成了一个增量数据,是对于14号新增且修改数据。数据拿过来以后,应该将6-15当天的数据和6-14的数据做一个融合,6-14的数据肯定是14号当天使用的数据,新增的数据肯定是新数据,被修改的数据叫做过期数据,过期数据的end data是6-14号,就产生了第一批过期的数据。
截止到6-15最全的数据是之前6-14没有被修改的数据以及6-15修改及新增的数据,过期的数据是6-14被修改的数据。
如何设计:做一张分区表,按照dt进行分区,其中有一个分区叫做9999-99-99,表示当前最新的数据。6-14需要往这个分区加入所有数据。第二天就需要把昨天最新的数据查出来 + 今天新增的数据 join在一起。将昨天没修改的 + 今天最新的数据拼成一份,覆盖到9999-99-99分区中,同时将过期的数据放到2020-06-14分区中,表示当天过期的数据。
之后如果想查询用户的维度信息,只需要查找9999-99-99分区即可。
(1)建表语句
添加了一个start_data和end_data,其余部分就是ods_user_info这张表。
DROP TABLE IF EXISTS dim_user_info;
CREATE EXTERNAL TABLE dim_user_info(
`id` STRING COMMENT '用户id',
`login_name` STRING COMMENT '用户名称',
`nick_name` STRING COMMENT '用户昵称',
`name` STRING COMMENT '用户姓名',
`phone_num` STRING COMMENT '手机号码',
`email` STRING COMMENT '邮箱',
`user_level` STRING COMMENT '用户等级',
`birthday` STRING COMMENT '生日',
`gender` STRING COMMENT '性别',
`create_time` STRING COMMENT '创建时间',
`operate_time` STRING COMMENT '操作时间',
`start_date` STRING COMMENT '开始日期',
`end_date` STRING COMMENT '结束日期'
) COMMENT '用户表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dim/dim_user_info/'
TBLPROPERTIES ("orc.compress"="snappy");
(2)数据装载
首日装载
6-15 产生6-14过期的数据,6-16产生6-15过期的数据,循环往复,所以对于这张表,首日导入和每日导入是有区别的。
拉链表首日装载,需要进行初始化操作,具体工作为将截止到初始化当日的全部历史用户导入一次性导入到拉链表中。目前的ods_order_info表的第一个分区,即2020-06-14分区中就是全部的历史用户,故将该分区数据进行一定处理后导入拉链表的9999-99-99分区即可。
insert overwrite table dim_user_info partition(dt='9999-99-99')
select
id,
login_name,
nick_name,
md5(name),
md5(phone_num),
md5(email),
user_level,
birthday,
gender,
create_time,
operate_time,
'2020-06-14',
'9999-99-99'
from ods_user_info
where dt='2020-06-14';
md5() 执行加密操作
每日装载
实现思路
每日更新需要将前一日(6-14)的全量数据查询出来 join 今天的数据(6-15的ods数据)
join的选择:需要将昨日的全量 + 今日的增量数据
- 内连接:能够用id连上的数据,说明被修改了,也就是修改的数据才能够被连上
- 右外连接:昨日没被修改的数据查询不到
- 全外连接:所有的数据都能被查询出来
动态分区:现有三列 id,name,class,未分区,称为老表。
有一张新表,有三列,id,name,class,其中class是分区列,称为新表。
动态分区就是自动将分区插入进去
insert into newbiao id,name,partition(class) select id,name,class from oldbiao
第一列id,第二列name是数据,第三列partition(class)会和后面三列进行匹配,这时会自动按照oldbiao第三列的数值做分区,这就是动态分区。
每日装载sql语句如下:
with
tmp as
(
select
old.id old_id,
old.login_name old_login_name,
old.nick_name old_nick_name,
old.name old_name,
old.phone_num old_phone_num,
old.email old_email,
old.user_level old_user_level,
old.birthday old_birthday,
old.gender old_gender,
old.create_time old_create_time,
old.operate_time old_operate_time,
old.start_date old_start_date,
old.end_date old_end_date,
new.id new_id,
new.login_name new_login_name,
new.nick_name new_nick_name,
new.name new_name,
new.phone_num new_phone_num,
new.email new_email,
new.user_level new_user_level,
new.birthday new_birthday,
new.gender new_gender,
new.create_time new_create_time,
new.operate_time new_operate_time,
new.start_date new_start_date,
new.end_date new_end_date
from
(
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date
from dim_user_info
where dt='9999-99-99'
)old
full outer join
(
select
id,
login_name,
nick_name,
md5(nick_name) nick_name,
md5(name) name,
md5(phone_num) phone_num,
user_level,
birthday,
gender,
create_time,
operate_time,
'2020-06-15' start_date,
'9999-99-99' end_date
from ods_user_info
where dt='2020-06-15'
)new
on old.id=new.id
)
insert overwrite table dim_user_info partition(dt)
select
nvl(new_id,old_id),
nvl(new_login_name,old_login_name),
nvl(new_nick_name,old_nick_name),
nvl(new_name,old_name),
nvl(new_phone_num,old_phone_num),
nvl(new_email,old_email),
nvl(new_user_level,old_user_level),
nvl(new_birthday,old_birthday),
nvl(new_gender,old_gender),
nvl(new_create_time,old_create_time),
nvl(new_operate_time,old_operate_time),
nvl(new_start_date,old_start_date),
nvl(new_end_date,old_end_date),
nvl(new_end_date,old_end_date) dt
from tmp
union all
select
old_id,
old_login_name,
old_nick_name,
old_name,
old_phone_num,
old_email,
old_user_level,
old_birthday,
old_gender,
old_create_time,
old_operate_time,
old_start_date,
cast(date_add('2020-06-15',-1) as string),
cast(date_add('2020-06-15',-1) as string) dt
from tmp
where new_id is not null and old_id is not null;
二 DIM层首日数据装载脚本
1 新建文件
# 在/home/hzy/bin目录下创建脚本ods_to_dim_db_init.sh
vim ods_to_dim_db_init.sh
2 脚本内容
#!/bin/bash
APP=gmall
if [ -n "$2" ] ;then
do_date=$2
else
echo "请传入日期参数"
exit
fi
dim_user_info="
insert overwrite table ${APP}.dim_user_info partition(dt='9999-99-99')
select
id,
login_name,
nick_name,
md5(name),
md5(phone_num),
md5(email),
user_level,
birthday,
gender,
create_time,
operate_time,
'$do_date',
'9999-99-99'
from ${APP}.ods_user_info
where dt='$do_date';
"
dim_sku_info="
with
sku as
(
select
id,
price,
sku_name,
sku_desc,
weight,
is_sale,
spu_id,
category3_id,
tm_id,
create_time
from ${APP}.ods_sku_info
where dt='$do_date'
),
spu as
(
select
id,
spu_name
from ${APP}.ods_spu_info
where dt='$do_date'
),
c3 as
(
select
id,
name,
category2_id
from ${APP}.ods_base_category3
where dt='$do_date'
),
c2 as
(
select
id,
name,
category1_id
from ${APP}.ods_base_category2
where dt='$do_date'
),
c1 as
(
select
id,
name
from ${APP}.ods_base_category1
where dt='$do_date'
),
tm as
(
select
id,
tm_name
from ${APP}.ods_base_trademark
where dt='$do_date'
),
attr as
(
select
sku_id,
collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
from ${APP}.ods_sku_attr_value
where dt='$do_date'
group by sku_id
),
sale_attr as
(
select
sku_id,
collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs
from ${APP}.ods_sku_sale_attr_value
where dt='$do_date'
group by sku_id
)
insert overwrite table ${APP}.dim_sku_info partition(dt='$do_date')
select
sku.id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.is_sale,
sku.spu_id,
spu.spu_name,
sku.category3_id,
c3.name,
c3.category2_id,
c2.name,
c2.category1_id,
c1.name,
sku.tm_id,
tm.tm_name,
attr.attrs,
sale_attr.sale_attrs,
sku.create_time
from sku
left join spu on sku.spu_id=spu.id
left join c3 on sku.category3_id=c3.id
left join c2 on c3.category2_id=c2.id
left join c1 on c2.category1_id=c1.id
left join tm on sku.tm_id=tm.id
left join attr on sku.id=attr.sku_id
left join sale_attr on sku.id=sale_attr.sku_id;
"
dim_base_province="
insert overwrite table ${APP}.dim_base_province
select
bp.id,
bp.name,
bp.area_code,
bp.iso_code,
bp.iso_3166_2,
bp.region_id,
br.region_name
from ${APP}.ods_base_province bp
join ${APP}.ods_base_region br on bp.region_id = br.id;
"
dim_coupon_info="
insert overwrite table ${APP}.dim_coupon_info partition(dt='$do_date')
select
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
limit_num,
taken_count,
start_time,
end_time,
operate_time,
expire_time
from ${APP}.ods_coupon_info
where dt='$do_date';
"
dim_activity_rule_info="
insert overwrite table ${APP}.dim_activity_rule_info partition(dt='$do_date')
select
ar.id,
ar.activity_id,
ai.activity_name,
ar.activity_type,
ai.start_time,
ai.end_time,
ai.create_time,
ar.condition_amount,
ar.condition_num,
ar.benefit_amount,
ar.benefit_discount,
ar.benefit_level
from
(
select
id,
activity_id,
activity_type,
condition_amount,
condition_num,
benefit_amount,
benefit_discount,
benefit_level
from ${APP}.ods_activity_rule
where dt='$do_date'
)ar
left join
(
select
id,
activity_name,
start_time,
end_time,
create_time
from ${APP}.ods_activity_info
where dt='$do_date'
)ai
on ar.activity_id=ai.id;
"
case $1 in
"dim_user_info"){
hive -e "$dim_user_info"
};;
"dim_sku_info"){
hive -e "$dim_sku_info"
};;
"dim_base_province"){
hive -e "$dim_base_province"
};;
"dim_coupon_info"){
hive -e "$dim_coupon_info"
};;
"dim_activity_rule_info"){
hive -e "$dim_activity_rule_info"
};;
"all"){
hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info$dim_base_province"
};;
esac
3 脚本使用
chmod +x ods_to_dim_db_init.sh
ods_to_dim_db_init.sh all 2020-06-14
注意:该脚本不包含时间维度表的装载,时间维度表需手动装载数据。
查看数据是否导入成功
三 DIM层每日数据装载脚本
1 新建文件
在/home/hzy/bin目录下创建脚本ods_to_dim_db.sh
vim ods_to_dim_db.sh
2 脚本内容
在脚本中填写如下内容
#!/bin/bash
APP=gmall
# 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
do_date=$2
else
do_date=`date -d "-1 day" +%F`
fi
dim_user_info="
set hive.exec.dynamic.partition.mode=nonstrict;
with
tmp as
(
select
old.id old_id,
old.login_name old_login_name,
old.nick_name old_nick_name,
old.name old_name,
old.phone_num old_phone_num,
old.email old_email,
old.user_level old_user_level,
old.birthday old_birthday,
old.gender old_gender,
old.create_time old_create_time,
old.operate_time old_operate_time,
old.start_date old_start_date,
old.end_date old_end_date,
new.id new_id,
new.login_name new_login_name,
new.nick_name new_nick_name,
new.name new_name,
new.phone_num new_phone_num,
new.email new_email,
new.user_level new_user_level,
new.birthday new_birthday,
new.gender new_gender,
new.create_time new_create_time,
new.operate_time new_operate_time,
new.start_date new_start_date,
new.end_date new_end_date
from
(
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date
from ${APP}.dim_user_info
where dt='9999-99-99'
and start_date<'$do_date'
)old
full outer join
(
select
id,
login_name,
nick_name,
md5(nick_name) nick_name,
md5(name) name,
md5(phone_num) phone_num,
user_level,
birthday,
gender,
create_time,
operate_time,
'$do_date' start_date,
'9999-99-99' end_date
from ${APP}.ods_user_info
where dt='$do_date'
)new
on old.id=new.id
)
insert overwrite table ${APP}.dim_user_info partition(dt)
select
nvl(new_id,old_id),
nvl(new_login_name,old_login_name),
nvl(new_nick_name,old_nick_name),
nvl(new_name,old_name),
nvl(new_phone_num,old_phone_num),
nvl(new_email,old_email),
nvl(new_user_level,old_user_level),
nvl(new_birthday,old_birthday),
nvl(new_gender,old_gender),
nvl(new_create_time,old_create_time),
nvl(new_operate_time,old_operate_time),
nvl(new_start_date,old_start_date),
nvl(new_end_date,old_end_date),
nvl(new_end_date,old_end_date) dt
from tmp
union all
select
old_id,
old_login_name,
old_nick_name,
old_name,
old_phone_num,
old_email,
old_user_level,
old_birthday,
old_gender,
old_create_time,
old_operate_time,
old_start_date,
cast(date_add('$do_date',-1) as string),
cast(date_add('$do_date',-1) as string) dt
from tmp
where new_id is not null and old_id is not null;
"
dim_sku_info="
with
sku as
(
select
id,
price,
sku_name,
sku_desc,
weight,
is_sale,
spu_id,
category3_id,
tm_id,
create_time
from ${APP}.ods_sku_info
where dt='$do_date'
),
spu as
(
select
id,
spu_name
from ${APP}.ods_spu_info
where dt='$do_date'
),
c3 as
(
select
id,
name,
category2_id
from ${APP}.ods_base_category3
where dt='$do_date'
),
c2 as
(
select
id,
name,
category1_id
from ${APP}.ods_base_category2
where dt='$do_date'
),
c1 as
(
select
id,
name
from ${APP}.ods_base_category1
where dt='$do_date'
),
tm as
(
select
id,
tm_name
from ${APP}.ods_base_trademark
where dt='$do_date'
),
attr as
(
select
sku_id,
collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
from ${APP}.ods_sku_attr_value
where dt='$do_date'
group by sku_id
),
sale_attr as
(
select
sku_id,
collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs
from ${APP}.ods_sku_sale_attr_value
where dt='$do_date'
group by sku_id
)
insert overwrite table ${APP}.dim_sku_info partition(dt='$do_date')
select
sku.id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.is_sale,
sku.spu_id,
spu.spu_name,
sku.category3_id,
c3.name,
c3.category2_id,
c2.name,
c2.category1_id,
c1.name,
sku.tm_id,
tm.tm_name,
attr.attrs,
sale_attr.sale_attrs,
sku.create_time
from sku
left join spu on sku.spu_id=spu.id
left join c3 on sku.category3_id=c3.id
left join c2 on c3.category2_id=c2.id
left join c1 on c2.category1_id=c1.id
left join tm on sku.tm_id=tm.id
left join attr on sku.id=attr.sku_id
left join sale_attr on sku.id=sale_attr.sku_id;
"
dim_base_province="
insert overwrite table ${APP}.dim_base_province
select
bp.id,
bp.name,
bp.area_code,
bp.iso_code,
bp.iso_3166_2,
bp.region_id,
bp.name
from ${APP}.ods_base_province bp
join ${APP}.ods_base_region br on bp.region_id = br.id;
"
dim_coupon_info="
insert overwrite table ${APP}.dim_coupon_info partition(dt='$do_date')
select
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
limit_num,
taken_count,
start_time,
end_time,
operate_time,
expire_time
from ${APP}.ods_coupon_info
where dt='$do_date';
"
dim_activity_rule_info="
insert overwrite table ${APP}.dim_activity_rule_info partition(dt='$do_date')
select
ar.id,
ar.activity_id,
ai.activity_name,
ar.activity_type,
ai.start_time,
ai.end_time,
ai.create_time,
ar.condition_amount,
ar.condition_num,
ar.benefit_amount,
ar.benefit_discount,
ar.benefit_level
from
(
select
id,
activity_id,
activity_type,
condition_amount,
condition_num,
benefit_amount,
benefit_discount,
benefit_level
from ${APP}.ods_activity_rule
where dt='$do_date'
)ar
left join
(
select
id,
activity_name,
start_time,
end_time,
create_time
from ${APP}.ods_activity_info
where dt='$do_date'
)ai
on ar.activity_id=ai.id;
"
case $1 in
"dim_user_info"){
hive -e "$dim_user_info"
};;
"dim_sku_info"){
hive -e "$dim_sku_info"
};;
"dim_base_province"){
hive -e "$dim_base_province"
};;
"dim_coupon_info"){
hive -e "$dim_coupon_info"
};;
"dim_activity_rule_info"){
hive -e "$dim_activity_rule_info"
};;
"first"){
hive -e "$dim_sku_info$dim_base_province$dim_coupon_info$dim_activity_rule_info"
};;
"all"){
hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info"
};;
esac
3 脚本使用
chmod +x ods_to_dim_db.sh
# 执行脚本
ods_to_dim_db.sh all 2020-06-14
查看数据是否导入成功