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 とキーを別のテーブルに抽出します。両方のテーブルに対して一般的なクエリセットを実行して、どの構造が最高のパフォーマンスを提供するかを確認します。

キー値のキャスト¶

VARIANT 列からキー値を抽出する場合は、予期しない結果を避けるために、値を（:: 表記を使用して）目的のデータ型にキャストします。例えば、キャストせずに文字列のキー値を抽出すると、結果は二重引用符で囲まれます（VARIANT 値に文字列が含まれ、異なる型ではないことを表す。つまり、 "1" は文字列で、 1 は値）。

SELECT col1:city;

+----------------------+
| CITY                 |
|----------------------|
| "Los Angeles"        |
+----------------------+

SELECT col1:city::string;

+----------------------+
| CITY                 |
|----------------------|
| Los Angeles          |
+----------------------+

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は特定のルールに基づいて、可能な限り多くのデータを列指向形式で抽出します。残りは、解析された半構造化構造の単一の列として格納されます。現在、次の特性を持つ要素は列に抽出 されません ：

• 単一の「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 の文字列または数）。

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

WHERE 句での FLATTEN を使用した結果のフィルター処理¶

FLATTEN 関数は、ネストされた値を個別の列に展開します。この関数を使用して、 WHERE 句でクエリ結果をフィルター処理できます。

次の例では、WHERE 句に一致するキーと値のペアを返し、別々の列に表示します。

CREATE TABLE pets (v variant);

INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');

SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';

+-------------------------+---------+--------------+
| V                       | KEY     | VALUE        |
|-------------------------+---------+--------------|
| {                       | species | "dog"        |
|   "is_dog": "true",     |         |              |
|   "name": "Fido",       |         |              |
|   "species": "dog"      |         |              |
| }                       |         |              |
| {                       | name    | "dog terror" |
|   "is_dog": "false",    |         |              |
|   "name": "dog terror", |         |              |
|   "species": "cat"      |         |              |
| }                       |         |              |
+-------------------------+---------+--------------+

FLATTEN を使用した異なるキー名のリスティング¶

なじみのない半構造化データを操作する場合は、 OBJECT のキー名がわからない可能性があります。RECURSIVE 引数付きの FLATTEN 関数を使用して、 OBJECT 内のネストされた要素のすべてにある個別のキー名のリストを返すことができます。

SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
  TYPEOF(f.value) AS "Type",
  COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;

REGEXP_REPLACE 関数は、配列インデックス値（[0] など）を削除し、それらを角括弧（[]）に置き換えて配列要素をグループ化します。

例:

{"a": 1, "b": 2, "special" : "data"}   <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"}    <----row 2 of VARIANT column

Output from query:

+---------+---------+-------+
| Path    | Type    | Count |
|---------+---------+-------|
| a       | INTEGER |     1 |
| b       | INTEGER |     1 |
| c       | INTEGER |     1 |
| d       | INTEGER |     1 |
| normal  | VARCHAR |     1 |
| special | VARCHAR |     1 |
+---------+---------+-------+

FLATTEN の使用による、 OBJECT でのパスのリスト¶

FLATTEN を使用した異なるキー名のリスティング に関連して、FLATTEN 関数と RECURSIVE 引数を使用して、 OBJECT 内のすべてのキーとパスを取得できます。

次のクエリは、 VARIANT 列に格納されているすべてのデータ型のキー、パス、および値（VARIANT「null」値を含む）を返します。このコードは、 VARIANT 列の各行に OBJECT が含まれていることを前提としています。

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.index,
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;

次のクエリは最初のクエリに似ていますが、ネストされた OBJECTs と ARRAYs を除外します。

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');

クエリは次の値を返します。

<バリアント列>

VARIANT 列の行として格納される OBJECT。

シーケンス

行のデータに関連付けられた一意のシーケンス番号。

キー

データ構造内の値に関連付けられた文字列。

パス

データ構造内の要素へのパス。

レベル

データ構造内のキーと値のペアのレベル。

型

値のデータ型。

インデックス

データ構造内の要素のインデックス。ARRAY 値にのみ適用されます。それ以外の場合は NULL です。

現在のレベルの値

データ構造の現在のレベルの値。

レベル上の値

データ構造の1レベル上の値。