Clever Geek Handbook

Python / Postgresqlで大量のデータを読み取る

検討中のテクノロジーのスタック ：Postgresql 9.3、インストールされたモジュール「psycopg2」を含むPython 2.7。



問題



大きなテーブル（1,000万件を超えるレコード）を処理するタスクを実際にどのくらい頻繁に処理しなければなりませんでしたか？ このタスクは、処理時間と関連するシステムリソースの両方の点で非常にリソース集約的であることに同意すると思います。 今日は、問題を解決する別の方法を示してみます。



提案：



Postgresql DBMSには、大量の情報、つまり「コピー」を扱うための優れた演算子があります。 この演算子を適用すると、テーブル内の膨大な量の情報を読み書きできます。 この記事では、読み取りモードについて検討します。



「COPY」演算子のドキュメントによると、「csv」を含むさまざまな形式だけでなく、ファイルまたはSTDOUTストリームへの読み取りのいくつかのモードが利用可能です。 最大限の利益を得るためにそれを使用しようとします。



準備：



「モルモット」として、100万件のレコードを持つテーブルを作成し、メソッドの本質を反映した小さなスクリプトを作成します。 sqlファイルはgitリポジトリにあります（リンクは記事の最後にあります）。



また、psycopg2拡張機能をインストールすることを忘れないでください！



実装：



素晴らしいcopy_expert関数を使用してデータを取得します。これにより、PythonクライアントからCOPYリクエストを実行できます。 



query = """ SELECT * from big_data inner join big_data as t1 USING(fname) """ output = StringIO() self.cursor.copy_expert("COPY (%s) TO STDOUT (FORMAT 'csv', HEADER true)" % query, output) data = output.getvalue() output.close() result = list() for item in getResults(data): # do whatever we need item = {k: None if v == "" else v for k, v in item.items()} result.append(item)


コードの説明：



  1. リクエストでは、それを複雑にするために自分自身で結合を行います（速度の利点はリクエストの複雑さに直接比例することに注意してください）。
  2. バッファーとして、StringIOオブジェクトを使用します。ここでは、カーソルからデータを書き込みます。
  3. getResultsジェネレーターで文字列を解析します。
  4. 解釈を簡単にするために、すべての空の行を「なし」と入力するように変換します。 「COPY」を使用した後、文字列値を取得します。
  5. ヘッダーの先頭行で「csv」形式を使用することに注意したいので、少し後で理解します。


ジェネレーターコード： 



 def getResults(stream): """ get result generator """ f = StringIO(stream) result = csv.DictReader(f, restkey=None) for item in result: yield item f.close()


説明：



  1. リストからわかるように、ここでも使い慣れたStringIOバッファーを使用します。
  2. 文字列「csv」を辞書に変換するには、csvネイティブライブラリの「DictReader」メソッドを使用します。 デフォルトでは、このメソッドは最初の行を辞書フィールドのリストとして受け取ります。


実際、必要なのはそれだけです！



私の構成 ：MacBook Air 2013プロセッサー：1.3 GHz Intel Core i5、Ram：4 GB 1600 MHz DDR3、SSD。



PS：

アクセラレーションを読み取るためのこのアプローチが常に機能するとは限らないことに注意してください。つまり、3〜5フィールドのかなり単純なテーブルがある場合、顕著な違い（少なくとも100万まで）に気付かないでしょう。 ただし、この方法は、複雑なクエリを使用すると速度が非常に速くなるだけで、加速は最大10〜20倍に達します。 また、スクリプトが実行されるアイロンの設定は非常に強く影響を受けます。



すべてのコードは、gitリポジトリhttps://github.com/drizgolovicha/python_bulk_readにあります 。



最適化のためのコメントと提案を歓迎します！



最後まで読んでくれてありがとう。



UPD ：

レコードの測定結果サンプル（14k）：

  1. ダイレクトSELECT、非インデックスフィールドのWhere条件-21.4s
  2. 前のリクエストのコピー-13.1s
  3. 同じSELECTの選択ですが、フィールドごとのインデックスを持つマテリアライズドビューから-12.6秒
  4. マテリアライズドビューのコピー-1.8秒


More articles:


All Articles