加载半结构化数据简介

本主题介绍半结构化数据，并提供有关如何在 Snowflake 中加载和存储此类数据的信息。

本主题内容：

什么是半结构化数据？

半结构化数据是指不符合传统结构化数据标准，但包含用于标识数据中的个别不同实体的标签或其他类型的标志的数据。

将半结构化数据与结构化数据区分开来的两个关键属性是嵌套的数据架构以及不具备固定架构：

  • 结构化数据需要具备固定架构，必须先定义该架构，之后才能在关系数据库系统中加载和查询数据。半结构化数据不需要预先定义架构，并且可以持续变化（即可以随时添加新属性）。

    此外，同一类中的实体可以具有不同的属性，即使其组合在一起也是如此，属性的顺序并不重要。

  • 结构化数据将数据表示为平面表，与此不同，半结构化数据可以包含嵌套信息的 N 级层次结构。

什么是分层数据？

半结构化数据通常按层次结构组织。可以通过嵌套较为简单的数据类型（例如 数组 和 对象）来构建复杂的数据结构。（注意：Snowflake OBJECT 对应于“字典”或“映射”。Snowflake 对象并不是“面向对象编程”中所指的“对象”。

例如， JSON 数据中可以有包含数组的对象。该数组的每个单元本身均可包含一个嵌套对象或数组。

可以使用 Snowflake 数据类型构造层次结构，以利用数据类型的以下属性来保存半结构化数据：

  • VARIANT 可以保存其他任何数据类型的值，包括 ARRAY 或 OBJECT。

  • 或保存 VARIANT 类型的值的 ARRAY OBJECT。

例如，假设您要存储不同类型自然灾害的发生日期。您可以创建一个 OBJECT，其中包含“Hurricane”、“Earthquake”、“Flood”等键。与每个键关联的值可以是一个 ARRAY，其中包含每种类型灾害的发生日期。由于每个键值对中的值都必须是 VARIANT，所以每个日期数组都将存储为对应 OBJECT 内的 VARIANT 中包装的 ARRAY。层次结构的顶层类似于以下内容（花括号表示 OBJECT，其中包含键值对）：

{
    "Flood": flood_date_array::VARIANT,
    "Earthquake": earthquake_date_array::VARIANT,
    ...
}
Copy

再举一个例子，假设您要按时间顺序存储单独一种灾害的列表。针对本例，外部数据类型可能是 ARRAY。ARRAY 的每个单元都可能包含一个 OBJECT（包装在 VARIANT 中），其中包含有关事件的信息的键值对。例如，每个描述地震的 OBJECT 都可能具有“Timestamp”、“Location”和“Magnitude”等键。每个描述龙卷风的 OBJECT 都可能有“Timestamp”和“Maximum_wind_speed”等键。

[
    {
        "Event_ID": 54::VARIANT,
        "Type": "Earthquake"::VARIANT,
        "Magnitude": 7.4::VARIANT,
        "Timestamp": "2018-06-09 12:32:15"::TIMESTAMP_LTZ::VARIANT
        ...
    }::VARIANT,
    {
        "Event_ID": 55::VARIANT,
        "Type": "Tornado"::VARIANT,
        "Maximum_wind_speed": 186::VARIANT,
        "Timestamp": "2018-07-01 09:42:55"::TIMESTAMP_LTZ::VARIANT
        ...
    }::VARIANT
]
Copy

您可以创建几乎任何深度或广度的数据层次结构（仅受限于每种数据类型的存储限制）。例如，包含龙卷风信息的 OBJECT 可能需要有关龙卷风期间不同时间的风速信息，因此数据结构可能如下所示：

  1. 顶层是 ARRAY。

  2. 每个 ARRAY 单元均包含一个描述一场龙卷风的 OBJECT。

  3. 每个 OBJECT 都包含一个风速数据的 ARRAY。

  4. 内部 ARRAY 的每个单元都是一个 OBJECT，其中包含的数据具有如下键：

    • 风速对应的时间戳。

    • 风速对应的位置。

    • 风速（单位为 KPH，即公里/小时）。

    在某些情况下，数据可能并不完整。例如，如果特定位置的风速是根据龙卷风后可见的损坏估算的（而非在龙卷风期间直接测量），则数据可能包括位置和风速，但不包括时间戳。

加载半结构化数据

Snowflake 可以导入 JSON、Avro、 ORC、Parquet 和 XML 格式的半结构化数据，并将其存储在 专门设计用于支持半结构化数据的 Snowflake 数据类型 中。

根据数据的结构、数据的大小以及用户选择导入数据的方式，半结构化数据可存储在单个列中，也可拆分到多个列中。

将半结构化数据加载到表中的步骤类似于将结构化数据加载到表中的步骤。但在加载和存储半结构化数据时，还可以显式指定全部结构、部分结构，或者不指定任何结构：

  • 如果数据是一组键值对，则可以将其加载到 类型为 OBJECT 的列中。

  • 如果数据是数组，则可以将其加载到类型为 ARRAY 的列中。

  • 如果您具有 分层数据，则可以执行以下任一操作：

    • 将数据拆分到多列中。您可以执行以下操作：

      • 将半结构化数据的列显式 提取并转换 为目标表中的单独列。

      • 使用 Snowflake 自动 检测和检索 暂存半结构化数据文件中的列定义。基于列定义创建 Snowflake 表、外部表或视图。为了节省时间，可使用从暂存文件中自动检索的列定义创建表。

    • 将数据存储在类型为 VARIANT 的单独一列中。您可以执行以下操作：

      • 显式指定结构（例如，指定数据类型为 VARIANT、 ARRAY 和 OBJECT 的层次结构）。

      • 在不显式指定结构的情况下加载数据。如果指定了 Snowflake 可以识别和解析的数据格式（JSON、Avro、Parquet 或 ORC），数据将转换为使用 Snowflake VARIANT、 ARRAY 和 OBJECT 数据类型的内部数据格式。

如果数据复杂型较高，或者单个值所需存储空间超过 16MB 左右，则可以结合使用上述多种技术。例如，可将数据拆分到多列中，其中一些列可以包含显式指定的数据类型层次结构。

可以通过以下方式加载半结构化数据：

  • 在创建表和加载数据时指定输入数据格式和 Snowflake 数据类型。例如，在下面的代码中， TABLE CREATE 语句中指定了 VARIANT 数据类型，而 COPY INTO <table> 命令的 TYPE = <data_format> 子句中指定了 JSON 输入数据格式：

    CREATE TABLE my_table (my_variant_column VARIANT);
COPY INTO my_table ... FILE FORMAT = (TYPE = 'JSON') ...
    Copy

  • 通过调用适当的函数来转换数据，指定输入数据格式和 Snowflake 数据类型。例如，要将 JSON 格式的数据转换为 VARIANT 值，请调用 PARSE_JSON，如下所示：

    INSERT INTO my_table (my_variant_column) SELECT PARSE_JSON('{...}');
    Copy

当数据存储在 ARRAY、 OBJECT 或 VARIANT 数据类型或这些类型的层次结构中时，可以对其进行 查询。

存储半结构化数据

半结构化数据通常存储为以下 Snowflake 数据类型：

  • ARRAY：类似于其他语言中的数组。

  • OBJECT：类似于 JSON 对象，在许多语言中也称为“字典”、“哈希”或“映射”。这包含键值对。

  • VARIANT：此数据类型可保存其他任何数据类型（包括 ARRAY 和 OBJECT）的值。VARIANT 用于构建和存储 分层数据。

（如果导入的数据在存储前被拆分到多个列中，则其中部分或全部列可以是简单数据类型，例如 FLOAT、 VARCHAR 等）

ARRAY、 OBJECT 和 VARIANT 数据类型可以单独使用，也可嵌套使用，从而构建层次结构。

如果数据以 JSON、Avro、 ORC 或 Parquet 格式导入，则 Snowflake 可以为您构建层次结构，并将其存储为 VARIANT。您也可以手动创建层次结构。

无论层次结构是如何构建的，Snowflake 都会将数据转换为使用 ARRAY、 OBJECT 和 VARIANT 的优化型内部存储格式。这种内部存储格式支持快速、高效的 SQL 查询。

有关 ARRAY、 OBJECT 和 VARIANT 数据类型的更多信息，请参阅 半结构化数据类型。

查询半结构化数据

Snowflake 支持使用运算符实现以下目的：

有关查询半结构化数据的详细信息，请参阅 查询半结构化数据。

有关通过指定 XML 标签执行 XML 查询的信息，请参阅 XMLGET 函数的文档。

语言: 中文