昨日は ↓ の簡単なコードで、PostgreSQL上でアドホックにRを実行するストアド関数を試しました。今日はもう少し実用的に、PostgreSQLに入っているデータを渡す方法について。実行環境は昨日と同様Windows 7 32bit + PostgreSQL Portable 9.4 + R 3.1.2 + PL/R 8.3.0.16です。
一時的なTSV(タブ区切りテキスト)ファイルで渡す
今日試した ↓ は原始的な方法で、最初に適当なダミーデータでビューを作り、次にCOPYコマンドで一時TSVファイルに出力。ここまではPL/Rと関係ない、一般的なPostgreSQLの使用です。三つ目のブロックが、昨日作ったPL/R用の「擬似DO文」ストアドを利用する所で、Rのread.delim関数を使いTSVを読み込み。コードの下に、実行風景の画像を示します。
-- create dummy data
CREATE VIEW test.dummy AS
SELECT concat('2015-05-', day) :: date AS "ymd",
round(random() :: numeric * 100, -1) AS "foovar",
CASE WHEN random() < 0.5 THEN 0 ELSE 1 END AS "event"
FROM generate_series(1, 31) "day";
-- save to a temporary file
COPY (SELECT * FROM test.dummy)
TO 'R:/to_plr.tsv' (DELIMITER E'\t', FORMAT csv, HEADER TRUE);
-- pseudo DO
DO $_d_$
BEGIN
PERFORM test.plr_do($_r_$
dat = read.delim('R:/to_plr.tsv', stringsAsFactors=F)
head(dat, 20)
$_r_$);
END $_d_$;
上の例では一時TSVの場所をRドライブとし、事前にPostgreSQLのユーザにRドライブへの読み取り・書き込み権限を与えています。COPYコマンドでの出力先指定(TOの次)は絶対パスが必要。またCOPYコマンドのオプションでFORMAT csvとあるのはtsvの間違いではなく、このオプションを入れることで、区切り文字とヘッダ有無を設定できます。(詳細はドキュメントを参照)
一時TSVの場所をCOPYとRコードで2回入力するのが難点ですが、1回で済ますには、DOの中にCOPYコマンドを入れ、ファイルパスを変数化し、COPYコマンドを動的クエリとして組み立ててEXECUTEするという回りくどい方法しか無さそうで、元々の趣旨(アドホックな手軽な実行)から離れるため不採用。
Rには、CSVやTSVなどのテキストファイルを読み込むread.table関数とその一群があり、今日使ったread.delimもその一つです。詳細は下のドキュメントを参照。オプション引数でstringsAsFactors=Fを指定しないと、数値の列も因子型になってしまうのが少し面倒。簡単にする方法がないか、今後調べます。
»R Documentation : read.table {utils}
実行例と制約
これで一応アドホックに実行するRの中へPostgreSQLデータを渡せたので、後はRの処理を適宜追加できます。下のExample 1はデータの第2・3列でクロス集計、Example 2はglm関数でロジスティック回帰。どちらの結果も、特にprint文は必要ありませんでした。
-- Example 1
DO $_d_$
BEGIN
PERFORM test.plr_do($_r_$
dat = read.delim('R:/to_plr.tsv', stringsAsFactors=F)
table(dat[2:3]) # contingency table
$_r_$);
END $_d_$;
-- Example 2
DO $_d_$
BEGIN
PERFORM test.plr_do($_r_$
dat = read.delim('R:/to_plr.tsv', stringsAsFactors=F)
options(show.signif.stars = F)
summary(glm(dat$event ~ dat$foovar, family=binomial)) # logistic regression
$_r_$);
END $_d_$;
普通にRを使うのと違いPostgreSQLサーバ上に一時的にRを乗せているような感じなので、制約もあります。例えばhead、summaryといった何か出力する関数を複数箇所で記述しても、最後の結果しか表示されません。対策としてはRのsink関数を使う(全ての出力を一時ファイルに保存できるので、それを最後にまとめて読み込む)ことが考えられ、今後必要になったら試します。
あと試しに出力ファイル指定なしでplotを実行したら、Rのコンソールで行うのと同様、グラフィックウィンドウが立ち上がりました。が、それを手動で閉じたり移動したりできず、クエリツールで再び何か実行したら消えるという微妙な挙動。もう少し調べて別記事にします。