以前のJSONデータのロードの例は、JSONEachRow (NDJSON) の使用を前提としています。このフォーマットは、各JSON行のキーをカラムとして読み取ります。例えば:
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', 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', 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', 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を読み取るのにも役立ちます。例如、キーが行ごとに型が異なる場合（時には文字列、他の時にはオブジェクトなど）。そのような場合、ClickHouseはJSONEachRowを使用して安定したスキーマを推論することができず、JSONAsObjectを使用することでデータを厳密な型制約なしに取り込むことができ、各JSON行を単一のカラムに全体として格納します。たとえば、次の例でJSONEachRowが失敗する様子を確認してください:
SELECT count()
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/bluesky/file_0001.json.gz', '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', '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クローズを使用し、インポートが成功したことを確認できます:
┌─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を参照）:
{
"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配列のリストを扱います:
["Akiba_Hebrew_Academy", "2017-08-01", 241],
["Aegithina_tiphia", "2018-02-01", 34],
["1971-72_Utah_Stars_season", "2016-10-01", 1]
この場合、ClickHouseはこのデータをロードし、各値を配列内の順序に基づいて対応するカラムに割り当てます。これにはJSONCompactEachRowフォーマットを使用します:
┌─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オブジェクトには値を持つカラムが含まれています。次のファイルを見てみましょう:
{
"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オブジェクトが含まれているこのファイルを考えてみましょう:
[
{"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は、通常JSONEachRowフォーマットと一緒に使用されるJSONオブジェクトを行単位でフォーマットしたファイルでも問題なく動作することに注意してください。
ネストされたオブジェクトのスキーマ
ネストされたJSONオブジェクト（nested JSON objects）を扱う場合、明示的なスキーマを定義し、複雑な型（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
これにより、ドット表記を使用してネストされた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オブジェクトを平坦化したり、一部のネストされた値を使用してそれらを別のカラムとして保存したりできます。
不明なカラムのスキップ
デフォルトでは、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)
ClickHouseはJSONとテーブルカラムの構造が不一致である場合に例外を投げます。
BSON
ClickHouseは、BSONエンコードファイルへのエクスポートとインポートを許可します。このフォーマットは、一部のDBMS（例えば、MongoDBデータベース）によって使用されます。
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ファイルにエクスポートされます。
