半结构化数据支持的格式¶
本主题介绍半结构化数据支持的格式。
Snowflake 原生支持以下半结构化数据格式。具体而言，Snowflake 在 COPY 命令中提供了相关选项，用于加载和卸载这些格式的数据文件。
JSON¶
什么是 JSON？¶
JSON (JavaScript Object Notation) 是一种基于 JavaScript 编程语言子集的轻量级、纯文本、数据交换格式。
JSON 数据可由任何应用程序生成。一些常见示例包括：
使用原生方法生成 JSON 的 JavaScript 应用程序。
使用库（通常带有扩展）生成 JSON 数据的非 JavaScript 应用程序。
临时的 JavaScript 生成器。
JSON 文档的串联（可以按行分隔，也可以不按行分隔）。
由于没有正式的规范，因此各种实现之间存在显著差异。如果 JSON 解析器的语言定义很严格，那么这些差异将导致无法导入类似 JSON 的数据集。为了使 JSON 数据集的导入尽可能没有问题，Snowflake 遵循“自由接受”的规则。其目的是接受尽可能广泛的 JSON 和类似 JSON 的输入，以便进行明确的解释。
本主题介绍 Snowflake 接受的 JSON 文档的语法。
有关 JSON 的更多信息，请参阅 json.org (http://www.json.org)。
基本 JSON 语法¶
JSON 数据是按对象和数组分组的名/值对的分层集合：
冒号“
:”分隔名/值对中的名称和值。花括号“
{}”表示对象。方括号“
[]”表示数组。逗号“
,”分隔对象和数组中的实体。
名/值对¶
JSON 名/值对由字段名称（在双引号内）组成，后跟冒号，然后是值。
例如：
{"firstName":"John", "empid":45611}
支持的数据类型¶
名/值对中的值可以是：
数字（整数或浮点）
字符串（在双引号内）
布尔（true 或 false）
数组（在方括号内）
对象（在花括号内）
Null
对象¶
JSON 对象写在花括号内。对象可以包含多个名/值对，用逗号分隔。例如：
{"firstName":"John", "lastName":"Doe"}
数组¶
JSON 数组写在方括号内。数组可以包含多个对象，用逗号分隔。例如：
{"employees":[
{"firstName":"John", "lastName":"Doe"},
{"firstName":"Anna", "lastName":"Smith"},
{"firstName":"Peter", "lastName":"Jones"}
]
}
JSON 文档示例¶
FILE NAME： json_sample_data1
包含一个有 3 个简单员工记录（对象）的数组：
{"root":[{"employees":[ {"firstName":"John", "lastName":"Doe"}, {"firstName":"Anna", "lastName":"Smith"}, {"firstName":"Peter", "lastName":"Jones"} ]}]}
FILE NAME： json_sample_data2
包含一个数组，其中有 3 个员工记录（对象）及其关联的相依数据（孩子、孩子的姓名和年龄、员工居住的城市，以及员工在这些城市居住的年限）：
{"root": [ { "kind": "person", "fullName": "John Doe", "age": 22, "gender": "Male", "phoneNumber": {"areaCode": "206", "number": "1234567"}, "children": [ { "name": "Jane", "gender": "Female", "age": "6" }, { "name": "John", "gender": "Male", "age": "15" } ], "citiesLived": [ { "place": "Seattle", "yearsLived": ["1995"] }, { "place": "Stockholm", "yearsLived": ["2005"] } ] }, {"kind": "person", "fullName": "Mike Jones", "age": 35, "gender": "Male", "phoneNumber": { "areaCode": "622", "number": "1567845"}, "children": [{ "name": "Earl", "gender": "Male", "age": "10"}, {"name": "Sam", "gender": "Male", "age": "6"}, { "name": "Kit", "gender": "Male", "age": "8"}], "citiesLived": [{"place": "Los Angeles", "yearsLived": ["1989", "1993", "1998", "2002"]}, {"place": "Washington DC", "yearsLived": ["1990", "1993", "1998", "2008"]}, {"place": "Portland", "yearsLived": ["1993", "1998", "2003", "2005"]}, {"place": "Austin", "yearsLived": ["1973", "1998", "2001", "2005"]}]}, {"kind": "person", "fullName": "Anna Karenina", "age": 45, "gender": "Female", "phoneNumber": { "areaCode": "425", "number": "1984783"}, "citiesLived": [{"place": "Stockholm", "yearsLived": ["1992", "1998", "2000", "2010"]}, {"place": "Russia", "yearsLived": ["1998", "2001", "2005"]}, {"place": "Austin", "yearsLived": ["1995", "1999"]}]} ] }
Avro¶
什么是 Avro？¶
Avro 是一个开源数据序列化和 RPC 框架，最初是为与 Apache Hadoop 搭配使用而开发的。它利用 JSON 中定义的架构，以紧凑的二进制格式生成序列化数据。序列化数据可发送到任何目标（即应用程序或程序），并且由于数据中包含了架构，因此可以在目标上轻松地进行反序列化。
Avro 架构由 JSON 字符串、对象或数组组成，定义了架构类型和架构类型的数据属性（字段名称、数据类型等）。属性因架构类型而异。支持数组、映射等复杂数据类型。
Snowflake 将 Avro 数据读取到单个 VARIANT 列中。可以像查询 JSON 数据一样，使用类似的命令和函数查询 VARIANT 列中的数据。
有关更多信息，请参阅 avro.apache.org (http://avro.apache.org)。
Avro 架构示例¶
{
"type": "record",
"name": "person",
"namespace": "example.avro",
"fields": [
{"name": "fullName", "type": "string"},
{"name": "age", "type": ["int", "null"]},
{"name": "gender", "type": ["string", "null"]}
]
}
ORC¶
什么是 ORC？¶
ORC (Optimized Row Columnar) 是一种用于存储 Hive 数据的二进制格式。与早期的 Hive 文件格式相比，ORC 旨在实现高效压缩并改进读取、写入和处理数据的性能。有关 ORC 的更多信息，请参阅 https://orc.apache.org/ (https://orc.apache.org//)。
Snowflake 将 ORC 数据读取到单个 VARIANT 列中。可以像查询 JSON 数据一样，使用类似的命令和函数查询 VARIANT 列中的数据。
此外，还可以使用 CREATE TABLE AS SELECT 语句将暂存 ORC 文件中的列提取到单独的表列中。
备注
将 Map 数据反序列化为一个对象数组，例如：
"map": [{"key": "chani", "value": {"int1": 5, "string1": "chani"}}, {"key": "mauddib", "value": {"int1": 1, "string1": "mauddib"}}]
将 Union 数据反序列化为单个对象，例如：
{"time": "1970-05-05 12:34:56.197", "union": {"tag": 0, "value": 3880900}, "decimal": 3863316326626557453.000000000000000000}
已加载到 VARIANT 列中的 ORC 数据示例¶
+--------------------------------------+
| SRC |
|--------------------------------------|
| { |
| "boolean1": false, |
| "byte1": 1, |
| "bytes1": "0001020304", |
| "decimal1": 12345678.654745, |
| "double1": -1.500000000000000e+01, |
| "float1": 1.000000000000000e+00, |
| "int1": 65536, |
| "list": [ |
| { |
| "int1": 3, |
| "string1": "good" |
| }, |
| { |
| "int1": 4, |
| "string1": "bad" |
| } |
| ] |
| } |
+--------------------------------------+
Parquet¶
什么是 Parquet？¶
Parquet 是一种压缩、高效的列式数据表示形式，专为 Hadoop 生态系统中的项目而设计。该文件格式支持复杂的嵌套数据结构，使用 Dremel 记录粉碎和汇编算法。Parquet 文件无法在文本编辑器中打开。有关更多信息，请参阅 parquet.apache.org/docs/ (https://parquet.apache.org/docs/)。
备注
Snowflake 支持使用 Apache Iceberg™ 表的 Parquet 编写器 V2 或使用 矢量化扫描器 生成的 Parquet 文件。
根据您的加载用例，Snowflake 可将 Parquet 数据读取到单个 VARIANT 列中，或直接读取到表列中（例如，当您 从兼容 Iceberg 的 Parquet 文件加载数据时）。
可以像查询 JSON 数据一样，使用类似的命令和函数查询 VARIANT 列中的数据。还可以使用 CREATE TABLE AS SELECT 语句将暂存的 Parquet 文件中的 select 列提取到单独的表列中。
已加载到 VARIANT 列中的 Parquet 数据示例¶
+------------------------------------------+
| SRC |
|------------------------------------------|
| { |
| "continent": "Europe", |
| "country": { |
| "city": { |
| "bag": [ |
| { |
| "array_element": "Paris" |
| }, |
| { |
| "array_element": "Nice" |
| }, |
| { |
| "array_element": "Marseilles" |
| }, |
| { |
| "array_element": "Cannes" |
| } |
| ] |
| }, |
| "name": "France" |
| } |
| } |
+------------------------------------------+
XML¶
什么是 XML？¶
XML (eXtensible Markup Language) 是一种标记语言，它定义了一组用于编码文档的规则。它最初基于 SGML，这是为了使构成文档的结构和元素实现标准化而开发的另一种标记语言。
自推出以来，XML 已超越了最初对文档的关注，涵盖了广泛的用途，包括表示任意数据结构以及作为通信协议的基础语言。由于其可扩展性、多功能性和可用性，它已成为 Web 上最常用的数据交换标准之一。
XML 文档主要由以下结构组成：
标签（用尖括号标识，
<and>）元素
元素通常由“起始”标签和匹配的“结束”标签组成，标签之间的文本构成元素的内容。元素也可以由“空元素”标签组成，不包含“结束”标签。“起始”和“空元素”标签可能包含某些属性，这些属性有助于定义元素的特征或元数据。
当您查询 XML 数据时，美元符号运算符 ($) 会将其运算的值的内容以 VARIANT 值的形式返回。对于元素，则会返回该元素的内容：
如果该元素包含文本，则文本作为 VARIANT 值返回。
如果该元素包含另一个元素，则该元素作为 VARIANT 值以 XML 格式返回。
如果该元素包含一系列元素，则元素数组作为 VARIANT 值以 JSON 格式返回。
使用以下运算符在查询中访问 VARIANT 值：
$表示值的内容。@表示值的名称。当您遍历具有不同名称的元素时，此运算符非常有用。使用
@attribute_name获取命名属性的内容。例如，对于@attr，属性名称是attr。查询会返回与紧跟在和号后面的名称对应的属性的内容。如果未找到属性，则返回 NULL。
有关查询 XML 数据的示例，请参阅 查询 XML 数据的示例。
您可以使用以下函数来处理 XML 数据：
处理 XML 的示例¶
以下示例向您展示如何加载和查询 XML 数据。
加载 XML 文档的示例¶
以下示例向您展示如何加载以下 XML 文档：
<?xml version="1.0"?>
<!DOCTYPE parts system "parts.dtd">
<?xml-stylesheet type="text/css" href="xmlpartsstyle.css"?>
<parts>
<part count="4">
<item>Spark Plugs</item>
<partnum>A3-400</partnum>
<manufacturer>ABC company</manufacturer>
<price units="dollar"> 27.00</price>
</part>
<part count="1">
<item>Motor Oil</item>
<partnum>B5-200</partnum>
<source>XYZ company</source>
<price units="dollar"> 14.00</price>
</part>
<part count="1">
<item>Motor Oil</item>
<partnum>B5-300</partnum>
<source>XYZ company</source>
<price units="dollar"> 16.75</price>
</part>
<part count="1">
<item>Engine Coolant</item>
<partnum>B6-120</partnum>
<source>XYZ company</source>
<price units="dollar"> 19.00</price>
</part>
<part count="1">
<item>Engine Coolant</item>
<partnum>B6-220</partnum>
<source>XYZ company</source>
<price units="dollar"> 18.25</price>
</part>
</parts>
完成以下步骤以加载 XML 文档：
将 XML 文档的内容复制到文件系统中的文件中。
此示例假设文件在
/examples/xml/目录中命名为auto-parts.xml。在内部暂存位置暂存文件：
PUT FILE:///examples/xml/auto-parts.xml @~/xml_stage;
为 XML 文档创建表：
CREATE OR REPLACE TABLE sample_xml_parts(src VARIANT);
将暂存的 XML 文件加载到表中：
COPY INTO sample_xml_parts FROM @~/xml_stage FILE_FORMAT=(TYPE=XML) ON_ERROR='CONTINUE';
查询 XML 数据的示例¶
以下示例查询 XML 数据。
直接查询 XML 数据¶
查询包含 XML 数据的列以返回 XML 文档。
以下示例直接查询 加载 XML 文档的示例 中加载的 XML 数据：
SELECT src FROM sample_xml_parts;
+----------------------------------------------+
| SRC |
|----------------------------------------------|
| <parts> |
| <part count="4"> |
| <item>Spark Plugs</item> |
| <partnum>A3-400</partnum> |
| <manufacturer>ABC company</manufacturer> |
| <price units="dollar">27.00</price> |
| </part> |
| <part count="1"> |
| <item>Motor Oil</item> |
| <partnum>B5-200</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">14.00</price> |
| </part> |
| <part count="1"> |
| <item>Motor Oil</item> |
| <partnum>B5-300</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">16.75</price> |
| </part> |
| <part count="1"> |
| <item>Engine Coolant</item> |
| <partnum>B6-120</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">19.00</price> |
| </part> |
| <part count="1"> |
| <item>Engine Coolant</item> |
| <partnum>B6-220</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">18.25</price> |
| </part> |
| </parts> |
+----------------------------------------------+
使用运算符查询 XML 数据¶
使用 $ 和 @ 运算符查询包含 XML 数据的列。
以下示例使用 $ 运算符查询 加载 XML 文档的示例 中加载的 XML 数据。查询显示有关元素的值 ($) 和名称 (@) 的元数据。
SELECT src:"$" FROM sample_xml_parts;
+--------------------------------+
| SRC:"$" |
|--------------------------------|
| [ |
| { |
| "$": [ |
| { |
| "$": "Spark Plugs", |
| "@": "item" |
| }, |
| { |
| "$": "A3-400", |
| "@": "partnum" |
| }, |
| { |
| "$": "ABC company", |
| "@": "manufacturer" |
| }, |
| { |
| "$": 27, |
| "@": "price", |
| "@units": "dollar" |
| } |
| ], |
| "@": "part", |
| "@count": 4, |
| "item": 0, |
| "manufacturer": 2, |
| "partnum": 1, |
| "price": 3 |
| }, |
| { |
| "$": [ |
| { |
| "$": "Motor Oil", |
| "@": "item" |
| }, |
| { |
| "$": "B5-200", |
| "@": "partnum" |
| }, |
| { |
| "$": "XYZ company", |
| "@": "source" |
| }, |
| { |
| "$": 14, |
| "@": "price", |
| "@units": "dollar" |
| } |
| ], |
| "@": "part", |
| "@count": 1, |
| "item": 0, |
| "partnum": 1, |
| "price": 3, |
| "source": 2 |
| }, |
| |
| ... |
| |
+--------------------------------+
以下示例使用 @ 运算符查询相同的 XML 数据。查询显示根元素的名称。
SELECT src:"@" FROM sample_xml_parts;
+---------+
| SRC:"@" |
|---------|
| "parts" |
+---------+
以下示例使用 $ 运算符和 @ 运算符查询相同的 XML 数据。在根元素的子元素数组中，查询显示位于第一个 (0) 和第二个 (1) 索引位置的元素的 count 属性值。
SELECT src:"$"[0]."@count", src:"$"[1]."@count" FROM sample_xml_parts;
+---------------------+---------------------+
| SRC:"$"[0]."@COUNT" | SRC:"$"[1]."@COUNT" |
|---------------------+---------------------|
| 4 | 1 |
+---------------------+---------------------+
使用 XMLGET 函数查询 XML 数据¶
使用 XMLGET 函数查询包含 XML 数据的列。
以下示例查询 加载 XML 文档的示例 中加载的 XML 数据，并返回 XML 数据的根元素中元素的第一个实例。实例编号从 0 开始，而不是从 1 开始。因此，以下查询是等效的：
SELECT XMLGET(src, 'part') FROM sample_xml_parts;
SELECT XMLGET(src, 'part', 0) FROM sample_xml_parts;
+--------------------------------------------+
| XMLGET(SRC, 'PART') |
|--------------------------------------------|
| <part count="4"> |
| <item>Spark Plugs</item> |
| <partnum>A3-400</partnum> |
| <manufacturer>ABC company</manufacturer> |
| <price units="dollar">27.00</price> |
| </part> |
+--------------------------------------------+
此查询返回 XML 数据的根元素中的第三个元素（从 0 开始）。
SELECT XMLGET(src, 'part', 3) FROM sample_xml_parts;
+---------------------------------------+
| XMLGET(SRC, 'PART', 3) |
|---------------------------------------|
| <part count="1"> |
| <item>Engine Coolant</item> |
| <partnum>B6-120</partnum> |
| <source>XYZ company</source> |
| <price units="dollar">19.00</price> |
| </part> |
+---------------------------------------+
查询 XML 数据以使用多个函数提取元素内容¶
以下示例使用 FLATTEN 函数与 XMLGET 函数来提取 加载 XML 文档的示例 中加载的 XML 数据中的元素内容。
该示例使用 COALESCE 函数返回子元素 manufacturer 或 source``（如果存在），并转换为 VARCHAR 值。传递给 FLATTEN 的 ``SRC:"$" 指定了根元素 parts 中的值。LATERAL FLATTEN 遍历所有传入的重复元素。
SELECT XMLGET(VALUE, 'item'):"$"::VARCHAR AS item,
XMLGET(VALUE, 'partnum'):"$"::VARCHAR AS partnum,
COALESCE(XMLGET(VALUE, 'manufacturer'):"$"::VARCHAR,
XMLGET(VALUE, 'source'):"$"::VARCHAR) AS manufacturer_or_source,
XMLGET(VALUE, 'price'):"$"::VARCHAR AS price,
FROM sample_xml_parts,
LATERAL FLATTEN(INPUT => SRC:"$");
+----------------+---------+------------------------+-------+
| ITEM | PARTNUM | MANUFACTURER_OR_SOURCE | PRICE |
|----------------+---------+------------------------+-------|
| Spark Plugs | A3-400 | ABC company | 27 |
| Motor Oil | B5-200 | XYZ company | 14 |
| Motor Oil | B5-300 | XYZ company | 16.75 |
| Engine Coolant | B6-120 | XYZ company | 19 |
| Engine Coolant | B6-220 | XYZ company | 18.25 |
+----------------+---------+------------------------+-------+