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

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

タグ:dashdb

リレーショナルデータベースにデータを格納する場合、まずテーブルの定義を行って、その後にテーブル定義に沿ったデータを挿入(insert)していくことになります。

例えば、以下のようなデータをリレーショナルデータベースに格納することを考えてみます:
2016112101


(主キーとか制約とか)深く考えずにこのデータ格納用テーブルを定義すると、その SQL はこんな感じになると思います:
create table users( id long, name varchar(100), height float, weight float );

いわゆる「普通の」リレーショナルデータベースの場合、テーブルは行指向になります。1つのレコードを1行のデータと見なして格納します(直感的に理解しやすいと思います):
2016112102


これに対して、列指向と呼ばれるテーブル定義の場合、データは列ごとにまとまった形で持つことになります。そのため1つのレコードを格納する場合も、複数の列のデータに各値を追加することになります(こちらは直感的には理解しにくいと思います):
2016112103


なぜこんな直感的にわかりにくいテーブル定義が存在しているのか、というと、この方が便利になるケースがあるからです。例えば「身長の平均値」を取り出そうとすると、SQL では AVG 関数を使って以下のような処理を実行することになります:
select avg(height) from users;

SQL ではシンプルに見えますが、行指向データベースでこの時に内部で実行される処理は比較的重いものになります。各レコードを取り出し、各レコードの身長(height)の値を取り出しては合計して平均値を取り出し・・・という処理を行います。レコード数が増えるほど「各レコードを取り出す」回数が増え、全体負荷が大きなものになります。Oracle や SQL Server だと一発で標準偏差を求める STDDEV 関数とかもありますが、これも処理はかなり重いものです。

一方これが列指向データベースの場合、身長列のデータをひとまとめに取り出せるので、そこから平均値を計算するのが比較的楽になります。

では列指向データベースの方がパフォーマンスに優れているのか、というとそうとも限りません。データを1つ(1レコード)挿入する場合の内部処理を考えると、行指向では1つのレコードを追加するだけですが、列指向では各列に値を1つずつ追加することになります。データの追加や更新といった処理におけるパフォーマンスは、一般的には行指向データベースの方が優れていることになります。

要するに利用用途に応じて行指向データベースと列指向データベースを使い分けるのが理想ということになります。ただ「普段は行指向データベースを使って、統計処理を行う時のために列指向データベースに定期的に同期する」といったように、行指向データベースと列指向データベースを連携させる必要が出てきた場合など、データや形式の互換性などをあらかじめ考慮した上で製品やサービスを選ぶ必要がでてきます。


さて、そこで dashDB です。IBM Bluemix 上のサービスの1つとして提供されている DBaaS のデータベースですが、その特徴の1つに「テーブル定義の際に行指向か列指向かを指定できる」ことがあります。

例えば上記のテーブルを dashDB 上に行指向テーブルとして作成する場合は以下のような SQL コマンドを実行します:
create table users( id long, name varchar(100), height float, weight float ) organized by row;

一方、同じテーブルを列指向テーブルで作成する場合はこちらの SQL コマンドを実行します(ちなみに dashDB ではこちらがデフォルト):
create table users( id long, name varchar(100), height float, weight float ) organized by column;

つまり dashDB を使えばデータベース間の互換性を意識する必要もなく、1つのデータベースサービスインスタンスの中でテーブル毎に行指向か列指向かを指定して定義することができるのでした。トランザクション用途であれば行指向、分析用途であれば列指向といった具合にテーブルを定義することで、データベースサーバーの使い分けを意識することなく両方の用途で利用できるクラウドの DBaaS であることが最大の特徴だと思っています。

ちなみにこれが IBM Bluemix での dashDB のアイコンです。左が(普通の)dashDB で、右がトランザクションモードの dashDB Transaction 。パッと見て列指向か行指向かがわかるようになっている(らしい)です:
2016112201


加えて dashDB は Bluemix 上のフルマネージドな RDB クラウドサービスなので、データベースサーバーとしての管理は不要です。しかもデータ量が無料枠である1GB 未満であれば課金されることもないため、データベースとして本格的に利用する前の検証用途として安心して使ってくださいませ。


以前からずっと悩んでいたのが、PHP と IBM DB2 の相性の悪さ(苦笑)でした。

知らない方が多いと思うので補足すると、PHP からデータベースにアクセスする場合には PDO(PHP Data Object) の拡張モジュールが必要になります。更に PDO に加えてデータベースの種類毎(例えば MySQL とか PostgreSQL とか)に接続するための拡張モジュールをシステムに導入しておく必要があります。

CentOS での例を書いておくと、例えば MySQL や PostgreSQL を使う場合はそれぞれ以下のようにして、これらのモジュールをインストールすることができます(PDO 拡張モジュールごとインストールできます):
(MySQL の場合)
# yum install php-mysql
(PostgreSQL の場合)
# yum install php-postgresql

さて IBM DB2 のケースです。普通に "yum install php-db2" などと入力しても「何それ?」みたいな扱いになります(苦笑)。
# yum install php-db2
  :
  :
パッケージ php-db2 は利用できません。
エラー: 何もしません

ではどこから拡張モジュールをインストールするのか・・・ と思って調べてみたのですが、かなりハードル高そうです:
http://php.net/manual/ja/ibm-db2.installation.php


↑リンク先を読んでいただくとわかりますが、自分でヘッダファイルやライブラリファイルを用意して、ビルドしろ、ということになっています。ということは DB2 のインストールモジュールが必要になるわけで・・・ 普通に PHP から使いたいだけなのですが、クラウドの DBaaS として使うにはかなりハードルが高めに設定されているように見えますね。。


一方で IBM Bluemix 環境であれば、このハードルはぐっと下がります。なにしろ「用意されたビルドパックを使うだけ」です。

前提として、IBM Bluemix 上に dashDB のインスタンスを1つ作成し、以下のようなテーブル(names)を定義しておきます:
2016111401
↑ID(int) と NAME(varchar(20)) だけのテーブル定義

2016111402
↑3つのレコードを入力済み


この dashDB インスタンスを、IBM Bluemix 上に作成した PHP ランタイムからバインドしてアクセスする、というアプリケーションを作ります:
2016111401



ビルドパックはこちらにあるものをそのまま利用します:
https://github.com/ibmdb/db2heroku-buildpack-php

ソースコード(index.php)はこんな感じで用意します(ホスト名やユーザー名、パスワードなどは Bluemix の接続情報を参照して取得します):
<html>
<head>
<title>DB2</title>
</head>
<body>
<table border="1">
<tr><th>ID</th><th>NAME</th></tr>
<?php
$host = "xxxxx.services.dal.bluemix.net"; # DB2(dashDB) のホスト名
$port = 50000; # ポート番号
$db = "BLUDB"; # データベース名
$username = "(username)"; # ユーザー名
$password = "(password)"; # パスワード

$conn_str = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=" . $db . ";HOSTNAME=" . $host . ";PORT=" . $port . ";PROTOCOL=TCPIP;UID=" . $username . ";PWD=" . $password . ";";
$conn = db2_connect( $conn_str, $username, $password );
if( $conn ){
  $sql = "SELECT * FROM \"names\"";
  $stmt = db2_prepare( $conn, $sql );
  $result = db2_execute( $stmt );
  if( $result ){
    while( $row = db2_fetch_array( $stmt )){
?>
<tr><td><?php print_r($row[0]); ?></td><td><?php print_r($row[1]); ?></td></tr>
<?php
    }
  }else{
    $error = db2_stmt_error( $stmt );
    $errormsg = db2_stmt_errormsg( $stmt );
?>
<tr><td><?php print_r($error); ?></td><td><?php print_r($errormsg); ?></td></tr>
<?php } } ?> </table> </body> </html>

PHP 内で dashDB のインスタンスに接続して SQL を実行し、全レコードを取り出して表示する、という内容になっています。PHP としては非常にシンプルですが、"db2_" で始まる拡張関数が数か所使われており、DB2 用のコードになっていることがわかると思います。

こいつを cf コマンドでプッシュします。その際に -b オプションで上記のビルドパックを指定します:
# cf push (アプリ名) -b https://github.com/ibmdb/db2heroku-buildpack-php

この方法で作成したアプリケーションにアクセスすると、PHP の IBM DB2 拡張が読み込まれて実行され、期待通りの結果が表示されます:
2016111403


PHPer の皆さん、IBM Bluemix と dashDB であれば面倒な手続きなく PHP からも使えます。是非アプリを作ってみましょう!


なお、PHP の IBM DB2 拡張機能については公式ドキュメントが存在しているので、関数リファレンスも含めて詳しくはこちらを参照ください:
http://php.net/manual/ja/book.ibm-db2.php


IBM Bluemix から提供されている解析用 DBaaS の dashDB には R Studio 環境が付属しています(オープンソース製品としての R Studio 環境がセットアップ済みの状態から使えます)。個人的にもよく使ってます。



特に今回のように R Studio 環境を紹介するようなブログを書いていると、R Studio のスクリーンショットを撮りたくなることも珍しくないのですが、色々使った後の R Studio コンソール画面(下図の画面左半分)はごちゃごちゃしていたり、そもそも見せたくない内容が含まれていたりします。このコンソール部分をクリアする方法が分かっていなかったのですが、キーボードの Ctrl + L でクリアできることを知りました:
2016110201


色々操作した後に Ctrl + L すると、コンソールがクリアされます:
2016110202


それ以外にもこの辺りのショートカットが便利そうです:
- Ctrl + 1 : ソースエディタに移動
- Ctrl + 2 : コンソールに移動





 

Bluemix 上のデータストレージサービスである Cloudant と dashDB はデータを連携することができます。以前には Cloudant から dashDB へのレプリケーションによって実現できる、という記事を紹介しました:
Cloudant => dashDB の単方向レプリケーション

簡単に紹介すると dashDB 側から Cloudant のデータをプルする形で複製を作ることで実現する、という内容だったのですが、2016/Jan/28 時点ではこの方法は使えなくなっています。代わりに Cloudant 側から dashDB 上にデータウェアハウス用DBを作る、という(逆の)データエクスポート機能によって実現できるようになりました。 というわけで、その手順を紹介します。


まずは Cloudant 側にエクスポートするデータが格納されている必要があります。既に Cloudant でデータが溜まっているのであればそのデータを使ってください。これからデータを集める場合は Bluemix の Node-RED スターターで集めるのが簡単だと思います:
2016012801


"Node-RED Starter" ボイラープレートを使って、アプリケーション環境を作成します:
2016012802


Bluemix 上に Node.js アプリケーションサーバーと Cloudant データベースサーバーが作られ、Node-RED アプリケーションが稼働している状態が作れました。ここから Node-RED 画面に移動します:
2016012803


ここで Node-RED を使ってセンサーのデータを集めます。この辺りの詳しい手順はこちらを参照してください:
Bluemix の Node-RED サービスで IoT アプリを作る(1/2)
Bluemix の Node-RED サービスで IoT アプリを作る(2/2)
2016012805


この例では Cloudant 上の "iotdata" データベースにデータを集めました。このデータベースを dashDB にエクスポートしてみます:
2016012804


左メニューの "Warehousing" を選び、サブメニューの "Warehouses" を選択すると、現在作成されたウェアハウスの一覧が表示されます。初回は一覧に何もないので "Create a warehouse" ボタンがあるだけだと思います。今からウェアハウスを作るのでこのボタンをクリックします(またはメニューから "Warehousing" - "Create  a Warehouse" を選択します):
2016012807


「ウェアハウス」の実体が Bluemix 上の dashDB になります。というわけで、ここで Bluemix の ID とパスワードを入力してログインします:
2016012801


ログイン後、作成するウェアハウスの情報を入力します。ウェアハウスの名前を指定し、そして(今回は)ウェアハウスの dashDB を新規に作成するので "Create new dashDB instance" を選択します:
2016012802


そしてウェアハウス化する Cloudant のデータベースを指定します。今回は "iotdata" というデータベースを対象にします。途中まで入力すると候補が出てくるのでそこから選びます:
2016012803


対象データベースに "iotdata" が追加されました。必要に応じてここで複数のデータベースを追加指定することも可能です。最後に "Create Warehouse" をクリックします:
2016012804


しばらく待ちます・・・・
2016012805


Cloudant 上の "iotdata" データベースが指定した名前でウェアハウス化されました。"Open in dashDB" をクリックすると dashDB 上のデータとして開くことができます:
2016012806


dashDB で開いた時の画面です:
2016012807


メニューから Tables を選び、データベース名(IOTDATA)を選択すると、Cloudant から複製されたデータベースが確認できます。下図は Table Definition タブが選択された状態で、テーブル定義を確認できます:
2016012808


隣の Browse Data タブを選ぶと、実際のデータレコードが確認できます。Cloudant のデータが RDB である dashDB 内に格納されていることが確認できました:
2016012809


ちなみに、この時 Bluemix のダッシュボードを確認すると、未バインドの dashDB インスタンスが米国南部データセンター内に作られていました(元の Cloudant が米国南部以外で作られていても、dashDB は米国南部に作られるようです):
2016012808


また、ウェアハウスを作成した段階では Cloudant にも "_warehouser" というデータベースが作成されていました。2ドキュメントなので、おそらく管理用データベースかな:
2016012801


 

IBM Bluemix 内で利用可能な統計解析用の DBaaS である dashDB は、標準機能の一部として R 言語の実行環境である R Studio を内蔵しています:
http://www.ibm.com/software/data/dashdb/


この R Studio に dashDB 内のデータを呼び出した上で R 言語を使ってデータの解析を行う(具体的にはデータ間の相関関係を調べる)という一連の手順を紹介します。

この手順を行う上で、まず IBM Bluemix 上で dashDB を使えるようにすることと、dashDB 内に解析に使えるようなデータが格納されている必要があります。先日の別ブログでラズベリーパイのセンサーデータ(CPU温度、CPU負荷率など)を取得して、dashDB 内のテーブルに格納する、という手順を紹介しているので、実際にデータを取得して試してみたい、という方はこちらのエントリを参照してください:
Node-RED(QuickStart) のデータを dashDB に格納する


なお、ラズベリーパイを所有していないとか、或いはデータを集めるのが面倒、という場合は、実際に上記の方法で集めたデータを github 上に公開しておきましたので、こちらのデータを dashDB にインポートして使ってください(ちなみにこのデータはラズベリーパイに実際に負荷を与えて CPU 温度や CPU 負荷に変化を起こした時の、ある意味で解析しやすいデータになっています)。以下その手順を紹介します:

まずは github 上の以下のサイトから CSV データをダウンロードしてください:
https://raw.githubusercontent.com/dotnsf/RPDATA/master/RPDATA.csv
(上記 URL を開いて、内容を「名前を付けて保存」してください。ファイル名は RPDATA.csv としてください)

次に IBM Bluemix から dashDB のウェブコンソールからテーブル一覧を開きます。その手順は上述の「Node-RED(QuickStart) のデータを dashDB に格納する」のリンク先を参照してください。

テーブル一覧画面で "Add Table" ボタンをクリックします:
2015121501


"Create a table" ダイアログで作成する RPDATA テーブルの定義を指定します。DDL に以下の内容を指定して "Run DDL" ボタンをクリックします:
2015121508
CREATE TABLE "RPDATA"
(
  "ID" VARCHAR(20),
  "CPUTEMP" DOUBLE,
  "CPULOAD" DOUBLE,
  "SINE"  DOUBLE
);

成功すると "DDL ran successfully" というメッセージが表示されます:
2015121503


成功したらテーブル一覧に RPDATA テーブルが追加されているはずなので確認してみます。下図のような定義内容で追加されているはずです(ただしこの時点ではデータの中身は空です):
2015121507

(注 このテーブルのスキーマ名(上記画面では Schema で指定されている DASH104338)はこの後で R Studio からこのデータを呼び出す時に必要になるので、メモするなどして控えておいてください)


続けて中身となるデータもインポートしましょう。画面左メニューから "Load" - "Load from Desktop" を選択します:
2015121505


インポートデータを指定します。"Browse files" ボタンをクリックしてダウンロードした RPDATA.csv ファイルを指定します。また "Does row one contain the column names?" には "Yes"、Separator character には "comma" を指定します。一番下の "Does the file has columns that contains dates or times?(ファイルには日付や日時のデータを含むか?)" という質問には "No" を選択して "Load file" をクリックします:
2015121506


ファイルが読み込まれ、ロードされる予定のデータのプレビューが行われます。中身を確認して "Next" をクリックします:
2015121507


次にロード先テーブルを指定します。既に目的のテーブル(RPDATA)は作成済みなので、"Load into an existing table" を選択して "Next" をクリックします:
2015121508


テーブルの指定画面で RPDATA を選択し、"Append new data into the table"(テーブルにデータを追加する)を選択して "Finish" ボタンをクリックします:
2015121509


データのロードが実行されます。この CSV のロードが全て成功すると 189 行のデータレコードが作成されます。"Number of rows read = 189" で "Number of rows rejected = 0 " と表示されていれば全てのデータが正しく読み込まれてインポートされたことになります。画面下部にはインポートされたデータの一部がプレビューされています:
2015121510


改めてテーブル一覧に戻って RPDATA テーブルを選び、"Browse Data" タブを選択すると、インポートされた 189 行のレコードデータが確認できます:
2015121511


これで実際にデータを取得しなくても、動いていたデータの dashDB へのインポートが完了しました。では dashDB に格納されたデータを R Studio に呼び出して解析処理を行ってみます。なお、以下はここで CSV からインポートして作成した RPDATA を使って解析処理を行うため、実行結果は CSV からインポートした場合と同じものになるはずです(実際に皆さんのラズベリーパイのデータを取得した場合は異なる解析結果になると思います。実行手順の参考として確認してください)。

まずは R Studio にログインするための接続情報を確認しましょう。dashDB ウェブコンソール画面の左メニューから "Connect" - "Connection information" を選択します:
2015121501


データベースや R Studio に接続するための接続情報が表示されます。特にこの中で User IDPassword が必要になるので、どこかにメモしておきましょう:
2015121502


では改めて R Studio 画面に向かいます。dashDB ウェブコンソール画面の左メニューから "Analytics" - "R Scripts" を選択します:
2015121503


R Scripts 実行画面が表示されますが、ここは飛ばして R Studio を起動するので "R Studio" と書かれたボタンをクリックします:
2015121504


初回は R Studio にログインするための Username と Password が求められます。先程メモした接続情報の内容を入力します(必要であれば Stay signed in にチェックを入れて次回以降のログインを不要にします)。最後に "Sign in" ボタンをクリックして R Studio を起動します:
2015121505


以下の様な R Studio の画面が表示されれば成功です。これで dashDB から R Studio の呼び出しに成功しました:
2015121506


R Studio 画面左側の Console に R 言語を入力してデータの解析を行います。まずは以下の3行を1行ずつ入力していきます(青字が実行コマンド、赤字はコメント):
> mycon <- idaConnect("BLUDB", "", "") BLUEDB(dashDB)に接続
> idaInit(mycon) 初期化
> mydata <- as.data.frame(ida.data.frame('"DASHXXXXXX"."RPDATA"')[ ,c('ID','CPUTEMP','CPULOAD','SINE')])
 ↑スキーマ名とテーブル名、テーブル内の列名を指定して全データを指定し、mydata という名前の変数に代入

この3行を実行した時点で、RPDATA テーブルの内容が mydata という R 言語の変数に代入されています。画面右上部にも mydata 変数の中身(の一部)が確認できるようになっています:
2015121509



この状態からは普通に R 言語と mydata 変数を使って、統計計算を行っていくことができます。なので R 言語に詳しい人であれば好きに使っていただいて構いません。 以降はあまり詳しくない人のためにもう少し続けます。まずはデータのサマリーを参照してみましょう。先程の3行に続けて、Console に以下の1行を指定してください(青字が実行コマンド、黒字が実行結果です):
> summary(mydata)
ID CPUTEMP CPULOAD
Length:189 Min. :46.54 Min. :0.0400
Class :character 1st Qu.:47.62 1st Qu.:0.1700
Mode :character Median :47.62   Median :0.3400
Mean :48.14 Mean :0.3784 
3rd Qu.:48.69 3rd Qu.:0.5800
Max. :50.84 Max. :0.8800
SINE
Min. :-1.000000
1st Qu.:-0.720000
Median : 0.010000
Mean : 0.005608
3rd Qu.: 0.720000
Max. : 1.000000 
2015121510


CPULOAD, CPUTEMP, SINE の実行結果として Min., 1st Qu., Median, Mean, 3rd Qu., Max. という結果が表示されていますが、これらはそれぞれ以下の様な意味です:
表示意味
Min.最小値
1st Qu.下から4分の1のデータの値
Median中間値
Mean平均値
3rd Qu.下から4分の3(上から4分の1)のデータの値
Max.最大値


例えば CPUTEMP(CPU温度)であれば、測定期間中の最小は 46.54℃、最大は 50.84℃で、平均値は 48.14℃。一方、中間値(全データをソートした時の中間にあるデータの値)は 47.62℃なので全体的に平均よりも上に分布しているデータであることがわかります。

では CPUTEMP(CPU温度), CPULOAD(CPU負荷), SINE(サインカーブ)の3つのデータについて相関関係を調べてみます。実際に調べる前の時点で CPU 温度と CPU 負荷には何らかの相関関係があるような気がしますよね。一方これらと(決まった変化をとり続ける)サインカーブ値には何の関係もないような気がします。ただ実際にそのようなデータになっているかどうかを客観的に調べるのが相関係数です。相関係数は -1 から 1 までの間を取る値で、1に近いほど順の相関関係がある(一方が増えると、もう一方も増える関係にある)、-1 に近いほど逆の相関関係がある(一方が増えると、もう一方は減る関係にある)、0に近いほど相関関係がない(独立して変化する)、という関係を示す指数です。 R 言語では cor 関数を使って複数の相関関係を一発で調べることができます。パラメータには相関関係を調べたい変数をまとめて指定できます。今回はこの3つの値の相関関係を調べます。mydata 変数内ではこれらの値は2番目から4番目(1番目はID)なので、以下のように指定して実行します:
> cor(mydata[2:4])
CPUTMEP CPULOAD SINE
CPUTEMP 1.00000000 0.6329742 0.03631856
CPULOAD 0.63297415  1.0000000 -0.02040800
SINE 0.03631856 -0.02040800 1.00000000 
2015121511


結果は表の形で出力されます。CPUTEMP, CPULOAD, SINE それぞれの相関係数が2つの交点として出力されています。例えば CPUTEMP と CPULOAD の相関係数は 0.6329742 です。これはまあ比較的1に近い数字と考えられるので、相関関係があると言えそうです。一方、CPUTEMP と SINE の相関係数は 0.03631856 で 0 に近いので相関関係はなさそうです(CPULOAD と SINE も相関係数 -0.02040800 なので相関関係はなさそうです)。 このような結果から最初に推測した相関関係はデータでもある程度実証された、と言えそうです。


と、まあこんな感じです。dashDB というリレーショナルデータベースの DBaaS に格納されたデータを R Studio で読み込んで R 言語で統計処理を行う、ということができちゃうわけです。そしてこの dashDB 自体も実体としては IBM DB2 なので、JDBC などで普通にアクセスすることができます。クラウドのデータベースもアナリティクス機能を普通に搭載する時代になってきたんですねー。


このページのトップヘ