原來使用 Postgresql 就可以直接查詢文字或 CSV 檔案

有些時候，我們會需要處理資料內容龐大的文字檔案，我們可能想要先略微檢視資料內容，了解資料分佈，或做些過濾查詢的動作，當然把資料先匯入 Database 再進行處理是一個可行的方案，但如果資料量龐大到數十 GB，那麼光是匯入資料的時間，以及資料匯入 DB 後產生的資料澎漲就足以令人怯步。

要想維持文字或 CSV 檔案的簡便性，又想擁有資料庫查詢的方便性，可以透過 Postgresql Foreign Table for File 的功能來達成。簡單來說，Postgresql 的 Foreign Data Wrappers 能讓您包裝其他資料來源，然後透過一個集中的資料庫來操作，而這也就是 Federated Database 的觀念。

當我們在 Federated Database 下達 SQL 指令時，中央控管的 SQL 會透過 ANSI SQL 2003 的標準 SQL/MED (Management of External Data)，將 SQL 指令進行拆解，交付各個外部資料來源 (或資料庫) 來執行。最後再由中央控管的資料庫，將資料匯集起來傳給使用者。詳情可參考底下兩個連結：

• Foreign data wrappers - PostgreSQL wiki
• Postgresql Federation

Postgresql 的 Foreign Data Wrappers 實際上是 Postgresql 資料庫的 Extension，其中 file_fdw 在 Postgresql 9.1 之後的版本已內建，可讓我們把外部的文字或 CSV 檔案，變成是 Postgresql 的 FOREIGN TABLE。筆者進行測試的版本為 Postgresql 9.3 版。

為了示範，請到 http://data.gov.tw/node/8792 下載 "每日各站點進出站人數.csv" 檔案。完成下載後將檔名更改為 stationflow.csv。

接下來以 pgAdmin 執行以下 SQL:

CREATE EXTENSION file_fdw;
CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE stationflow (
yyyymmdd  char(8),
stationcode  integer,
stationname  varchar(5),
input   integer,
output   integer
) SERVER file_fdw_server
OPTIONS (format 'csv', header 'true', filename 'D:/testdata/stationflow.csv', delimiter ',', null '');

這裡 filename 參數用來指向我們剛剛下載、更名的 stationflow.csv，可能需要修正目錄的正確性。

注意，目前 pgAdmin 並不會把 FOREIGN TABLE 顯示在左方的樹狀節點中，因此若要查詢系統中有哪些 FOREIGN TABLE, 可以執行以下指令：

select * from information_schema.tables where table_type = 'FOREIGN TABLE'

如果在此時執行 SQL, 如：

select count(*) from stationflow;

那應該會收到 Postgresql 提示的 UTF8 編碼錯誤的訊息。這是因為檔案其實是以 Big5 編碼，我們可以使用以下指令修正 FOREIGN TABLE 的檔案編碼：

ALTER FOREIGN TABLE stationflow OPTIONS ( encoding 'Big5' ); 

完成之後，您便可以正常執行 SQL 操作：

select count(*) from stationflow;
select * from stationflow where stationname like '台東%'; 

如果你有內部資料表，甚至可以來個內外 Join, Union 或 Sub-Query 等等：

select * from stationflow where stationcode in (select id from stationlist ); 

測試完成後，可用以下指令刪除外部資料表：

drop FOREIGN table stationflow;

這篇文章僅展示了 Postgresql 查詢文字或 CSV 檔案的功能，事實上 Postgresql 可以外接的資料來源還很多，包括 SQL Database、NoSQL Database、Http、Twitter、Hadoop 等，有興趣的同學可以自行至 PostgreSQL Extension Network 下載編譯測試。