教程：使用 Amazon Redshift Spectrum 查询嵌套数据

本教程演示如何使用 Redshift Spectrum 查询嵌套数据。嵌套数据是包含嵌套字段的数据。嵌套字段是指联接在一起作为单个实体的字段，例如数组、结构或对象。

概述

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 入门中的步骤操作，然后继续。

要创建外部架构，请将以下命令中的 IAM 角色 ARN 替换为您在创建 IAM 角色中创建的角色 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 进行下载来查看源数据。

要创建本教程所需的外部表，请运行以下命令。

CREATE EXTERNAL TABLE spectrum.customers (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
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。

可以在任何级别嵌套 array 和 struct 类型。例如，您可以定义一个名为 toparray 的列，如以下示例所示。

toparray array<struct<nestedarray:
         array<struct<morenestedarray: 
         array<string>>>>>

您也可以为 struct 列嵌套 x 类型，如以下示例所示。

x struct<a: string,
         b: struct<c: integer,
                   d: struct<e: string>
                  >
        >

步骤 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<a: string,
         b: struct<c: integer, 
                      d: struct<e: string>
                  >
        >

您可以按 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 

使用左侧联接取消嵌套

以下查询输出所有客户名称及其订单。如果客户未下订单，则仍返回客户的名称。但在这种情况下，订单列为 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<given:varchar(20), family:varchar(20)>,
  phones map<varchar(20), varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';

由于 map 类型的行为类似于具有 key 和 value 列的 array 类型，因此您可以将前面的 schema 视为如下内容。

CREATE EXTERNAL TABLE spectrum.customers3 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<struct<key:varchar(20), value:varchar(20)>>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
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';