CSVT 및 TSV 포맷
CSVT 및 TSV 포맷
ClickHouse는 CSV로부터 데이터를 가져오고 CSV로 내보내기를 지원해요. CSV 파일은 헤더 행, 커스텀 구분자, 이스케이프 기호 등 포맷 특성이 다양할 수 있기 때문에, ClickHouse는 각 경우를 효율적으로 처리하도록 포맷과 설정을 제공해요.
출처: 문서
본문
CSV 파일에서 데이터 가져오기
데이터를 가져오기 전에 관련 구조의 테이블을 만들어 볼게요.
CREATE TABLE sometable
(
`path` String,
`month` Date,
`hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)
CSV 파일에서 sometable 테이블로 데이터를 가져오려면 파일을 clickhouse-client로 바로 파이프할 수 있어요.
clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv
CSV 포맷 데이터를 수집한다는 걸 ClickHouse에 알려주기 위해 FORMAT CSV를 사용한다는 점을 기억하세요. 또는 FROM INFILE 절로 로컬 파일에서 데이터를 적재할 수도 있어요.
INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV
여기서도 FORMAT CSV 절을 써서 ClickHouse가 파일 포맷을 알 수 있게 해요. url() 함수로 URL에서, s3() 함수로 S3 파일에서 직접 데이터를 적재할 수도 있어요.
file()과 INFILE/OUTFILE에는 명시적인 포맷 설정을 생략할 수 있어요. 이 경우 ClickHouse가 파일 확장자에 따라 포맷을 자동으로 감지해요.
헤더가 있는 CSV 파일
CSV 파일에 헤더가 있다고 가정해 볼게요.
head data-small-headers.csv
"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
이 파일에서 데이터를 가져오려면 CSVWithNames 포맷을 쓸 수 있어요.
clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv
이 경우 ClickHouse는 파일에서 데이터를 가져올 때 첫 번째 행을 건너뛰어요.
버전 23.1부터 ClickHouse는 CSV 포맷을 사용할 때 CSV 파일의 헤더를 자동으로 감지하므로, CSVWithNames나 CSVWithNamesAndTypes를 꼭 쓸 필요는 없어요.
커스텀 구분자가 있는 CSV 파일
CSV 파일이 쉼표가 아닌 구분자를 쓴다면 format_csv_delimiter 옵션으로 관련 기호를 설정할 수 있어요.
SET format_csv_delimiter = ';'
이제 CSV 파일에서 가져올 때 쉼표 대신 ; 기호가 구분자로 쓰여요.
CSV 파일에서 라인 건너뛰기
때로는 CSV 파일에서 데이터를 가져올 때 특정 수의 라인을 건너뛰고 싶을 수 있어요. input_format_csv_skip_first_lines 옵션으로 할 수 있어요.
SET input_format_csv_skip_first_lines = 10
이 경우 CSV 파일에서 처음 열 줄을 건너뛰게 돼요.
SELECT count(*) FROM file('data-small.csv', CSV)
┌─count()─┐
│ 990 │
└─────────┘
이 file은 1k 행이 있는데, 처음 10개를 건너뛰도록 했으므로 ClickHouse는 990개만 적재했어요.
file() 함수를 쓸 때 ClickHouse Cloud에서는 파일이 있는 머신의 clickhouse client에서 명령을 실행해야 해요. 또 다른 방법은 clickhouse-local을 사용해 로컬에서 파일을 탐색하는 거예요.
CSV 파일에서 NULL 값 처리
NULL 값은 파일을 생성한 애플리케이션에 따라 다르게 인코딩될 수 있어요. 기본적으로 ClickHouse는 CSV에서 \N을 NULL 값으로 사용해요. 하지만 format_csv_null_representation 옵션으로 바꿀 수 있어요. 다음 CSV 파일이 있다고 가정해 볼게요.
> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70
이 파일에서 데이터를 적재하면 ClickHouse는 Nothing을 String으로 취급해요 (이것은 정확해요).
SELECT * FROM file('nulls.csv')
┌─c1──────┬─c2──────┐
│ Donald │ 90 │
│ Joe │ Nothing │
│ Nothing │ 70 │
└─────────┴─────────┘
ClickHouse가 Nothing을 NULL로 취급하길 원한다면 다음 옵션으로 정의할 수 있어요.
SET format_csv_null_representation = 'Nothing'
이제 기대하는 자리에 NULL이 생겨요.
SELECT * FROM file('nulls.csv')
┌─c1─────┬─c2───┐
│ Donald │ 90 │
│ Joe │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ │ 70 │
└────────┴──────┘
TSV (탭으로 구분) 파일
탭으로 구분된 데이터 포맷은 데이터 교환 포맷으로 널리 쓰여요. TSV 파일에서 ClickHouse로 데이터를 적재하려면 TabSeparated 포맷을 사용해요.
clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv
헤더가 있는 TSV 파일을 다루기 위한 TabSeparatedWithNames 포맷도 있어요. 그리고 CSV와 마찬가지로 input_format_tsv_skip_first_lines 옵션으로 처음 X 라인을 건너뛸 수 있어요.
Raw TSV
때로는 TSV 파일이 탭과 줄바꿈을 이스케이프하지 않은 채 저장되기도 해요. 그런 파일을 다루려면 TabSeparatedRaw를 써야 해요.
CSV로 내보내기
앞선 예시의 어떤 포맷이든 데이터 내보내기에도 쓸 수 있어요. 테이블(또는 쿼리)에서 CSV 포맷으로 데이터를 내보내려면 같은 FORMAT 절을 사용해요.
SELECT *
FROM sometable
LIMIT 5
FORMAT CSV
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86
CSV 파일에 헤더를 추가하려면 CSVWithNames 포맷을 사용해요.
SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames
"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86
내보낸 데이터를 CSV 파일로 저장하기
내보낸 데이터를 파일로 저장하려면 INTO…OUTFILE 절을 쓸 수 있어요.
SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames
36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)
ClickHouse가 36m 행을 CSV 파일로 저장하는 데 약 1초 걸렸다는 점을 눈여겨보세요.
커스텀 구분자로 CSV 내보내기
쉼표가 아닌 구분자를 쓰고 싶다면 format_csv_delimiter 설정 옵션을 사용할 수 있어요.
SET format_csv_delimiter = '|'
이제 ClickHouse는 CSV 포맷에서 |를 구분자로 사용해요.
SELECT *
FROM sometable
LIMIT 5
FORMAT CSV
"Akiba_Hebrew_Academy"|"2017-08-01"|241
"Aegithina_tiphia"|"2018-02-01"|34
"1971-72_Utah_Stars_season"|"2016-10-01"|1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8"|"2015-12-01"|73
"2016_Greater_Western_Sydney_Giants_season"|"2017-05-01"|86
Windows용 CSV 내보내기
CSV 파일이 Windows 환경에서 잘 동작하길 원한다면 output_format_csv_crlf_end_of_line 옵션을 켜는 걸 고려해 보세요. 그러면 줄바꿈으로 \n 대신 \r\n을 사용해요.
SET output_format_csv_crlf_end_of_line = 1;
CSV 파일의 스키마 추론
많은 경우 알 수 없는 CSV 파일을 다루게 되므로, 컬럼에 어떤 타입을 쓸지 탐색해야 해요. ClickHouse는 기본적으로 주어진 CSV 파일 분석을 바탕으로 데이터 포맷을 추측하려고 해요. 이것을 "스키마 추론(Schema Inference)"이라고 해요. 감지된 데이터 타입은 file() 함수와 함께 DESCRIBE 문을 사용해 탐색할 수 있어요.
DESCRIBE file('data-small.csv', CSV)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(Date) │ │ │ │ │ │
│ c3 │ Nullable(Int64) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
여기서 ClickHouse가 CSV 파일의 컬럼 타입을 효율적으로 추측했어요. ClickHouse가 추측하지 않게 하려면 다음 옵션으로 끌 수 있어요.
SET input_format_csv_use_best_effort_in_schema_inference = 0
이 경우 모든 컬럼 타입이 String으로 처리돼요.
명시적 컬럼 타입으로 CSV 내보내기·가져오기
ClickHouse는 CSVWithNamesAndTypes(및 다른 *WithNames 포맷 계열)로 데이터를 내보낼 때 컬럼 타입을 명시적으로 설정하는 것도 지원해요.
SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNamesAndTypes
"path","month","hits"
"String","Date","UInt32"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86
이 포맷은 두 개의 헤더 행(컬럼 이름 행과 컬럼 타입 행)을 포함해요. 덕분에 ClickHouse(및 다른 앱)는 그런 파일에서 데이터를 적재할 때 컬럼 타입을 식별할 수 있어요.
DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path │ String │ │ │ │ │ │
│ month │ Date │ │ │ │ │ │
│ hits │ UInt32 │ │ │ │ │ │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이제 ClickHouse는 추측하는 대신 (두 번째) 헤더 행을 바탕으로 컬럼 타입을 식별해요.
커스텀 구분자, 분리자, 이스케이프 규칙
복잡한 경우 텍스트 데이터가 아주 커스텀하게 포맷되어도 여전히 구조를 가질 수 있어요. ClickHouse에는 그런 경우를 위한 특별한 CustomSeparated 포맷이 있어요. 커스텀 이스케이프 규칙, 구분자, 라인 구분자, 시작·끝 기호를 설정할 수 있어요. 파일에 다음 데이터가 있다고 가정해 볼게요.
row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...
개별 행이 row()로 감싸지고, 라인은 ,로, 개별 값은 ;로 구분되는 걸 볼 수 있어요. 이 경우 다음 설정으로 이 파일에서 데이터를 읽을 수 있어요.
SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';
이제 커스텀 포맷된 파일에서 데이터를 적재할 수 있어요.
SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3
┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy │ 2017-08-01 │ 241 │
│ Aegithina_tiphia │ 2018-02-01 │ 34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │ 1 │
└───────────────────────────┴────────────┴─────┘
헤더를 올바르게 내보내고 가져오려면 CustomSeparatedWithNames도 쓸 수 있어요. 더 복잡한 경우를 다루려면 regex 및 템플릿 포맷을 살펴보세요.
대용량 CSV 파일 다루기
CSV 파일은 클 수 있는데, ClickHouse는 어떤 크기의 파일이든 효율적으로 처리해요. 큰 파일은 보통 압축된 상태로 제공되며, ClickHouse는 처리 전에 압축 해제할 필요 없이 이를 처리해요. insert 중에 COMPRESSION 절을 쓸 수 있어요.
INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV
COMPRESSION 절을 생략하면 ClickHouse는 여전히 파일 확장자를 바탕으로 압축을 추측하려 해요. 같은 방식을 내보내기에서 압축 포맷으로 직접 저장하는 데도 쓸 수 있어요.
SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV
이렇게 하면 압축된 data_csv.csv.gz 파일이 생성돼요.
다른 포맷
ClickHouse는 다양한 시나리오와 플랫폼을 다루기 위해 텍스트·바이너리 여러 포맷을 지원해요. 다음 문서에서 더 많은 포맷과 작업 방법을 살펴보세요.
- CSV 및 TSV 포맷
- Parquet
- JSON 포맷
- Regex 및 템플릿
- Native 및 바이너리 포맷
- SQL 포맷
또한 clickhouse-local을 확인해 보세요. ClickHouse 서버 없이 로컬/원격 파일을 다룰 수 있는 휴대형 풀기능 도구예요.