使用 JDBC 和 Spanner 修訂時間戳記功能建立變更記錄

本文將說明如何使用提交時間戳記功能，追蹤資料庫記錄的變更日期和時間。如果您有大型資料庫，其中包含許多變更記錄的交易，且您想擷取對資料集所做的變更，則可以使用提交時間戳記功能簡化這項工作。

本文件將透過一系列程式碼範例，說明如何重寫現有的資料操縱語言 (DML)，以建立變更記錄。本文件延伸自「使用 Cloud Spanner 修訂時間戳記，利用 Go 建立變更記錄」，示範使用 Java 和 JDBC 的方法。邏輯以 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 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 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 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

後續步驟