前処理にPostgreSQLを使う:環境情報の切り出し


こんにちは。2014年の初投稿となりますが、あまり新年とは関係ありません。

データ分析とその前処理について、個人の環境を作るだけなら問題になりませんが、チームで共有したい場合に色々と考えることが出て来ます。一番面倒なのは、環境を共有する必要があるが、環境そのものを共用することはできない(会社が違う)、しかも全く同じ環境をコピーする訳にはいかないという状況です。
例として、PostgreSQLのCOPY文を環境(ファイルのパス)非依存にする方法について書こうと思います。
dynamiccopy

対処法1:PL/pgSQLを使う

過去にknimeで環境変数を切り出す方法を書いたことがあります。実はあのケースでは前処理にPostgreSQLを用いていました。元データとなるCSVファイルをcopy文で取り込み、色々整形していたのですが、PostgreSQLのcopy文は「サーバ側に置いたファイルを絶対パスで指定する」という動作なので、PostgreSQLサーバの動作環境が異なるとパスを修正する必要がありました。多くのファイルを取り込まなければならない場合、これは大変だなぁということで、当時はPL/pgSQLで関数を定義するという方法を採りました。

-- 定義
CREATE FUNCTION import(path VARCHAR) RETURNS VOID AS $_$
  DECLARE
  BEGIN
    CREATE TABLE table1 (
      ...
    );
    EXECUTE E'COPY table1 FROM '''||path||'file1.csv'''; 
    CREATE TABLE table2 (
      ...
    );
    EXECUTE E'copy table2 FROM '''||path||'file2.csv''';
    ...
  END;
$_$ LANGUAGE PLPGSQL;

-- 実行(どっちかをアンコメント)
-- SELECT import(E'C:\\path_to_project\\data\\'); -- windows環境
SELECT import('/home/nishio/path_to_project/data/'); -- linux環境

-- 実行(もっと手の込んだやり方)
CREATE TABLE config (
  path VARCHAR
);
INSERT INTO config(path) VALUES ('/home/nishio/path_to_project/data/');
SELECT import(path) FROM config LIMIT 1;

これはこれで良かったのですが、関数定義と実行の2つに分けてスクリプトを書いたり、動かしたりする必要があって、試行錯誤には向いていないと感じました。

対処法2:pgScriptを使う

ところで、SQLの実行にはpgAdminを使っています。実はpgAdminにはSQLを実行するだけでなく、pgScriptと呼ばれるスクリプトを実行する機能というのがあります。今までこの機能には注目していなかったのですが、調べてみると案外簡単に書けることが分かりました。

-- 準備
CREATE TABLE config (
  path VARCHAR
);
INSERT INTO config(path) VALUES ('/home/nishio/path_to_project/data/');

-- 実行
SET @config = SELECT * FROM config LIMIT 1;
SET @path = @config[0]['path']; -- 添え字の0は一行目、'dir'はカラム名(case insensitive)に対応

CREATE TABLE table1 (
  ...
);
SET @file = @path + 'file1.csv';
COPY table1 FROM '@file' WITH CSV HEADER; -- '@file'のクォーテーションに注意

CREATE TABLE table2 (
  ...
);
SET @file = @path + 'file2.csv';
COPY table2 FROM '@file' WITH CSV HEADER;
...

こんな感じです。

このようなことをしないといけない状況というのはあまり無いかも知れませんが、定数の繰り返しを避けられるのはプログラマとしてはうれしいところです。ちなみに言語リファレンスには、ループしながら動的にテーブルを作るといったサンプルもあります。気軽に動的な処理が書けるので今後も色々試したいと思います。

以上、少々軽いネタでした。本年もよろしくお願いいたします。


This entry was posted in データマイニング, 技術. Bookmark the permalink.