Creare un log delle modifiche con JDBC utilizzando la funzionalità di timestamp commit di Spanner

Questo documento descrive come utilizzare la funzionalità di timestamp del commit per tenere traccia della data e dell'ora in cui vengono apportate modifiche ai record del database. Quando disponi di un database di grandi dimensioni con molte transazioni che modificano i record e vuoi acquisire le modifiche apportate ai set di dati, la funzionalità del timestamp di commit semplifica questa attività.

Attraverso una serie di esempi di codice, questo documento mostra come riscrivere il data manipulation language (DML) esistente per creare un log delle modifiche. Il documento estende l'utilizzo dei timestamp di commit di Cloud Spanner per creare un log delle modifiche con Go, dimostrando i metodi mediante Java e JDBC. La logica è rappresentata in SQL anziché come oggetti client Spanner.

Come funzionano le tabelle

Supponi di avere una tabella dei fatti denominata msgs e una tabella della cronologia denominata msg_history. Ogni volta che la tabella dei fatti viene modificata, vuoi salvare contemporaneamente un record nella tabella msg_history. Puoi utilizzare i contenuti della tabella della cronologia in un secondo momento per altri utilizzi, ad esempio per il controllo o come origine evento.

La tabella msgs contiene i fatti, rappresentati da un ID transazione (id) e dal messaggio (msg). L'istruzione DDL (Data Definition Language) che utilizzi per creare la tabella ha il seguente aspetto:

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

La tabella msg_history contiene la cronologia delle transazioni. Il seguente DDL crea la tabella della cronologia. La colonna ts memorizza il timestamp del commit.

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

Gli esempi

Anziché scrivere solo nella tabella dei fatti, devi eseguire l'operazione sulla tabella della cronologia nella stessa transazione. Il driver JDBC di Spanner supporta la dichiarazione dell'inizio e della fine di una transazione, che è un'operazione JDBC standard.

Passaggio 1: riscrivi le operazioni di inserimento

Il primo passaggio consiste nel riscrivere le operazioni insert dal formato seguente:

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

  • Modifica gli inserti in modo che abbiano il seguente aspetto:

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

    Ecco il codice 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();

Passaggio 2: riscrivi le operazioni di aggiornamento

A questo punto, riscrivi le operazioni update dal formato seguente:

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

  • Modifica gli aggiornamenti in modo che abbiano il seguente aspetto:

    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;

    Ecco il codice 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();

Passaggio 3: riscrivi le operazioni di eliminazione

Infine, riscrivi le operazioni delete dal formato seguente:

delete from msgs where id = 1

  • Modifica le eliminazioni nel seguente modo:

    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;

    Ecco il codice 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();

Utilizzo della tabella della cronologia

  • Accedi alla cronologia delle modifiche nel seguente modo:

    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

    I risultati sono simili ai seguenti:

    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

Passaggi successivi