X DevAPIを使って MySQL 8.0へアクセス Connector/Node.js版 MySQL Casual Talks vol.12　2019/9/13 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） https://qiita.com/hmatsu47 名古屋でWebインフラのお守り係（非DBA） ○ 会社ではAmazon Aurora（MySQL 5.6互換版）を 使ってます MySQL 8.0：趣味・遊び ○ 「MySQL 8.0の薄い本」を作って配っています https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ○ 現在8.0.17対応版第2刷です ○ 今日も持ってきています 2

【補足】「MySQL 8.0の薄い本」の使い方 「電子版PDF」をメインに！ ○ 「MySQL 8.0の薄い本」＝リンク集＆サンプル集です ○ サンプルもコピペしやすいです（途中の改行に注意！） 印刷したものを使うときは章末のQRコードを活用してください ○ Webリンク集に飛びます 巻末の索引を活用してください ○ キーワードを眺めていると新たな発見があるかも？ 3

今回のテーマ X DevAPIを使ってMySQL 8.0へアクセス ○ ドキュメントDBのコレクションへアクセス ○ RDBテーブルへアクセス Connector/Node.jsを使う ○ 非同期処理を試してみます 8.0.17でサポートされたMulti-Valued Indexesを使う ○ Amazon Rekognitionで画像分析を行った結果データからラベルの名前 のみを抽出・配列化してMySQL 8.0のJSON列に格納します 4

【参考】X DevAPI X Protocolを使う（≠MySQL Protocol） TCP:33060（デフォルト） CRUDオペレーションとSQLアクセス 非同期I/O処理 ○ X DevAPI User Guide https://dev.mysql.com/doc/x-devapi-userguide/en/ ○ MySQL Connector/Node.js X DevAPI Reference https://dev.mysql.com/doc/dev/connector-nodejs/8.0/ 5

【参考】X DevAPI X Protocolを使う（≠MySQL Protocol） TCP:33060（デフォルト） CRUDオペレーションとSQLアクセス 非同期I/O処理 ○ X DevAPI User Guide https://dev.mysql.com/doc/x-devapi-userguide/en/ ○ MySQL Connector/Node.js X DevAPI Reference https://dev.mysql.com/doc/dev/connector-nodejs/8.0/ 6 ちなみに、8.0.16ではMySQL Protocolのほうも 非同期I/F対応C APIのサポートがアナウンスされました 28.7.12 C API Asynchronous Interface（MySQL 8.0 Reference Manual） https://dev.mysql.com/doc/refman/8.0/en/c-api-asynchronous-interface.html

【参考】コレクションとテーブル コレクション　　　　DB　　　　　テーブル　　 ドキュメント（JSON列） ドキュメント（JSON列） ドキュメント（JSON列） 行（列, 列, …） 行（列, 列, …） 行（列, 列, …） 7

【参考】コレクションとテーブル コレクション　　　　DB　　　　　テーブル　　 ドキュメント（JSON列） ドキュメント（JSON列） ドキュメント（JSON列） 行（列, 列, …） 行（列, 列, …） 行（列, 列, …） 8 ベースは同じ「テーブル」です （コードから違う呼び出し方をするだけ） ただし、コレクションは「doc」という 名前のJSON列（とインデックス）だけを 持つテーブルです

【参考】Amazon Rekognition（画像分析） Diaper＝おむつの信頼スコア99.9%（yoku0825さんおむつ説） 信頼スコアが高い順にラベルの名前を表示 9

【参考】Amazon Rekognition（画像分析） { "Labels": [ { "Name": "Electronics", "Confidence": 99.74884033203125, "Instances": [], "Parents": [] }, { "Name": "Computer", "Confidence": 99.74884033203125, "Instances": [], "Parents": [ { "Name": "Electronics" } ] }, （以降、省略） レスポンス例（当日取り忘れたので翌日やってみたら、おむつスコアが若干下がりました） 10

おことわり 内容は無保証かつ所属先とは無関係です ○ 普段Node.jsは使っていません（個人・所属先とも） ○ MySQL 8.0も所属先では使っていません 認証・認可、入力値チェック、詳細なエラー処理などは実装され ていません ○ このままプロダクトコードに流用しないように（ダメ！） サーバの設定等の解説はしません ○ Node.js・Nginx・MySQL ○ CORS ほか 11

作ったもの／内容 Amazon Rekognitionを使って画像のラベリングを行う ○ 信頼スコア60以上・上位10個までを抽出 以下をドキュメントDBのコレクションimage_labelingに保存 ○ 画像の保存ファイル名（filename） ○ 元のファイル名（originalname） ○ 抽出ラベル名の配列（labels） RDBテーブルlabelsにラベル名を分解して保存 ○ ラベルセレクタ用（label） 12

function convertArray(detectLabels) { let resultArray = []; detectLabels.forEach(element => resultArray.push(element.Name)); return resultArray; } // for Label selector (convert resultSet format) function convertResultSet(rows) { let resultArray = []; rows.forEach((element, index) => { if (index == 0) { resultArray.push(element); } }); return resultArray[0]; } // for Data loader (convert resultSet format) function convertResultSetData(rows) { let resultArray = []; rows.forEach(element => resultArray.push(element)); return resultArray[0]; } // for Initiator (remove image files) function removeFiles() { const targetFiles = fs.readdirSync(filePath); targetFiles.forEach(targetFile => fs.unlinkSync(filePath + targetFile)); } // for HTTP response ('OK':HTTP 200) function responseOK(res) { responseResult(res, {"message": "OK"}); } // for HTTP response (with result:HTTP 200) function responseResult(res, result) { res.send(result); } // for HTTP response (error:HTTP 400) function responseError400(error, res, message) { responseError(error, res, message, 400); } // for HTTP response (error:HTTP 500) function responseError500(error, res, message) { responseError(error, res, message, 500); } // for HTTP response (error) function responseError(error, res, message, code) { console.log('[ERROR]', error); const express = require('express'); const multer = require("multer"); const bodyParser = require('body-parser'); const AWS = require('aws-sdk'); const fs = require('fs') const mysqlx = require('@mysql/xdevapi'); const filePath = '/dev/nginx-1.17.2/html/images/'; const app = express(); // for POST request (use body-perser) app.use(bodyParser.urlencoded({ extended: true })); app.use(bodyParser.json()); // for AWS Rekognition (convert file -> base64) function getBase64BufferFromFile(filename) { return (new Promise((resolve, reject) => { fs.readFile(filename, 'base64', (err, data) => { if (err) return reject(err); resolve(new Buffer.from(data, 'base64')); }); })); } // Amazon Rekognition (detect labels) AWS.config.update({ region: 'ap-northeast-1' }); const rekognition = new AWS.Rekognition({ apiVersion: '2016-06-27' }); function detectLabelsFromBytes(bytes, maxLabels, minConfidence) { const params = { Image: { Bytes: bytes }, MaxLabels: typeof maxLabels !== 'undefined' ? maxLabels : 100, MinConfidence: typeof minConfidence !== 'undefined' ? minConfidence : 60.0 }; return (new Promise((resolve, reject) => { rekognition.detectLabels(params, (err, data) => { if (err) return reject(err); resolve(data); }); })); } // for MySQL document (convert array format) 作ったもの／内容 Amazon Rekognitionを使って画像のラベリングを行う ○ 信頼スコア60以上・上位10個までを抽出 以下をドキュメントDBのコレクションimage_labelingに保存 ○ 画像の保存ファイル名（filename） ○ 元のファイル名（originalname） ○ 抽出ラベル名の配列（labels） RDBテーブルlabelsにラベル名を分解して保存 ○ ラベルセレクタ用（label） 13

作ったもの／動作環境・構成 Node.js 12.8.0（バックエンドサーバ実行環境） ○ Express 4.17.1（Web APIサーバ） ○ Connector/Node.js 8.0.17（DBコネクタ） Nginx 1.17.2（Webサーバ兼API用リバースプロキシサーバ） MySQL Community Server 8.0.17（DBサーバ） ※フロントエンドでVue.js 2.6.10＋axios 0.19.0を使用 ○ GitHubリポジトリ： https://github.com/hmatsu47/rekognitionlabels 14

作ったもの／デモ 15

接続パラメータ RFC 3986 URI形式で記述 ほかに、以下の形式で記述可（リンク先を参照） ○ "unified" connection string ○ session configuration object https://dev.mysql.com/doc/dev/connector-nodejs/8.0/tutorial-Connecting_to_a_Server.html ※接続タイムアウト・フェイルオーバーの指定はここで行います // MySQL X DevAPI const schemaName = 'xdevtest'; const collectionName = 'image_labeling'; const labelTableName = 'labels'; const connectParam = 'mysqlx://xdevuser:XDevAPIUser8.0@localhost:33060/' + schemaName; 16

接続方法 通常の場合（今回のコードもこちら） プーリングを使う場合 【注】試していません（マニュアルにはPromiseパターンの例が載っています） const session = await mysqlx.getSession(connectParam); 17 const client = await mysqlx.getClient(connectParam, { pooling: { maxSize: 1, maxIdleTime: 1000, queueTimeout: 2000}}); const session = await client.getSession(); // ここにsessionを使って処理を書く await client.close();

非同期処理とエラー処理 async/awaitで非同期処理（PromiseパターンでもOK） ※個別にエラー処理をするときはawait 関数名().catch(エラー処理)を使います ○ async関数においてtry/catchではなくawait/catchパターンを活用する https://qiita.com/akameco/items/cc73afcdb5ac5d0774bc (async () => { try { // ここに実行する処理を記述・完了まで待って次へ進むものは await を指定 } catch(error) { // ここにエラー処理を記述 } })(); 18

3つのDBアクセス方式（注：勝手に命名しました） コレクション方式 ○ ドキュメントDB用 collection.find().groupBy().sort().limit().bind().execute() テーブル方式 ○ RDBテーブル用 table.select().where().groupBy().orderBy().limit().bind().execute() SQL方式 ○ 上2つで表現できないもの sql().bind().execute() 19

[1]:コレクション方式（ドキュメントDB用） 例：Amazon Rekognitionから受け取ったデータの必要部分 （Labels.Name）だけを配列に変換し、コレクションに追加 ○ collection.add().execute() で、コレクションにドキュメントが追加されます // Store Labels in MySQL Document Store const session = await mysqlx.getSession(connectParam); const collection = await session.getSchema(schemaName).getCollection(collectionName); const labels = convertArray(found.Labels); const dummy = await collection.add({ "filename": req.file.filename, "originalname": req.file.originalname, "labels": labels }).execute(); 20

[2]:テーブル方式（RDBテーブル用） 例：1ラベル＝1行に分解してRDBテーブルに保存 ○ table.insert().values().execute() ここではPromise.all()を使って各行の挿入を並列処理している ○ そのため、分解後の行の挿入順序は保証されません ○ 順序を保証したい場合はfor()などを使って直列処理します // Store Labels (for Selector) in MySQL Table const table = await session.getSchema(schemaName).getTable(labelTableName); await Promise.all( labels.map(async label => await table.insert('label') .values(label) .execute())); 21

[2]:テーブル方式（RDBテーブル用）・余談 これ、ダメなんです… ○ 「*」がエラーになります ○ 実際のコードでは「`label`」を入れています const rows = []; const session = await mysqlx.getSession(connectParam); const table = await session.getSchema(schemaName).getTable(labelTableName); const dummy = await table.select('`label`', 'COUNT(*) AS `count`') .groupBy('`label`') .orderBy('`count` DESC', '`label` ASC') .limit(typeof req.body.numof !== 'undefined' ? req.body.numof : 100) .execute(row => rows.push(convertResultSet(row))); 22

[2’]:テーブル方式でコレクションを扱う 例：Multi-Valued Indexesを使ってコレクションからAND検索 ○ table.select(`doc`).where()[.limit()].execute() ○ AND検索では.where(“JSON文字列 in `doc`->’$.列名’”)を使います ○ OR検索では.where(“JSON文字列 overlaps `doc`->’$.列名’”)です ○ JSON文字列はバインドできませんでした const rows = []; const session = await mysqlx.getSession(connectParam); const table = await session.getSchema(schemaName).getTable(collectionName); const dummy = await table.select('`doc`') .where(JSON.stringify(req.body.labels) + " in `doc`->'$.labels'") .limit(typeof req.body.numof !== 'undefined' ? req.body.numof : 100) .execute(row => rows.push(convertResultSetData(row))); 23

[2’]:テーブル方式でコレクションを扱う・余談 当初、こう記述したところ、エラーが… ○ 「,」がNGでした ○ 一旦SQL方式に直して完成させましたが、後で「in」を見つけたので、 再度テーブル方式に戻しました const rows = []; const session = await mysqlx.getSession(connectParam); const table = await session.getSchema(schemaName).getTable(collectionName); const dummy = await table.select('`doc`') .where(“JSON_CONTAINS(`doc`->'$.labels', CAST('[" + JSON.stringify(req.body.labels) + "]' AS JSON))”) .limit(typeof req.body.numof !== 'undefined' ? req.body.numof : 100) .execute(row => rows.push(convertResultSetData(row))); 24

[3]:SQL方式 例：RDBテーブル作成 ○ session.sql().execute() // Create MySQL Table const query = 'CREATE TABLE `' + schemaName + '`.`' + labelTableName + '` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `label` VARCHAR(100) NOT NULL, INDEX `label` (`label`)' + ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'; const dummy = await session.sql(query) .execute(); 25

リザルトセットのサンプル コレクション方式 ○ まあ、普通です テーブル方式・SQL方式 ○ 当然といえば当然の形ですが、意外と扱いにくいかも… 【注】このアプリケーションのリザルトセットの例ではありません [{"_id": "abcdef01", “Label”: “hoge”, “Score”: 99.74}, {"_id": "abcdef02", “Label”: “fuga”, “Score”: 91.32}, …] [[“hoge”, 99.74], [“fuga”, 91.32], …] 26

良さげな点 コレクション方式・テーブル方式ともシンプルな形で書ける ○ 余計な下準備／後始末などは不要です ○ 冗長な書き方をしなくて良いです 非同期処理でも割と楽に書ける ○ Promiseに対応しています（最近では当然といえば当然ですが） ○ Promiseに対応＝async/awaitにも対応、です 【注】MySQL Shellは非同期処理に対応していません 27

いまいちな点 .find()で指定できるパターンが限られている .select()や.where()で指定できるパターンも限られている ○ 「*」「,」などが使えません ○ COUNT(*)のような書き方ができないのは痛いです .bind()でバインドできる対象が限られている リファレンスマニュアルに載っていない（？）情報が多い ○ .where()の「in」「overlaps」などリリースノートには書いてあるのに （「InsideMySQL.com」を読んで初めて知りました） 28

気になる点 一旦MySQL Server has gone away.になった後の再接続性 ○ 一度だけ、MySQLプロトコルでは普通に再接続できるのにXプロトコル で全く接続できなくなりました ○ MySQL Serverを再起動するまでずっと不安定な状態が続きました 29

まとめ 非同期処理も含めて割と簡単に書ける コレクション方式・テーブル方式で書けるパターンが限られる リファレンスマニュアルの情報が不足気味 もしかすると少し不安定かも？（確証なし） 30

【参考URL】 InsideMySQL.com ○ Working with result sets in Connector/Node.js リザルトセットについて https://insidemysql.com/working-with-result-sets-in-connector-node-js/ ○ MySQL Connector/Node.js 8.0.8-dmr has been released IN（JSON_CONTAINS()） https://insidemysql.com/mysql-connectornode-js-8-0-8-dmr-has-been-released/ ○ MySQL Connector/Node.js 8.0.17 has been released overlaps（JSON_OVERLAPS()） https://insidemysql.com/mysql-connector-node-js-8-0-17-has-been-released/ 31

【参考URL】 Qiita（hmatsu47） ○ Node.js から X DevAPI を使って MySQL 8.0.17 のドキュメントデータ ベースと RDB テーブルにアクセスする https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e ○ MySQL Connector/J 8.0.15 で X DevAPI を使ってドキュメントデータ ベース操作をしてみる https://qiita.com/hmatsu47/items/2de98cd0c9472e72a52a ○ MySQL 8.0.17 で Multi-Valued Indexes を試す https://qiita.com/hmatsu47/items/3e49a473bc36aeefc706 32