Pythonモジュールpyodbcでのfetch処理

はじめに

データアナリティクス事業本部のkobayashiです。

前回まで行った環境を使ってPythonモジュールのpyodbcを使ってSQL Serverからデータを取得してRedshiftへ移行する処理を行っていました。その過程で結構なレコード長と件数の入出力を行っていたところ、ふとfetch処理を行う際のメモリ消費量が気になって調べてみましたのでその結果をまとめます。

前回の記事

PythonでODBC接続を行えるpyodbcをインストールする

環境

  • macOS Mojave
  • Python 3.6.5
  • pyodbc 4.0.27
  • memory-profiler 0.57.0

pydobcのfectch処理

pyodbcでのfetch処理はfetchall、fetchmany、fetchone、fetchvalがあります。

  • fetchall
    • クエリのすべての結果レコードを取得する。
  • fetchmany
    • クエリの結果を指定したレコード数づつ順次取得する。
  • fetchone
    • クエリの結果を1レコードづつ順次取得する。
  • fetchval − クエリの結果の最初の1レコードを取得する。

fetchoneに関してはfetchmanyでも代用出来ますのであまり利用する機会は無いと思います。

The Cursor object represents a database cursor, which is typically used to manage the context of a fetch operation. Database cursors map to ODBC HSTMTs. Cursors created from the same connection are not isolated, i.e. any changes done to the database by one cursor are immediately visible by the other cursors. Note, cursors do not manage database transactions, transactions are committed and rolled-back from the connection.

メモリ消費量計測用のモジュール memory-profilerのインストール

下準備としてまずはPythonスクリプトのメモリ消費量を計測するためのPythonモジュールをインストールします。

This is a python module for monitoring memory consumption of a process as well as line-by-line analysis of memory consumption for python programs. It is a pure python module which depends on the psutil module.

pip install memory-profiler

これは、Pythonスクリプトのメモリ消費量をスクリプトの行ごとに計測できるモジュールで、使い方も非常に簡単で計測する関数にデコレータとして@profileを利用すれば良いだけです。

from memory_profiler import profile

@profile
def test_func():
...

メモリ消費量計測

計測用のスクリプト

以下のような非常に簡単なスクリプトで計測します。 単純にデータソースから10万件のレコードを取得し、後続の処理でPandasを使って色々加工する処理を想定しています。

from memory_profiler import profile
import pyodbc
import pandas as pd
import numpy as np

connection = "DRIVER=Redshift;UID=test_user;PWD=test_pass;DATABASE=public;SERVER={hostname};"

@profile
def main():
    con = pyodbc.connect(connection)

    cur = con.cursor()

    query = "select * from test.test_table limit 100000;"
    cur.execute(query)

    rows = cur.fetchall()
    df = pd.DataFrame(np.array(rows))
    print(len(df))
    cur.close()


@profile
def main2():
    con = pyodbc.connect(connection)

    cur = con.cursor()

    query = "select * from test.test_table limit 100000;"
    cur.execute(query)

    row_cnt = 1000
    rows = cur.fetchmany(row_cnt)
    while len(rows) > 0:
        df = pd.DataFrame(np.array(rows))
        print(len(df))
        # next
        rows = cur.fetchmany(row_cnt)

    cur.close()


if __name__ == "__main__":
    main()
    main2()

では実際にこのスクリプトを実行してみます。

fetchallを使って一括でデータを取得した場合

Line #    Mem usage    Increment   Line Contents
================================================
    19     79.2 MiB     79.2 MiB   @profile
    20                             def main():
    21     86.5 MiB      7.3 MiB       con = pyodbc.connect(connection)
    22                             
    23     86.5 MiB      0.0 MiB       cur = con.cursor()
    24                             
    25     86.5 MiB      0.0 MiB       query = "select * from test.test_table limit 100000;"
    26    185.5 MiB     99.0 MiB       cur.execute(query)
    27                             
    28    399.5 MiB    214.0 MiB       rows = cur.fetchall()
    29    550.3 MiB    150.8 MiB       df = pd.DataFrame(np.array(rows))
    30    550.3 MiB      0.0 MiB       print(len(df))
    31    550.3 MiB      0.0 MiB       cur.close()

上記の通り、excecuteを行うまでのメモリ消費量はそれほどでもありませんが、fetchallを行った際にメモリ消費量が大幅に増えています。またPandasデータフレームに変換した際に更に消費量が大幅に増えています。

fetchmanyを使ってレコード数を区切ってデータを取得した場合

Line #    Mem usage    Increment   Line Contents
================================================
    34     79.2 MiB     79.2 MiB   @profile
    35                             def main2():
    36     86.5 MiB      7.3 MiB       con = pyodbc.connect(connection)
    37                             
    38     86.5 MiB      0.0 MiB       cur = con.cursor()
    39                             
    40     86.5 MiB      0.0 MiB       query = "select * from test.test_table limit 100000;"
    41    185.5 MiB     99.0 MiB       cur.execute(query)
    42                             
    43    185.5 MiB      0.0 MiB       row_cnt = 1000
    44    188.1 MiB      2.6 MiB       rows = cur.fetchmany(row_cnt)
    45    196.2 MiB      0.0 MiB       while len(rows) > 0:
    46    196.2 MiB      1.4 MiB           df = pd.DataFrame(np.array(rows))
    47                                     print(len(df))
    48                                     # next
    49    196.2 MiB      2.6 MiB           rows = cur.fetchmany(row_cnt)
    50                             
    51    156.2 MiB      0.0 MiB       cur.close()

上記の通り、excecuteを行うまでのメモリ消費量は処理内容(クエリ)が同一なので当然fetchallと同一です。一方fetchmanyを行った際にはfetchallと比べ大幅に削減されています。これはfetchmanyで取得するレコードを1000件に絞っているためで、その後続でPandasデータフレームに変換した際にもメモリ消費量は大幅に削減されています。

まとめ

レコード長にもよりますが、数千件のレコードでしたら一括で取得して一括でデータを処理してもそれほどリソースも消費しないので特にメモリ消費量のことは考えなくても良いと思います。一方、数十万・数百万件のレコードを取得して加工しようと考えた場合は適切な処理が必要となります。

最後まで読んで頂いてありがとうございました。