Spanner commit タイムスタンプ機能を使用して JDBC で変更ログを作成する

このドキュメントでは、commit タイムスタンプ機能を使用して、データベース レコードに変更が加えられた日時を追跡する方法について説明します。レコードを変更するトランザクションが多数ある大規模なデータベースで、データセットに加えられた変更をキャプチャする場合は、commit タイムスタンプ機能を使用して処理を簡略化できます。

このドキュメントでは、一連のコードサンプルを使用して、既存のデータ操作言語（DML）を書き換えて変更ログを作成する方法について説明します。ここでは Java と JDBC を使用する方法を示しますが、この内容は Cloud Spanner commit タイムスタンプを使用して Go で変更ログを作成する場合にも応用できます。ロジックは、Spanner クライアント オブジェクトではなく SQL で表されます。

テーブルの仕組み

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 には commit タイムスタンプが格納されます。

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

次のステップ