주피터 노트북에서 MySQL DB와 연동 작업을 하는 과제를 수행 중에 난관에 부딪혔다. 대량의 데이터를 DB에 입력, 혹은 불러오는 과정에서 처리 시간이 상당히 오래걸린다는 것.
한줄 한줄 쿼리를 입력하는 연습을 하는 초급자 입장에서는 단순히 반복문을 사용해 insert into/fetchone 함수를 사용하면 되는 줄 알았는데, 개별 쿼리를 서버에 넘기고 실행하는 속도가 상당히 느리다는 것을 깨달았다.
이렇게 많은 양의 데이터를 한꺼번에 조작해야할 때 일괄처리(Batch processing)를 사용할 수 있다는 것을 알게 되었다. 이 방법은 특히 주기적으로 대량의 데이터를 업데이트 하는 과정에서 효율적인 자동화 작업을 위해 사용된다고 하니, 잘 알아두면 좋을 것 같다.
import mysql.connector
# 최종 데이터 SQL 테이블에 넣기
sql_insert = '''
INSERT INTO
coffee_store (id, brand, name, gu_name, address, lat, lng)
VALUES (%s, %s, %s, %s, %s, %s, %s)
''' # insert query
cursor = connection.cursor() # 쿼리 실행을 위한 커서
batch_size = 50 # 데이터 크기에 따라 다르게 설정
batch = [] # 일괄처리할 데이터가 들어갈 리스트
for idx, row in df.iterrows():
row_tuple = tuple(row) # 개별 데이터의 튜플변환
batch.append(row_tuple) # 개별 데이터를 배치에 추가
if len(batch) == batch_size: # batch 50개씩 코드 실행
cursor.executemany(sql_insert, batch)
connection.commit()
batch = [] # 배치 비워주기
if batch: # batch에 남아있는 코드 실행
cursor.executemany(sql_insert, batch)
connection.commit()
cursor.close() # 닫아주는 것이 매우!! 중요하다...코드 해석
여기서 한번에 일괄처리될 batch 크기는 50이다. 경우에 따라 유동적으로 크기를 변경해서 사용하는 듯 하다. 데이터 셋이 약 600개 정도의 row로 구성되어 있어 한번에 50으로 끊어 가기로 한다.
df 데이터프레임에서 각 row를 batch 안에 순서대로 넣어준다. 배치 크기 상한인 50개의 데이터가 다 차면, executemany() 메서드를 실행해 50개의 데이터를 배치 실행하여 SQL 테이블에 넣어준다. 개별 쿼리를 실행할 때 쓰는 execute()를 사용했다간 작업 시간이 상당히 길어지더라..
하나의 배치처리가 완료되면 배치를 초기화 해주고, 다시 채워주는 과정을 반복한다.
이때 배치는 상한선인 50개가 다 차야만 코드가 실행된다. 따라서 데이터셋의 숫자가 50의 배수로 맞아떨어지지 않으면 나머지 데이터가 실행이 되지 않았다. 그래서 마지막에 if 문을 추가해줬다. 만약 전체 데이터가 303개 라고 할때, 300개의 데이터는 50개씩 처리되지만, 남은 3개는 이 반복문을 통해 처리해줘야 하는 것.
sql_table_format = '''
SELECT *
FROM
df_table
'''
batch_size = 1000
result_format = []
cursor = connection.cursor()
cursor.execute(sql_table_format)
while True:
batch = cursor.fetchmany(batch_size)
if not batch:
break
result_format.extend(batch)
cursor.close()두 번째 예시는 데이터를 불러와야 하는 경우. 기본 원리는 위와 동일한데, 차이점은 INSERT INTO와 같은 데이터 조작 언어(data manipulation language)와 달리 데이터 쿼리 언어(data query langauge)인 SELECT 문을 사용하기 때문에 첫 번째 예시에서 사용한 executemany 메서드가 아닌 fetchmany 메서드를 사용한다.
이번에는 데이터셋의 크기가 꽤 커서 배치 사이즈를 넉넉히 1000으로 설정해준다.
이번엔 while 문을 이용해서 배치실행문을 작성해보았다. For문을 이용한 코드보다 조금 더 간결해진 것 같다. Cursor를 사용해 배치크기 만큼의 데이터를 fetchmany 해주고, 이를 result_format 이라는 리스트에 extend해서 붙여준다.
만약 배치안에 fetch된 데이터가 전혀 없다면, 반복문을 빠져나오고 작업이 종료된다.
SQL을 다루는 경우엔 필연적으로 대량의 데이터를 다루게 되는 만큼, 오늘 학습한 내용이 데이터를 다루는 가장 기초적인 작업 방법이 아닌가 싶다.
AWS '배치 처리란 무엇인가요?' https://aws.amazon.com/ko/what-is/batch-processing/
