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