テンポラル・テーブル – MariaDB Server 10.3 新機能

Posted on 1月 22, 2019 by

2018年5月にGAとなった MariaDB Server 10.3 では，すべてのデータ作成/更新/削除履歴を記録可能なテンポラル・テーブル(System-Versioned Tables)が新機能として追加されました。
テンポラル・テーブルにより以下のオペレーションが可能となり，より高度なセキュリティ，データ復旧性などが得られます。

  • すべてのデータ変更の監査
  • 過去の任意時点でのデータのリカバリ
  • 偶発的なデータ変更/削除や人為的エラー，アプリケーション エラーからの復旧

今回は簡単なサンプルデータを用いてテンポラルテーブルを解説したいと思います。

テスト環境

  • MariaDB Server 10.3.12
  • CentOS 7.6.1810

サンプルテーブル作成

従業員リストのようなテーブルを作成し，Linuxのdateコマンドでシステム時間を変更しながらサンプルデータをINSERTします。

create table employees (id int, 
name varchar(50)
) WITH SYSTEM VERSIONING;

テンポラルテーブルを用いるには，上記のように，WITH SYSTEM VERSIONING を付与します。なお，CREATE TABLEだけでなく，ALTER TABLE でも利用可能です。

サンプルデータINSERT

以下の日付でサンプルデータをINSERTします。システムの日時は，例えば2017-01-01 9:00の場合は，以下のコマンドで変更することが可能です。(chronyd は disable としています)。

date 010109002017
# date MMDDhhmmCCYY

2017-01-01:
Johnが入社

INSERT INTO employees VALUES (1, 'John');

2017-03-01:
Mariaが入社

INSERT INTO employees VALUES (2, 'Maria');

2018-06-01:
Bobが入社

INSERT INTO employees VALUES (3, 'Bob');

2018-06-30:
John(id=1)が退職

delete from employees where id=1;

2018-12-24:
Eveが入社

INSERT INTO employees VALUES (4, 'Eve');

2018-12-31:
Bob(id=3)が退職

delete from employees where id=3;

2019-01-01:
Tomが入社

INSERT INTO employees VALUES (5, 'Tom');

サンプルクエリ

全レコードのSELECT

全履歴を参照するには，FOR SYSTEM_TIME ALL を付与してSELECTを実行します。

MariaDB [test]> SELECT *, row_start, row_end 
FROM employees FOR SYSTEM_TIME ALL;
+------+-------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+------+-------+----------------------------+----------------------------+
| 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 |
| 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 |
| 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 |
| 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 |
| 5 | Tom | 2019-01-01 09:00:12.955820 | 2038-01-19 12:14:07.999999 |
+------+-------+----------------------------+----------------------------+

System Versioned Table では，row_start と row_end が隠しカラムとして追加され，各レコードがいつからいつまで存在していたか記録されます。

2017-01-01時点での全社員リスト

任意時点でのレコードを抽出するには，FOR SYSTEM_TIME AS OF TIMESTAMP '日時'をSELECTに付与します。

MariaDB [test]> SELECT *, row_start, row_end 
　FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2017-01-01 10:00';
+------+------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+------+------+----------------------------+----------------------------+
| 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 |
+------+------+----------------------------+----------------------------+
2018-01-01時点での全社員リスト
MariaDB [test]> SELECT *, row_start, row_end 
FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-01-01 10:00';
+------+-------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+------+-------+----------------------------+----------------------------+
| 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 |
| 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 |
+------+-------+----------------------------+----------------------------+
2018-06-30時点での全社員リスト

John が 2018-06-30 17:00 で退職しますが，10:00の時点では社員数が3となります。

MariaDB [test]> SELECT *, row_start, row_end 
FROM employees FOR SYSTEM_TIME AS OF TIMESTAMP '2018-06-30 10:00';
+------+-------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+------+-------+----------------------------+----------------------------+
| 1 | John | 2017-01-01 09:00:56.052927 | 2018-06-30 17:00:11.097712 |
| 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 |
| 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 |
+------+-------+----------------------------+----------------------------+
2018-12-31時点での全社員リスト

John が退職済で，2018-12-24 に Eve が入社していますので，社員数は 3 となります。

MariaDB [test]> SELECT *, row_start, row_end FROM employees 
FOR SYSTEM_TIME AS OF TIMESTAMP '2018-12-31 10:00';
+------+-------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+------+-------+----------------------------+----------------------------+
| 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 |
| 3 | Bob | 2018-06-01 09:00:16.662932 | 2018-12-31 17:00:08.481786 |
| 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 |
+------+-------+----------------------------+----------------------------+
最新の社員リスト

2018年いっぱいで Bob が退職し，2019-01-01 に Tom が入社していますので，社員数は 3 となります。

MariaDB [test]> SELECT *, row_start, row_end FROM employees;
+------+-------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+------+-------+----------------------------+----------------------------+
| 2 | Maria | 2017-03-01 09:00:45.058783 | 2038-01-19 12:14:07.999999 |
| 4 | Eve | 2018-12-24 09:00:22.983417 | 2038-01-19 12:14:07.999999 |
| 5 | Tom | 2019-01-01 09:00:12.955820 | 2038-01-19 12:14:07.999999 |
+------+-------+----------------------------+----------------------------+

まとめ

MariaDB Server 10.3 で新たに導入されたテンポラル・テーブルについて解説いたしました。複雑なSQL文を用いたり，アプリケーション側で日時の演算を行うことなく，任意の過去の時点でのレコードを簡単に参照可能であることが確認できました。
商用データベースでは以前から同様の機能を備えているものもあり，MariaDBへの移行を検討されている方にとって有益な新機能と考えます。