VARIANT に格納されている半構造化データに関する考慮事項¶
このトピックでは、半構造化データを含んでいる VARIANT 値をロードして作業するためのベストプラクティス、一般的なガイドライン、および重要な考慮事項について説明します。これは、明示的に構築された 階層データ、または JSON、Avro、 ORC、Parquetなどの半構造化データ形式からロードしたデータにすることができます。このトピックの情報は、XML データには必ずしも適用されません。
このトピックの内容：
データサイズの制限¶
VARIANT 値の非圧縮データの最大サイズは16 MB です。しかし実際には、内部的なオーバーヘッドのために最大サイズは小さくなるのが普通です。最大サイズは保存されるオブジェクトにも依存します。
詳細については、 VARIANT をご参照ください。
16 MB より大きいデータをロードするための準備については、 ロード前に16 MB より大きいオブジェクトのサイズを縮小する を参照してください。
VARIANT 列への半構造化データの格納とネスト構造のフラット化¶
半構造化データに対して実行する操作の種類がまだわからない場合、現時点ではデータを VARIANT 列に保存することをSnowflakeはお勧めします。
ほとんどが規則的でネイティブから半構造化形式のデータ型（例: JSON 形式用の文字列と整数）のみを使用するデータの場合、リレーショナルデータと VARIANT 列のデータに対するストレージ要件とクエリパフォーマンスは非常に似ています。
プルーニングを改善し、ストレージの消費を抑えるために、半構造化データに以下が含まれる場合は、 OBJECT とキーデータを別々のリレーショナル列にフラット化することをお勧めします。
日付とタイムスタンプ、特に文字列値としての ISO 8601 以外の日付とタイムスタンプ
文字列内の数字
配列
非ネイティブ値（JSON での日付やタイムスタンプなど）は、 VARIANT 列にロードされると文字列として格納されるため、これらの値に対する操作は、対応するデータ型のリレーショナル列に格納される場合よりも遅くなり、より多くのスペースを消費します。
データのユースケースがわかっている場合は、一般的なデータセットでテストを実行します。データセットをテーブルの VARIANT 列にロードします。 FLATTEN 関数を使用して、クエリする予定の OBJECTs とキーを別のテーブルに抽出します。両方のテーブルに対して一般的なクエリセットを実行して、どの構造が最高のパフォーマンスを提供するかを確認します。
NULL 値¶
Snowflakeは、半構造化データで2種類の NULL 値をサポートしています。
SQL NULL: SQL NULL は、半構造化データ型の場合も、構造化データ型の場合と同じことを意味します。値が欠落している、または不明です。
JSON null（「VARIANT NULL」と呼ばれる場合あり）: VARIANT 列では、 SQL NULL 値と区別するために JSON null値が「null」という単語を含む文字列として保存されます。
次の例では、 SQL NULL と JSON nullを対比しています。
SELECT PARSE_JSON(NULL) AS "SQL NULL", PARSE_JSON('null') AS "JSON NULL", PARSE_JSON('[ null ]') AS "JSON NULL", PARSE_JSON('{ "a": null }'):a AS "JSON NULL", PARSE_JSON('{ "a": null }'):b AS "ABSENT VALUE"; +----------+-----------+-----------+-----------+--------------+ | SQL NULL | JSON NULL | JSON NULL | JSON NULL | ABSENT VALUE | |----------+-----------+-----------+-----------+--------------| | NULL | null | [ | null | NULL | | | | null | | | | | | ] | | | +----------+-----------+-----------+-----------+--------------+
VARIANT "null" 値を SQL NULL に変換するには、文字列としてキャストします。例:
SELECT PARSE_JSON('{ "a": null }'):a, TO_CHAR(PARSE_JSON('{ "a": null }'):a); +-------------------------------+----------------------------------------+ | PARSE_JSON('{ "A": NULL }'):A | TO_CHAR(PARSE_JSON('{ "A": NULL }'):A) | |-------------------------------+----------------------------------------| | null | NULL | +-------------------------------+----------------------------------------+
半構造化データファイルと列指向化¶
半構造化データが VARIANT 列に挿入されると、Snowflakeは特定のルールを使用して、可能な限り多くのデータを列指向形式で抽出します。残りのデータは、解析された半構造化構造の単一の列として格納されます。
デフォルトでは、Snowflakeはテーブルごと、パーティションごとに最大200個の要素を抽出します。この制限を増やすには、 Snowflakeサポート にお問い合わせください。
抽出されない要素¶
現在、次の特性を持つ要素は列に抽出 されません。
単一の「null」値を含む要素は列に抽出されません。これは、値が欠落している要素ではなく、列指向形式で表される「null」値をともなう要素に適用されます。
このルールは、情報が失われないことを保証します（つまり、 VARIANT「null」値と SQL NULL 値の差は失われません）。
複数のデータ型を含む要素。例：
1行の
foo要素には数字が含まれています。{"foo":1}
別の行の同じ要素には文字列が含まれます。
{"foo":"1"}
抽出がクエリに与える影響¶
半構造化要素に対してクエリを実行すると、Snowflakeの実行エンジンは、要素が抽出されたかどうかによって異なる動作をします。
要素が列に抽出された場合、エンジンは抽出された列のみをスキャンします。
要素が列に抽出 されなかった 場合、実行エンジンは JSON 構造全体をスキャンし、各行で構造を走査して値を出力する必要があります。これはパフォーマンスに影響します。
抽出されなかった要素へのパフォーマンスの影響を避けるには、次を行います。
それらをロードする 前に、「null」値を含む半構造化データ要素をリレーショナル列に抽出します。
または、ファイルの「null」値が欠損値を示しており、他の特別な意味がない場合、半構造化データファイルをロードするときに ファイル形式オプション STRIP_NULL_VALUES を TRUE に設定することをお勧めします。このオプションは、「null」値を含む OBJECT 要素または ARRAY 要素を削除します。
それぞれの一意の要素が、その形式に固有である単一のデータ型の値を格納していることを確認してください（例えば、 JSON の文字列または数）。
NULL 値の解析¶
VARIANT "null" キー値から SQL NULL 値を出力するには、 TO_CHAR , TO_VARCHAR 関数を使用して値を文字列としてキャストします。例:
SELECT column1
, TO_VARCHAR(PARSE_JSON(column1):a)
FROM
VALUES('{"a" : null}')
, ('{"b" : "hello"}')
, ('{"a" : "world"}');
+-----------------+-----------------------------------+
| COLUMN1 | TO_VARCHAR(PARSE_JSON(COLUMN1):A) |
|-----------------+-----------------------------------|
| {"a" : null} | NULL |
| {"b" : "hello"} | NULL |
| {"a" : "world"} | world |
+-----------------+-----------------------------------+