自分のための「PythonでDBの自動構築」

とりあえず、Python 経由でPostgreSQL + PostGIS 2.0の環境を構築するスクリプト。
ついでに、UDFを内部で定義できるように、PL/Python の設定も行なっている。
結論から書くと、必要なコードは以下の通り。

何に必要かというと…秘密…では無いのだが、説明が面倒。興味ある人は、個別に。
要するに、私の研究の都合上、必要なので、メモも兼ねて書いておくにする。
役に立ちそうなことと言えば、subprocess.call の使い方くらい。何でも有りの裏技。

#!/usr/bin/python
# -*- coding: utf-8

import subprocess

# Create a new database
dbname = "sampledb"
subprocess.call("createdb %s -T template_postgis" % (dbname), shell=True)
subprocess.call("createlang plpythonu %s" % (dbname), shell=True)

ここでは、PostGIS 2.0 のテンプレートを適用している。
この部分は、事前に設定していないとダメ。その設定方法は以下の通り。
以下はBashによる設定。Ubuntu 12.04では正常に動作。

# Create a new database for the template.
createdb -E UTF8 template_postgis
createlang -d template_postgis plpgsql
# Create the template by using PostGIS sql files.
psql -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis'" -d postgres
# Apply spatial extension to the database.
psql -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql -d template_postgis 
# Insert information of spatial reference system to the database.
psql -f '/usr/share/postgresql/9.1/contrib/postgis-2.0/spatial_ref_sys.sql' -d template_postgis
# Apply PostGIS raster extension to the database.
psql -f '/usr/share/postgresql/9.1/contrib/postgis-2.0/rtpostgis.sql' -d template_postgis

psql -c "GRANT ALL ON geometry_columns TO PUBLIC;" -d template_postgis
psql -c "GRANT ALL ON geography_columns TO PUBLIC;" -d template_postgis
psql -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;" -d template_postgis

折角の機会なので、ついでに、簡単なUDFを書いてみる。
やはり、最初に書くなら「Helloworld」。とにかく、これが動けば第一段階クリア。
あれだけ苦労したのに、見直してみると、僅か数行のこと。最初はそんなもの。

さて、今回は、ちょっとインチキして、pgAdminIII のインタフェースからログイン。
そんでもって、SQLエディタで書いてみる。書き方は、それほど難しくなさそう。
以下は、単に、「Hello World」という文字列を返すだけの関数。

CREATE FUNCTION return_helloworld() RETURNS character AS
$$
return ("Hello World")
$$ LANGUAGE plpythonu;

まず、CREATE FUNCTION の後ろに関数名を書いて、RETURNSの後ろに返り値の型。
「$$」と「$$ LANGUAGE plpythonu」の間にPython コードを書くだけ。
とりあえず、一行だけのコード。この実行結果のスクリーンショットは以下の通り。

ちゃんと、コードは通っている模様。上段に、先程のコードが書いてある。
SELECT return_helloworld(); で定義した関数を実行。
画面下段に実行結果が出力されている。

なお、CREATE FUNCTION関数は、自動的には上書きしてくれないので、
同じ関数名のUDFを作ろうとすると、以下のようなエラーが帰ってきてしまう。
これは少々面倒…かと思ったら、実は、大したことは無かった。

ERROR:  function "return_helloworld" already exists with same argument types

********** Error **********

ERROR: function "return_helloworld" already exists with same argument types
SQL state: 42723

作成した関数を修正したい場合には、以下のように、一度、関数を削除し、
もう一度、作り直せば良いらしい。次の例では、既存の関数を削除した後、
先程のコードを修正し、変数「value」に値を入れてから結果を返すようにしている。

DROP FUNCTION return_helloworld();

CREATE FUNCTION return_helloworld() RETURNS character AS
$$
value = "Hello World 2"
return (value)
$$ LANGUAGE plpythonu;

大体、状況は飲み込めてきた。では、作成したUDFはどこに保存されるのか？
pgAdminIIIのインタフェースで見てみると、以下のようになっている。

Public スキーマの中の「Function」という所に自動的に保存されているらしい。
先程、自分で定義した「return_helloworld()」が無事に定義されている。
ログインユーザに色々と不備があるが…まぁ、今は関係ないから良いことにする。

さてさて、最初の段階はどうやらクリアできたらしい。
PL/Python、 今日、初めて試した。まだよくわかっていない。
もう少し、試してみて、理解が進んだらこの記事も書き直そう…。

自分のための「PL/Rを使ってる」（１）

今日の話は、データベースを扱ったことのある人向け。
スプレッドシートとデータベースの区別が付かない人や、
データベースとDBMSの区別が出来ない人には無理。

自分で言うの何であるが、今回の話、「親切さ」に欠ける。

以前、私が作っている別の技術系のサイトで、海外の方から色々と問い合わせがあった。
問い合わせ内容は、GRASS GIS 環境設定に関するものであったが、
この人物、面白い人で「PL/R 」と「 PL/Python 」というのを使っているとか。

それから、半月ほど、この人物とメールで技術交換をしたのだが、
結果として、むしろ、私の方が色々と刺激を受けたのである。海外の人は実に面白い。
面倒で面白くもない仕事に追われるあまり、時間を割けなかったが、ようやくやる気に。

まぁ、愚痴を言っていても仕方無い。

要するに、これらの機能を使うと、PostgreSQLというDBMS内に、関数が定義できる。
一般的に、ユーザ定義関数（UDF：User-Defined Function）と言われるものであろう。
名前は知っていたのであるが、煩雑そうであったので、深くは入りこまなかった。

ところが、最近、このUDFとやらに手を付ける必要が出てきた。っで、やる気に。
本来は、C言語で書くのであるが、RやPythonのコードが使えれば、
これまでの資源が無駄にならない...かもしれないし、無駄になるかもしれない。

やってみないと解らない。何せ、この記事を書きながら実験をしているので、
本当に、想像通りに扱えるのか、扱えないのかさえ、今日の記事を書き終えるまで不明。
最後の一行に「ダメだった」っと書く可能性もあるのだが...まぁ、とにかく始める。

ということで、少しずつ、これらの技術を学んでみる。まずは、初期設定から。
完全に素人なので、エレガンスに欠けるが...まぁ、良いことにする。
もう少し、全体像を理解できるようになれば、書きなおそう。

オープンソース系の技術を使うのであれば、やはり、Linuxが使いやすい。
この辺りの話は、「文系のための情報管理」の話で書いておいた。
ということで、Mac と Windows の動作確認はしない。各自、自力で。

Ubuntu の場合、手を抜いてSynaptic 経由でPL/R をインストールすると、
PL/R を有効化するためのSQLファイルは以下の場所に置かれる。
インストールの方法によっては、contrib の下に置かれる。

'/usr/share/postgresql/9.1/extension/plr.sql'

PL/Rを有効化するためには、このSQLファイルを実行する必要がある。
まずは、実験用のサンプルデータベースを作ってみる。
PostgreSQL にログインする。細かい話は省略。いずれ別のところで。

psql -U postgres -h localhost -W

ログインしたら、とにかく、以下をコピペ。
このサンプルデータは、某国某地域の古墳の前方幅。

CREATE DATABASE dbname;
CREATE TABLE sample(col float);
INSERT INTO sample values(27);
INSERT INTO sample values(19.6);
INSERT INTO sample values(18);
INSERT INTO sample values(15);
INSERT INTO sample values(19);
INSERT INTO sample values(47);
INSERT INTO sample values(24);
INSERT INTO sample values(22);
INSERT INTO sample values(27);
INSERT INTO sample values(14);
INSERT INTO sample values(20);
INSERT INTO sample values(36);
INSERT INTO sample values(23);
INSERT INTO sample values(20);
INSERT INTO sample values(12);
INSERT INTO sample values(24);
INSERT INTO sample values(46);
INSERT INTO sample values(38);
INSERT INTO sample values(27);
INSERT INTO sample values(21);
INSERT INTO sample values(30);
INSERT INTO sample values(24);
INSERT INTO sample values(19.3);
INSERT INTO sample values(25.7);
COMMIT();
\q

データベースが無事に出来上がったら、次に、PL/R の有効化を行う。

Terminal から実行するならば、以下のようになる。
要するに、既存のデータベースを構築してから有効化を行うのがポイント。

psql -U postgres -h localhost -W dbname < "/usr/share/postgresql/9.1/extension/plr.sql"

再び、構築したデータベースにログインし、PL/Rの動作確認。
とりあえず、標準偏差でも計算してみる。ここからが重要。

psql -U postgres -h localhost -W dbname

ログインしたら、次は、関数の定義を行う。ここでは、関数名を「r_sd」としておく。
関数の中身は、AS'〜' LANGUAGE の間に書く。この部分は、Rスクリプトそのもの。
中身のコードは...言わなくても大丈夫であろう。単に、標準偏差を求めているだけ。

CREATE OR REPLACE FUNCTION r_sd(_float8) RETURNS float AS'
  res <- sd(arg1)
  return(res)
' LANGUAGE 'plr';

次に、定義した関数の動作を確認する。定義した関数は、SELECT文で呼び出す。
何か、奇妙な感じがするであるが...まぁ、ここは「えいや！」で...。
なお、col列の値を配列で返すためにARRAY() 関数でデータを整形しておく。

SELECT r_sd(ARRAY(SELECT col FROM sample));

以下が、このスクリプトの実行結果。

dbname=# SELECT r_sd(ARRAY(SELECT col FROM sample));
       r_sd      
------------------
 9.01944357035624
(1 row)

よし、上手くいった。どうやら、引数はデータ型のみを指定し、

スクリプト内の変数は、引数順に arg1, arg2, ..., argn となるらしい。
色々と解らないことも多いが、とりあえず、この方法で何とかなる。

さてさて、私に残された時間は有限では無い。
動くことが判ったから、今度は、実際の研究で応用してみよう。