半構造化データでサポートされている形式¶
このトピックでは、半構造化データでサポートされている形式について説明します。
Snowflakeは、以下の半構造化データ形式をネイティブでサポートしています。具体的には、Snowflakeは、これらの形式のデータファイルをロードおよびアンロードするためのオプションを COPY コマンドで提供します。
JSON¶
JSON とは何ですか？¶
JSON （JavaScript Object Notation）は、JavaScript プログラミング言語のサブセットに基づいた、軽量でプレーンテキストのデータ交換形式です。
JSON データはどのアプリケーションでも生成できます。一般的な例を次に示します。
JavaScript ネイティブメソッドを使用して JSON を生成するアプリケーション。
ライブラリ（通常は拡張機能を使用）を使用して JSON データを生成する非 JavaScript アプリケーション。
アドホック JavaScript ジェネレーター。
JSON ドキュメントの連結（行区切りの場合とそうでない場合があります）。
正式な仕様がないため、実装が異なると大きな違いがあります。これらの違いにより、JSON パーサーの言語定義が厳密な場合、JSON のようなデータセットのインポートは不可能になります。できるだけ問題なく JSON データセットのインポートを行うために、Snowflakeは「受け入れるものに寛容であること」というルールに従います。これは、明確な解釈を可能にする JSON および JSON のような入力の可能な限り広い範囲を受け入れることを意図しています。
このトピックでは、Snowflakeで受け入れる JSON ドキュメントの構文について説明します。
JSON の詳細については、 json.org をご参照ください。
基本的な JSON 構文¶
JSON データは、オブジェクトと配列にグループ化された名前/値のペアの階層的なコレクションです。
コロン
:は、名前と値のペアで名前と値を区切ります。中括弧
{}はオブジェクトを示します。角括弧
[]は配列を示します。コンマ
,は、オブジェクトと配列内のエンティティを区切ります。
名前/値のペア¶
JSON 名前/値のペアは、フィールド名（二重引用符で囲まれた）、コロン、値から構成されます。
例:
{"firstName":"John", "empid":45611}
サポートされているデータ型¶
名前/値のペアの値は次のいずれかです。
数値（整数または浮動小数点）
文字列（二重引用符内）
ブール値（trueまたはfalse）
配列（角括弧内）
オブジェクト（中括弧内）
Null
オブジェクト¶
JSON オブジェクトは中括弧内に書き込みます。オブジェクトには、コンマで区切られた複数の名前/値のペアを含めることができます。例：
{"firstName":"John", "lastName":"Doe"}
配列¶
JSON 配列は角括弧内に書き込みます。配列には、コンマで区切られた複数のオブジェクトを含めることができます。例：
{"employees":[
{"firstName":"John", "lastName":"Doe"},
{"firstName":"Anna", "lastName":"Smith"},
{"firstName":"Peter", "lastName":"Jones"}
]
}
JSON ドキュメントの例¶
FILE NAME： json_sample_data1
3つの単純な従業員記録（オブジェクト）の配列が含まれます。
{"root":[{"employees":[ {"firstName":"John", "lastName":"Doe"}, {"firstName":"Anna", "lastName":"Smith"}, {"firstName":"Peter", "lastName":"Jones"} ]}]}
FILE NAME： json_sample_data2
3つの従業員記録（オブジェクト）とそれに関連する依存データ（子供、子供の名前と年齢、従業員の居住都市、従業員のその都市における居住年数）を含む配列が含まれます。
{"root": [ { "kind": "person", "fullName": "John Doe", "age": 22, "gender": "Male", "phoneNumber": {"areaCode": "206", "number": "1234567"}, "children": [ { "name": "Jane", "gender": "Female", "age": "6" }, { "name": "John", "gender": "Male", "age": "15" } ], "citiesLived": [ { "place": "Seattle", "yearsLived": ["1995"] }, { "place": "Stockholm", "yearsLived": ["2005"] } ] }, {"kind": "person", "fullName": "Mike Jones", "age": 35, "gender": "Male", "phoneNumber": { "areaCode": "622", "number": "1567845"}, "children": [{ "name": "Earl", "gender": "Male", "age": "10"}, {"name": "Sam", "gender": "Male", "age": "6"}, { "name": "Kit", "gender": "Male", "age": "8"}], "citiesLived": [{"place": "Los Angeles", "yearsLived": ["1989", "1993", "1998", "2002"]}, {"place": "Washington DC", "yearsLived": ["1990", "1993", "1998", "2008"]}, {"place": "Portland", "yearsLived": ["1993", "1998", "2003", "2005"]}, {"place": "Austin", "yearsLived": ["1973", "1998", "2001", "2005"]}]}, {"kind": "person", "fullName": "Anna Karenina", "age": 45, "gender": "Female", "phoneNumber": { "areaCode": "425", "number": "1984783"}, "citiesLived": [{"place": "Stockholm", "yearsLived": ["1992", "1998", "2000", "2010"]}, {"place": "Russia", "yearsLived": ["1998", "2001", "2005"]}, {"place": "Austin", "yearsLived": ["1995", "1999"]}]} ] }
Avro¶
Avroとは何ですか？¶
Avroは、Apache Hadoopと併用するために開発されたオープンソースのデータシリアル化および RPC フレームワークです。JSON で定義されたスキーマを利用して、シリアル化されたデータをコンパクトなバイナリ形式で生成します。シリアル化されたデータは任意の宛先（アプリケーションまたはプログラム）に送信でき、スキーマがデータに含まれているため、宛先で簡単に逆シリアル化できます。
Avroスキーマは、スキーマのタイプとスキーマタイプのデータ属性（フィールド名、データタイプなど）を定義する JSON 文字列、オブジェクト、または配列で構成されます。属性は、スキーマタイプによって異なります。配列やマップなどの複雑なデータ型がサポートされています。
Snowflakeは、Avroデータを単一の VARIANT 列に読み取ります。同様のコマンドと関数を使用して、 JSON データと同様に VARIANT 列のデータをクエリできます。
詳細については、 avro.apache.org をご参照ください。
Avroスキーマの例¶
{
"type": "record",
"name": "person",
"namespace": "example.avro",
"fields": [
{"name": "fullName", "type": "string"},
{"name": "age", "type": ["int", "null"]},
{"name": "gender", "type": ["string", "null"]}
]
}
ORC¶
ORC とは何ですか？¶
ORC （最適化された行の列指向）は、Hiveデータを格納するために使用されるバイナリ形式です。ORC は、効率的な圧縮と、以前のHiveファイル形式によるデータの読み取り、書き込み、処理のパフォーマンス向上のために設計されています。ORC の詳細については、 https://orc.apache.org/ をご参照ください。
Snowflakeは ORC データを単一の VARIANT 列に読み取ります。同様のコマンドと関数を使用して、 JSON データと同様に VARIANT 列のデータをクエリできます。
または、 CREATE TABLE AS SELECT ステートメントを使用して、ステージングされた ORC ファイルから列を個別のテーブル列に抽出できます。
注釈
マップデータは、オブジェクトの配列に逆シリアル化されます。例:
"map": [{"key": "chani", "value": {"int1": 5, "string1": "chani"}}, {"key": "mauddib", "value": {"int1": 1, "string1": "mauddib"}}]
ユニオンデータは単一のオブジェクトに逆シリアル化されます。例：
{"time": "1970-05-05 12:34:56.197", "union": {"tag": 0, "value": 3880900}, "decimal": 3863316326626557453.000000000000000000}
ORC データが VARIANT 列にロードされた例¶
+--------------------------------------+
| SRC |
|--------------------------------------|
| { |
| "boolean1": false, |
| "byte1": 1, |
| "bytes1": "0001020304", |
| "decimal1": 12345678.654745, |
| "double1": -1.500000000000000e+01, |
| "float1": 1.000000000000000e+00, |
| "int1": 65536, |
| "list": [ |
| { |
| "int1": 3, |
| "string1": "good" |
| }, |
| { |
| "int1": 4, |
| "string1": "bad" |
| } |
| ] |
| } |
+--------------------------------------+
Parquet¶
Parquetとは何ですか？¶
Parquetは、Hadoopエコシステムのプロジェクト用に設計された、圧縮された効率的な列指向データ表現です。このファイル形式は、ネストされた複雑なデータ構造をサポートし、Dremelレコードシュレッディングおよびアセンブリアルゴリズムを使用します。Parquetファイルはテキストエディタでは開けません。詳細は parquet.apache.org/docs/ をご参照ください。
注釈
Snowflakeは、 Apache Iceberg™ テーブル用のParquetライターV2を使用して作成されたParquetファイル、または ベクトル化スキャナー を使用して作成されたParquetファイルをサポートしています。
読み込みのユースケースに応じて、SnowflakeはParquetデータを単一の VARIANT 列に読み込むか、テーブル列に直接読み込みます （Iceberg互換のParquetファイル からデータを読み込む場合など）。
同様のコマンドと関数を使用して、 JSON データと同様に VARIANT 列のデータをクエリできます。または、CREATE TABLE AS SELECT ステートメントを使用して、ステージングされたParquetファイルから選択した列を個別のテーブル列に抽出できます。
VARIANT 列にロードされたParquetデータの例¶
+------------------------------------------+
| SRC |
|------------------------------------------|
| { |
| "continent": "Europe", |
| "country": { |
| "city": { |
| "bag": [ |
| { |
| "array_element": "Paris" |
| }, |
| { |
| "array_element": "Nice" |
| }, |
| { |
| "array_element": "Marseilles" |
| }, |
| { |
| "array_element": "Cannes" |
| } |
| ] |
| }, |
| "name": "France" |
| } |
| } |
+------------------------------------------+
XML¶
XML とは何ですか？¶
XML （拡張マークアップ言語）は、ドキュメントをエンコードするための一連のルールを定義するマークアップ言語です。当初は、ドキュメントを構成する構造と要素を標準化するために開発された別のマークアップ言語である SGML に基づいていました。
導入以来、XML は当初のドキュメントに対する焦点を超えて成長し、任意のデータ構造の表現や通信プロトコルのベース言語としての機能など、幅広い用途を網羅しています。拡張性、汎用性、使いやすさから、ウェブでのデータ交換で最もよく使用される標準の1つになりました。
XML ドキュメントは、主に次の構成要素で構成されます。
タグ（山括弧
<および>で識別）要素
要素は通常、「開始」タグとそれに対応する「終了」タグで構成され、タグ間のテキストが要素の内容を構成します。要素は、「終了」タグのない「空エレメント」タグで構成することもできます。「開始」タグと「空エレメント」タグには属性が含まれることがあり、要素の特性やメタデータを定義するのに役立ちます。
XML データをクエリする場合、ドル記号演算子 ($) は、その演算子が演算した値の内容を VARIANT 値として返します。要素に対して、その要素の内容が返されます。
要素にテキストが含まれる場合、テキストは VARIANT の値として返されます。
要素に別の要素が含まれている場合、その要素は XML 形式の VARIANT 値として返されます。
要素に一連の要素が含まれる場合は、その配列が JSON 形式の VARIANT 値として返されます。
クエリで VARIANT の値にアクセスするには、以下の演算子を使用します。
$を値の内容に使用します。@を値の名前に使用します。この演算子は、異なる名前の要素を繰り返し処理するときに便利です。名前付き属性の内容には
@attribute_nameを使用します。例えば@attrの場合、属性名はattrです。クエリは、アンパサンドに直接続く名前の属性の内容を返します。属性が見つからない場合は、 NULL が返されます。
XML データをクエリする例については、 XML データのクエリ例 を参照してください。
XML データを扱うには、以下の関数を使用できます。
XML 協力の例¶
以下の例では、 XML データの読み込みとクエリの方法を示します。
XML ドキュメントの読み込み例¶
この例では、次の XML ドキュメントを読み込む方法を示します。
<?xml version="1.0"?>
<!DOCTYPE parts system "parts.dtd">
<?xml-stylesheet type="text/css" href="xmlpartsstyle.css"?>
<parts>
<part count="4">
<item>Spark Plugs</item>
<partnum>A3-400</partnum>
<manufacturer>ABC company</manufacturer>
<price units="dollar"> 27.00</price>
</part>
<part count="1">
<item>Motor Oil</item>
<partnum>B5-200</partnum>
<source>XYZ company</source>
<price units="dollar"> 14.00</price>
</part>
<part count="1">
<item>Motor Oil</item>
<partnum>B5-300</partnum>
<source>XYZ company</source>
<price units="dollar"> 16.75</price>
</part>
<part count="1">
<item>Engine Coolant</item>
<partnum>B6-120</partnum>
<source>XYZ company</source>
<price units="dollar"> 19.00</price>
</part>
<part count="1">
<item>Engine Coolant</item>
<partnum>B6-220</partnum>
<source>XYZ company</source>
<price units="dollar"> 18.25</price>
</part>
</parts>
XML ドキュメントを読み込むには、以下の手順に従います。
XML ドキュメントの内容をファイルシステム上のファイルにコピーします。
この例では、
/examples/xml/ディレクトリにあるauto-parts.xmlという名前のファイルを想定しています。内部ステージングの場所にステージングされたファイル:
PUT FILE:///examples/xml/auto-parts.xml @~/xml_stage;
XML ドキュメント用のテーブルを作成します。
CREATE OR REPLACE TABLE sample_xml_parts(src VARIANT);
ステージングされた XML ファイルをテーブルにロードします。
COPY INTO sample_xml_parts FROM @~/xml_stage FILE_FORMAT=(TYPE=XML) ON_ERROR='CONTINUE';
XML データのクエリ例¶
これらの例では、 XML データをクエリします。
XML のデータを直接クエリします。¶
XML データを含む列をクエリして、 XML ドキュメントを返します。
この例では、 XML ドキュメントの読み込み例 で読み込み中の XML のデータを直接クエリします：
SELECT src FROM sample_xml_parts;
+----------------------------------------------+
| SRC |
|----------------------------------------------|
| <parts> |
| <part count="4"> |
| <item>Spark Plugs</item> |
| <partnum>A3-400</partnum> |
| <manufacturer>ABC company</manufacturer> |
| <price units="dollar">27.00</price> |
| </part> |
| <part count="1"> |
| <item>Motor Oil</item> |
| <partnum>B5-200</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">14.00</price> |
| </part> |
| <part count="1"> |
| <item>Motor Oil</item> |
| <partnum>B5-300</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">16.75</price> |
| </part> |
| <part count="1"> |
| <item>Engine Coolant</item> |
| <partnum>B6-120</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">19.00</price> |
| </part> |
| <part count="1"> |
| <item>Engine Coolant</item> |
| <partnum>B6-220</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">18.25</price> |
| </part> |
| </parts> |
+----------------------------------------------+
演算子を使った XML データのクエリ¶
$ および @ 演算子を使用して、 XML データを含む列をクエリします。
この例では、 $ 演算子を使用して、 XML ドキュメントの読み込み例 で読み込み中の XML データをクエリしています。クエリは、要素の値 ($) と名前 (@) に関するメタデータを表示します。
SELECT src:"$" FROM sample_xml_parts;
+--------------------------------+
| SRC:"$" |
|--------------------------------|
| [ |
| { |
| "$": [ |
| { |
| "$": "Spark Plugs", |
| "@": "item" |
| }, |
| { |
| "$": "A3-400", |
| "@": "partnum" |
| }, |
| { |
| "$": "ABC company", |
| "@": "manufacturer" |
| }, |
| { |
| "$": 27, |
| "@": "price", |
| "@units": "dollar" |
| } |
| ], |
| "@": "part", |
| "@count": 4, |
| "item": 0, |
| "manufacturer": 2, |
| "partnum": 1, |
| "price": 3 |
| }, |
| { |
| "$": [ |
| { |
| "$": "Motor Oil", |
| "@": "item" |
| }, |
| { |
| "$": "B5-200", |
| "@": "partnum" |
| }, |
| { |
| "$": "XYZ company", |
| "@": "source" |
| }, |
| { |
| "$": 14, |
| "@": "price", |
| "@units": "dollar" |
| } |
| ], |
| "@": "part", |
| "@count": 1, |
| "item": 0, |
| "partnum": 1, |
| "price": 3, |
| "source": 2 |
| }, |
| |
| ... |
| |
+--------------------------------+
この例では、 @ 演算子を使用して、同じ XML データをクエリします。クエリはルート要素の名前を表示します。
SELECT src:"@" FROM sample_xml_parts;
+---------+
| SRC:"@" |
|---------|
| "parts" |
+---------+
この例では、 $ 演算子と @ 演算子を使用して、同じ XML データをクエリします。ルート要素の子要素の配列では、クエリは、1番目(0)と2番目(1)のインデックスの要素の count 属性の値を示します。
SELECT src:"$"[0]."@count", src:"$"[1]."@count" FROM sample_xml_parts;
+---------------------+---------------------+
| SRC:"$"[0]."@COUNT" | SRC:"$"[1]."@COUNT" |
|---------------------+---------------------|
| 4 | 1 |
+---------------------+---------------------+
XMLGET 関数を使った XML データのクエリ¶
XMLGET 関数を使用して、 XML データを含む列をクエリします。
この例では、 XML ドキュメントの読み込み例 で読み込み中の XML データをクエリし、 XML データのルート要素内の要素の最初のインスタンスを返します。インスタンス番号は1ベースではなく0ベースです。つまり、以下のクエリは等価です。
SELECT XMLGET(src, 'part') FROM sample_xml_parts;
SELECT XMLGET(src, 'part', 0) FROM sample_xml_parts;
+--------------------------------------------+
| XMLGET(SRC, 'PART') |
|--------------------------------------------|
| <part count="4"> |
| <item>Spark Plugs</item> |
| <partnum>A3-400</partnum> |
| <manufacturer>ABC company</manufacturer> |
| <price units="dollar">27.00</price> |
| </part> |
+--------------------------------------------+
このクエリは、 XML データのルート要素の 3 番目の要素（0 ベース）を返します。
SELECT XMLGET(src, 'part', 3) FROM sample_xml_parts;
+---------------------------------------+
| XMLGET(SRC, 'PART', 3) |
|---------------------------------------|
| <part count="1"> |
| <item>Engine Coolant</item> |
| <partnum>B6-120</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">19.00</price> |
| </part> |
+---------------------------------------+
XML データをクエリし、複数の関数を使用して要素の内容を抽出します。¶
この例では、 FLATTEN 関数と XMLGET 関数を使用して、 XML ドキュメントの読み込み例 で読み込み中の XML データの要素のコンテンツを抽出しています。
この例では、 COALESCE 関数を使用して、子要素 manufacturer または source が存在する場合は、 VARCHAR 値にキャストして返します。FLATTEN に渡された SRC:"$" は、ルート要素 parts の値を指定します。LATERAL FLATTEN は、渡されたすべての繰り返し要素を繰り返し処理します。
SELECT XMLGET(VALUE, 'item'):"$"::VARCHAR AS item,
XMLGET(VALUE, 'partnum'):"$"::VARCHAR AS partnum,
COALESCE(XMLGET(VALUE, 'manufacturer'):"$"::VARCHAR,
XMLGET(VALUE, 'source'):"$"::VARCHAR) AS manufacturer_or_source,
XMLGET(VALUE, 'price'):"$"::VARCHAR AS price,
FROM sample_xml_parts,
LATERAL FLATTEN(INPUT => SRC:"$");
+----------------+---------+------------------------+-------+
| ITEM | PARTNUM | MANUFACTURER_OR_SOURCE | PRICE |
|----------------+---------+------------------------+-------|
| Spark Plugs | A3-400 | ABC company | 27 |
| Motor Oil | B5-200 | XYZ company | 14 |
| Motor Oil | B5-300 | XYZ company | 16.75 |
| Engine Coolant | B6-120 | XYZ company | 19 |
| Engine Coolant | B6-220 | XYZ company | 18.25 |
+----------------+---------+------------------------+-------+