입력 데이터로부터의 자동 스키마 추론
입력 데이터로부터의 자동 스키마 추론 (Automatic schema inference from input data)
ClickHouse는 거의 모든 지원되는 입력 포맷에서 입력 데이터의 구조를 자동으로 파악할 수 있어요. 여기서는 스키마 추론이 언제 사용되고, 다양한 입력 포맷에서 어떻게 동작하며, 어떤 설정이 그것을 제어하는지 설명해 드릴게요.
출처: 문서
본문
ClickHouse는 거의 모든 지원되는 입력 포맷에서 입력 데이터의 구조를 자동으로 파악할 수 있어요. 이 문서는 스키마 추론이 언제 사용되는지, 다양한 입력 포맷에서 어떻게 동작하는지, 그리고 어떤 설정이 그것을 제어할 수 있는지 설명할게요.
사용 (Usage)
스키마 추론은 ClickHouse가 특정 데이터 포맷으로 데이터를 읽어야 하는데 구조를 모를 때 사용돼요.
테이블 함수 file, s3, url, hdfs, azureBlobStorage
이 테이블 함수들은 입력 데이터의 구조를 가진 선택적 인수 structure가 있어요. 이 인수를 지정하지 않거나 auto로 설정하면 구조가 데이터에서 추론돼요.
예시:
user_files 디렉터리에 JSONEachRow 포맷의 hobbies.jsonl 파일이 있다고 가정해 봐요:
{"id" : 1, "age" : 25, "name" : "Josh", "hobbies" : ["football", "cooking", "music"]}
{"id" : 2, "age" : 19, "name" : "Alan", "hobbies" : ["tennis", "art"]}
{"id" : 3, "age" : 32, "name" : "Lana", "hobbies" : ["fitness", "reading", "shopping"]}
{"id" : 4, "age" : 47, "name" : "Brayan", "hobbies" : ["movies", "skydiving"]}
ClickHouse는 구조를 지정하지 않아도 이 데이터를 읽을 수 있어요:
SELECT * FROM file('hobbies.jsonl')
┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│ 1 │ 25 │ Josh │ ['football','cooking','music'] │
│ 2 │ 19 │ Alan │ ['tennis','art'] │
│ 3 │ 32 │ Lana │ ['fitness','reading','shopping'] │
│ 4 │ 47 │ Brayan │ ['movies','skydiving'] │
└────┴─────┴────────┴──────────────────────────────────┘
참고: JSONEachRow 포맷은 파일 확장자 .jsonl에 의해 자동으로 결정됐어요.
DESCRIBE 쿼리로 자동 결정된 구조를 볼 수 있어요:
DESCRIBE file('hobbies.jsonl')
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ Nullable(Int64) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
테이블 엔진 File, S3, URL, HDFS, azureBlobStorage
CREATE TABLE 쿼리에서 열 목록이 지정되지 않으면 테이블의 구조가 데이터에서 자동으로 추론돼요.
예시:
hobbies.jsonl 파일을 사용해 볼게요. 이 파일의 데이터로 엔진 File의 테이블을 만들 수 있어요:
CREATE TABLE hobbies ENGINE=File(JSONEachRow, 'hobbies.jsonl')
Ok.
SELECT * FROM hobbies
┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│ 1 │ 25 │ Josh │ ['football','cooking','music'] │
│ 2 │ 19 │ Alan │ ['tennis','art'] │
│ 3 │ 32 │ Lana │ ['fitness','reading','shopping'] │
│ 4 │ 47 │ Brayan │ ['movies','skydiving'] │
└────┴─────┴────────┴──────────────────────────────────┘
DESCRIBE TABLE hobbies
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ Nullable(Int64) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
clickhouse-local
clickhouse-local에는 입력 데이터의 구조를 가진 선택적 파라미터 -S/--structure가 있어요. 이 파라미터를 지정하지 않거나 auto로 설정하면 구조가 데이터에서 추론돼요.
예시:
hobbies.jsonl 파일을 사용해 볼게요. clickhouse-local로 이 파일의 데이터를 조회할 수 있어요:
clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='DESCRIBE TABLE hobbies'
id Nullable(Int64)
age Nullable(Int64)
name Nullable(String)
hobbies Array(Nullable(String))
clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='SELECT * FROM hobbies'
1 25 Josh ['football','cooking','music']
2 19 Alan ['tennis','art']
3 32 Lana ['fitness','reading','shopping']
4 47 Brayan ['movies','skydiving']
삽입 테이블의 구조 사용 (Using structure from insertion table)
테이블 함수 file/s3/url/hdfs가 테이블에 데이터를 삽입하는 데 사용될 때,
데이터에서 구조를 추출하는 대신 삽입 테이블의 구조를 사용하는 옵션이 있어요.
스키마 추론은 시간이 걸릴 수 있으므로 삽입 성능을 개선할 수 있어요. 또한 테이블이 최적화된 스키마를 가지면 타입 간 변환이 수행되지 않으므로 유용해요.
이 동작을 제어하는 특별한 설정 use_structure_from_insertion_table_in_table_functions이 있어요. 3가지 가능한 값:
- 0 - 테이블 함수가 데이터에서 구조를 추출.
- 1 - 테이블 함수가 삽입 테이블의 구조를 사용.
- 2 - ClickHouse가 삽입 테이블의 구조를 사용할 수 있는지 자동 결정하거나 스키마 추론을 사용. 기본값.
예시 1:
다음 구조로 hobbies1 테이블을 만들어 볼게요:
CREATE TABLE hobbies1
(
`id` UInt64,
`age` LowCardinality(UInt8),
`name` String,
`hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY id;
그리고 hobbies.jsonl 파일에서 데이터를 삽입:
INSERT INTO hobbies1 SELECT * FROM file(hobbies.jsonl)
이 경우 파일의 모든 열이 변경 없이 테이블에 삽입되므로, ClickHouse는 스키마 추론 대신 삽입 테이블의 구조를 사용해요.
예시 2:
다음 구조로 hobbies2 테이블을 만들어 볼게요:
CREATE TABLE hobbies2
(
`id` UInt64,
`age` LowCardinality(UInt8),
`hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY id;
그리고 hobbies.jsonl 파일에서 데이터를 삽입:
INSERT INTO hobbies2 SELECT id, age, hobbies FROM file(hobbies.jsonl)
이 경우 SELECT 쿼리의 모든 열이 테이블에 존재하므로, ClickHouse는 삽입 테이블의 구조를 사용해요.
이것은 JSONEachRow, TSKV, Parquet 등 열의 일부만 읽는 것을 지원하는 입력 포맷에서만 동작한다는 점에 주의하세요(예: TSV 포맷에서는 동작하지 않아요).
예시 3:
다음 구조로 hobbies3 테이블을 만들어 볼게요:
CREATE TABLE hobbies3
(
`identifier` UInt64,
`age` LowCardinality(UInt8),
`hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY identifier;
그리고 hobbies.jsonl 파일에서 데이터를 삽입:
INSERT INTO hobbies3 SELECT id, age, hobbies FROM file(hobbies.jsonl)
이 경우 열 id가 SELECT 쿼리에서 사용되지만 테이블에는 이 열이 없어요(identifier라는 열이 있음). 따라서 ClickHouse는 삽입 테이블의 구조를 사용할 수 없고 스키마 추론이 사용돼요.
예시 4:
다음 구조로 hobbies4 테이블을 만들어 볼게요:
CREATE TABLE hobbies4
(
`id` UInt64,
`any_hobby` Nullable(String)
)
ENGINE = MergeTree
ORDER BY id;
그리고 hobbies.jsonl 파일에서 데이터를 삽입:
INSERT INTO hobbies4 SELECT id, empty(hobbies) ? NULL : hobbies[1] FROM file(hobbies.jsonl)
이 경우 SELECT 쿼리에서 열 hobbies에 대해 테이블에 삽입하기 위해 어떤 연산이 수행되므로, ClickHouse는 삽입 테이블의 구조를 사용할 수 없고 스키마 추론이 사용돼요.
스키마 추론 캐시 (Schema inference cache)
대부분의 입력 포맷에서 스키마 추론은 그 구조를 결정하기 위해 일부 데이터를 읽으며 이 과정은 시간이 걸릴 수 있어요. ClickHouse가 같은 파일에서 데이터를 읽을 때마다 같은 스키마를 추론하는 것을 방지하기 위해, 추론된 스키마가 캐시되고 같은 파일에 다시 접근할 때 ClickHouse는 캐시의 스키마를 사용해요.
이 캐시를 제어하는 특별한 설정들이 있어요:
schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure}- 해당 테이블 함수에 대한 캐시된 스키마의 최대 수. 기본값은4096. 이 설정들은 서버 설정에 설정해야 해요.schema_inference_use_cache_for_{file,s3,hdfs,url,azure}- 스키마 추론에 캐시 사용을 켜고 끌 수 있게 함. 이 설정들은 쿼리에서 사용할 수 있어요.
파일의 스키마는 데이터를 수정하거나 포맷 설정을 바꾸면 변경될 수 있어요. 이러한 이유로 스키마 추론 캐시는 파일 소스, 포맷 이름, 사용된 포맷 설정, 파일의 마지막 수정 시간으로 스키마를 식별해요.
참고: url 테이블 함수에서 접근하는 일부 파일은 마지막 수정 시간에 대한 정보를 포함하지 않을 수 있어요. 이 경우를 위해 특별한 설정 schema_inference_cache_require_modification_time_for_url이 있어요. 이 설정을 비활성화하면 그런 파일에 대해 마지막 수정 시간 없이 캐시의 스키마를 사용할 수 있어요.
또한 캐시의 모든 현재 스키마가 있는 시스템 테이블 schema_inference_cache와 모든 소스 또는 특정 소스에 대해 스키마 캐시를 정리할 수 있게 하는 시스템 쿼리 SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS]이 있어요.
예시:
s3에서 샘플 데이터 세트 github-2022.ndjson.gz의 구조를 추론해 보고 스키마 추론 캐시가 어떻게 동작하는지 볼게요:
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', NOSIGN)
┌─name───────┬─type─────────────────────────────────────────┐
│ type │ Nullable(String) │
│ actor │ Tuple( ↴│
│ │↳ avatar_url Nullable(String), ↴│
│ │↳ display_login Nullable(String), ↴│
│ │↳ id Nullable(Int64), ↴│
│ │↳ login Nullable(String), ↴│
│ │↳ url Nullable(String)) │
│ repo │ Tuple( ↴│
│ │↳ id Nullable(Int64), ↴│
│ │↳ name Nullable(String), ↴│
│ │↳ url Nullable(String)) │
│ created_at │ Nullable(String) │
│ payload │ Tuple( ↴│
│ │↳ action Nullable(String), ↴│
│ │↳ distinct_size Nullable(Int64), ↴│
│ │↳ pull_request Tuple( ↴│
│ │↳ author_association Nullable(String),↴│
│ │↳ base Tuple( ↴│
│ │↳ ref Nullable(String), ↴│
│ │↳ sha Nullable(String)), ↴│
│ │↳ head Tuple( ↴│
│ │↳ ref Nullable(String), ↴│
│ │↳ sha Nullable(String)), ↴│
│ │↳ number Nullable(Int64), ↴│
│ │↳ state Nullable(String), ↴│
│ │↳ title Nullable(String), ↴│
│ │↳ updated_at Nullable(String), ↴│
│ │↳ user Tuple( ↴│
│ │↳ login Nullable(String))), ↴│
│ │↳ ref Nullable(String), ↴│
│ │↳ ref_type Nullable(String), ↴│
│ │↳ size Nullable(Int64)) │
└────────────┴──────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.601 sec.
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', NOSIGN)
┌─name───────┬─type─────────────────────────────────────────┐
│ type │ Nullable(String) │
│ actor │ Tuple( ↴│
│ │↳ avatar_url Nullable(String), ↴│
│ │↳ display_login Nullable(String), ↴│
│ │↳ id Nullable(Int64), ↴│
│ │↳ login Nullable(String), ↴│
│ │↳ url Nullable(String)) │
│ repo │ Tuple( ↴│
│ │↳ id Nullable(Int64), ↴│
│ │↳ name Nullable(String), ↴│
│ │↳ url Nullable(String)) │
│ created_at │ Nullable(String) │
│ payload │ Tuple( ↴│
│ │↳ action Nullable(String), ↴│
│ │↳ distinct_size Nullable(Int64), ↴│
│ │↳ pull_request Tuple( ↴│
│ │↳ author_association Nullable(String),↴│
│ │↳ base Tuple( ↴│
│ │↳ ref Nullable(String), ↴│
│ │↳ sha Nullable(String)), ↴│
│ │↳ head Tuple( ↴│
│ │↳ ref Nullable(String), ↴│
│ │↳ sha Nullable(String)), ↴│
│ │↳ number Nullable(Int64), ↴│
│ │↳ state Nullable(String), ↴│
│ │↳ title Nullable(String), ↴│
│ │↳ updated_at Nullable(String), ↴│
│ │↳ user Tuple( ↴│
│ │↳ login Nullable(String))), ↴│
│ │↳ ref Nullable(String), ↴│
│ │↳ ref_type Nullable(String), ↴│
│ │↳ size Nullable(Int64)) │
└────────────┴──────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.059 sec.
보시다시피 두 번째 쿼리는 거의 즉시 성공했어요.
추론된 스키마에 영향을 줄 수 있는 일부 설정을 변경해 볼게요:
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', NOSIGN)
SETTINGS input_format_json_try_infer_named_tuples_from_objects=0, input_format_json_read_objects_as_strings = 1
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type │ Nullable(String) │ │ │ │ │ │
│ actor │ Nullable(String) │ │ │ │ │ │
│ repo │ Nullable(String) │ │ │ │ │ │
│ created_at │ Nullable(String) │ │ │ │ │ │
│ payload │ Nullable(String) │ │ │ │ │ │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
5 rows in set. Elapsed: 0.611 sec
보시다시피 추론된 스키마에 영향을 줄 수 있는 설정이 변경됐으므로 같은 파일에 대해 캐시의 스키마가 사용되지 않았어요.
system.schema_inference_cache 테이블의 내용을 확인해 볼게요:
SELECT schema, format, source FROM system.schema_inference_cache WHERE storage='S3'
┌─schema─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─format─┬─source───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type Nullable(String), actor Tuple(avatar_url Nullable(String), display_login Nullable(String), id Nullable(Int64), login Nullable(String), url Nullable(String)), repo Tuple(id Nullable(Int64), name Nullable(String), url Nullable(String)), created_at Nullable(String), payload Tuple(action Nullable(String), distinct_size Nullable(Int64), pull_request Tuple(author_association Nullable(String), base Tuple(ref Nullable(String), sha Nullable(String)), head Tuple(ref Nullable(String), sha Nullable(String)), number Nullable(Int64), state Nullable(String), title Nullable(String), updated_at Nullable(String), user Tuple(login Nullable(String))), ref Nullable(String), ref_type Nullable(String), size Nullable(Int64)) │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
│ type Nullable(String), actor Nullable(String), repo Nullable(String), created_at Nullable(String), payload Nullable(String) │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘
보시다시피 같은 파일에 대해 두 개의 서로 다른 스키마가 있어요.
시스템 쿼리로 스키마 캐시를 정리할 수 있어요:
SYSTEM CLEAR SCHEMA CACHE FOR S3
Ok.
SELECT count() FROM system.schema_inference_cache WHERE storage='S3'
┌─count()─┐
│ 0 │
└─────────┘
텍스트 포맷 (Text formats)
텍스트 포맷의 경우 ClickHouse는 데이터를 한 줄씩 읽고, 포맷에 따라 열 값을 추출한 다음,
재귀 파서와 휴리스틱을 사용해 각 값에 대한 타입을 결정해요. 스키마 추론에서 데이터에서 읽는 최대 행/바이트 수는
설정 input_format_max_rows_to_read_for_schema_inference(기본 25000)과 input_format_max_bytes_to_read_for_schema_inference(기본 32Mb)로 제어돼요.
기본적으로 추론된 모든 타입은 Nullable이지만, schema_inference_make_columns_nullable을 설정해 변경할 수 있어요(설정 섹션의 예시 참조).
JSON 포맷 (JSON formats)
JSON 포맷에서 ClickHouse는 JSON 명세에 따라 값을 파싱한 다음 그것에 가장 적합한 데이터 타입을 찾으려 해요.
어떻게 동작하는지, 어떤 타입이 추론될 수 있고, JSON 포맷에서 어떤 특정 설정을 사용할 수 있는지 볼게요.
예시
여기서부터는 예시에서 format 테이블 함수가 사용돼요.
정수, 부동소수점, 부울, 문자열:
DESC format(JSONEachRow, '{"int" : 42, "float" : 42.42, "string" : "Hello, World!"}');
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int │ Nullable(Int64) │ │ │ │ │ │
│ float │ Nullable(Float64) │ │ │ │ │ │
│ bool │ Nullable(Bool) │ │ │ │ │ │
│ string │ Nullable(String) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
날짜, 날짜시간:
DESC format(JSONEachRow, '{"date" : "2022-01-01", "datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}')
┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(Date) │ │ │ │ │ │
│ datetime │ Nullable(DateTime) │ │ │ │ │ │
│ datetime64 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열:
DESC format(JSONEachRow, '{"arr" : [1, 2, 3], "nested_arrays" : [[1, 2, 3], [4, 5, 6], []]}')
┌─name──────────┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Nullable(Int64)) │ │ │ │ │ │
│ nested_arrays │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└───────────────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열이 null을 포함하면 ClickHouse는 다른 배열 요소의 타입을 사용해요:
DESC format(JSONEachRow, '{"arr" : [null, 42, null]}')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열이 다른 타입의 값을 포함하고 설정 input_format_json_infer_array_of_dynamic_from_array_of_different_types가 활성화되어 있으면(기본적으로 활성화), 타입이 Array(Dynamic)이 돼요:
SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=1;
DESC format(JSONEachRow, '{"arr" : [42, "hello", [1, 2, 3]]}');
┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Dynamic) │ │ │ │ │ │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
명명된 튜플 (Named tuples):
설정 input_format_json_try_infer_named_tuples_from_objects가 활성화되면, 스키마 추론 중 ClickHouse는 JSON 객체에서 명명된 Tuple을 추론하려 시도해요.
결과 명명된 Tuple은 샘플 데이터의 모든 해당 JSON 객체의 모든 요소를 포함해요.
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
무명 튜플 (Unnamed Tuples):
설정 input_format_json_infer_array_of_dynamic_from_array_of_different_types가 비활성화되면, JSON 포맷에서 다른 타입의 요소가 있는 배열을 무명 튜플로 취급해요.
SET input_format_json_infer_array_of_dynamic_from_array_of_different_types = 0;
DESC format(JSONEachRow, '{"tuple" : [1, "Hello, World!", [1, 2, 3]]}')
┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
일부 값이 null이거나 비어 있으면 다른 행의 해당 값 타입을 사용해요:
SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=0;
DESC format(JSONEachRow, $$
{"tuple" : [1, null, null]}
{"tuple" : [null, "Hello, World!", []]}
{"tuple" : [null, null, [1, 2, 3]]}
$$)
┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
맵 (Maps):
JSON에서 같은 타입의 값을 가진 객체를 Map 타입으로 읽을 수 있어요.
참고: 이것은 설정 input_format_json_read_objects_as_strings와 input_format_json_try_infer_named_tuples_from_objects가 비활성화된 경우에만 동작해요.
SET input_format_json_read_objects_as_strings = 0, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, '{"map" : {"key1" : 42, "key2" : 24, "key3" : 4}}')
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ map │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
중첩 복합 타입 (Nested complex types):
DESC format(JSONEachRow, '{"value" : [[[42, 24], []], {"key1" : 42, "key2" : 24}]}')
┌─name──┬─type─────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Tuple(Array(Array(Nullable(String))), Tuple(key1 Nullable(Int64), key2 Nullable(Int64))) │ │ │ │ │ │
└───────┴──────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
데이터에 null/빈 객체/빈 배열만 있어서 ClickHouse가 일부 키에 대한 타입을 결정할 수 없으면, 설정 input_format_json_infer_incomplete_types_as_strings가 활성화된 경우 타입 String이 사용되고, 그렇지 않으면 예외가 발생해요:
DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 1;
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr │ Array(Nullable(String)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 0;
Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'arr' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...
JSON 설정 (JSON settings)
이 설정을 활성화하면 문자열 값에서 숫자를 추론할 수 있어요.
이 설정은 기본적으로 비활성화돼요.
예시:
SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(JSONEachRow, $$
{"value" : "42"}
{"value" : "424242424242"}
$$)
┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │ │ │ │ │ │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이 설정을 활성화하면 JSON 객체에서 명명된 Tuple을 추론할 수 있어요. 결과 명명된 Tuple은 샘플 데이터의 모든 해당 JSON 객체의 모든 요소를 포함해요. JSON 데이터가 희소하지 않아 데이터 샘플이 가능한 모든 객체 키를 포함할 때 유용할 수 있어요.
이 설정은 기본적으로 활성화돼요.
예시
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"array" : [{"a" : 42, "b" : "Hello"}, {}, {"c" : [1,2,3]}, {"d" : "2020-01-01"}]}')
┌─name──┬─type────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ array │ Array(Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Nullable(Date))) │ │ │ │ │ │
└───────┴─────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이 설정을 활성화하면 JSON 객체에서 명명된 튜플 추론 중(input_format_json_try_infer_named_tuples_from_objects가 활성화되어 있을 때) 모호한 경로에 대해 예외 대신 String 타입을 사용할 수 있어요.
모호한 경로가 있어도 JSON 객체를 명명된 Tuple로 읽을 수 있게 해 줘요.
기본적으로 비활성화돼요.
예시
설정 비활성화 상태:
SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 0;
DESC format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');
Code: 636. DB::Exception: The table structure cannot be extracted from a JSONEachRow format file. Error:
Code: 117. DB::Exception: JSON objects have ambiguous data: in some objects path 'a' has type 'Int64' and in some - 'Tuple(b String)'. You can enable setting input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects to use String type for path 'a'. (INCORRECT_DATA) (version 24.3.1.1).
You can specify the structure manually. (CANNOT_EXTRACT_TABLE_STRUCTURE)
설정 활성화 상태:
SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : "a" : 42}, {"obj" : {"a" : {"b" : "Hello"}}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Nullable(String)) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj─────────────────┐
│ ('42') │
│ ('{"b" : "Hello"}') │
└─────────────────────┘
이 설정을 활성화하면 중첩된 JSON 객체를 문자열로 읽을 수 있어요. 이 설정은 JSON 객체 타입을 사용하지 않고 중첩 JSON 객체를 읽는 데 사용할 수 있어요.
이 설정은 기본적으로 활성화돼요.
참고: 이 설정을 활성화하면 input_format_json_try_infer_named_tuples_from_objects가 비활성화된 경우에만 효과가 있어요.
SET input_format_json_read_objects_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, $$
{"obj" : {"key1" : 42, "key2" : [1,2,3,4]}}
{"obj" : {"key3" : {"nested_key" : 1}}}
$$)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이 설정을 활성화하면 숫자 값을 문자열로 읽을 수 있어요.
이 설정은 기본적으로 활성화돼요.
예시
SET input_format_json_read_numbers_as_strings = 1;
DESC format(JSONEachRow, $$
{"value" : 1055}
{"value" : "unknown"}
$$)
┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │ │ │ │ │ │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이 설정을 활성화하면 Bool 값을 숫자로 읽을 수 있어요.
이 설정은 기본적으로 활성화돼요.
예시:
SET input_format_json_read_bools_as_numbers = 1;
DESC format(JSONEachRow, $$
{"value" : true}
{"value" : 42}
$$)
┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │ │ │ │ │ │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이 설정을 활성화하면 Bool 값을 문자열로 읽을 수 있어요.
이 설정은 기본적으로 활성화돼요.
예시:
SET input_format_json_read_bools_as_strings = 1;
DESC format(JSONEachRow, $$
{"value" : true}
{"value" : "Hello, World"}
$$)
┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │ │ │ │ │ │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
이 설정을 활성화하면 JSON 배열 값을 문자열로 읽을 수 있어요.
이 설정은 기본적으로 활성화돼요.
예시
SET input_format_json_read_arrays_as_strings = 1;
SELECT arr, toTypeName(arr), JSONExtractArrayRaw(arr)[3] from format(JSONEachRow, 'arr String', '{"arr" : [1, "Hello", [1,2,3]]}');
┌─arr───────────────────┬─toTypeName(arr)─┬─arrayElement(JSONExtractArrayRaw(arr), 3)─┐
│ [1, "Hello", [1,2,3]] │ String │ [1,2,3] │
└───────────────────────┴─────────────────┴───────────────────────────────────────────┘
이 설정을 활성화하면 스키마 추론 중 데이터 샘플에 Null/{}/[]만 포함된 JSON 키에 String 타입을 사용할 수 있어요.
JSON 포맷에서 모든 관련 설정(모두 기본적으로 활성화)이 활성화되면 어떤 값이든 String으로 읽을 수 있으며, 타입을 알 수 없는 키에 String 타입을 사용함으로써 추론 중
Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps 같은 오류를 피할 수 있어요.
예시:
SET input_format_json_infer_incomplete_types_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 1;
DESCRIBE format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj │ Tuple(a Array(Nullable(Int64)), b Nullable(String), c Nullable(String), d Nullable(String), e Array(Nullable(String))) │ │ │ │ │ │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj────────────────────────────┐
│ ([1,2,3],'hello',NULL,'{}',[]) │
└────────────────────────────────┘
CSV
CSV 포맷에서 ClickHouse는 구분자에 따라 행에서 열 값을 추출해요. ClickHouse는 숫자와 문자열을 제외한 모든 타입이 큰따옴표로 묶이기를 기대해요. 값이 큰따옴표로 묶여 있으면 ClickHouse는 따옴표 안의 데이터를 재귀 파서로 파싱하려 시도한 다음 가장 적합한 데이터 타입을 찾으려 해요. 값이 큰따옴표로 묶여 있지 않으면 ClickHouse는 그것을 숫자로 파싱하려 하고, 값이 숫자가 아니면 문자열로 취급해요.
ClickHouse가 일부 파서와 휴리스틱으로 복합 타입을 결정하려 시도하는 것을 원하지 않으면 설정 input_format_csv_use_best_effort_in_schema_inference를 비활성화할 수 있고
그러면 ClickHouse가 모든 열을 문자열로 취급해요.
설정 input_format_csv_detect_header가 활성화되면 ClickHouse는 스키마 추론 중 열 이름(및 어쩌면 타입)이 있는 헤더를 감지하려 시도해요. 이 설정은 기본적으로 활성화돼요.
예시:
정수, 부동소수점, 부울, 문자열:
DESC format(CSV, '42,42.42,true,"Hello,World!"')
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
│ c3 │ Nullable(Bool) │ │ │ │ │ │
│ c4 │ Nullable(String) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
따옴표 없는 문자열:
DESC format(CSV, 'Hello world!,World hello!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
날짜, 날짜시간:
DESC format(CSV, '"2020-01-01","2020-01-01 00:00:00","2022-01-01 00:00:00.000"')
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Date) │ │ │ │ │ │
│ c2 │ Nullable(DateTime) │ │ │ │ │ │
│ c3 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열:
DESC format(CSV, '"[1,2,3]","[[1, 2], [], [3, 4]]"')
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(CSV, $$"['Hello', 'world']","[['Abc', 'Def'], []]"$$)
┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(String)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(String))) │ │ │ │ │ │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열에 null이 포함되면 ClickHouse는 다른 배열 요소의 타입을 사용해요:
DESC format(CSV, '"[NULL, 42, NULL]"')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
맵:
DESC format(CSV, $$"{'key1' : 42, 'key2' : 24}"$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
중첩 배열과 맵:
DESC format(CSV, $$"[{'key1' : [[42, 42], []], 'key2' : [[null], [42]]}]"$$)
┌─name─┬─type──────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Map(String, Array(Array(Nullable(Int64))))) │ │ │ │ │ │
└──────┴───────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
데이터에 null만 있어서 ClickHouse가 따옴표 안의 타입을 결정할 수 없으면 문자열로 취급해요:
DESC format(CSV, '"[NULL, NULL]"')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
설정 input_format_csv_use_best_effort_in_schema_inference 비활성화 예시:
SET input_format_csv_use_best_effort_in_schema_inference = 0
DESC format(CSV, '"[1,2,3]",42.42,Hello World!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
헤더 자동 감지 예시(input_format_csv_detect_header 활성화 시):
이름만:
SELECT * FROM format(CSV,
$$"number","string","array"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)
┌─number─┬─string─┬─array───┐
│ 42 │ Hello │ [1,2,3] │
│ 43 │ World │ [4,5,6] │
└────────┴────────┴─────────┘
이름과 타입:
DESC format(CSV,
$$"number","string","array"
"UInt32","String","Array(UInt16)"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)
┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32 │ │ │ │ │ │
│ string │ String │ │ │ │ │ │
│ array │ Array(UInt16) │ │ │ │ │ │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
헤더는 String이 아닌 타입의 열이 하나 이상 있을 때만 감지될 수 있다는 점에 주의하세요. 모든 열이 String 타입이면 헤더가 감지되지 않아요:
SELECT * FROM format(CSV,
$$"first_column","second_column"
"Hello","World"
"World","Hello"
$$)
┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello │ World │
│ World │ Hello │
└──────────────┴───────────────┘
CSV 설정 (CSV settings)
이 설정을 활성화하면 문자열 값에서 숫자를 추론할 수 있어요.
이 설정은 기본적으로 비활성화돼요.
예시:
SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(CSV, '42,42.42');
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
TSV/TSKV
TSV/TSKV 포맷에서 ClickHouse는 표 구분자에 따라 행에서 열 값을 추출한 다음, 재귀 파서를 사용해 추출된 값을 파싱해 가장 적합한 타입을 결정해요. 타입을 결정할 수 없으면 ClickHouse는 그 값을 문자열로 취급해요.
ClickHouse가 일부 파서와 휴리스틱으로 복합 타입을 결정하려 시도하는 것을 원하지 않으면 설정 input_format_tsv_use_best_effort_in_schema_inference를 비활성화할 수 있고
그러면 ClickHouse가 모든 열을 문자열로 취급해요.
설정 input_format_tsv_detect_header가 활성화되면 ClickHouse는 스키마 추론 중 열 이름(및 어쩌면 타입)이 있는 헤더를 감지하려 시도해요. 이 설정은 기본적으로 활성화돼요.
예시:
정수, 부동소수점, 부울, 문자열:
DESC format(TSV, '42 42.42 true Hello,World!')
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
│ c3 │ Nullable(Bool) │ │ │ │ │ │
│ c4 │ Nullable(String) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(TSKV, 'int=42 float=42.42 bool=true string=Hello,World!\n')
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int │ Nullable(Int64) │ │ │ │ │ │
│ float │ Nullable(Float64) │ │ │ │ │ │
│ bool │ Nullable(Bool) │ │ │ │ │ │
│ string │ Nullable(String) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
날짜, 날짜시간:
DESC format(TSV, '2020-01-01 2020-01-01 00:00:00 2022-01-01 00:00:00.000')
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Date) │ │ │ │ │ │
│ c2 │ Nullable(DateTime) │ │ │ │ │ │
│ c3 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열:
DESC format(TSV, '[1,2,3] [[1, 2], [], [3, 4]]')
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(TSV, '[''Hello'', ''world''] [[''Abc'', ''Def''], []]')
┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(String)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(String))) │ │ │ │ │ │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열에 null이 포함되면 ClickHouse는 다른 배열 요소의 타입을 사용해요:
DESC format(TSV, '[NULL, 42, NULL]')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
튜플:
DESC format(TSV, $$(42, 'Hello, world!')$$)
┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Tuple(Nullable(Int64), Nullable(String)) │ │ │ │ │ │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
맵:
DESC format(TSV, $${'key1' : 42, 'key2' : 24}$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
중첩 배열, 튜플 및 맵:
DESC format(TSV, $$[{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}]$$)
┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │ │ │ │ │ │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
데이터에 null만 있어서 ClickHouse가 타입을 결정할 수 없으면 문자열로 취급해요:
DESC format(TSV, '[NULL, NULL]')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
설정 input_format_tsv_use_best_effort_in_schema_inference 비활성화 예시:
SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3] 42.42 Hello World!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
헤더 자동 감지 예시(input_format_tsv_detect_header 활성화 시):
이름만:
SELECT * FROM format(TSV,
$$number string array
42 Hello [1, 2, 3]
43 World [4, 5, 6]
$$);
┌─number─┬─string─┬─array───┐
│ 42 │ Hello │ [1,2,3] │
│ 43 │ World │ [4,5,6] │
└────────┴────────┴─────────┘
이름과 타입:
DESC format(TSV,
$$number string array
UInt32 String Array(UInt16)
42 Hello [1, 2, 3]
43 World [4, 5, 6]
$$)
┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32 │ │ │ │ │ │
│ string │ String │ │ │ │ │ │
│ array │ Array(UInt16) │ │ │ │ │ │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
헤더는 String이 아닌 타입의 열이 하나 이상 있을 때만 감지될 수 있다는 점에 주의하세요. 모든 열이 String 타입이면 헤더가 감지되지 않아요:
SELECT * FROM format(TSV,
$$first_column second_column
Hello World
World Hello
$$)
┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello │ World │
│ World │ Hello │
└──────────────┴───────────────┘
Values
Values 포맷에서 ClickHouse는 행에서 열 값을 추출한 다음, 리터럴이 파싱되는 것과 비슷하게 재귀 파서를 사용해 파싱해요.
예시:
정수, 부동소수점, 부울, 문자열:
DESC format(Values, $$(42, 42.42, true, 'Hello,World!')$$)
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(Float64) │ │ │ │ │ │
│ c3 │ Nullable(Bool) │ │ │ │ │ │
│ c4 │ Nullable(String) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
날짜, 날짜시간:
DESC format(Values, $$('2020-01-01', '2020-01-01 00:00:00', '2022-01-01 00:00:00.000')$$)
┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Date) │ │ │ │ │ │
│ c2 │ Nullable(DateTime) │ │ │ │ │ │
│ c3 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열:
DESC format(Values, '([1,2,3], [[1, 2], [], [3, 4]])')
┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
│ c2 │ Array(Array(Nullable(Int64))) │ │ │ │ │ │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
배열에 null이 포함되면 ClickHouse는 다른 배열 요소의 타입을 사용해요:
DESC format(Values, '([NULL, 42, NULL])')
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
튜플:
DESC format(Values, $$((42, 'Hello, world!'))$$)
┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Tuple(Nullable(Int64), Nullable(String)) │ │ │ │ │ │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
맵:
DESC format(Values, $$({'key1' : 42, 'key2' : 24})$$)
┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Map(String, Nullable(Int64)) │ │ │ │ │ │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
중첩 배열, 튜플 및 맵:
DESC format(Values, $$([{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}])$$)
┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │ │ │ │ │ │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
데이터에 null만 있어서 ClickHouse가 타입을 결정할 수 없으면 예외가 발생해요:
DESC format(Values, '([NULL, NULL])')
Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'c1' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...
설정 input_format_tsv_use_best_effort_in_schema_inference 비활성화 예시:
SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3] 42.42 Hello World!')
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(String) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
CustomSeparated
CustomSeparated 포맷에서 ClickHouse는 먼저 지정된 구분자에 따라 행에서 모든 열 값을 추출한 다음, 이스케이프 규칙에 따라 각 값에 대한 데이터 타입을 추론하려 시도해요.
설정 input_format_custom_detect_header가 활성화되면 ClickHouse는 스키마 추론 중 열 이름(및 어쩌면 타입)이 있는 헤더를 감지하려 시도해요. 이 설정은 기본적으로 활성화돼요.
예시
SET format_custom_row_before_delimiter = '<row_before_delimiter>',
format_custom_row_after_delimiter = '<row_after_delimiter>\n',
format_custom_row_between_delimiter = '<row_between_delimiter>\n',
format_custom_result_before_delimiter = '<result_before_delimiter>\n',
format_custom_result_after_delimiter = '<result_after_delimiter>\n',
format_custom_field_delimiter = '<field_delimiter>',
format_custom_escaping_rule = 'Quoted'
DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Float64) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
헤더 자동 감지 예시(input_format_custom_detect_header 활성화 시):
SET format_custom_row_before_delimiter = '<row_before_delimiter>',
format_custom_row_after_delimiter = '<row_after_delimiter>\n',
format_custom_row_between_delimiter = '<row_between_delimiter>\n',
format_custom_result_before_delimiter = '<result_before_delimiter>\n',
format_custom_result_after_delimiter = '<result_after_delimiter>\n',
format_custom_field_delimiter = '<field_delimiter>',
format_custom_escaping_rule = 'Quoted'
DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>'number'<field_delimiter>'string'<field_delimiter>'array'<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)
┌─number─┬─string────────┬─array──────┐
│ 42.42 │ Some string 1 │ [1,NULL,3] │
│ ᴺᵁᴸᴸ │ Some string 3 │ [1,2,NULL] │
└────────┴───────────────┴────────────┘
Template
Template 포맷에서 ClickHouse는 먼저 지정된 템플릿에 따라 행에서 모든 열 값을 추출한 다음, 각 값에 대한 데이터 타입을 이스케이프 규칙에 따라 추론하려 시도해요.
예시
다음 내용의 resultset 파일이 있다고 가정해 봐요:
<result_before_delimiter>
${data}<result_after_delimiter>
다음 내용의 row_format 파일이 있다고 가정해 봐요:
<row_before_delimiter>${column_1:CSV}<field_delimiter_1>${column_2:Quoted}<field_delimiter_2>${column_3:JSON}<row_after_delimiter>
그러면 다음 쿼리를 만들 수 있어요:
SET format_template_rows_between_delimiter = '<row_between_delimiter>\n',
format_template_row = 'row_format',
format_template_resultset = 'resultset_format'
DESC format(Template, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter_1>'Some string 1'<field_delimiter_2>[1, null, 2]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>\N<field_delimiter_1>'Some string 3'<field_delimiter_2>[1, 2, null]<row_after_delimiter>
<result_after_delimiter>
$$)
┌─name─────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ column_1 │ Nullable(Float64) │ │ │ │ │ │
│ column_2 │ Nullable(String) │ │ │ │ │ │
│ column_3 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Regexp
Template과 유사하게, Regexp 포맷에서 ClickHouse는 먼저 지정된 정규 표현식에 따라 행에서 모든 열 값을 추출한 다음, 지정된 이스케이프 규칙에 따라 각 값의 데이터 타입을 추론하려 시도해요.
예시
SET format_regexp = '^Line: value_1=(.+?), value_2=(.+?), value_3=(.+?)',
format_regexp_escaping_rule = 'CSV'
DESC format(Regexp, $$Line: value_1=42, value_2="Some string 1", value_3="[1, NULL, 3]"
Line: value_1=2, value_2="Some string 2", value_3="[4, 5, NULL]"$$)
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Int64) │ │ │ │ │ │
│ c2 │ Nullable(String) │ │ │ │ │ │
│ c3 │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
텍스트 포맷용 설정 (Settings for text formats)
input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference
이 설정들은 스키마 추론 중에 읽을 데이터 양을 제어해요. 읽는 행/바이트가 많을수록 스키마 추론에 더 많은 시간을 쓰지만, (데이터에 null이 많을 때 특히) 타입을 올바르게 결정할 가능성이 더 커져요.
기본값:
input_format_max_rows_to_read_for_schema_inference의 경우25000.input_format_max_bytes_to_read_for_schema_inference의 경우33554432(32 Mb).
column_names_for_schema_inference
명시적 열 이름이 없는 포맷의 스키마 추론에 사용할 열 이름 목록. 지정된 이름이 기본 c1,c2,c3,... 대신 사용돼요. 형식: column1,column2,column3,....
예시
DESC format(TSV, 'Hello, World! 42 [1, 2, 3]') settings column_names_for_schema_inference = 'str,int,arr'
┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ str │ Nullable(String) │ │ │ │ │ │
│ int │ Nullable(Int64) │ │ │ │ │ │
│ arr │ Array(Nullable(Int64)) │ │ │ │ │ │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
schema_inference_hints
자동 결정된 타입 대신 스키마 추론에 사용할 열 이름과 타입 목록. 형식: 'column_name1 column_type1, column_name2 column_type2, …'. 이 설정은 자동으로 결정할 수 없는 열의 타입을 지정하거나 스키마를 최적화하는 데 사용할 수 있어요.
예시
DESC format(JSONEachRow, '{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}') SETTINGS schema_inference_hints = 'age LowCardinality(UInt8), status Nullable(String)', allow_suspicious_low_cardinality_types=1
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ LowCardinality(UInt8) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ status │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
schema_inference_make_columns_nullable
nullability 정보가 없는 포맷의 스키마 추론에서 추론된 타입을 Nullable로 만들지 제어. 가능한 값:
- 0 - 추론된 타입은 절대
Nullable이 되지 않음, - 1 - 모든 추론된 타입이
Nullable이 됨, - 2 또는 'auto' - 텍스트 포맷의 경우 추론된 타입은 열이 스키마 추론 중 파싱되는 샘플에
NULL을 포함하는 경우에만Nullable이 됨; 강타입 포맷(Parquet, ORC, Arrow)의 경우 nullability 정보는 파일 메타데이터에서 가져옴, - 3 - 텍스트 포맷의 경우
Nullable을 사용; 강타입 포맷의 경우 파일 메타데이터 사용.
기본값: 3.
예시
SET schema_inference_make_columns_nullable = 1;
DESC format(JSONEachRow, $$
{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
{"id" : 2, "age" : 19, "name" : "Alan", "status" : "married", "hobbies" : ["tennis", "art"]}
$$)
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(Int64) │ │ │ │ │ │
│ age │ Nullable(Int64) │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ status │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(Nullable(String)) │ │ │ │ │ │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET schema_inference_make_columns_nullable = 'auto';
DESC format(JSONEachRow, $$
{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
{"id" : 2, "age" : 19, "name" : "Alan", "status" : "married", "hobbies" : ["tennis", "art"]}
$$)
┌─name────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ age │ Int64 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ status │ Nullable(String) │ │ │ │ │ │
│ hobbies │ Array(String) │ │ │ │ │ │
└─────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET schema_inference_make_columns_nullable = 0;
DESC format(JSONEachRow, $$
{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
{"id" : 2, "age" : 19, "name" : "Alan", "status" : "married", "hobbies" : ["tennis", "art"]}
$$)
┌─name────┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ age │ Int64 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ status │ String │ │ │ │ │ │
│ hobbies │ Array(String) │ │ │ │ │ │
└─────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
input_format_try_infer_integers
참고: 이 설정은 JSON 데이터 타입에는 적용되지 않아요.
활성화되면 ClickHouse는 텍스트 포맷의 스키마 추론에서 부동소수점 대신 정수를 추론하려 시도해요.
열의 모든 숫자가 샘플 데이터에서 정수이면 결과 타입은 Int64이고, 숫자 중 하나라도 부동소수점이면 결과 타입은 Float64예요.
샘플 데이터에 정수만 있고 적어도 하나의 정수가 양수로 Int64를 넘치면 ClickHouse는 UInt64를 추론해요.
기본적으로 활성화돼요.
예시
SET input_format_try_infer_integers = 0
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 2}
$$)
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_try_infer_integers = 1
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 2}
$$)
┌─name───┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Int64) │ │ │ │ │ │
└────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 18446744073709551615}
$$)
┌─name───┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(UInt64) │ │ │ │ │ │
└────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"number" : 1}
{"number" : 2.2}
$$)
┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │ │ │ │ │ │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
input_format_try_infer_datetimes
활성화되면 ClickHouse는 텍스트 포맷의 스키마 추론에서 문자열 필드에서 타입 DateTime 또는 DateTime64를 추론하려 시도해요.
열의 모든 필드가 샘플 데이터에서 datetime으로 성공적으로 파싱되면 결과 타입은 DateTime 또는 DateTime64(9)(datetime 중 하나에 분수부가 있으면)이고,
하나라도 datetime으로 파싱되지 않으면 결과 타입은 String이에요.
기본적으로 활성화돼요.
예시
SET input_format_try_infer_datetimes = 0;
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
$$)
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(String) │ │ │ │ │ │
│ datetime64 │ Nullable(String) │ │ │ │ │ │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_try_infer_datetimes = 1;
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
$$)
┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(DateTime) │ │ │ │ │ │
│ datetime64 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "unknown", "datetime64" : "unknown"}
$$)
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(String) │ │ │ │ │ │
│ datetime64 │ Nullable(String) │ │ │ │ │ │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
input_format_try_infer_datetimes_only_datetime64
활성화되면 input_format_try_infer_datetimes가 활성화되어 있을 때 datetime 값에 분수부가 없더라도 ClickHouse는 항상 DateTime64(9)를 추론해요.
기본적으로 비활성화돼요.
예시
SET input_format_try_infer_datetimes = 1;
SET input_format_try_infer_datetimes_only_datetime64 = 1;
DESC format(JSONEachRow, $$
{"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
{"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
$$)
┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime │ Nullable(DateTime64(9)) │ │ │ │ │ │
│ datetime64 │ Nullable(DateTime64(9)) │ │ │ │ │ │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
참고: 스키마 추론 중 datetime 파싱은 설정 date_time_input_format을 존중해요.
input_format_try_infer_dates
활성화되면 ClickHouse는 텍스트 포맷의 스키마 추론에서 문자열 필드에서 타입 Date를 추론하려 시도해요.
열의 모든 필드가 샘플 데이터에서 date로 성공적으로 파싱되면 결과 타입은 Date이고,
하나라도 date로 파싱되지 않으면 결과 타입은 String이에요.
기본적으로 활성화돼요.
예시
SET input_format_try_infer_datetimes = 0, input_format_try_infer_dates = 0
DESC format(JSONEachRow, $$
{"date" : "2021-01-01"}
{"date" : "2022-01-01"}
$$)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SET input_format_try_infer_dates = 1
DESC format(JSONEachRow, $$
{"date" : "2021-01-01"}
{"date" : "2022-01-01"}
$$)
┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(Date) │ │ │ │ │ │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
DESC format(JSONEachRow, $$
{"date" : "2021-01-01"}
{"date" : "unknown"}
$$)
┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │ │ │ │ │ │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
input_format_try_infer_exponent_floats
활성화되면 ClickHouse는 텍스트 포맷(지수 형태의 숫자가 항상 추론되는 JSON 제외)에서 지수 형태의 부동소수점을 추론하려 시도해요.
기본적으로 비활성화돼요.
예시
SET input_format_try_infer_exponent_floats = 1;
DESC format(CSV,
$$1.1E10
2.3e-12
42E00
$$)
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1 │ Nullable(Float64) │ │ │ │ │ │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
자기 설명 포맷 (Self describing formats)
자기 설명 포맷은 데이터 자체에 데이터 구조에 대한 정보를 담고 있어요. 설명이 있는 어떤 헤더, 바이너리 타입 트리 또는 어떤 종류의 테이블일 수 있어요. 그런 포맷의 파일에서 스키마를 자동으로 추론하려면 ClickHouse는 타입에 대한 정보를 담고 있는 데이터의 일부를 읽고 그것을 ClickHouse 테이블의 스키마로 변환해요.
-WithNamesAndTypes 접미사가 있는 포맷 (Formats with -WithNamesAndTypes suffix)
ClickHouse는 접미사 -WithNamesAndTypes가 있는 일부 텍스트 포맷을 지원해요. 이 접미사는 데이터가 실제 데이터 앞에 열 이름과 타입이 있는 두 개의 추가 행을 포함한다는 뜻이에요. 그런 포맷에 대한 스키마 추론에서 ClickHouse는 처음 두 행을 읽고 열 이름과 타입을 추출해요.
예시
DESC format(TSVWithNamesAndTypes,
$$num str arr
UInt8 String Array(UInt8)
42 Hello, World! [1,2,3]
$$)
┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num │ UInt8 │ │ │ │ │ │
│ str │ String │ │ │ │ │ │
│ arr │ Array(UInt8) │ │ │ │ │ │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
메타데이터가 있는 JSON 포맷 (JSON formats with metadata)
일부 JSON 입력 포맷(JSON, JSONCompact, JSONColumnsWithMetadata)은 열 이름과 타입이 있는 메타데이터를 포함해요. 그런 포맷에 대한 스키마 추론에서 ClickHouse는 이 메타데이터를 읽어요.
예시
DESC format(JSON, $$
{
"meta":
[
{
"name": "num",
"type": "UInt8"
},
{
"name": "str",
"type": "String"
},
{
"name": "arr",
"type": "Array(UInt8)"
}
],
"data":
[
{
"num": 42,
"str": "Hello, World",
"arr": [1,2,3]
}
],
"rows": 1,
"statistics":
{
"elapsed": 0.005723915,
"rows_read": 1,
"bytes_read": 1
}
}
$$)
┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num │ UInt8 │ │ │ │ │ │
│ str │ String │ │ │ │ │ │
│ arr │ Array(UInt8) │ │ │ │ │ │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
Avro
Avro 포맷에서 ClickHouse는 데이터에서 스키마를 읽고 다음 타입 매칭으로 ClickHouse 스키마로 변환해요:
| Avro 데이터 타입 | ClickHouse 데이터 타입 |
|---|---|
boolean |
Bool |
int |
Int32 |
int (date) * |
Date32 |
long |
Int64 |
float |
Float32 |
double |
Float64 |
bytes, string |
String |
fixed |
FixedString(N) |
enum |
Enum |
array(T) |
Array(T) |
union(null, T), union(T, null) |
Nullable(T) |
null |
Nullable(Nothing) |
string (uuid) * |
UUID |
binary (decimal) * |
Decimal(P, S) |
다른 Avro 타입은 지원되지 않아요.
Parquet
Parquet 포맷에서 ClickHouse는 데이터에서 스키마를 읽고 다음 타입 매칭으로 ClickHouse 스키마로 변환해요:
| Parquet 데이터 타입 | ClickHouse 데이터 타입 |
|---|---|
BOOL |
Bool |
UINT8 |
UInt8 |
INT8 |
Int8 |
UINT16 |
UInt16 |
INT16 |
Int16 |
UINT32 |
UInt32 |
INT32 |
Int32 |
UINT64 |
UInt64 |
INT64 |
Int64 |
FLOAT |
Float32 |
DOUBLE |
Float64 |
DATE |
Date32 |
TIME (ms) |
DateTime |
TIMESTAMP, TIME (us, ns) |
DateTime64 |
STRING, BINARY |
String |
DECIMAL |
Decimal |
LIST |
Array |
STRUCT |
Tuple |
MAP |
Map |
다른 Parquet 타입은 지원되지 않아요.
Arrow
Arrow 포맷에서 ClickHouse는 데이터에서 스키마를 읽고 다음 타입 매칭으로 ClickHouse 스키마로 변환해요:
| Arrow 데이터 타입 | ClickHouse 데이터 타입 |
|---|---|
BOOL |
Bool |
UINT8 |
UInt8 |
INT8 |
Int8 |
UINT16 |
UInt16 |
INT16 |
Int16 |
UINT32 |
UInt32 |
INT32 |
Int32 |
UINT64 |
UInt64 |
INT64 |
Int64 |
FLOAT, HALF_FLOAT |
Float32 |
DOUBLE |
Float64 |
DATE32 |
Date32 |
DATE64 |
DateTime |
TIMESTAMP, TIME32, TIME64 |
DateTime64 |
STRING, BINARY |
String |
DECIMAL128, DECIMAL256 |
Decimal |
LIST |
Array |
STRUCT |
Tuple |
MAP |
Map |
다른 Arrow 타입은 지원되지 않아요.
ORC
ORC 포맷에서 ClickHouse는 데이터에서 스키마를 읽고 다음 타입 매칭으로 ClickHouse 스키마로 변환해요:
| ORC 데이터 타입 | ClickHouse 데이터 타입 |
|---|---|
Boolean |
Bool |
Tinyint |
Int8 |
Smallint |
Int16 |
Int |
Int32 |
Bigint |
Int64 |
Float |
Float32 |
Double |
Float64 |
Date |
Date32 |
Timestamp |
DateTime64 |
String, Char, Varchar,BINARY |
String |
Decimal |
Decimal |
List |
Array |
Struct |
Tuple |
Map |
Map |
Union |
Variant |
다른 ORC 타입은 지원되지 않아요.
Native
Native 포맷은 ClickHouse 내부에서 사용되며 데이터에 스키마를 포함해요. 스키마 추론에서 ClickHouse는 변환 없이 데이터에서 스키마를 읽어요.
외부 스키마가 있는 포맷 (Formats with external schema)
그런 포맷은 특정 스키마 언어의 별도 파일에 데이터를 설명하는 스키마가 필요해요. 그런 포맷의 파일에서 스키마를 자동으로 추론하려면 ClickHouse는 별도 파일에서 외부 스키마를 읽고 그것을 ClickHouse 테이블 스키마로 변환해요.
Protobuf
Protobuf 포맷의 스키마 추론에서 ClickHouse는 다음 타입 매칭을 사용해요:
| Protobuf 데이터 타입 | ClickHouse 데이터 타입 |
|---|---|
bool |
UInt8 |
float |
Float32 |
double |
Float64 |
int32, sint32, sfixed32 |
Int32 |
int64, sint64, sfixed64 |
Int64 |
uint32, fixed32 |
UInt32 |
uint64, fixed64 |
UInt64 |
string, bytes |
String |
enum |
Enum |
repeated T |
Array(T) |
message, group |
Tuple |
CapnProto
CapnProto 포맷의 스키마 추론에서 ClickHouse는 다음 타입 매칭을 사용해요:
| CapnProto 데이터 타입 | ClickHouse 데이터 타입 |
|---|---|
Bool |
UInt8 |
Int8 |
Int8 |
UInt8 |
UInt8 |
Int16 |
Int16 |
UInt16 |
UInt16 |
Int32 |
Int32 |
UInt32 |
UInt32 |
Int64 |
Int64 |
UInt64 |
UInt64 |
Float32 |
Float32 |
Float64 |
Float64 |
Text, Data |
String |
enum |
Enum |
List |
Array |
struct |
Tuple |
union(T, Void), union(Void, T) |
Nullable(T) |
강타입 바이너리 포맷 (Strong-typed binary formats)
그런 포맷에서 각 직렬화된 값은 자신의 타입(그리고 어쩌면 이름)에 대한 정보를 포함하지만 전체 테이블에 대한 정보는 없어요.
그런 포맷에 대한 스키마 추론에서 ClickHouse는 데이터를 한 줄씩(input_format_max_rows_to_read_for_schema_inference 행 또는 input_format_max_bytes_to_read_for_schema_inference 바이트까지) 읽고
각 값에 대한 타입(그리고 어쩌면 이름)을 데이터에서 추출한 다음 이 타입들을 ClickHouse 타입으로 변환해요.
MsgPack
MsgPack 포맷에는 행 사이 구분자가 없으므로, 이 포맷에 대한 스키마 추론을 사용하려면 설정 input_format_msgpack_number_of_columns으로 테이블의 열 수를 지정해야 해요. ClickHouse는 다음 타입 매칭을 사용해요:
MessagePack 데이터 타입 (INSERT) |
ClickHouse 데이터 타입 |
|---|---|
int N, uint N, negative fixint, positive fixint |
Int64 |
bool |
UInt8 |
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32 |
String |
float 32 |
Float32 |
float 64 |
Float64 |
uint 16 |
Date |
uint 32 |
DateTime |
uint 64 |
DateTime64 |
fixarray, array 16, array 32 |
Array |
fixmap, map 16, map 32 |
Map |
기본적으로 모든 추론된 타입은 Nullable 안에 있지만, 설정 schema_inference_make_columns_nullable으로 변경할 수 있어요.
BSONEachRow
BSONEachRow에서 각 데이터 행은 BSON 문서로 제시돼요. 스키마 추론에서 ClickHouse는 BSON 문서를 하나씩 읽고 데이터에서 값, 이름, 타입을 추출한 다음 다음 타입 매칭을 사용해 이 타입들을 ClickHouse 타입으로 변환해요:
| BSON 타입 | ClickHouse 타입 |
|---|---|
\x08 boolean |
Bool |
\x10 int32 |
Int32 |
\x12 int64 |
Int64 |
\x01 double |
Float64 |
\x09 datetime |
DateTime64 |
\x05 binary with\x00 binary subtype, \x02 string, \x0E symbol, \x0D JavaScript code |
String |
\x07 ObjectId, |
FixedString(12) |
\x05 binary with \x04 uuid subtype, size = 16 |
UUID |
\x04 array |
Array/Tuple (중첩 타입이 다른 경우) |
\x03 document |
명명된 Tuple/Map (String 키 사용) |
기본적으로 모든 추론된 타입은 Nullable 안에 있지만, 설정 schema_inference_make_columns_nullable으로 변경할 수 있어요.
상수 스키마가 있는 포맷 (Formats with constant schema)
그런 포맷의 데이터는 항상 같은 스키마를 가져요.
LineAsString
이 포맷에서 ClickHouse는 데이터에서 전체 줄을 String 데이터 타입의 단일 열로 읽어요. 이 포맷의 추론된 타입은 항상 String이고 열 이름은 line이에요.
예시
DESC format(LineAsString, 'Hello\nworld!')
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │ │ │ │ │ │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
JSONAsString
이 포맷에서 ClickHouse는 데이터에서 전체 JSON 객체를 String 데이터 타입의 단일 열로 읽어요. 이 포맷의 추론된 타입은 항상 String이고 열 이름은 json이에요.
예시
DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}')
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ String │ │ │ │ │ │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
JSONAsObject
이 포맷에서 ClickHouse는 데이터에서 전체 JSON 객체를 JSON 데이터 타입의 단일 열로 읽어요. 이 포맷의 추론된 타입은 항상 JSON이고 열 이름은 json이에요.
예시
DESC format(JSONAsObject, '{"x" : 42, "y" : "Hello, World!"}');
┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ JSON │ │ │ │ │ │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
스키마 추론 모드 (Schema inference modes)
데이터 파일 집합에서의 스키마 추론은 2가지 모드로 동작할 수 있어요: default와 union.
모드는 설정 schema_inference_mode로 제어돼요.
기본 모드 (Default mode)
기본 모드에서 ClickHouse는 모든 파일이 같은 스키마를 가진다고 가정하고, 성공할 때까지 파일을 하나씩 읽어 스키마를 추론하려 시도해요.
예시:
다음 내용의 data1.jsonl, data2.jsonl, data3.jsonl 세 파일이 있다고 가정해 봐요:
data1.jsonl:
{"field1" : 1, "field2" : null}
{"field1" : 2, "field2" : null}
{"field1" : 3, "field2" : null}
data2.jsonl:
{"field1" : 4, "field2" : "Data4"}
{"field1" : 5, "field2" : "Data5"}
{"field1" : 6, "field2" : "Data5"}
data3.jsonl:
{"field1" : 7, "field2" : "Data7", "field3" : [1, 2, 3]}
{"field1" : 8, "field2" : "Data8", "field3" : [4, 5, 6]}
{"field1" : 9, "field2" : "Data9", "field3" : [7, 8, 9]}
이 3개 파일에 스키마 추론을 사용해 보자면:
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'
┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64) │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘
보시다시피 data3.jsonl 파일의 field3이 없어요.
ClickHouse가 먼저 data1.jsonl 파일에서 스키마를 추론하려 시도했고 field2에 null만 있어 실패한 다음 data2.jsonl에서 스키마를 추론하려 시도해 성공했기 때문에, data3.jsonl 파일의 데이터는 읽히지 않았어요.
유니언 모드 (Union mode)
유니언 모드에서 ClickHouse는 파일들이 서로 다른 스키마를 가질 수 있다고 가정하므로 모든 파일의 스키마를 추론한 다음 그것들을 공통 스키마로 유니언해요.
다음 내용의 data1.jsonl, data2.jsonl, data3.jsonl 세 파일이 있다고 가정해 봐요:
data1.jsonl:
{"field1" : 1}
{"field1" : 2}
{"field1" : 3}
data2.jsonl:
{"field2" : "Data4"}
{"field2" : "Data5"}
{"field2" : "Data5"}
data3.jsonl:
{"field3" : [1, 2, 3]}
{"field3" : [4, 5, 6]}
{"field3" : [7, 8, 9]}
이 3개 파일에 스키마 추론을 사용해 보자면:
:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'
┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64) │
│ field2 │ Nullable(String) │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘
보시다시피 모든 파일의 모든 필드가 있어요.
참고:
- 일부 파일은 결과 스키마의 일부 열을 포함하지 않을 수 있으므로, 유니언 모드는 열의 일부만 읽는 것을 지원하는 포맷(JSONEachRow, Parquet, TSVWithNames 등)에서만 지원되며 다른 포맷(CSV, TSV, JSONCompactEachRow 등)에서는 동작하지 않아요.
- ClickHouse가 파일 중 하나에서 스키마를 추론할 수 없으면 예외가 발생해요.
- 파일이 많으면 모든 파일에서 스키마를 읽는 데 시간이 많이 걸릴 수 있어요.
자동 포맷 감지 (Automatic format detection)
데이터 포맷이 지정되지 않고 파일 확장자로도 결정할 수 없으면 ClickHouse는 내용으로 파일 포맷을 감지하려 시도해요.
예시:
다음 내용의 data가 있다고 가정해 봐요:
"a","b"
1,"Data1"
2,"Data2"
3,"Data3"
포맷이나 구조를 지정하지 않고 이 파일을 검사하고 조회할 수 있어요:
:) desc file(data);
┌─name─┬─type─────────────┐
│ a │ Nullable(Int64) │
│ b │ Nullable(String) │
└──────┴──────────────────┘
:) select * from file(data);
┌─a─┬─b─────┐
│ 1 │ Data1 │
│ 2 │ Data2 │
│ 3 │ Data3 │
└───┴───────┘
참고: ClickHouse는 포맷의 일부 하위 집합만 감지할 수 있고 이 감지에는 시간이 걸리므로, 항상 포맷을 명시적으로 지정하는 것이 더 좋아요.