テーブルのデータをCSV形式で高速に出力する小ネタ

記事の内容

以前、同僚からテーブルのデータをCSV出力するのに時間が掛かるという話をされたので、ちょっとした内容ですがアドバイスしました。

時間の計測まではしていなかったので、どのくらい早くなるのか簡単な検証も踏まえてメモを残します。

前提

・テーブルの内容は全データそのまま出力する
・PostgreSQL 12.1

用意データ

準備したデータは以下のidだけ違うデータを1000万件用意しました。

-[ RECORD 1 ]--------
id     | 1
data1  | aiueo
data2  | kakikukeko
data3  | sasshisuseso
data4  | tachitsuteto
data5  | naninuneno
data6  | hahihuheho
data7  | mamimumemo
data8  | yayuyo
data9  | rarirurero
data10 | wawon

出力方法１

出力方法１は全データ取得して、プログラムの中でCSV形式に変換するという内容です。

SQL

select * from demo

出力コード

csv_out.py

import psycopg2
import time

def get_connection():
    return psycopg2.connect("host=localhost port=5432 dbname=sampledb user=postgres password=postgres")

path = "csv1.csv"
with get_connection() as conn:
    with conn.cursor() as cur:
        start = time.time()
        cur.execute("select * from demo")
        exec_time = time.time() - start
        print(exec_time)
        start = time.time()
        with open(path, "w") as f:
            i = 1
            for row in cur:
                csv = ''
                for data in row:
                    csv = csv + str(data) + ','
                f.write(csv[:-1] + '\n')

                if i % 1000 == 0:
                    f.flush()
                i += 1
        exec_time = time.time() - start
        print(exec_time)                

出力方法２

SQLでSQL形式に変換しておく方法です。

SQL

select 
    id | ',' | 
    data1 | ',' | 
    data2 | ',' | 
    data3 | ',' | 
    data4 | ',' | 
    data5 | ',' | 
    data6 | ',' | 
    data7 | ',' | 
    data8 | ',' | 
    data9 | ',' | 
    data10 as data
from demo 

出力コード

csv_out2.py

import psycopg2
import time
def get_connection():
    return psycopg2.connect("host=localhost port=5432 dbname=sampledb user=postgres password=postgres")

path = "csv2.csv"
with get_connection() as conn:
    with conn.cursor() as cur:
        start = time.time()
        cur.execute("select id || ',' || data1 || ',' || data2 || ',' || data3 || ',' || data4 || ',' || data5 || ',' || data6 || ',' || data7 || ',' || data8 || ',' || data9 || ',' || data10 as data from demo")
        exec_time = time.time() - start
        print(exec_time)
        start = time.time()

        with open(path, "w") as f:
            i = 1
            for row in cur:
                for data in row:
                    f.write(data + '\n')
                if i % 1000 == 0:
                    f.flush()
                i += 1
        exec_time = time.time() - start
        print(exec_time)

出力結果

結果は以下の通りです。

処理	出力方法１	出力方法２
SQL	11.68s	13.35s
ファイル出力	56.95s	15.05s

出力方法２の方が早く出力できました。

感想

個人的にはもう少し早くなるかなと思ってましたが、出力カラム数が少ないのでこんなものかもしれません。
出力するカラム数が増えるほど効果が出てくるやり方です。

DBサーバに接続してCSV出力をするのであれば、ExportコマンドやCOPY TOコマンドなどの方が早く出力が出来ると思います。
ただ、業務システムの保守開発ではテーブルのデータをそのまま出力するという処理は割とあるので、そういった場合にこのやり方を使っています。