R と PostgreSQL

R と PostgreSQL

PL/R†

PostgreSQL 用 R 手続き型言語(R Procedural Language for PostgreSQL)

PL/R とは†

PostgreSQL の SQL よりRの機能を呼び出す。

インストール(Linux)†

• PL/R のソースをPostgreSQL の contrib で解凍
• make を実行してビルド
• "make install" でライブラリをインストール
• contrib/plr ディレクトリにある plr.sql で、任意のデータベース内で有効にする
  • psql -d mydatabase -f plr.sql

インストール(Win32)†

• Win32 インストーラ、ソース
• Win32実行形式 旧版
• チュートリアル（英文） ボロノイ図の生成例

利用方法†

関数の基本形†

　以下のようにPL/R形の手続きを定義する

CREATE OR REPLACE FUNCTION 関数名 (引数タイプ)
RETURNS return-type AS '
   関数本体　−−　ここにRのスクリプトを書く
' LANGUAGE 'plr';

• R スクリプトのみの例
  　引き数の変数名は以下のように、arg1,arg2 ...., argn となる。

CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
   if (arg1 > arg2)
      return(arg1)
   else
      return(arg2)
' LANGUAGE 'plr' STRICT;

PostgreSQL 7.5 では、以下のように引数名が定義できる。この引数名はSQLの関数内でそのまま使える。

CREATE OR REPLACE FUNCTION sd(vals FLOAT8[]) RETURNS FLOAT AS '
   sd(vals)
' LANGUAGE 'plr' STRICT;

上のSQL文では最後に"STRICT"を入れてNULL値対策をおこなっている。

"STRICT" を使用しない場合

CREATE OR REPLACE FUNCTION r_max (INTEGER, INTEGER) RETURNS INTEGER AS '
   if (is.null(arg1) && is.null(arg2))
       return(NULL)
   if (is.null(arg1))
       return(arg2)
   if (is.null(arg2))
       return(arg1)
   if (arg1 > arg2)
       return(arg1)
   arg2
' LANGUAGE 'plr';

　複合型の戻り値を返す例(データフレームの値を返す)。

CREATE OR REPLACE FUNCTION get_emps() RETURNS SETOF emp AS '
   names <- c("Joe","Jim","Jon")
   ages <- c(41,25,35)
   salaries <- c(250000,120000,50000)
   df <- data.frame(name = names, age = ages, salary = salaries)
   return(df)
' LANGUAGE 'plr';

SELECT * FROM get_emps();

 name | age |  salary
------+-----+-----------
 Jim  |  41 | 250000.00
 Joe  |  25 | 120000.00
 Jon  |  35 |  50000.00
(3 rows)

　SQL内にRスクリプトを定義せずに、R関数と同名かつ引数の型も同じであるSQL関数を定義する場合。その際、SQL関数内はなにもない。

CREATE OR REPLACE FUNCTION sd(_float8) RETURNS FLOAT AS '' LANGUAGE 'plr';
SELECT ROUND(sd('{1.23,1.31,1.42,1.27}'::_FLOAT8)::NUMERIC,8);
   round    
------------
 0.08180261
(1 row)

大域データ†

データベースアクセスサポート関数†

　以下のコマンドをPL/R手続きの本体より実行するとデータベースにアクセスできる。

• pg.spi.exec (問い合わせ文の文字列)

• pg.spi.prepare (character query, integer vector type_vector)
  後で実行する問い合わせ文を準備し、保存しておく。

• pg.spi.execp (external pointer saved_plan, variable listvalue_list)
  pg.spi.prepare で事前に用意された問い合わせ文の実行

• pg.spi.lastoid()
  pg.spi.exec または pg.spi.execp 経由で最後の問い合わせが挿入された行の OID を返す。

• pg.quoteliteral (character SQL_string)

• pg.quoteident (character SQL_string)

• pg.thrownotice (character message)
  　PostgreSQL NOTICE メッセージの発行
• pg.throwerror (character message)
  　PostgreSQL ERROR メッセージの発行。これ以降の処理が放棄され、現在のトランザクションも中止される。

• pg.spi.factor (data.frame data)
  R のデータフレームを入力として受け入れ、すべての非数値カラムをファクターに変換する。

PostgreSQL サポート関数†

• install_rcmd (text R_code)
  文字列で与えられた R のコードをインタープリータにインストール
• reload_plr_modules ()
  plr_modules テーブルより、R のコードを強制的にリロード
• plr_singleton_array (float8 first_element)
• plr_array_push (float8[] array, float8 next_element)
• plr_array_accum (float8[] state_value, float8 next_element)
• load_r_typenames()
• r_typenames()
• plr_environ()
  Postmaster が現在動作している環境の表示

集約関数†

起動時にRモジュールを読み込む†

PL/R はインタープリータの初期化中に R のコードを自動ローディングをすることをサポートしている。PL/R は plr_modules という R コードのモジュールを持つことになる特別なテーブルを使っている。このテーブルがある場合には、テーブルに定義されているモジュールがテーブルから持ってこられて、作成後すぐさま R インタープリータにロードされる。

以下に plr_modules の例を示す。

CREATE TABLE plr_modules (
  modseq int4,
  modsrc text
);

上の例でフィールド modseq はインストールの順序を制御し、フィールド modsrc には R のコードが入っている。以下にこれらの作成例を示す。

INSERT INTO plr_modules
 VALUES (0, 'pg.test.module.load <-function(msg) {print(msg)}');
 

PL/R は以下のように直接簡単に参照できる。

CREATE OR REPLACE FUNCTION pg_test_module_load(text) RETURNS TEXT AS '
  pg.test.module.load(arg1)
' LANGUAGE 'plr';

SELECT pg_test_module_load('hello world');
 pg_test_module_load 
---------------------
 hello world
(1 row)

R 関数名†

トリガー手続き†

PL/R ではトリガー手続きを書くことができる。PostgreSQL ではトリガーは引数がなく、trigger 型を返す関数で宣言する必要がある。

• pg.tg.name
  CREATE TRIGGER ステートメントからのCREATE TRIGGER名

• pg.tg.relid
  トリガー手続きを実行したテーブルのオブジェクトID

• pg.tg.relname
  トリガー手続きを実行したテーブル名

• pg.tg.when
  トリガー呼び出し型に依存する文字列 BEFORE または AFTER

• pg.tg.level
  トリガー呼び出し型に依存する文字列 ROW または STATEMENT

• pg.tg.op
  トリガー呼び出し型に依存する文字列 INSERT、UPDATE、または DELETE

• pg.tg.new
  

• pg.tg.old
  

• pg.tg.args
  CREATE TRIGGER で作成された手続きの引数ベクトル

• 例

CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS '

   if (pg.tg.op == "INSERT")
   {
     retval <- pg.tg.new
     retval[pg.tg.args[1]] <- 0
   }
   if (pg.tg.op == "UPDATE")
   {
     retval <- pg.tg.new
     retval[pg.tg.args[1]] <- pg.tg.old[pg.tg.args[1]] + 1
   }
   if (pg.tg.op == "DELETE")
     retval <- pg.tg.old

   return(retval)
' LANGUAGE plr;

CREATE TABLE mytab (num integer, description text, modcnt integer);

CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
   FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');

動作が確認されている環境†

Red Hat 7.3, 8.0, & 9.
Mingw 上では？

利用例†

テーブル内のデータをグラフ表示†

ボロノイ図作成†

　http://www.varlena.com/varlena/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html

RGDAL の利用†

PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL

TSPostgreSQL†

Time Series Database Interface extensions for PostgreSQL

リンク†

• RとPostGIS
  • PostgreSQL-embedded Statistical Analysis with PL/R
  • Using PL/R a language and environment for statistics in "Language Architecture in PostgreSQL"
  • PostgreSQL-embedded Statistical Analysis with PL/R スライドあり
  • Using R with SQL Spatial Databases Course spgwr との利用例
• PostgreSQLとPL/R - fousの日記
• グループウェアのログデータを分析対象とした、 PL/Rを用いたデータマイニングWebアプリの実現(1)
• 自分のための「PL/Rを使ってる」（１）

参考書†

• PostGIS in Action PLR

最新の20件