まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:sql

Node-RED だけで SQL Database(DB2) 内のデータを検索して結果を表示する、という仕組みを作ってみます。

IBM Bluemix などから提供されているオープンソース・データフローエディタである Node-RED は、ノードと呼ばれる機能単位のブロックを組み合わせて、ほぼコーディングをすることなくデータのワークフローを作れるウェブアプリケーションです。そのフローにデータベースを組み合わせて問い合わせしたり、結果を格納したり、といったことも少ない(或いはほとんどない)コーディングで実現できる、という便利なツールです。特に Bluemix と組わせることで IBM IoT Foundation と連携した MQTT システムを簡単に構築することが可能になります:
2016020101


データのフローを得意とする一方で、Node-RED 単体では UI はあまり得意ではありません。全くできないわけではないのですが、基本的には HTML などの知識を前提として、動的に HTML を作成するような形になります。むしろサーブレット的というか、UI を外出しした仕組みの1パーツとして利用するのが便利だと思っています。


例えば、現在 SQL DB 内に MYTABLE というテーブルが作られ、以下の様な状態になっているものとします。ここから ID 値が 'fff57f93.000a8' であるデータを検索する、ということを目的としてみます。この ID はパラメータとして与えるようにして、リクエストの度に変更できるようなものにします:
2016020101


普通に SQL を使うのであれば(言語によって JDBC などの設定をした上で)、SELECT * FROM MYTABLE WHERE ID = 'fff57f93.000a8' といった具合にクエリーを実行して結果を得ることになると思います。

この処理を NodeRED で行うにはどうすればよいでしょうか?そのためにはまずこの SQLDB に NodeRED からアクセスできるようになっている必要があります。Bluemix 環境であれば、目的の NodeRED プロジェクトに、この SQLDB を事前にバインドしておきます(下図ではサービス名が SQL Database-iv となっています。適宜みなさんの実際の環境と読み替えてください):
2016020101


バインドした上で NodeRED のパレット内の SQLDB を使います。なお、ここでの SQLDB ノードは(検索をしたいので)左右両方にノード接続点があるものを使います。
2016020102


そして以下の様な NodeRED データフローチャートを作ります。HTTP Request ノード、SQL DB ノード、JSON ノード、Template ノード、HTTP Response ノード、そして Debug ノードの6つを以下のようにつなぎます。以下、各ノードの属性を説明します:
2016020101


HTTP Request ノードでは Method として GET、そして URL として /query を指定します。この URL で指定した内容が実際に問い合わせをする際の URL になります:
2016020102


SQL DB ノードでは問い合わせ処理を記述します。Service では SQL DB のサービス名をプルダウンから選択し、Query には ID をキーに MYTABLE テーブルを検索したいので select * from MYTABLE where id = ? と入力します。そしてこの ? パラメータに代入される値を直前の HTTP Request ノードの URL パラメータから取得するようにします(Prepared Statement っぽい感じ)。HTTP Request ノードの URL パラメータの値は msg.req.query で取得できます。今回は id というパラメータにこの値が入っているよう想定するので、Parameter Markers の欄に msg.req.query.id と指定します:
2016020103


JSON ノードは JSON ブロックをそのまま配置するだけです(名前は適当に指定してください):
2016020104


そして Template ノードで検索結果を表示用に整形します。ここでは HTML 形式で、背景を薄い黄色にして表示してみましょう。以下の様な内容を入力します。HTML 内の {{payload}} 部分に検索結果が入り、その結果を msg.payload として返す、という指定をしていることになります:
2016020105


最後に HTTP Response ノードと debug ノードをそれぞれ配置します。この状態でフローを Deploy します:
2016020106


そしてウェブブラウザで、http://(Node-RED のあるサーバー)/query?id=(SQL DB から検索したいレコードのID値) を指定してアクセスします。成功すると指定した ID 値を持つレコードが JSON テキスト形式で、指定したフォーマット(背景が薄い黄色)で表示されるはずです:
2016020107


URL パラメータの id の値を別のレコードのものに変えると、指定したレコードの内容に変わるはずです:
2016020108


同時にこれらの検索結果は Node-RED 画面の debug タブ内にも表示されているはずです:
2016020109


上記例では結果を(わざわざ)HTML 化して出力しましたが、現実には JSON のまま返したり、XML 化して返す、といったサーブレット的な処理をする機会が多いと思います。要は見た目については Node-RED の外で管理すべきと考えています。


・・・と、そう思っていたのですが、最近になって Node-RED に UI パーツを追加できるようなノード部品が提供されたようです。ある程度用途は限られていますが、UI も Node-RED で作れるようになっていくのかもしれません:
https://github.com/andrei-tatar/node-red-contrib-ui


とりあえず、Node-RED だけでサーブレット的な検索機能を実装することもできそうだ、ということが分かりました。


普段は MySQL 使いの自分が IBM DB2 を使おうとして、意外とハマったのが以下に紹介する2点です。

まず1点目。MySQL ではテーブル列の属性に auto_increment という指定を付与することができます:
mysql> create table langs(
  id int primary key auto_increment,
  name varchar(256)
);

この属性が付いた列はデータ挿入時に値を指定する必要がなく、定義に従ったルールでユニークな値を勝手に挿入してくれる便利な属性です(赤字はコメントです):
mysql> insert into langs( name ) values( 'Java' );  nameだけを指定してinsert
mysql> insert into langs( name ) values( 'PHP' );
mysql> insert into langs( name ) values( 'Ruby' );

mysql> select * from langs;
+----+------+
| id | name |
+----+------+
|  2 | Java | ユニークなid値が勝手に挿入されている
| 12 | PHP  |
| 22 | Ruby |
+----+------+

これと同じことを DB2 利用時にもやりたい! のですが、DB2 では create table 時に auto_increment 属性を認識してくれません。


では DB2 で同じことをするには、create table 時にどのような指定をすればよいのでしょうか?

その答がこちらです。indentity 構文を使いますが、ちと面倒:
db2> create table langs(
  id int primary key generated always as identity (start with 1 increment by 1),
  name varchar(256)
);

これをテーブル定義時に指定しておけば insert 時にはユニークな値を自動生成してくれるので楽ちんです。



2点目。同様に、MySQL では auto_increment 指定のある列に挿入された値を知りたい、と思うことがあります:
mysql> insert into langs( name ) values( 'Python' );
↑今、インサートしたこのレコードの ID 値が何だったのかを知りたい

それは LAST_INSERT_ID() で取得できました。
mysql> select last_insert_id() from langs;

これと同じことを DB2 の identity 指定した列に対してやるにはどうすればいいでしょうか?
その答は IDENTITY_VAL_LOCAL() です:
db2> select IDENTITY_VAL_LOCAL() from langs;

MySQL と DB2 で文法の違いがありますが、とりあえずはどちらも出来るということで。 

自分が迷ったのは今のところこの位ですが、Bluemix を使うユーザーが増えると必然的(?)に SQL Database や dashDB を使う人も増えると思います。DB2 をベースにしたこれらのサービスを使う時に、今回紹介したような点で戸惑う人の助けになれば幸いです。


(参考にしたページ)
http://stackoverflow.com/questions/13466347/how-to-auto-increment-in-db2
http://stackoverflow.com/questions/3087836/db2-how-to-get-the-last-insert-id-from-a-table
 

IBM Bluemix からは様々なサービスが提供されていますが、その中に "SQL Database" と呼ばれているものがあります:
2015070301


「お! SQL Server が使えるの!?」と勘違いしたあなたへの大切なメッセージがあります。これは某社の "SQL Server" ではなく "SQL Database" です。サービス一覧からクリックして詳細を表示すると分かるのですが、実はこれ、実体は IBM DB2 です:
2015070302


Bluemix から提供されている「データベース」のサービスは多岐にわたっています。SQL もあれば NoSQL もあるし、それぞれ IBM が提供するもの/IBM のビジネスパートナーが提供するもの/オープンソースのもの があります。その中で「IBM 提供の SQL 型データベースの DBaaS」という位置づけに相当するものがこの "SQL Database" です。DBaaS としてサーバー機能が提供されているだけでなく、ウェブから使える管理機能のインターフェースも提供されます。これによって別途 DB2 クライアントや管理ツールなどを用意することなくデータベースの管理を行うことも可能です:
2015070305


そしてこの SQL Database 、2015年7月以降で少し仕様が変わりました。Bluemix から選択した時のデフォルトで適用されるエディションがこれまでの "Free Beta" から "Small" に変更されています:
2015070303


無料トライアル期間中はどちらを選んでも一切料金がかからない、という点は変わりませんが、有償プランで利用されている方(クレジットカード登録をした上で無料枠内で使っている方を含む)は、今後 SQL Database を利用する場合にデフォルト設定のまま作成すると有料プランを選択することになります。この点ご注意ください。

これまでどおりの Free Beta で利用する場合は、作成前に選択プランを "Free Beta" に変更してから「作成」するようにしてください:
2015070304


なお、Bluemix 上で SQL Database を使う場合の詳細についてはオンラインドキュメントを参照ください:
http://www.ng.bluemix.net/docs/#services/SQLDB/index.html



IBM Bluemix では IoT デバイスからのセンサー情報を使ったデータフローを定義する Node-RED エディタを提供しています。

特にセンサー情報はそのデバイス機器によって内容やデータフォーマットが異なるため、その内容をデータベースに保存しようと考えた場合はいわゆる NoSQL データベースを使ってテーブルを定義することなく JSON 丸ごと保存、なんてことをやったりします。これはこれで便利ですよね。

ただ保存するまではいいのですが、保存したデータを再利用する際には NoSQL だとクエリーが使えないため、その取り出しに不便に感じることもあります。その解決策の1つとしていったん Claudant に格納したデータを dashDB に複製する、という方法もあります:
Claudant => dashDB の単方向レプリケーション

そのような方法もありますが、もうデータフォーマットがわかりきっていて、NoSQL に格納する理由が特になければ最初からセンサーデータを SQL 型のデータベースに格納してしまってもいいわけです。本エントリではそんな方法のサンプルとして、Node-RED 内で SQL Database(DB2) にセンサーデータを格納する方法を紹介します。


まず Node-RED 側の用意をします。今回はすごくシンプルな形にして、IoT デバイスからのデータを一度整形して、そのままデバッグ出力するだけ、というものです:
2015051901


この中の整形部分(上記の "f" と書かれたノード)の中身は以下のようにしています。メッセージID とペイロードの名前、そして温度情報をそれぞれ ID, MYNAME, TEMP という名前で取り出しています。DB2 側で列名は大文字であることを前提とするため、JSON 側でもここを大文字にすることが肝です:
2015051902


この状態で一度デプロイして動かしてみます。ちゃんと動きますね:
2015051903


デバッグタブの出力内容は(期待通り)このようになっています。この内容を SQL Database のテーブルに格納していきます:
2015051904


では次に Bluemix 側の(SQL Database 側の)準備をします。Bluemix 上に作成した Node-RED プロジェクトランタイムで「サービスまたは API の追加」をクリックします:
2015051905


サービスの一覧から "SQL Database" を選んで追加します:
2015051906


追加が成功するとプロジェクトランタイムの中に SQL Database サービスが表示されます。このアイコンをクリックします:
2015051907


SQL Database の説明画面が表示されます。この中の "LAUNCH" ボタンをクリックしてウェブコンソールに移動します:
2015051908


ウェブコンソールが開いたら、Node-RED からのデータを格納するテーブルを定義する必要があります。そこで "Work with Tables" をクリックします:
2015051909


テーブル作業画面の左側にあるプラスマークをクリックしてテーブルを追加します:
2015051910


画面右のテキストエリアにテーブル作成の DDL を記述します:
2015051911


今回はこのような内容にしました。テーブル名は MYTABLE です:
CREATE TABLE MYTABLE
(
  ID VARCHAR(15),
  MYNAME VARCHAR(20),
  TEMP INT
);


最後に画面下部の "Run DDL" をクリックして、MYTABLE テーブルを作成します。成功すると MYTABLE テーブルが作成され、その定義内容を確認することができます:
2015051912


改めて Node-RED 画面で SQL Database の設定を追加します。ノード一覧の中から左側だけに丸のついた "sqldb"(左右に付いているのは更新用、左側だけのが追加用)を探して、キャンバスにドラッグして追加します:
2015051913


追加した "sqldb" ノードをダブルクリックし、そのサービスとテーブル名を定義します。テーブル名は先ほど作成した "MYTABLE" を指定します:
2015051914


最後にこのようにノードをつなぎます。これでデプロイ!:
2015051915


デプロイが成功するとデバッグタブにセンサー情報が表示されます。同時に SQL Database にも同じデータが格納されているはずです:
2015051916


SQL Database のウェブコンソールに戻り、MYTABLE テーブルの "Browse Data" タブを開くと追加(INSERT)されたレコードが表示されます。センサーデータが直接 DB2 に格納できたことが確認できました:
2015051917


あとは SQL を使って適当にご自由に。










 

このページのトップヘ