impala实现拉链表

1.文档目的

        拉链表是针对数据仓库设计中表存储数据的方式而定义的,即是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。传统数据仓库一般采用拉链的方式保留主数据(例如客户信息)的变化数据,采用这种设计方式的主要原因是减少数据冗余。这个需求在Hadoop中主要是有以下两种实现方式选择:

  1. 每天保留一份全量的切片数据。Hadoop平台由于采用通用的硬件设备,因此存储空间的成本较低,因此建议采用时间切片的方式保留每天的主数据信息。当前数据单独存放在当前表中,历史数据存放在历史表中,并按时间分区
  2. 在Hadoop之上也可以实现拉链表。当前数据单独存放在当前表中(即下面要介绍的USER表),发生变化的历史数据存放在历史表中(即下面要介绍的USER_HIS表),每条数据按照start_dt和end_dt做拉链。

本文主要是使用Impala基于上面介绍的方案2来做实操讲解。我们知道HDFS是一个append-only的存储系统,所以Hive/Impala表都无法进行update操作。所以在拉链表有update操作时,需要改写SQL来实现,具体可以参考本文后面的SQL和脚本。以下我们先来看看拉链表的具体实现:

  1. 首先我们需要一份ODS层的用户全量表,用它来初始化,图中是‘2018-01-15’。在拉链表USER_HIS中创建开链分区‘9999-12-31’,并将‘2018-01-15’的USER表中的数据start_dt都设置为‘2018-01-15’,end_dt都设置为‘9999-12-31’并插入到USER_HIS的‘9999-12-31’分区中。
  2. 假设过了一天,到了‘2018-01-16’。这时最新的‘2018-01-16’的用户全量表已经insert overwrite到USER表中。这时我们首先在拉链表USER_HIS中创建闭链分区‘2018-01-16’,然后通过比较最新USER表和USER_HIS表的开链(分区为‘9999-12-31’)数据,找到变化数据,做成闭链(start_dt为‘2018-01-15’, end_dt为‘2018-01-16’)后插入到USER_HIS的闭链分区‘2018-01-16’中。
  3. 通过USER表,USER_HIS的‘2018-01-16’分区和‘9999-12-31’分区的数据,通过较为复杂的SQL将‘2018-01-16’那天没变的数据,新增的数据(start_dt需设为‘2018-01-16’),更新的数据(start_dt也需设为‘2018-01-16’)一起insert overwrite到拉链表USER_HIS的9999-12-31’中。
  4. 后面每天的操作基本相似。

2.拉链表设计

用户表USER,用于存储用户最新的全量信息

建表语句:

create table user(
  id bigint,
  username string,
  birthday timestamp
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS parquet;
INSERT INTO user values
(10001, 'fayson', '1989-08-28'),
(10002, 'zhangsan', '1979-07-28'),
(10003, 'lisi', '1980-06-18'),
(10004, 'wangwu', '1977-01-20');

2用户拉链表USER_HIS

建表语句:

create table user_his(
  id bigint,
  username string,
  birthday timestamp,
  start_dt timestamp
) partitioned by (end_dt string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS parquet;

 

我们在这里使用了分区表,主要是为了能够实现拉链数据的更新和删除。

3.使用上面的表创建USER和USER_HIS表,并初始化USER表数据。

3.拉链流程实现

1.首先在USER_HIS表中创建一个’9999-12-31’的分区用于存储所有用户开链数据

ALTER TABLE user_his ADD PARTITION (end_dt='9999-12-31');

首次USER_HIS表中无任何数据,通过USER表数据初始化拉链表USER_HIS表数据,插入所有用户的开链数据

INSERT overwrite TABLE user_his PARTITION (end_dt = '9999-12-31')
SELECT id,
       username,
       birthday,
       from_timestamp(adddate(now(), -3), 'yyyy-MM-dd')
FROM USER;

这里用三天前的日期方便演示,此时拉链表的数据如下:

所有用户数据为开链状态。

3.为了与拉链表对比用户数据的变更,这里把USER表的username修改为如下

INSERT overwrite TABLE USER
SELECT id,
       concat(username,'1'),
       birthday
FROM USER;

4.在拉链表上创建”2018-01-16”的分区

--ALTER TABLE user_his ADD PARTITION (end_dt= from_timestamp(now(), 'yyyy-MM-dd'));
ALTER TABLE user_his ADD PARTITION (end_dt= "2018-01-16");

5.将修改的USER表用户数据与USER_HIS表中开链数据比对,将可以闭链的数据插入”2018-01-16”分区

INSERT overwrite TABLE user_his PARTITION (end_dt = "2018-01-16")
SELECT b.id,
       b.username,
       b.birthday,
       b.start_dt
FROM USER a
LEFT JOIN user_his b ON a.id=b.id
WHERE b.end_dt = '9999-12-31'
  AND (a.username != b.username
       OR a.birthday != b.birthday);

执行完上述语句后可以看到之前开链的数据已闭链,但用户的开链信息还未更新

6.在用户表中新增一条用户信息,模拟用户表数据不存在拉链表的开链数据中

INSERT INTO user VALUES (10005, 'zhaoda', '1976-02-09');

7.更新拉链表USER_HIS的开链数据(包含已更新的用户、未更新用户和新增用户)

INSERT overwrite TABLE user_his PARTITION(end_dt = '9999-12-31')
SELECT a.id,
       a.username,
       a.birthday,
       b.end_dt AS start_dt
FROM USER a
LEFT JOIN user_his b ON a.id = b.id
WHERE b.end_dt = "2018-01-16"
union all
SELECT b.id,
       b.username,
       b.birthday,
       b.start_dt
FROM user_his b
WHERE NOT EXISTS
    (SELECT id
     FROM user_his c
     WHERE c.end_dt = "2018-01-16" and b.id = c.id)
  AND b.end_dt = '9999-12-31'
union ALL
SELECT a.id,
       a.username,
       a.birthday,
       "2018-01-16" AS start_dt
FROM USER a
WHERE NOT EXISTS
    (SELECT 1
     FROM user_his b
     WHERE end_dt = '9999-12-31'
       AND a.id = b.id);

8.模拟更新部分用户信息,验证拉链业务是否正常

 

用户最新开链数据:

 

USER表数据

INSERT INTO user values
(10001, 'fayson2', '1989-09-27'),
(10002, 'zhangsan2', '1979-07-28'),
(10003, 'lisi1', '1980-06-18'),
(10004, 'wangwu1', '1977-01-20'),
(10005, 'zhaoda', '1976-02-09');

 

创建USRE_HIS表“2018-01-17”分区

ALTER TABLE user_his ADD PARTITION (end_dt= "2018-01-17");

将用户的闭链数据插入到“2018-01-17”分区

INSERT overwrite TABLE user_his PARTITION (end_dt = "2018-01-17")
SELECT b.id,
       b.username,
       b.birthday,
       b.start_dt
FROM USER a
LEFT JOIN user_his b ON a.id=b.id
WHERE b.end_dt = '9999-12-31'
  AND (a.username != b.username
       OR a.birthday != b.birthday);

根据USER和USER_HIS中“2018-01-17”分区的闭链数据,更新所有用户开链数据:(含新增用户、闭链用户和开链用户)

INSERT overwrite TABLE user_his PARTITION(end_dt = '9999-12-31')
SELECT a.id,
       a.username,
       a.birthday,
       b.end_dt AS start_dt
FROM USER a
LEFT JOIN user_his b ON a.id = b.id
WHERE b.end_dt = "2018-01-17"
union all
SELECT b.id,
       b.username,
       b.birthday,
       b.start_dt
FROM user_his b
WHERE NOT EXISTS
    (SELECT id
     FROM user_his c
     WHERE c.end_dt = "2018-01-17" and b.id = c.id)
  AND b.end_dt = '9999-12-31'
union ALL
SELECT a.id,
       a.username,
       a.birthday,
       "2018-01-17" AS start_dt
FROM USER a
WHERE NOT EXISTS
    (SELECT 1
     FROM user_his b
     WHERE end_dt = '9999-12-31'
       AND a.id = b.id);

4.拉链表实现完整脚本

执行脚本的前置条件,拉链表已存在且已创建了开链分区,脚本中将分区替换为当前日期按照每天的一次的频率执行。

use test_db;
--创建当天闭链分区
ALTER TABLE user_his ADD PARTITION(end_dt= from_timestamp(now(), 'yyyy-MM-dd'));
--将闭链数据插入当天闭链分区中
INSERT overwrite TABLE user_his PARTITION(end_dt = from_timestamp(now(), 'yyyy-MM-dd'))
SELECT b.id,
       b.username,
       b.birthday,
       b.start_dt
FROM USER a
LEFT JOIN user_his b ON a.id=b.id
WHERE b.end_dt = '9999-12-31'
  AND (a.username != b.username
       OR a.birthday != b.birthday);
--更新拉链表数据开链数据(包含已更新的用户、未更新用户和新增用户)
INSERT overwrite TABLE user_his PARTITION(end_dt = '9999-12-31')
SELECT a.id,
       a.username,
       a.birthday,
       b.end_dt AS start_dt
FROM USER a
LEFT JOIN user_his b ON a.id = b.id
WHERE b.end_dt = from_timestamp(now(), 'yyyy-MM-dd')
union all
SELECT b.id,
       b.username,
       b.birthday,
       b.start_dt
FROM user_his b
WHERE NOT EXISTS
    (SELECT id
     FROM user_his c
     WHERE c.end_dt = from_timestamp(now(), 'yyyy-MM-dd') and b.id = c.id)
  AND b.end_dt = '9999-12-31'
union ALL
SELECT a.id,
       a.username,
       a.birthday,
       from_timestamp(now(), 'yyyy-MM-dd') AS start_dt
FROM USER a
WHERE NOT EXISTS
    (SELECT 1
     FROM user_his b
     WHERE end_dt = '9999-12-31'
       AND a.id = b.id);

 

版权声明:本文为weixin_37796929原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_37796929/article/details/99079931

智能推荐

Intellij IDEA 搭建Spring Boot项目(一)

Intellij IDEA 搭建Spring Boot项目 标签(空格分隔): SpringBoot JAVA后台 第一步 选择File –> New –> Project –>Spring Initialer –> 点击Next  第二步 自己修改 Group 和 Artif...

CentOS学习之路1-wget下载安装配置

参考1: https://blog.csdn.net/zhaoyanjun6/article/details/79108129 参考2: http://www.souvc.com/?p=1569 CentOS学习之路1-wget下载安装配置 1.wget的安装与基本使用 安装wget yum 安装软件 默认安装保存在/var/cache/yum ,用于所有用户使用。 帮助命令 基本用法 例子:下载...

深入浅出Spring的IOC容器,对Spring的IOC容器源码进行深入理解

文章目录 DispatcherServlet整体继承图 入口:DispatcherServlet.init() HttpServletBean.init() FrameworkServlet.initServletBean() 首先大家,去看Spring的源码入口,第一个就是DispatcherServlet DispatcherServlet整体继承图 入口:DispatcherServlet....

laravel框架的课堂知识点概总

1. MVC 1.1 概念理解 MVC全名是Model View Controller,是模型(model)-视图(view)-控制器(controller)的缩写,一种软件设计典范,用一种业务逻辑、数据、界面显示分离的方法组织代码,将业务逻辑聚集到一个部件里面,在改进和个性化定制界面及用户交互的同时,不需要重新编写业务逻辑 MVC 是一种使用 MVC(Model View Controller ...

Unity人物角色动画系统学习总结

使用动画系统控制人物行走、转向、翻墙、滑行、拾取木头 混合树用来混合多个动画 MatchTarget用来匹配翻墙贴合墙上的某一点,人物以此为支点翻墙跳跃 IK动画类似于MatchTarget,控制两只手上的两个点来指定手的旋转和位置,使得拾取木头时更逼真 创建AnimatorController: 首先创建一个混合树,然后双击 可以看到该混合树有五种状态机,分别是Idle、WalkForward、...

猜你喜欢

Composer 安装 ThinkPHP6 问题

Composer 安装 ThinkPHP6 问题 先说说问题 一.运行环境要求 二.配置 参考: ThinkPHP6.0完全开发手册 先说说问题 执行ThinkPHP6的安装命令 遇到问题汇总如下: 看提示是要更新版本,执行命令更新。 更新之后,再次安装ThinkPHP,之后遇到如下问题。 尝试了很多方法,依然不能解决。其中包括使用https://packagist.phpcomposer.com...

Spring Boot 整合JDBC

今天主要讲解一下SpringBoot如何整合JDBC,没啥理论好说的,直接上代码,看项目整体结构 看一下对应的pom.xml 定义User.java 定义数据源配置,这里使用druid,所以需要写一个配置类 上面指定druid的属性配置,和用户登录的账号信息以及对应的过滤规则: 下面定义数据访问接口和对应的实现: 数据访问层很简单,直接注入JdbcTemplate模板即可,下面再看对应的servi...

html鼠标悬停显示样式

1.显示小手:     在style中添加cursor:pointer 实现鼠标悬停变成小手样式     实例:         其他参数: cursor语法: cursor : auto | crosshair | default | hand | move | help | wait | tex...

Yupoo(又拍网)的系统架构

Yupoo!(又拍网) 是目前国内最大的图片服务提供商,整个网站构建于大量的开源软件之上。以下为其使用到的开源软件信息: 操作系统:CentOS、MacOSX、Ubuntu 服务器:Apache、Nginx、Squid 数据库:MySQLmochiweb、MySQLdb 服务器监控:Cacti、Nagios、 开发语言:PHP、Python、Erlang、Java、Lua 分布式计算:Hadoop...

创建一个Servlet项目流程(入门)

版本 IDEA 2020.2 JDK1.8 apache-tomcat-9.0.36 项目流程 一、IDEA中新建JaveEE项目 项目起名,选择项目存放地址,点击finish创建成功 进入项目后,右键选择项目,选择add Framework Support 选择Web Application,点击OK 此时项目文件夹 在WEB-INF下创建两个目录classes和lib 按ctrl+alt+sh...