Node-REDでMySQL DBに格納したデータを抽出するアプリを作ってみた #MySQL

はじめに

Node-REDでMySQL DBのデータを抽出できるフローを作成したいと思います。
WindowsにMySQLをインストールした際には、MySQLにデータを格納した後、Node-REDからデータを抽出した際には、日本語のデータが文字化けしてしまいました。
そのため、今回は、Virtual Box上にUbuntu環境を構築し、UbuntuにMySQLをインストールします。

アプリ実装

Node-RED、ClearDBサービスの作成

Bluemix上で、Node-RED Starterを選択し、Node-REDのアプリを作成します。
Bluemixへの登録、ログインは、下記URLから行ってください。
https://console.bluemix.net/
　　
Node-REDのアプリが開始中になったら、「ClearDB Managed MySQL Database」のサービスを作成し、Node-REDのアプリを再ステージングします。
　　
後ほどClearDBの認証情報を使用しますので、Bluemix上でClearDBの認証情報をメモしておきます。アプリの画面から、「ランタイム」→「環境変数」を選択し、「VCAP_SERVICES」のClearDBの「name」、「hostname」、「port」、「username」、「password」をメモしておきます。

Virtual Box、Ubuntuのインストール

Virtual BOXのインストール
Virtual BOXのインストールは下記URLから、行います。
https://www.virtualbox.org/

　インストール方法は、下記URLを参考にしてください。
　https://eng-entrance.com/virtualbox-install#Virtualbox
　　
2. Ubuntuのインストール
2-1. Ubuntuのインストールは下記URLから、最新バージョンの仮想ハードディスクをインストールします。
https://www.ubuntulinux.jp/download/ja-remix-vhd

2-2.インストールしたzipファイルは展開しておきます。　　

Virtual Box上にUbuntu環境を構築　　

Virtual BOXを起動させます。
仮想マシンを作成しますので、「名前」に「Ubuntu」を入れ、「次へ」をクリックします。

　　
任意のメモリサイズを指定し、「次へ」をクリックします。

　　
先ほどインストールしたUbuntuの仮想ハードディスクを使用しますので、
「すでにある仮想ディスクファイルを使用する」を選択し、フォルダのアイコンを選択します。

　　
先ほど展開したUbuntuの仮想ハードディスクを選択し、「開く」をクリックします。
　　
Ubuntuの仮想ハードディスクが選択されていることを確認し、「作成」をクリックします。

　　
仮想マシンを作成できたら、「起動」をクリックし仮想マシンを起動させます。
　　
仮想マシンを起動させると、言語、地域、キーボードの設定画面が表示されるので、任意のものを指定し、「続ける」をクリックします。
　　
最後にコンピュータ名とパスワードを入力する画面がありますので、任意のものを指定し、「続ける」をクリックします。

　　
一通りの設定が完了すると、下記の画面が表示されます。

Ubuntuにsqlをインストール

Ubuntuの端末を開き、「mysql」とコマンドでたたきます。インストールはまだしていませんので、下記を実行いたします。今回はmysql-client-core-5.6をインストールしますが、そのときの最新バージョンをインストールしてください。

 $sudo apt-get install mysql-client-core-5.6

これで、sqlのインストールが完了しました。

Ubuntuからブラウザ上のファイルを保存

指定URLのファイルをLinux上に保存しますので、下記コマンドを実行してください。

 $wget https://raw.githubusercontent.com/dotnsf/name-samples/master/initial-utf8.sql

initial-utf8.sqlというファイル名で、ダウンロードしたファイルが保存されました。
　　
2. 保存されたファイルの確認しますので、「ls」とコマンドをたたいてください。

先ほどインストールしたinitial-utf8.sqlを確認できます。

UbuntuからClearDBのDBにテーブル作成

sqlにログインしますので、下記を実行してください。「HOSTNAME」と「USERANAME」は先ほどメモしたご自身のClearDBの認証情報に変更してください。

 $mysql -h HOSTNAME -u USERANAME -p

その後、PASSWORDの入力を求められますので、先ほどメモしたご自身のPASSWORDを入力してください。

　　
2. clearDBのDBを指定しますので、下記を実行してください。
「DATABASENAME」は先ほどメモしたご自身のDATABASENAMEに変更してください。

 $use DATABASENAME

　　
3. テーブル作成しますので、下記を実行してください。

 $create table names( id int primary key auto_increment, name varchar(50), ruby varchar(50), category_id int, url varchar(256) ) default charset=utf8;

　　
4. ダウンロードしたファイルの内容をテーブルに保存しますので、下記コマンドを入力してください。
FILENAMEには、ブラウザからダウンロードしたファイル名を指定します。

 $source FILENAME

　　
5. テーブルの中身を確認します。
今回は、rubyというスキーマの中で「あ」で始まるものを抽出しますので、下記コマンドを実行してください。

 $select * from names where ruby like 'あ%';

Node-RED上にMySQLノードを追加

現在、MySQLノードは、標準で含まれていないため、インポートします。

Node-REDアプリの再ステージングが完了したら、Node-REDの編集画面を開きます。
　　
右上の「≡」から「処理ノードの追加削除」を選択し、MySQLノードをインポートします。
　　
Node-REDの左側に「処理ノードの追加削除」画面が現れます。
「処理ノードの追加」をクリックし、「mysql」と検索します。
検索すると、「node-red-mysql」が候補にあがりますので、「node-red-mysql」の「処理ノードを追加」をクリックしてください。

　　
下記の画面が表示されたら「install」をクリックしてください。
　
「node-red-mysql」の「処理ノードを追加」が、「追加しました」に変わったことを確認したら「完了」をクリックしてください。

　　
MySQLノードの追加が完了すると、「ストレージ」カテゴリーに「mysql」が追加されます。

これでMySQLノードを追加できました。
　　

Node-REDのフローをインポート

右上の「≡」から「読み込み」→「クリップボード」を選択します。　　
下記フローを全コピーし、貼り付け、「読み込み」をクリックしてください。

Node-REDにインポートするフロー

[{"id":"2ac1c4a2.b29efc","type":"mysql","z":"51963087.fa92a","mydb":"26f13f7c.b0e8b8","name":"","x":194.40475463867188,"y":241.54762268066406,"wires":[["c51792d2.183dd","7e5fa84d.762bf8","8c08d347.280c3","1e80f5f0.0b601a","e6985dbf.dded2","e356872.dd26578"]]},{"id":"c51792d2.183dd","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"payload","x":879.4047546386719,"y":230.04762268066406,"wires":[]},{"id":"cb8852a5.016258","type":"inject","z":"51963087.fa92a","name":"","topic":"","payload":"さとう","payloadType":"str","repeat":"","crontab":"","once":false,"x":125.40475463867188,"y":62.54762268066406,"wires":[["fb5008da.56a0a8","2d05fde5.c99fe2"]]},{"id":"7e5fa84d.762bf8","type":"function","z":"51963087.fa92a","name":"0番目のIDを抽出","func":"next_word = msg.payload[0];\nmsg.payload = next_word.id;\nreturn msg;","outputs":1,"noerr":0,"x":523.4047546386719,"y":297.54762268066406,"wires":[["a172344c.521f68"]]},{"id":"5fe6d4d5.1c1e5c","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"false","x":729.4047546386719,"y":50.54762268066406,"wires":[]},{"id":"fb5008da.56a0a8","type":"function","z":"51963087.fa92a","name":"一番後ろの文字抽出","func":"word = msg.payload.substr(msg.payload.length-1);\nmsg.payload = word;\nreturn msg;","outputs":1,"noerr":0,"x":370.4047546386719,"y":51.04762268066406,"wires":[["5fe6d4d5.1c1e5c"]]},{"id":"2d05fde5.c99fe2","type":"function","z":"51963087.fa92a","name":"一番後ろの文字で始まるデータを抽出","func":"word = msg.payload.substr(msg.payload.length-1);\nmsg.payload = word;\nmsg.topic = \"Select * from names where ruby like '\" + word + \"%';\"\nreturn msg;","outputs":1,"noerr":0,"x":416.4047546386719,"y":121.04762268066406,"wires":[["2ac1c4a2.b29efc"]]},{"id":"a172344c.521f68","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"false","x":884.4047546386719,"y":295.54762268066406,"wires":[]},{"id":"8c08d347.280c3","type":"function","z":"51963087.fa92a","name":"IDをカンマ(,)区切りですべて抽出","func":"var next_id = 0;\nvar next_word = msg.payload;\nfor(var i = 0; i<next_word.length; i++){\n    next_id = next_id + \",\" + next_word[i].id;\n}\nmsg.payload = next_id;\nreturn msg;","outputs":1,"noerr":0,"x":583.4047546386719,"y":456.54762268066406,"wires":[["ccfc4ff9.ee761"]]},{"id":"ccfc4ff9.ee761","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"false","x":894.4047546386719,"y":454.54762268066406,"wires":[]},{"id":"1e80f5f0.0b601a","type":"function","z":"51963087.fa92a","name":"IDを配列に格納","func":"var next_id = [];\nvar next_word = msg.payload;\nfor(var i = 0; i<next_word.length; i++){\n    next_id[i] = next_word[i].id;\n}\nmsg.payload = next_id;\nreturn msg;","outputs":1,"noerr":0,"x":530.4047546386719,"y":511.54762268066406,"wires":[["55f54255.155184"]]},{"id":"55f54255.155184","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"false","x":890.4047546386719,"y":510.54762268066406,"wires":[]},{"id":"e356872.dd26578","type":"function","z":"51963087.fa92a","name":"0番目の名前を抽出","func":"next_ruby = msg.payload[0];\nmsg.payload = next_ruby.ruby;\nreturn msg;","outputs":1,"noerr":0,"x":538.4047546386719,"y":347.54762268066406,"wires":[["cf7d2eca.a27ee8"]]},{"id":"cf7d2eca.a27ee8","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"false","x":885.4047546386719,"y":342.54762268066406,"wires":[]},{"id":"e6985dbf.dded2","type":"function","z":"51963087.fa92a","name":"該当するID数を抽出","func":"var next_id;\nvar next_word = msg.payload;\nmsg.payload = next_word.length;\nreturn msg;","outputs":1,"noerr":0,"x":542.4047546386719,"y":402.54762268066406,"wires":[["be3c3b9e.35c78"]]},{"id":"be3c3b9e.35c78","type":"debug","z":"51963087.fa92a","name":"","active":true,"console":"false","complete":"false","x":888.4047546386719,"y":398.54762268066406,"wires":[]},{"id":"26f13f7c.b0e8b8","type":"MySQLdatabase","z":"","host":"","port":"","db":"","tz":""}]

インポートすると、下記のフローが読み込まれます。

MySQLの認証情報の設定　

Node-REDのMySQLノードをダブルクリックし、ClearDBの認証情報を設定します。

MySQLのノードをダブルクリックし、鉛筆マークをクリックします。
　　
下記の認証情報を入力し、「更新」をクリックします。
「Host」にCleaDBの「hostname」、
「Port」にCleaDBの「port」、
「User」にCleaDBの「username」、
「Password」にCleaDBの「password」、
「Database」にCleaDBの「name」

　　
最後に下記の「完了」をクリックします。

4.　右上のデプロイボタンをクリックします。

MySQL DBに格納したデータを抽出

injectノードの左のボタンをクリックすることで、injectノード内で定義した文字列の最後の文字で始まるデータを抽出し、debugタブに表示することができます。
debugタブに表示する内容は、functionノードのコードを変更したり、injectノードで設定してある文字列を変更したりすることで、変更できます。　　
injectノードで設定してある文字列を変更するには、injectノードをダブルクリックし、「Payload」の文字列を変更します。
今回はひらがなで検索する仕様になっているため、ひらがなを入力してください。
「Payload」の文字列を変更したら、「完了」をクリックし、右上のデプロイをクリックします。

injectノードの左のボタンをクリックすると、debugタブへの表示内容が変わりました。