半構造化データ型¶
次のSnowflakeデータ型には、他のデータ型を含めることができます。
VARIANT （他のデータ型を含めることができます）。
ARRAY （直接 VARIANT を含めることができるため、それ自体を含む他のデータ型を間接的に含めることができます）。
OBJECT （直接 VARIANT を含めることができるため、それ自体を含む他のデータ型を間接的に含めることができます）。
これらのデータ型はしばしば、 半構造化 データ型と呼ばれます。厳密に言えば、これらのデータ型の中で OBJECT のみが単体で、真の 半構造化データ型 の特性すべてを備えています。ただし、これらのデータ型を組み合わせると、任意の 階層データ構造 を明示的に表すことができます。これを使用して、半構造化形式（例: JSON、Avro、 ORC、Parquet、または XML）のデータでロードしたり操作したりできます。
これらの各データ型について、以下で説明します。
このトピックの内容:
VARIANT¶
VARIANT での値の使用¶
VARIANT データ型との間で値を変換するには、 CAST 関数、 TO_VARIANT 関数、または :: 演算子（例: expression::variant）を使用して明示的にキャストできます。
状況によっては、値を暗黙的に VARIANT にキャストできます。詳細については、 データ型の変換 をご参照ください。
以下のサンプルコードは、 VARIANT から VARIANT への変換方法を含む、 VARIANT の使用方法を示しています。
テーブルを作成して値を挿入します。
CREATE TABLE varia (float1 FLOAT, v VARIANT, float2 FLOAT); INSERT INTO varia (float1, v, float2) VALUES (1.23, NULL, NULL);最初の UPDATE は、値を FLOAT から VARIANT に変換します。2番目の UPDATE は、値を VARIANT から FLOAT に変換します。
UPDATE varia SET v = TO_VARIANT(float1); -- converts FROM a float TO a variant. UPDATE varia SET float2 = v::FLOAT; -- converts FROM a variant TO a float.すべての値を SELECT:
SELECT * FROM varia; +--------+-----------------------+--------+ | FLOAT1 | V | FLOAT2 | |--------+-----------------------+--------| | 1.23 | 1.230000000000000e+00 | 1.23 | +--------+-----------------------+--------+
VARIANT データ型から値を変換するには、変換するデータ型を指定します。たとえば、次のステートメントは、 :: 演算子を使用して、 VARIANT を FLOAT に変換する必要があることを指定します。
SELECT my_variant_column::FLOAT * 3.14 FROM ...;
VARIANT は、値と値のデータ型の両方を格納します。これにより、最初に VARIANT をキャストすることなく、値のデータ型が有効な式で VARIANT 値を使用することができるようになります。たとえば、 VARIANT 列 my_variant_column に数値が含まれている場合は、 my_variant_column に別の数値を直接乗算できます。
SELECT my_variant_column * 3.14 FROM ...;
TYPEOF 関数を使用して、値のネイティブデータ型を取得できます。
デフォルトでは、 VARCHARs、 DATEs、 TIMEs、および TIMESTAMPs が VARIANT 列から取得される場合、値は二重引用符で囲まれます。基になるデータ型（例: VARIANT から VARCHAR）に値を明示的にキャストすることにより、二重引用符を削除できます。例:
SELECT 'Sample', 'Sample'::VARIANT, 'Sample'::VARIANT::VARCHAR;
+----------+-------------------+----------------------------+
| 'SAMPLE' | 'SAMPLE'::VARIANT | 'SAMPLE'::VARIANT::VARCHAR |
|----------+-------------------+----------------------------|
| Sample | "Sample" | Sample |
+----------+-------------------+----------------------------+
VARIANT null 値とは異なる VARIANT 値が欠落している（SQL NULL を含む）可能性があります。これは、半構造化データでnull値を表すために使用される実際の値です。VARIANT null は、それ自体に等しいと比較される真の値です。詳細については、 VARIANT null をご参照ください。
データが JSON 形式でロードされ、 VARIANT に格納された場合は、次のガイドラインが適用されます。
ほとんどが規則的で、ネイティブ JSON 型（タイムスタンプではなく文字列と数字）のみを使用するデータの場合、リレーショナルデータと VARIANT 列のデータ操作のストレージとクエリのパフォーマンスは非常に似ています。ただし、日付やタイムスタンプなどの非ネイティブ値は、 VARIANT 列にロードされると文字列として格納されるため、これらの値に対する操作は、対応するデータ型のリレーショナル列に格納される場合よりも遅くなり、より多くのスペースを消費します。
VARIANT の使用の詳細については、 VARIANT に格納されている半構造化データに関する考慮事項 をご参照ください。
VARIANT に格納されている半構造化データのクエリの詳細については、 半構造化データのクエリ をご参照ください。
VARIANT 挿入の例¶
VARIANT データを直接挿入するには、 IIS （INSERT INTO ... SELECT）を使用します。次の例は、 JSON 形式のデータを VARIANT に挿入する方法を示しています。
INSERT INTO varia (v) SELECT TO_VARIANT(PARSE_JSON('{"key3": "value3", "key4": "value4"}'));
VARIANT の一般的な使用法¶
VARIANT は通常、次の場合に使用されます。
2つ以上の ARRAYs または OBJECTs を含む階層を明示的に定義して、 階層データ を作成します。
データの階層構造を明示的に記述せずに、 JSON、Avro、 ORC、またはParquetデータを直接ロードする必要があるとします。
Snowflakeは、データを JSON、Avro、 ORC、またはParquet形式から ARRAY、 OBJECT、および VARIANT データの内部階層に変換し、その階層データを VARIANT に直接格納できます。自分でデータ階層を手動で構築することもできますが、通常はSnowflakeに任せる方が簡単です。
半構造化データのロードと変換の詳細については、 半構造化データのロード をご参照ください。
OBJECT¶
Snowflake OBJECT は、 JSON 「オブジェクト」 に類似しています。他のプログラミング言語では、対応するデータ型はしばしば、「ディクショナリ」、「ハッシュ」、または「マップ」と呼ばれることがあります。
OBJECT にはキーと値のペアが含まれます。
OBJECT の特徴¶
Snowflake OBJECT では、各キーは VARCHAR であり、各値は VARIANT です。
VARIANT はその他いずれのデータ型も格納できるため、異なる値（異なるキーと値のペア内）には、異なった基になるデータ型を持つことができます。たとえば、 OBJECT は、人の名前を VARCHAR として、人の年齢を INTEGER として保持できます。以下の例では、名前と年齢の両方が VARIANT にキャストされています。
SELECT OBJECT_CONSTRUCT( 'name', 'Jones'::VARIANT, 'age', 42::VARIANT );
Snowflakeは現在、明示的に入力されたオブジェクトをサポートしていません。
キーと値のペアでは、キーは空の文字列であってはならず、キーも値も NULL であってはなりません。
OBJECT の最大長は16 MB です。
OBJECT には、 半構造化データ を含めることができます。
OBJECT を使用して、 階層データ構造 を作成できます。
OBJECT定数¶
定数 （リテラル とも呼ばれます）は、固定データ値を指します。Snowflakeは、定数を使用してOBJECT値を指定することをサポートしています。OBJECT定数は波括弧（{ および }）で区切られます。
例えば、次のコードブロックは、2つのOBJECT定数を示しています。最初の定数は空のOBJECTで、2番目の定数にはカナダの2つの州の名前と首都が含まれています。
{}
{ 'Alberta': 'Edmonton' , 'Manitoba': 'Winnipeg' }
次のステートメントは、OBJECT定数と OBJECT_CONSTRUCT 関数を使用して同じタスクを実行します。
UPDATE my_table SET my_object = { 'Alberta': 'Edmonton' , 'Manitoba': 'Winnipeg' };
UPDATE my_table SET my_object = OBJECT_CONSTRUCT('Alberta', 'Edmonton', 'Manitoba', 'Winnipeg');
SQLステートメントはOBJECT内の文字列リテラルを一重引用符で指定しますが（Snowflake SQLの他の場所と同様）、OBJECT内の文字列リテラルは二重引用符で表示されることに注意してください。
SELECT { 'Manitoba': 'Winnipeg' } AS 'Capital';
+--------------------------+
| CAPITAL |
|--------------------------|
| { |
| "Manitoba": "Winnipeg" |
| } |
+--------------------------+
キーによる OBJECT の要素へのアクセス¶
オブジェクトの値を取得するには、以下に示すように、 角括弧 で囲んだキーを指定します。
select my_variant_column['key1'] from my_table;
コロン演算子を使用することもできます。次のコマンドは、コロンを使用しても角括弧を使用しても、結果が同じであることを示しています。
SELECT object_column['thirteen'], object_column:thirteen FROM object_example; +---------------------------+------------------------+ | OBJECT_COLUMN['THIRTEEN'] | OBJECT_COLUMN:THIRTEEN | |---------------------------+------------------------| | 13 | 13 | +---------------------------+------------------------+
コロン演算子の詳細については、 ドット表記 をご参照ください。ここでは、ネストされたデータにアクセスするための : および . 演算子の使用について説明しています。
OBJECT 挿入の例¶
OBJECT データを直接挿入するには、 IIS （INSERT INTO ... SELECT）を使用します。
次のコードは、 OBJECT_CONSTRUCT 関数を使用して、挿入する OBJECT を作成します。
INSERT INTO object_example (object_column) SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);
次のコードは、 OBJECT定数 を使用して、挿入するOBJECTを指定します。
INSERT INTO object_example (object_column) SELECT { 'thirteen': 13::VARIANT, 'zero': 0::VARIANT };
キーと値の各ペアでは、値が明示的に VARIANT にキャストされていたことに注意してください。これらの場合、明示的なキャストは必要ありませんでした。Snowflakeは、暗黙的に VARIANT にキャストできます。（暗黙的なキャストについては、 データ型の変換 をご参照ください。）
OBJECT の一般的な使用法¶
OBJECT は通常、次の1つ以上が当てはまる場合に使用されます。
文字列により識別される複数のデータがあります。たとえば、州名で情報を検索する場合は、 OBJECT を使用できます。
データに関する情報をデータと一緒に格納する場合、名前（キー）は単なる個別の識別子ではなく、意味があります。
情報には自然な順序がないか、キーからのみ順序を推測できます。
データの構造が異なるか、データが不完全である可能性があります。たとえば、通常はタイトル、著者名、出版日が含まれている本のカタログを作成したいが、場合によっては出版日が不明な場合は、 OBJECT を使用できます。
ARRAY¶
Snowflake ARRAY は、他の多くのプログラミング言語にある配列と類似しています。ARRAY には0個以上のデータが含まれています。各要素には、配列内の位置を指定してアクセスします。
ARRAY の特徴¶
ARRAY の各値は、型 VARIANT です。（VARIANT には、他のデータ型の値を含めることができます。）
他のデータ型の値は VARIANT にキャストして、配列に格納できます。 ARRAY_CONSTRUCT を含む ARRAYs の一部の関数では、 VARIANT に値を 暗黙的にキャスト できます。
ARRAYs は VARIANT 値を格納し、 VARIANT 値は他のデータ型を格納できるため、 ARRAY の値の基になるデータ型は同一である必要はありません。ただし、ほとんどの場合、データ要素は同じ型または互換性のある型であるため、すべて同じ方法で処理できます。
Snowflakeは、特定の非 VARIANT 型の要素における配列をサポートしていません。
Snowflake ARRAY は、要素の数を指定せずに宣言されます。ARRAY は、 ARRAY_APPEND などの操作に基づいて動的に拡張できます。Snowflakeは現在、固定サイズの配列をサポートしていません。
ARRAY には NULL の値を含めることができます。
ARRAY にある値すべての理論上の最大合計サイズは16 MB です。ただし、 ARRAYs には内部オーバーヘッドがあります。実用的な最大データサイズは、要素の数と値に応じて、通常はこれよりも小さくなります。
ARRAY定数¶
定数 （リテラル とも呼ばれます）は、固定データ値を指します。Snowflakeは、定数を使用してARRAY値を指定することをサポートしています。ARRAY定数は角括弧（[ および ]）で区切られます。
例えば、次のコードブロックは、2つのARRAY定数を示しています。最初の定数は空のARRAYで、2番目の定数には2つのカナダの州の名前が含まれています。
[]
[ 'Alberta', 'Manitoba' ]
次のステートメントは、ARRAY定数と ARRAY_CONSTRUCT 関数を使用して同じタスクを実行します。
UPDATE my_table SET my_array = [ 1, 2 ];
UPDATE my_table SET my_array = ARRAY_CONSTRUCT(1, 2);
SQLステートメントはARRAY内の文字列リテラルを一重引用符で指定しますが（Snowflake SQLの他の場所と同様）、ARRAY内の文字列リテラルは二重引用符で表示されることに注意してください。
SELECT [ 'Manitoba' ] AS 'Capital';
+----------------+
| CAPITAL |
|----------------|
| [ "Winnipeg" ] |
+----------------+
インデックスまたはスライスによる ARRAY の要素へのアクセス¶
配列インデックスは0ベースです。配列にある最初の要素は要素0です。
配列の値にアクセスするには、配列要素のインデックス番号を角括弧で囲んで指定します。たとえば、次のクエリは、 my_array_column に格納されている配列のインデックス位置2の値を読み取ります。
select my_array_column[2] from my_table;
配列はネストできます。次のクエリは、ネストされた配列のゼロ番目の要素におけるゼロ番目の要素を読み取ります。
select my_array_column[0][0] from my_table;
配列の終わりを超えて要素にアクセスしようとすると、 NULL が返されます。
配列の スライス は、隣接する要素のシーケンス（つまり、配列の連続したサブセット）です。
ARRAY_SLICE 関数を呼び出すことにより、配列のスライスにアクセスできます。例:
select array_slice(my_array_column, 5, 10) from my_table;
ARRAY_SLICE() 関数は、指定された開始要素（上記の例では5）から指定された終了要素（上記の例では10） 未満 までの要素を返します。
空の配列または空のスライスは、多くの場合、間に何もない（[]）角括弧のペアで示されます。
疎と密の ARRAYs¶
配列は 密 または 疎 にできます。
密配列では、要素のインデックス値はゼロから始まり、連続しています（0、1、2など）。しかし、疎配列では、インデックス値は非連続である可能性があります（例: 0、2、5）。値は0で始まる必要はありません。
インデックスに対応する要素がない場合、そのインデックスに対応する値は 未定義 と言われます。たとえば、疎配列に3つの要素があり、それらの要素がインデックス0、2、および5にある場合、インデックス1、3、および4の要素は undefined です。
0 2 5 +-----+.....+-------+.....+.....+------+ | Ann | | Carol | | | Fred | +-----+.....+-------+.....+.....+------+ ^ ^ ^ | | | undefined--------------
未定義の要素は要素として扱われます。たとえば、インデックス0、2、および5の要素を含む（そしてインデックス5の後に要素を持たない）、前の疎配列の例を考えてみます。インデックス3および4の要素を含むスライスを読み取ると、出力は次のようになります。
[ undefined, undefined ]
配列の終わりを超えてスライスにアクセスしようとすると、 undefined 値の配列ではなく、空の配列になります。次の SELECT ステートメントは、例の疎配列にある最後の要素を超えて読み取ろうとします。
select array_slice(array_column, 6, 8) from table_1;
出力は空の配列です。
+---------------------------------+ | array_slice(array_column, 6, 8) | +---------------------------------+ | [ ] | +---------------------------------+
undefined は NULL とは異なることに注意してください。配列の NULL 値は定義された要素です。
密配列では、要素の値が NULL であっても、各要素はストレージスペースを消費します。
疎配列では、 undefined 要素はストレージスペースを直接消費しません。
密配列では、インデックス値の理論的な範囲は0から16777215です。（サイズの上限は16 MB （16777216バイト）であり、可能な最小値は1バイトであるため、理論上の最大要素数は16777216です。）
疎配列では、インデックス値の理論上の範囲は0から231 - 1です。ただし、16 MB の制限により、疎配列は231 値を保持できません。理論上の値の最大数は、依然として16777216に制限されています。
（内部オーバーヘッドのため、密配列と疎配列両方の実際のサイズ制限は、理論上の最大値である16 MB よりも少なくともわずかに小さいことに注意してください。）
ARRAY_INSERT 関数を使用して、配列内の特定のインデックスポイントに値を挿入する（他の配列要素 undefined を残す）ことにより、疎配列を作成できます。 ARRAY_INSERT() は、要素を右方向にプッシュし、それらにアクセスするために必要なインデックス値を変更するため、ほとんどの場合、疎配列を左から右に埋める必要があります（つまり、0から上に、挿入される新しい値ごとにインデックス値を増やします）。
ARRAY 挿入の例¶
ARRAY データを直接挿入するには、 IIS （INSERT INTO ... SELECT）を使用します。
次のコードは、 ARRAY_CONSTRUCT 関数を使用して、挿入する ARRAY を作成します。
INSERT INTO array_example (array_column) SELECT ARRAY_CONSTRUCT(12, 'twelve', NULL);
次のコードは、 ARRAY定数 を使用して、挿入するARRAYを指定します。
INSERT INTO array_example (array_column) SELECT [ 12, 'twelve', NULL ];
ARRAY の一般的な使用法¶
ARRAY は通常、次の1つ以上が当てはまる場合に使用されます。
多くのデータがあり、それぞれが同一または同様に構造化されています。
各データは同様に処理する必要があります。たとえば、データをループして、各部分を同じ方法で処理することができます。
データには、時系列などの自然な順序があります。
例¶
この最初の例は、 VARIANT、 ARRAY、および OBJECT データを含むテーブルでの DESC TABLE コマンドの出力を示しています。
CREATE OR REPLACE TABLE test_semi_structured(var VARIANT,
arr ARRAY,
obj OBJECT
);
DESC TABLE test_semi_structured;
+------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type | kind | null? | default | primary key | unique key | check | expression | comment |
|------+---------+--------+-------+---------+-------------+------------+-------+------------+---------|
| VAR | VARIANT | COLUMN | Y | NULL | N | N | NULL | NULL | NULL |
| ARR | ARRAY | COLUMN | Y | NULL | N | N | NULL | NULL | NULL |
| OBJ | OBJECT | COLUMN | Y | NULL | N | N | NULL | NULL | NULL |
+------+---------+--------+-------+---------+-------------+------------+-------+------------+---------+
この例は、テーブルに単純な値をロードする方法と、テーブルをクエリしたときにそれらの値がどのように見えるかを示しています。
テーブルを作成し、データをロードします。
CREATE TABLE demonstration1 ( ID INTEGER, array1 ARRAY, variant1 VARIANT, object1 OBJECT ); INSERT INTO demonstration1 (id, array1, variant1, object1) SELECT 1, ARRAY_CONSTRUCT(1, 2, 3), PARSE_JSON(' { "key1": "value1", "key2": "value2" } '), PARSE_JSON(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": "1b" }, ' || ' "outer_key2": { "inner_key2": 2 } } ') ; INSERT INTO demonstration1 (id, array1, variant1, object1) SELECT 2, ARRAY_CONSTRUCT(1, 2, 3, NULL), PARSE_JSON(' { "key1": "value1", "key2": NULL } '), PARSE_JSON(' { "outer_key1": { "inner_key1A": "1a", "inner_key1B": NULL }, ' || ' "outer_key2": { "inner_key2": 2 } ' || ' } ') ;テーブルのデータを表示します。
SELECT * FROM demonstration1 ORDER BY id; +----+-------------+---------------------+--------------------------+ | ID | ARRAY1 | VARIANT1 | OBJECT1 | |----+-------------+---------------------+--------------------------| | 1 | [ | { | { | | | 1, | "key1": "value1", | "outer_key1": { | | | 2, | "key2": "value2" | "inner_key1A": "1a", | | | 3 | } | "inner_key1B": "1b" | | | ] | | }, | | | | | "outer_key2": { | | | | | "inner_key2": 2 | | | | | } | | | | | } | | 2 | [ | { | { | | | 1, | "key1": "value1", | "outer_key1": { | | | 2, | "key2": null | "inner_key1A": "1a", | | | 3, | } | "inner_key1B": null | | | undefined | | }, | | | ] | | "outer_key2": { | | | | | "inner_key2": 2 | | | | | } | | | | | } | +----+-------------+---------------------+--------------------------+
追加の半構造化データの使用例については、 半構造化データのクエリ をご参照ください。