Watson IoT Platform - 直接dashDBへデータを格納する

初めてWatson IoT Platformを使ってみよう、というかたのためのガイドです。
Watson IoT Platformを使ってみるでIoTアプリを準備しました。
前項までの非SQLデータベース(Cloudant）を経由する方法ではなく、dashDBを個別に用意しそこへ直接IoTデータを書き込む手順です。

#dashDBを個別に用意します

1. 他のBluemixサービスを使えるようにするの手順を参考にして、「データ＆分析」カテゴリーの「DashDB」サービスのインスタンスを作成します。DashDB-xxのような名称が自動的にアサインされます。
2. 作成されたdashDBインスタンス（"DashDB-xx"）を、Node-REDが稼働しているIoTアプリに接続します。Bluemixのダッシュボード（アプリ一覧）から該当するIoTアプリをダブルクリックして開き、「接続」メニューから「既存に接続」を選択し、前項で作成したdashDBインスタンスを選択して「接続」を押し、アプリを再ステージします。
3. 作成されたdashDBのタイルをダブルクリックしてコンソールを開き、「Go to your tables」ボタンもしくは画面左側のメニューの「Tables」ボタンから「Add Table」を選択します。
4. "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)
);

#dashDBへ直接書き込みます

1. 表が定義されたら、Node-REDのフローから書き込みます。下記のフローでは、実物のセンサーがなくてもDashDBへの書き込みを確認することが可能です。

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

• センサータグCC2650の実物を接続してSENSOR2DBへ書き込む際には、以下のフローを使用可能です。下記のフローでは毎秒のデータが書き込まれます

実物のセンサータグからのデータのDashDBへの書き込み

[{"id":"8adcf1f2.2d066","type":"ibmiot in","z":"af611d0a.390108","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":90,"y":80,"wires":[["4256dc3b.7a655c"]]},{"id":"4256dc3b.7a655c","type":"function","z":"af611d0a.390108","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":280,"y":80,"wires":[["c48af21a.27cab"]]},{"id":"c48af21a.27cab","type":"function","z":"af611d0a.390108","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 : msg.payload.d.accelX,\n\tACCELY : msg.payload.d.accelY,\n\tACCELZ : msg.payload.d.accelZ,\n\tAMBIENTTEMP : msg.payload.d.ambientTemp,\n\tGYROX : msg.payload.d.gyroX,\n\tGYROY : msg.payload.d.gyroY,\n\tGYROZ : msg.payload.d.gyroZ,\n\tHUMIDITY : msg.payload.d.humidity,\n\tLIGHT : msg.payload.d.light,\n    MAGX : msg.payload.d.magX,\n\tMAGY : msg.payload.d.magY,\n\tMAGZ : msg.payload.d.magZ,\n\tOBJECTTEMP : msg.payload.d.objectTemp\n};\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":80,"wires":[["8fc33952.764748"]]},{"id":"60f64626.3f54c8","type":"inject","z":"af611d0a.390108","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":100,"y":140,"wires":[["4256dc3b.7a655c"]]},{"id":"8fc33952.764748","type":"dashDB out","z":"af611d0a.390108","service":"dashDB-wf","table":"SENSOR2DB","name":"dashDBへ書き込み","x":710,"y":80,"wires":[]}]

• 同様に、下記のフローでは実物のCC2650センサータグからのデータを10秒毎にdashDBのSENSOR2DBへ書き込みます。書き込む間隔は１０秒毎に書き出しノードのif (msg.date.second % 10 === 0) return msg;の値を15秒毎、20秒毎、30秒毎などへ変更することで調整可能です。

実物のセンサータグから時間間隔でデータをDashDBへの書き込み

[{"id":"b1c25b95.3903a","type":"ibmiot in","z":"1b4d0fef.0afda8","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":80,"wires":[["41bfc260.353cec"]]},{"id":"41bfc260.353cec","type":"function","z":"1b4d0fef.0afda8","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":300,"y":80,"wires":[["48468dfa.d396cc"]]},{"id":"48468dfa.d396cc","type":"function","z":"1b4d0fef.0afda8","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 : msg.payload.d.accelX,\n\tACCELY : msg.payload.d.accelY,\n\tACCELZ : msg.payload.d.accelZ,\n\tAMBIENTTEMP : msg.payload.d.ambientTemp,\n\tGYROX : msg.payload.d.gyroX,\n\tGYROY : msg.payload.d.gyroY,\n\tGYROZ : msg.payload.d.gyroZ,\n\tHUMIDITY : msg.payload.d.humidity,\n\tLIGHT : msg.payload.d.light,\n    MAGX : msg.payload.d.magX,\n\tMAGY : msg.payload.d.magY,\n\tMAGZ : msg.payload.d.magZ,\n\tOBJECTTEMP : msg.payload.d.objectTemp\n};\nreturn msg;","outputs":1,"noerr":0,"x":500,"y":80,"wires":[["a3b8a0c3.7b7bf"]]},{"id":"39818c66.f55ffc","type":"inject","z":"1b4d0fef.0afda8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":140,"y":180,"wires":[["41bfc260.353cec"]]},{"id":"f29e4ba4.c214b","type":"dashDB out","z":"1b4d0fef.0afda8","service":"dashDB-9x","table":"SENSOR2DB","name":"dashDBへ書き込み","x":710,"y":180,"wires":[]},{"id":"a3b8a0c3.7b7bf","type":"function","z":"1b4d0fef.0afda8","name":"１０秒毎に書き出し","func":"if (msg.date.second % 10 === 0) return msg;","outputs":1,"noerr":0,"x":480,"y":180,"wires":[["f29e4ba4.c214b","e36cc315.0859c"]]},{"id":"e36cc315.0859c","type":"debug","z":"1b4d0fef.0afda8","name":"","active":true,"console":"false","complete":"false","x":700,"y":120,"wires":[]}]

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

1. Node-REDを使ってdashDBからデータが読み出せる事を確認します。

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"]]}]

#dashDB内のデータを確認します。
dashDBの機能を使ってデータベース内のデータを確認します。

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

select count(*) from DASH016xxx.sensor2db;

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

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

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

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

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

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

delete from DASHxxxxx.SENSOR2DB where ambienttemp=0;

#サンプル１：シミュレートしたIoTデータを指定した間隔でdashDBへ書き込む
下記のフローではIoTデータをシミュレートし、指定した間隔でdashDBへ書き込みます。

1. IoTデータをシミュレート（生成）します
2. 指定した間隔でdashDBへ直接書き込みます
3. 使用時には左端の「１秒毎」Injectノードを次の「日付時刻の追加」functionノードへ接続して使用します。普段データが生成され続けてしまうので、使用しない時は切断しておくことをお薦めします。
4. 必要に応じ、「センサー値を出力」functionノードを開き、冒頭のインターバル値を調整します。

シミュレートしたIoTデータをDBへ書き込み

[{"id":"d6a7c81e.450048","type":"inject","z":"7e6262d4.bcd4e4","name":"1秒毎","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"x":90,"y":120,"wires":[[]]},{"id":"6f55fe33.6cf118","type":"inject","z":"7e6262d4.bcd4e4","name":"restart","topic":"","payload":"0","payloadType":"num","repeat":"","crontab":"","once":false,"x":90,"y":160,"wires":[["f2602e5b.bbb1b"]]},{"id":"a8639f02.42b0c8","type":"debug","z":"7e6262d4.bcd4e4","name":"出力","active":true,"console":"false","complete":"payload","x":630,"y":100,"wires":[]},{"id":"dce6eed8.b9fc9","type":"comment","z":"7e6262d4.bcd4e4","name":"シミュレートしたIoTデータを指定した間隔でDBへ書き込み","info":"","x":240,"y":60,"wires":[]},{"id":"f2602e5b.bbb1b","type":"function","z":"7e6262d4.bcd4e4","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();\n//if (!msg.payload) {\n//    msg.payload = { \"initial\": \"value\" };\n//}\nif (!msg.date) {\n    msg.date = { \"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":261,"y":140,"wires":[["47d39b27.1988dc","27f97ffa.6af63"]]},{"id":"47d39b27.1988dc","type":"debug","z":"7e6262d4.bcd4e4","name":"時刻の表示","active":false,"console":"false","complete":"date","x":430,"y":100,"wires":[]},{"id":"c4e1e935.195f18","type":"function","z":"7e6262d4.bcd4e4","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 : msg.sensor1,\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 : msg.average\n};\nreturn msg;\n","outputs":1,"noerr":0,"x":660,"y":140,"wires":[["7e904315.ceda14"]]},{"id":"27f97ffa.6af63","type":"function","z":"7e6262d4.bcd4e4","name":"センサー値を出力","func":"//var sampling_min_interval = 1;\nvar sampling_sec_interval = 60;     // データを出力する間隔\n\nvar sum = 0, average = 0;\nvar sen1 = Math.floor(Math.random () * 10) + 1;　// センサー値を生成（シミュレート）\n\nif ( !context.array ) {\n    context.array = new Array (10); //直近１０件分を保持\n}\nvar shifted = context.array.shift();\ncontext.array.push(sen1);           // 最新の値を保存\nvar length = context.array.length;\n\nfor (k=0; k<length; k++) {\n        sum = sum + context.array[k];   // 合計を算出\n}\n\nvar hour = JSON.stringify(msg.date.hour);\nvar min_org  = JSON.stringify(msg.date.minute);\nvar min = ( \"0\" + min_org ).substr( -2 );       // 一桁分の場合、二桁に\nvar sec_org  = JSON.stringify(msg.date.second);\nvar sec = ( \"0\" + sec_org ).substr( -2 );       // 一桁秒の場合、二桁に\nvar time = hour + \":\" + min + \":\" + sec;\n\naverage = sum / length;\nvar data = [[time],[sen1],[average]];\nmsg.payload = data;\nif ( ( msg.date.second % sampling_sec_interval ) !== 0 ) return;\n//if ( ( msg.date.minute % sampling_min_interval ) !== 0 ) return;\nreturn msg;\n","outputs":1,"noerr":0,"x":450,"y":140,"wires":[["c4e1e935.195f18","a8639f02.42b0c8"]]},{"id":"7e904315.ceda14","type":"dashDB out","z":"7e6262d4.bcd4e4","service":"","table":"SENSOR2DB","name":"dashDB","x":820,"y":140,"wires":[]}]

#サンプル２：センサータグ（CC2650)からのIoTデータを指定した間隔でdashDBへ書き込む
下記のフローではセンサータグCC2650からのIoTデータを指定した間隔でdashDBへ書き込み、それを表示します。

1. センサータグCC2650からデータを取得します
2. 指定した間隔でdashDBへ直接書き込みます
3. 必要に応じ、「センサー値を出力」functionノードを開き、冒頭のインターバル値を調整します。

センサータグCC2650からのデータをdashDBへ格納し、D3で可視化する

[{"id":"175917e8.30b87","type":"inject","z":"64589e61.60898","name":"1秒毎","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"x":110,"y":200,"wires":[[]]},{"id":"c3e41b5d.223d28","type":"inject","z":"64589e61.60898","name":"restart","topic":"","payload":"0","payloadType":"num","repeat":"","crontab":"","once":false,"x":110,"y":240,"wires":[[]]},{"id":"a0155b1b.cda098","type":"comment","z":"64589e61.60898","name":"IoTデータを指定した間隔でDBへ書き込み","info":"","x":200,"y":60,"wires":[]},{"id":"6d18f085.c1b5d8","type":"function","z":"64589e61.60898","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();\n//if (!msg.payload) {\n//    msg.payload = { \"initial\": \"value\" };\n//}\nif (!msg.date) {\n    msg.date = { \"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":301,"y":140,"wires":[["3b7f6362.24696c","def4328.f99acd"]]},{"id":"3b7f6362.24696c","type":"debug","z":"64589e61.60898","name":"時刻の表示","active":false,"console":"false","complete":"date","x":510,"y":100,"wires":[]},{"id":"14d3087b.28646","type":"function","z":"64589e61.60898","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 : msg.sensor.accelX,\n\tACCELY : msg.sensor.accelY,\n\tACCELZ : msg.sensor.accelZ,\n\tAMBIENTTEMP : msg.sensor.ambientTemp,\n\tGYROX : msg.sensor.gyroX,\n\tGYROY : msg.sensor.gyroY,\n\tGYROZ : msg.sensor.gyroZ,\n\tHUMIDITY : msg.sensor.humidity,\n\tLIGHT : msg.sensor.light,\n    MAGX : msg.sensor.magX,\n\tMAGY :msg.sensor.magY,\n\tMAGZ : msg.sensor.magZ,\n\tOBJECTTEMP : msg.sensor.objectTemp\n};\n\nreturn msg;\n","outputs":1,"noerr":0,"x":520,"y":220,"wires":[["ba9d26f1.6a516","ecfd037.e72c4"]]},{"id":"8bcc302e.8dbd58","type":"function","z":"64589e61.60898","name":"センサー値を出力","func":"var sampling_min_interval = 10;     // 10分毎にDBヘ書き込み\nvar sampling_sec_interval = 60;     // データを出力する間隔\n\n/*\nvar sum = 0, average = 0;\nvar sen1 = Math.floor(Math.random () * 10) + 1;　// センサー値を生成（シミュレート）\n\nif ( !context.array ) {\n    context.array = new Array (10); //直近１０件分を保持\n}\nvar shifted = context.array.shift();\ncontext.array.push(sen1);           // 最新の値を保存\nvar length = context.array.length;\n\nfor (k=0; k<length; k++) {\n        sum = sum + context.array[k];   // 合計を算出\n}\n\nvar hour = JSON.stringify(msg.date.hour);\nvar min_org  = JSON.stringify(msg.date.minute);\nvar min = ( \"0\" + min_org ).substr( -2 );       // 一桁分の場合、二桁に\nvar sec_org  = JSON.stringify(msg.date.second);\nvar sec = ( \"0\" + sec_org ).substr( -2 );       // 一桁秒の場合、二桁に\nvar time = hour + \":\" + min + \":\" + sec;\n\naverage = sum / length;\nvar data = [[time],[sen1],[average]];\nmsg.sensor.average = average;\n*/\nif ( ( msg.date.second % sampling_sec_interval ) !== 0 ) return;\nif ( ( msg.date.minute % sampling_min_interval ) !== 0 ) return;\nreturn msg;\n","outputs":1,"noerr":0,"x":310,"y":220,"wires":[["14d3087b.28646"]]},{"id":"b7faf05f.7d756","type":"ibmiot in","z":"64589e61.60898","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":90,"y":140,"wires":[["6d18f085.c1b5d8"]]},{"id":"4144253.9dd2fdc","type":"http in","z":"64589e61.60898","name":"","url":"/test28","method":"get","swaggerDoc":"","x":110,"y":380,"wires":[["1f8c6452.0e2c14"]]},{"id":"fdd5ab25.ad1cd8","type":"debug","z":"64589e61.60898","name":"op","active":false,"console":"false","complete":"payload.op","x":670,"y":360,"wires":[]},{"id":"d1c0278d.5bf49","type":"template","z":"64589e61.60898","name":"htmlを返す","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"ja\">\n<head>\n   <meta charset=\"utf-8\">\n   <title>データベースからのIoTデータ表示</title>\n      <script src=\"https://d3js.org/d3.v4.min.js\"></script>\n\n   <style type=\"text/css\">\n         /* ここにスタイルルールを追加します */\n      .axis path,\n      .axis line {\n         fill: none;\n         stroke: black;\n         shape-rendering: crispEdges;\n      }\n      .axis text {\n         font-family: sans-serif;\n         font-size: 11px;\n      }\n   </style>\n\n   <div id=\"op\" foo={{payload.op}}/>\n\n<script type=\"text/javascript\">\n\nvar op_a = document.getElementById( 'op' ).getAttribute( 'foo' );\t//データを取得\nop_a = op_a.substr( 0, op_a.length-1 ) ;\t//末尾の\"/\"でエラーになるので\nvar op_a2 = JSON.parse(op_a);\t//JSON文字列からjavascriptオブジェクトを復元\n\nvar op_a3 = new Array();\nfor(var i in op_a2){\n\top_a3.push(op_a2[i].AMBIENTTEMP);\t//AMBIENTTEMPを取り出して配列op_a3に順次格納\n}\n\nvar op_a5 = new Array();\nfor(var i in op_a2){\n\top_a5.push(op_a2[i].HOUR + \":\" + (( \"0\" + op_a2[i].MINUTE ).substr( -2 )));\n}\n\n   // 幅（ Width ）と高さ（ height ）\n   var w = 800;\n   var h = 300;\n   var zoom =5;\n   var barPadding = 1;  // 棒グラフの棒と棒の間隔\n   var margin = 30;     // グラフ表示域のまわりの余白\n   \n   var dataset = op_a3;\n   var datasetT = op_a5;\n   \n   // スケール関数の生成\n   var xScale = d3.scaleLinear()\n         .domain([0, d3.max(dataset, function(d) { return d; })])\n         .range([margin, w + margin * 2]);\n\n   var yScale = d3.scaleLinear()\n            .domain([0, (h-margin) / zoom]) // Y軸のスケールを棒グラフと一致させる\n            .range([h-margin, 0]);　　　　// Y軸表示場所\n\n   // X 軸の定義\n   var xAxis = d3.axisBottom()\n        .scale(xScale);\n\n   // Y 軸の定義\n   var yAxis = d3.axisLeft(yScale);\n   \n   // SVG 要素の生成\n   var canvas = d3.select(\"body\")\n         .append(\"svg\")\n         .attr(\"width\", w)\n         .attr(\"height\", h);\n\n   canvas.selectAll(\"rect\")\n         .data(dataset)\n         .enter()\n         .append(\"rect\")\n         .attr(\"x\", function(d, i) {\n            return i * ((w-margin) / dataset.length) + margin;\n         })\n         .attr(\"y\", function(d) {\n            return h - (d * zoom) - margin;\n         })\n         .attr(\"width\", (w-margin) / dataset.length - barPadding)\n         .attr(\"height\", function(d) {\n            return d * zoom;\n         })\n         .attr(\"fill\", function(d) {\n//            return \"rgb(0, 0, \" + ( ( (d-20) * 10 ) + 100 ) + \")\";\n            return \"rgb(0, 51, 255)\";\n                     });\n\t\t \n   canvas.selectAll(\"text\")\n         .data(datasetT)\n         .enter()\n         .append(\"text\")\n         .text(function(d) {\n            return d;\n         })\n         .attr(\"text-anchor\", \"middle\")\n          .attr(\"x\", function(d, i) {\n            return i * ((w-margin) / dataset.length) + margin + ((w-margin) / dataset.length - barPadding ) / 2;\n         })\n        .attr(\"y\", function(d) {\n            return h;\n         })\n         .attr(\"font-family\", \"sans-serif\")\n         .attr(\"font-size\", \"11px\")\n         .attr(\"fill\", \"black\");\n\n/*    canvas.selectAll(\"text\")\n         .data(dataset)\n         .enter()\n         .append(\"text\")\n         .text(function(d) {\n            return d;\n         })\n         .attr(\"text-anchor\", \"middle\")\n         .attr(\"x\", function(d, i) {\n            return i * ((w-margin) / dataset.length) + (w / dataset.length - barPadding) / 2  + margin;\n         })\n         .attr(\"y\", function(d) {\n            return h - (d * 4) + 14  - margin;\n         })\n         .attr(\"font-family\", \"sans-serif\")\n         .attr(\"font-size\", \"11px\")\n         .attr(\"fill\", \"white\");\n*/\n   // X 軸の生成\n//   canvas.append(\"g\")\n//         .attr(\"class\", \"axisBottom\")\n//         .attr(\"transform\", \"translate(0,\" + (h - margin) + \")\")\n//         .call(xAxis);\n\n   // Y 軸の生成\n   canvas.append(\"g\")\n         .attr(\"class\", \"axisLeft\")\n         .attr(\"transform\", \"translate(\" + margin + \",0)\")\n         .call(yAxis);\n\t\t \n\t\t \n   var m =  0;\n   var mh = 0;\n   var mm = 0;\n   var mt = 0;\n   for(var i in op_a2){\n\t   if (m < Number(op_a2[i].AMBIENTTEMP)) {\n\t       m = Number(op_a2[i].AMBIENTTEMP);\n\t\t   mh = op_a2[i].HOUR;\n\t\t   mm = ( \"0\" + op_a2[i].MINUTE ).substr( -2 ) ;\n\t\t   mt = op_a2[i].AMBIENTTEMP;\n    //console.log(\"max found was \" + mh + \":\" + mm + \" \" + mt);\n\t   }\n   }\n\n   m = mh + \":\" + mm;\n\n   window.onload = function onLoad() {\n      var target1 = document.getElementById(\"output1\");\n         target1.innerHTML = m;\n      var target2 = document.getElementById(\"output2\");\n         target2.innerHTML = mt;\n\t}\n\t\n</script>\n</head>\n<body>\n\n<div id=\"a-box\" style=\"background-color: #87cefa; padding: 20px;\">\n\t<h1>データベースからのIoTデータ表示</h1>\n\t<br />\n\t<p>\n\t\tデータベースに保存されたIoTデータを可視化します<br />\n\t</p>\n</div>\n\n<div id=\"b-box\" style=\"background-color: #ecf2fe; padding: 20px; \">\n\t\t<h2>最高値の時刻</h2>\n\t\t\t<div style=\"text-align:left\" id=\"output0\"></div>\n\t\t\t<div style=\"text-align:left;font-size: 18pt\" id=\"output1\"></div>\n\t\t<h2>最高値　</h2>\n\t\t\t<span style=\"text-align:left;font-size: 18pt\" id=\"output2\"></span> ℃\n</div>\n</body>\n</html>","x":690,"y":420,"wires":[["5d901718.3c51f8"]]},{"id":"5d901718.3c51f8","type":"http response","z":"64589e61.60898","name":"","x":850,"y":420,"wires":[]},{"id":"422b2203.7d68e4","type":"function","z":"64589e61.60898","name":"stringify","func":"var m = JSON.stringify(msg.payload);\nmsg.payload.op = m;\nreturn msg;","outputs":1,"noerr":0,"x":520,"y":380,"wires":[["d1c0278d.5bf49","fdd5ab25.ad1cd8"]]},{"id":"def4328.f99acd","type":"function","z":"64589e61.60898","name":"センサー値を待避","func":"msg.sensor = {\n        \"accelX\": msg.payload.d.accelX,\n        \"accelY\": msg.payload.d.accelY,\n        \"accelZ\": msg.payload.d.accelZ,\n        \"ambientTemp\": msg.payload.d.ambientTemp,\n        \"gyroX\": msg.payload.d.gyroX,\n        \"gyroY\": msg.payload.d.gyroY,\n        \"gyroZ\": msg.payload.d.gyroZ,\n        \"humidity\": msg.payload.d.humidity,\n        \"light\": msg.payload.d.light,\n        \"magX\": msg.payload.d.magX,\n        \"magY\": msg.payload.d.magY,\n        \"magZ\": msg.payload.d.magZ,\n        \"objectTemp\": msg.payload.d.objectTemp,\n};\n\nreturn msg;\n","outputs":1,"noerr":0,"x":530,"y":140,"wires":[["8bcc302e.8dbd58"]]},{"id":"ba9d26f1.6a516","type":"debug","z":"64589e61.60898","name":"書き込みデータ","active":true,"console":"false","complete":"payload","x":740,"y":260,"wires":[]},{"id":"838fa4c.eef7fd8","type":"comment","z":"64589e61.60898","name":"DBからのデータを可視化","info":"","x":150,"y":340,"wires":[]},{"id":"ecfd037.e72c4","type":"dashDB out","z":"64589e61.60898","service":"","table":"SENSOR2DB","name":"dashDBへ書き込み","x":750,"y":220,"wires":[]},{"id":"1f8c6452.0e2c14","type":"dashDB in","z":"64589e61.60898","service":"","query":"select hour,minute,ambienttemp, objecttemp  from SENSOR2DB where (minute%10)=0;","params":"","name":"dashDBから読み込み","x":320,"y":380,"wires":[["422b2203.7d68e4"]]}]

#サンプル３：Alps IoT Smart ModuleからのIoTデータを指定した間隔でdashDBへ書き込む
Alps IoT Smart ModuleからのIoTデータを格納する表は下記のようなDDLで作成可能です。

CREATE TABLE "ALPSIOT"
(
  "DATE" BIGINT,
  "DAY" BIGINT,
  "HOUR" BIGINT,
  "MINUTE" BIGINT,
  "MONTH" BIGINT,
  "SECOND" BIGINT,
  "YEAR" BIGINT,
  "ACCELX" VARCHAR(20),
  "ACCELY" VARCHAR(20),
  "ACCELZ" VARCHAR(20),
  "AMBIENTLIGHT" VARCHAR(20),
  "GEOMAGNETICX" VARCHAR(20),
  "GEOMAGNETICY" VARCHAR(20),
  "GEOMAGNETICZ" VARCHAR(20),
  "HUMIDITY" VARCHAR(20),
  "PRESSURE" VARCHAR(20),
  "UV" VARCHAR(20)
);

下記のフローではAlps IoT Smart ModuleからのIoTデータを指定した間隔でdashDBへ書き込みます

実物のセンサータグからのデータのDashDBへの書き込み

[{"id":"32a7a763.d28538","type":"ibmiot in","z":"1a18e39.b66a99c","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":90,"y":80,"wires":[["6b0cda13.f42dfc"]]},{"id":"6b0cda13.f42dfc","type":"function","z":"1a18e39.b66a99c","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":240,"y":80,"wires":[["7720544a.dce0c4"]]},{"id":"7720544a.dce0c4","type":"function","z":"1a18e39.b66a99c","name":"データ整理","func":"if (msg.payload.d.pressure > 0 ) {\n    context.set('ps', msg.payload.d.pressure);\n    context.set('hm', msg.payload.d.humidity); \n    context.set('uv', msg.payload.d.uv); \n    context.set('al', msg.payload.d.ambientLight); \n} else {\n}\nif (msg.payload.d.accelX ) {\n    msg.payload = { \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 : msg.payload.d.accelX,\n    \tACCELY : msg.payload.d.accelY,\n    \tACCELZ : msg.payload.d.accelZ,\n    \tAMBIENTLIGHT : context.get('al'),\n        GEOMAGNETICX : msg.payload.d.geoMagneticX,\n        GEOMAGNETICY : msg.payload.d.geoMagneticY,\n        GEOMAGNETICZ : msg.payload.d.geoMagneticZ,\n    \tHUMIDITY : context.get('hm'),\n    \tPRESSURE :  context.get('ps'),\n        UV :  context.get('uv')\n    };\n    return msg;\n}","outputs":1,"noerr":0,"x":410,"y":80,"wires":[["c0bfac0d.b874f"]]},{"id":"c0bfac0d.b874f","type":"function","z":"1a18e39.b66a99c","name":"５秒毎に書き出し","func":"if (msg.date.second % 5 === 0 && msg.date.second != context.get('sec')) {\n    context.set('sec', msg.date.second);\n    return msg;\n}","outputs":1,"noerr":0,"x":590,"y":80,"wires":[["a647df73.04592"]]},{"id":"a647df73.04592","type":"dashDB out","z":"1a18e39.b66a99c","dashDB":"","service":"_ext_","table":"ALPSIOT","name":"dashDBへ書き込み","x":790,"y":80,"wires":[]}]