アイスタイルで分析基盤の構築・運用を行っているやすです。
主にBigQuery, digdag, embulk, Redashといった技術を日々の業務では取り扱っています。
Automatic Data Versioningとは
PJTでMariaDB ColumnStoreを検証した際に、MariaDB Server 10.3.4から追加された
Automatic Data Versioningというテーブルのバージョン管理機能を見つけたので、紹介をさせていただきます。
MariaDB の公式TwitterアカウントからAutomatic Data Versioning in MariaDB Server 10.3 | MariaDB が
流れてきたのがキッカケでした。
Automatic Data VersioningはSQL:2011で追加されたTemporal databaseに基づき実装され、データの変更履歴と
これからの状態を保持する機能です。例えば、ある商品の価格の変化を時系列で分析する場合などが想定されています。
以下に「どう設定するか」と「どうなるのか」を簡単にまとめます。
どう設定するか
Automatic Data Versioningの設定は簡単で、CREATE文の末尾にWITH SYSTEM VERSIONINGを加えるだけです。
CREATE DATABASE company;
CREATE TABLE person (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
gender char(1) NOT NULL,
department_id int(11) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT con_gender CHECK (gender in ('f','m')))
WITH SYSTEM VERSIONING;
これで設定は完了です。
どうなるのか
リンク先のブログ中では1レコード挿入し、DepartmentIdをUPDATE文で1→2→3と値を更新して行きます。
その後、下記クエリを流すと以下のような結果が返却されます。
MariaDB [Company]> SELECT *, ROW_START, ROW_END FROM person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| id | first_name| last_name | gender | department_id| ROW_START | ROW_END |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| 1 | Rasmus | Johansson | m | 1 | 2018-06-26 19:30:46.885377 | 2018-06-26 19:31:32.252801 |
| 1 | Rasmus | Johansson | m | 2 | 2018-06-26 19:31:32.252801 | 2018-06-26 19:31:43.284752 |
| 1 | Rasmus | Johansson | m | 3 | 2018-06-26 19:31:43.284752 | 2038-01-19 12:14:07.999999 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
3 rows in set (0.001 sec)
Companyテーブルに SELECT * のクエリを実行しただけでは、ROW_STARTとROW_ENDのカラムは出てきません。
これは10.3.3でリリースされたInvisible Columnsという機能が働いているからです。
Invisible Columns – MariaDB Knowledge Base
要するに、「不可視のカラム」というわけです。
続いて、ROW_STARTとROW_ENDはその時間幅におけるレコードの状態を表しています。
例えば、上記結果2レコード目は2018-05-03 07:22:29.188266
から 2018-05-03 07:22:47.596481
までの間の
DepartmentIdが2であることを意味しています。
未来日を指定したらどうなるか
2018-06-26 19:31:34
時点のレコードを取得するためには以下のようなクエリを実行します。
MariaDB [Company]> SELECT * FROM person FOR SYSTEM_TIME AS OF TIMESTAMP '2018-06-26 19:31:34';
+----+-----------+-----------+--------+--------------+
| id | first_name| last_name | gender | department_id|
+----+-----------+-----------+--------+--------------+
| 1 | Rasmus | Johansson | m | 2 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)
ではMariaDBの繁栄を期待して、今から50年後の2068-06-26
時点のレコードを取得して見ましょう。
MariaDB [Company]> SELECT * FROM person FOR SYSTEM_TIME AS OF TIMESTAMP '2068-06-26 00:00:00';
Empty set (0.001 sec)
空で返ってきました。SELECT *,ROW_START, ROW_END FROM person FOR SYSTEM_TIME ALL;の
結果を見ていただくとわかるのですが、最新のレコードのROW_ENDの値が2038-01-19 12:14:07.999999
と
それ以降の時点を指定しているので、空で返ってきます。残念ながらINSERTでもUPDATEでも変更することはできません。
(変更できたからと言ってメリットはそれほどありませんが…)
ある時点のデータに戻したい場合
下記のようなWITH句とUPDATE句を組み合わせたクエリは、エラーになってしまいます。
WITH his AS(
SELECT *
FROM person
FOR SYSTEM_TIME AS OF TIMESTAMP '2018-06-26 19:31:34'
)
UPDATE person p
JOIN his h ON p.id = h.id
SET p.id = h.id, p.first_Name = h.first_Name,
p.last_name = h.last_name, p.gender = h.gender,
p.department_id = h.department_id
WHERE p.id = h.id;
一度ビューに指定時点のデータを格納し、UPDATE句で対象のテーブルとJOINさせる方法で
ある時点のデータに戻すことができます。(※これよりうまい方法がある気がします…)
MariaDB [Company]> CREATE VIEW v1 AS SELECT * FROM person FOR SYSTEM_TIME AS OF TIMESTAMP '2018-06-26 19:31:34';
Query OK, 0 rows affected (0.006 sec)
MariaDB [Company]> SELECT * FROM v1;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
| 1 | Rasmus | Johansson | m | 2 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)
MariaDB [Company]> SELECT * FROM person;
+----+-----------+-----------+--------+--------------+
| Id | FirstName | LastName | Gender | DepartmentId |
+----+-----------+-----------+--------+--------------+
| 1 | Rasmus | Johansson | m | 3 |
+----+-----------+-----------+--------+--------------+
1 row in set (0.001 sec)
以下のUPDATE文を流します。
UPDATE person P
JOIN v1 H ON p.Id = h.Id
SET p.id = h.id, p.first_name = h.first_name,
p.last_name = h.last_name, p.gender = h.gender,
p.department_id = h.department_id
WHERE p.id = h.id;
ちゃんと更新履歴にも反映されていますね。
MariaDB [Company]> SELECT *, ROW_START, ROW_END FROM person FOR SYSTEM_TIME ALL;
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| id | first_name| last_name | gender | department_id| ROW_START | ROW_END |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
| 1 | Rasmus | Johansson | m | 1 | 2018-06-26 19:30:46.885377 | 2018-06-26 19:31:32.252801 |
| 1 | Rasmus | Johansson | m | 2 | 2018-06-26 19:31:32.252801 | 2018-06-26 19:31:43.284752 |
| 1 | Rasmus | Johansson | m | 3 | 2018-06-26 19:31:43.284752 | 2018-07-06 04:02:00.816946 |
| 1 | Rasmus | Johansson | m | 2 | 2018-07-06 04:02:00.816946 | 2038-01-19 12:14:07.999999 |
+----+-----------+-----------+--------+--------------+----------------------------+----------------------------+
4 rows in set (0.001 sec)
Automatic Data Versioningを利用して、テーブルを特定時点に切り戻すことができました。
データサイズの変化
Automatic Data Versioningを適応したテーブルとそうでないテーブルで、サイズはどのように変化するのか実験して見ます。
Idと作成日時だけをもつシンプルなテーブルで、Data Versioningを適応したものとそうでないものを用意します。
CREATE TABLE not_data_ver(
id int(11) NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE data_ver(
id int(11) NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)WITH SYSTEM VERSIONING;
件数の比較
とりあえず100万件ほどデータを投入し、SELECT COUNT(*)を実行してみると…大体1.5秒くらいの差がこの時点であります。
MariaDB [Company]> SELECT COUNT(*) FROM not_data_ver;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (0.408 sec)
MariaDB [Company]> SELECT COUNT(*) FROM data_ver;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (1.912 sec)
各テーブルのcreated_atを2018-07-07 00:00:00
に更新するクエリを実行します。
MariaDB [Company]> UPDATE data_ver SET created_at = '2018-07-07 00:00:00';
Query OK, 1048576 rows affected (1 min 8.673 sec)
Rows matched: 1048576 Changed: 1048576 Inserted: 1048576 Warnings: 0
MariaDB [Company]> UPDATE not_data_ver SET created_at = '2018-07-07 00:00:00';
Query OK, 1048576 rows affected (13.346 sec)
Rows matched: 1048576 Changed: 1048576 Warnings: 0
Automatic Data Versioningが適応されてない方は13秒で終わりましたが、適応されているテーブルは1分ほどかかりました!
更新後の両テーブルにSELECT COUNT(*)を実行してみると、Automatic Data Versioningが
適応されてない方は時間が変わりませんが、なんと適応されたテーブルは8秒と更新前と比べ、4倍ほどになっています。
MariaDB [Company]> SELECT COUNT(*) FROM not_data_ver;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (0.432 sec)
MariaDB [Company]> SELECT COUNT(*) FROM data_ver;
+----------+
| count(*) |
+----------+
| 1048576 |
+----------+
1 row in set (8.716 sec)
テーブルサイズの比較
information_schema.tablesから各テーブルの行数とサイズを見てみると、Automatic Data Versioningが適応されたテーブルは
UPDATE文後に適応していないテーブルと比較すると3倍のテーブルサイズに、UPDATE文の前後でnot_data_verテーブルを
比較すると、2倍ほどに膨れ上がっています。
(rows_countの値がおかしい気もしますがここでは割愛)
UPDATE文を流す前
+-------------------+------------+----------------+
| table_name | rows_count | table size(MB) |
+-------------------+------------+----------------+
| data_ver | 955824 | 44.5 |
| not_data_ver | 955824 | 29.6 |
+-------------------+------------+----------------+
UPDATE文を流した後
+-------------------+------------+----------------+
| table_name | rows_count | table size(MB) |
+-------------------+------------+----------------+
| data_ver | 2114495 | 96.6 |
| not_data_ver | 955824 | 29.6 |
+-------------------+------------+----------------+
大量のレコードを保持する予定のテーブルに適応する場合は、通常の場合よりも
容量を多く確保しておいた方がよさそうです。
おわり
MariaDB Server の Automatic Data Versioning機能を使った特定時点のテーブルの状態に戻す方法と
更新によるデータサイズの変化を試してみました!
現在サービスのDBをクロールして、テーブル定義書を作成するアプリケーションを作成していて、カラムの変更履歴を
Automatic Data Versioningを活用して管理していこうと考えています!
ではまた >゜)))彡