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

아래 포스팅에서 진행한 데이터를 가지고 mySQL에 삽입하고 조회해보겠습니다. 

2022.07.06 - [python] - [python] 대용량 데이터 읽고 저장 ( pandas vs pyarrow ) with parquet

[python] 대용량 데이터 읽고 저장 ( pandas vs pyarrow ) with parquet

천 만개의 row를 가진 데이터이기 때문에 시간이 좀 걸릴 거라고 예상됩니다. 참고한 사이트들에는 필자보다 적은 데이터인 500-800만 개이지만 삽입하는 시간이 20-30분, 조회하는 시간이 10-20분 걸렸습니다. 

타 사이트와는 다른 점은 pyarrow와 parquet를 사용한 점인데 유의미한 결과가 나오는지 확인해보겠습니다.

 

데이터 : ecommerce 웹 로그 데이터이며 천만 개의 row와 2G 데이터입니다.

환경 : MySQL Workbench, command line

---

데이터베이스 생성 및 테이블 생성

CREATE DATABASE test_db

테이블 Columns을 구성할 때 df.info()를 참고하여 작성해도 좋지만, 직접 데이터를 보면서 type을 정하는 것이 더 정확합니다. 필자 같은 경우에도 Columns 중 category_id를 단순 int로 지정해서 처리할 때 오류가 나서 데이터를 직접 확인한 결과 bigint로 처리해줘야 하는 것을 알게 되었습니다.

 

 

 

sqlalchemy / to_sql

sqlalchemy

python에서 사용 가능한 ORM(Object-relation mapping)입니다. 데이터베이스의 데이터를 Object 필드에 매핑시켜줍니다.

먼저 create_engine이라는 메서드로 DB를 정의합니다. DB에 바로 연결시키는 것이 아닌 메모리에 인식시키는 과정입니다.

 

to_sql

sqlalchemy 라이브러리를 활용해서 Dataframe을 바로 MySQL에 저장하는 방법입니다.

import time
import pandas as pd
import datetime
import pyarrow as pa
import pyarrow.parquet as pq
import pyarrow.csv as pc
from sqlalchemy import create_engine

def connect_database_load_dataframe():
    start = time.time()
    engine = create_engine("mysql+mysqldb://root:" + {password} + "@localhost:3306/test_db", encoding='utf-8')
    # conn = engine.connect()
    # parquet 파일로 저장한 데이터를 읽고 pandas로 변형
    df = pq.read_table(PARQUET_FILE_PATH).to_pandas()
    df.to_sql(name="ecommerce", con=engine, if_exists="append", index=False)
    end = time.time()
    sec = (end - start)
    print("걸린 시간 : ", datetime.timedelta(seconds=sec))

 

 

결과

천 만개의 row를 가진 ecommerce 데이터를 DB의 ecommerce 테이블에 적재하는 데 걸린 시간입니다. 

8분 38초

DB의 ecommerce 테이블의 모든 레코드를 조회하는 데 걸린 시간입니다.

40초

데이터의 형식에 맞게 잘 들어가 있는 것을 확인할 수 있습니다.