Esta entrada es específica para bases de datos MariaDB, y está referida a un sistema de tablas temporales de versiones de datos, característica disponible desde la versión 10.3.4. El concepto, temporal data tables, no hay que confundirlo con las tablas temporales. Te lo explico!
Las tablas temporales de versiones de datos
Las tablas temporales son tablas que sólo están disponibles durante el tiempo que permanezca la sesión abierta. Es muy útil porque permite agilizar el trabajo durante la sesión de trabajo, y que se borra al concluir la sesión. Pero no es lo que queremos ver en esta entrada.
System-versioned tables
Las tablas temporales de versiones de datos son tablas que almacenan la historia de todos los cambios ocurridos en cualquier registro de dicha tabla: system-versioned tables. No afecta solo a los datos actuales, sino que permite conocer la evolución de un registro (con independencia de que los datos sean o no válidos en el tiempo).
El uso de este concepto está indicado para realizar análisis de datos en cualquier punto del tiempo, permite auditoría de cambios a lo largo del tiempo, y se puede utilizar para:
- Análisis forense y legal de requisitos de almacenamiento durante x años
- Análisis de datos
- Recuperación de datos en cierto momento del tiempo, lo que permite conocer incluso el estado de la tabla en dicho momento.
La sintáxis SQL para introducir esta característica fue incluida en la versión estandar SQL:2011.
El sistema de versiones de una tabla supone:
- Reconvertir todos los UPDATE en INSERTs
- Almacenar tanto los datos actuales cómo los históricos
- Los datos son válidos en su tiempo (desde que fueron insertados en la tabla)
Antes de seguir, vamos a ver algunos conceptos previos para diferenciar posibles conflictos:
Los datos temporales
No te trata de series temporales de datos, sino que se trata de conocer la evolución de cambios que ha tenido un registro. La validez o invalidez de cada caso depende del tiempo y la fecha (puede o no estar relacionado).
Tampoco significa que los datos sean temporales, sino que se va generando la información temporal confirma se van ejecutando sentencias
UPDATE table SET ….
Y así es como se crean las tablas temporales de datos.
Creación de tablas con sistema de versiones, system-versioned tables
La sintaxis larga incluye la creación de las dos columnas necesarias:
CREATE TABLE t1 (
x INT,
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
Aunque también es posible utilizar la versión corta:
CREATE TABLE t2 ( x INT ) WITH SYSTEM VERSIONING;
La diferencia entre la primera versión y la segunda es que, en la primera, la gestión de las versiones se realiza con campos que yo he definido como start_timestamp y end_timestamp, mientras que en la segunda se llaman ROW_START y ROW_END.
En el primer caso, la vista de la tabla es esta:
Y en la segunda tabla:
Cómo puedes apreciar, al no nombrar específicamente los campos, no aparecen (están ocultos).
Insertamos un dato, y hacemos un SELECT en las dos tablas:
Ahora hacemos una actualización en los datos:
Por último, para comprobar la evolución en el tiempo, podemos realizar varias consultas utilizando FOR SYSTEM_TIME, así, para un determinado momento, escribimos:
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP ‘2020-07-06 17:38:06’;
El registro se modificó a las 17:48, pero preguntamos qué estado tenía a las 17:38.
Otra opción es conocer lo que pasó en un determinado periodo, para lo que escribimos:
SELECT *, row_start, row_end FROM t2 FOR SYSTEM_TIME FROM ‘2020-01-01 00:00:00’ TO ‘2021-01-01 00:00:00’;
Al igual que en la tabla t1, el registro de la tabla t2 también cambió a las 17:48, pero preguntamos la evolución de dicho registro durante 2020, y ha tenido dos estados. En concreto, FROM muestra todos los registros que en dicho periodo, en algún momento, han estado visibles incluyendo el START, pero excluyendo el END.
La última opción es usar BETWEEN o sea, también entre dos fechas, pero trabaja de forma inclusiva, una fila visible exactamente al principio o al final se mostrará también.
Consideraciones sobre system-versioned tables
Sinceramente, el hecho de permitir conocer la evolución de un registro en el tiempo (y con el tiempo), es una característica magnífica que, si bien no es interesante de explotar en muchas tablas, sí puede resultar de mucho interés en algunas.
Si ya dispones de una tabla, siempre puedes iniciar el sistema de versiones mediante un ALTER TABLE, y disponiendo desde entonces de dicha posibilidad.
Cuando realices consultas sobre una tabla que almacena versiones, sólo mostrará los datos históricos si se incluye la cláusula FOR SYSTEM_TIME, ya que en caso de no incluirse, se estará seleccionando sobre los datos actuales (no históricos).
Se pueden crear vistas usando FOR STSTEM_TIME, pero además, también se puede utilizar con subconsultas.
Quizás pueda resultar muy interesante separar el almacenamiento de los datos históricos de los datos actuales, porque el almacenaje conjunto supone un aumento en tamaño, en los índices, …. y en el caso de que muchas consultas afecten sólo a los datos actuales, en esos casos puede resultar muy atractivo disponer de los datos históricos en otra tabla independiente.
El sistema de versiones no es algo que deba generalizarse, sino más bien debe ser una característica concreta de ciertas tablas, ya que en el caso de que necesites realizar una modificación en el esquema de la tabla, obtendrás un error. Necesitarás activar permiso para ello con la sentencia:
SET @@system_versioning_alter_history = 1;
Y entonces, ya puedes proceder a realizar alteraciones en la estructura de la tabla.
Y ya está bien por hoy! Happy coding!