存储在 VARIANT 中的半结构化数据的注意事项¶

本主题提供有关加载和使用 VARIANT 值（包含半结构化数据）的最佳实践、一般准则和重要注意事项。这可以是显式构造的分层数据，也可以是从半结构化数据格式（如 JSON、Avro、ORC 和 Parquet）加载的数据。本主题中的信息不一定适用于 XML 数据。

本主题内容：

数据大小限制¶

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

有关更多信息，请参阅 VARIANT。

通常，JSON 数据集是多个文档的简单串联。某些软件的 JSON 输出由包含多条记录的单个巨大数组组成。无需使用换行符或逗号分隔文档，尽管两者都受支持。

如果数据超过 16 MB，请启用 COPY INTO <table> 命令的 STRIP_OUTER_ARRAY 文件格式选项，以删除外部数组结构并将记录加载到单独的表行中：

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

Copy

在 VARIANT 列中存储半结构化数据与展平嵌套结构¶

如果还不确定要对半结构化数据执行哪种类型的操作，Snowflake 建议暂时将数据存储在 VARIANT 列中。

对于大多数常规数据，并且只使用您所使用的半结构化格式的本地数据类型（例如 JSON 格式的字符串和整数），对关系数据和 VARIANT 列中的数据的操作，其存储要求和查询性能非常相似。

为了更好地削减和减少存储消耗，如果半结构化数据包含以下内容，建议将 OBJECT 数据和关键数据平展到单独的关系列中：

日期和时间戳，尤其是非 ISO 8601 (http://www.iso.org/iso/home/standards/iso8601.htm) 日期和时间戳，作为字符串值
字符串中的数字
数组

非本机值（如 JSON 中的日期和时间戳）在加载到 VARIANT 列中时存储为字符串，因此对这些值的操作可能比存储在具有相应数据类型的关系列中时更慢，并且占用更多空间。

如果您知道数据的用例，请对典型数据集执行测试。将数据集加载到表中的 VARIANT 列中。使用 FLATTEN 函数将计划查询的 OBJECTs 和键提取到单独的表中。对这两个表运行一组典型的查询，查看哪种结构可提供最佳性能。

NULL 值¶

Snowflake 在半结构化数据中支持两种类型的 NULL 值：

SQL NULL：SQL NULL 对于半结构化数据类型来说，与结构化数据类型的意思相同：值缺失或未知。
JSON null（有时称为“VARIANT NULL”）：在 VARIANT 列中、JSON null 值存储为包含单词“null”的字符串，将它们与 SQL NULL 值区分开来。

以下示例对比 SQL NULL 和 JSON null：

select 
    parse_json(NULL) AS "SQL NULL", 
    parse_json('null') AS "JSON NULL", 
    parse_json('[ null ]') AS "JSON NULL",
    parse_json('{ "a": null }'):a AS "JSON NULL",
    parse_json('{ "a": null }'):b AS "ABSENT VALUE";
+----------+-----------+-----------+-----------+--------------+
| SQL NULL | JSON NULL | JSON NULL | JSON NULL | ABSENT VALUE |
|----------+-----------+-----------+-----------+--------------|
| NULL     | null      | [         | null      | NULL         |
|          |           |   null    |           |              |
|          |           | ]         |           |              |
+----------+-----------+-----------+-----------+--------------+

Copy

要将 VARIANT "null" 值转换为 SQL NULL，请将其转换为字符串。例如：

select 
    parse_json('{ "a": null }'):a,
    to_char(parse_json('{ "a": null }'):a);
+-------------------------------+----------------------------------------+
| PARSE_JSON('{ "A": NULL }'):A | TO_CHAR(PARSE_JSON('{ "A": NULL }'):A) |
|-------------------------------+----------------------------------------|
| null                          | NULL                                   |
+-------------------------------+----------------------------------------+

Copy

半结构化数据文件和列化¶

将半结构化数据插入 VARIANT 列中时，Snowflake 会使用某些规则将尽可能多的数据提取为列形式。其余数据作为单个列存储在解析的半结构化结构中。

默认情况下，Snowflake 每个分区、每个表最多提取 200 个元素。要提高此限制，请联系 Snowflake 支持部门 (https://community.snowflake.com/s/article/How-To-Submit-a-Support-Case-in-Snowflake-Lodge)。

未提取的元素¶

具有以下特征的元素不会提取到列中：

即使包含单个“null”值的元素也不会提取到列中。这适用于具有“null”值的元素，而不适用于以列形式表示的缺少值的元素。

此规则可确保不会丢失任何信息（即，VARIANT“null”值和 SQL NULL 值之间的差异不会丢失）。
包含多种数据类型的元素。例如：

一行中的 foo 元素包含一个数字：
```
{"foo":1}
```
Copy
另一行中的同一元素包含一个字符串：
```
{"foo":"1"}
```
Copy

提取如何影响查询¶

当您查询半结构化元素时，Snowflake 的执行引擎的表现会根据是否提取元素而有所不同。

如果元素已提取到列中，则引擎仅扫描提取的列。
如果未将元素提取到列中，则引擎必须扫描整个 JSON 结构，然后对每一行遍历结构以输出值，从而影响性能。

为避免对未提取的元素产生性能影响，请执行以下操作：

在加载前，将包含“null”值的半结构化数据元素提取到关系列中。

或者，如果文件中的“null”值表示缺失值并且没有其他特殊含义，建议在加载半结构化数据文件时将文件格式选项 STRIP_NULL_VALUES 设置为 TRUE。此选项将删除 OBJECT 元素或包含“null”值的 ARRAY 元素。
确保每个唯一元素只存储该格式原生支持的单个数据类型（例如，对于 JSON 格式，可以是字符串或数字）。

解析 NULL 值¶

要从 VARIANT "null" 键值输出 SQL NULL 值，请使用 TO_CHAR、TO_VARCHAR 函数将值转换为字符串，例如：

SELECT column1
  , TO_VARCHAR(PARSE_JSON(column1):a)
FROM
  VALUES('{"a" : null}')
, ('{"b" : "hello"}')
, ('{"a" : "world"}');

+-----------------+-----------------------------------+
| COLUMN1         | TO_VARCHAR(PARSE_JSON(COLUMN1):A) |
|-----------------+-----------------------------------|
| {"a" : null}    | NULL                              |
| {"b" : "hello"} | NULL                              |
| {"a" : "world"} | world                             |
+-----------------+-----------------------------------+

Copy