[Python]pandas 의 to_sql 이용한 Bulk insert

DB 를 이원화할 일이 생겨서 기존 mariadb 랑 신규 timescaledb 간 데이터 동기화 구현이 필요해졌다.

본격 작업 전에 python 으로 mariadb -> timescaledb 스크립트로 몇 가지 테스트를 해보기로 했는데 얼마 전 pandas 의 read_sql_query 과 to_csv 편하게 써먹었던 기억 있어서 DB write 하는 기능도 었으려나 하고 찾아봤는데 to_sql 이란게 제공되네요.

from sqlalchemy import create_engine
import pandas as pd
import pymysql
import time

def pg_connect(user, password, db, host, port=5432):
    url = 'postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, db)
    return create_engine(url, client_encoding='utf8')
    return engine

def md_connect(user, password, db, host, port=3306):
    url = 'mysql+mysqldb://{}:{}@{}:{}/{}'.format(user, password, host, port, db)
    engine = create_engine(url)
    return engine


host = '...'
user = '...'
passwd = '...'
db_name = '...'
query = """
    SELECT ...
    FROM source_table
    limit 1000
    """

start_time = time.time()
md_engine = md_connect(user, passwd, db_name, host)
df = pd.read_sql_query(query, md_engine)

pg_engine = pg_connect(user, passwd, db_name, host)
start_time1 = time.time()
df.to_sql('target_table', con=pg_engine, if_exists='append', chunksize=1000, index=False)

print("Total  : %s seconds" % (time.time() - start_time))
print("to_sql : %s seconds" % (time.time() - start_time1))

 

동작은 잘 하는데 bulk insert 용으로는 속도가 너무 떨어진다. to_sql 못 쓰는걸까 아쉬워하려던 참에 유용한 옵션을 몇 개 발견.

Bulk insert 성능을 높이는 방법.

1. create_engine 옵션
   1. use_batch_mode=True : 소스에서 create_engine(url, client_encoding='utf8', use_batch_mode=True) 처럼 사용.
   2. fast_executemany=True : 이건 python odbc 드라이버를 사용할 때 사용할 수 있는 옵션 같은데 아직 테스트해보지 못했음.
2. to_sql 옵션 : method='multi'
   • df.to_sql('table_name', con=engine, if_exists='append', chunksize=1000, index=False, method='multi')

 

각각의 방법으로 1000건, postgresql 에 insert 하는 시간 테스트해본 결과.

• 원래 소스(옵션 적용 없는 상태) : 58.40686011314392 seconds
• use_batch_mode=True (1.1) 만 적용했을 때 : 11.04028344154358 seconds
• use_batch_mode=True 와 method='multi' 적용 ( 1.1 + 2 ) : 3.76364803314209 seconds

두 옵션 조합했을 때 최고의 성능! (아쉽게도 create_engine 의 use_batch_mode 옵션은 postgresql 만 지원하는 듯)