Änderungslog mit JDBC mit dem Commit-Zeitstempelfeature von Cloud Spanner erstellen

In diesem Dokument wird beschrieben, wie Sie mit dem Commit-Zeitstempelfeature das Datum und die Uhrzeit erfassen, wenn Änderungen an Ihren Datenbankeinträgen vorgenommen werden. Wenn Sie eine große Datenbank mit vielen Transaktionen haben, die Datensätze ändern, und Änderungen an den Datasets erfassen möchten, vereinfacht das Commit-Zeitstempelfeature diese Aufgabe.

In diesem Dokument wird anhand einer Reihe von Codebeispielen gezeigt, wie Sie eine vorhandene Datenbearbeitungssprache (Data Manipulation Language, DML) neu erstellen, um ein Änderungslog zu erstellen. In diesem Dokument wird die Verwendung von Commit-Zeitstempeln von Cloud Spanner zum Erstellen eines Änderungslogs mit Go durch Java und JDBC erweitert. Die Logik wird in SQL und nicht als Spanner-Clientobjekte dargestellt.

So funktionieren die Tabellen

Angenommen, Sie haben eine Faktentabelle namens msgs und eine Verlaufstabelle namens msg_history. Jedes Mal, wenn die Faktentabelle geändert wird, sollten Sie gleichzeitig einen Datensatz in der Tabelle msg_history speichern. Sie können den Inhalt der Verlaufstabelle später für andere Zwecke verwenden, z. B. zum Auditing oder als Ereignisquelle.

Die Tabelle msgs enthält die Fakten, dargestellt durch eine Transaktions-ID (id) und die Nachricht (msg). Die DDL-Anweisung, mit der Sie die Tabelle erstellen, sieht so aus:

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

Die Tabelle msg_history enthält den Transaktionsverlauf. Die folgende DDL erstellt die Verlaufstabelle. In der Spalte ts wird der Commit-Zeitstempel gespeichert.

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

Beispiele

Anstatt nur in die Faktentabelle zu schreiben, müssen Sie den Vorgang in derselben Transaktion in der Verlaufstabelle ausführen. Der JDBC-Treiber von Spanner unterstützt die Deklaration vom Beginn und Ende einer Transaktion, bei der es sich um einen Standard-JDBC-Vorgang handelt.

Schritt 1: insert-Vorgänge umschreiben

Der erste Schritt besteht darin, die insert-Vorgänge im folgenden Format umzuschreiben:

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

  • Ändern Sie die insert-Vorgänge so:

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

    Hier ist der Java-Code:

    // 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();

Schritt 2: update-Vorgänge umschreiben

Als Nächstes schreiben Sie die update-Vorgänge im folgenden Formular um:

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

  • Ändern Sie die update-Vorgänge so:

    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;

    Hier ist der Java-Code:

    // 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();

Schritt 3: delete-Vorgänge umschreiben

Abschließend schreiben Sie die delete-Vorgänge im folgenden Formular um:

delete from msgs where id = 1

  • Ändern Sie die delete-Vorgänge so:

    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;

    Hier ist der Java-Code:

    // 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();

Verlaufstabelle verwenden

  • So greifen Sie auf den Änderungsverlauf zu:

    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

    Die Ergebnisse sehen in etwa so aus:

    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

Nächste Schritte