VARIANT に格納されている半構造化データに関する考慮事項¶
このトピックでは、半構造化データを含んでいる VARIANT 値をロードして作業するためのベストプラクティス、一般的なガイドライン、および重要な考慮事項について説明します。これは、明示的に構築された 階層データ、または JSON、Avro、 ORC、Parquetなどの半構造化データ形式からロードしたデータにすることができます。このトピックの情報は、XML データには必ずしも適用されません。
このトピックの内容:
データサイズの制限¶
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 | | | | | | ] | | | +----------+-----------+-----------+-----------+--------------+
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は特定のルールに基づいて、可能な限り多くのデータを列指向形式で抽出します。残りは、解析された半構造化構造の単一の列として格納されます。現在、次の特性を持つ要素は列に抽出 されません :
単一の「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 |
+-----------------+-----------------------------------+
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レベル上の値。