半構造化データ型¶

VARIANT 値の特徴¶

VARIANT 値の非圧縮データの最大サイズは16 MB です。しかし実際には、内部的なオーバーヘッドのために最大サイズは小さくなるのが普通です。最大サイズは保存されるオブジェクトにも依存します。

VARIANT データの挿入¶

VARIANT データを直接挿入するには、 INSERT INTO ... SELECT を使用します。次の例は、 JSONでフォーマットされたデータを VARIANT の値に挿入する方法を示しています。

CREATE OR REPLACE TABLE variant_insert (v VARIANT);
INSERT INTO variant_insert (v)
  SELECT PARSE_JSON('{"key3": "value3", "key4": "value4"}');
SELECT * FROM variant_insert;

+---------------------+
| V                   |
|---------------------|
| {                   |
|   "key3": "value3", |
|   "key4": "value4"  |
| }                   |
+---------------------+

VARIANT 値の使用。¶

VARIANT データ型との間で値を変換するには、 CAST 関数、 TO_VARIANT 関数、または :: 演算子（例: expression::VARIANT）を使用して明示的にキャストできます。

状況によっては、値を暗黙的に VARIANT にキャストできます。詳細については、 データ型の変換 をご参照ください。

以下のサンプルコードは、 VARIANT 値から VARIANT 値への変換方法を含む、 VARIANT 値の使用方法を示しています。

テーブルを作成して値を挿入します。

CREATE OR REPLACE 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 value to a VARIANT value.
UPDATE varia SET float2 = v::FLOAT;       -- converts from a VARIANT value to a FLOAT value.

すべての値を SELECT:

SELECT * FROM varia;

+--------+-----------------------+--------+
| FLOAT1 | V                     | FLOAT2 |
|--------+-----------------------+--------|
|   1.23 | 1.230000000000000e+00 |   1.23 |
+--------+-----------------------+--------+

前の例で示したように、 VARIANT データ型から値を変換するには、 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 関数を使用して、値のネイティブデータ型を取得できます。

デフォルトでは、 VARCHAR、 DATE、 TIME 、および TIMESTAMP が 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 データは通常、次の場合に使用されます。

• 2つ以上の ARRAYs または OBJECTs を含む階層を明示的に定義して、 階層データ を作成します。

• データの階層構造を明示的に記述せずに、 JSON、Avro、 ORC、またはParquetデータを直接ロードする必要があるとします。

  Snowflakeは、データを JSON 、Avro、 ORC 、またはParquet形式から ARRAY 、 OBJECT 、および VARIANT データの内部階層に変換し、その階層データを VARIANT 値に直接格納できます。自分でデータ階層を手動で構築することもできますが、通常はSnowflakeに任せる方が簡単です。

  半構造化データのロードと変換の詳細については、 半構造化データのロード をご参照ください。

OBJECT 値の特徴¶

Snowflake半構造化 OBJECT データでは、各キーは VARCHAR であり、各値は VARIANT 値です。

VARIANT はその他いずれのデータ型も格納できるため、異なる VARIANT 値（異なるキーと値のペア内）には、異なった基になるデータ型を持つことができます。たとえば、 OBJECT 値は、人の名前を VARCHAR 値として、人の年齢を INTEGER 値として保持できます。以下の例では、名前と年齢の両方が VARIANT 値にキャストされています。

SELECT OBJECT_CONSTRUCT(
  'name', 'Jones'::VARIANT,
  'age',  42::VARIANT);

OBJECT データにサービスを作成する場合は、次の点を考慮する必要があります。

• 現在のところ、Snowflakeは明示的に型付けされたオブジェクトをサポートしていません。

• キーと値のペアでは、キーは空の文字列であってはならず、キーも値も NULL であってはなりません。

• OBJECT 値の最大長は16 MB です。

• OBJECT には、 半構造化データ を含めることができます。

• OBJECT を使用して、 階層データ構造 を作成できます。

OBJECT データの挿入¶

OBJECT データを直接挿入するには、 INSERT INTO ... SELECT を使用します。

次のコードは、 OBJECT_CONSTRUCT 関数を使用して、挿入する OBJECT を作成します。

CREATE OR REPLACE TABLE object_example (object_column OBJECT);
INSERT INTO object_example (object_column)
  SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);
SELECT * FROM object_example;

+-------------------+
| OBJECT_COLUMN     |
|-------------------|
| {                 |
|   "thirteen": 13, |
|   "zero": 0       |
| }                 |
+-------------------+

キーと値の各ペアでは、値が明示的に VARIANT にキャストされていたことに注意してください。これらの場合、明示的なキャストは必要ありませんでした。Snowflakeは、暗黙的に VARIANT にキャストできます。（暗黙的なキャストについては、 データ型の変換 をご参照ください。）

また、 OBJECT 定数を使用して、挿入する OBJECT 値を指定することもできます。詳細については、 OBJECT 定数 をご参照ください。

OBJECT 定数¶

定数 （リテラル とも呼ばれます）は、固定データ値を指します。Snowflakeは、定数を使用してOBJECT値を指定することをサポートしています。OBJECT定数は波括弧（{ および }）で区切られます。

OBJECT 定数には次のような構文があります。

{ [<key>: <value> [, <key>: <value> , ...]] }

条件:

key

キーと値のペアのキー。 key は文字列リテラルである必要があります。

value

キーに関連付けられた値。 value 値は、リテラルまたは式です。 value はどのようなデータ型でも使用できます。

以下は、 OBJECT 定数を指定する例です。

• {} は空の OBJECT 値です。

• { 'key1': 'value1' , 'key2': 'value2' } 値にはリテラルを使用し、 OBJECT 値の指定されたキーと値のペアを含みます。

• { 'key1': c1+1 , 'key2': c1+2 } には、 OBJECT 値の指定されたキーと値のペアが、値の式を使用して含まれています。

• {*} は、属性名をキー、関連する値を値として、指定されたデータからの OBJECT 値を構築するワイルドカードです。

  オブジェクト定数で指定する場合、ワイルドカードは非修飾でも、テーブル名やエイリアスで修飾してもよいです。例えば、これらのワイルドカード指定はどちらも有効です。

  SELECT {*} FROM my_table;
  
  SELECT {my_table1.*}
    FROM my_table1 INNER JOIN my_table2
        ON my_table2.col1 = my_table1.col1;
  

  Copy

  オブジェクト定数では、 ILIKE と EXCLUDE キーワードを使うことができます。特定の列を選択するには、 ILIKE キーワードを使用します。例えば、以下のクエリは、テーブル my_table のパターン col1% にマッチする列を選択します。

  SELECT {* ILIKE 'col1%'} FROM my_table;
  

  Copy

  特定の列を除外するには、 EXCLUDE キーワードを使用します。例えば、以下のクエリはテーブル my_table の col1 を除外します。

  SELECT {* EXCLUDE col1} FROM my_table;
  

  Copy

  次のクエリは、テーブル my_table の col1 と col2 を除外します。

  SELECT {* EXCLUDE (col1, col2)} FROM my_table;
  

  Copy

  ワイルドカードをキーと値のペアに混ぜることはできません。たとえば、次のワイルドカード指定は許可されていません。

  SELECT {*, 'k': 'v'} FROM my_table;
  

  Copy

  1つのオブジェクト定数で複数のワイルドカードを使用することはできません。たとえば、次のワイルドカード指定は許可されていません。

  SELECT {t1.*, t2.*} FROM t1, t2;
  

  Copy

以下のステートメントでは、 OBJECT 定数と OBJECT_CONSTRUCT 関数を使用して、 OBJECT データをテーブルに挿入しています。OBJECT の値には、カナダの2つの州の名前と州都が含まれています。

CREATE OR REPLACE TABLE my_object_table (my_object OBJECT);

INSERT INTO my_object_table (my_object)
  SELECT { 'PROVINCE': 'Alberta'::VARIANT , 'CAPITAL': 'Edmonton'::VARIANT };

INSERT INTO my_object_table (my_object)
  SELECT OBJECT_CONSTRUCT('PROVINCE', 'Manitoba'::VARIANT , 'CAPITAL', 'Winnipeg'::VARIANT );

SELECT * FROM my_object_table;

+--------------------------+
| MY_OBJECT                |
|--------------------------|
| {                        |
|   "CAPITAL": "Edmonton", |
|   "PROVINCE": "Alberta"  |
| }                        |
| {                        |
|   "CAPITAL": "Winnipeg", |
|   "PROVINCE": "Manitoba" |
| }                        |
+--------------------------+

次の例では、ワイルドカード（{*}）を使用して、 FROM 句から属性名と値を取得し、 OBJECT データを挿入しています。まず、県名と首都名を含む VARCHAR 値を持つ demo_ca_provinces という名前のテーブルを作成します。

CREATE OR REPLACE TABLE demo_ca_provinces (province VARCHAR, capital VARCHAR);
INSERT INTO demo_ca_provinces (province, capital) VALUES
  ('Ontario', 'Toronto'),
  ('British Columbia', 'Victoria');

SELECT province, capital
  FROM demo_ca_provinces
  ORDER BY province;

+------------------+----------+
| PROVINCE         | CAPITAL  |
|------------------+----------|
| British Columbia | Victoria |
| Ontario          | Toronto  |
+------------------+----------+

demo_ca_provinces テーブルのデータを使って、 my_object_table にオブジェクトデータを挿入します。

INSERT INTO my_object_table (my_object)
  SELECT {*} FROM demo_ca_provinces;

SELECT * FROM my_object_table;

+----------------------------------+
| MY_OBJECT                        |
|----------------------------------|
| {                                |
|   "CAPITAL": "Edmonton",         |
|   "PROVINCE": "Alberta"          |
| }                                |
| {                                |
|   "CAPITAL": "Winnipeg",         |
|   "PROVINCE": "Manitoba"         |
| }                                |
| {                                |
|   "CAPITAL": "Toronto",          |
|   "PROVINCE": "Ontario"          |
| }                                |
| {                                |
|   "CAPITAL": "Victoria",         |
|   "PROVINCE": "British Columbia" |
| }                                |
+----------------------------------+

次の例では、 OBJECT 定数の値に式を使用しています。

SET my_variable = 10;
SELECT {'key1': $my_variable+1, 'key2': $my_variable+2};

+--------------------------------------------------+
| {'KEY1': $MY_VARIABLE+1, 'KEY2': $MY_VARIABLE+2} |
|--------------------------------------------------|
| {                                                |
|   "key1": 11,                                    |
|   "key2": 12                                     |
| }                                                |
+--------------------------------------------------+

SQL ステートメントは OBJECT 内の文字列リテラルを一重引用符で指定しますが（Snowflake SQL の他の場所と同様）、 OBJECT 内の文字列リテラルは二重引用符で表示されることに注意してください。

SELECT { 'Manitoba': 'Winnipeg' } AS province_capital;

+--------------------------+
| PROVINCE_CAPITAL         |
|--------------------------|
| {                        |
|   "Manitoba": "Winnipeg" |
| }                        |
+--------------------------+

キーによる OBJECT の要素へのアクセス¶

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 データは通常、次の1つ以上が当てはまる場合に使用されます。

• 文字列により識別される複数のデータがあります。たとえば、州名で情報を検索する場合は、 OBJECT 値を使用できます。

• データに関する情報をデータと一緒に格納する場合、名前（キー）は単なる個別の識別子ではなく、意味があります。

• 情報には自然な順序がないか、キーからのみ順序を推測できます。

• データの構造が異なるか、データが不完全である可能性があります。たとえば、通常はタイトル、著者名、出版日が含まれている本のカタログを作成したいが、場合によっては出版日が不明な場合は、 OBJECT 値を使用できます。

配列の特徴¶

半構造化配列の各値は VARIANT 型です。VARIANT には、他のデータ型の値を含めることができます。

他のデータ型の値は VARIANT にキャストして、配列に格納できます。 ARRAY_CONSTRUCT を含む配列の一部の関数では、 VARIANT に値を 暗黙的にキャスト できます。

配列は VARIANT 値を格納し、 VARIANT 値は他のデータ型を格納できるため、配列の値の基になるデータ型は同一である必要はありません。ただし、ほとんどの場合、データ要素は同じ型または互換性のある型であるため、すべて同じ方法で処理できます。

配列には、以下の考慮事項が適用されます。

• Snowflakeは、特定の非 VARIANT 型の要素における配列をサポートしていません。

• Snowflake 配列は、要素の数を指定せずに宣言されます。配列は、 ARRAY_APPEND などの操作に基づいて動的に拡張できます。Snowflakeは現在、固定サイズの配列をサポートしていません。

• 配列は NULL の値を含むことができます。

• 配列にある値すべての理論上の最大合計サイズは16 MB です。しかし、配列には内部的なオーバーヘッドがあります。実用的な最大データサイズは、要素の数と値に応じて、通常はこれよりも小さくなります。

ARRAY データの挿入¶

ARRAY データを直接挿入するには、 INSERT INTO ... SELECT を使用します。

次のコードは、 ARRAY_CONSTRUCT 関数を使用して、挿入する配列を作成します。

CREATE OR REPLACE TABLE array_example (array_column ARRAY);
INSERT INTO array_example (array_column)
  SELECT ARRAY_CONSTRUCT(12, 'twelve', NULL);

ARRAY 定数を使用して挿入する配列を指定することもできます。詳細については、 ARRAY 定数 をご参照ください。

ARRAY 定数¶

定数 （リテラル とも呼ばれます）は、固定データ値を指します。Snowflakeは、定数を使用してARRAY値を指定することをサポートしています。ARRAY定数は角括弧（[ および ]）で区切られます。

ARRAY 定数には次のような構文があります。

[<value> [, <value> , ...]]

条件:

value

配列要素に関連付けられた値。 value 値は、リテラルまたは式です。 value はどのようなデータ型でも使用できます。

以下は、 ARRAY 定数を指定する例です。

• [] は空の ARRAY 値です。

• { 'value1' , 'value2' } には、 ARRAY 定数に指定された値がリテラルで格納されます。

• { c1+1 , c1+2 } には、 ARRAY 定数に指定された値が、値の式を使って含まれています。

以下の例では、 ARRAY 定数を使って挿入する配列を指定しています。

INSERT INTO array_example (array_column)
  SELECT [ 12, 'twelve', NULL ];

次のステートメントは、ARRAY定数と ARRAY_CONSTRUCT 関数を使用して同じタスクを実行します。

UPDATE my_table SET my_array = [ 1, 2 ];

UPDATE my_table SET my_array = ARRAY_CONSTRUCT(1, 2);

次の例では、 ARRAY 定数の値に式を使用しています。

SET my_variable = 10;
SELECT [$my_variable+1, $my_variable+2];

+----------------------------------+
| [$MY_VARIABLE+1, $MY_VARIABLE+2] |
|----------------------------------|
| [                                |
|   11,                            |
|   12                             |
| ]                                |
+----------------------------------+

SQL ステートメントは配列内の文字列リテラルを一重引用符で指定しますが（Snowflake SQL の他の場所と同様）、配列内の文字列リテラルは二重引用符で表示されることに注意してください。

SELECT [ 'Alberta', 'Manitoba' ] AS province;

+--------------+
| PROVINCE     |
|--------------|
| [            |
|   "Alberta", |
|   "Manitoba" |
| ]            |
+--------------+

インデックスまたはスライスによる配列の要素へのアクセス¶

配列インデックスは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） 未満 までの要素を返します。

空の配列または空のスライスは、多くの場合、間に何もない（[]）角括弧のペアで示されます。

密な配列と疎な配列¶

配列は 密 または 疎 にできます。

密配列では、要素のインデックス値はゼロから始まり、連続しています（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から2³¹ - 1です。ただし、16 MB の制限により、疎配列は2³¹ 値を保持できません。理論上の値の最大数は、依然として16777216に制限されています。

（内部オーバーヘッドのため、密配列と疎配列両方の実際のサイズ制限は、理論上の最大値である16 MB よりも少なくともわずかに小さいことに注意してください。）

ARRAY_INSERT 関数を使用して、配列内の特定のインデックスポイントに値を挿入する（他の配列要素 undefined を残す）ことにより、疎配列を作成できます。ARRAY_INSERT は、要素を右方向にプッシュし、それらにアクセスするために必要なインデックス値を変更するため、ほとんどの場合、疎配列を左から右に埋める必要があります（つまり、0から上に、挿入される新しい値ごとにインデックス値を増やします）。

ARRAY データの一般的な使用法¶

ARRAY データは通常、次の1つ以上が当てはまる場合に使用されます。

• データの集まりがあり、その中のそれぞれの断片は同じか似たような構造になっています。

• 各データは同様に処理する必要があります。たとえば、データをループして、各部分を同じ方法で処理することができます。

• データには、時系列などの自然な順序があります。