[mysql] mysqlimport 커맨드
1. mysqlimport
1-1. mysqlimport 설명
- 기본 사용법 : mysqlimport [options] db_name textfile
- db이름은 나와있지만 테이블 이름은 존재하지 않음 이를 해결하기 위해서는 테이블 이름과 파일이름을 매핑 시켜야함.
- test_1과 test_1.txt은 서로 같은 test_1테이블에 데이터를 가져옴(확장자는 중요하지 않음.)
- CLI로 되어 있는 프로그램으로 Load Data SQL과 같은 방법으로 데이터를 테이블에 로드함.
- Load Data란 고속 데이터 적재 방법으로 mysqldump로 덤프된 파일보다 속도가 더욱 빠름.
- mysql 클라이언트에 로그인하지 않고 로드를 하고 싶다면 mysqlimport를 사용해야함.
1-2. 옵션 설명
--help, -?
도움말을 출력해줌.
--character-sets-dir=path
문자 셋이 설치되어 있는 디렉토리를 나타냄.
--columns=column_list, -c column_list
이 옵션은 쉼표(,)로 구분된 컬럼 이름 리스트를 자신의 값으로 가져간다. 컬럼 이름의 순서는 데이터 파일 컬럼을 테이블 컬럼과 어떻게 매치를 시켜야 하는지를 나타냄.
--compress, -C
가능한 클라이언트와 서버간에 전달되는 정보를 압축함.
--debug[=debug_options], -# [debug_options]
디버깅 로그를 작성함. debug_options 일반적인 문자는 'd:t:o,file_name'. default는 d:t:o
--debug-check
프로그램 종료시 디버깅 정보 출력
--debug-info
프로그램 종료시 디버깅 정보, 메모리, CPU 사용량 출력
--default-character-set=charset_name
기본적으로 사용할 문자 셋 이름 지정
--delete, -D
텍스트 파일을 임포트 이전에 테이블을 비움.
--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=..., --lines-terminated-by=...
이 옵션들은 LOAD DATA INFILE에 대해서 상응되는 구문들과 같은 의미를 가지고 있음.
fields 설명
- terminated by : 필드 구분자
- enclosed by : 필드를 구분지을 수 있는 처음과 끝 문자 (‘문자’ -> ‘을 의미함)
- optionally-enclosed-by : 필요할 경우 필요한 구분자를 더 추가함.
- escaped-by : 필드를 빠져나올 수 있는 구분자(데이터에 빈값이 존재 할 때 이 필드를 빠져나올 수 있는 방법이 존재해야함. 그러지 않으면 컬럼 개수 대비 데이터가 일치하지 않고 순서가 어긋날 수 있음.
--force, -f
에러를 무시함. 예로 만일 텍스트 파일에 대한 테이블이 존재하지 않는다면, 남아 있는 파일들에 대한 처리를 계속 진행함. --force를 사용하지 않으면, 테이블이 존재하지 않을 경우에는 mysqlimport가 종료됨.
--host=host_name, -h host_name
데이트를 임포트할 서버를 지정함.
--ignore-lines=N
데이터 파일안에 처음부터 N번째 라인까지 무시함.
--local, -L
기본적으로 파일은 MYSQL 서버 로컬에서 읽음, 만약 원격 클라이언트에서 서버로 데이터를 전송하려면 옵션 사용 필요함.
--lock-tables, -l
텍스트 파일을 처리하기 전에 모든 테이블을 잠금. 이를 통해 모든 테이블이 서버에서 동기화 될 수 있음.
--low-priority
테이블을 로드시 사용함.
--password[=password], -p[password]
서버에 접속시 사용할 패스워드
--port=port_num, -P port_num
접속에 사용할 TCP/IP 포트 번호
--print-defaults
프로그램 이름과 옵션 파일에서 얻는 모든 옵션 인쇄
--protocol={TCP|SOCKET|PIPE|MEMORY}
사용할 접속 프로토콜
--replace, -r (중복키 처리)
--replace 와 --ignore 옵션은 유니크 키 값에 존재하는 열을 복제(duplicate)하는 입력 열 처리를 제어함. 만약 --replace를 지정하면, 새로운 열이 동일한 유니크 키 값을 가지고 있는 현재의 열을 대체함. 만일 --ignore를 지정하면, 유니크 키 값에 존재하는 열을 복제하는 입력 열은 무시됨. 만일 두 가지 중에 어느 것도 지정을 하지 않으면, 복제 키 값이 발견될 때 에러가 발생하고, 텍스트 파일의 나머지는 무시가 됨.
--ignore, -i
--replace 옵션에 대한 설명을 보여 줌.
--silent, -s
에러가 발생할 시에만 출력
--socket=path, -S path
localhost에 연결하는 경우 사용되는 유닉스 소켓 파일, 또는 윈도우 네임드 파이프의 이름
--user=user_name, -u user_name
서버에 접속할 때 사용되는 MySQL 사용자 이름
--verbose, -v
프로그램이 실행하는 것에 대한 정보를 자세히 출력
--version, -V
버전 정보를 출력
1-3. 예시
mysqlimport -u ebigdata --local --replace --ignore-lines=1 --fields-terminated-by=, --lines-terminated-by="\n" test.csv -p
-u 옵션 : ebigdata 유저를 사용함.
--local 옵션 : 로컬에 있는 파일(test.csv)을 가져옴
--replace 옵션 : 중복키값 존재시 나머지 내용을 대체함.
-ignore-lines=1 옵션 : 1번째 행(컬럼)을 무시함.
--fields-terminated-by=, 옵션 : ,를 통해 데이터를 구분함
--lines-terminated-by=“\n" 옵션 : “\n" 줄바꿈 문자를 통해 라인을 구분함
-p 옵션 : 사용자 암호를 입력 받음.
2. mysql 데이터 추출
2-1. 데이터 추출 방법
- mysql 명령어를 통해 .sql파일로 저장 가능함
mysql -h(host name) -u(user name) -p(password) db_name table_name > test.sql (명렁어를 실행한 위치에 파일 생성)
mysql -h(host name) -u(user name) -p(password) db_name < test.sql (test.sql 파일이 실행되어 테이블이 생성되고 데이터가 INSERT됨)
- mysql 명령어를 통해 csv, xls로 저장
mysql -h -u -p db_name "SELECT * FROM table_name" > test.csv
(csv 형식으로 현재위치에 파일 저장)
mysql -h -u -p db_name "SELECT * FROM table_name" > test.xls
(xls 형식으로 현재위치에 파일 저장)
2-2. 예시
mysql -p data_food_origin2 -e "SELECT * FROM food_origin2_deliver_trim"| sed 's/\t/","/g;s/^/"/;s/$/"/;' > food_origin2_deliver_trim.csv
sed 옵션
sed 기본형 : sed 's/찾는문자열/바꿀문자열/g' > 입력파일
- sed 's/\t/","/g;s/^/"/;s/$/"/;' 설명
\t을 ,로 대체, ^를 “로 대체, $를 ”로 대체함.
\t는 탭을 의미, ^는 해당라인의 첫칸을 의미함. $는 공백문자를 의미함.