이 랩에서는 MySQL 데이터베이스에서 데이터를 가져오고 내보내는 필수 기술을 배우게 됩니다. 대량 데이터 삽입을 위한 빠르고 효율적인 방법인 LOAD DATA INFILE 명령을 사용하여 CSV(Comma-Separated Values) 파일에서 테이블로 데이터를 로드하는 연습을 하게 됩니다.
또한 테이블에서 새 CSV 파일로 데이터를 내보내는 역방향 프로세스도 배우게 됩니다. 추가적으로, 이 랩에서는 데이터 품질을 보장하기 위해 가져오기 후 기본적인 데이터 유효성 검사를 수행하는 방법을 다룹니다. 이 랩이 끝나면 MySQL 로 데이터를 주고받는 데 능숙해질 것입니다.
데이터를 가져오기 전에 데이터를 저장할 대상이 필요합니다. 여기에는 데이터를 수용할 데이터베이스와 가져오려는 데이터와 일치하는 구조를 가진 테이블을 만드는 작업이 포함됩니다.
먼저 데스크톱에서 터미널을 엽니다.
root 사용자로 MySQL 서버에 연결합니다. 이 랩 환경에서는 비밀번호 없이 연결하기 위해 sudo를 사용할 수 있습니다.
sudo mysql -u root연결되면 MySQL 프롬프트 (mysql>) 가 표시되며, 이는 데이터베이스 서버와 직접 상호 작용하고 있음을 나타냅니다.
다음으로 company라는 새 데이터베이스를 만듭니다. IF NOT EXISTS 절은 데이터베이스가 이미 생성된 경우 오류를 방지하는 좋은 습관입니다.
CREATE DATABASE IF NOT EXISTS company;이제 새로 만든 데이터베이스로 전환하여 후속 모든 명령이 해당 데이터베이스에 적용되도록 합니다.
USE company;마지막으로 직원 데이터를 저장할 employees라는 테이블을 만듭니다. 테이블 구조는 나중에 가져올 CSV 파일의 열과 일치해야 합니다.
CREATE TABLE IF NOT EXISTS employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department VARCHAR(50)
);INT PRIMARY KEY: id 열을 정수 및 기본 키로 정의하며, 이는 각 값이 고유해야 함을 의미합니다.VARCHAR(50): 최대 50 자까지 가변 길이 문자열을 저장할 수 있는 열을 정의합니다.다음 명령을 실행하여 테이블이 성공적으로 생성되었는지 확인할 수 있습니다.
SHOW TABLES;출력에서 employees 테이블이 나열된 것을 볼 수 있습니다.
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
+-------------------+
1 row in set (0.00 sec)다음 단계에서 계속 사용할 것이므로 MySQL 셸을 열어 둡니다.
데이터베이스와 테이블이 준비되었으므로 이제 외부 파일에서 데이터를 가져올 수 있습니다. LOAD DATA INFILE 문은 텍스트 파일에서 테이블로 데이터를 대량 로드하는 매우 효율적인 방법입니다.
이 랩의 설정 스크립트는 이미 /tmp 디렉토리에 employees.csv라는 파일을 생성했습니다. 가져오기 전에 파일 내용을 검사하는 것이 좋습니다.
중요: 현재 터미널에서 MySQL 셸이 실행 중이므로 이 명령을 위해 새 터미널 탭을 열어야 합니다. 터미널 창에서 + 아이콘을 클릭하여 새 탭을 엽니다. 새 터미널에서 다음을 실행합니다.
cat /tmp/employees.csv출력에는 쉼표로 구분된 네 줄의 데이터가 표시됩니다.
1,John,Doe,john.doe@example.com,Sales
2,Jane,Smith,jane.smith@example.com,Marketing
3,Peter,Jones,peter.jones@example.com,Engineering
4,Mary,Brown,mary.brown@example.com,HR이제 MySQL 셸 (mysql>) 이 있는 원래 터미널 탭으로 돌아갑니다. LOAD DATA INFILE 명령을 사용하여 파일을 가져옵니다.
LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';이 명령을 자세히 살펴보겠습니다.
LOAD DATA INFILE '/tmp/employees.csv': 소스 파일의 전체 절대 경로를 지정합니다.INTO TABLE employees: 데이터의 대상 테이블을 지정합니다.FIELDS TERMINATED BY ',': 각 행에서 쉼표가 데이터 필드 (열) 를 구분한다고 MySQL 에 알립니다.LINES TERMINATED BY '\n': 파일의 각 새 줄이 새 행을 나타낸다고 MySQL 에 알립니다.명령이 실행된 후 MySQL 은 가져온 행 수를 보고합니다. 가져오기가 성공했는지 확인하려면 테이블을 쿼리하여 내용을 확인합니다.
SELECT * FROM employees;출력에는 CSV 파일의 네 개의 레코드가 표시되며, 이제 employees 테이블에 저장됩니다.
+----+------------+-----------+---------------------------+-------------+
| id | first_name | last_name | email | department |
+----+------------+-----------+---------------------------+-------------+
| 1 | John | Doe | john.doe@example.com | Sales |
| 2 | Jane | Smith | jane.smith@example.com | Marketing |
| 3 | Peter | Jones | peter.jones@example.com | Engineering |
| 4 | Mary | Brown | mary.brown@example.com | HR |
+----+------------+-----------+---------------------------+-------------+
4 rows in set (0.00 sec)데이터를 내보내는 것은 가져오는 것만큼 중요합니다. 보고서를 작성하거나, 다른 시스템과 데이터를 공유하거나, 스프레드시트 프로그램에서 분석을 수행해야 할 수 있습니다. SELECT ... INTO OUTFILE 문을 사용하면 모든 쿼리 결과를 파일로 직접 저장할 수 있습니다.
먼저 MySQL 셸에서 테이블에 두 명의 직원을 더 추가해 보겠습니다.
INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', 'alice.johnson@example.com', 'Sales'),
(6, 'Bob', 'Williams', 'bob.williams@example.com', 'Marketing');이제 전체 employees 테이블을 employees_export.csv라는 새 파일로 내보냅니다. 먼저 올바른 데이터베이스에 있는지 확인합니다.
SELECT id, first_name, last_name, email, department
FROM company.employees
INTO OUTFILE '/tmp/employees_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';SELECT ...: 내보낼 데이터를 지정하는 표준 쿼리입니다.INTO OUTFILE '/tmp/employees_export.csv': 출력 파일의 전체 경로를 지정합니다. 보안상의 이유로 MySQL 은 이 파일이 이미 존재하지 않아야 합니다.FIELDS TERMINATED BY ',': 필드를 쉼표로 구분합니다.ENCLOSED BY '"': 일반적인 CSV 형식인 각 필드 값을 큰따옴표로 묶습니다.LINES TERMINATED BY '\n': 각 행을 줄 바꿈 문자로 끝냅니다.명령을 실행한 후 다른 터미널 탭으로 전환하거나 (또는 새 탭을 열어) 새로 생성된 파일의 내용을 확인합니다.
cat /tmp/employees_export.csv테이블의 모든 여섯 행이 CSV 파일 형식으로 표시됩니다.
"1","John","Doe","john.doe@example.com","Sales"
"2","Jane","Smith","jane.smith@example.com","Marketing"
"3","Peter","Jones","peter.jones@example.com","Engineering"
"4","Mary","Brown","mary.brown@example.com","HR"
"5","Alice","Johnson","alice.johnson@example.com","Sales"
"6","Bob","Williams","bob.williams@example.com","Marketing"데이터를 가져온 후에는 품질과 무결성을 보장하기 위해 유효성을 검사하는 것이 중요합니다. 실제 데이터는 종종 오류, 누락된 값 또는 잘못된 형식을 포함하여 지저분한 경우가 많습니다. 이 단계에서는 간단한 SQL 쿼리를 사용하여 일반적인 문제를 찾는 방법을 보여줍니다.
설정 스크립트는 employees_validation.csv를 생성했으며, 여기에는 잘못된 이메일과 누락된 부서 값이 포함되어 있습니다. 먼저 MySQL 셸에서 employees 테이블을 비웁니다.
TRUNCATE TABLE employees;이제 유효성 검사 파일을 가져옵니다.
LOAD DATA INFILE '/tmp/employees_validation.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';"더러운" 데이터가 로드되었으므로 몇 가지 유효성 검사를 실행해 보겠습니다.
1. 잘못된 이메일 형식 찾기
유효한 이메일에 대한 매우 기본적인 검사는 @ 기호와 . 기호가 포함되어 있는지 확인하는 것입니다. NOT LIKE를 사용하여 이 검사에 실패한 행을 찾을 수 있습니다.
SELECT * FROM employees WHERE email NOT LIKE '%@%.%';이 쿼리는 필요한 기호가 없기 때문에 이메일이 invalid_email인 행을 찾습니다.
+----+------------+-----------+---------------+------------+
| id | first_name | last_name | email | department |
+----+------------+-----------+---------------+------------+
| 3 | Invalid | Email | invalid_email | Sales |
+----+------------+-----------+---------------+------------+
1 row in set (0.00 sec)2. 누락된 부서 찾기
값이 누락된 행은 빈 문자열 ''을 확인하여 찾을 수 있습니다.
SELECT * FROM employees WHERE department = '';이 쿼리는 CSV 파일에서 부서가 비어 있는 행을 찾습니다.
+----+------------+------------+--------------------------------+------------+
| id | first_name | last_name | email | department |
+----+------------+------------+--------------------------------+------------+
| 4 | Missing | Department | missing.department@example.com | |
+----+------------+------------+--------------------------------+------------+
1 row in set (0.00 sec)이러한 간단한 쿼리는 초기 데이터 품질 검사에 강력한 도구입니다. 문제가 있는 행을 식별한 후에는 UPDATE 문으로 수정하거나 DELETE 문으로 제거할지 결정할 수 있습니다.
이제 랩을 완료했습니다. MySQL 셸을 종료할 수 있습니다.
exit이 랩에서는 MySQL 데이터베이스로 데이터를 이동하고 데이터베이스에서 데이터를 내보내는 기본 작업을 배웠습니다. 새 데이터베이스와 테이블을 사용하여 적절한 데이터베이스 환경을 설정하는 것부터 시작했습니다. 그런 다음 LOAD DATA INFILE 명령을 사용하여 CSV 파일에서 효율적으로 데이터를 가져왔습니다.
다음으로, 보고 및 데이터 공유에 일반적인 작업인 SELECT ... INTO OUTFILE 문을 사용하여 테이블에서 새 CSV 파일로 데이터를 내보내는 연습을 했습니다. 마지막으로 가져온 후 형식 오류 및 누락된 값을 확인하기 위해 SQL 쿼리로 기본 데이터 유효성 검사를 수행하는 방법을 배웠습니다. 이러한 기술은 MySQL 을 사용하는 모든 개발자 또는 관리자에게 필수적입니다.
