[홀로서기 #01] Python에서 대용량 데이터 SQL에 적재(Insert)하기

---

# 홀로서기 기획 연재물은 최근 개인 프로젝트를 진행하면서 겪은 어려움들을 기록한 지극히 개인적인 콘텐츠입니다.

---

SQL, 손에 익어 간다.

  파이썬을 처음 쓸 때, C++을 썼던 때가 떠올랐다. 코딩하는 사람들에게 너무나도 쉽고 직관적인 언어인 듯했다. C++을 쓸 때보다 훨씬. 그래서인지, 강의를 들을 때도 지금 강의도 술술 읽히고 다음 강의도 술술 읽혔다. 쉽고 편리한 언어에 매료되어 금방 초급을 뗐다.

  그런데, SQL은 더 직관적이었다. 'SELECT * FROM TABLE' 이라니. 그냥 문장을 적으면 실행이 된다. 슥-슥- 내가 원하는 명령어를 평소에 쓰던 어휘들로 이어 붙여 실행한다. Command 창을 켜고 로그인만 하면.

# AWS connecting
mysql -h [Database Name].[Host Address] -P [Port] -u [Username] -p

# SELECT
SELECT * FROM Table;
SELECT Column FROM Table WHERE Condition;

# UPDATE
UPDATE Table SET Column = 0 WHERE Condition;

# INSERT
INSERT INTO Table VALUES (0, 0, 0);

# DELETE
DELETE FROM Table WHERE Condition;

- 기본적으로 많이 쓸 것들은 정리를 해봤다. (SQL)

 

  특히 매력적인 것은 데이터베이스를 AWS에서 이용하는 것이었다. Remote에 접근하는 코드를 Terminal로 입력해서 진행하는 것도 굉장히 흥미로운 일이었다. 그리고 그것을 이제는 VS Code에서 한다. SQL 쿼리 파일을 만들어서, Python으로 실행해서 SQL 데이터베이스를 수정할 수도 있다. 터미널을 거치지 않고, Python에서 AWS으로 바로.

- 기본적으로 mysql.connector 를 활용한다.

- SQL에 적재하기 전에 기본적으로 pandas의 DataFrame 으로 기본적인 전처리를 해주길 추천한다.

# import library : mysql.connector
import mysql.connector
conn_aws = mysql.connector.connect(
    host = "[Host Address]",
    port = [Port],
    user = "[Username]",
    password = "[Password]", 
    database = "[Database Name]"
)

# Execute project init query (Create database and table)
cur = conn_aws.cursor()
sql = open("project.sql").read()
cur.execute(sql)

# Insert data into table(weather)
# %s : value
cur = conn_aws.cursor(buffered=True)
sql = """INSERT INTO Weather VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
for i, row in weather.iterrows():
    cur.execute(sql, tuple(row))
    conn_aws.commit()
    
# Select all data from table(weather)
cur.execute("SELECT * FROM Weather")
result = cur.fetchall()
for row in result:
    print(row)

대용량 데이터(Python → SQL) 적재 문제 (Execute SQL File, SQL Bulk Insert)

  어쩌면 이번 화를 쓰게 된 가장 큰 이유가 이것 때문일지도. 내가 적재해야 할 프로젝트 데이터셋 중에 무려 800만 개가 넘는 세트가 있었다. 위 방법(cur.execute(sql, tuple(row)))으로 진행하면 800만 개의 데이터프레임을 한줄 한줄 읽어서  execute 해주어야 하기 때문에, 매우 시간이 오래 걸린다. (반나절에도 끝나지 않을 것이다..)

  그리하여, 본인이 진행했던 몇 가지 방법을 소개하고자 한다. 상세한 한국어 후기가 없어서 힘겨웠던 나의 지난 날이 있었기에, 이 글을 읽는 독자는 그런 불편함을 겪지 않기를 바란다.

 

1) execute 대신 executemany 활용하기

# dataframe = user -> list setting
# tqdm : we can see the progress status of 'for iteration'
from tqdm import tqdm
data = []
for i, row in tqdm(user.iterrows()):
    data.append(tuple(row))

# AWS Connection
import mysql.connector
conn_aws = mysql.connector.connect(
    host = "[Host Address]",
    port = [Port],
    user = "[User Name]",
    password = "[password]", 
    database = "[Database Name]"
)

# sql writing and commit
sql = """INSERT INTO User (bicycle_index, time_start, stop_index1, stop_1, count_stop1, time_end, stop_index2, stop_2, count_stop2, cycle, distance) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
cur = conn_aws.cursor(buffered=True)
cur.executemany(sql, data)
conn_aws.commit()

  executemany를 활용하는 방법이다. 기존이 execute에 'many'가 붙은 것만 보아도, 많은 줄의 데이터 프레임을 sql로 수행하기 위한 적절한 명령어라는 것을 느낄 수 있을 것이다. 어디서 본 바로는 3~4배에서 빠르면 10배까지도 성능을 높여준다고 한다.

  문제는, 필자는 노트북 용량의 문제(메모리)였는지, MemoryError가 떴었다. 그리고, 몇 번의 시도를 더 했지만, sql connection이 끊기는 문제가 계속 발생했다. mysql 자체에서 connection time 설정을 변경했는데도 불구하고, 알 수 없는 이유로 적재를 실패했다.

# connection problem
2013 (HY000): Lost connection to MySQL server during query

 

2) sqlalchemy(create_engine) + to_sql 활용하기

# library import : sqlalchemy 
from sqlalchemy import create_engine

# engine : AWS connecting
engine = create_engine("mysql+pymysql://[username]:"+"[passward]"+"[host]:[port]/[database name]?charset=utf8", encoding='utf-8')
conn = engine.connect()

# to_sql (user : Dataframe / name = tablename, con = connection)
import pandas as pd
user.to_sql(name="User", con=engine, if_exists='append', index=False)

8,135,015개의 데이터가 잘 들어갔다. SELECT COUNT만 하는데 무려 18.3초!

  구글링을 통해서 여러 방법을 시도했지만, mysql로 구현이 불가능하거나 옛 게시글이어서 라이브러리 내 모듈이 사라진 코드들이 몇 개 있었다. 그 중 Pandas의 to_sql로 데이터프레임을 통째로 올리는 방법이 있다고 해서, 진행했는데 성공했다. 찾아보니까, 데이터 100만 개 적재하는데, 약 4분 정도 소요된다고 한다. (훨씬 빠르다.. 감사합니다.) 나의 경우에는 26분 15초 정도 소요된 것으로 확인했다.

 

3) 기타 방법 활용하기

https://medium.com/analytics-vidhya/speed-up-bulk-inserts-to-sql-db-using-pandas-and-python-61707ae41990

Speed up Bulk inserts to SQL db using Pandas and Python

  본인은 시도해보지 않았지만, SQL Bulk insert를 시도한 다양한 사례가 있다. Method 4가 가장 빠른 방법이라고 하는데, 나는 마지막 대안이라고 생각하고 찾아놓았으나, 2번 방법으로 성공해서 시도하지 않았다. 다음 번에 시도할 일이 생긴다면 Medium 글에서 소개된 가장 빠른 방법(확인해보지는 않았지만, 실험한 바로는 그렇다고 한다)으로 Insert를 시도해보고 후기를 아래 남기도록 하겠다.

 

 

---

 

Photo : Unsplash, "Data Science"

 

 

이번 겨울에는 큰 사이즈의 데이터를 정돈할 기회가 많을 듯하다.

SQL을 많이 익혀서 프로젝트 수행에 효율성을 높이도록 해야겠다.

다음 화에서 이어서 프로젝트를 되짚어보겠다. 홀로서기 #1 끝.

---