2013/09/25
PostgreSQL > COPY … FROM PROGRAM で cURL を使う
PostgreSQL > COPY … FROM PROGRAM で cURL を使う
1. 今日やったこと
2013/09/11（9.3.0 リリース、COPY … PROGRAM を試す）の続編。一昨日、海外のウェブサイト CYBERTEC で PostgreSQL 9.3 の新機能 COPY … PROGRAM を使う例があったので、同じことをやってみた。CYBERTEC での OS は UNIX 系と思われるが（理由は 2. に記述）、私はいつもと同じ Windows XP で試し、同じ結果が得られた。下が CYBERTEC の記事。COPY … FROM PROGRAM から cURL を使い、株式市場データを PostgreSQL のテーブルに直接インポートしている。
CYBERTEC - Importing stock market data into PostgreSQL
http://www.cybertec.at/importing-stock-market-data-into-postgresql/
↓ 同じことを私の環境で実行し、インポート先テーブルを表示した様子。COPY 実行部分は、使い回しできるよう少し変えた。詳細は 3. で述べる。
2. cURL のインストール
cURL は元々 UNIX のコマンドラインツールで、ウェブコンテンツを取得して表示や保存などができ、今では Windows をはじめ様々な OS 用がある。CYBERTEC の記事は To download data from the net you can use a UNIX command line tool called curl. とだけ述べインストールについて触れていないので、cURL が簡単に使える UNIX 系での実行を念頭に置いていると思う。下が cURL の公式サイト。curl and libcurl
http://curl.haxx.se/
以下、私が Windows XP Pro SP3 (32bit) でインストールした際の手順。公式サイトトップページから下記ダウンロードページに行き、膨大な数の OS 用ファイルの中から Windows 32bit 用を選ぶ。下のように Win32 用もたくさんあるが、今回は矢印のファイルを使った。
cURL : Download
http://curl.haxx.se/download.html
↓ ダウンロードしたファイルは ZIP 形式で、適当な場所に解凍すればすぐに使える。
↓ 解凍した cURL のテスト。オプション無しで URL だけ指定すると、単純にアクセスして結果を表示する。これで準備ができた。
3. SQL の作成と実行
まず CYBERTEC の記事と同様にインポート先テーブルを作る。SQL は下のとおり。CREATE TABLE インポート先テーブル名 (
d date
, open numeric
, high numeric
, low numeric
, close numeric
, volume int8
, adj_close numeric);
cURL で取得する株式市場データの URL も、CYBERTEC と同じ Yahoo! Finance への下記リクエストとする。ただしレスポンスが 16,000 行以上と多いので、テスト時は &c=1950 を &c=2013 に変更するなど、レスポンス行数を少なくする方がよい。
http://ichart.finance.yahoo.com/table.csv?s=%5EGSPC&d=8&e=5&f=2013&g=d&a=0&b=3&c=1950&ignore=.csv
次に、COPY … FROM PROGRAM で指定するコマント文字列を作成する。Windows なので URL 中にたくさんある & を全てエスケープしないといけない。エスケープは ^（キャレット）を前置する。結果として次のようになる。
curl http://ichart.finance.yahoo.com/table.csv?s=%5EGSPC^&d=8^&e=5^&f=2013^&g=d^&a=0^&b=3^&c=2013^&ignore=.csv
↓ コマンドプロンプトで上記を実行した結果、うまくいった。ここでは、上に書いたように取得行数を減らしテストしている。
上記コマンドを COPY … FROM PROGRAM に渡す SQL を作成する。ここで後々使い回せるよう、また & のエスケープを一つ一つしなくて済むよう、全体を DO ブロックで囲み URL や cURL のパスを変数に入れる。エスケープは URL 全体の & を キャレット付きに置換する。下が作成した DO ブロック。
DO $D$
DECLARE
url text ;
cmd text ;
BEGIN
url = 'http://ichart.finance.yahoo.com/table.csv'
|| '?s=%5EGSPC&d=8&e=5&f=2013&g=d&a=0&b=3'
|| '&c=1950&ignore=.csv' ;
cmd = 'curl ' || replace(url, '&', '^&') ;
-- curl の解凍先に合わせる
-- curl が postgres から実行できる前提
EXECUTE '
COPY インポート先テーブル名
FROM PROGRAM ''' || cmd || ''' CSV HEADER' ;
END $D$ ;
この DO ブロックを実行し、問題なくインポートできた。下は冒頭の再掲で、インポート先テーブルを表示したところ。
なお CYBERTEC の記事では 16021 行インポートされ、直近データの日付が 9/4 のようだが、今回私が試したら一行多く、直近データの日付が 9/5 だった。
以上、Windows でも PostgreSQL 9.3 の新機能 COPY … FROM PROGRAM から cURL を使ってウェブ上の CSV データを取り込むことができた。cURL のパスと実行権限を一度設定すれば、一回の SQL 実行でウェブ上のデータを直にテーブルに変換できるわけで、いろいろ使い道がありそう。