はじめに

この記事はNTTテクノクロス Advent Calendar 2018の5日目の記事です🙌

NTTテクノクロスの @yamamo-i です🤗
普段はApache IgniteやPostgreSQLの技術検証をしています💪
本日はRDBMSをドキュメントDBとして利用する技術について触れたいと思います📖
PostgreSQL🐘をメインにしていますが今日はMySQL🐬の話をします😇

TL;DR

MySQLのX Protocolを試してみました。
- MySQLのX Pluginの構築とPythonでX DevAPIを使ってみました。
ドキュメントDBとしてMySQLのX Protocolも選択肢に入れてみてよいと思います。

ドキュメント(json)管理

最近はMongoDBなどのNoSQLデータベースがドキュメントのデータストアとして台頭しています。
しかし、RDBMSもドキュメント管理のインターフェースを用意しています。

RDBMSでのドキュメント管理

RDBMSでもドキュメントを管理するインターフェースを用意しています
ただし、独特のSQLを発行する必要があるため、アプリケーションエンジニアからの敷居が高く感じます。

MySQL X Protocol

MySQLではX Protocolというドキュメント管理(json型)用の操作Protocolを提供しています。
https://dev.mysql.com/doc/refman/8.0/en/document-store.html
以下の図がX Protocolの概念になります。
X DevAPIを利用してPython, JavaScript, Node.js, ShellなどからX Protocolを利用できます。
X Pluginはドキュメント管理用のMySQL Serverのインターフェースを用意します。

引用元(https://dev.mysql.com/doc/internals/en/x-protocol.html)

MySQLの公式から提供されることで以下のことが期待できます。

トランザクション操作ができる
json型用のSQLをアプリケーションから隠蔽できる

本記事はその構築方法や操作方法を記述します。

X Pluginの構築

MySQLをデフォルトで起動してもX Protocol(33060ポート)はLISTENしません。
MySQLコミュニティからパッケージが提供されているので追加します。
今回はDocker(18.06.1-ce-mac73)を用いて構築しました。
Dockerfileは以下になります。

FROM mysql:8.0.13

EXPOSE 33060
RUN set -x \
    && apt update \
    && apt install -y wget net-tools libpython2.7 \
    && apt --fix-broken install \
    && (cd tmp && wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_8.0.13-1debian9_amd64.deb && dpkg -i mysql-shell_8.0.13-1debian9_amd64.deb) \
    && (cd tmp && wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-dbgsym_8.0.13-1debian9_amd64.deb && dpkg -i mysql-shell-dbgsym_8.0.13-1debian9_amd64.deb) \
    && rm /tmp/*.deb

ビルドと起動は以下のコマンドを発行します。

# Docker Imageの作成
$ docker build -t mysql:8.0.13-x .

# Docker Containerの起動
$ docker run --name mysql -d -p33060:33060 --env MYSQL_ROOT_PASSWORD='password' mysql:8.0.13-x

# 33060ポートがLISTENします
$ docker exec -it mysql netstat -tan
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp6       0      0 :::33060                :::*                    LISTEN
tcp6       0      0 :::3306                 :::*                    LISTEN

# Pluginのmysqlxが有効化しています
$ docker exec -it mysql mysql -uroot -p -e"SHOW plugins"
Enter password:
+---------------------------------+----------+--------------------+---------+---------+
| Name                            | Status   | Type               | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
.
(中略)
.
| mysqlx                          | ACTIVE   | DAEMON             | NULL    | GPL     |
.
+---------------------------------+----------+--------------------+---------+---------+

これでX Protocolが開いたMySQLが起動しました。

PythonによるX DevAPIの操作

Python(今回は3.7.1)からX DevAPIを操作するにはmysql-connector-pythonを使用します。
インストールはpipを利用します。

# 今回は最新版の8.0.13を使用しました
$ pip install mysql-connector-python==8.0.13

まずはコネクションを取得して操作を始めます。

# X DevAPI用のライブラリをインポート
import mysqlx

# MySQLのX Protocolを使用してコネクションを張る
session = mysqlx.get_session({'host': '127.0.0.1', 'port': 33060, 'user': 'root', 'password': 'password'})
#  `mysql` スキーマに接続
schema = session.get_schema('mysql')

これでデータベースの操作ができるようになりました。

DDL(Data Definition Language)

X DevAPIからドキュメント用のテーブルを作成できます。

# テーブルの作成
schema.create_collection('my_collection')

作成したテーブルは一意キーの_idとjson管理用のdocの2カラムが定義されます。
このテーブルでドキュメントを管理します。

mysql> show create table my_collection\G
*************************** 1. row ***************************
       Table: my_collection
Create Table: CREATE TABLE `my_collection` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

DML(Data Manipulation Language)

INSERT

レコードの挿入はaddメソッドで操作します。
Pythonのdictをそのまま挿入できるので便利です。

insert_record.py

# 挿入レコードを用意
record = {"company": {
    "NTT-TX": [
        "Soft",
        "AT",
        "IT"
    ]
}}
# データ挿入処理
collection.add(record).execute();

挿入したレコードは以下の様になっています。
docカラムのjsonには自動で_id要素が付与されます。

mysql> select * from my_collection;
+--------------------------------------------------------------------------------------+------------------------------+
| doc                                                                                  | _id                          |
+--------------------------------------------------------------------------------------+------------------------------+
| {"_id": "00005c034a580000000000000001", "company": {"NTT-TX": ["Soft", "AT", "IT"]}} | 00005c034a580000000000000001 |
+--------------------------------------------------------------------------------------+------------------------------+
1 row in set (0.00 sec)

MySQLに発行されているSQL¹は以下であり、_idカラムで管理しているidを自動でjsonに付与しています。

SELECT @@mysqlx_document_id_unique_prefix,@@auto_increment_offset,@@auto_increment_increment;
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_SET(JSON_OBJECT('company',JSON_OBJECT('NTT-TX',JSON_ARRAY('Soft','AT','IT'))), '$._id', '00005c034a580000000000000001'));

また_idをユーザーが指定したり、複数レコードの挿入もできます。

bulk_insert.py

# レコードの生成
record_list = [{"_id": _id, "age": _id * 5} for _id in range(10)]
# listでも複数引数でも挿入可能
collection.add(*record_list).execute();

mysql> select * from my_collection;
+-----------------------+-----+
| doc                   | _id |
+-----------------------+-----+
| {"_id": 0, "age": 0}  | 0   |
| {"_id": 1, "age": 5}  | 1   |
| {"_id": 2, "age": 10} | 2   |
| {"_id": 3, "age": 15} | 3   |
| {"_id": 4, "age": 20} | 4   |
| {"_id": 5, "age": 25} | 5   |
| {"_id": 6, "age": 30} | 6   |
| {"_id": 7, "age": 35} | 7   |
| {"_id": 8, "age": 40} | 8   |
| {"_id": 9, "age": 45} | 9   |
+-----------------------+-----+
10 rows in set (0.00 sec)

INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_OBJECT('_id',0,'age',0)),(JSON_OBJECT('_id',1,'age',5)),(JSON_OBJECT('_id',2,'age',10)),(JSON_OBJECT('_id',3,'age',15)),(JSON_OBJECT('_id',4,'age',20)),(JSON_OBJECT('_id',5,'age',25)),(JSON_OBJECT('_id',6,'age',30)),(JSON_OBJECT('_id',7,'age',35)),(JSON_OBJECT('_id',8,'age',40)),(JSON_OBJECT('_id',9,'age',45));

READ

レコードの読み込みはfind, bind, limit で操作します。

select_records.py

record = collection.find("age >= :param").bind("param", 21).limit(2).execute()
for rec in record.fetch_all():
    print(rec)

# 実行結果
# {"_id": 5, "age": 25}
# {"_id": 6, "age": 30}

発行されるSQLは以下になります。

SELECT doc FROM `mysql`.`my_collection` WHERE (JSON_EXTRACT(doc,'$.age') >= 21) LIMIT 0, 2;

UPDATE

レコードのUPDATEはmodify, patchメソッドで操作します。

update_records.py

collection.modify("age <= 21").patch('{"_is": "young"}').execute()
collection.modify("age > 21").patch('{"_is": "old"}').execute()

発行されるSQLとレコードは以下のようになります。

mysql> select * from my_collection;
+---------------------------------------+-----+
| doc                                   | _id |
+---------------------------------------+-----+
| {"_id": 0, "_is": "young", "age": 0}  | 0   |
| {"_id": 1, "_is": "young", "age": 5}  | 1   |
| {"_id": 2, "_is": "young", "age": 10} | 2   |
| {"_id": 3, "_is": "young", "age": 15} | 3   |
| {"_id": 4, "_is": "young", "age": 20} | 4   |
| {"_id": 5, "_is": "old", "age": 25}   | 5   |
| {"_id": 6, "_is": "old", "age": 30}   | 6   |
| {"_id": 7, "_is": "old", "age": 35}   | 7   |
| {"_id": 8, "_is": "old", "age": 40}   | 8   |
| {"_id": 9, "_is": "old", "age": 45}   | 9   |
+---------------------------------------+-----+
10 rows in set (0.00 sec)

UPDATE `mysql`.`my_collection` SET doc=JSON_SET(JSON_MERGE_PATCH(doc,JSON_OBJECT('_is','young')),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$.age') <= 21);
UPDATE `mysql`.`my_collection` SET doc=JSON_SET(JSON_MERGE_PATCH(doc,JSON_OBJECT('_is','old')),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$.age') > 21);

DELETE

レコードのDELETEはremoveメソッドで操作します。

delete_records.py

collection.remove("age >= 31").execute()

発行されるSQLとレコードは以下のようになります。

mysql> select * from my_collection;
+---------------------------------------+-----+
| doc                                   | _id |
+---------------------------------------+-----+
| {"_id": 0, "_is": "young", "age": 0}  | 0   |
| {"_id": 1, "_is": "young", "age": 5}  | 1   |
| {"_id": 2, "_is": "young", "age": 10} | 2   |
| {"_id": 3, "_is": "young", "age": 15} | 3   |
| {"_id": 4, "_is": "young", "age": 20} | 4   |
| {"_id": 5, "_is": "old", "age": 25}   | 5   |
| {"_id": 6, "_is": "old", "age": 30}   | 6   |
+---------------------------------------+-----+
7 rows in set (0.00 sec)

DELETE FROM `mysql`.`my_collection` WHERE (JSON_EXTRACT(doc,'$.age') >= 31);

DCL(Data Control Language)

X DevAPIはMySQLがベースであるからこそトランザクション操作も実現できます。
トランザクションはsession.start_transaction, session.commit,session.rollbackメソッドで操作できます。
以下のようなコードで書けます。

transaction.py

# セッションの確立
session = mysqlx.get_session({'host': '127.0.0.1', 'port': 33060, 'user': 'root', 'password': 'root'})

try:
    # テーブル情報の取得
    schema = session.get_schema('mysql')
    schema.create_collection('my_collection', True)
    collection = schema.get_collection('my_collection')

    # トランザクションの開始
    session.start_transaction()
    collection.add({"_id": 1, "NTT": "TX"}).execute()

    # トランザクションのコミット
    session.commit()
except Exception:
    # 例外時はトランザクションをロールバック
    session.rollback()
finally:
    session.close()

上記のコードを実行した時、発行されるSQLは以下になります。

START TRANSACTION;
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_OBJECT('_id',1,'NTT','TX'));
COMMIT;

故意に例外を発生させるとROLLBACKが発行されます。

START TRANSACTION;
INSERT INTO `mysql`.`my_collection` (doc) VALUES (JSON_OBJECT('_id',1,'NTT','TX'));
ROLLBACK;

その他

MySQL X DevAPIは他にも以下の操作ができます。

RDBMSで一般的な操作がドキュメントDBとして実現されています。

まとめ

X Protocolを使ってMySQLをドキュメントDBとして操作してみました。
メリットは以下に感じました。

RDBMSなので使い慣れている
トランザクション機能が使える
json型のIFを隠蔽してくれる
MySQLが公式にリリースしている

いきなりドキュメントDBを導入するのではなくRDBMSの機能を使うのもよいのではないでしょうか？
明日からのNTTテクノクロス Advent Calendar 2018もお楽しみに🎉
明日の担当は @j-yama さんです🙏

MySQLに発行されているSQLの調査はslow_query_logを有効かつlong_query_timeを0にして調査しました。 ↩

ドキュメントDBとしてのRDBMS