从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 Python UDF 将继续正常运行至 2026 年 6 月 30 日。有关更多信息，请参阅[博客文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。
# 教程：使用 Amazon Redshift Spectrum 查询嵌套数据
本教程演示如何使用 Redshift Spectrum 查询嵌套数据。嵌套数据是包含嵌套字段的数据。嵌套字段是指联接在一起作为单个实体的字段，例如数组、结构或对象。
**Topics**
+ [概述](#tutorial-nested-data-overview)
+ [步骤 1：创建包含嵌套数据的外部表](#tutorial-nested-data-create-table)
+ [步骤 2：使用 SQL 扩展在 Amazon S3 中查询嵌套数据](#tutorial-query-nested-data-sqlextensions)
+ [嵌套数据使用案例](nested-data-use-cases.md)
+ [嵌套数据限制（预览版）](nested-data-restrictions.md)
+ [序列化复杂嵌套 JSON](serializing-complex-JSON.md)
## 概述
Amazon Redshift Spectrum 支持以 Parquet、ORC、JSON 和 Ion 文件格式查询嵌套数据。Redshift Spectrum 使用外部表访问数据。可以创建使用复杂数据类型 `struct`、`array` 和 `map` 的外部表。
例如，假定您的数据文件在名为 `customers` 的文件夹中包含 Amazon S3 中的以下数据。尽管没有单个根元素，但此示例数据中的每个 JSON 对象都表示表中的一行。
```
{"id": 1,
"name": {"given": "John", "family": "Smith"},
"phones": ["123-457789"],
"orders": [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50},
{"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]
}
{"id": 2,
"name": {"given": "Jenny", "family": "Doe"},
"phones": ["858-8675309", "415-9876543"],
"orders": []
}
{"id": 3,
"name": {"given": "Andy", "family": "Jones"},
"phones": [],
"orders": [{"shipdate": "2018-03-02T08:02:15.000Z", "price": 13.50}]
}
```
您可以使用 Amazon Redshift Spectrum 来查询文件中的嵌套数据。下面的教程将向您展示如何使用 Apache Parquet 数据实现这一功能。
### 先决条件
如果您尚未使用 Redshift Spectrum，请按照 [Amazon Redshift Spectrum 入门](c-getting-started-using-spectrum.md)中的步骤操作，然后继续。
要创建外部架构，请将以下命令中的 IAM 角色 ARN 替换为您在[创建 IAM 角色](c-getting-started-using-spectrum.md#c-getting-started-using-spectrum-create-role)中创建的角色 ARN。然后在 SQL 客户端中运行该命令。
```
create external schema spectrum
from data catalog
database 'myspectrum_db'
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;
```
## 步骤 1：创建包含嵌套数据的外部表
您可以通过从 Amazon S3 进行下载来查看[源数据](https://s3.amazonaws.com/redshift-downloads/tickit/spectrum/customers/customer_file1)。
要创建本教程所需的外部表，请运行以下命令。
```
CREATE EXTERNAL TABLE spectrum.customers (
id int,
name struct,
phones array,
orders array>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';
```
在前述示例中，外部表 `spectrum.customers` 使用 `struct` 和 `array` 数据类型定义具有嵌套数据的列。Amazon Redshift Spectrum 支持以 Parquet、ORC、JSON 和 Ion 文件格式查询嵌套数据。`STORED AS` 参数是适用于 Apache Parquet 文件的 `PARQUET`。`LOCATION` 参数必须引用包含嵌套数据或文件的 Amazon S3 文件夹。有关更多信息，请参阅 [CREATE EXTERNAL TABLE](r_CREATE_EXTERNAL_TABLE.md)。
可以在任何级别嵌套 `array` 和 `struct` 类型。例如，您可以定义一个名为 `toparray` 的列，如以下示例所示。
```
toparray array>>>>
```
您也可以为 `struct` 列嵌套 `x` 类型，如以下示例所示。
```
x struct
>
>
```
## 步骤 2：使用 SQL 扩展在 Amazon S3 中查询嵌套数据
Redshift Spectrum 支持通过对 Amazon Redshift SQL 语法的扩展来查询 `array`、`map` 和 `struct` 复杂类型。
### 扩展 1：访问 struct 列
您可以使用将字段名称与路径相连的点表示法从 `struct` 列提取数据。例如，以下查询返回客户的姓氏和名字。名字通过长路径 `c.name.given` 进行访问。姓氏通过长路径 `c.name.family` 进行访问。
```
SELECT c.id, c.name.given, c.name.family
FROM spectrum.customers c;
```
前述的查询返回以下数据。
```
id | given | family
---|-------|-------
1 | John | Smith
2 | Jenny | Doe
3 | Andy | Jones
(3 rows)
```
`struct` 可以是另一个 `struct` 的列，而后者可能是任何级别的另一个 `struct` 的列。访问如此深的嵌套 `struct` 的路径可以是任意长度。例如，请查看以下示例中的 `x` 列的定义。
```
x struct
>
>
```
您可以按 `x.b.d.e` 方式访问 `e` 中的数据。
### 扩展 2：FROM 子句中的范围扩展数组
您可以通过在 `FROM` 子句中指定 `array` 列来代替表名称，以提取 `array` 列（扩展后包括 `map` 列）中的数据。扩展应用于主查询的 `FROM` 子句，也应用于子查询的 `FROM` 子句。
您可以按位置（例如 `c.orders[0]`）引用 `array` 元素。（预览版）
通过将范围扩展 `arrays` 与联接结合使用，您可以实现各种取消嵌套，如下面的使用案例中所述。
#### 使用内部联接取消嵌套
以下查询为具有订单的客户选择客户 ID 和订单发货日期。FROM 子句中的 SQL 扩展 `c.orders o` 取决于别名 `c`。
```
SELECT c.id, o.shipdate
FROM spectrum.customers c, c.orders o
```
对于具有订单的每个客户 `c`，`FROM` 子句为客户 `c` 的每个订单 `o` 返回一行。该行将客户行 `c` 和订单行 `o` 合并起来。然后，`SELECT` 子句只保留 `c.id` 和 `o.shipdate`。结果如下所示。
```
id| shipdate
--|----------------------
1 |2018-03-01 11:59:59
1 |2018-03-01 09:10:00
3 |2018-03-02 08:02:15
(3 rows)
```
别名 `c` 提供对客户字段的访问，而别名 `o` 提供对订单字段的访问。
语义类似于标准 SQL。您可以将 `FROM` 子句视为执行以下嵌套循环，然后 `SELECT` 选择要输出的字段。
```
for each customer c in spectrum.customers
for each order o in c.orders
output c.id and o.shipdate
```
因此，如果客户没有订单，则客户不会显示在结果中。
您还可以将其视为对 `customers` 表和 `orders` 数组执行 `JOIN` 的 `FROM` 子句。实际上，您还可以编写查询，如下面的示例所示。
```
SELECT c.id, o.shipdate
FROM spectrum.customers c INNER JOIN c.orders o ON true
```
**注意**
如果存在名为 `c` 的 schema 且具有名为 `orders` 的表，则 `c.orders` 引用表 `orders`，而不是 `customers` 的数组列。
#### 使用左侧联接取消嵌套
以下查询输出所有客户名称及其订单。如果客户未下订单，则仍返回客户的名称。但在这种情况下，订单列为 NULL，如下面 Jenny Doe 的示例所示。
```
SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price
FROM spectrum.customers c LEFT JOIN c.orders o ON true
```
前述的查询返回以下数据。
```
id | given | family | shipdate | price
----|---------|---------|----------------------|--------
1 | John | Smith | 2018-03-01 11:59:59 | 100.5
1 | John | Smith | 2018-03-01 09:10:00 | 99.12
2 | Jenny | Doe | |
3 | Andy | Jones | 2018-03-02 08:02:15 | 13.5
(4 rows)
```
### 扩展 3：使用别名直接访问标量数组
当 `FROM` 子句中的别名 `p` 范围扩展到标量数组时，查询将 `p` 的值视为 `p`。例如，以下查询生成成对的客户名称和电话号码。
```
SELECT c.name.given, c.name.family, p AS phone
FROM spectrum.customers c LEFT JOIN c.phones p ON true
```
前述的查询返回以下数据。
```
given | family | phone
-------|----------|-----------
John | Smith | 123-4577891
Jenny | Doe | 858-8675309
Jenny | Doe | 415-9876543
Andy | Jones |
(4 rows)
```
### 扩展 4：访问映射的元素
Redshift Spectrum 将 `map` 数据类型视为 `array` 类型，其中包含具有 `key` 列和 `value` 列的 `struct` 类型。`key` 必须是 `scalar`；值可以是任何数据类型。
例如，以下代码使用 `map` 创建外部表来存储电话号码。
```
CREATE EXTERNAL TABLE spectrum.customers2 (
id int,
name struct,
phones map,
orders array>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';
```
由于 `map` 类型的行为类似于具有 `key` 和 `value` 列的 `array` 类型，因此您可以将前面的 schema 视为如下内容。
```
CREATE EXTERNAL TABLE spectrum.customers3 (
id int,
name struct,
phones array>,
orders array>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';
```
以下查询返回具有手机号码的客户名称，并返回每个名称对应的号码。映射查询被视为等同于查询 `struct` 类型的嵌套 `array`。仅当您按前面的说明创建了外部表时，以下查询才返回数据。
```
SELECT c.name.given, c.name.family, p.value
FROM spectrum.customers c, c.phones p
WHERE p.key = 'mobile';
```
**注意**
`map` 的 `key` 是 Ion 和 JSON 文件类型的 `string`。
# 嵌套数据使用案例
本主题介绍嵌套数据的用例。嵌套数据是包含嵌套字段的数据。嵌套字段是指联接在一起作为单个实体的字段，例如数组、结构或对象。
您可以使用常用的 SQL 功能组合前面介绍的扩展。下面的使用案例阐述了一些常见的组合。这些示例帮助演示如何使用嵌套数据。它们不是本教程的组成部分。
**Topics**
+ [提取嵌套数据](#ingesting-nested-data)
+ [使用子查询聚合嵌套数据](#aggregating-with-subquery)
+ [联接 Amazon Redshift 和嵌套数据](#joining-redshift-data)
## 提取嵌套数据
您可以使用 `CREATE TABLE AS` 语句从包含复杂数据类型的外部表中提取数据。以下查询使用 `LEFT JOIN` 从外部表中提取所有客户及其电话号码，并将它们存储在 Amazon Redshift 表 `CustomerPhones` 中。
```
CREATE TABLE CustomerPhones AS
SELECT c.name.given, c.name.family, p AS phone
FROM spectrum.customers c LEFT JOIN c.phones p ON true;
```
## 使用子查询聚合嵌套数据
您可以使用子查询聚合嵌套数据。以下示例说明了此方法。
```
SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount
FROM spectrum.customers c;
```
将返回以下数据。
```
given | family | ordercount
--------|----------|--------------
Jenny | Doe | 0
John | Smith | 2
Andy | Jones | 1
(3 rows)
```
**注意**
当您通过按父行进行分组来聚合嵌套数据时，最高效的方法是前面示例中所示的方法。在该示例中，`c.orders` 的嵌套行按其父行 `c` 分组。或者，如果您知道 `id` 对于每个 `customer` 是唯一的且 `o.shipdate` 从不为 Null，则您可以按下例中所示进行聚合。但一般而言，这种方法的效率比不上前面的示例。
```
SELECT c.name.given, c.name.family, COUNT(o.shipdate) AS ordercount
FROM spectrum.customers c LEFT JOIN c.orders o ON true
GROUP BY c.id, c.name.given, c.name.family;
```
您也可以在 `FROM` 子句中使用一个子查询来编写查询，此子查询引用原级查询的别名 (`c`) 并提取数组数据。以下示例演示了此方法。
```
SELECT c.name.given, c.name.family, s.count AS ordercount
FROM spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s;
```
## 联接 Amazon Redshift 和嵌套数据
您还可以在外部表中联接 Amazon Redshift 数据以及嵌套数据。例如，假设您在 Amazon S3 中具有以下嵌套数据。
```
CREATE EXTERNAL TABLE spectrum.customers2 (
id int,
name struct,
phones array,
orders array>
);
```
此外，假设您在 Amazon Redshift 中具有下面的表。
```
CREATE TABLE prices (
id int,
price double precision
);
```
以下查询根据前面的内容查找每个客户的采购总数和购买金额。下例仅用于举例说明。仅当您创建了前面介绍的表时，它才返回数据。
```
SELECT c.name.given, c.name.family, COUNT(o.date) AS ordercount, SUM(p.price) AS ordersum
FROM spectrum.customers2 c, c.orders o, prices p ON o.item = p.id
GROUP BY c.id, c.name.given, c.name.family;
```
# 嵌套数据限制（预览版）
本主题介绍使用 Redshift Spectrum 读取嵌套数据的限制。嵌套数据是包含嵌套字段的数据。嵌套字段是指联接在一起作为单个实体的字段，例如数组、结构或对象。
**注意**
以下列表中标记的限制（预览版）仅适用于在以下区域中创建的预览集群。
美国东部（俄亥俄州）(us-east-2)
美国东部（弗吉尼亚州北部）(us-east-1)
美国西部（北加利福尼亚）(us-west-1)
亚太地区（东京）(ap-northeast-1)
欧洲地区（爱尔兰）(eu-west-1)
欧洲地区（斯德哥尔摩）(eu-north-1)
有关设置预览版集群的信息，请参阅《Amazon Redshift 管理指南》**中的[创建预览版集群](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-clusters-console.html#cluster-preview)。
以下限制适用于嵌套数据：
+ `array` 或 `map` 类型可以包含其他 `array` 或 `map` 类型，前提是对嵌套 `arrays` 或 `maps` 的查询不返回 `scalar` 值。（预览版）
+ Amazon Redshift Spectrum 只支持将复杂数据类型用作外部表。
+ 子查询结果列必须是顶级列。（预览版）
+ 如果 `OUTER JOIN` 表达式引用嵌套表，则它只能引用该表及其嵌套数组（和映射）。如果 `OUTER JOIN` 表达式不引用嵌套表，它可以引用任何数量的非嵌套表。
+ 如果子查询中的 `FROM` 子句引用一个嵌套表，则它无法引用任何其他表。
+ 如果子查询依赖于引用父表的嵌套表，那么子查询只能在 `FROM` 子句中使用父表。您无法在任何其他子句中使用此父项，如 `SELECT` 或 `WHERE` 子句。例如，以下查询无法运行，因为子查询的 `SELECT` 子句引用父表 `c`。
```
SELECT c.name.given
FROM spectrum.customers c
WHERE (SELECT COUNT(c.id) FROM c.phones p WHERE p LIKE '858%') > 1;
```
以下查询之所以有效，是因为只在子查询的 `FROM` 子句中使用了父 `c`。
```
SELECT c.name.given
FROM spectrum.customers c
WHERE (SELECT COUNT(*) FROM c.phones p WHERE p LIKE '858%') > 1;
```
+ 在 `FROM` 子句之外的任何位置访问嵌套数据的子查询必须返回单个值。唯一的例外是 `(NOT) EXISTS` 子句中的 `WHERE` 运算符。
+ `(NOT) IN`不支持 。
+ 所有嵌套类型的最大嵌套深度均为 100。该限制适用于所有文件格式（Parquet、ORC、Ion 和 JSON）。
+ 访问嵌套数据的聚合子查询只能引用 `arrays` 子句中的 `maps` 和 `FROM`，而不能引用外部表。
+ 不支持查询 Redshift Spectrum 表中嵌套数据的伪列。有关更多信息，请参阅 [Pseudocolumns](c-spectrum-external-tables.md#c-spectrum-external-tables-pseudocolumns)。
+ 通过在 `FROM` 子句中指定数组列或映射列来提取这些列中的数据时，如果值为 `scalar`，则只能从这些列中选择值。例如，以下查询都尝试从数组内部 `SELECT` 元素。选择 `arr.a` 的查询之所以起作用，是因为 `arr.a` 是一个 `scalar` 值。第二个查询不起作用，因为 `array` 是从 `FROM` 子句中的 `s3.nested table` 提取的数组。（预览版）
```
SELECT array_column FROM s3.nested_table;
array_column
-----------------
[{"a":1},{"b":2}]
SELECT arr.a FROM s3.nested_table t, t.array_column arr;
arr.a
-----
1
--This query fails to run.
SELECT array FROM s3.nested_table tab, tab.array_column array;
```
不能在 `FROM` 子句中使用本身来自另一个数组或映射的数组或映射。要选择嵌套在其他数组中的数组或其他复杂结构，请考虑在 `SELECT` 语句中使用索引。
# 序列化复杂嵌套 JSON
本主题演示如何以 JSON 格式序列化嵌套数据。嵌套数据是包含嵌套字段的数据。嵌套字段是指联接在一起作为单个实体的字段，例如数组、结构或对象。
本教程中演示的方法的替代方法是以序列化 JSON 格式查询顶级嵌套集合列。您可以通过 Redshift Spectrum 使用序列化以 JSON 格式检查、转换和摄取嵌套数据。ORC、JSON、Ion 和 Parquet 格式支持此方法。使用会话配置参数 `json_serialization_enable` 配置序列化行为。设置时，复杂的 JSON 数据类型将序列化为 VARCHAR(65535)。嵌套的 JSON 可以通过 [JSON 函数](json-functions.md) 访问。有关更多信息，请参阅 [json\$1serialization\$1enable](r_json_serialization_enable.md)。
例如，如果不设置 `json_serialization_enable`，则以下访问嵌套列的查询直接失败。
```
SELECT * FROM spectrum.customers LIMIT 1;
=> ERROR: Nested tables do not support '*' in the SELECT clause.
SELECT name FROM spectrum.customers LIMIT 1;
=> ERROR: column "name" does not exist in customers
```
设置 `json_serialization_enable` 允许直接查询顶级集合。
```
SET json_serialization_enable TO true;
SELECT * FROM spectrum.customers order by id LIMIT 1;
id | name | phones | orders
---+--------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------
1 | {"given": "John", "family": "Smith"} | ["123-457789"] | [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50}, {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]
SELECT name FROM spectrum.customers order by id LIMIT 1;
name
---------
{"given": "John", "family": "Smith"}
```
在序列化嵌套 JSON 时，请考虑以下项目。
+ 当集合列被序列化为 VARCHAR(65535) 时，不能再将其嵌套子字段作为查询语法的一部分直接访问（即在筛选器子句中）。但是，JSON 函数可用于访问嵌套的 JSON。
+ 不支持以下专门化表示：
+ ORC 联合
+ 具有复杂类型键的 ORC 映射
+ Ion 数据报
+ Ion SEXP
+ 时间戳以 ISO 序列化字符串的形式返回。
+ 基本映射键被提升为字符串（例如 `1` 到 `"1"`）。
+ 顶级 null 值被序列化为 NULL。
+ 如果序列化溢出最大 VARCHAR 大小 65535，则单元格将设置为 NULL。
## 序列化包含 JSON 字符串的复杂类型
预设情况下，嵌套集合中包含的字符串值被序列化为转义 JSON 字符串。当字符串为有效的 JSON 时，转义可能是不可取的。相反，您可能希望直接将嵌套子元素或 VARCHAR 字段编写为 JSON。通过 `json_serialization_parse_nested_strings` 会话级别配置启用此行为。设置 `json_serialization_enable` 和 `json_serialization_parse_nested_strings` 时，有效的 JSON 值将被内联序列化，没有转义字符。当该值是无效的 JSON 时，它会被转义，就好像未设置 `json_serialization_parse_nested_strings` 配置值一样。有关更多信息，请参阅 [json\$1serialization\$1parse\$1nested\$1strings](r_json_serialization_parse_nested_strings.md)。
例如，假设前面示例中的数据包含 JSON 作为 `name` VARCHAR(20) 字段中的 `structs` 复杂类型：
```
name
---------
{"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"}
```
当设置 `json_serialization_parse_nested_strings` 时，`name` 列序列化如下：
```
SET json_serialization_enable TO true;
SET json_serialization_parse_nested_strings TO true;
SELECT name FROM spectrum.customers order by id LIMIT 1;
name
---------
{"given": {"first":"John","middle":"James"}, "family": "Smith"}
```
而不是像这样进行转义：
```
SET json_serialization_enable TO true;
SELECT name FROM spectrum.customers order by id LIMIT 1;
name
---------
{"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"}
```