半構造化データの考慮事項

このトピックでは、JSON、Avro、ORC、およびParquetデータのロードと操作に関するベストプラクティス、一般的なガイドライン、および重要な考慮事項について説明します。このトピックの情報は、XML データには必ずしも適用されません。

このトピックの内容:

データサイズの制限

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

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

代わりに、 COPY INTO <テーブル> コマンドの STRIP_OUTER_ARRAY ファイル形式オプションを有効にして、外部配列構造を削除し、レコードを別のテーブル行にロードすることをお勧めします。

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

VARIANT 列への半構造化データの保存とネスト構造のフラット化

半構造化データに対して実行する操作の種類が不明な場合は、今のところ VARIANT 列に保存することをお勧めします。ほとんどが規則的でネイティブタイプ(文字列と整数)のみを使用するデータの場合、リレーショナルデータと VARIANT 列のデータに対する操作のストレージ要件とクエリパフォーマンスは非常に似ています。

プルーニングを改善し、ストレージの消費を抑えるために、半構造化データに以下が含まれる場合は、オブジェクトとキーデータを別々のリレーショナル列にフラット化することをお勧めします:

  • 日付とタイムスタンプ、特に文字列値としての ISO 8601 以外の日付とタイムスタンプ

  • 文字列内の数字

  • 配列

日付やタイムスタンプなどの非ネイティブ値は、 VARIANT 列にロードされると文字列として格納されるため、これらの値に対する操作は、対応するデータ型のリレーショナル列に格納される場合よりも遅くなり、より多くのスペースを消費します。

データのユースケースがわかっている場合は、一般的なデータセットでテストを実行します。データセットをテーブルの VARIANT 列にロードします。 FLATTEN 関数を使用して、クエリする予定のオブジェクトとキーを別のテーブルに抽出します。両方のテーブルに対して一般的なクエリセットを実行して、どの構造が最高のパフォーマンスを提供するかを確認します。

Key-Valueのキャスト

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

SELECT col1:city;

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

SELECT col1:city::string;

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

NULL 値

VARIANT 列では、NULL 値は「null」という単語を含む文字列として格納されます。この動作により、「null」値と、存在しない値(SQL NULL を生成する)を区別できます。

例:

select parse_json('{ "a": null}'):a, parse_json('{ "a": null}'):b;

+------------------------------+------------------------------+
| PARSE_JSON('{ "A": NULL}'):A | PARSE_JSON('{ "A": NULL}'):B |
|------------------------------+------------------------------|
| null                         | NULL                         |
+------------------------------+------------------------------+

VARIANT の「null」値を SQL NULL に変換するには、文字列としてキャストします。

例:

select to_char(parse_json('{ "a": null}'):a);

+---------------------------------------+
| TO_CHAR(PARSE_JSON('{ "A": NULL}'):A) |
|---------------------------------------|
| NULL                                  |
+---------------------------------------+

半構造化データファイルと列への変換

半構造化データが VARIANT 列に挿入されると、Snowflakeは特定のルールに基づいて、可能な限り多くのデータを列形式に抽出します。残りは、解析された半構造化構造の単一の列として格納されます。現在、次の特性を持つ要素は列に抽出 されません

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

    このルールにより、情報が失われないようにします。つまり、 VARIANT の「null」値と SQL NULL 値の差が曖昧にならないようにします。

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

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

    {"foo":1}
    

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

    {"foo":"1"}
    

半構造化要素がクエリされると、

  • 要素が列に抽出された場合、Snowflakeの実行エンジン(列状)は抽出された列のみをスキャンします。

  • 要素が列に抽出されなかった場合、実行エンジンは JSON 構造全体をスキャンし、各行で構造を走査して値を出力するため、パフォーマンスが低下します。

このパフォーマンスの低下を回避するには、

  • それらをロードする 前に 、「null」値を含む半構造化データ要素をリレーショナル列に抽出します。

    または、ファイルの「null」値が欠損値を示しており、他の特別な意味がない場合、半構造化データファイルをロードするときに ファイル形式オプション STRIP_NULL_VALUES を TRUE に設定することをお勧めします。このオプションは、「null」値を含むオブジェクト要素または配列要素を削除します。

  • 一意の各要素に、単一のネイティブデータ型(文字列または数値)の値が格納されていることを確認します。

NULL 値の解析

VARIANT「null」Key-Valueから 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                             |
+-----------------+-----------------------------------+

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 を使用した異なるキー名のリスティング

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

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 を使用したオブジェクトでのパスのリスティング

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

次のクエリは、オブジェクトに格納されているすべてのデータ型のキー、パス、および値(VARIANT「null」値を含む)を返します。

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;

次のクエリは最初のクエリに似ていますが、ネストされたオブジェクトと配列を除外します。

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

VARIANT 列の行として格納されるオブジェクト。

シーケンス

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

キー

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

パス

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

レベル

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

値のデータ型。

インデックス

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

現在のレベルの値

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

レベル上の値

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