X DevAPIを使ってMySQL 8.0へアクセス

Transcript

1. X DevAPIを使って MySQL 8.0へアクセス Connector/Node.js版 MySQL Casual Talks vol.12　2019/9/13 まつひさ（hmatsu47）
2. 自己紹介 松久裕保（@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
3. 【補足】「MySQL 8.0の薄い本」の使い方 「電子版PDF」をメインに！ ◦ 「MySQL 8.0の薄い本」＝リンク集＆サンプル集です ◦ サンプルもコピペしやすいです（途中の改行に注意！） 印刷したものを使うときは章末のQRコードを活用してください ◦ Webリンク集に飛びます 巻末の索引を活用してください ◦ キーワードを眺めていると新たな発見があるかも？ 3
4. 今回のテーマ X DevAPIを使ってMySQL 8.0へアクセス ◦ ドキュメントDBのコレクションへアクセス ◦ RDBテーブルへアクセス Connector/Node.jsを使う ◦ 非同期処理を試してみます 8.0.17でサポートされたMulti-Valued Indexesを使う ◦ Amazon Rekognitionで画像分析を行った結果データからラベルの名前 のみを抽出・配列化してMySQL 8.0のJSON列に格納します 4
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/ 5
6. 【参考】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
7. 【参考】コレクションとテーブル コレクション　　　　DB　　　　　テーブル　　 ドキュメント（JSON列） ドキュメント（JSON列） ドキュメント（JSON列） 行（列, 列, …） 行（列, 列, …） 行（列, 列, …） 7
8. 【参考】コレクションとテーブル コレクション　　　　DB　　　　　テーブル　　 ドキュメント（JSON列） ドキュメント（JSON列） ドキュメント（JSON列） 行（列, 列, …） 行（列, 列, …） 行（列, 列, …） 8 ベースは同じ「テーブル」です （コードから違う呼び出し方をするだけ） ただし、コレクションは「doc」という 名前のJSON列（とインデックス）だけを 持つテーブルです
9. 【参考】Amazon Rekognition（画像分析） Diaper＝おむつの信頼スコア99.9%（yoku0825さんおむつ説） 信頼スコアが高い順にラベルの名前を表示 9
10. 【参考】Amazon Rekognition（画像分析） { "Labels": [ { "Name": "Electronics", "Confidence": 99.74884033203125, "Instances": [], "Parents": [] }, { "Name": "Computer", "Confidence": 99.74884033203125, "Instances": [], "Parents": [ { "Name": "Electronics" } ] }, （以降、省略） レスポンス例（当日取り忘れたので翌日やってみたら、おむつスコアが若干下がりました） 10
11. おことわり 内容は無保証かつ所属先とは無関係です ◦ 普段Node.jsは使っていません（個人・所属先とも） ◦ MySQL 8.0も所属先では使っていません 認証・認可、入力値チェック、詳細なエラー処理などは実装され ていません ◦ このままプロダクトコードに流用しないように（ダメ！） サーバの設定等の解説はしません ◦ Node.js・Nginx・MySQL ◦ CORS ほか 11
12. 作ったもの／内容 Amazon Rekognitionを使って画像のラベリングを行う ◦ 信頼スコア60以上・上位10個までを抽出 以下をドキュメントDBのコレクションimage_labelingに保存 ◦ 画像の保存ファイル名（filename） ◦ 元のファイル名（originalname） ◦ 抽出ラベル名の配列（labels） RDBテーブルlabelsにラベル名を分解して保存 ◦ ラベルセレクタ用（label） 12
13. 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
14. 作ったもの／動作環境・構成 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. 作ったもの／デモ 15
16. 接続パラメータ 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
17. 接続方法 通常の場合（今回のコードもこちら） プーリングを使う場合 【注】試していません（マニュアルには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();
18. 非同期処理とエラー処理 async/awaitで非同期処理（PromiseパターンでもOK） ※個別にエラー処理をするときはawait 関数名().catch(エラー処理)を使います ◦ async関数においてtry/catchではなくawait/catchパターンを活用する https://qiita.com/akameco/items/cc73afcdb5ac5d0774bc (async () => { try { // ここに実行する処理を記述・完了まで待って次へ進むものは await を指定 } catch(error) { // ここにエラー処理を記述 } })(); 18
19. 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
20. [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
21. [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
22. [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
23. [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
24. [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
25. [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
26. リザルトセットのサンプル コレクション方式 ◦ まあ、普通です テーブル方式・SQL方式 ◦ 当然といえば当然の形ですが、意外と扱いにくいかも… 【注】このアプリケーションのリザルトセットの例ではありません [{"_id": "abcdef01", “Label”: “hoge”, “Score”: 99.74}, {"_id": "abcdef02", “Label”: “fuga”, “Score”: 91.32}, …] [[“hoge”, 99.74], [“fuga”, 91.32], …] 26
27. 良さげな点 コレクション方式・テーブル方式ともシンプルな形で書ける ◦ 余計な下準備／後始末などは不要です ◦ 冗長な書き方をしなくて良いです 非同期処理でも割と楽に書ける ◦ Promiseに対応しています（最近では当然といえば当然ですが） ◦ Promiseに対応＝async/awaitにも対応、です 【注】MySQL Shellは非同期処理に対応していません 27
28. いまいちな点 .find()で指定できるパターンが限られている .select()や.where()で指定できるパターンも限られている ◦ 「*」「,」などが使えません ◦ COUNT(*)のような書き方ができないのは痛いです .bind()でバインドできる対象が限られている リファレンスマニュアルに載っていない（？）情報が多い ◦ .where()の「in」「overlaps」などリリースノートには書いてあるのに （「InsideMySQL.com」を読んで初めて知りました） 28
29. 気になる点 一旦MySQL Server has gone away.になった後の再接続性 ◦ 一度だけ、MySQLプロトコルでは普通に再接続できるのにXプロトコル で全く接続できなくなりました ◦ MySQL Serverを再起動するまでずっと不安定な状態が続きました 29
30. まとめ 非同期処理も含めて割と簡単に書ける コレクション方式・テーブル方式で書けるパターンが限られる リファレンスマニュアルの情報が不足気味 もしかすると少し不安定かも？（確証なし） 30
31. 【参考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
32. 【参考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