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

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


## アプリ実装
### Node-RED、ClearDBサービスの作成
1. Bluemix上で、Node-RED Starterを選択し、Node-REDのアプリを作成します。  
Bluemixへの登録、ログインは、下記URLから行ってください。  
https://console.bluemix.net/  
　　
2. Node-REDのアプリが開始中になったら、「ClearDB Managed MySQL Database」のサービスを作成し、Node-REDのアプリを再ステージングします。  
　　
3. 後ほどClearDBの認証情報を使用しますので、Bluemix上でClearDBの認証情報をメモしておきます。
アプリの画面から、「ランタイム」→「環境変数」を選択し、「VCAP_SERVICES」のClearDBの「name」、「hostname」、「port」、「username」、「password」をメモしておきます。
![cleardb1.png](https://qiita-image-store.s3.amazonaws.com/0/185050/b8ef9482-e8a7-2581-b2b8-ccc9f31de998.png)


### Virtual Box、Ubuntuのインストール
1. 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環境を構築　　
1. Virtual BOXを起動させます。  
仮想マシンを作成しますので、「名前」に「Ubuntu」を入れ、「次へ」をクリックします。  
![ubuntu1.png](https://qiita-image-store.s3.amazonaws.com/0/185050/5b4dca9d-bf45-f4f7-dee6-241be93e8f15.png)  
　　
2. 任意のメモリサイズを指定し、「次へ」をクリックします。  
![ubuntu2.png](https://qiita-image-store.s3.amazonaws.com/0/185050/994150da-3e62-d44e-202b-b5f492afd2e2.png)  
　　
3. 先ほどインストールしたUbuntuの仮想ハードディスクを使用しますので、  
「すでにある仮想ディスクファイルを使用する」を選択し、フォルダのアイコンを選択します。  
![ubuntu3.png](https://qiita-image-store.s3.amazonaws.com/0/185050/9a9c9d6b-14b7-0963-ffd6-3982a9962891.png)  
　　
4. 先ほど展開したUbuntuの仮想ハードディスクを選択し、「開く」をクリックします。
![ubuntu4.png](https://qiita-image-store.s3.amazonaws.com/0/185050/ba025369-65f9-8fd8-f33c-288ff2dab9c5.png)  
　　
5. Ubuntuの仮想ハードディスクが選択されていることを確認し、「作成」をクリックします。  
![ubuntu5.png](https://qiita-image-store.s3.amazonaws.com/0/185050/602677e2-d189-d57b-ee5d-a53390ea1c81.png)  
　　
6. 仮想マシンを作成できたら、「起動」をクリックし仮想マシンを起動させます。
![ubuntu6.png](https://qiita-image-store.s3.amazonaws.com/0/185050/8cb87a05-47c5-de90-1084-77ad9b9400ab.png)  
　　
7. 仮想マシンを起動させると、言語、地域、キーボードの設定画面が表示されるので、任意のものを指定し、「続ける」をクリックします。  
　　
8. 最後にコンピュータ名とパスワードを入力する画面がありますので、任意のものを指定し、「続ける」をクリックします。  
![ubuntu11.png](https://qiita-image-store.s3.amazonaws.com/0/185050/c487899e-dbc3-2b16-8b0b-d4cf56984eae.png)  
　　
9. 一通りの設定が完了すると、下記の画面が表示されます。　
![ubuntu14.png](https://qiita-image-store.s3.amazonaws.com/0/185050/422cebca-406a-07c3-1759-d1636398fd59.png)

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

```
 $sudo apt-get install mysql-client-core-5.6
```
![sql15.png](https://qiita-image-store.s3.amazonaws.com/0/185050/bcf02f60-5304-ad6e-d274-02fd2ccb036c.png)

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

### Ubuntuからブラウザ上のファイルを保存
1. 指定URLのファイルをLinux上に保存しますので、下記コマンドを実行してください。

```
 $wget https://raw.githubusercontent.com/dotnsf/name-samples/master/initial-utf8.sql
```
![sql5.png](https://qiita-image-store.s3.amazonaws.com/0/185050/2d0f6cb3-8ce0-f9d2-d70c-aade01203f6a.png)
initial-utf8.sqlというファイル名で、ダウンロードしたファイルが保存されました。
　　
2. 保存されたファイルの確認しますので、「ls」とコマンドをたたいてください。
![sql6.png](https://qiita-image-store.s3.amazonaws.com/0/185050/85f9dcc5-b54e-32de-2456-3e4c29bd9e13.png)
先ほどインストールしたinitial-utf8.sqlを確認できます。

### UbuntuからClearDBのDBにテーブル作成
1. sqlにログインしますので、下記を実行してください。
「HOSTNAME」と「USERANAME」は先ほどメモしたご自身のClearDBの認証情報に変更してください。

```
 $mysql -h HOSTNAME -u USERANAME -p
```
その後、PASSWORDの入力を求められますので、先ほどメモしたご自身のPASSWORDを入力してください。
![sql7_1.png](https://qiita-image-store.s3.amazonaws.com/0/185050/b07b2dd6-5113-34a5-be72-105d47b08034.png)
　　
2. clearDBのDBを指定しますので、下記を実行してください。
「DATABASENAME」は先ほどメモしたご自身のDATABASENAMEに変更してください。

```
 $use DATABASENAME
```
![sql8.png](https://qiita-image-store.s3.amazonaws.com/0/185050/b6d67f80-6133-0559-978c-01b5072f536f.png)
　　
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; 
```
![sql9.png](https://qiita-image-store.s3.amazonaws.com/0/185050/dcb1797c-33fd-c94c-3bc0-58afbf7ad126.png)
　　
4. ダウンロードしたファイルの内容をテーブルに保存しますので、下記コマンドを入力してください。
FILENAMEには、ブラウザからダウンロードしたファイル名を指定します。

```
 $source FILENAME
```
![sql13.png](https://qiita-image-store.s3.amazonaws.com/0/185050/f548867a-9f58-5eb5-082b-450de8a4a1ac.png)
　　
5. テーブルの中身を確認します。
今回は、rubyというスキーマの中で「あ」で始まるものを抽出しますので、下記コマンドを実行してください。

```
 $select * from names where ruby like 'あ%';
```
![sql10.png](https://qiita-image-store.s3.amazonaws.com/0/185050/def31a20-b5c3-8848-36b6-9861e410595a.png)




### Node-RED上にMySQLノードを追加
現在、MySQLノードは、標準で含まれていないため、インポートします。  

1. Node-REDアプリの再ステージングが完了したら、Node-REDの編集画面を開きます。  
　　
2. 右上の「≡」から「処理ノードの追加削除」を選択し、MySQLノードをインポートします。
![cleardb6.png](https://qiita-image-store.s3.amazonaws.com/0/185050/ef3b8753-29e9-c9c0-ee42-9f960e656be5.png)  
　　
3. Node-REDの左側に「処理ノードの追加削除」画面が現れます。  
「処理ノードの追加」をクリックし、「mysql」と検索します。  
検索すると、「node-red-mysql」が候補にあがりますので、「node-red-mysql」の「処理ノードを追加」をクリックしてください。  
![claerdb1.png](https://qiita-image-store.s3.amazonaws.com/0/185050/bf473a01-48f0-3fa9-b4a4-87df67c8b81e.png)  
　　
4. 下記の画面が表示されたら「install」をクリックしてください。
![claerdb2.png](https://qiita-image-store.s3.amazonaws.com/0/185050/d29a2dd7-24a2-e233-dfa1-5cb0bae5a355.png)  
　
5. 「node-red-mysql」の「処理ノードを追加」が、「追加しました」に変わったことを確認したら「完了」をクリックしてください。  
![cleardb4.png](https://qiita-image-store.s3.amazonaws.com/0/185050/ae55a6e4-b86e-e413-08d2-ac8822035417.png)  
　　
6. MySQLノードの追加が完了すると、「ストレージ」カテゴリーに「mysql」が追加されます。  
![cleardb5.png](https://qiita-image-store.s3.amazonaws.com/0/185050/8d6633cd-e007-db6f-960e-35318843e6bc.png)

これでMySQLノードを追加できました。
　　
### Node-REDのフローをインポート 
1. 右上の「≡」から「読み込み」→「クリップボード」を選択します。
![cleardb7.png](https://qiita-image-store.s3.amazonaws.com/0/185050/a7da85f8-e864-91bc-2a67-4dc77a621e67.png)
　　
2. 下記フローを全コピーし、貼り付け、「読み込み」をクリックしてください。

```ruby: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":""}]
```

3. インポートすると、下記のフローが読み込まれます。
![cleardb9.png](https://qiita-image-store.s3.amazonaws.com/0/185050/208b2f6e-50fd-1d1c-478b-9492589f2471.png)

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

1. MySQLのノードをダブルクリックし、鉛筆マークをクリックします。
![mysql1.png](https://qiita-image-store.s3.amazonaws.com/0/185050/a7308491-6e3f-6dde-9cce-f61f7d534b78.png)  
　　
2. 下記の認証情報を入力し、「更新」をクリックします。  
「Host」にCleaDBの「hostname」、  
「Port」にCleaDBの「port」、  
「User」にCleaDBの「username」、   
「Password」にCleaDBの「password」、  
「Database」にCleaDBの「name」  
![mysql2.png](https://qiita-image-store.s3.amazonaws.com/0/185050/06b8848d-9b19-2786-f079-f44031ccaddc.png)  
　　
3. 最後に下記の「完了」をクリックします。  
![cleardb10.png](https://qiita-image-store.s3.amazonaws.com/0/185050/19ed7c42-d2c3-dae1-0f01-8424c871c1f3.png)

4.　右上のデプロイボタンをクリックします。
![cleardb11.png](https://qiita-image-store.s3.amazonaws.com/0/185050/a4d081e9-9960-c394-39ea-2dcf72daa763.png)

### MySQL DBに格納したデータを抽出
1. injectノードの左のボタンをクリックすることで、injectノード内で定義した文字列の最後の文字で始まるデータを抽出し、debugタブに表示することができます。  
debugタブに表示する内容は、functionノードのコードを変更したり、injectノードで設定してある文字列を変更したりすることで、変更できます。
![cleardb12.png](https://qiita-image-store.s3.amazonaws.com/0/185050/d55d0f06-b189-c556-c145-c0bb48966128.png)
　　
2. injectノードで設定してある文字列を変更するには、injectノードをダブルクリックし、「Payload」の文字列を変更します。  
今回はひらがなで検索する仕様になっているため、ひらがなを入力してください。  
「Payload」の文字列を変更したら、「完了」をクリックし、右上のデプロイをクリックします。  
![cleardb13.png](https://qiita-image-store.s3.amazonaws.com/0/185050/dbeac8d0-554a-5172-e979-bc9f777f3071.png)

injectノードの左のボタンをクリックすると、debugタブへの表示内容が変わりました。
![cleardb14.png](https://qiita-image-store.s3.amazonaws.com/0/185050/f22f5763-0c2e-8cf1-4778-b968c765488f.png)