从补丁 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`。