[Olist 이커머스 분석 #1] MySQL에 데이터 적재

https://github.com/heyroha/olist-ecommerce-analysis

GitHub - heyroha/olist-ecommerce-analysis: Olist Brazilian E-Commerce (RFM,Delivery)

 

---

🎯 프로젝트 개요

브라질 최대 이커머스 플랫폼 Olist의 실거래 데이터(약 10만 건)를 활용해 RFM 고객 분석, 배송 지연 분석, 지리 시각화까지 이어지는 데이터 분석 포트폴리오를 만들고 있습니다.

#1 — MySQL에 9개 테이블을 적재하는 과정과 그 과정에서 마주친 오류들을 정리했습니다.

📁 데이터셋: Kaggle - Brazilian E-Commerce Public Dataset by Olist 🛠️ 사용 도구: MySQL 8.0.47 + MySQL Workbench

---

📦 데이터셋 구조

총 9개의 CSV 파일로 구성되어 있으며, 각 테이블은 아래와 같이 연결됩니다.

테이블명 내용 행 수

olist_customers_dataset	고객 정보	99,441
olist_geolocation_dataset	위경도 정보	1,000,163
olist_orders_dataset	주문 정보	99,441
olist_order_items_dataset	주문 상품	112,650
olist_order_payments_dataset	결제 정보	103,886
olist_order_reviews_dataset	리뷰 정보	99,224
olist_products_dataset	상품 정보	32,951
olist_sellers_dataset	판매자 정보	3,095
product_category_name_translation	카테고리 번역	71

---

🏗️ DB 및 테이블 생성

CREATE DATABASE IF NOT EXISTS olist
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE olist;

한글·포르투갈어 등 다국어 데이터가 포함되어 있어 utf8mb4로 설정했습니다.

CREATE TABLE customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    customer_unique_id VARCHAR(50),
    customer_zip_code_prefix VARCHAR(10),
    customer_city VARCHAR(100),
    customer_state VARCHAR(5)
);

CREATE TABLE geolocation (
    geolocation_zip_code_prefix VARCHAR(10),
    geolocation_lat DECIMAL(11,8),
    geolocation_lng DECIMAL(11,8),
    geolocation_city VARCHAR(100),
    geolocation_state VARCHAR(5)
);

CREATE TABLE orders (
    order_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    order_status VARCHAR(20),
    order_purchase_timestamp DATETIME,
    order_approved_at DATETIME,
    order_delivered_carrier_date DATETIME,
    order_delivered_customer_date DATETIME,
    order_estimated_delivery_date DATETIME
);

CREATE TABLE order_items (
    order_id VARCHAR(50),
    order_item_id INT,
    product_id VARCHAR(50),
    seller_id VARCHAR(50),
    shipping_limit_date DATETIME,
    price DECIMAL(10,2),
    freight_value DECIMAL(10,2),
    PRIMARY KEY (order_id, order_item_id)
);

CREATE TABLE order_payments (
    order_id VARCHAR(50),
    payment_sequential INT,
    payment_type VARCHAR(30),
    payment_installments INT,
    payment_value DECIMAL(10,2)
);

CREATE TABLE order_reviews (
    review_id VARCHAR(50),
    order_id VARCHAR(50),
    review_score INT,
    review_comment_title VARCHAR(100),
    review_comment_message TEXT,
    review_creation_date DATETIME,
    review_answer_timestamp DATETIME
);

CREATE TABLE products (
    product_id VARCHAR(50) PRIMARY KEY,
    product_category_name VARCHAR(100),
    product_name_length INT,
    product_description_length INT,
    product_photos_qty INT,
    product_weight_g INT,
    product_length_cm INT,
    product_height_cm INT,
    product_width_cm INT
);

CREATE TABLE sellers (
    seller_id VARCHAR(50) PRIMARY KEY,
    seller_zip_code_prefix VARCHAR(10),
    seller_city VARCHAR(100),
    seller_state VARCHAR(5)
);

CREATE TABLE category_translation (
    product_category_name VARCHAR(100) PRIMARY KEY,
    product_category_name_english VARCHAR(100)
);

 

---

📥 데이터 적재 방법 선택

Table Data Import Wizard

처음에는 Workbench의 Table Data Import Wizard를 사용했으나 인코딩 오류가 발생했습니다. 이후 LOAD DATA LOCAL INFILE 방식으로 전환했습니다.

방법	장점	단점
Import Wizard	GUI, 간편	BOM 인코딩 오류, 느림
LOAD DATA LOCAL INFILE	빠름, 세밀한 제어 가능	사전 설정 필요

---

⚙️ LOAD DATA 사전 설정

LOAD DATA LOCAL INFILE을 사용하려면 두 가지 설정이 필요합니다.

① MySQL 서버 설정

SET GLOBAL local_infile = 1;

② Workbench 연결 설정 Connection > Advanced > Others 입력란에 아래 추가 후 재접속:

OPT_LOCAL_INFILE=1

---

🔄 적재 쿼리 (예시: orders 테이블)

LOAD DATA LOCAL INFILE '/Users/username/Downloads/archive/olist_orders_dataset.csv'
INTO TABLE orders
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(order_id, customer_id, order_status, order_purchase_timestamp, order_approved_at,
 order_delivered_carrier_date, order_delivered_customer_date, order_estimated_delivery_date);

---

✅ 최종 적재 검증

SELECT 'customers' t, COUNT(*) c FROM customers
UNION ALL SELECT 'geolocation', COUNT(*) FROM geolocation
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'order_payments', COUNT(*) FROM order_payments
UNION ALL SELECT 'order_reviews', COUNT(*) FROM order_reviews
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'sellers', COUNT(*) FROM sellers
UNION ALL SELECT 'category_translation', COUNT(*) FROM category_translation;

결과:

<테이블 적재 행 수>

---

🚨 시행착오 & 트러블슈팅

❌ 오류 1. Import Wizard BOM 인코딩 오류

Unhandled exception: 'ascii' codec can't decode byte 0xef

원인: UTF-8 BOM 파일을 Import Wizard가 올바르게 처리하지 못함 해결: Import Wizard 포기 → LOAD DATA LOCAL INFILE로 전환

---

❌ 오류 2. Error Code 2068 — LOCAL INFILE 거부

Error Code: 2068. LOAD DATA LOCAL INFILE disabled

원인: 보안상 기본값이 비활성화 해결:

SET GLOBAL local_infile = 1;

Workbench Advanced 설정에 OPT_LOCAL_INFILE=1 추가 후 재접속

---

❌ 오류 3. Error Code 2 — 파일을 찾을 수 없음

Error Code: 2. File not found

원인 1: 경로를 Windows 스타일(C:/Users/...)로 작성 → Mac에서는 C: 없이 /Users/... 원인 2: 폴더명 오타 (/olist/ → 실제 경로는 /Downloads/archive/) 해결: Mac 절대경로 형식으로 수정

/Users/username/Downloads/archive/파일명.csv

---

❌ 오류 4. 테이블명/컬럼명 혼동

원인: 쿼리를 복붙하다가 테이블명을 바꾸지 않아 geolocation 데이터가 category_translation에 들어감 해결:

TRUNCATE TABLE category_translation;
-- 올바른 쿼리로 재적재

교훈: 복붙 후 반드시 테이블명·컬럼 목록 검토

---

⚠️ 경고 1. Warning 1265 — DECIMAL 자리수 초과 (geolocation)

Warning 1265: Data truncated for column 'geolocation_lat'

원인: CSV의 위경도가 소수점 8자리 이상인데 컬럼이 DECIMAL(10,6)으로 선언됨 해결: 컬럼을 DECIMAL(11,8)로 수정 (0.11m 이하 오차 → 분석에 무방) 적재 결과: Records: 1,000,163 / Skipped: 0 ✅

---

⚠️ 경고 2. Warning 4096 — \r 구분자 (order_reviews)

Warning 4096: Delimiter '\r' is deprecated

원인: Windows에서 생성된 CSV의 줄바꿈이 \r\n인데 MySQL 8.x가 \r 단독 사용 경고 해결: 경고일 뿐, 에러 아님. Records: 99,224 / Skipped: 0 ✅

---

⚠️ 경고 3. Warning 1366 — 빈 문자열 → INT 변환 (products)

Warning 1366: Incorrect integer value: '' for column 'product_photos_qty'

원인: 일부 상품의 크기·수량 정보가 원본 CSV에 아예 없음 (실제 결측값) 해결: 경고일 뿐, 0으로 저장됨. Records: 32,951 / Skipped: 0 ✅ 참고: 3강 EDA에서 결측치 비율 확인 예정

---

⚠️ 경고 4. Warning 1265 — 빈 DATETIME (orders)

Warning 1265: Data truncated for column 'order_delivered_customer_date'

원인: 배송 완료되지 않은 주문은 order_delivered_customer_date가 NULL — 정상 데이터 해결: 경고 무시. Records: 99,441 / Skipped: 0 ✅

---

❌ 오류 5. Error Code 1054 — 컬럼명 불일치 (products)

Error Code: 1054. Unknown column 'product_name_lenght' in 'field list'

원인: CSV 헤더는 product_name_lenght(오타)인데, 테이블은 product_name_length(정확한 철자)로 생성됨 해결: @변수로 CSV 컬럼을 받아서 SET으로 매핑

LOAD DATA LOCAL INFILE '...'
INTO TABLE products
...
(product_id, product_category_name, @product_name_lenght, @product_description_lenght, ...)
SET
  product_name_length = @product_name_lenght,
  product_description_length = @product_description_lenght;

---

💡 회고

잘한 점

• 오류를 만났을 때 Import Wizard에 집착하지 않고 LOAD DATA LOCAL INFILE로 빠르게 전환한 것
• 각 오류 메시지를 정확히 읽고 Error Code별로 원인을 구분한 것
• 적재 후 행 수 검증 쿼리로 모든 테이블을 확인한 것

아쉬운 점

• 복붙 실수로 geolocation 데이터를 category_translation에 넣은 것 → 쿼리 실행 전 테이블명 한 번 더 확인 습관 필요
• 경고와 오류를 처음에 구분하지 못해 당황했던 것 → Records / Skipped 숫자를 먼저 보는 게 핵심

배운 것

"Warning은 읽고, Error는 고친다." Skipped: 0이면 데이터는 모두 들어간 것. 결측값·타입 불일치 경고는 3강 EDA에서 분석의 재료가 된다.

---

🔜 다음 포스팅

[Olist 이커머스 분석 #2] SQL로 EDA — 주문 추이, 카테고리 매출, 배송 소요일 분석

---

본 시리즈는 Kaggle의 Olist Brazilian E-Commerce 데이터셋을 활용한 포트폴리오 프로젝트입니다.