ClickHouseはJSONデータの構造を自動的に推定できます。これにより、たとえばディスク上やS3バケット内のJSONデータをclickhouse-localで直接クエリしたり、ClickHouseにデータをロードする前にスキーマを自動的に作成したりできます。
- 構造が一定している - 型を推論しようとしているデータに、必要なすべてのキーが含まれている場合です。型推論は、データを最大行数または最大バイト数までサンプリングして行われます。サンプル範囲より後のデータに追加のカラムがあっても、それらは無視されるためクエリできません。
- 型が一定している - 特定のキーのデータ型には互換性が必要です。つまり、ある型から別の型へ自動的に型変換できる必要があります。
より動的な JSON で、新しいキーが追加され、同じパスに対して複数の型を取りうる場合は、「半構造化データと動的データの操作」を参照してください。
以下では、JSON の構造が一貫しており、各パスに対応する型が 1 つだけであることを前提とします。
これまでの例では、NDJSON フォーマットの Python PyPI dataset の簡易版を使用してきました。この節では、より複雑な入れ子構造を持つデータセット、つまり 250 万件の学術論文を含む arXiv dataset を見ていきます。NDJSON として配布されるこのデータセットでは、各行が公開済みの学術論文 1 件を表します。行の例を以下に示します。
{
"id": "2101.11408",
"submitter": "Daniel Lemire",
"authors": "Daniel Lemire",
"title": "Number Parsing at a Gigabyte per Second",
"comments": "Software at https://github.com/fastfloat/fast_float and\n https://github.com/lemire/simple_fastfloat_benchmark/",
"journal-ref": "Software: Practice and Experience 51 (8), 2021",
"doi": "10.1002/spe.2984",
"report-no": null,
"categories": "cs.DS cs.MS",
"license": "http://creativecommons.org/licenses/by/4.0/",
"abstract": "With disks and networks providing gigabytes per second ....\n",
"versions": [
{
"created": "Mon, 11 Jan 2021 20:31:27 GMT",
"version": "v1"
},
{
"created": "Sat, 30 Jan 2021 23:57:29 GMT",
"version": "v2"
}
],
"update_date": "2022-11-07",
"authors_parsed": [
[
"Lemire",
"Daniel",
""
]
]
}
Tuple や Array などの複雑な型を導入します。
このデータセットは、s3://datasets-documentation/arxiv/arxiv.json.gz にある公開 S3 バケットに保存されています。
上記のデータセットには、ネストされた JSON オブジェクトが含まれていることがわかります。スキーマは作成してバージョン管理すべきですが、スキーマ推論を使うと、データから型を推論できます。これにより、スキーマ DDL を自動生成できるため、手動で作成する必要がなくなり、開発プロセスを高速化できます。
フォーマットの自動検出スキーマの検出に加えて、JSON スキーマ推論では、ファイル拡張子と内容からデータのフォーマットも自動的に推論します。その結果、上記のファイルは自動的に NDJSON として検出されます。
DESCRIBE コマンドとともに使用すると、推論される型を確認できます。
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS describe_compact_output = 1
┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
│ id │ Nullable(String) │
│ submitter │ Nullable(String) │
│ authors │ Nullable(String) │
│ title │ Nullable(String) │
│ comments │ Nullable(String) │
│ journal-ref │ Nullable(String) │
│ doi │ Nullable(String) │
│ report-no │ Nullable(String) │
│ categories │ Nullable(String) │
│ license │ Nullable(String) │
│ abstract │ Nullable(String) │
│ versions │ Array(Tuple(created Nullable(String),version Nullable(String))) │
│ update_date │ Nullable(Date) │
│ authors_parsed │ Array(Array(Nullable(String))) │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘
String として検出され、update_date カラムは正しく Date として検出されています。versions カラムはオブジェクトのリストを格納するために Array(Tuple(created String, version String)) として作成され、authors_parsed はネストされた配列を表す Array(Array(String)) として定義されています。
以下では、JSON が一貫した構造を持ち、各 パス ごとに型が 1 つだけであることを前提としています。
スキーマ推論を利用すれば、JSON データをそのままクエリできます。以下では、日付と配列が自動的に検出されることを利用して、年ごとの上位の著者を求めます。
SELECT
toYear(update_date) AS year,
authors,
count() AS c
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
GROUP BY
year,
authors
ORDER BY
year ASC,
c DESC
LIMIT 1 BY year
┌─year─┬─authors────────────────────────────────────┬───c─┐
│ 2007 │ The BABAR Collaboration, B. Aubert, et al │ 98 │
│ 2008 │ The OPAL collaboration, G. Abbiendi, et al │ 59 │
│ 2009 │ Ashoke Sen │ 77 │
│ 2010 │ The BABAR Collaboration, B. Aubert, et al │ 117 │
│ 2011 │ Amelia Carolina Sparavigna │ 21 │
│ 2012 │ ZEUS Collaboration │ 140 │
│ 2013 │ CMS Collaboration │ 125 │
│ 2014 │ CMS Collaboration │ 87 │
│ 2015 │ ATLAS Collaboration │ 118 │
│ 2016 │ ATLAS Collaboration │ 126 │
│ 2017 │ CMS Collaboration │ 122 │
│ 2018 │ CMS Collaboration │ 138 │
│ 2019 │ CMS Collaboration │ 113 │
│ 2020 │ CMS Collaboration │ 94 │
│ 2021 │ CMS Collaboration │ 69 │
│ 2022 │ CMS Collaboration │ 62 │
│ 2023 │ ATLAS Collaboration │ 128 │
│ 2024 │ ATLAS Collaboration │ 120 │
└──────┴────────────────────────────────────────────┴─────┘
18 rows in set. Elapsed: 20.172 sec. Processed 2.52 million rows, 1.39 GB (124.72 thousand rows/s., 68.76 MB/s.)
CREATE AS EMPTY コマンドを実行すると、テーブルの DDL が推論され、テーブルが作成されます。この操作ではデータは読み込まれません。
CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS schema_inference_make_columns_nullable = 0
SHOW CREATE TABLE コマンドを使用します。
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv
(
`id` String,
`submitter` String,
`authors` String,
`title` String,
`comments` String,
`journal-ref` String,
`doi` String,
`report-no` String,
`categories` String,
`license` String,
`abstract` String,
`versions` Array(Tuple(created String, version String)),
`update_date` Date,
`authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
input_format_max_rows_to_read_for_schema_inference (デフォルトは25000) と input_format_max_bytes_to_read_for_schema_inference (デフォルトは32MB) で制御されます。検出結果が正しくない場合は、こちら に記載されているようにヒントを指定できます。
上の例では、S3 上のファイルを使ってテーブルのスキーマを作成しています。1 行だけのスニペットからスキーマを作成したい場合もあるでしょう。その場合は、以下のように format 関数を使用します。
CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM format(JSONEachRow, '{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"Withdisks and networks providing gigabytes per second ","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]]}') SETTINGS schema_inference_make_columns_nullable = 0
SHOW CREATE TABLE arxiv
CREATE TABLE arxiv
(
`id` String,
`submitter` String,
`authors` String,
`title` String,
`comments` String,
`doi` String,
`report-no` String,
`categories` String,
`license` String,
`abstract` String,
`versions` Array(Tuple(created String, version String)),
`update_date` Date,
`authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
INSERT INTO SELECT を使ってデータをテーブルに挿入できます。
INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
0 rows in set. Elapsed: 38.498 sec. Processed 2.52 million rows, 1.39 GB (65.35 thousand rows/s., 36.03 MB/s.)
Peak memory usage: 870.67 MiB.
PrettyJSONEachRow を使用すると、行を元の構造のまま表示できます。
SELECT *
FROM arxiv
LIMIT 1
FORMAT PrettyJSONEachRow
{
"id": "0704.0004",
"submitter": "David Callan",
"authors": "David Callan",
"title": "A determinant of Stirling cycle numbers counts unlabeled acyclic",
"comments": "11 pages",
"journal-ref": "",
"doi": "",
"report-no": "",
"categories": "math.CO",
"license": "",
"abstract": " We show that a determinant of Stirling cycle numbers counts unlabeled acyclic\nsingle-source automata.",
"versions": [
{
"created": "Sat, 31 Mar 2007 03:16:14 GMT",
"version": "v1"
}
],
"update_date": "2007-05-23",
"authors_parsed": [
[
"Callan",
"David"
]
]
}
1 row in set. Elapsed: 0.009 sec.
input_format_allow_errors_num と input_format_allow_errors_ratio を使用できます。さらに、推論を補助するために hints を指定することもできます。
前の例では JSON を使用しましたが、そこではキー名と型があらかじめ決まっている静的なものでした。しかし、実際にはそうでないことがよくあります。キーが追加されたり、キーの型が変わったりすることがあります。これはオブザーバビリティデータのようなユースケースでは一般的です。
ClickHouse では、専用の JSON 型によってこれに対応しています。
JSON が非常に動的で、一意なキーが多く、同じキーに複数の型が現れることが分かっている場合は、JSONEachRow でスキーマ推論を使い、各キーに対応するカラムを推論しようとすることは推奨しません。データが改行区切りの 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"
}
}
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz')
-- 結果は省略
9 rows in set. Elapsed: 127.066 sec.
JSONEachRow フォーマットが使われていることです。これは JSON の各キーごとにカラム型を 1 つ推論 しようとするもので、つまり JSON 型を使わずに、データに静的なスキーマを適用しようとしていることになります。
一意なカラムが数千もある場合、この推論方法では時間がかかります。代わりに、JSONAsObject フォーマットを使用できます。
JSONAsObject は入力全体を 1 つの JSON オブジェクトとして扱い、JSON 型の単一カラムに格納するため、動的に変化しやすい JSON ペイロードやネストされた JSON ペイロードにより適しています。
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz', 'JSONAsObject')
SETTINGS describe_compact_output = 1
┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘
1 row in set. Elapsed: 0.005 sec.
sample.json ファイルを考えてみましょう。
この場合、ClickHouse は型の競合を吸収し、カラム a を Nullable(String) として扱えます。
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/sample.json')
SETTINGS describe_compact_output = 1
┌─name─┬─type─────────────┐
│ a │ Nullable(String) │
└──────┴──────────────────┘
1 row in set. Elapsed: 0.081 sec.
DESCRIBE コマンドは失敗します。
DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json')
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 JSON format file. Error:
Code: 53. DB::Exception: Automatically defined type Tuple(b Int64) for column 'a' in row 1 differs from type defined by previous rows: Int64. You can specify the type for this column using setting schema_inference_hints.
JSONAsObject は各行を単一の JSON 型 (同じカラムに複数の型を持たせることをサポート) として扱います。これは不可欠です。
DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json', JSONAsObject)
SETTINGS enable_json_type = 1, describe_compact_output = 1
┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘
1 row in set. Elapsed: 0.010 sec.
