メインコンテンツまでスキップ
メインコンテンツまでスキップ

JSON スキーマの推測

ClickHouseは、JSONデータの構造を自動的に特定できます。これを使用して、clickhouse-localやS3バケット上のディスクでJSONデータを直接クエリしたり、データをClickHouseにロードする前にスキーマを自動的に作成することができます。

型推測を使用するタイミング​

  • 一貫した構造 - 型を推測するデータには、あなたが興味を持つすべてのカラムが含まれている必要があります。型推測後に追加されたカラムを含むデータは無視され、クエリを実行できません。
  • 一貫した型 - 特定のカラムのデータ型は互換性がある必要があります。
重要

より動的なJSON(例えば、Kubernetesのログで十分な通知なしに新しいキーが追加される場合)をお持ちの場合は、JSONスキーマの設計を読むことをお勧めします。

型の検出​

以前の例では、NDJSON形式のPython PyPIデータセットのシンプルなバージョンを使用しました。このセクションでは、ネストされた構造を持つより複雑なデータセット - 250万件の学術論文を含むarXivデータセットを探ります。このデータセットの各行は、公開された学術論文を表します。以下に例となる行を示します:

このデータは、以前の例よりもはるかに複雑なスキーマを必要とします。以下に、このスキーマを定義するプロセスを示し、TupleやArrayのような複雑な型を導入します。

このデータセットは、s3://datasets-documentation/arxiv/arxiv.json.gzの公共S3バケットに保存されています。

上記のデータセットには、ネストされたJSONオブジェクトが含まれています。ユーザーはスキーマをドラフトしてバージョン管理する必要がありますが、推測によりデータから型を推測できます。これにより、スキーマDDLが自動生成され、手動でスキーマを構築する必要がなくなり、開発プロセスが加速します。

自動フォーマット検出

スキーマを検出するだけでなく、JSONスキーマ推測はファイル拡張子と内容からデータのフォーマットを自動的に推測します。上記のファイルは、その結果NDJSONとして自動的に検出されます。

s3 functionを使用し、DESCRIBEコマンドを実行すると、推測される型が表示されます。

NULLを避ける

多くのカラムが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ファイルをクエリでき、アドホックデータ分析のタスクを加速します。

テーブルの作成​

スキーマ推測を利用してテーブルのスキーマを作成できます。以下の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上のファイルを使用してテーブルスキーマを作成しました。ユーザーは、1行のスニペットからスキーマを作成したい場合があります。これは、以下のようにformat関数を使用することで実現できます:

JSONデータのロード​

前述のコマンドでデータをロードできるテーブルが作成されました。次に、以下のINSERT INTO SELECTを使用して、テーブルにデータを挿入できます:

他のソースからデータをロードする例（例えばファイルなど）については、こちらを参照してください。

データがロードされたら、クエリを実行でき、オプションでPrettyJSONEachRow形式を使用して、元の構造のまま行を表示できます:

エラー処理​

時には、悪いデータが存在することがあります。特定のカラムが正しい型を持っていないか、不適切にフォーマットされたJSONがあります。この場合、設定input_format_allow_errors_ratioを使用して、データが挿入エラーを引き起こす場合に無視される行数を許可できます。さらに、推測を補助するためにヒントを提供できます。

さらなる読み物​

データ型推測について詳しく学びたい場合は、こちらのドキュメントページを参照してください。