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

このトピックでは、半構造化データを含んでいる VARIANT 値をロードして作業するためのベストプラクティス、一般的なガイドライン、および重要な考慮事項について説明します。これは、明示的に構築された 階層データ、または JSON、Avro、 ORC、Parquetなどの半構造化データ形式からロードしたデータにすることができます。このトピックの情報は、XML データには必ずしも適用されません。

このトピックの内容:

データサイズの制限

VARIANT の最大サイズは16 MB です。しかし実際には、内部的なオーバーヘッドのために最大サイズは小さくなるのが普通です。最大サイズは保存されるオブジェクトにも依存します。

詳細については、 VARIANT をご参照ください。

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

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

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

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サポート にお問い合わせください。

抽出されない要素

現在、次の特性を持つ要素は列に抽出 されません

  • 単一の「null」値を含む要素は列に抽出されません。これは、値が欠落している要素ではなく、列指向形式で表される「null」値をともなう要素に適用されます。

    このルールは、情報が失われないことを保証します(つまり、 VARIANT「null」値と SQL NULL 値の差は失われません)。

  • 複数のデータ型を含む要素。例:

    1行の foo 要素には数字が含まれています。

    {"foo":1}
    
    Copy

    別の行の同じ要素には文字列が含まれます。

    {"foo":"1"}
    
    Copy

抽出がクエリに与える影響

半構造化要素に対してクエリを実行すると、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                             |
+-----------------+-----------------------------------+
Copy