[미해결] 대용량 데이터 MySQL에 저장 및 조회 - executemany 의문점

필자가 적재해야 할 데이터 셋은 1000만 개의 세트가 있는데, 단순 cur.execute로 진행하면 데이터 프레임을 한 줄씩 읽어서 execute 해주기 때문에 오랜 시간이 걸립니다. 그래서 executemany를 사용하기로 결정했습니다.

import mysql.connector
import time
import pyarrow.parquet as pq
import datetime
from tqdm import tqdm

def csv_multi_insert_table():
	# parquet 형식으로 저장한 데이터를 pandas dataframe으로 변형해서 저장
    df = pq.read_table(PARQUET_FILE_PATH).to_pandas()
	
    # 시간 측정
    # dataframe 행에 대한 순한 반복으로 튜플로 변형 후 리스트에 저장
    start = time.time()
    data = []
    for i, row in tqdm(df.iterrows()):
        data.append(tuple(row))

    end = time.time()
    sec = (end - start)
    print("dataframe -> tuple list 변환 걸린 시간 : ", datetime.timedelta(seconds=sec))
	
    # db 연결
    mydb = mysql.connector.connect(
        host="localhost",
        port=3306,
        user="root",
        password={password},
        database="test_db"
    )
    
    # 시간 측정
    start = time.time()
    my_cursor = mydb.cursor(buffered=True)
    sql = "INSERT INTO ecommerce_executemany (event_time, event_type, product_id, " \
          "category_id, category_code, brand, price, user_id, user_session) " \
          "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
    # execute를 사용하여 sql에 맞게 data를 db에 저장
    my_cursor.executemany(sql, data)
    mydb.commit()

    end = time.time()
    sec = (end - start)
    print("db 적재 걸린 시간 : ", datetime.timedelta(seconds=sec))

executemany를 사용하면 많은 줄을 가진 데이터 프레임을 상대로 좋은 성능을 보여주는 명령어입니다. (execute와 비교해서)

문제는 "Loss coneection to MySQL server during query" 오류가 떴는데, 검색한 결과 실행한 쿼리에 걸리는 시간이 길어져서 정해진 제한 시간 동안 쿼리가 종료되지 못해서 발생한 오류였습니다.

해결 방법으로는 Workbench Preference > SQL Editor > 쿼리 제한 시간을 0(무한)이나 10시간으로 변경하고 재 시작하고 실행했지만 해결하지 못했습니다.

 

혹시, 저와 똑같은 상황이신 분이나 해결 방법을 아시는 분은 댓글이나 메일로 남겨주시면 감사하겠습니다. 

이메일 : goosw775@naver.com

 

 

sql 테이블에 대한 내용과 위 방식 말고 다른 방식으로 MySQL에 적재하시고 싶으시다면 아래 포스팅을 참고해주시면 됩니다. 감사합니다.

2022.07.07 - [python/MYSQL] - [MySQL] 대용량 데이터 MySQL에 저장 및 조회 - sqlalchemy, to_sql

[MySQL] 대용량 데이터 MySQL에 저장 및 조회 - sqlalchemy, to_sql