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

Questo documento descrive come utilizzare funzionalità timestamp di commit per monitorare la data e l'ora in cui vengono apportate modifiche ai record del database. Quando hai un grande database con molte transazioni che modificano i record e quando vuoi acquisire le modifiche apportate ai set di dati, la funzionalità di timestamp semplifica questa attività.

Attraverso una serie di esempi di codice, questo documento mostra come riscrivere il linguaggio di manipolazione dei dati (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 piuttosto che come oggetti client Spanner.

Come funzionano le tabelle

Supponi di avere una tabella delle informazioni denominata msgs e una tabella della cronologia denominata msg_history. Ogni volta che la tabella delle informazioni viene modificata, vuoi salva un record nella tabella msg_history. Puoi utilizzare i contenuti per altri utilizzi, ad esempio per controlli o come eventi sorgente.

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. Driver JDBC di Spanner supporta la dichiarazione dell'inizio e della fine di una transazione, che è uno standard Operazione JDBC.

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 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

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

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