Amazon Redshift
数据库开发人员指南 (API 版本 2012-12-01)
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

步骤 2:使用 SQL 扩展在 Amazon S3 中查询嵌套数据

Redshift Spectrum 支持通过对 Amazon Redshift SQL 语法的扩展来查询 arraymapstruct 复杂类型。

扩展 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> > >

您可以按 e 方式访问 x.b.d.e 中的数据。

注意

您使用 struct 只是为了描述指向它们所包含的字段的路径。您无法在查询中直接访问它们,也无法从查询中返回它们。

扩展 2:FROM 子句中的范围扩展数组

您可以通过在 array 子句中指定 map 列来代替表名称,以提取 array 列(扩展后包括 FROM 列)中的数据。扩展应用于主查询的 FROM 子句,也应用于子查询的 FROM 子句。您无法按位置(例如 array)引用 c.orders[0] 元素。

通过将范围扩展 arrays 与联接结合使用,您可以实现各种取消嵌套,如下面的使用案例中所述。

使用内部联接取消嵌套

以下查询为具有订单的客户选择客户 ID 和订单发货日期。FROM 子句中的 SQL 扩展 c.orders o 取决于别名 c

SELECT c.id, o.shipdate FROM spectrum.customers c, c.orders o

对于具有订单的每个客户 cFROM 子句为客户 o 的每个订单 c 返回一行。该行将客户行 c 和订单行 o 合并起来。然后,SELECT 子句只保留 c.ido.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

因此,如果客户没有订单,则客户不会显示在结果中。

您还可以将其视为对 FROM 表和 JOIN 数组执行 customersorders 子句。实际上,您还可以编写查询,如下面的示例所示。

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 2 | 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:使用别名直接访问标量数组

p 子句中的别名 FROM 范围扩展到标量数组时,查询将 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 类型,其中包含具有 struct 列和 key 列的 value 类型。key 必须是 scalar;值可以是任何数据类型。

例如,以下代码使用 map 创建外部表来存储电话号码。

CREATE EXTERNAL TABLE spectrum.customers ( id int, name struct<given:varchar(20), family:varchar(20)>, phones map<varchar(20), varchar(20)>, orders array<struct<shipdate:timestamp, price:double precision>> )

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

CREATE EXTERNAL TABLE spectrum.customers ( 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>> )

以下查询返回具有手机号码的客户名称,并返回每个名称对应的号码。映射查询被视为等同于查询 array 类型的嵌套 struct。仅当您按前面的说明创建了外部表时,以下查询才返回数据。

SELECT c.name.given, c.name.family, p.value FROM spectrum.customers c, c.phones p WHERE p.key = 'mobile'

注意

keymap 是 Ion 和 JSON 文件类型的 string