MySQL8.0はGAとなった8.0.11以降のメンテナンスリリースでも新機能のリリースを行うことが公式に言及されており、今回ご紹介するMySQL ShellのJSONインポート関数も8.0.13で新しく追加されました。
本記事ではMySQL Server Teamのブログ記事そのままの内容ですがMongoDBからMySQLへのJSONデータインポートを試したいと思います。
検証環境は以下の通りです。簡単のためMySQLとMongoDBは同一インスタンスにインストールしています。
・CentOS7.5 (Vagrantで構築）
・MySQL8.0.13
・MongoDB4.0.4
事前準備
まずMongoDBにサンプルデータを用意し、それをmongoexportでJSONファイルとして出力します。
上記ブログ記事の末尾にサンプルデータのリンクがありましたのでそちらを使用します。
$ mongoexport --db test --collection restaurants --out restaurants_mongo.json
2018-10-08T18:38:19.104+0100 connected to: localhost
2018-10-08T18:38:19.633+0100 exported 25359 records
$ mongoexport --db test --collection neighborhoods --out neighborhoods_mongo.json
2018-10-08T18:38:45.923+0100 connected to: localhost
2018-10-08T18:38:46.382+0100 exported 195 records
JSONファイルのインポート
MySQL Shellを使用してtestデータベースにJSONファイルをインポートします。
JavaScriptモードとPythonモードそれぞれに関数が用意されています。
・Javascriptモードの場合
$ mysqlsh -uroot -p --js
# neighborhoods_mongo.jsonをコレクション形式でインポート
MySQL localhost:33060+ ssl test JS > util.importJson("/home/vagrant/neighborhoods_mongo.json", {schema: "test", collection: "neighborhoods", convertBsonOid: true});
Importing from file "/home/vagrant/neighborhoods_mongo.json" to collection `test`.`neighborhoods` in MySQL Server at localhost:33060
.. 195.. 195
Processed 4.14 MB in 195 documents in 0.2959 sec (658.93 documents/s)
Total successfully imported documents 195 (658.93 documents/s)
# restaurants_mongo.jsonをテーブル形式でインポート
MySQL localhost:33060+ ssl JS > util.importJson("/home/vagrant/restaurants_mongo.json", {schema: "test", table: "restaurants", convertBsonOid: true});
Importing from file "/home/vagrant/restaurants_mongo.json" to table `test`.`restaurants` in MySQL Server at localhost:33060
.. 25359.. 25359
Processed 3.52 MB in 25359 documents in 0.5532 sec (45.84K documents/s)
Total successfully imported documents 25359 (45.84K documents/s)
・Pythonモードの場合
$ mysqlsh -uroot -p --py
# neighborhoods_mongo.jsonをコレクション形式でインポート
MySQL localhost:33060+ ssl test Py > util.import_json("/home/vagrant/neighborhoods_mongo.json", {"schema": "test", "collection": "neighborhoods", "convertBsonOid": True})
Importing from file "/home/vagrant/neighborhoods_mongo.json" to collection `test`.`neighborhoods` in MySQL Server at localhost:33060
.. 195.. 195
Processed 4.14 MB in 195 documents in 0.2748 sec (709.66 documents/s)
Total successfully imported documents 195 (709.66 documents/s)
# restaurants_mongo.jsonをテーブル形式でインポート
MySQL localhost:33060+ ssl test Py > util.import_json("/home/vagrant/restaurants_mongo.json", {"schema": "test", "table": "restaurants", "convertBsonOid": True})
Importing from file "/home/vagrant/restaurants_mongo.json" to table `test`.`restaurants` in MySQL Server at localhost:33060
.. 25359.. 25359
Processed 3.52 MB in 25359 documents in 0.5541 sec (45.77K documents/s)
Total successfully imported documents 25359 (45.77K documents/s)
これらのコマンドによってJSONファイルがインポートされます。（テーブルはDB内に存在しない場合でも自動的に作成されます）
それぞれ2つのファイルをインポートする際に第二引数のフィールド名として"collection"もしくは"field"を指定していますが、これらはインポート時のオプションであり
・MySQL Document Storeの機能を最大限に享受する場合は"collection"を使用する。"collection"を指定した場合、値として明示された正しいコレクション（または値が存在しないもの）のみ処理を許可し、それ以外はエラーとする。
・SQLのみの操作によるJSON型データまたはJSON関数を扱う場合は"table"オプションを使用する。すでに存在しているテーブルにインポートする場合なども同様。
とあります。（Server Teamの過去記事もご参照ください）
インポートした後のデータ形式の差異については次項で詳しく見ていきます。
また、"convertBsonOid"オプションをtrueに設定することによりMongoDBのObject Idを正しい値に変換することができます。（JSONファイルでは{"_id": {"$oid":"55cba2476c522cafdb053add"}, ...}」となっている値を「{"_id": "55cba2476c522cafdb053add", ...}」といった形に変換）
上記では使用していませんが、他にtableColumnというオプションでデフォルトでは"doc"となるJSONカラムの名称を指定することができます。
インポートした値およびコレクション形式とテーブル形式の差異について
JSONファイルをインポートした結果について見ていきます。（以下はJavaScriptモードの例です）
先程コレクション形式で生成したneighborhoodsについてはdb.getCollections()関数によって取得することができます。一方、テーブル形式で生成したrestaurantsは取得されません。
MySQL localhost:33060+ ssl mysql JS > \use test
Default schema `test` accessible through db.
MySQL localhost:33060+ ssl test JS > db.getCollections();
[
<Collection:neighborhoods>
]
SHOW TABLESにより、いずれの形式においてもテーブルとして生成されていることがわかります。
MySQL localhost:33060+ ssl test JS > session.sql("SHOW TABLES");
+--------------------------+
| Tables_in_test |
+--------------------------+
| neighborhoods |
| restaurants |
+--------------------------+
2 rows in set (0.0017 sec)
それぞれSHOW CREATE TABLEでの結果は以下のようになっています。
# コレクション形式の場合
MySQL localhost:33060+ ssl test JS > session.sql("SHOW CREATE TABLE test.neighborhoods");
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| neighborhoods | CREATE TABLE `neighborhoods` (
`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.0009 sec)
# テーブル形式の場合
MySQL localhost:33060+ ssl test JS > session.sql("SHOW CREATE TABLE test.restaurants");
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| restaurants | CREATE TABLE `restaurants` (
`doc` json DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25360 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0007 sec)
生成されるID列（プライマリキー）が、コレクションの場合はJSONの_idフィールドを使用したGENERATEDカラムであるのに対し、
テーブルの場合はAUTO INCREMENTなidカラムとなります。
インポートしたJSONデータから新しい列を生成する
ここまでの例でも分かる通り、インポートしたJSONデータはそのままだとJSON型カラムにすべて収納された形となっています。が、このままではインデックスを用いた高速なスキャンを行うことができず、またデータを一部だけ使用することが難しい状態です。次の例ではJSONデータから新しい列を生成してみます。
まず、新しいJSONデータセットであるprimer-dataset.jsonをmy_restaurantsテーブルとしてインポートし、JSON関数によってフィールドの値を取得する場合の実行計画を見てみます。
# JSONデータをインポート
MySQL localhost:33060+ ssl JS > util.importJson("/home/vagrant/primer-dataset.json", {schema: "test", table: "my_restaurants"});
Importing from file "/home/vagrant/primer-dataset.json" to table `test`.`my_restaurants` in MySQL Server at localhost:33060
.. 25359.. 25359
Processed 11.85 MB in 25359 documents in 1.2729 sec (19.92K documents/s)
Total successfully imported documents 25359 (19.92K documents/s)
# 出力結果を縦にする（SQLモードでの末尾\Gに相当）
MySQL localhost:33060+ ssl JS > shell.options.outputFormat = "vertical";
vertical
# 実行計画を取得
MySQL localhost:33060+ ssl JS > session.sql('EXPLAIN SELECT doc->>"$.name" AS name FROM test.my_restaurants WHERE doc->>"$.cuisine" = "Italian"');
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: my_restaurants
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 24281
filtered: 100
Extra: Using where
1 row in set, 1 warning (0.0475 sec)
Note (code 1003): /* select#1 */ select json_unquote(json_extract(`test`.`my_restaurants`.`doc`,'$.name')) AS `name` from `test`.`my_restaurants` where (json_unquote(json_extract(`test`.`my_restaurants`.`doc`,'$.cuisine')) = 'Italian')
インデックスがないためテーブルフルスキャンが実行されようとしています。（type: ALLおよびrowsの数に注目）
次に、JSON型カラムからcuisineフィールドを仮想列としてGENERATEし、そこにインデックスを作成したのち実行計画を見てみます。
# cuisineフィールドを仮想列として作成
MySQL localhost:33060+ ssl JS > session.sql('ALTER TABLE test.my_restaurants ADD COLUMN cuisine VARCHAR(80) GENERATED ALWAYS AS (doc->>"$.cuisine") VIRTUAL, WITH VALIDATION');
Query OK, 25359 rows affected (0.3038 sec)
# 仮想列にインデックスを作成
MySQL localhost:33060+ ssl JS > session.sql('ALTER TABLE test.my_restaurants ADD INDEX cuisine_idx (cuisine)');
Query OK, 0 rows affected (0.1667 sec)
# 実行計画を取得
MySQL localhost:33060+ ssl JS > session.sql('EXPLAIN SELECT doc->>"$.name" AS name FROM test.my_restaurants WHERE cuisine = "Italian"');
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: my_restaurants
partitions: NULL
type: ref
possible_keys: cuisine_idx
key: cuisine_idx
key_len: 323
ref: const
rows: 1069
filtered: 100
Extra: NULL
1 row in set, 1 warning (0.0011 sec)
Note (code 1003): /* select#1 */ select json_unquote(json_extract(`test`.`my_restaurants`.`doc`,'$.name')) AS `name` from `test`.`my_restaurants` where (`test`.`my_restaurants`.`cuisine` = 'Italian')
type: refとなり、rowsの数も大幅に減少していることからインデックスが使用され、スキャン効率が向上していることがわかります。（MySQL Workbenchでより視覚的に分かりやすく確認することができます）
最後に
以上のように、MySQL8.0.13ではJSONデータをかなり扱いやすくなりました。"NoSQL + SQL"を大々的に推していることからもかなり気合を入れて開発されていることが感じられ、今後ドキュメントストアを導入する場合は選択肢として挙がってくるのではないでしょうか。