다른 JSON 포맷 다루기
다른 JSON 포맷 다루기
앞선 JSON 데이터 적재 예시는 JSONEachRow(NDJSON)를 사용한다고 가정했어요. 이 포맷은 각 JSON 라인의 키를 컬럼으로 읽어요. 예를 들어:
출처: 문서
본문
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', NOSIGN, JSONEachRow)
LIMIT 5
┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘
5 rows in set. Elapsed: 0.449 sec.
일반적으로 이것이 가장 흔히 쓰이는 JSON 포맷이지만, 다른 포맷을 만나거나 JSON을 단일 객체로 읽어야 하는 경우도 있어요. 아래에서 다른 일반적인 포맷으로 JSON을 읽고 적재하는 예시를 살펴볼게요.
JSON을 객체로 읽기
앞선 예시들은 JSONEachRow가 줄바꿈 구분 JSON을 읽는 방법을 보여줘요. 각 라인은 테이블 행에, 각 키는 컬럼에 매핑되는 별도의 객체로 읽혀요. 이것은 각 컬럼에 단일 타입이 있고 JSON이 예측 가능한 경우에 이상적이에요. 반면 JSONAsObject는 각 라인을 단일 JSON 객체로 취급해서 JSON 타입의 단일 컬럼에 저장해요. 그래서 중첩 JSON 페이로드와 키가 동적이고 잠재적으로 여러 타입을 가질 수 있는 경우에 더 잘 맞아요. 행 단위 삽입에는 JSONEachRow를, 유연하거나 동적 JSON 데이터를 저장할 때는 JSONAsObject를 사용하세요. 위 예시를, 같은 데이터를 라인당 JSON 객체로 읽는 다음 쿼리와 비교해 볼게요.
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', NOSIGN, JSONAsObject)
LIMIT 5
┌─json─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.338 sec.
JSONAsObject는 단일 JSON 객체 컬럼을 사용해 테이블에 행을 삽입할 때 유용해요. 예를 들어:
CREATE TABLE pypi
(
`json` JSON
)
ENGINE = MergeTree
ORDER BY tuple();
INSERT INTO pypi SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', NOSIGN, JSONAsObject)
LIMIT 5;
SELECT *
FROM pypi
LIMIT 2;
┌─json─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.003 sec.
JSONAsObject 포맷은 객체 구조가 일관되지 않은 줄바꿈 구분 JSON을 읽을 때도 유용할 수 있어요. 예를 들어 키의 타입이 행마다 다르면(어떤 때는 문자열, 어떤 때는 객체) JSONEachRow로는 안정적인 스키마를 추론할 수 없는데, JSONAsObject는 엄격한 타입 강제 없이 각 JSON 행을 단일 컬럼 전체로 저장해서 데이터를 수집할 수 있게 해줘요. 예를 들어 다음 예시에서 JSONEachRow가 실패하는 걸 눈여겨보세요.
SELECT count()
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/bluesky/file_0001.json.gz', NOSIGN, 'JSONEachRow')
Elapsed: 1.198 sec.
Received exception from server (version 24.12.1):
Code: 636. DB::Exception: Received from sql-clickhouse.clickhouse.com:9440. 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 'record.subject' has type 'String' and in some - 'Tuple(`$type` String, cid String, uri 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 'record.subject'. (INCORRECT_DATA) (version 24.12.1.18239 (official build))
To increase the maximum number of rows/bytes to read for structure determination, use setting input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference.
You can specify the structure manually: (in file/uri bluesky/file_0001.json.gz). (CANNOT_EXTRACT_TABLE_STRUCTURE)
반대로, 이 경우에는 JSONAsObject를 쓸 수 있어요. JSON 타입이 같은 서브컬럼에 여러 타입을 지원하니까요.
SELECT count()
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/bluesky/file_0001.json.gz', NOSIGN, 'JSONAsObject')
┌─count()─┐
│ 1000000 │
└─────────┘
1 row in set. Elapsed: 0.480 sec. Processed 1.00 million rows, 256.00 B (2.08 million rows/s., 533.76 B/s.)
JSON 객체 배열
JSON 데이터의 가장 인기 있는 형태 중 하나는 이 예시처럼 JSON 배열 안에 JSON 객체 목록이 있는 경우예요.
> cat list.json
[
{
"path": "Akiba_Hebrew_Academy",
"month": "2017-08-01",
"hits": 241
},
{
"path": "Aegithina_tiphia",
"month": "2018-02-01",
"hits": 34
},
...
]
이런 데이터를 위한 테이블을 만들어 볼게요.
CREATE TABLE sometable
(
`path` String,
`month` Date,
`hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)
JSON 객체 목록을 가져오려면 JSONEachRow 포맷을 쓸 수 있어요 (list.json 파일에서 데이터 삽입).
INSERT INTO sometable
FROM INFILE 'list.json'
FORMAT JSONEachRow
로컬 파일에서 데이터를 적재하기 위해 FROM INFILE 절을 사용했고, 가져오기가 성공했음을 볼 수 있어요.
SELECT *
FROM sometable
┌─path──────────────────────┬──────month─┬─hits─┐
│ 1971-72_Utah_Stars_season │ 2016-10-01 │ 1 │
│ Akiba_Hebrew_Academy │ 2017-08-01 │ 241 │
│ Aegithina_tiphia │ 2018-02-01 │ 34 │
└───────────────────────────┴────────────┴──────┘
JSON 객체 키
어떤 경우에는 JSON 객체 목록이 배열 요소가 아니라 객체 속성으로 인코딩될 수 있어요 (objects.json을 참고하세요).
cat objects.json
{
"a": {
"path":"April_25,_2017",
"month":"2018-01-01",
"hits":2
},
"b": {
"path":"Akahori_Station",
"month":"2016-06-01",
"hits":11
},
...
}
ClickHouse는 JSONObjectEachRow 포맷으로 이런 종류의 데이터를 적재할 수 있어요.
INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;
SELECT * FROM sometable;
┌─path────────────┬──────month─┬─hits─┐
│ Abducens_palsy │ 2016-05-01 │ 28 │
│ Akahori_Station │ 2016-06-01 │ 11 │
│ April_25,_2017 │ 2018-01-01 │ 2 │
└─────────────────┴────────────┴──────┘
부모 객체 키 값 지정하기
부모 객체 키의 값도 테이블에 저장하고 싶다고 가정해 볼게요. 이 경우 다음 옵션으로 키 값을 저장할 컬럼 이름을 정의할 수 있어요.
SET format_json_object_each_row_column_for_object_name = 'id'
이제 file() 함수를 사용해 원본 JSON 파일에서 어떤 데이터가 적재될지 확인할 수 있어요.
SELECT * FROM file('objects.json', JSONObjectEachRow)
┌─id─┬─path────────────┬──────month─┬─hits─┐
│ a │ April_25,_2017 │ 2018-01-01 │ 2 │
│ b │ Akahori_Station │ 2016-06-01 │ 11 │
│ c │ Abducens_palsy │ 2016-05-01 │ 28 │
└────┴─────────────────┴────────────┴──────┘
id 컬럼이 키 값으로 올바르게 채워진 걸 눈여겨보세요.
JSON 배열
때로는 공간을 아끼기 위해 JSON 파일이 객체가 아니라 배열로 인코딩되기도 해요. 이 경우 우리는 JSON 배열 목록을 다루는 거예요.
cat arrays.json
["Akiba_Hebrew_Academy", "2017-08-01", 241],
["Aegithina_tiphia", "2018-02-01", 34],
["1971-72_Utah_Stars_season", "2016-10-01", 1]
이 경우 ClickHouse는 데이터를 적재하면서 배열에서의 순서에 따라 각 값을 해당 컬럼에 배정해요. 이를 위해 JSONCompactEachRow 포맷을 사용해요.
SELECT * FROM sometable
┌─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 │
└───────────────────────────┴────────────┴─────┘
JSON 배열에서 개별 컬럼 가져오기
어떤 경우에는 데이터가 행 단위가 아니라 열 단위로 인코딩될 수 있어요. 이 경우 부모 JSON 객체가 값이 있는 컬럼들을 포함해요. 다음 파일을 살펴볼게요.
cat columns.json
{
"path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],
"month": ["2016-07-01", "2015-07-01", "2015-07-01"],
"hits": [178, 11, 1]
}
ClickHouse는 그렇게 포맷된 데이터를 파싱하기 위해 JSONColumns 포맷을 사용해요.
SELECT * FROM file('columns.json', JSONColumns)
┌─path───────────────────────┬──────month─┬─hits─┐
│ 2007_Copa_America │ 2016-07-01 │ 178 │
│ Car_dealerships_in_the_USA │ 2015-07-01 │ 11 │
│ Dihydromyricetin_reductase │ 2015-07-01 │ 1 │
└────────────────────────────┴────────────┴──────┘
객체 대신 컬럼 배열을 다룰 때는 JSONCompactColumns 포맷으로 더 압축된 포맷도 지원돼요.
SELECT * FROM file('columns-array.json', JSONCompactColumns)
┌─c1──────────────┬─────────c2─┬─c3─┐
│ Heidenrod │ 2017-01-01 │ 10 │
│ Arthur_Henrique │ 2016-11-01 │ 12 │
│ Alan_Ebnother │ 2015-11-01 │ 66 │
└─────────────────┴────────────┴────┘
JSON 객체 파싱 대신 저장하기
JSON 객체를 파싱하는 대신 단일 String(또는 JSON) 컬럼에 저장하고 싶은 경우가 있어요. 서로 다른 구조의 JSON 객체 목록을 다룰 때 유용할 수 있어요. 이 파일을 예로 들어볼게요. 부모 목록 안에 서로 다른 여러 JSON 객체가 있어요.
cat custom.json
[
{"name": "Joe", "age": 99, "type": "person"},
{"url": "/my.post.MD", "hits": 1263, "type": "post"},
{"message": "Warning on disk usage", "type": "log"}
]
원본 JSON 객체를 다음 테이블에 저장하고 싶어요.
CREATE TABLE events
(
`data` String
)
ENGINE = MergeTree
ORDER BY ()
이제 JSONAsString 포맷으로 파일에서 테이블로 데이터를 적재해서, JSON 객체를 파싱하지 않고 보존할 수 있어요.
INSERT INTO events (data)
FROM INFILE 'custom.json'
FORMAT JSONAsString
그리고 JSON 함수로 저장된 객체를 쿼리할 수 있어요.
SELECT
JSONExtractString(data, 'type') AS type,
data
FROM events
┌─type───┬─data─────────────────────────────────────────────────┐
│ person │ {"name": "Joe", "age": 99, "type": "person"} │
│ post │ {"url": "/my.post.MD", "hits": 1263, "type": "post"} │
│ log │ {"message": "Warning on disk usage", "type": "log"} │
└────────┴──────────────────────────────────────────────────────┘
JSONAsString은 라인당 JSON 객체 포맷(보통 JSONEachRow 포맷과 함께 쓰이는)의 파일에서도 완벽하게 동작한다는 점을 기억하세요.
중첩 객체를 위한 스키마
중첩 JSON 객체를 다룰 때 추가로 명시적인 스키마를 정의하고 복잡한 타입(Array, JSON 또는 Tuple)을 사용해 데이터를 적재할 수 있어요.
SELECT *
FROM file('list-nested.json', JSONEachRow, 'page Tuple(path String, title String, owner_id UInt16), month Date, hits UInt32')
LIMIT 1
┌─page───────────────────────────────────────────────┬──────month─┬─hits─┐
│ ('Akiba_Hebrew_Academy','Akiba Hebrew Academy',12) │ 2017-08-01 │ 241 │
└────────────────────────────────────────────────────┴────────────┴──────┘
중첩 JSON 객체 접근하기
다음 설정 옵션을 켜면 중첩 JSON 키를 참조할 수 있어요.
SET input_format_import_nested_json = 1
이렇게 하면 점 표기법(dot notation)으로 중첩 JSON 객체 키를 참조할 수 있어요 (동작하려면 백틱 기호로 감싸는 걸 기억하세요).
SELECT *
FROM file('list-nested.json', JSONEachRow, '`page.owner_id` UInt32, `page.title` String, month Date, hits UInt32')
LIMIT 1
┌─page.owner_id─┬─page.title───────────┬──────month─┬─hits─┐
│ 12 │ Akiba Hebrew Academy │ 2017-08-01 │ 241 │
└───────────────┴──────────────────────┴────────────┴──────┘
이런 식으로 중첩 JSON 객체를 평탄화(flatten)하거나 일부 중첩 값을 사용해 별도 컬럼으로 저장할 수 있어요.
알 수 없는 컬럼 건너뛰기
기본적으로 ClickHouse는 JSON 데이터를 가져올 때 알 수 없는 컬럼을 무시해요. month 컬럼이 없는 테이블로 원본 파일을 가져와 볼게요.
CREATE TABLE shorttable
(
`path` String,
`hits` UInt32
)
ENGINE = MergeTree
ORDER BY path
3개 컬럼이 있는 원본 JSON 데이터를 여전히 이 테이블에 삽입할 수 있어요.
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
SELECT * FROM shorttable
┌─path──────────────────────┬─hits─┐
│ 1971-72_Utah_Stars_season │ 1 │
│ Aegithina_tiphia │ 34 │
│ Akiba_Hebrew_Academy │ 241 │
└───────────────────────────┴──────┘
ClickHouse는 가져오는 동안 알 수 없는 컬럼을 무시해요. 이것은 input_format_skip_unknown_fields 설정 옵션으로 끌 수 있어요.
SET input_format_skip_unknown_fields = 0;
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
Ok.
Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: month: (in file/uri /data/clickhouse/user_files/list.json): (at row 1)
JSON과 테이블 컬럼 구조가 일치하지 않는 경우 ClickHouse는 예외를 던져요.
BSON
ClickHouse는 BSON으로 인코딩된 파일로 내보내기와 가져오기를 지원해요. 이 포맷은 MongoDB 같은 일부 DBMS에서 사용해요. BSON 데이터를 가져오려면 BSONEachRow 포맷을 사용해요. 이 BSON 파일에서 데이터를 가져와 볼게요.
SELECT * FROM file('data.bson', BSONEachRow)
┌─path──────────────────────┬─month─┬─hits─┐
│ Bob_Dolman │ 17106 │ 245 │
│ 1-krona │ 17167 │ 4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 17167 │ 3 │
└───────────────────────────┴───────┴──────┘
같은 포맷으로 BSON 파일에 내보낼 수도 있어요.
SELECT *
FROM sometable
INTO OUTFILE 'out.bson'
FORMAT BSONEachRow
그러면 데이터가 out.bson 파일로 내보내져요.