R と PostgreSQL
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
]
開始行:
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)
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)
***大域データ [#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
終了行:
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)
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)
***大域データ [#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
ページ名: