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
    

次のステップ