🐼

pandasのDataFrame.to_sqlのmethodとchunksize設定について

2023/08/29に公開

Python

pandas

SQLAlchemy

tech

ことのはじまり

BigQueryで集計した結果をMySQLに入れる作業でpythonを用いてこんな感じの実装をしていた（実際はもうちょいいろいろしてる

query_job = bq_client.query(some_query)
query_job.result()
result = query_job.to_dataframe()

result.to_sql(
	some_table,
	connection,
	if_exist="append",
)

あるとき、この部分でエラーが起きて対応したのだが（この対応自体は本記事とあまり関係ないため省略）、pandasのドキュメントを読んでいたところ、 to_sql() のオプションに気になることが書いていた

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

この中のパラメータの部分である

chunksize : int, optional
Specify the number of rows in each batch to be written at a time. By default, all rows will be written at once.

method : {None, ‘multi’, callable}, optional
Controls the SQL insertion clause used:
None : Uses standard SQL INSERT clause (one per row).
‘multi’: Pass multiple values in a single INSERT clause.
callable with signature (pd_table, conn, keys, data_iter).
Details and a sample callable implementation can be found in the section insert method.

つまり、chunksizeの方では標準ではデータの行は全て一度に書き込まれると記述されているが、methodの方では標準では1行ずつ実行されると書いているわけである
どっち？

ちなみに結論から言ってしまうと、DBのエンジン次第のようである
普通にMySQL/SQLAlchemyの接続先の場合、1つのクエリで全てのデータが挿入されるようだった

同じ疑問を持ってた人がいた

英語だが同様に疑問に思った人が立てたIssueがあった
ドキュメントの修正が提案されており、案まででているがその後アクションがないっぽい。実際ドキュメントの記述は修正されていない
ただここで提案された際に動作を記述されているソースコードの場所が出ているのでそこを追ってソースを見て見るとする

pandasの実装

というわけで上のIssueで上がっていた to_sql の実装を見てみると、確かに分岐している
ただ、どうも実行自体は今度はデータベースのライブラリの方に引き継がれているのでは？となった

insert methodについて

というわけでふたたびドキュメントに戻ると、ドキュメントでは詳しいことは insert method の項を見ろと言っていたのでそっちを見る

これを見たところ、'multi'による挿入はすべてのバックエンドでサポートされていないとのことが書いてある。いわく、PrestoやRedshiftのような分析計のDB向けの処理とも書いてある。また、旧来のSQL（traditional sql）ではカラム数が増えると逆にパフォーマンスが悪化するらしい。

なんか調べる途中に method='multi' を指定して高速化！って言ってるポストがいっぱい出たけど本当に早くなっているのだろうか。疑問である

さらに細かい情報はSQLAlchemyの方を見ろとも書いている

というわけでSQLAlchemyのドキュメント

The Insert construct also supports being passed a list of dictionaries or full-table-tuples, which on the server will render the less common SQL syntax of “multiple values” - this syntax is supported on backends such as SQLite, PostgreSQL, MySQL, but not necessarily others:

と書いている。リストで渡してあげると勝手にmultiple valuesとして処理してくれるらしい（ここの multiple values が上記の method='multi' と同等の処理なのかは調査しきれていない）。
まあつまるところ、pandasのto_sqlで設定しなくてもその先でいい感じにやってくれているようだった

ちなみにこのページには続きに multiple-parameters というものも書いてある

いわく、データが多いとこっちのほうがパフォーマンスがいいらしい。条件等の記述はない様子（誰か知ってたら教えてください

結論

to_sql の method と chunksize は、設定しなかった場合
- pandasはデータを全部渡す
- SQLAlchemyの場合、渡されたデータをまとめてInsertしようとしてくれる
- 結果、method=Noneでもワンクエリで全データをInsertしてくれる
method='multi'を指定した場合、SQLAlchemyを始めとしたライブラリには多量のデータを前提として処理が渡されるっぽい
pandasのドキュメントは、古いバージョンのSQLAlchemyを参照していて（当時は一行ずつ処理していたとかで）それが追記されていないためこうなっている？
- あくまで推測

具体的にパラメータ変更によるクエリの変化等も追えればいいのかもしれないが、ちょっと忙しいのでこういう記事だけ残しておくことにする