Crea un registro de cambios con JDBC con la función de marca de tiempo de confirmación de Spanner

En este documento, se describe cómo usar la función de marca de tiempo de confirmación para realizar un seguimiento de la fecha y hora en que se realizan los cambios en los registros de tu base de datos. Cuando tienes una base de datos grande con muchas transacciones que cambian los registros y deseas capturar los cambios realizados en los conjuntos de datos, la función marca de tiempo de confirmación simplifica esta tarea.

A través de una serie de muestras de código, en este documento, se muestra cómo volver a escribir el lenguaje de manipulación de datos (DML) existente para crear un registro de cambios. En el documento, se extiende Usa las marcas de tiempo de confirmación de Cloud Spanner para crear un registro de cambios con Go mediante la demostración de los métodos con Java y JDBC. La lógica se representa en SQL en lugar de como objetos de cliente de Spanner.

Cómo funcionan las tablas

Supongamos que tienes una tabla de hechos llamada msgs y una tabla del historial llamada msg_history. Cada vez que se modifica la tabla de hechos, debes guardar un registro en simultáneo en la tabla msg_history. Puedes usar el contenido de la tabla del historial más adelante para otros usos, por ejemplo, para auditorías o como fuente de eventos.

La tabla msgs contiene los hechos, representados por un ID de transacción (id) y el mensaje (msg). La declaración del lenguaje de definición de datos (DDL) que usas para crear la tabla se ve de la siguiente manera:

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

La tabla msg_history contiene el historial de transacciones. El siguiente DDL crea la tabla del historial. La columna ts almacena la marca de tiempo de confirmación.

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

Los ejemplos

En lugar de escribir solo en la tabla de hechos, debes realizar la operación en la tabla del historial en la misma transacción. El controlador JDBC de Spanner admite la declaración del inicio y la finalización de una transacción, que es una operación JDBC estándar.

Paso 1: Vuelve a escribir las operaciones de inserción

El primer paso es volver a escribir las operaciones insert de la siguiente forma:

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

  • Cambia las inserciones para que se vean de la siguiente manera:

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

Paso 2: Vuelve a escribir las operaciones de actualización

A continuación, vuelve a escribir las operaciones update de la siguiente forma:

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

  • Cambia las actualizaciones para que se vean de la siguiente manera:

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

Paso 3: Vuelve a escribir las operaciones de eliminación

Por último, vuelve a escribir las operaciones delete de la siguiente forma:

delete from msgs where id = 1

  • Cambia las eliminaciones para que se vean de la siguiente manera:

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

Usa la tabla del historial

  • Accede al historial de cambios de la siguiente manera:

    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

    Los resultados son similares a los siguientes:

    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

¿Qué sigue?