8000万レコードをInsertする

概要

・とある物件で8000万弱のレコードを新規のDBに効率的にぶち込む方法を考える必要に
・MySQL(Amazon RDS) + Python(mysql.connector)を想定
・mysql.connector + pythonについては拙文ながら(http://nekopuni.holy.jp/?p=927)に書いております。
・コミットの位置には気をつけよう + Multiple Insert最強ねというお話。

方法その1

まずは最初にやった方法。1レコードごとにInsertしてコミットしていく方法。
今までDB関連でやったコードはレコード量も大したことなかったので以下の方法でも特に問題なかった。

具体的な環境としてはテキストデータ(csv)を読み込み、それをDBにInsertしていくというもの。
csvの中身は日付(DATE)とデータ値(VALUE)がカンマ区切りになっているものであり、エッセンスだけ抜き出すと以下の様なコードを書いていました。

1000レコード弱を処理するのに33秒ほど。8000万行だと30日かかる計算に( д) ゚ ゚
これはイカンザキ

方法その2

コミットするのに時間がかかるということを風のうわさで聞いたので、コミットに時間がかかるのなら最後にコミットすれば良いじゃないというマリーアントワネット作戦

1000レコード弱を処理するのに8秒ほど。たっだ1行のインデントを変えただけで25%ほどの速度で処理できるように。
ただこれでも8000万レコードだと7.5日かかる計算。。

方法その3

どうやら世の中にはMultiple Insertというのがあるらしい。これで1000レコードごとに束ねてInsertすればさらに速度が向上するのでは
以下の例では2レコードのMultiple Insertですが、実際には1000レコード続けて書くようにして実施。

で、出てきたエラー(!)がこちら

なんでやねん!と思ったらAmazon RDSで借りられるデフォルトのDBの容量5GBでは足りていない模様。
ということで急遽100GBまで増額して再トライ。
1000レコードあたりの速度が0.18秒まで高速化!方法その2の3%ほどの速度で処理できるように。

ということで最終的に8000万レコードをぶっ込んだ際にかかった時間が以下

当初30日かかるという無謀な計画が、無事2時間で終えることができました。
最初どんだけ効率悪いコード書いてたんだって話ですねorzorz
Multiple Insert最強ね。

参考

インサート(insert)の処理方式別のパフォーマンスを検証 : 株式会社インターオフィス
http://www.inter-office.co.jp/contents/194/

mysql.connectorが使えるようになるまで

概要

・地味にハマったのでPythonからMySQLサーバー(Amazon RDS)にアクセスするまでの俺様メモ
・実際にpythonからselect文とinsert文を実行する
・Windows版Python 2.7.6を想定(macはもっと簡単でしたorz)

PythonのWindows版をインストール

Releases
http://www.python.org/download/releases/

適当にこの辺りからインストール

コマンドプロンプトでPythonが使用できるようにする

このままではコマンドプロンプトでpythonを使用することが出来ないので環境変数をいじくる

3. Using Python on Windows — Python v2.7.6 documentation
http://docs.python.org/2/using/windows.html

環境変数PATHに
C:\Python27
といったパスを追加.

Mysql.connectorのインストール

PythonからSQLを叩くライブラリは色々あるようですが無難にmysql.connectorを選択
ライブラリ自体は下記からダウンロード

2. mysql-connector-python 1.0.12 : Python Package Index
https://pypi.python.org/pypi/mysql-connector-python/

ダウンロードされたzipを解凍し,下記サイトに書かれているように,コマンドプロンプトからpython setup.py installでインストール

3.MySQL :: MySQL Connector/Python :: 4.3 Installing Connector/Python Source Distribution on Microsoft Windows
http://dev.mysql.com/doc/connector-python/en/connector-python-installation-source-windows.html

select文の実行

これで一応使える準備は整ったはずなので以下ようなのコードでテスト
サーバー情報,テーブル情報は架空のものとしています.
例えば,DATEとVALUEというカラムが存在するテーブル(TABLE)を読みに行くとしたらこんな感じになるかと思います.

以下のサイトも参考になると思います.

MySQL :: MySQL Connector/Python :: 5.4 Querying Data Using Connector/Python
http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

insert文の実行

データベースに新たなレコードを追加するにはinsert文を実行するかと思いますが,その際には
コミットのための1行が追加となります.

ご参考には以下のページ

MySQL :: MySQL 5.5 Reference Manual :: 22.6.4.3 Inserting Data Using Connector/Python
http://dev.mysql.com/doc/refman/5.5/en/connector-python-example-cursor-transaction.html

ということでハマりましたというお話でした.

セントルイス連銀のデータをpython経由で取得する

概要

・セントルイス連銀が提供しているFRED Economic Dataはデータが豊富なので欲しい
・PythonでFRED APIを叩いてデータを取得する

ユーザー登録

まずはユーザー登録を行い,apikeyを取得する必要がある様子

Register User Account – St. Louis Fed
http://research.stlouisfed.org/useraccount/register/step1

データ取得に関するドキュメント
St. Louis Fed Web Services: fred/series/observations
http://api.stlouisfed.org/docs/fred/series_observations.html

データ取得方法

APIが公開されているので例えば
http://api.stlouisfed.org/fred/series/observations?series_id=PAYEMS&api_key=[YOUR_APIKEY]&file_type=json&realtime_start=2000-01-01

といったURLにアクセスすればデータを取得することが出来ます.
(ユーザー登録後に取得したapikeyを[YOUR_APIKEY]のところに指定する必要あり)
ちなみに上記URLではPAYEMSという系列(アメリカの非農業部門雇用者数)を取得しています.
詳しくは上述したドキュメントを参照ということで(― ―)

ファイル・タイプとしては,
・XML
・JSON
・TXT
・CSV
といった方式が対応しているようです.今回はJSONを使用.

Pythonコード

まずはURLクローラということで,getPageText関数をパクじゃなくて参考にして作成します.
変数responseは通常str型で返ってきますので,ここではJSON形式に変換して返すようにしています.

これを実行すると

といった具合に値が返ってきます.
なお,これは非農業部門雇用者数となっておりますので,通常着目される非農業部門雇用者数の増加数自体は
136765と136562の差分をとって203となります.単位が1000人となっておりますので,
10月~11月に非農業部門雇用者数は203,000人増加したということですね.
この値は米国労働省のHPからも確認できます.

U.S. Bureau of Labor Statistics
http://www.bls.gov/

131221

サイトの右下のPayroll Employmentに +203,000とありそれっぽい値が取れてそうということが分かります.

参考

プログラマはWebページをデータとして扱う — ExSoft
http://www.exsoft.net/blog/entry/v8eik8