Pandas DataFrame을 SQL Server에 빠르게 업로드하기: fast_executemany 활용

이 문제를 해결하기 위해 pyODBC의 fast_executemany 기능을 활용하여 업로드 속도를 크게 향상시킬 수 있습니다.

fast_executemany란 무엇인가?

fast_executemany는 pyODBC에서 제공하는 기능으로, 여러 개의 SQL 쿼리를 한 번에 실행하여 성능을 향상시킵니다. pandas.DataFrame.to_sql 함수와 함께 사용하면 DataFrame의 모든 행을 단일 쿼리로 삽입하여 속도를 크게 높일 수 있습니다.

fast_executemany 활용 방법

다음은 fast_executemany를 사용하여 Pandas DataFrame을 SQL Server 테이블에 업로드하는 방법입니다.

라이브러리 import

import pandas as pd
import sqlalchemy as sa
from pyodbc import connect

데이터 준비

# Pandas DataFrame 생성
df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})

# SQL Server 연결 문자열
connection_string = 'Driver={SQL Server Native Client 11.0};Server=localhost;Database=test;Trusted_Connection=Yes;'

# SQLAlchemy 엔진 생성
engine = sa.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(connection_string))

fast_executemany 사용

# `fast_executemany` 활성화
engine.connect().execution_options(fast_executemany=True)

# DataFrame을 SQL Server 테이블에 업로드
df.to_sql('my_table', engine, if_exists='replace', index=False)

연결 닫기

engine.dispose()

추가 팁

• chunksize 옵션: 데이터 양이 매우 큰 경우 chunksize 옵션을 사용하여 DataFrame을 여러 부분으로 나누어 업로드할 수 있습니다.
• method 옵션: method 옵션을 'multi'로 설정하면 fast_executemany 기능이 활성화됩니다.

참고 자료

주의 사항

• fast_executemany 기능은 모든 데이터베이스에서 지원되는 것은 아닙니다. 사용 전에 해당 데이터베이스가 지원하는지 확인해야 합니다.
• fast_executemany 기능을 사용하면 데이터 유형 검사가 비활성화됩니다. 데이터 유형이 올바른지 확인해야 합니다.

예제 코드

import pandas as pd
import sqlalchemy as sa
from pyodbc import connect

# 데이터 준비
df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})

# SQL Server 연결 문자열
connection_string = 'Driver={SQL Server Native Client 11.0};Server=localhost;Database=test;Trusted_Connection=Yes;'

# SQLAlchemy 엔진 생성
engine = sa.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(connection_string))

# `fast_executemany` 활성화
engine.connect().execution_options(fast_executemany=True)

# DataFrame을 SQL Server 테이블에 업로드
df.to_sql('my_table', engine, if_exists='replace', index=False)

# 연결 닫기
engine.dispose()

• 이 코드는 pandas.DataFrame.to_sql 함수와 pyODBC의 fast_executemany 기능을 사용하여 Pandas DataFrame을 SQL Server 테이블에 업로드합니다.
• connection_string 변수에 SQL Server 연결 문자열을 설정합니다.
• engine 변수에 SQLAlchemy 엔진을 생성합니다.
• engine.connect().execution_options(fast_executemany=True) 코드는 fast_executemany 기능을 활성화합니다.
• df.to_sql('my_table', engine, if_exists='replace', index=False) 코드는 DataFrame을 'my_table' 테이블에 업로드합니다.

참고

• 이 코드는 예시이며, 실제 환경에 맞게 수정해야 합니다.

fast_executemany 외에 Pandas DataFrame을 SQL Server 테이블에 빠르게 업로드하는 방법에는 다음과 같은 대안들이 있습니다.

bulk_insert_records 함수 사용

from sqlalchemy.orm import sessionmaker

# Session 생성
session = sessionmaker(bind=engine)()

# `bulk_insert_records` 함수 사용
session.bulk_insert_records(table, df.to_dict(orient='records'))

# Session 종료
session.close()

bcp 명령 사용

SQL Server에서 제공하는 bcp 명령을 사용하여 CSV 파일을 직접 테이블에 업로드할 수 있습니다.

bcp my_table in data.csv -T -c -S localhost -d test

• data.csv: DataFrame을 CSV 파일로 저장한 파일 경로
• my_table: 업로드할 테이블 이름
• -T: 텍스트 형식으로 업로드
• -c: 열 구분 문자를 쉼표로 설정
• -S: SQL Server 서버 이름
• -d: 데이터베이스 이름

SSIS 패키지 사용

SQL Server Integration Services (SSIS)를 사용하여 데이터 업로드를 위한 패키지를 만들 수 있습니다. SSIS는 다양한 데이터 ソース와 목적지를 지원하며, 복잡한 데이터 마이그레이션 작업을 자동화할 수 있습니다.

고속 데이터베이스 사용

데이터 양이 매우 크고 성능이 중요한 경우 NoSQL 데이터베이스와 같은 고속 데이터베이스를 사용하는 것을 고려할 수 있습니다.

적절한 방법 선택

위에 설명된 방법 중 어떤 방법이 가장 적합한지는 다음과 같은 요소들을 고려하여 선택해야 합니다.

• 데이터 양
• 데이터 유형
• 성능 요구 사항
• 사용자의 기술 수준

참고 자료