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

B1dca90d4b3ffd2ccd918774e1ba170d?s=47 hmatsu47
September 13, 2019

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

B1dca90d4b3ffd2ccd918774e1ba170d?s=128

hmatsu47

September 13, 2019
Tweet

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