使用 Spanner 提交时间戳功能通过 JDBC 创建更改日志

本文档介绍了如何使用提交时间戳功能来跟踪对数据库记录进行更改的日期和时间。如果您的大型数据库的许多事务都会更改记录，并且您需要捕获对数据集所做的更改，您可以使用提交时间戳功能简化此任务。

通过一系列代码示例，本文档介绍了如何重写现有数据操纵语言 (DML)，以创建更新日志。本文档使用 Java 和 JDBC 演示相关方法，扩展了使用 Cloud Spanner 提交时间戳来通过 Go 创建更新日志的内容。逻辑使用 SQL（而不是以 Spanner 客户端对象形式）表示。

表的工作原理

假设您有一个名为 msgs 的事实表和一个名为 msg_history 的记录表。每次修改事实表时，您都希望在 msg_history 表中并发保存一条记录。您可以稍后将记录表的内容用于其他用途，例如用于审核或用作事件来源。

msgs 表包含由事务 ID (id) 和消息 (msg) 表示的事实。您用于创建该表的数据定义语言 (DDL) 语句如下所示：

CREATE TABLE msgs (
    id INT64,
    msg STRING(MAX),
) PRIMARY KEY (id)

msg_history 表包含事务记录。以下 DDL 用于创建记录表。ts 列存储着提交时间戳。

CREATE TABLE msgs_history (
    id INT64,
    ts TIMESTAMP OPTIONS (allow_commit_timestamp=true),
    previous_msg STRING(MAX)
) PRIMARY KEY (ts, id)

示例

向事实表写入内容时，您还必须在同一事务中向记录表写入同样的内容。Spanner 的 JDBC 驱动程序支持声明事务的开始和结束，这是一个标准 JDBC 操作。

第 1 步：重写 insert 操作

第一步是按如下形式重写 insert 操作：

insert into msgs (id, msg) values (1, 'a real msg')

  • 更改插入内容，使其如下所示：

    insert into msgs_history (id, previous_msg, ts) values (1, Null, PENDING_COMMIT_TIMESTAMP());
insert into msgs (id, msg) values (1, 'a real msg');

    以下是 Java 代码：

    // insert into history
stmt.executeUpdate(
    "insert into msgs_history (id, previous_msg, ts)"
        + " values (1, Null, PENDING_COMMIT_TIMESTAMP())");
// insert into real table
stmt.executeUpdate("insert into msgs (id, msg)" + " values (1, 'a real msg')");
conn.commit();

第 2 步：重写 update 操作

接下来，您可以按如下形式重写 update 操作：

update msgs set msg = 'new message' where id = 1

  • 更改更新内容，使其如下所示：

    insert msgs_history (id, previous_msg, ts) values ((select id from msgs where id =1 ), (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP());
update msgs set msg = 'new message' where id = 1;

    以下是 Java 代码：

    // insert into historystmt.executeUpdate(
    "insert msgs_history (id, previous_msg, ts)"
        + " values  ((select id from msgs where id =1),"
        + " (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP())");
// update into fact table
stmt.executeUpdate("update msgs set msg = 'new message' where id = 1");
conn.commit();

第 3 步：重写 delete 操作

最后，您可以按如下形式重写 delete 操作：

delete from msgs where id = 1

  • 更改删除内容，使其如下所示：

    insert msgs_history (id, previous_msg, ts) values ((select id from msgs where id =1 ), (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP());
delete from msgs where id = 1;

    以下是 Java 代码：

    // insert into history
stmt.executeUpdate(
    "insert msgs_history (id, previous_msg, ts)"
        + " values  ((select id from msgs where id =1),"
        + " (select msg from msgs where id = 1), PENDING_COMMIT_TIMESTAMP()) ");
// delete from real table
stmt.executeUpdate("delete from msgs where id = 1");
conn.commit();

使用记录表

  • 使用如下命令访问更改历史记录：

    select id,msg,current_timestamp() as ts
from msgs where id = 1
union all
select id,previous_msg, ts as msg
from msgs_history where id = 1 order by ts desc

    结果类似于以下内容：

    id    msg             ts
1     new message     2020-02-07T07:44:10.24833726Z
1     a real msg      2020-01-14T10:07:20.137935Z
1                     2020-01-14T10:07:20.070374Z

后续步骤