clickhouse-localで直接クエリしたり、ClickHouseにデータをロードする前にスキーマを自動的に作成したりできます。
型推論を使用する場合
- 構造が一定している - 型を推論しようとしているデータに、必要なすべてのキーが含まれている場合です。型推論は、データを最大行数または最大バイト数までサンプリングして行われます。サンプル範囲より後のデータに追加のカラムがあっても、それらは無視されるためクエリできません。
- 型が一定している - 特定のキーのデータ型には互換性が必要です。つまり、ある型から別の型へ自動的に型変換できる必要があります。
型の検出
NDJSON フォーマットの Python PyPI dataset の簡易版を使用してきました。この節では、より複雑な入れ子構造を持つデータセット、つまり 250 万件の学術論文を含む arXiv dataset を見ていきます。NDJSON として配布されるこのデータセットでは、各行が公開済みの学術論文 1 件を表します。行の例を以下に示します。
Tuple や Array などの複雑な型を導入します。
このデータセットは、s3://datasets-documentation/arxiv/arxiv.json.gz にある公開 S3 バケットに保存されています。
上記のデータセットには、ネストされた JSON オブジェクトが含まれていることがわかります。スキーマは作成してバージョン管理すべきですが、スキーマ推論を使うと、データから型を推論できます。これにより、スキーマ DDL を自動生成できるため、手動で作成する必要がなくなり、開発プロセスを高速化できます。
フォーマットの自動検出スキーマの検出に加えて、JSON スキーマ推論では、ファイル拡張子と内容からデータのフォーマットも自動的に推論します。その結果、上記のファイルは自動的に NDJSON として検出されます。
DESCRIBE コマンドとともに使用すると、推論される型を確認できます。
NULL を避ける多くのカラムが Nullable として検出されていることがわかります。Nullable 型は、どうしても必要な場合を除いて使用しないことを推奨します。Nullable を適用するタイミングの挙動は、schema_inference_make_columns_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 のクエリ
テーブルの作成
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) で制御されます。検出結果が正しくない場合は、こちら に記載されているようにヒントを指定できます。
スニペットからテーブルを作成する
JSON データの取り込み
INSERT INTO SELECT を使ってデータをテーブルに挿入できます。
PrettyJSONEachRow を使用すると、行を元の構造のまま表示できます。
エラーへの対処
input_format_allow_errors_num と input_format_allow_errors_ratio を使用できます。さらに、推論を補助するために hints を指定することもできます。
半構造化データと動的データの操作
JSON 型によってこれに対応しています。
JSON が非常に動的で、一意なキーが多く、同じキーに複数の型が現れることが分かっている場合は、JSONEachRow でスキーマ推論を使い、各キーに対応するカラムを推論しようとすることは推奨しません。データが改行区切りの JSON フォーマットであっても同様です。
次の例では、上記の Python PyPI dataset を拡張したデータセットを使用します。ここでは、ランダムなキーと値のペアを持つ任意の tags カラムを追加しています。
JSONEachRow フォーマットが使われていることです。これは JSON の各キーごとにカラム型を 1 つ推論 しようとするもので、つまり JSON 型を使わずに、データに静的なスキーマを適用しようとしていることになります。
一意なカラムが数千もある場合、この推論方法では時間がかかります。代わりに、JSONAsObject フォーマットを使用できます。
JSONAsObject は入力全体を 1 つの JSON オブジェクトとして扱い、JSON 型の単一カラムに格納するため、動的に変化しやすい JSON ペイロードやネストされた JSON ペイロードにより適しています。
sample.json ファイルを考えてみましょう。
a を Nullable(String) として扱えます。
型変換この型変換は、いくつかの設定で制御できます。上記の例は、設定
input_format_json_read_numbers_as_strings に依存しています。DESCRIBE コマンドは失敗します。
JSONAsObject は各行を単一の JSON 型 (同じカラムに複数の型を持たせることをサポート) として扱います。これは不可欠です。