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

Questo documento descrive come utilizzare la funzionalità di timestamp del commit per monitorare la data e l'ora in cui vengono apportate modifiche ai record del database. Quando hai 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 del commit semplifica questa operazione.

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

Come funzionano le tabelle

Supponiamo 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 i controlli o come origine eventi.

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

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

Invece di scrivere solo nella tabella dei fatti, devi eseguire l'operazione nella 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 nel seguente formato:

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

  • Modifica gli insert 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

Poi riscrivi le operazioni update nel seguente formato:

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 nel seguente modulo:

delete from msgs where id = 1

  • Modifica le eliminazioni 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());
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();

Utilizzare la tabella della cronologia

  • Per accedere alla cronologia delle modifiche:

    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