半構造化データのクエリ¶
このトピックでは、特別な演算子と関数を使用して、 VARIANT に格納されている複雑な階層データをクエリする方法について説明します。
（ARRAYs と OBJECTs から値を抽出する方法の簡単な例については、 インデックスまたはスライスによる配列の要素へのアクセス と キーによる OBJECT の要素へのアクセス をご参照ください。）
通常、階層データは、サポートされている次のデータ形式のいずれかから VARIANT にインポートされています。
JSON
Avro
ORC
Parquet
XML データ (例えば、 XML データ形式から始まり、 PARSE_XML を呼び出すことで OBJECT 値に変換されたデータ) のクエリに関する情報については、 XML データのクエリ例 および XMLGET を参照してください。
Tip
検索最適化サービスを利用して、クエリのパフォーマンスを向上させることができます。詳細については、 検索最適化サービス をご参照ください。
このトピックの内容:
例で使用されるサンプルデータ¶
特に断りのない限り、このトピックの例では、 src という名前の単一の VARIANT 列を含む car_sales という名前のテーブルを参照しています。この VARIANT には、ネストされた ARRAYs と OBJECTs が含まれています。
テーブルを作成してロードします。
CREATE OR REPLACE TABLE car_sales
(
src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
"date" : "2017-04-28",
"dealership" : "Valley View Auto Sales",
"salesperson" : {
"id": "55",
"name": "Frank Beasley"
},
"customer" : [
{"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
],
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
}'),
('{
"date" : "2017-04-28",
"dealership" : "Tindel Toyota",
"salesperson" : {
"id": "274",
"name": "Greg Northrup"
},
"customer" : [
{"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
],
"vehicle" : [
{"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
]
}') v;
データを選択します。
SELECT * FROM car_sales;
+-------------------------------------------+
| SRC |
|-------------------------------------------|
| { |
| "customer": [ |
| { |
| "address": "San Francisco, CA", |
| "name": "Joyce Ridgely", |
| "phone": "16504378889" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Valley View Auto Sales", |
| "salesperson": { |
| "id": "55", |
| "name": "Frank Beasley" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "paint protection" |
| ], |
| "make": "Honda", |
| "model": "Civic", |
| "price": "20275", |
| "year": "2017" |
| } |
| ] |
| } |
| { |
| "customer": [ |
| { |
| "address": "New York, NY", |
| "name": "Bradley Greenbloom", |
| "phone": "12127593751" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Tindel Toyota", |
| "salesperson": { |
| "id": "274", |
| "name": "Greg Northrup" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "rust proofing", |
| "fabric protection" |
| ], |
| "make": "Toyota", |
| "model": "Camry", |
| "price": "23500", |
| "year": "2017" |
| } |
| ] |
| } |
+-------------------------------------------+
半構造化データの走査¶
VARIANT 列名と任意の第1レベル要素の間にコロン : を挿入します。 <列>:<第1レベル要素>。
注釈
次の例では、クエリ出力は VARCHARではなく VARIANTであるため、クエリ出力は二重引用符で囲まれています。（VARIANT 値は文字列ではありません。 VARIANT 値は文字列を 含みます。）演算子 : および後続の . および [] は、常に文字列を含む VARIANT 値を返します。
たとえば、すべてのディーラー名のリストを取得します。
SELECT src:dealership
FROM car_sales
ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP |
|--------------------------|
| "Tindel Toyota" |
| "Valley View Auto Sales" |
+--------------------------+
JSON オブジェクトの要素にアクセスするには、次の2つの方法があります。
重要
使用する表記法に関係なく、列名では大文字と小文字が区別されませんが、要素名では大文字と小文字が区別されます。例えば、次のリストでは、最初の2つのパスは同等ですが、3番目のパスは同等ではありません。
src：salesperson.name
SRC:salesperson.name
SRC:Salesperson.Name
ドット表記¶
ドット表記を使用して、JSON オブジェクトのパスを走査します。 <列>:<第1レベル要素>.<第2レベル要素>.<第3レベル要素>。オプションで、要素名を二重引用符で囲みます。 <列>:"<第1レベル要素>"."<第2レベル要素>"."<第3レベル要素>"。
注釈
JSON キー（要素名）のルールは、Snowflake SQL 識別子のルールとは異なります。
Snowflake SQL 識別子のルールの詳細については、 識別子の要件 をご参照ください。
JSON キーの詳細については、http://json.orgの「文字列」の説明をご参照ください。
要素名がSnowflake SQL 識別子ルールに準拠していない場合、例えば、スペースが含まれている場合は、名前を二重引用符で囲む 必要があります 。以下は、二重引用符で囲まないと有効なSnowflake識別子名にならない、有効な JSON 要素名の例です（すべてが上記のcar_salesの例ではありません）。
-- This contains a blank.
SELECT src:"company name" FROM partners;
-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;
-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;
車を販売したすべての営業担当者の名前を取得します。
SELECT src:salesperson.name
FROM car_sales
ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------+
かっこ表記¶
または、かっこ表記を使用して、オブジェクトのパスを走査します。 <列>['<第1レベル要素>']['<第2レベル要素>']。要素名を一重引用符で囲みます。値は文字列として取得されます。
車を販売したすべての営業担当者の名前を取得します。
SELECT src['salesperson']['name']
FROM car_sales
ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------------+
繰り返し要素の単一インスタンスの取得¶
配列参照に番号付き述語（0から始まる）を追加して、繰り返し配列内の子要素の特定の番号付きインスタンスを取得します。
繰り返し配列の子要素の すべての インスタンスを取得するには、配列をフラット化する必要があることにご注意ください。このトピックの FLATTEN関数を使用した配列の解析 の例をご参照ください。
各販売の車両の詳細を取得します。
SELECT src:customer[0].name, src:vehicle[0]
FROM car_sales
ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0] |
|----------------------+-------------------------|
| "Bradley Greenbloom" | { |
| | "extras": [ |
| | "ext warranty", |
| | "rust proofing", |
| | "fabric protection" |
| | ], |
| | "make": "Toyota", |
| | "model": "Camry", |
| | "price": "23500", |
| | "year": "2017" |
| | } |
| "Joyce Ridgely" | { |
| | "extras": [ |
| | "ext warranty", |
| | "paint protection" |
| | ], |
| | "make": "Honda", |
| | "model": "Civic", |
| | "price": "20275", |
| | "year": "2017" |
| | } |
+----------------------+-------------------------+
販売された各車の価格を取得します。
SELECT src:customer[0].name, src:vehicle[0].price
FROM car_sales
ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500" |
| "Joyce Ridgely" | "20275" |
+----------------------+----------------------+
値の明示的なキャスト¶
VARIANT から値を抽出する場合は、値を目的のデータ型に明示的にキャストできます。たとえば、価格を数値として抽出し、それらに対して計算を実行できます。
SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
FROM car_sales
ORDER BY tax;
+--------+
| TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+
デフォルトでは、 VARCHARs、 DATEs、 TIMEs、および TIMESTAMPs が VARIANT 列から取得される場合、値は二重引用符で囲まれます。値を明示的にキャストすると、二重引用符を削除できます。例:
SELECT src:dealership, src:dealership::VARCHAR
FROM car_sales
ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota" | Tindel Toyota |
| "Valley View Auto Sales" | Valley View Auto Sales |
+--------------------------+-------------------------+
VARIANT 値のキャストの詳細については、 VARIANT データの挿入 をご参照ください。
一般的なキャストの詳細については、 データ型の変換 をご参照ください。
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レベル上の値。
FLATTEN 関数を使用した配列の解析¶
FLATTEN 関数を使用して配列を解析します。FLATTEN は、 VARIANT、 OBJECT、または ARRAY 列の側面ビューを生成するテーブル関数です。関数は各オブジェクトの行を返し、LATERAL 修飾子はデータをオブジェクトの外部の情報と結合します。
すべての顧客の名前と住所を取得します。VARIANT 出力を文字列値にキャストします。
SELECT
value:name::string as "Customer Name",
value:address::string as "Address"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:customer);
+--------------------+-------------------+
| Customer Name | Address |
|--------------------+-------------------|
| Joyce Ridgely | San Francisco, CA |
| Bradley Greenbloom | New York, NY |
+--------------------+-------------------+
FLATTEN 関数を使用したネストされた配列の解析¶
extras 配列は、サンプルデータの vehicle 配列内にネストされます。
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
2番目の FLATTEN 句を追加して、フラット化された vehicle 配列内の extras 配列をフラット化し、販売された車ごとに購入された「エクストラ」を取得します。
SELECT
vm.value:make::string as make,
vm.value:model::string as model,
ve.value::string as "Extras Purchased"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:vehicle) vm
, LATERAL FLATTEN(INPUT => vm.value:extras) ve
ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE | MODEL | Extras Purchased |
|--------+-------+-------------------|
| Honda | Civic | ext warranty |
| Honda | Civic | paint protection |
| Toyota | Camry | ext warranty |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing |
+--------+-------+-------------------+
PARSE_JSON 関数を使用した VARIANT 値としてのテキストの解析¶
PARSE_JSON 関数を使用して、テキストを JSON ドキュメントとして解析します。
入力が NULL の場合は、出力も NULL になります。ただし、入力文字列が null の場合、VARIANT null 値として解釈されます。つまり、結果は SQL NULL ではなく、半構造化形式でnull値を表すために使用される実際の値です。
例については、このトピック内の 例で使用されるサンプルデータ をご参照ください。
GET 関数を使用した値の抽出¶
GET VARIANT、OBJECT、または ARRAY の値を最初の引数として受け入れ、2番目の引数として提供されたパス内の要素の VARIANT 値を抽出します。
GET および ARRAY_SIZE 関数を使用して、VARIANT 列の各配列の最後の要素を計算して抽出します。ARRAY_SIZE は入力配列のサイズを返します。
注釈
この例は、このトピックの他の場所で使用されている car_sales テーブルとは異なります。
CREATE OR replace TABLE colors (v variant);
INSERT INTO
colors
SELECT
parse_json(column1) AS v
FROM
VALUES
('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
v;
SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;
+---------------+-------------------------+
| V | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [ | { |
| { | "b": 255, |
| "b": 0, | "g": 0, |
| "g": 12, | "r": 0 |
| "r": 255 | } |
| }, | |
| { | |
| "b": 0, | |
| "g": 255, | |
| "r": 0 | |
| }, | |
| { | |
| "b": 255, | |
| "g": 0, | |
| "r": 0 | |
| } | |
| ] | |
| [ | { |
| { | "c": 1, |
| "c": 0, | "k": 0, |
| "k": 0, | "m": 1, |
| "m": 1, | "y": 0 |
| "y": 1 | } |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 0, | |
| "y": 1 | |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 1, | |
| "y": 0 | |
| } | |
| ] | |
+---------------+-------------------------+
GET_PATH 関数を使用したパスによる値の抽出¶
GET_PATH、 : 関数を使用して、VARIANT 列から値を抽出します。この関数は GET のバリエーションで、パス名を使用して値を抽出するために使用されます。GET_PATH は、 GET の関数のチェーンと同等です。
各顧客が購入した車のメーカーを取得します。
SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;
+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda" |
| "Toyota" |
+----------------------------------+
半構造化データの走査 では、VARIANT 列の要素を取得するために使用されるパス構文について説明します。構文は、GET または GET_PATH、 : 関数の省略形です。パス構文とは異なり、これらの関数は不規則なパスまたはパス要素を処理できます。
次のクエリは同じ結果を生成します。
SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;
SELECT src:vehicle[0].make FROM car_sales;
ステージングされたデータファイルからの配列の直接解析¶
contacts.json.gz という名前のステージングされたファイルに、次のデータが含まれていると想定します。
{
"root": [
{
"employees": [
{
"firstName": "Anna",
"lastName": "Smith"
},
{
"firstName": "Peter",
"lastName": "Jones"
}
]
}
]
}
また、 my_json_format という名前のファイル形式の定義に、 TYPE=JSON が含まれると想定します。
ステージングされたファイルにある最初の従業員の名前をクエリします。この例では、ファイルは customers テーブルステージにありますが、任意の内部（つまり、Snowflake）または外部ステージに配置することもできます。
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;
+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '|| |
| S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME|| |
| ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith |
+----------------------------------------------+
Snowflakeの高階関数でラムダ関数をデータ上で使用する¶
Snowflakeの高階関数を使用すると、Lambda関数を使用して半構造化データおよび構造化データをフィルタリング、削減、変換できます。Snowflake高次関数を呼び出すときは、 配列 で指定されたデータを操作するLambda関数を作成するためにLambda式を使用します。Snowflake高次関数は、データ操作や高度な分析を実行するための簡潔で読みやすく、効率的な方法を提供します。
以下の高次関数が利用可能です。
高次関数の利点¶
データアナリティクスで半構造化データを使用する場合、通常は配列をループし、配列内の各値に対してアクションを実行する必要があります。これらの操作は、Snowflake高次関数の呼び出しで実行できます。これらの高次関数には、以下のようなメリットがあります。
高度なアナリティクスを合理化 - 配列要素に対する反復処理を簡素化することで、関数はデータのフィルタリング、削減、変換のためのカスタムロジックの実装を容易にし、分析プロセスを合理化します。高次関数がない場合、この種の操作にはLATERALFLATTENの操作かユーザー定義関数 (UDFs) が必要です。
開発者体験の向上 - 高次関数は、操作ロジックをLambda式にカプセル化し、より読みやすく保守性の高い SQL ステートメントを可能にします。高次関数を使うことで、冗長で複雑なSQLクエリを書く必要がなくなります。
不要なUDFsを回避 - 高次関数を使用すると、その場限りの配列操作ロジック用にUDFsを作成、維持、管理する必要がなくなります。これらの関数は、オーバーヘッドを削減し、データ操作プロセスを簡素化することができます。
Lambda式¶
Lambda式は、引数を受け取って値を返す短いコードブロックです。Lambda式では、Lambda演算子（->）の左辺に引数を、右辺に式を指定します。Lambda式を使って、さまざまな処理を行うことができます。
例えば、Lambda式を使って数値出力を生成することができます。次のLambda式は要素を2倍にします。
a -> a * 2
Lambda式を使って要素をフィルターし、フィルター条件が TRUE を返す要素を返すことができます。たとえば、次のLambda式は、 value が 50 より大きい要素を返します。
a -> a:value > 50
Lambda式を使って要素にテキストを追加することができます。例えば、次のLambda式は、 some string というテキストを要素に追加します。
a -> a || ' some string'
Lambda引数のデータ型を指定できます。例えば、次のLambda式は2つの値 INTEGER を指定し、それらを加算します。
(x INT, y INT) -> (x + y)
Lambda式では関数呼び出しを使うことができます。例えば、次のLambda式は UPPER 関数を呼び出します。
a -> UPPER(a)
Lambda式では 無相関スカラーサブクエリ を実行できます。例えば、次のLambda式はこのようなサブクエリを含みます。
a -> a + (SELECT MAX(c1) FROM mytable)
Lambda式では ユーザー定義の SQL 関数 を呼び出すことができます。例えば、次のLambda式は mysqlfunction 関数を呼び出します。
a -> mysqlfunction(5)
制限事項¶
Lambda式は、独立したオブジェクトとしてはサポートされていません。これらはSnowflakeの高階関数の引数として指定しなければなりません。
Lambda式は匿名でなければなりません。名前付き関数は、Snowflakeの高階関数へのLambda引数として渡すことはできません。
Lambda式は、組み込み関数（集約関数とウィンドウ関数を除く）、 SQL ユーザー定義関数、および無相関スカラーサブクエリのみを受け付けます。SQL 以外の言語で作成されたユーザー定義関数、ネストされたコンテキスト（Snowflake Scripting変数など）の参照、 CTE 式、現在のクエリブロックまたはネストされたクエリブロック内の列参照、ユーザー定義関数の引数、および相関サブクエリはサポートしていません。