教程:使用 Amazon 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
注意
如果存在名为 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<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';
注意
map
的 key
是 Ion 和 JSON 文件类型的 string
。