file_fdw
FORCIA, Inc. 板垣 貴裕
PostgreSQL 9.1 では、データベース外部のデータをテーブルとして扱うことができる、外部テーブル (FOREIGN TABLE) がサポートされました。 file_fdw モジュールは、タブ区切りや CSV 形式のファイルをテーブルとして読み込むためのモジュールです。 データを取り込むだけならば、以前から COPY FROM コマンドがありましたが、いったんテーブルに格納する手間が省けるため、気軽に外部ファイル連携できます。
他のデータベースの機能に対応させると、MySQL の CSV ストレージエンジンや、Oracle Database の外部表と類似の機能といえます。
基本的な使い方
file_fdw でシフトJISエンコーディングのタブ区切りのファイルを読み込むコマンド例を以下に示します。 モジュールのインストールに CREATE EXTENSION コマンドが使えるようになったこともあり、手順は簡単です。
-- 最初に1回だけ
=# CREATE EXTENSION file_fdw;
=# CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;
-- ファイルごと
=# CREATE FOREIGN TABLE external_tsv
(id char(3), i integer, name text)
SERVER fs
OPTIONS (filename '/path/to/file.tsv', encoding 'sjis');
=# SELECT * FROM external_tsv;
id | i | name
-----+-----+-------------
A01 | 123 | CSV
B02 | 456 |
C03 | -10 | SJISの文字列
(3 行)
テーブルにデータをロードすることなく、直接ファイルを参照できていることがわかります。 また、これも 9.1 の新機能ですが、file_fdw や COPY コマンドには encoding オプションが指定できるようになったため、データベースとは異なるエンコーディングのファイルも読み込めます。
注意点
便利な file_fdw ですが、注意が必要な点もあります。
- 大きなファイルや繰り返し読む場合の性能
- 内部的にはタブ区切りやCSV区切りのファイルを毎回読み込んでいます。 インデックスは張れませんし、
WHERE句を指定した場合でも単に全行読み取る途中でフィルタリングしているだけです。 CSV ファイルの一部のみに繰り返しアクセスする場合には、いったんCOPY FROMで取り込み、インデックスを張ったほうが良いかもしれません。 - ファイルはデータベース・サーバの起動ユーザが読み取る
- ファイルはデータベース・サーバのプロセスが読み取るため、サーバ側のファイルシステム上の、そのユーザがアクセスできる場所にファイルを置く必要があります。 一般的には postgres ユーザになることが多いでしょうか。 また、file_fdw の filename オプションは絶対パスで指定してください。
さらに具体的な例を挙げながら、file_fdw を使い方を見ていきます。
例: 不正なデータが混入したファイルを取り込む
COPY FROM コマンドで外部データを取り込む際、ファイル内のテキストは取り込み先テーブルの列のデータ型にあわせて変換されます。 この際、ファイル内のテキストの書式が PostgreSQL が解釈できる形でないと、エラーになってしまいます。 エラーを回避するために、とりあえずいったんテキスト (text型) として取り込んでおいて、その後 実際の列の型に自分で変換することも必要でした。 これでは、テーブルを2回作成することになり、特にデータ量が多くなるともったいなく感じます。
その他にも、PostgreSQL の特性を考えると、入力ファイルに無い値を最初は NULL として取り込み、後から値を計算して UPDATE で設定することは避けたい処理パターンです。 (この処理であれば、TRIGGER を使うか、新しいテーブルに CREATE TABLE AS SELECT で値を設定しながら移し変えたほうが良いでしょう。)
こういった、入力ファイルを加工しながら取り込む場合には、file_fdw を使うと 変換しながら取り込めるため、無駄がありません。
CSV ファイル中の空文字を NULL として取り込む
=# CREATE TABLE tbl (id integer, v integer); =# COPY tbl FROM '/path/to/baddata.csv' CSV; ERROR: invalid input syntax for integer: "" CONTEXT: COPY tbl, line 3, column v: ""
この CSV ファイルは、NULL を "" という文字列で格納しているようです。 PostgreSQL は NULL として ,, を、空文字として ,"", を期待するので、このままではエラーになってしまいます。 さらに、このケースでは COPY コマンドの NULL オプションでも対応できません。 そこで、いったん text 型を経由して空文字を NULL に変換しながら取り込んでみます。
=# CREATE FOREIGN TABLE baddata (id integer, tmp_v text)
SERVER fs
OPTIONS (filename '/path/to/baddata.csv', format 'csv');
=# INSERT INTO tbl
SELECT id, nullif(tmp_v, '')::integer AS v FROM baddata;
INSERT 0 10
この方式ならば成功しました！ また、もし tbl がまだ空であれば、CREATE + INSERT の代わりに CREATE TABLE AS を使うとさらに高速化 (WALスキップ) できます。
対応できないケース
ただし、あくまで「テキストならば問題なく取り込める」必要があるため、以下のようなケースには対応できないことに注意してください。
- × 不正な文字エンコーディング
- × エスケープ不足 (タブ, 改行, バックスラッシュ)
- × 列数の超過
まとめ
file_fdw は、外部テーブルとしてタブ区切りや CSV ファイルを読み込むためのドライバでした。 外部テーブル機能自体は、ドライバ・モジュールさえ追加すれば、その他の外部データも読み込めます。 file_fdw のソースコードはシンプルで読みやすいので、ドライバを開発する際のテンプレートして参考にするのも良いと思います。