半结构化数据类型¶

VARIANT 的特征¶

VARIANT 可以存储任何其他类型的值，包括 OBJECT 和 ARRAY。

VARIANT 的最大未压缩数据大小为 16 MB。但实际上，由于内部开销，最大大小通常较小。最大大小还取决于要存储的对象。

使用 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);

Copy

第一个 UPDATE 将值从 FLOAT 转换为 VARIANT。第二个 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.

Copy

SELECT 所有值：

SELECT * FROM varia;
+--------+-----------------------+--------+
| FLOAT1 | V                     | FLOAT2 |
|--------+-----------------------+--------|
|   1.23 | 1.230000000000000e+00 |   1.23 |
+--------+-----------------------+--------+

Copy

要从 VARIANT 数据类型转换值，请指定要转换为的数据类型。例如，以下语句使用 :: 运算符指定 VARIANT 应转换为 FLOAT：

SELECT my_variant_column::FLOAT * 3.14 FROM ...;

Copy

VARIANT 存储值和值的数据类型。这样您可以在表达式中使用 VARIANT 值，其中值的数据类型有效，而无需先转换 VARIANT。例如，如果 VARIANT 列 my_variant_column 包含一个数值，则可以直接将 my_variant_column 乘以另一个数值：

SELECT my_variant_column * 3.14 FROM ...;

Copy

可以使用 TYPEOF 函数检索值的本机数据类型。

默认情况下，从 VARIANT 列检索 VARCHARs、DATEs、TIMEs 和 TIMESTAMPs 时，这些值都会用双引号引起来。您可以通过将值显式转换为基础数据类型（例如，从 VARIANT 转换为 VARCHAR）来消除双引号。例如：

SELECT 'Sample', 'Sample'::VARIANT, 'Sample'::VARIANT::VARCHAR;
+----------+-------------------+----------------------------+
| 'SAMPLE' | 'SAMPLE'::VARIANT | 'SAMPLE'::VARIANT::VARCHAR |
|----------+-------------------+----------------------------|
| Sample   | "Sample"          | Sample                     |
+----------+-------------------+----------------------------+

VARIANT 值可以缺失（包含 SQL NULL），这与 VARIANT 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"}'));

Copy

VARIANT 的常见用途¶

VARIANT 通常用于以下情况：

• 您希望通过显式定义包含两个或多个 ARRAYs 或 OBJECTs 的层次结构来创建 分层数据。

• 您希望直接加载 JSON、Avro、ORC 或 Parquet 数据，而无需显式描述数据的分层结构。

  Snowflake 可以将数据从 JSON、Avro、ORC 或 Parquet 格式转换为 ARRAY、OBJECT 和 VARIANT 数据的内部层次结构，并将该分层数据直接存储到 VARIANT。虽然您可以自己手动构建数据层次结构，但让 Snowflake 为您完成通常更容易。

  有关加载和转换半结构化数据的更多信息，请参阅 加载半结构化数据。

OBJECT 的特征¶

在 Snowflake 半结构化 OBJECT 中，每个键都是 VARCHAR，每个值都是 VARIANT。

由于 VARIANT 可以存储任何其他数据类型，因此不同的值（在不同的键值对中）可以具有不同的基础数据类型。例如，OBJECT 可以以 VARCHAR 形式保存人名，以 INTEGER 形式保存人的年龄。在下面的示例中，姓名和年龄都转换为 VARIANT。

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

Copy

Snowflake 当前不支持显式类型对象。

在键值对中，键不应为空字符串，键和值都不应为 NULL。

OBJECT 的最大长度为 16 MB。

OBJECT 可以包含 半结构化数据 (link removed)。

OBJECT 可用于创建 分层数据结构。

OBJECT 常量¶

常量*（也称为 *字面量）是指固定的数据值。Snowflake 支持使用常量来指定 OBJECT 值。OBJECT 常量用大括号（{ 和 }）分隔。

例如，以下代码块显示两个 OBJECT 常量，第一个是空 OBJECT，第二个包含两个加拿大省份的名称和首府城市：

{}

{ '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 province_capital;

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

通过键访问 OBJECT 的元素¶

要检索对象中的值，请在 方括号 中指定键，如下所示：

select my_variant_column['key1'] from my_table;

Copy

您也可以使用冒号运算符。以下命令显示，无论使用冒号还是方括号，结果都是相同的：

SELECT object_column['thirteen'],
       object_column:thirteen
    FROM object_example;
+---------------------------+------------------------+
| OBJECT_COLUMN['THIRTEEN'] | OBJECT_COLUMN:THIRTEEN |
|---------------------------+------------------------|
| 13                        | 13                     |
+---------------------------+------------------------+

Copy

有关冒号运算符的更多信息，请参阅 点表示法，其中介绍了如何使用 : 和 . 运算符访问嵌套数据。

插入 OBJECT 的示例¶

要直接插入 OBJECT 数据，请使用 IIS (INSERT INTO ... SELECT)。

以下代码使用 OBJECT_CONSTRUCT 函数构造它插入的 OBJECT。

INSERT INTO object_example (object_column)
    SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);

Copy

以下代码使用 OBJECT 常量 来指定要插入的 OBJECT。

INSERT INTO object_example (object_column)
    SELECT { 'thirteen': 13::VARIANT, 'zero': 0::VARIANT };

Copy

请注意，在每个键值对中，值都显式转换为 VARIANT。在这些情况下，不需要显式类型转换。Snowflake 可以隐式转换为 VARIANT。（有关隐式类型转换的信息，请参阅 数据类型转换。）

OBJECT 的常见用途¶

当以下一项或多项为真时，通常使用 OBJECT：

• 您有多条数据由字符串标识。例如，如果要按省份名称查找信息，可能需要使用 OBJECT。

• 您希望将有关数据的信息与数据一起存储；名称（键）不仅是不同的标识符，而且是有意义的。

• 信息没有自然顺序，或者只能从键中推断出顺序。

• 数据的结构各不相同，或者数据可能不完整。例如，如果要创建通常包含书名、作者姓名和出版日期的图书目录，但在某些情况下出版日期未知，则可能需要使用 OBJECT。

ARRAY 的特征¶

半结构化 ARRAY 中的每个值都是 VARIANT 类型。（ VARIANT 可以包含任何其他数据类型的值。）

其他数据类型的值可以转换为 VARIANT，然后存储在数组中。ARRAYs 的某些函数（包括 ARRAY_CONSTRUCT）可以为您将值 隐式地转换 为 VARIANT。

由于 ARRAYs 存储 VARIANT 值，并且由于 VARIANT 值可以在其中存储其他数据类型，因此 ARRAY 中值的基础数据类型不必完全相同。但是，在大多数情况下，数据元素属于相同或兼容的类型，因此可以以相同的方式处理所有数据元素。

Snowflake 不支持特定非 VARIANT 类型的元素数组。

声明 Snowflake ARRAY 时不指定元素数。ARRAY 可以根据 ARRAY_APPEND 等操作动态增长。Snowflake 目前不支持固定大小的数组。

ARRAY 可以包含 NULL 值。

ARRAY 中所有值的理论最大组合大小为 16 MB。但是，ARRAYs 有内部开销。实际的最大数据大小通常较小，具体取决于元素的数量和值。

ARRAY 常量¶

常量*（也称为 *字面量）是指固定的数据值。Snowflake 支持使用常量来指定 ARRAY 值。ARRAY 常量用方括号（[ 和 ]）分隔。

例如，以下代码块显示两个 ARRAY 常量，第一个是空 ARRAY，第二个包含两个加拿大省份的名称：

[]

[ '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 [ 'Alberta', 'Manitoba' ] AS province;

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

通过索引或切片访问 ARRAY 的元素¶

数组索引从 0 开始；数组中的第一个元素是元素 0。

通过在方括号中指定数组元素的索引号来访问数组中的值。例如，以下查询读取 my_array_column 中存储的数组中位于索引位置 2 的值。

select my_array_column[2] from my_table;

Copy

数组可以嵌套。以下查询读取嵌套数组的第零个元素的第零个元素：

select my_array_column[0][0] from my_table;

Copy

尝试访问数组末尾之外的元素将返回 NULL。

数组的 切片 是相邻元素的序列（即数组的连续子集）。

您可以通过调用 ARRAY_SLICE 函数来访问数组的切片。例如：

select array_slice(my_array_column, 5, 10) from my_table;

Copy

ARRAY_SLICE() 函数返回从指定的起始元素（上例中的 5）到指定的结束元素（上例中的 10，但不包括 该结束元素）之间的元素。

空数组或空切片通常由一对方括号（中间没有任何内容）表示 ([])。

稀疏与密集 ARRAYs¶

数组可以是 密集 数组或 稀疏 数组。

在密集数组中，元素的索引值从零开始，并且是连续的（0、1、2 等）。但是，在稀疏数组中，索引值可以是非连续的（例如 0、2、5）。值不需要从 0 开始。

如果索引没有相应的元素，则与该索引对应的值称为 undefined。例如，如果稀疏数组有三个元素，并且这些元素位于索引 0、2 和 5 处，则索引 1、3 和 4 处的元素为 undefined。

   0            2                  5
+-----+.....+-------+.....+.....+------+
| Ann |     | Carol |     |     | Fred |
+-----+.....+-------+.....+.....+------+

         ^             ^     ^
         |             |     |
        undefined--------------

Copy

undefined 元素被视为元素。例如，注意前面的稀疏数组示例，该示例包含索引 0、2 和 5 处的元素（索引 5 之后没有任何元素）。如果读取包含索引 3 和 4 处元素的切片，则输出类似于以下内容：

[ undefined, undefined ]

Copy

尝试访问数组末尾之外的切片会导致得到空数组，而不是 undefined 值的数组。以下 SELECT 语句尝试读取示例稀疏数组中最后一个元素之外的内容：

select array_slice(array_column, 6, 8) from table_1;

Copy

输出是一个空数组：

+---------------------------------+
| array_slice(array_column, 6, 8) |
+---------------------------------+
| [ ]                             |
+---------------------------------+

请注意，undefined 与 NULL 不同。数组中的 NULL 值是定义的元素。

在密集数组中，每个元素都会占用存储空间，即使元素的值为 NULL。

在稀疏数组中，undefined 元素不直接占用存储空间。

在密集数组中，索引值的理论范围是从 0 到 16777215。（最大理论元素数为 16777216，因为大小上限为 16 MB [16777216 个字节]，最小可能值为 1 个字节。）

在稀疏数组中，索引值的理论范围是从 0 到 2³¹ - 1。但是，由于 16 MB 的限制，稀疏数组不能容纳 2³¹ 值。最大理论值数仍限制为 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);

Copy

以下代码使用 ARRAY 常量 来指定要插入的 ARRAY。

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

Copy

ARRAY 的常见用途¶

当以下一项或多项为真时，通常使用 ARRAY：

• 您有多条数据，每条数据的结构相同或相似。

• 每条数据都应以类似的方式处理。例如，您可以循环遍历数据，以相同的方式处理每条数据。

• 数据具有自然顺序，例如按时间顺序排列。