Apache Flink 漫谈系列(11) - Temporal Table JOIN

一、什么是Temporal Table

在《Apache Flink 漫谈系列 - JOIN LATERAL》中提到了Temporal Table JOIN，本篇就向大家详细介绍什么是Temporal Table JOIN。

在ANSI-SQL 2011 中提出了Temporal 的概念，Oracle，SQLServer，DB2等大的数据库厂商也先后实现了这个标准。Temporal Table记录了历史上任何时间点所有的数据改动，Temporal Table的工作流程如下：

上图示意Temporal Table具有普通table的特性，有具体独特的DDL/DML/QUERY语法，时间是其核心属性。历史意味着时间，意味着快照Snapshot。

二、ANSI-SQL 2011 Temporal Table示例

我们以一个DDL和一套DML示例说明Temporal Table的原理，DDL定义PK是可选的，下面的示例我们以不定义PK的为例进行说明：

1. DDL 示例

CREATE TABLE Emp 

ENo INTEGER, 

Sys_Start TIMESTAMP(12) GENERATED 

ALWAYS AS ROW Start, 

Sys_end TIMESTAMP(12) GENERATED 

ALWAYS AS ROW END, 

EName VARCHAR(30), 

PERIOD FOR SYSTEM_TIME (Sys_Start,Sys_end) 

) WITH SYSTEM VERSIONING 

2. DML 示例

(1) INSERT

INSERT INTO Emp (ENo, EName) VALUES (22217, 'Joe') 

说明: 其中Sys_Start和Sys_End是数据库系统默认填充的。

(2) UPDATE

UPDATE Emp SET EName = 'Tom' WHERE ENo = 22217 

说明: 假设是在 2012-02-03 10:00:00 执行的UPDATE，执行之后上一个值"Joe"的Sys_End值由9999-12-31 23:59:59 变成了 2012-02-03 10:00:00, 也就是下一个值"Tom"生效的开始时间。可见我们执行的是UPDATE但是数据库里面会存在两条数据，数据值和有效期不同，也就是版本不同。

(3) DELETE (假设执行DELETE之前的表内容如下)

DELETE FROM Emp WHERE ENo = 22217 

说明: 假设我们是在2012-06-01 00:00:00执行的DELETE，则Sys_End值由9999-12-31 23:59:59 变成了 2012-06-01 00:00:00, 也就是在执行DELETE时候没有真正的删除符合条件的行，而是系统将符合条件的行的Sys_end修改为执行DELETE的操作时间。标识数据的有效期到DELETE执行那一刻为止。

(4) SELECT

SELECT ENo,EName,Sys_Start,Sys_End FROM Emp 

FOR SYSTEM_TIME AS OF TIMESTAMP '2011-01-02 00:00:00' 

说明: 这个查询会返回所有Sys_Start <= 2011-01-02 00:00:00 并且 Sys_end > 2011-01-02 00:00:00 的记录。

三、SQLServer Temporal Table 示例

1. DDL

CREATE TABLE Department 

( 

DeptID int NOT NULL PRIMARY KEY CLUSTERED 

, DeptName varchar(50) NOT NULL 

, ManagerID INT NULL 

, ParentDeptID int NULL 

, SysStartTime datetime2 GENERATED ALWAYS AS ROW Start NOT NULL 

, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) 

) 

WITH (SYSTEM_VERSIONING = ON); 

执行上面的语句，在数据库会创建当前表和历史表，如下图：

Department 显示是有版本控制的，历史表是默认的名字，我也可以指定名字如：SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)。

2. DML

(1) INSERT - 插入列不包含SysStartTime和SysEndTime列

INSERT INTO [dbo].[Department] ([DeptID] ,[DeptName] ,[ManagerID] ,[ParentDeptID]) 

VALUES(10, 'Marketing', 101, 1); 

VALUES(10, 'Marketing', 101, 1);

执行之后我们分别查询当前表和历史表，如下图：