马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
本帖最后由 168主编 于 2020-2-29 14:55 编辑
- 拉链表介绍
- 适用场景
- 实现思路
- 拉链表示例
- 查询拉链表最新分区
- 查询最新有效数据
- 查询20180103的历史快照数据
- 准备数据
- 创建Hive表
- 添加分区
- 上传文件
- 查看数据
- 数据说明
- 生成拉链表
- 建立Hive表
- 初始化第一个分区
- 查看拉链表第一个分区
- 拉链表更新
- 添加20180104的数据
- 合并增量数据
- 查看拉链表的新分区
拉链表介绍
在数据分析中,有时会需要维护一些历史状态,比如订单状态变化、评分变化等,为了保存下来这些状态变化的路径,可以通过拉链表实现 适用场景
实现思路
拉链表示例
查询拉链表最新分区
SELECT * from tmp_order_detail t WHERE dt = '20180104'
查询最新有效数据
SELECT * from tmp_order_detail t WHERE dt = '20180104' AND t.end_date = '99991231'
| t.order_id | t.order_time | t.user_id | t.order_status | t.start_date | t.end_date | t.dt | 1 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 已完成 | 20180104 | 99991231 | 20180104 | 2 | C3 | 2018-01-02 17:15:01 | 5FECEB66FF | 待收货 | 20180104 | 99991231 | 20180104 | 3 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 已完成 | 20180104 | 99991231 | 20180104 | 查询20180103的历史快照数据
SELECT * from tmp_order_detail t WHERE dt = '20180104' AND t.start_date <='20180103' AND t.end_date >= '20180103'
| t.order_id | t.order_time | t.user_id | t.order_status | t.start_date | t.end_date | t.dt | 1 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待收货 | 20180103 | 20180103 | 20180104 | 2 | C3 | 2018-01-02 17:15:01 | 5FECEB66FF | 已支付 | 20180102 | 20180103 | 20180104 | 3 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 待收货 | 20180103 | 20180103 | 20180104 |
准备数据创建Hive表
[AppleScript] 纯文本查看 复制代码 CREATE EXTERNAL TABLE `tmp_order`(
`order_id` string COMMENT '订单ID',
`order_time` string COMMENT '下单时间',
`user_id` string COMMENT '用户ID',
`order_status` string COMMENT '订单状态')
PARTITIONED BY (
`dt` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION
'hdfs://ns1/tmp/order';
添加分区
[AppleScript] 纯文本查看 复制代码 ALTER TABLE tmp_order ADD IF NOT EXISTS partition(dt='20180101') LOCATION '20180101';
ALTER TABLE tmp_order ADD IF NOT EXISTS partition(dt='20180102') LOCATION '20180102';
ALTER TABLE tmp_order ADD IF NOT EXISTS partition(dt='20180103') LOCATION '20180103';
上传文件按以下方式组织HDFS上的数据 [AppleScript] 纯文本查看 复制代码 [mapred@gateway2 ~]$ hdfs dfs -cat /tmp/order/20180101/20180101.txt
052018-01-01 07:10:3186A08095B2待支付20180101
EF2018-01-01 18:41:00BB89D486A3已支付20180101
[mapred@gateway2 ~]$ hdfs dfs -cat /tmp/order/20180102/20180102.txt
052018-01-01 07:10:3186A08095B2已支付20180102
C32018-01-02 17:15:015FECEB66FF已支付20180102
[mapred@gateway2 ~]$ hdfs dfs -cat /tmp/order/20180103/20180103.txt
052018-01-01 07:10:3186A08095B2待收货20180103
EF2018-01-01 18:41:00BB89D486A3待收货20180103
查看数据
SELECT * from tmp_order t WHERE dt BETWEEN '20180101' AND '20180103'
| t.order_id | t.order_time | t.user_id | t.order_status | t.dt | 1 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待支付 | 20180101 | 2 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 已支付 | 20180101 | 3 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 已支付 | 20180102 | 4 | C3 | 2018-01-02 17:15:01 | 5FECEB66FF | 已支付 | 20180102 | 5 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待收货 | 20180103 | 6 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 待收货 | 20180103 |
数据说明
订单ID | 下单时间 | 用户ID | 订单状态 | 分区key | order_id | order_time | user_id | order_status | dt |
生成拉链表建立Hive表
[AppleScript] 纯文本查看 复制代码 CREATE EXTERNAL TABLE `tmp_order_detail`(
`order_id` string COMMENT '订单ID',
`order_time` string COMMENT '下单时间',
`user_id` string COMMENT '用户ID',
`order_status` string COMMENT '订单状态',
`start_date` string COMMENT '开始生效日期',
`end_date` string COMMENT '失效日期')
PARTITIONED BY (
`dt` string)
STORED AS PARQUET
LOCATION
'hdfs://ns1/tmp/order_detail';
初始化第一个分区加载原始表所有数据,以分区key字段dt为开始生效日期 按start_date倒序排序后,取上一条记录的前一天为当前记录的失效日期 如果没有更新的记录,记失效日期为默认值99991231
[AppleScript] 纯文本查看 复制代码 INSERT OVERWRITE TABLE tmp_order_detail PARTITION (dt='20180103')
SELECT
order_id,order_time,user_id,order_status,dt AS start_date,
regexp_replace (
split (
LAG (
date_add(
from_unixtime(
unix_timestamp(dt, 'yyyyMMdd')
),
- 1
),
1,
'9999-12-31 00:00:00'
) OVER (
PARTITION BY order_id
ORDER BY
dt DESC
),
' '
)[0], '-',
''
) AS end_date
FROM
tmp_order
WHERE dt BETWEEN '20180101' AND '20180103'
查看拉链表第一个分区
SELECT * from tmp_order_detail t WHERE dt='20180103'
| t.order_id | t.order_time | t.user_id | t.order_status | t.start_date | t.end_date | t.dt | 1 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待收货 | 20180103 | 99991231 | 20180103 | 2 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 已支付 | 20180102 | 20180102 | 20180103 | 3 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待支付 | 20180101 | 20180101 | 20180103 | 4 | C3 | 2018-01-02 17:15:01 | 5FECEB66FF | 已支付 | 20180102 | 99991231 | 20180103 | 5 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 待收货 | 20180103 | 99991231 | 20180103 | 6 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 已支付 | 20180101 | 20180102 | 20180103 |
此处用到了Hive窗口函数LAG,关于该函数的使用可以参考《LAG用法》 此处使用LEAD函数也可以,不过需要更改排序方式 拉链表更新添加20180104的数据
ALTER TABLE tmp_order ADD IF NOT EXISTS partition(dt='20180104') LOCATION '20180104';
[AppleScript] 纯文本查看 复制代码 [mapred@gateway2 ~]$ hdfs dfs -cat /tmp/order/20180104/20180104.txt
052018-01-01 07:10:3186A08095B2已完成20180104
C32018-01-02 17:15:015FECEB66FF待收货20180104
EF2018-01-01 18:41:00BB89D486A3已完成20180104
合并增量数据[AppleScript] 纯文本查看 复制代码 INSERT OVERWRITE TABLE tmp_order_detail PARTITION (dt='20180104')
SELECT
order_id,order_time,user_id,order_status,start_date,
regexp_replace (
split (
LAG (
date_add(
from_unixtime(
unix_timestamp(start_date, 'yyyyMMdd')
),
- 1
),
1,
'9999-12-31 00:00:00'
) OVER (
PARTITION BY order_id
ORDER BY
start_date DESC
),
' '
)[0], '-',
''
) AS end_date
FROM
(
SELECT
order_id,order_time,user_id,order_status,dt AS start_date
FROM
tmp_order
WHERE dt = '20180104'
UNION ALL
SELECT
order_id,order_time,user_id,order_status,start_date
FROM
tmp_order_detail
WHERE dt = '20180103'
) tmp
查看拉链表的新分区
SELECT * from tmp_order_detail t WHERE dt='20180104'
| t.order_id | t.order_time | t.user_id | t.order_status | t.start_date | t.end_date | t.dt | 1 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 已完成 | 20180104 | 99991231 | 20180104 | 2 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待收货 | 20180103 | 20180103 | 20180104 | 3 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 已支付 | 20180102 | 20180102 | 20180104 | 4 | 05 | 2018-01-01 07:10:31 | 86A08095B2 | 待支付 | 20180101 | 20180101 | 20180104 | 5 | C3 | 2018-01-02 17:15:01 | 5FECEB66FF | 待收货 | 20180104 | 99991231 | 20180104 | 6 | C3 | 2018-01-02 17:15:01 | 5FECEB66FF | 已支付 | 20180102 | 20180103 | 20180104 | 7 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 已完成 | 20180104 | 99991231 | 20180104 | 8 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 待收货 | 20180103 | 20180103 | 20180104 | 9 | EF | 2018-01-01 18:41:00 | BB89D486A3 | 已支付 | 20180101 | 20180102 | 20180104
|
|