数据拉链demo

-- 生成演示数据
CALL `Create table tmp`();

-- 初始化拉链表演示
CALL `Zipper table initialization`('20171218');

-- 更新拉链表
CALL `Update zipper table`('20190822');

-- 更新拉链表
CALL `Update zipper table`('20190823');

-- 更新拉链表
CALL `Update zipper table`('20190824');

-- 查询某ID历史数据
SELECT * FROM `拉链表` WHERE `id`='2';
SELECT * FROM `拉链表` WHERE `id`='4';

-- 查询某日时间快照数据


SELECT * FROM `拉链表` WHERE `开始时间`<='20190101'AND `结束时间` >'20190101' ORDER BY `id`;
#反映2019-08-22历史数据：
SELECT * FROM `拉链表` WHERE `开始时间`<='20190822'AND `结束时间` >'20190822' ORDER BY `id`;
#反映2019-08-22历史数据：
SELECT * FROM `拉链表` WHERE `开始时间`<='20190823'AND `结束时间` >'20190823' ORDER BY `id`;

#反应更新数据
SELECT * FROM `拉链表` WHERE `开始时间`<>`更新批次`;

SELECT * FROM `拉链表` WHERE `结束时间`<>`更新批次`;

SELECT * FROM `拉链表` WHERE `结束时间`=`更新批次`;

SELECT * FROM `拉链表` WHERE `开始时间`=`更新批次`;

-- 创建演示数据存储过程

DROP PROCEDURE IF EXISTS `Create table tmp`;
CREATE PROCEDURE `Create table tmp`()
BEGIN
-- ----------------------------
-- 创建demo表
-- ----------------------------
DROP TABLE IF EXISTS `tmp`;
CREATE TABLE `tmp`  (
  `id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `add` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- 写入demo数据
-- ----------------------------
INSERT INTO `tmp` VALUES (1, 'a', ' 是的');
INSERT INTO `tmp` VALUES (2, ' ', '号 的');
INSERT INTO `tmp` VALUES (3, NULL, NULL);
INSERT INTO `tmp` VALUES (4, ' aa a ', '   哈 哈,his都是  ');
INSERT INTO `tmp` VALUES (5, '    ', '~!@#$%^&*()_+|\":?><');
INSERT INTO `tmp` VALUES (6, 'date', '2018-08-09');
INSERT INTO `tmp` VALUES (7, 'date', '2018年8月9日');
INSERT INTO `tmp` VALUES (8, 'date', '2018/08/09');
INSERT INTO `tmp` VALUES (9, 'date', '09/08/2018');

END;

#mysql_upgrade -u root -p admin

-- 创建拉链表初始化存储过程
DROP PROCEDURE IF EXISTS `Zipper table initialization`;
CREATE PROCEDURE `Zipper table initialization`(IN `batch` VARCHAR(255))
BEGIN
-- ----------------------------
-- 数据初始化……
-- ----------------------------
DROP TABLE IF EXISTS `tmp0`;
CREATE TABLE `tmp0` AS(
SELECT *,
MD5(CONCAT_WS('',`name`,`add`)) AS `me_hash`
FROM `tmp`);

-- 创建增量表
DROP TABLE IF EXISTS `增量表`;
CREATE TABLE `增量表`(
`id` VARCHAR(255) COMMENT '数据唯一标识',
`name`VARCHAR(255) COMMENT '字段内容',
`add`VARCHAR(255) COMMENT '字段内容',
`ETL标记`VARCHAR(255) COMMENT'新增、删除、变更前、变更后'
);

-- 创建历史表
DROP TABLE IF EXISTS `历史表`;
CREATE TABLE `历史表`(
`id` VARCHAR(255) COMMENT '数据唯一标识',
`name`VARCHAR(255)COMMENT '字段内容',
`add`VARCHAR(255)COMMENT '字段内容',
`开始时间`VARCHAR(255)COMMENT'生效日期',
`结束时间`VARCHAR(255)COMMENT'失效日期',
`更新批次`VARCHAR(255)COMMENT'批次号',
`最新批次`VARCHAR(255)COMMENT'最新批次号'
);

-- 创建拉链表
DROP TABLE IF EXISTS `拉链表`;
CREATE TABLE `拉链表`(
`id`VARCHAR(255)COMMENT'数据唯一标识',
`name`VARCHAR(255)COMMENT'字段内容',
`add`VARCHAR(255)COMMENT'字段内容',
`开始时间`VARCHAR(255)COMMENT'生效日期',
`结束时间`VARCHAR(255)COMMENT'失效日期',
`更新批次`VARCHAR(255)COMMENT'批次号'
);

#写入全量数据
SET @temp_time=batch ;   -- NOW(); -- 执行时间
INSERT INTO `拉链表`(`id`,`name`,`add`,`开始时间`,`结束时间`,`更新批次`)
SELECT
`id`,
`name`,
`add`,
@temp_time AS `开始时间`,
'99991231' AS `结束时间`,
@temp_time AS `更新批次`
FROM
`tmp0`;

END;



-- 更新拉链表存储过程
DROP PROCEDURE IF EXISTS `Update zipper table`;
CREATE PROCEDURE `Update zipper table`(IN `batch` VARCHAR(255))
BEGIN
-- ----------------------------
-- 数据拉链测试
-- ----------------------------
SET @temp_time=batch;

DROP TABLE IF EXISTS `tmp_更新前`;-- 更新前的表
CREATE TABLE`tmp_更新前` AS(
-- SELECT * FROM `tmp0`;
SELECT 
`id`,
`name`,
`add`,
MD5(CONCAT_WS('',`name`,`add`)) AS `me_hash` FROM `拉链表` WHERE `结束时间`='99991231'
);

DROP TABLE IF EXISTS `tmp0`;
CREATE TABLE `tmp0` AS(
SELECT *,
MD5(CONCAT_WS('',`name`,`add`)) AS `me_hash`
FROM `tmp`);

DROP TABLE IF EXISTS `tmp_更新后`;-- 更新后的表
CREATE TABLE`tmp_更新后` AS(
SELECT * FROM `tmp0`
);



#清空增量数据
TRUNCATE `增量表`;

#加载增量数据-新增
INSERT INTO `增量表`
SELECT `t1`.`id`,`t1`.`name`,`t1`.`add`,'新增' AS `ETL标记` FROM
(SELECT * FROM `tmp_更新后`) `t1` -- 更新后的表
LEFT JOIN
(SELECT * FROM `tmp_更新前`) `t2` -- 更新前的表
ON `t1`.`id`=`t2`.`id` WHERE `t2`.`id` IS NULL;


#加载增量数据-删除
INSERT INTO `增量表`
SELECT `t1`.`id`,`t1`.`name`,`t1`.`add`,'删除' AS `ETL标记` FROM
(SELECT * FROM `tmp_更新前`) `t1`
LEFT JOIN
(SELECT * FROM `tmp_更新后`) `t2`
ON `t1`.`id`=`t2`.`id` WHERE `t2`.`id` IS NULL;

#加载增量数据-变更后
INSERT INTO `增量表`
SELECT `t1`.`id`,`t1`.`name`,`t1`.`add`,'变更后' AS `ETL标记` FROM
(SELECT * FROM `tmp_更新后`) `t1`
LEFT JOIN
(SELECT * FROM `tmp_更新前`) `t2`
ON `t1`.`id`=`t2`.`id` WHERE `t2`.`id` IS NOT NULL AND `t1`.`me_hash`<>`t2`.`me_hash`;


#加载增量数据-变更前
INSERT INTO `增量表`
SELECT `t1`.`id`,`t2`.`name`,`t2`.`add`,'变更前' AS `ETL标记` FROM
(SELECT * FROM `tmp_更新后`) `t1`
LEFT JOIN
(SELECT * FROM `tmp_更新前`) `t2`
ON `t1`.`id`=`t2`.`id` WHERE `t2`.`id` IS NOT NULL AND `t1`.`me_hash`<>`t2`.`me_hash`;


#1.重跑：删除已跑入数据
DELETE FROM `拉链表` WHERE `更新批次`= @temp_time;

#2.重跑：从历史表恢复数据
INSERT INTO `拉链表`(`id`,`name`,`add`,`开始时间`,`结束时间`,`更新批次`)
SELECT
`id`,
`name`,
`add`,
`开始时间`,
`结束时间`,
`更新批次`
FROM
`历史表` WHERE `最新批次`= @temp_time;

#3.重跑：删除已跑入的历史数据
DELETE FROM `历史表` WHERE `最新批次`= @temp_time;

#4.备份数据到历史表
INSERT INTO `历史表`(`id`,`name`,`add`,`开始时间`,`结束时间`,`更新批次`,`最新批次`)
SELECT
`id`,
`name`,
`add`,
`开始时间`,
`结束时间`,
`更新批次`,
@temp_time 
FROM 
`拉链表` AS `t`
WHERE 
`t`.`结束时间`='99991231' AND EXISTS(SELECT 1 FROM `增量表` AS `s` WHERE `t`.`id`=`s`.`id`);

#5.断链
UPDATE
`拉链表` AS `t`
SET
`t`.`结束时间`= @temp_time,
`t`.`更新批次`= @temp_time
WHERE
`结束时间`='99991231' AND EXISTS (SELECT 1 FROM `增量表` AS `s` WHERE `ETL标记` IN ('新增','删除','变更后') AND `t`.`id`=`s`.`id`);


#6.加链
INSERT INTO `拉链表`(`id`,`name`,`add`,`开始时间`,`结束时间`,`更新批次`)
SELECT
`id`,
`name`,
`add`,
@temp_time AS `开始时间`,
'99991231' AS `结束时间`,
@temp_time AS `更新批次`
FROM
`增量表` 
WHERE
`ETL标记` IN('变更后','新增');


#7.保持数据完整性
INSERT INTO `拉链表`(`id`,`name`,`add`,`开始时间`,`结束时间`,`更新批次`)
SELECT
`id`,
`name`,
`add`,
@temp_time `开始时间`,
@temp_time AS `结束时间`,
@temp_time AS `更新批次`
FROM
`增量表` AS `t` 
WHERE `ETL标记`='删除' AND NOT EXISTS (SELECT 1 FROM `增量表` AS `s` WHERE `t`.`id`=`s`.`id`);



END;