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 disponi di 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 l'utilizzo dei timestamp di commit di Cloud Spanner per creare un log delle modifiche con Go dimostrando 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 il messaggio (msg). L'istruzione DDL (Data Definition Language) utilizzata per creare la tabella, ha questo 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

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

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

  • Cambia 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 utilizzando il 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, procedi 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