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

初めてWatson IoT Platformを使ってみよう、というかたのためのガイドです。
[Watson IoT Platformを使ってみる](http://qiita.com/egplnt/items/af867711a7191923b2ff)でIoTアプリを準備しました。
この記事は[Watson IoT Platform - Node-RED - データベースに保存してみる](http://qiita.com/egplnt/items/1d436ae729e80f95d61f)の続編です。

#SQLが使えるデータベースへ
CloudantNoSQLDBはIoTに特有な非構造化データの格納に適したNoSQLデータベースです。そのためそのままではSQLでアクセスできません。
SQLでアクセス可能にするため、DashDBへウェアハウスします。
1. [他のBluemixサービスを使えるようにする](http://qiita.com/egplnt/items/0924dd0c31993826d217)の手順を参考にして、「データ＆分析」カテゴリーの「DashDB」サービスのインスタンスを作成します。`DashDB-xx`のような名称が自動的にアサインされます。
2. [Watson IoT Platform - Node-RED - データベースに保存してみる](http://qiita.com/egplnt/items/1d436ae729e80f95d61f)で準備したCloudantNoSQLDBのコンソールを開きます。
3. IoTデータを保存したデータベース（例：iotdb）を確認し、画面左側の「Warehousing」というメニューを選択します。このメニューが表示されていない場合には、画面左上の三本線をクリックすることで表示されます。
![スクリーンショット 2016-05-19 12.04.08.png](https://qiita-image-store.s3.amazonaws.com/0/125842/c5790e98-8820-06ff-d1a6-7b73d431e66b.png)
4. ウェアハウスのコンソール画面で「Create a dashDB Warehouse」を選択します。
5. Bluemixへログインする際に使用したIBM IDを入力します。
![スクリーンショット 2016-05-19 12.09.06.png](https://qiita-image-store.s3.amazonaws.com/0/125842/72d389a4-7f79-17ea-43f6-1dc20f019e3a.png)
6. 作成するウェアハウス名を適当に指定（例："iotwh"）し、対象とするデータベース名（例："iotdb"）の先頭の文字（例："i"）から対象データベースを指定します。
![スクリーンショット 2016-06-29 10.57.50.png](https://qiita-image-store.s3.amazonaws.com/0/125842/4bf1ccf6-9a86-de3f-7d16-35edd5554013.png)
7. 「dashDB service instance」を選択し、前項で準備したDashDBサービスを選択します。
8. Warehouseが作成されるのを待ちます（しばらくかかります）。
![スクリーンショット 2016-05-19 17.48.17.png](https://qiita-image-store.s3.amazonaws.com/0/125842/43065953-97d1-ade2-9bef-13dacfd40582.png)

8. dashDBインスタンスが作成されます。
![スクリーンショット 2016-05-19 17.51.48.png](https://qiita-image-store.s3.amazonaws.com/0/125842/b4ffe4bd-1ac6-f587-c161-4d17048364c3.png)

#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")を指定します。
![スクリーンショット 2016-05-19 18.06.50.png](https://qiita-image-store.s3.amazonaws.com/0/125842/c550dbaa-a1d2-6d7c-7f5a-11ae767375d7.png)
4. 画面中央の「Schema」欄に表示されるスキーマ名（例：DASH016xxx）を控えておきます。この画面の「Browse Data」メニューから、データベース表内のデータを確認（閲覧）することが可能です。画面右側の「列の表示/非表示と順序付け」ボタンから表示される列を選択することも可能です。![スクリーンショット 2016-08-17 18.00.06.png](https://qiita-image-store.s3.amazonaws.com/0/125842/f7ee6225-1573-cacd-df06-798df1e415e6.png)
5. 画面左側のメニューから「Run SQL」を選択します。
6. 表示されたSQL入力画面に下記を入力します。「Validate」ボタンを押してSQL構文にエラーが無い事を確認して、「Run」ボタンで実行します。

```
select count(*) from DASHxxxxx.iotdb;
```
- 予め用意されたSQL文は削除しても構いませんし、先頭に"--"を付加することでコメント化されます。
![スクリーンショット 2016-05-19 18.16.11.png](https://qiita-image-store.s3.amazonaws.com/0/125842/9f7f8854-2e9a-fdff-a0c3-773c477dfe61.png)

- 実行結果をスクロールダウンし、データベースに保存されているレコードの件数がCloudantコンソールに表示される件数と一致する事を確認します。
![スクリーンショット 2016-05-19 18.26.43.png](https://qiita-image-store.s3.amazonaws.com/0/125842/c5fb9151-2aa8-2eac-bd3d-5b9d5c66ab7d.png)

- 同様に、「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ツールからのアクセスに指定する情報が表示されます。
![スクリーンショット 2016-05-19 18.28.58.png](https://qiita-image-store.s3.amazonaws.com/0/125842/1c2386b3-c2de-f055-a009-b2e4666bb9dc.png)

2. 「Analytics」メニューの「R Scripts」から、R Studioを使った解析が可能です。
![スクリーンショット 2016-05-19 18.32.03.png](https://qiita-image-store.s3.amazonaws.com/0/125842/d9eab609-a3c0-690e-21a2-ffc42de77ec5.png)

#dashDBに表を用意して直接書き込む
dashDBにセンサー用の表を用意してNode-REDのフローから直接書き込む事も可能です。
1.  dashDBのコンソールを開き、「Tables」 → 「Add Table」を選択します。
2.  "Edit the DDL statements"の欄に下記をコピペし、"Run DDL"で実行します。  

```a: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のフローから書き込みます。

```a: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からアクセスできる事を確認します。

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

```

![スクリーンショット 2016-07-06 19.32.04.png](https://qiita-image-store.s3.amazonaws.com/0/125842/f9ba910d-cc74-61f6-3a52-3e44cfccdb9c.png)