COLOR(red){SIZE(30){R と PostgreSQL}} #contents *[[PL/R:http://www.joeconway.com/plr/]] [#j58cc7f6] PostgreSQL 用 R 手続き型言語(R Procedural Language for PostgreSQL) **PL/R とは [#wa010f01] PostgreSQL の SQL よりRの機能を呼び出す。 **インストール(Linux) [#b90332f5] -PL/R のソースをPostgreSQL の contrib で解凍 -make を実行してビルド -"make install" でライブラリをインストール -contrib/plr ディレクトリにある plr.sql で、任意のデータベース内で有効にする -- psql -d mydatabase -f plr.sql **インストール(Win32) [#s0768a42] -[[Win32 インストーラ、ソース:http://joeconway.com/plr/]] -[[Win32実行形式 旧版:http://www.davidgis.fr/download/plr-8.2.0.4_0_win32.exe]] -[[チュートリアル(英文):http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01]] [[ボロノイ図の生成例:http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02]] **利用方法 [#z9e41d48] ***関数の基本形 [#i1c44d2b] 以下のように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) return(arg1) else RETURN(arg2) 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) 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) 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) ***大域データ [#f06f12ca] ***データベースアクセスサポート関数 [#td53291f] 以下のコマンドを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 サポート関数 [#j8200a03] -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 が現在動作している環境の表示 ***集約関数 [#v8bb8e5c] ***起動時にRモジュールを読み込む [#rb2f7517] 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 関数名 [#l40d4bf0] ***トリガー手続き [#v08e6470] 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'); **動作が確認されている環境 [#j31e266f] Red Hat 7.3, 8.0, & 9. ~ [[Mingw 上:http://momjian.postgresql.org/main/writings/pgsql/win32.html]]では? **利用例 [#q0c176a9] *** テーブル内のデータをグラフ表示 [#mf409590] *** ボロノイ図作成 [#q8669433] http://www.varlena.com/varlena/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html *** RGDAL の利用 [#zf5b442c] [[PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL:http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut03]] *[[TSPostgreSQL:http://www.google.co.jp/url?sa=t&source=web&ct=res&cd=3&ved=0CDEQFjAC&url=http%3A%2F%2Fcran.r-project.org%2Fweb%2Fpackages%2FTSPostgreSQL%2Findex.html&ei=YisGTJS0H8qGkAXJyPTtCg&usg=AFQjCNFLuwxryZBgRPFiM17BWa_cltlrRQ&sig2=g28MLZ8nJrE8HLtKEFKqeA]] [#lcfde618] Time Series Database Interface extensions for PostgreSQL *リンク [#m0d3bf90] -[[RとPostGIS]] --[[PostgreSQL-embedded Statistical Analysis with PL/R:http://www.joeconway.com/presentations/pgday-lightning-2009.07.19.r00.pdf]] //-[[PostgreSQL-embedded Statistical Analysis with PL/R:http://joeconway.com/oscon-pres-2003-1.pdf]] --[[Using PL/R a language and environment for statistics in "Language Architecture in PostgreSQL":http://www.postgresonline.com/journal/index.php?/categories/13-PLR]] --[[PostgreSQL-embedded Statistical Analysis with PL/R:http://conferences.oreillynet.com/cs/os2003/view/e_sess/4533]] [[スライド:http://conferences.oreillynet.com/presentations/os2003/conway_joe.pdf]]あり --[[Using R with SQL Spatial Databases Course:http://www.comp.dit.ie/pbrowne/Spatial%20Databases%20SDEV4005/lab9.doc]] spgwr との利用例 -[[PostgreSQLとPL/R - fousの日記:http://www.google.co.jp/url?sa=t&source=web&cd=2&ved=0CB8QFjAB&url=http%3A%2F%2Fd.hatena.ne.jp%2Ffous%2F20090828%2F1251387016&ei=wM8kTp75GonRmAXPuuHsCQ&usg=AFQjCNEdKy7hPcL3AqzFJ1eM1eKKmYNpOQ]] -[[グループウェアのログデータを分析対象とした、 PL/Rを用いたデータマイニングWebアプリの実現(1):http://developer.cybozu.co.jp/tech/2009/06/plrweb1-8cb7.html]] -[[自分のための「PL/Rを使ってる」(1) :http://cis-jp.blogspot.jp/2012/10/plr.html]] *参考書 [#l2b08056] -[[PostGIS in Action:http://www.manning.com/obe/]] PLR