VARIANT に格納されている半構造化データに関する考慮事項¶

データサイズの制限¶

VARIANT データ型は、個々の行に16 MB のサイズ制限を課します。

一部の半構造化データ形式（例: JSON）では多くの場合、データセットは複数のドキュメントの単純な連結です。一部のソフトウェアからの JSON 出力は、複数の記録を含む単一の巨大な配列で構成されています。両方ともサポートされていますが、ドキュメントを改行またはカンマで区切る必要はありません。

データが 16 MB を超える場合は、 COPY INTO <テーブル> コマンドの STRIP_OUTER_ARRAY ファイル形式オプションを有効にして、外部配列構造を削除し、記録を別のテーブル行にロードします。

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

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    |           |              |
|          |           | ]         |           |              |
+----------+-----------+-----------+-----------+--------------+

Copy

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                                   |
+-------------------------------+----------------------------------------+

Copy

半構造化データファイルと列指向化¶

半構造化データが VARIANT 列に挿入されると、Snowflakeは特定のルールを使用して、可能な限り多くのデータを列指向形式で抽出します。残りのデータは、解析された半構造化構造の単一の列として格納されます。

デフォルトでは、Snowflakeはテーブルごと、パーティションごとに最大200個の要素を抽出します。この制限を増やすには、 Snowflakeサポート にお問い合わせください。

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                             |
+-----------------+-----------------------------------+