Watson IoT Platform - IoTデータをSQLでアクセスする

初めてWatson IoT Platformを使ってみよう、というかたのためのガイドです。
Watson IoT Platformを使ってみるでIoTアプリを準備しました。
この記事はWatson IoT Platform - Node-RED - データベースに保存してみるの続編です。

#SQLが使えるデータベースへ
CloudantNoSQLDBはIoTに特有な非構造化データの格納に適したNoSQLデータベースです。そのためそのままではSQLでアクセスできません。
SQLでアクセス可能にするため、DashDBへウェアハウスします。

1. 他のBluemixサービスを使えるようにするの手順を参考にして、「データ＆分析」カテゴリーの「DashDB」サービスのインスタンスを作成します。DashDB-xxのような名称が自動的にアサインされます。

2. Watson IoT Platform - Node-RED - データベースに保存してみるで準備したCloudantNoSQLDBのコンソールを開きます。

3. IoTデータを保存したデータベース（例：iotdb）を確認し、画面左側の「Warehousing」というメニューを選択します。このメニューが表示されていない場合には、画面左上の三本線をクリックすることで表示されます。
   

4. ウェアハウスのコンソール画面で「Create a dashDB Warehouse」を選択します。

5. Bluemixへログインする際に使用したIBM IDを入力します。
   

6. 作成するウェアハウス名を適当に指定（例："iotwh"）し、対象とするデータベース名（例："iotdb"）の先頭の文字（例："i"）から対象データベースを指定します。
   

7. 「dashDB service instance」を選択し、前項で準備したDashDBサービスを選択します。

8. Warehouseが作成されるのを待ちます（しばらくかかります）。
   

9. dashDBインスタンスが作成されます。
   

#dashDBでSQLアクセスしてみます
dashDBで提供されている機能を使って保存されたIoTデータへアクセスしてみます。

1. オレンジ色の「Open in dashDB」ボタンを押してdashDBコンソールを開きます。
2. 「Go to your tables」ボタンもしくは画面左側のメニューの「Tables」ボタンを押します。
3. 「Create, drop, and work with tables」画面の「Table name」欄のプルダウンで対象のデータベース（例："IOTDB")を指定します。
   
4. 画面中央の「Schema」欄に表示されるスキーマ名（例：DASH016xxx）を控えておきます。この画面の「Browse Data」メニューから、データベース表内のデータを確認（閲覧）することが可能です。画面右側の「列の表示/非表示と順序付け」ボタンから表示される列を選択することも可能です。
5. 画面左側のメニューから「Run SQL」を選択します。
6. 表示されたSQL入力画面に下記を入力します。「Validate」ボタンを押してSQL構文にエラーが無い事を確認して、「Run」ボタンで実行します。

select count(*) from DASHxxxxx.iotdb;

• 予め用意されたSQL文は削除しても構いませんし、先頭に"--"を付加することでコメント化されます。
  

• 実行結果をスクロールダウンし、データベースに保存されているレコードの件数がCloudantコンソールに表示される件数と一致する事を確認します。
  

• 同様に、「Run SQL」画面からSQLを発行する事が可能です。

• 特定の行を挿入（登録）するには下記のようなSQLを使用します。ここではambienttempがゼロの行を挿入（登録）しています。

insert into DASHxxxx.SENSOR2DB (ambienttemp) values(0);

• 特定の行を削除するには下記のようなSQLを使用します。ここではambienttempがゼロの行を削除しています。

delete from DASHxxxxx.SENSOR2DB where ambienttemp=0;

#dashDBへNode-REDからSQLでアクセスしてみます

1. Node-REDに用意されたSQLノードでdashDBへアクセスする事も可能です。
2. 画面左側のstorageセクションのdashDBノードを使用します。
3. ダブルクリックして開き、対象となるdashDBサービスをプルダウンから指定し、SQL文を書いて実行します。

#dashDBのその他の機能
dashDBのその他の機能も見てみます。

1. 「Connect」メニューの「Connection Information」を開くと、JDBCアクセスなど外部のSQLツールからのアクセスに指定する情報が表示されます。
   

2. 「Analytics」メニューの「R Scripts」から、R Studioを使った解析が可能です。
   

#dashDBに表を用意して直接書き込む
dashDBにセンサー用の表を用意してNode-REDのフローから直接書き込む事も可能です。

1. dashDBのコンソールを開き、「Tables」 → 「Add Table」を選択します。
2. "Edit the DDL statements"の欄に下記をコピペし、"Run DDL"で実行します。

CC2650センサータグのデータを格納する表の例

CREATE TABLE "SENSOR2DB"
(
  "DATE" BIGINT,
  "DAY" BIGINT,
  "HOUR" BIGINT,
  "MINUTE" BIGINT,
  "MONTH" BIGINT,
  "SECOND" BIGINT,
  "YEAR" BIGINT,
  "ACCELX" VARCHAR(20),
  "ACCELY" VARCHAR(20),
  "ACCELZ" VARCHAR(20),
  "AMBIENTTEMP" VARCHAR(20),
  "GYROX" VARCHAR(20),
  "GYROY" VARCHAR(20),
  "GYROZ" VARCHAR(20),
  "HUMIDITY" VARCHAR(20),
  "LIGHT" VARCHAR(20),
  "MAGX" VARCHAR(20),
  "MAGY" VARCHAR(20),
  "MAGZ" VARCHAR(20),
  "OBJECTTEMP" VARCHAR(20)
);

. 3. 表が定義されたら、Node-REDのフローから書き込みます。

Node-REDのフローからdashDBへ直接書き込み

[{"id":"d9208db3.c058b","type":"ibmiot in","z":"26e29f59.971b9","authentication":"quickstart","apiKey":"","inputType":"evt","deviceId":"","applicationId":"","deviceType":"+","eventType":"+","commandType":"","format":"json","name":"IBM IoT","service":"quickstart","allDevices":"","allApplications":"","allDeviceTypes":true,"allEvents":true,"allCommands":"","allFormats":"","x":110,"y":100,"wires":[["d0b499ec.913da"]]},{"id":"d0b499ec.913da","type":"function","z":"26e29f59.971b9","name":"日付時刻の追加","func":"var d = new Date();\nvar localTime = d.getTime();\nvar localOffset = d.getTimezoneOffset() * 60000;\nvar utc = localTime + localOffset;\nvar offset = -9.0;\nvar result = utc - (3600000 * offset);\nvar date = parseInt(new Date(result) / 1000);\nvar dt = new Date(result);\n\nvar year = dt.getFullYear();\nvar month = dt.getMonth()+1;\nvar day = dt.getDate();\nvar hour = dt.getHours();\nvar minute = dt.getMinutes();\nvar second = dt.getSeconds();\nif (!msg.payload) {\n    msg.payload = { \"initial\": \"value\" };\n}\nmsg.date = {\n        \"date\": date,\n        \"year\": year,\n        \"month\": month,\n        \"day\": day,\n        \"hour\": hour,\n        \"minute\": minute,\n        \"second\": second\n}\nreturn msg;","outputs":1,"noerr":0,"x":275,"y":100,"wires":[["3eaf0b79.ef1934"]]},{"id":"3eaf0b79.ef1934","type":"function","z":"26e29f59.971b9","name":"表へ書き込む","func":"msg.payload = \n{ \n\tDATE : msg.date.date,\n\tDAY : msg.date.day,\n\tHOUR : msg.date.hour,\n\tMINUTE : msg.date.minute,\n\tMONTH : msg.date.month,\n\tSECOND : msg.date.second,\n\tYEAR : msg.date.year,\n\tACCELX : 0,\n\tACCELY : 0,\n\tACCELZ : 0,\n\tAMBIENTTEMP : 10,\n\tGYROX : 0,\n\tGYROY : 0,\n\tGYROZ : 0,\n\tHUMIDITY : 0,\n\tLIGHT : 0,\n    MAGX : 0,\n\tMAGY : 0,\n\tMAGZ : 0,\n\tOBJECTTEMP : 20\n};\nreturn msg;","outputs":1,"noerr":0,"x":475,"y":100,"wires":[["a53a6b28.95b568"]]},{"id":"82761d4.5b880e","type":"inject","z":"26e29f59.971b9","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":160,"wires":[["d0b499ec.913da"]]},{"id":"a53a6b28.95b568","type":"dashDB out","z":"26e29f59.971b9","service":"","table":"SENSOR2DB","name":"dashDBへ書き込み","x":690,"y":100,"wires":[]}]

. 4. Node-REDからアクセスできる事を確認します。

Node-REDを使ってdashDBから読み込み

[{"id":"127a1686.2cd8a1","type":"inject","z":"26e29f59.971b9","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":240,"wires":[["ea6b87c4.f3089"]]},{"id":"3ae7e667.6b1402","type":"debug","z":"26e29f59.971b9","name":"","active":true,"console":"false","complete":"false","x":530,"y":240,"wires":[]},{"id":"ea6b87c4.f3089","type":"dashDB in","z":"26e29f59.971b9","service":"","query":"select * from SENSOR2DB;","params":"","name":"dashDBから読み取り","x":320,"y":240,"wires":[["3ae7e667.6b1402"]]}]