테이블 구조

아래와같은 데이터 구조를 갖는 테이블이 있다.

테이블 데이터 추출

위의 테이블 데이터를 csv 형태로 추출하기 위해서는 아래와 같은 쿼리문을 작성하면 된다.

SELECT column_name1, column_name2, column_name3, ...
INTO OUTFILE '~/mydirectory/myfile.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE condition;

에러발생

--secure-file-priv 옵션에 의한 에러가 발생했다.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

이와 같은 에러는 --secure-file-priv 옵션이 MySQL 서버가 파일을 읽거나 쓸 수 있는 디렉토리를 제한하기 때문에 발생한다.

TROUBLE SHOOTING

SHOW VARIABLES LIKE 'secure_file_priv';

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

secure_file_priv 가 가리키는 Value 필드를 보면 /var/lib/mysql-files/ 와 같이 디렉토리 형식으로 출력이 된 것을 볼 수 있는데, 이 디렉토리가 secure_file_priv 옵션이 허용한 MySQL 서버가 읽고 쓸 수 있는 디렉토리이다. 즉, INTO OUTPUT 문 다음의 디렉토리를 이 곳으로 쓰면 된다.

SOLUTION

쿼리문에서 추출할 디렉토리를 secure_file_priv 가 허용하는 곳으로 변경하면된다.

그리고 아래와 같이 직접사용할 디렉토리로 파일을 이동해오면 된다.

• sudo mv /var/lib/mysql-files/analytics_hourly_visit_stats_5036.csv .

그리고 파일의 줄 개수(행 개수) 를 확인해보자

• wc -l analytics_hourly_visit_stats_5036.csv

쿼리문

SELECT *
INTO OUTFILE '/var/lib/mysql-files/analytics_hourly_visit_stats_5036.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM analytics_hourly_visit_stats
WHERE site_id = 5036 AND date BETWEEN '2023-01-01 00:00:00' AND '2023-06-20 23:59:59' AND region NOT IN (0, 21, 38, 39, 46, 47);

CSV to MySQL

번외로 CSV 데이터를 MySQL 의 테이블에 넣는 방법이다.

LOAD DATA INFILE 문 만으로도 해결할 수 있지만, secure_file_priv 옵션에 걸리면 LOCAL 을 INFILE 옆에 붙여서 아래와 같이 해결할 수도 있다.

하지만 이 것도 MySQL 클라이언트와 서버가 local_infile 설정이 활성화 되도록 설정 되어있을 때만 가능하다.

모든게 귀찮다면 CSV 파일을 위에서 말한 /var/lib/mysql-files/ 디렉토리로 옮긴 후, LOAD DATA INFILE 만 사용해서 해결할 수도 있다.

LOAD DATA LOCAL INFILE '~/mydirectory/myfile.csv' 
INTO TABLE table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(mac, name);

• IGNORE 1 ROWS : CSV 파일의 첫번째 행 무시(헤더있을 시)
• (mac, name) : CSV 의 첫번째 열은 테이블의 mac 필드에 맞춰서, 두번째 열은 name 필드에 맞춰서 입력

번외

아래는 본인이 가끔씩 필요한 쿼리문이다.

SELECT *
INTO OUTFILE '/var/lib/mysql-files/analytics_mobility_n_5036.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM analytics_mobility_n
WHERE
    site_id = 5036
    AND date BETWEEN '2023-01-01' AND '2023-06-26'
    AND (
        (sequence_1 NOT IN (0, 21, 38, 39, 46, 47))
        OR (sequence_1 IS NULL)
    )
    AND (
        (sequence_2 NOT IN (0, 21, 38, 39, 46, 47))
        OR (sequence_2 IS NULL)
    )
    AND (
        (sequence_3 NOT IN (0, 21, 38, 39, 46, 47))
        OR (sequence_3 IS NULL)
    )
    AND num_sequence = 3;