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

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

タグ:query

IBM Cloud の比較的新しい機能として、オブジェクトストレージ内の CSV ファイルに対して SQL を実行してレコードを抽出することができるようになりました。その新機能 SQL Query を紹介します。なお以下で紹介する手順は IBM Cloud のライトアカウント(無料版)の範囲内でも実施可能なので、興味ある方はぜひ本記事を参考に使ってみてください。
2021011600



この SQL Query はオブジェクトストレージ内の CSV ファイルを対象として実行するので、まずは(まだ利用していない場合は)クラウドオブジェクトストレージサービスを作成します。プランが「ライト」のものを選択して作成すれば一定条件内で無料で利用可能です:
2021011601


作成したオブジェクトストレージサービスの "Integrations" メニューを表示すると、このオブジェクトストレージサービスに統合されたサービスを確認できます。作成直後ではここには何もないはずですが、画面下部に「SQL Query を新規に作成して統合可能」と案内されています。この SQL Query のアイコンを選択することで、SQL Query (の無料版)を新規に作成して、このオブジェクトストレージと紐付けて利用することができるようになります:
2021011602


作成する SQL Query サービスの属性を設定する画面が表示されます。このプランとして「ライト」を選択すれば一定条件内で無料利用が可能です。最後に「作成」をクリック:
2021011603


SQL Query サービスが追加され、オブジェクトストレージと統合されて利用できるようになりました:
2021011604


また「バケット」メニューを参照すると、SQL Query 用のバケットが1つ追加されていることが確認できます。このバケットは必ずしも利用する必要はありませんが、SQL を実行する CSV ファイルや、SQL の実行結果をファイルとして残したい場合のファイル格納場所として利用できます。

今回はこの作成されたバケット内に CSV ファイルを置いて SQL を実行し、その実行結果ファイルもこのバケット内に作成する手順を以下で紹介します。というわけで、まずはこのバケット内に CSV ファイルを格納するため、バケット名部分を選択します:
2021011605


選択した(作成したばかりの)バケット内のファイル一覧が表示されますが、この時点ではまだ CSV ファイルは格納されていません:
2021011606


もし自分でお持ちの CSV ファイルを利用したい場合は、その CSV ファイルをこの一覧部分にドラッグ&ドロップしてアップロードしていただいても構いません。以下のサンプルでは以前に自分が全く別の目的でラズベリーパイの機器温度等を1秒おきに取得した際の CSV ファイルがあったので、このファイルを使って紹介することにします。

同 CSV ファイルは以下に公開しているので、以下ページを表示して、マウス右クリックから「名前を付けて保存」し、RPDATA.csv という名前で保存してください:
https://raw.githubusercontent.com/dotnsf/RPDATA/master/RPDATA.csv

2021011607
(1行目は列定義、2行目以降がデータで、左から ID、CPU 温度(℃)、CPU 負荷(%)、サイン値)


ダウンロードした RPDATA.csv をオブジェクトストレージ内のバケットにドラッグ&ドロップ(または「アップロード」ボタンからアップロード)して同バケット内に格納しておきます:
2021011608


これで SQL を実行する準備ができました。では SQL Query のサービスに移動します。改めてオブジェクトストレージのサービス画面から Integrations メニューを選択し、統合されている SQL Query サービス名部分を選択します:
2021011601


SQL Query サービス画面が表示されたら、画面右上の "Launch SQL Query UI" ボタンをクリックして UI 画面に移動します:
2021011602


以下のような IBM SQL Query の UI 画面が表示されます(↓この画面ではエラーメッセージが表示されていますが気にしないでください(苦笑)):
2021011603


画面上部に SQL を入力します。その際の from 直後のテーブル部を指定する箇所では、アップロードした CSV ファイルを指定します。CSV ファイルは Target location に表示されている文字列の、最後の result/ を除いて変わりに RPDATA.csv を指定し、最後に Run ボタンをクリックします:
2021011601
("select * from cos://us-south/XXXXXX/RPDATA.csv limit 10" と入力しました。一般的な SQL SELECT 文のテーブル名を指定する箇所にオブジェクトストレージ内の CSV ファイルを URI で指定する形になっています)


すると画面下部に SQL クエリーの実行結果が表示されます。特にテーブル定義をすることなく、CSV の1行目にかかれていた列情報をそのまま使って SELECT 文の実行ができることがわかります:
2021011602


またこの実行結果はもとのオブジェクトストレージのバケット内の新 CSV ファイルとしても保存されています。必要に応じてダウンロードするなどして結果を確認できます:
2021011603


オブジェクトストレージに格納された CSV ファイルに対して直接クエリーを実行できるので、IoT システムなどによって記録された CSV ファイルをバッチ処理でオブジェクトストレージ内に格納できてしまえすれば、(SQL 型のデータベースにインポートすることなく)その CSV ファイルの中から条件にあった列だけを取り出すことができる、ということがわかりました。実行結果もオブジェクトストレージ内のファイルとして生成することができるので、IoT システムなどでセンサーデータを集める場合でも「とりあえずは CSV にしてオブジェクトストレージに入れておけば、条件を指定した取り出しは後からできる」ことになって便利だと感じました。




Node.js + Express の環境でウェブアプリケーションを開発していると、いくつかの方法で URL のパラメータを受け取る方法があります:
6dnng3pre04xxdebia1g


例えば、 "/xxx?name0=value0&name1=value1" というパス /xxx への GET アクセス時に URL パラメータである name0 及び name1 の値(それぞれ "value0" と "value1" に相当する部分)を取得するには以下のように req.query オブジェクトから取り出すという処理で実現できます:
var express = express();
var app = express();

app.get( '/xxx', function( req, res ){
  var name0 = req.query.name0;
  var name1 = req.query.name1;
    :
});

  :
  :

また、"/yyy/name2" というパスへの GET アクセス時における "name2" 部分を可変にしたい(例えば "/yyy/1" にアクセスした場合は name2 = "1" で、"yyy/2" にアクセスした場合は name2 = "2" として取り出したい)場合は、以下のように req.params オブジェクトから取り出すことで実現できます:
var express = express();
var app = express();

app.get( '/yyy/:name2', function( req, res ){
  var name2 = req.params.name2;
    :
});

  :
  :

ここまでは express の標準機能として実装されています。


で、今回挑戦したいのは "/zzz/name0/name1/name2/.../name9" というパスへの GET アクセス時における name0, name1, name2, ..., name9 の値を取り出すことです。この例ではパラメータが10個ですが、実際にはいくつ存在しているかわからないものとします(1個かもしれないし、10個以上かもしれない)。つまり "/zzz/" で始まるパスへの GET アクセス時に "/zzz" 以降のパス部分をまとめてパラメータ化して取得したい、という要望実現への挑戦です。

パラメータの個数が固定であれば(例えば3つであれば)、上述の "/yyy/:name2" の時の応用で、"/zzz/:name0/:name1/:name2/" のハンドリングを行って、req.params.name0, req.params.name1, req.params.name2 の値をそれぞれ取り出すことで実現できます。ただ今回はこのパラメータの数を可変にしたい場合の実現方法を考えたいのです。

その実現例の1つがこちらです:
var express = express();
var app = express();

app.use( function( req, res, next ){
  if( req.url.startsWith( '/zzz/' ) ){
    // '/zzz/' で始まるパスへのアクセスだった場合は、5文字目以降をパラメータとして取り出し、
// '/zzz?params=' の後ろに URL パラメータとしてくっつけてリダイレクトする var params = req.url.substr( 4 ); res.redirect( '/zzz?params=' + params ); }else{
// '/zzz/' で始まらないパスへのアクセスだった場合はそのまま処理する next(); } });
app.get( '/zzz', function( req, res ){ // params URL パラメータの値を取り出して、'/' で分割する var params = req.query.params.split( '/' );

// params[0] に "name0" が、params[1] に "name1" が、・・それぞれ格納されている : }); : :

可変階層のパスをハンドリングすることはできないので、該当部分を URL パラメータ(params)として処理するようにしています。そして /zzz/ で始まるパスへのアクセスがあった場合は app.use() による前処理として5文字目(2つ目の '/')以降を取り出して params パラメータの値に指定してリダイレクトするようにしています。こうすることで /zzz/name0/name1/name2/../name9 へのアクセスは /zzz?params=name0/name1/../name9 へアクセスするようリダイレクトされ、リダイレクトされた先で元のパラメータを残さず処理することができるようになります。

リダイレクトしての処理なので、ずるい(?)やり方ではあるんですが、一応これなら /zzz/name0/name1/name2/... へのアクセス全般を可変階層でも(リダイレクト先で)処理できそうです。


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 だけでサーブレット的な検索機能を実装することもできそうだ、ということが分かりました。


PHP で MongoDB を使っていると、「その中身を簡単に確認したい!」と思うことがままあります。

MySQL であれば phpMyAdmin など、有名なツールがいくつかありますが、MongoDB の場合は数が限られてい(るように思い)ます。

そんな中で自分が使っているのが phpMoAdmin です。phpMyAdmin と名前が似ていますが、おそらく意識しているのでしょう。

phpMoAdmin はダウンロード&展開するとわかりますが、 moadmin.php という1つの .php ファイルから作られている MongoDB 管理用GUIツールです。ファイル1つなので、このファイルをドキュメントルート以下におくだけで設置完了です。

なお、接続先の MongoDB サーバーの情報もこのファイルの中に直書きします(何も指定しないとローカルホスト状の MongoDB を参照しに行きます)。
例えば MongoDB サーバーがリモートの mongo.test.com というサーバーで、デフォルトの 27017 番ポートで稼働している場合、この moadmin.php ファイルをエディタで開いて 'MONGO_CONNECTION' の値として以下のように記述して保存します:
  define( 'MONGO_CONNECTION', 'mongodb://mongo.test.com:27017' );

これでウェブブラウザからこの moadmin.php を開けば、目的の MongoDB サーバーにアクセスして、コレクションやレコードの状態を参照することができるようになります。


ところで、MongoDB を使っていると大量のデータを扱うことが少なくありません(そのための No-SQL だと思っています)。コレクション(RDB でいう所のテーブル)を指定して、そのレコードの一覧を参照することは簡単ですが、特定の条件を満たすレコードだけを取り出して見ることはできないでしょうか? それが今から紹介するクエリー(Query)機能です。

クエリーを実行するには、コレクションのレコード一覧の画面から "[query]" と書かれた箇所をクリックします:
2014101601

するとクエリーを指定するフィールドが開きます。ここに目的のレコードに絞り込むクエリーを記述して "Query" をクリックすることでレコードを選別することができます。
2014101602


さて、意外と戸惑ったのが、このクエリーの指定方法です。例えば「item_id の値が'14'のレコード」を探したい場合、どのように指定すればいいのでしょうか?意外と資料や情報が少なくて難儀しました。

結論としては CakePHP の find の中で指定するような感覚で、以下のように記載します:
 array('item_id'=>'14')
2014101603

無事見つかりました。同様にして「item_id が '14' で、category_id が '10' のレコード」であればこんな感じになります:
 array('item_id'=>'14','category_id'=>'10')


注意が必要な点として、どうやらこのクエリーで数値を指定する場合、そのデータが数値文字列なのか、数値なのかを意識して指定する必要がありそう、ということです。例えば先程の例を
 array('item_id'=>14)
のように 14 を数値文字列ではなく数値として指定した場合、レコードは見つかりませんでした。
2014101604


要するにデータがどのような型で格納されているのかをちゃんと意識して指定する必要があるのだと思います。

このあたりを気をつけていれば軽量で使える便利なツールといえそうです。


 

このページのトップヘ