Criar um registro de alterações com JDBC usando o recurso de carimbo de data/hora de confirmação do Spanner

Neste documento, descrevemos como usar o recurso de carimbo de data/hora de confirmação para rastrear a data e a hora em que as alterações são feitas nos registros do banco de dados. Quando você tem um grande banco de dados com muitas transações que alteram registros e quer capturar alterações feitas nos conjuntos de dados, o recurso de carimbo de data/hora de confirmação simplifica essa tarefa.

Neste documento, usamos uma série de exemplo de código para mostrar como reescrever a linguagem de manipulação de dados (DML) atual para criar um registro de alterações. No documento, abordamos Como usar carimbos de data/hora de confirmação do Cloud Spanner para criar um registro de alterações com Go, demonstrando os métodos usando Java e JDBC. A lógica é representada no SQL, e não como objetos de cliente do Spanner.

Como as tabelas funcionam

Suponha que você tenha uma tabela de fatos denominada msgs e uma tabela de histórico denominada msg_history. Sempre que a tabela de fatos é modificada, você quer salvar um registro simultaneamente na tabela msg_history. É possível usar o conteúdo da tabela de histórico posteriormente para outros usos, por exemplo, para auditoria ou como uma origem de evento.

A tabela msgs contém os fatos, representados por um código da transação (id) e a mensagem (msg). A instrução de linguagem de definição de dados (DDL, na sigla em inglês) que você usa para criar a tabela é semelhante a esta:

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

A tabela msg_history contém o histórico de transações. A DDL a seguir cria a tabela de histórico. A coluna ts armazena o carimbo de data/hora de confirmação.

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

Os exemplos

Em vez de gravar apenas na tabela de fatos, você precisa realizar a operação na tabela de histórico na mesma transação. O driver JDBC do Spanner aceita a declaração do início e do fim de uma transação, que é uma operação JDBC padrão.

Etapa 1: reescrever as operações de inserção

A primeira etapa é reescrever as operações insert a partir do seguinte formulário:

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

  • Altere as inserções para que fiquem assim:

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

    Este é o código 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();

Etapa 2: reescrever as operações de atualização

Em seguida, reescreva as operações update usando o formulário a seguir:

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

  • Altere as atualizações para que fiquem assim:

    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;

    Este é o código 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();

Etapa 3: reescrever as operações de exclusão

Por fim, reescreva as operações delete usando o formulário a seguir:

delete from msgs where id = 1

  • Altere as exclusões para que fiquem assim:

    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;

    Este é o código 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();

Como usar a tabela de histórico

  • Acesse o histórico de alterações da seguinte maneira:

    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

    Os resultados são semelhantes aos seguintes:

    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

A seguir