以下の例では、構造化JSONデータと半構造化JSONデータの読み込み方法を、ごく簡単な例で示します。ネスト構造を含むより複雑なJSONについては、ガイドJSONスキーマの設計を参照してください。
このセクションでは、JSONデータがNDJSON (改行区切りJSON) フォーマット、すなわちClickHouseでJSONEachRowとして知られるフォーマットであり、カラム名と型が固定された構造化データであることを前提とします。NDJSONは簡潔でスペース効率に優れるため、JSONの読み込みに推奨されるフォーマットですが、入力と出力の両方で他のフォーマットもサポートされています。
次のJSONサンプルは、Python PyPIデータセットの1行を表しています：
{
"date": "2022-11-15",
"country_code": "ES",
"project": "clickhouse-connect",
"type": "bdist_wheel",
"installer": "pip",
"python_minor": "3.9",
"system": "Linux",
"version": "0.3.0"
}
可能であれば静的なスキーマを優先してくださいカラム名と型が固定されており、新しいカラムが追加される見込みがない場合は、本番環境では常に静的に定義されたスキーマを優先してください。JSON型は、カラム名や型が変わる可能性のある、非常に動的なデータに適しています。この型は、プロトタイピングやデータ探索にも役立ちます。
CREATE TABLE pypi (
`date` Date,
`country_code` String,
`project` String,
`type` String,
`installer` String,
`python_minor` String,
`system` String,
`version` String
)
ENGINE = MergeTree
ORDER BY (project, date)
ソートキーここでは、ORDER BY 句でソートキーを指定しています。ソートキーの詳細と選び方については、こちらを参照してください。 SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
LIMIT 1
┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-11-15 │ CN │ clickhouse-connect │ bdist_wheel │ bandersnatch │ │ │ 0.2.8 │
└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘
1 row in set. Elapsed: 1.232 sec.
*.json.gz ファイルを読み込みます。ClickHouse はファイルの拡張子と内容からフォーマットが JSONEachRow (ndjson) であることを自動的に推測します。ClickHouse が検出できない場合は、パラメータ関数を使ってフォーマットを手動で指定できます。
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)
圧縮ファイル上記のファイルは圧縮形式にも対応しており、ClickHouse が自動的に検出して処理します。
INSERT INTO SELECT を使用します。
INSERT INTO pypi SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
Ok.
0 rows in set. Elapsed: 10.445 sec. Processed 19.49 million rows, 35.71 MB (1.87 million rows/s., 3.42 MB/s.)
SELECT * FROM pypi LIMIT 2
┌───────date─┬─country_code─┬─project────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-05-26 │ CN │ clickhouse-connect │ sdist │ bandersnatch │ │ │ 0.0.7 │
│ 2022-05-26 │ CN │ clickhouse-connect │ sdist │ bandersnatch │ │ │ 0.0.7 │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┘
2 rows in set. Elapsed: 0.005 sec. Processed 8.19 thousand rows, 908.03 KB (1.63 million rows/s., 180.38 MB/s.)
FORMAT 句 を使ってインラインで読み込むこともできます。たとえば、
INSERT INTO pypi
FORMAT JSONEachRow
{"date":"2022-11-15","country_code":"CN","project":"clickhouse-connect","type":"bdist_wheel","installer":"bandersnatch","python_minor":"","system":"","version":"0.2.8"}
JSONEachRow フォーマットを使用することを前提としています。その他の一般的な JSON フォーマットにも対応しており、それらの読み込み例はこちらで確認できます。
前の例では、キー名と型があらかじめ分かっている固定的な JSON を読み込みました。しかし、実際にはそうでないことも少なくありません。キーが追加されたり、型が変わったりすることがあります。これは、オブザーバビリティ データのようなユースケースではよくあることです。
ClickHouse では、専用の JSON 型でこれに対応しています。
次に、上記の Python PyPI dataset を拡張したバージョンの例を見てみましょう。ここでは、任意のキーと値のペアを含む tags カラムを追加しています。
{
"date": "2022-09-22",
"country_code": "IN",
"project": "clickhouse-connect",
"type": "bdist_wheel",
"installer": "bandersnatch",
"python_minor": "",
"system": "",
"version": "0.2.8",
"tags": {
"5gTux": "f3to*PMvaTYZsz!*rtzX1",
"nD8CV": "value"
}
}
tagsカラムは内容が一定ではないため、スキーマとして定義できません。このデータを読み込むには、先ほどのスキーマを使いつつ、型をJSONとする追加のtagsカラムを指定できます。
SET enable_json_type = 1;
CREATE TABLE pypi_with_tags
(
`date` Date,
`country_code` String,
`project` String,
`type` String,
`installer` String,
`python_minor` String,
`system` String,
`version` String,
`tags` JSON
)
ENGINE = MergeTree
ORDER BY (project, date);
INSERT INTO pypi_with_tags SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample.json.gz')
INSERT INTO pypi_with_tags SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample.json.gz')
Ok.
0 rows in set. Elapsed: 255.679 sec. Processed 1.00 million rows, 29.00 MB (3.91 thousand rows/s., 113.43 KB/s.)
Peak memory usage: 2.00 GiB.
SELECT *
FROM pypi_with_tags
LIMIT 2
┌───────date─┬─country_code─┬─project────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┬─tags─────────────────────────────────────────────────────┐
│ 2022-05-26 │ CN │ clickhouse-connect │ sdist │ bandersnatch │ │ │ 0.0.7 │ {"nsBM":"5194603446944555691"} │
│ 2022-05-26 │ CN │ clickhouse-connect │ sdist │ bandersnatch │ │ │ 0.0.7 │ {"4zD5MYQz4JkP1QqsJIS":"0","name":"8881321089124243208"} │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┴──────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.149 sec.
- キーが固定されておらず、時間の経過とともに変化する。
- 値の型が一定ではない (例: path には文字列が入ることもあれば、数値が入ることもある) 。
- 厳密な型付けが現実的でなく、スキーマに柔軟性が求められる。
一方、データ構造が明確で一貫している場合は、データが JSON フォーマットであっても、JSON 型が必要になることはほとんどありません。具体的には、データに次のような特徴がある場合です。
- 既知のキーを持つフラットな構造: 標準的なカラム型 (例: String) を使用します。
- 予測可能なネスト構造: このような構造には Tuple、Array、または Nested 型を使用します。
- 構造は予測可能だが型が変わる: 代わりに Dynamic 型または Variant 型を検討してください。
また、上記の例のように、予測可能なトップレベルのキーには静的カラムを使用し、ペイロード内の動的な部分には 1 つの JSON カラムを使う形で、これらのアプローチを組み合わせることもできます。 
