CS6/mariadb-temporal-table.md

## mariadb-temporal-table.md

      
    Raw
  

              mariadb-temporal-table.md
            
          
    MariaDB Temporal Table

[TOC]
Description

This is a HOWTO for database record/value version control, not for schema.
With MariaDB, now we can Versioning database records (values) WITH SYSTEM VERSIONING.
Once we have tables configured correctly, Data Versions will be generated automatically everytime we have new data inserted or values updated.
Also note that when new values are as the same as the old ones, no modifications will be done and no new versions will be created; it just works like a automatic data dedup (de-duplication) feature which avoids adding repeating data.

Unless you really need to log every metric data down (ex, system monitoring), and in that case you might want to check TSDB (time series databases) like InfluxDB, saving repeating unchanged records is just a waste of disk and memory and could also drag system performance down. That's why data dedup is important.

Prerequirements:


MariaDB version 10.3.4 or up (better with 10.4 or up, for Application-time Periods)

With docker: docker run --name mariadb -e MYSQL_ROOT_PASSWORD=password -d mariadb


Prepare

Connect

Connect with client shipped with docker image Docker Hub

docker exec -it mariadb mysql -u root -p

Initialize

Create test database:
CREATE DATABASE Company;
use Company;

Table definition (Default)

Create a Temporal table with versioning support for all columns:
CREATE TABLE Person (
  Id int(11) NOT NULL AUTO_INCREMENT,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  Gender char(1) NOT NULL,
  DepartmentId int(11) NOT NULL,
  PRIMARY KEY (Id),
  CONSTRAINT con_gender CHECK (Gender in ('f','m')))
WITH SYSTEM VERSIONING;

Exclusive table definition (Optional)


For excluding columns from versioning, we can append WITHOUT SYSTEM VERSIONING in column definition.
Reference

CREATE TABLE t (
   x INT,
   y INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;

Inclusive table definition (Optional)


For inclusive column declaration, we can append WITH VERSIONING.
Reference

CREATE TABLE t (
   x INT WITH SYSTEM VERSIONING,
   y INT
);

Partitioning - Storing the History Separately (Recommend)


If most queries on that table use only current data in stead of historical data,
partitioning can reduce the size of the table, gain performance on data queries (table scans and index searches).
Reference

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
  );

Play with Data

Insert record

INSERT always creates new version, as it creates a new data of values as a new record.
MariaDB [Company]> INSERT INTO Person (FirstName, LastName, Gender, DepartmentId) VALUES ('Rasmus', 'Johansson', 'm', 1);
Query OK, 1 row affected (0.002 sec)

MariaDB [Company]> SELECT * FROM Person;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            1 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)

Above command created the first row of records, and it's timestamps.
MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
1 rows in set (0.001 sec)

Update record

UPDATE doesn't always generates new version:

If values are really being changed, it will create a new version.
If values are the same, the UPDATE won't modify any value, so no new version.

UPDATE with different values

MariaDB [Company]> UPDATE Person SET DepartmentId = 2 WHERE Id = 1;
Query OK, 1 row affected (0.050 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

New version shows up.
MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
2 rows in set (0.001 sec)

UPDATE with the same values as existing ones

MariaDB [Company]> UPDATE Person SET DepartmentId = 2 WHERE Id = 1;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Inserted: 0  Warnings: 0

No new version created.
MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
2 rows in set (0.001 sec)

UPDATE with different values, again

MariaDB [Company]> UPDATE Person SET DepartmentId = 3 WHERE Id = 1;
Query OK, 1 row affected (0.011 sec)
Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0

Select data, in different ways

Select only current (latest) data

MariaDB [Company]> SELECT * FROM Person;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            3 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)

Select all versions of data

MariaDB [Company]> SELECT * FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            1 |
|  1 | Rasmus    | Johansson | m      |            2 |
|  1 | Rasmus    | Johansson | m      |            3 |
+----+-----------+-----------+--------+--------------+
3 rows in set (0.001 sec)

Select all versions of data and their duration (time)

MariaDB [Company]> SELECT *,ROW_START, ROW_END FROM Person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            1 | 2020-02-19 22:04:48.565979 | 2020-02-19 22:05:13.094584 |
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
|  1 | Rasmus    | Johansson | m      |            3 | 2020-02-19 22:05:39.879380 | 2038-01-19 03:14:07.999999 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
3 rows in set (0.001 sec)


Select data at a specific moment

MariaDB [Company]> SELECT * FROM Person FOR SYSTEM_TIME AS OF TIMESTAMP '2020-02-19 22:05:20';
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName  | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
|  1 | Rasmus    | Johansson | m      |            2 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)

MariaDB [Company]> SELECT *, ROW_START, ROW_END FROM Person FOR SYSTEM_TIME AS OF TIMESTAMP '2020-02-19 22:05:20'; 
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| Id | FirstName | LastName  | Gender | DepartmentId | ROW_START                  | ROW_END                    |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
|  1 | Rasmus    | Johansson | m      |            2 | 2020-02-19 22:05:13.094584 | 2020-02-19 22:05:39.879380 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
1 row in set (0.001 sec)

References


Automatic Data Versioning in MariaDB Server 10.3
Temporal Data Tables
Use Cases for MariaDB Data Versioning
Some Notes on MariaDB system-versioned Tables
MariaDB Enterprise Backup