PostgreSQLとPL/R - fousの日記

Rって知っていますか？
OSSな統計解析言語です。
というわけで、PostgreSQL上でRを使うためのメモ。RやPostgreSQLの説明はいくらでもあるので、省略しています。

Setup

Joseph Conway - PL/RというRでPostgreSQLのFunctionを記述する言語を使用します。
Debian Lennyでやりたかったのですが、肝心のPL/Rがsqueeze以降にしかなかったので、パッケージを借りてきています。

postgresql-plrパッケージをインストールし、PL/RをCREATE LANGUAGEするためのSQLを流し込みます。これだけ。

aptitude install postgresql-plr
psql -f /usr/share/postgresql/8.3/plr.sql -U postgres YOUR_DATABASE

Basics

さっそくhttp://www.joeconway.com/plr/doc/plr-funcs.htmlにあるサンプルを実行してみます。

CREATE TABLE emp (name text, age int, salary numeric(10,2));
INSERT INTO emp VALUES ('Joe', 41, 250000.00);
INSERT INTO emp VALUES ('Jim', 25, 120000.00);
INSERT INTO emp VALUES ('Jon', 35, 50000.00);
CREATE OR REPLACE FUNCTION overpaid (emp) RETURNS bool AS '
    if (200000 < arg1$salary) {
        return(TRUE)
    }
    if (arg1$age < 30 && 100000 < arg1$salary) {
        return(TRUE)
    }
    return(FALSE)
' LANGUAGE 'plr';
SELECT name, overpaid(emp) FROM emp;

注意点として、PL/RのCREATE FUNCTION文はデータベースのsuper user権限が必要です。postgresユーザーなどですね。Rにはコマンド実行やファイル入出力などシステムに影響を与える命令も使用できるので、仕方ないです。どうしても使用したければCREATE LANGUAGE時にTRUSTEDオプションを与えればできる・・・かも。

Graph part 1

Rは統計解析ソフトであると同時に、結果をわかりやすく表示するために、様々なグラフを描画する機能があります。描画したグラフは画像ファイルなどに保存することができます。

PL/Rでの簡単なサンプルとしてはMaking Graphs with PostgreSQL and Rがあります。ここにあるサンプルで

CREATE TABLE temp (x int, y int);

INSERT INTO temp VALUES(4,6);
INSERT INTO temp VALUES(9,4);

CREATE OR REPLACE FUNCTION f_graph() RETURNS text AS
'
str <<- pg.spi.exec (''select x as "my a" ,y as "my b" from temp order by x,y'');
pdf(''/tmp/myplot.pdf'');
plot(str,type="l",main="Graphics Demonstration",sub="Line Graph");
dev.off();
print(''done'');
'
LANGUAGE plr;

とやると、/tmp/myplot.pdfが作成されます。簡単。

Graph part 2

これだけだとファイルとして保存されるだけなので、使い勝手がよくないです。
できればPostgreSQLの値として、画像データがほしいですね。
単純なようにみえますが、これをPL/Rで実現するのはちょっと面倒です。

まずいくつか追加のパッケージをインストールします。

aptitude install r-cran-cairodevice r-cran-rgtk2 xvfb

画像データを得るために、RGtk2とcairoDeviceというRのパッケージを使います。
しかしRGtk2には一つ問題が。なんと、Xサーバーがないと利用できない。

PostgreSQLのためにXサーバーをあげるのは嫌なので、xvfbというダミーのXサーバーを起動します。
DISPLAYの設定も必要なので、DISPLAY環境変数を設定します。

Xvfb :5 -screen 0 1024x768x24 -ac &
echo "DISPLAY = ':5.0'" >> /etc/postgresql/8.3/main/environment

これでPostgreSQLを再起動すれば、PL/RからRGtk2が使用できるようになります。
この例ではXvfbをコマンドラインから起動しているので、別途サービスとして起動するようにしておくといいと思います。が、Xvfbのパッケージにはinitスクリプトはない模様。

実際にグラフの画像データを返すFunctionの例はこんなのです。えらく増量しましたが、実際にデータをとってきて描画するのは真ん中の2行だけです。plot文をpie(unlist(sp));とすると、円グラフになります。

CREATE OR REPLACE FUNCTION plr_g3() RETURNS bytea AS
'
  library(cairoDevice)
  library(RGtk2)
  pixmap <- gdkPixmapNew(w=500, h=500, depth=24)
  asCairoDevice(pixmap)
  
  sp <<- pg.spi.exec (''select generate_series(1, 10), generate_series(1, 5)'');
  plot(sp, type="l");

  plot_pixbuf <- gdkPixbufGetFromDrawable(NULL, pixmap,
  pixmap$getColormap(),0, 0, 0, 0, 500, 500)
  buffer <- gdkPixbufSaveToBufferv(plot_pixbuf, "png",
  character(0),character(0))$buffer
  return(buffer)
'
LANGUAGE plr;

これをこんな感じのCGIから呼び出すと、見事にグラフが表示されます。データベースからの動的グラフ作成ソフトとしても使える・・・かも？

my $q = new CGI;
print $cgi->header(-type=>'image/png',-expires=>'+3d');
my $data = $dbh->selectrow_array("SELECT plr_get_raw(plr_g3())") || die $@;
print $data;

Setup

Basics

Graph part 1

Graph part 2

References