트러블슈팅: MySQL 대용량 CSV 파일 LOAD DATA로 넣기

환경

MacOS (Homebrew MySQL 9.1.0)

데이터: CSV 14.68GB(약 1억 1천만 행)

https://www.kaggle.com/datasets/mkechinov/ecommerce-behavior-data-from-multi-category-store

eCommerce behavior data from multi category store

타깃 스토리지: InnoDB

 

 

---

스키마(스테이징) 설계

INDEX/FK 없이 먼저 적재

-- 이벤트 테이블 생성 (때용량 적재에 맞춘 컬럼 타입)
CREATE TABLE ecommerce_events (           -- 테이블 생성
    event_time DATETIME,                  -- 이벤트 시각
    event_type VARCHAR(50),               -- view/cart/purchase 등 이벤트 유형
    product_id BIGINT,                    -- 제품 ID (INT 초과 가능성 고려)
    category_id BIGINT,                   -- 카테고리 ID
    category_code VARCHAR(255),           -- 카테고리 코드(계층형 문자열)
    brand VARCHAR(255),                   -- 브랜드명
    price DECIMAL(10,2),                  -- 가격 (소수점 2자리)
    user_id BIGINT,                       -- 유저 ID (INT 초과 가능성 고려)
    user_session VARCHAR(255)             -- 세션 ID
);

 

 

사후에 INDEX 추가가 훨씬 빠르기 때문

 

 

대용량 LOAD 기본 커맨드

CSV가 로컬(내 컴퓨터)에 있고, 클라이언트에서 전송하게 하려면 LOCAL 옵션이 편함

-- 2019년 10월 데이터 적재
LOAD DATA LOCAL INFILE '/Users/semlng/2019-Oct.csv'
INTO TABLE ecommerce_events
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(event_time, event_type, product_id, category_id, category_code, brand, price, user_id, user_session);

-- 2019년 11월 데이터 적재
LOAD DATA LOCAL INFILE '/Users/semlng/2019-Nov.csv'
INTO TABLE ecommerce_events
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(event_time, event_type, product_id, category_id, category_code, brand, price, user_id, user_session);

 

실제 적재: 6,750만 행 + 4,244만 행 / 6분38초대로 완료 (환경에 따라 달라짐)

2019-Oct, 5.67GB

2019-Nov, 9.01GB

 

 

트러블 슈팅 1 - Error 1290

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

Why?

LOAD DATA INFILE(Server Side)는 서버가 직접 파일을 읽어들임.

이때 서버는 보안상 특정 폴더(=@@secure_file_priv)만 접근 가능

 

해결 옵션 2가지

1. 허용 폴더로 파일 이동 (가장 안전)

SELECT @@secure_file_priv;     -- 허용된 디렉토리 확인
-- 해당 경로로 CSV를 옮긴 뒤 LOCAL 없는 형태로 실행
LOAD DATA INFILE '/allowed/path/2019-Nov.csv' INTO TABLE ...;

 

2. LOCAL 사용으로 우회

-- 서버 파일 접근 대신, 클라이언트에서 전송
LOAD DATA LOCAL INFILE '/path/2019-Nov.csv' INTO TABLE ...;

 

 

트러블 슈팅 2 - Error 2068

LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

 

Why?

LOCAL은 서버와 클라이언트 모두 허용되어야 함

기본값이 꺼져 있는 경우가 많음

 

서버 측:

SHOW VARIABLES LIKE 'local_infile';        -- OFF면
SET GLOBAL local_infile = 1;               -- ON으로 전환(권한 필요)

 

 

클라이언트 측:

터미널 접속 시 --local-infile=1

mysql --local-infile=1 -u user -p -h host dbname

allowLoadLocalInfile = True로 설정 필수!