JSON schema inference
ClickHouseは、JSONデータの構造を自動的に特定できます。これにより、clickhouse-localやS3バケットを介してディスク上のJSONデータを直接クエリすることができ、また、ClickHouseにデータを読み込む前にスキーマを自動的に作成することも可能です。
型推論を使用するタイミング
- 一貫した構造 - タイプを推測するためのデータには、興味のあるすべてのキーが含まれています。タイプ推論は、最大行数またはバイト数までのデータをサンプリングすることに基づいています。サンプル後のデータで追加のカラムがある場合、それらは無視され、クエリすることはできません。
- 一貫した型 - 特定のキーのデータ型は互換性がある必要があります。つまり、一方の型を他方に自動的に強制変換できる必要があります。
もし、新しいキーが追加される動的なJSONがある場合や、同じパスに対して複数の型が可能な場合は、"非構造化データと動的データの扱い"を参照してください。
型の検出
以下の内容は、JSONが一貫した構造を持ち、各パスに対して単一の型を持つと仮定しています。
前述の例では、NDJSON形式のPython PyPIデータセットのシンプルなバージョンを使用しました。このセクションでは、ネストされた構造を持つより複雑なデータセット－2.5百万の学術論文を含むarXivデータセットを探ります。このデータセットの各行は、公開された学術論文を表しています。以下に例を示します：
このデータには、前の例よりも遥かに複雑なスキーマが必要です。以下にこのスキーマの定義プロセスを概説し、TupleやArrayなどの複雑な型を紹介します。
このデータセットは、s3://datasets-documentation/arxiv/arxiv.json.gzというパブリックS3バケットに保存されています。
上記のデータセットにはネストされたJSONオブジェクトが含まれていることがわかります。ユーザーはスキーマをドラフトし、バージョン管理する必要がありますが、推論によりデータから型を推測できます。これにより、スキーマのDDLが自動生成され、手動で作成する必要がなくなり、開発プロセスが加速します。
スキーマを検出するだけでなく、JSONスキーマ推論はファイル拡張子と内容から自動的にデータのフォーマットを推測します。上記のファイルは、その結果としてNDJSONとして自動的に検出されます。
s3関数を使用したDESCRIBEコマンドは、推測される型を示します。
多くのカラムがNullableとして検出されていることがわかります。私たちはNullable型の使用を必要な場合を除いて推奨していません。schema_inference_make_columns_nullableを使用して、Nullableが適用される場合の動作を制御できます。
ほとんどのカラムは自動的にStringとして検出され、update_dateカラムは正しくDateとして検出されました。versionsカラムはArray(Tuple(created String, version String))として生成され、オブジェクトのリストを保存します。authors_parsedはネストされた配列のためにArray(Array(String))として定義されています。
日付や日時の自動検出は、それぞれinput_format_try_infer_datesおよびinput_format_try_infer_datetimesの設定で制御できます（両方ともデフォルトで有効）。オブジェクトをタプルとして推測することは、input_format_json_try_infer_named_tuples_from_objectsの設定で制御されます。他のJSONのスキーマ推論を制御する設定、数値の自動検出などは、こちらで見つけることができます。
JSONのクエリ
以下の内容は、JSONが一貫した構造を持ち、各パスに対して単一の型を持つと仮定しています。
スキーマ推論に依存して、JSONデータをその場でクエリできます。以下では、日付と配列が自動的に検出されるという事実を利用して、各年のトップ著者を見つけます。
スキーマ推論により、スキーマを指定することなくJSONファイルをクエリでき、アドホックなデータ分析タスクを加速することができます。
テーブルの作成
スキーマ推論に依存して、テーブルのスキーマを作成できます。以下のCREATE AS EMPTYコマンドは、テーブルのDDLを推論させ、テーブルを作成します。これはデータを読み込むことはありません：
テーブルスキーマを確認するために、SHOW CREATE TABLEコマンドを使用します：
上記がこのデータの正しいスキーマです。スキーマ推論はデータをサンプリングして読み取り、行ごとにデータを読み取ります。カラムの値はフォーマットに従って抽出され、型を決定するために再帰的なパーサーとヒューリスティクスが使用されます。スキーマ推論において読み取る最大行数とバイト数は、設定input_format_max_rows_to_read_for_schema_inference（デフォルト25000行）およびinput_format_max_bytes_to_read_for_schema_inference（デフォルト32MB）で制御されます。検出が正しくない場合、ユーザーはこちらに記載されているようにヒントを提供できます。
スニペットからのテーブル作成
上記の例では、S3上のファイルを使用してテーブルスキーマを作成しました。ユーザーは単一の行スニペットからスキーマを作成したいかもしれません。これは、以下のようにformat関数を使用して達成できます：
JSONデータの読み込み
以下の内容は、JSONが一貫した構造を持ち、各パスに対して単一の型を持つと仮定しています。
前述のコマンドで、データを読み込むことができるテーブルが作成されました。次に、以下のようにINSERT INTO SELECTを使用してデータをテーブルに挿入できます：
他のソースからのデータの読み込みの例（例：ファイル）については、こちらを参照してください。
データが読み込まれたら、元の構造で行を表示するために形式PrettyJSONEachRowを使用してデータをクエリできます：
エラーの処理
時には、不正なデータを持つことがあります。特定のカラムが正しい型でない場合や、不正にフォーマットされたJSONオブジェクトが考えられます。その場合、設定input_format_allow_errors_numおよびinput_format_allow_errors_ratioを使用して、データが挿入エラーを引き起こす場合に無視できる行の数を許可できます。また、推論を補助するためにヒントを提供することができます。
非構造化データと動的データの扱い
前述の例では、静的でよく知られたキー名と型を持つJSONを使用しました。しかし、これはしばしば当てはまりません。キーが追加されたり、型が変更されたりすることがあります。これは、可観測性データなどのユースケースで一般的です。
ClickHouseは、専用のJSON型を通じてこれに対応します。
もしあなたのJSONが非常に動的で、ユニークなキーが多数あり、同じキーに対して複数の型がある場合、JSONEachRowでスキーマ推論を使用して各キーのカラムを推測することはお勧めしません – たとえデータが改行区切りJSON形式であっても。
以下は、前述のPython PyPIデータセットの拡張バージョンの例です。ここでは、ランダムなキー値ペアを持つ任意のtagsカラムを追加しました。
このデータのサンプルは改行区切りJSON形式で公開されています。このファイルでスキーマ推論を試みると、パフォーマンスが悪く、非常に冗長な応答が得られることがわかります：
ここでの主な問題は、スキーマ推論のためにJSONEachRowフォーマットが使用されていることです。これは、JSONの各キーに対してカラム型を推測しようとします – つまり、JSON型を使用せずにデータに静的なスキーマを適用しようとすることです。
ユニークなカラムが何千もあるため、この推論のアプローチは遅くなります。代わりに、ユーザーはJSONAsObjectフォーマットを使用できます。
JSONAsObjectは、入力全体を単一のJSONオブジェクトとして扱い、それをJSON型の単一カラムに保存します。これにより、非常に動的またはネストされたJSONペイロードに適しています。
このフォーマットは、カラムに複数の型があり、それらが調和できない場合にも重要です。たとえば、次のような改行区切りJSONを持つsample.jsonファイルを考えてください：
この場合、ClickHouseは型の衝突を強制変換し、カラムaをNullable(String)として解決できます。
この型の強制変換は、いくつかの設定を通じて制御できます。上記の例は、設定input_format_json_read_numbers_as_stringsに依存しています。
しかし、互換性のない型も存在します。次の例を考えてみてください：
この場合、ここでの型変換は不可能です。したがって、DESCRIBEコマンドは失敗します：
この場合、JSONAsObjectは各行を単一のJSON型としてみなします（同じカラムが複数の型を持つことをサポートします）。これは不可欠です：
さらなる情報
データ型の推論についてもっと知りたい場合は、こちらのドキュメントページを参照してください。
