Crear un registro de cambios con JDBC mediante 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 hacer un seguimiento de la fecha y la hora en las que se hacen cambios en los registros de tu base de datos. Si tienes una base de datos grande con muchas transacciones que cambian registros y quieres registrar los cambios realizados en los conjuntos de datos, la función de marca de tiempo de confirmación simplifica esta tarea.

A través de una serie de ejemplos de código, este documento muestra cómo reescribir el lenguaje de manipulación de datos (DML) para crear un registro de cambios. En este documento se amplía la información del artículo Usar marcas de tiempo de confirmación de Cloud Spanner para crear un registro de cambios con Go. En él se muestran 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 de historial llamada msg_history. Cada vez que se modifique la tabla de hechos, quieres guardar simultáneamente un registro en la tabla msg_history. Puede usar el contenido de la tabla de historial más adelante para otros fines, como 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 en lenguaje de definición de datos (DDL) que se usa para crear la tabla es similar a la siguiente:

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 la 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 hacer la operación en la tabla de historial en la misma transacción. El controlador JDBC de Spanner admite la declaración del inicio y el final de una transacción, que es una operación JDBC estándar.

Paso 1: Reescribe las operaciones de inserción

El primer paso es reescribir las operaciones de insert del siguiente formulario:

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

  • Cambia las inserciones para que tengan el siguiente aspecto:

    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: Reescribe las operaciones de actualización

A continuación, reescribe las operaciones de update con el siguiente formato:

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

  • Cambia las actualizaciones para que tengan el siguiente aspecto:

    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: Reescribe las operaciones de eliminación

Por último, reescribe las operaciones delete del siguiente formulario:

delete from msgs where id = 1

  • Cambia las eliminaciones para que tengan el siguiente aspecto:

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

Usar la tabla del historial

  • Para acceder al historial de cambios, siga estos pasos:

    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 deberían ser 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

Siguientes pasos