查询具有复杂类型和嵌套结构的数组 - Amazon Athena
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

如果我们为英文版本指南提供翻译,那么如果存在任何冲突,将以英文版本指南为准。在提供翻译时使用机器翻译。

查询具有复杂类型和嵌套结构的数组

您的源数据通常包含具有复杂数据类型和嵌套结构的数组。本部分中的示例显示如何使用 Athena 查询更改元素的数据类型,在数组内找到元素,以及查找关键字。

创建 ROW

注意

本部分中的示例使用 ROW 作为一种方法来创建样本数据以供使用。当您在 Athena 中查询表时,无需创建 ROW 数据类型,因为它们已从数据源创建。当您使用 CREATE_TABLE 时,Athena 将针对数据集中的每一行,在其中定义 STRUCT,向其填充数据,并为您创建 ROW 数据类型。底层 ROW 数据类型包含支持的任何 SQL 数据类型的命名字段。

WITH dataset AS ( SELECT ROW('Bob', 38) AS users ) SELECT * FROM dataset

此查询返回:

+-------------------------+ | users | +-------------------------+ | {field0=Bob, field1=38} | +-------------------------+

使用 CAST 更改数组中的字段名称

要更改包含 ROW 值的数组中的字段名称,您可以对 ROW 声明执行 CAST 操作:

WITH dataset AS ( SELECT CAST( ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER) ) AS users ) SELECT * FROM dataset

此查询返回:

+--------------------+ | users | +--------------------+ | {NAME=Bob, AGE=38} | +--------------------+
注意

在上述示例中,您可以将 name 声明为 VARCHAR,因为这是它在 Presto 中的类型。如果您在 CREATE TABLE 语句中声明此 STRUCT,请使用 String 类型,因为 Hive 将此数据类型定义为 String

使用 . 表示法筛选数组

在以下示例中,使用点 . 表示法从 AWS CloudTrail 日志表的 userIdentity 列中选择 accountId 字段。有关更多信息,请参阅查询 AWS CloudTrail 日志

SELECT CAST(useridentity.accountid AS bigint) as newid FROM cloudtrail_logs LIMIT 2;

此查询返回:

+--------------+ | newid | +--------------+ | 112233445566 | +--------------+ | 998877665544 | +--------------+

要查询一个值数组,请发出以下查询:

WITH dataset AS ( SELECT ARRAY[ CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER)) ] AS users ) SELECT * FROM dataset

它将返回此结果:

+-----------------------------------------------------------------+ | users | +-----------------------------------------------------------------+ | [{NAME=Bob, AGE=38}, {NAME=Alice, AGE=35}, {NAME=Jane, AGE=27}] | +-----------------------------------------------------------------+

筛选具有嵌套值的数组

大型数组通常包含嵌套结构,您需要能够对其中的值进行筛选或搜索。

要为包含嵌套 BOOLEAN 值的值数组定义数据集,请发出以下查询:

WITH dataset AS ( SELECT CAST( ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ) AS sites ) SELECT * FROM dataset

它将返回此结果:

+----------------------------------------------------------+ | sites | +----------------------------------------------------------+ | {HOSTNAME=aws.amazon.com, FLAGGEDACTIVITY={ISNEW=true}} | +----------------------------------------------------------+

接下来,要筛选和访问该元素的 BOOLEAN 值,请继续使用点 . 表示法。

WITH dataset AS ( SELECT CAST( ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ) AS sites ) SELECT sites.hostname, sites.flaggedactivity.isnew FROM dataset

此查询选择嵌套字段并返回此结果:

+------------------------+ | hostname | isnew | +------------------------+ | aws.amazon.com | true | +------------------------+

使用 UNNEST 筛选数组

要按照一个子元素筛选包含嵌套结构的数组,请发出具有 UNNEST 运算符的查询。有关 UNNEST 的更多信息,请参阅展平嵌套数组

例如,此查询在数据集中查找站点的主机名。

WITH dataset AS ( SELECT ARRAY[ CAST( ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ), CAST( ROW('news.cnn.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ), CAST( ROW('netflix.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ) ] as items ) SELECT sites.hostname, sites.flaggedActivity.isNew FROM dataset, UNNEST(items) t(sites) WHERE sites.flaggedActivity.isNew = true

它返回:

+------------------------+ | hostname | isnew | +------------------------+ | aws.amazon.com | true | +------------------------+

使用 regexp_like 在数组中查找关键字

以下示例说明如何使用 regexp_like 函数搜索数据集以查找数组内元素中的关键字。它以一个要计算的正则表达式模式(或一个由竖线 (|) 分隔的搜索词列表)作为输入,计算此模式,并确定指定的字符串是否包含此模式。

此正则表达式模式需要包含在此字符串内,但并不一定要与此字符串匹配。要匹配整个字符串,请在模式开头使用 ^ 并在末尾使用 $ 将模式括起来,例如 '^pattern$'

请考虑包含其主机名的站点和 flaggedActivity 元素的数组。此元素包含一个 ARRAY,其中包含多个 MAP 元素,每个元素列出不同的流行关键字及其受欢迎程度计数。假设您要在此数组中的 MAP 内查找特定关键字。

要搜索此数据集以查找具有特定关键字的网站,我们使用 regexp_like 而不是类似的 SQL LIKE 运算符,因为使用 regexp_like 搜索大量关键字的效率更高。

例 示例 1:使用 regexp_like

本示例中的查询使用 regexp_like 函数来搜索可在数组内的值中找到的词 'politics|bigdata'

WITH dataset AS ( SELECT ARRAY[ CAST( ROW('aws.amazon.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']), MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']), MAP(ARRAY['term', 'count'], ARRAY['iot', '74']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('news.cnn.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['politics', '241']), MAP(ARRAY['term', 'count'], ARRAY['technology', '211']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']), MAP(ARRAY['term', 'count'], ARRAY['iot', '170']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('netflix.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']), MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']), MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']), MAP(ARRAY['term', 'count'], ARRAY['iot', '4']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ) ] AS items ), sites AS ( SELECT sites.hostname, sites.flaggedactivity FROM dataset, UNNEST(items) t(sites) ) SELECT hostname FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags) WHERE regexp_like(flags['term'], 'politics|bigdata') GROUP BY (hostname)

此查询返回两个站点:

+----------------+ | hostname | +----------------+ | aws.amazon.com | +----------------+ | news.cnn.com | +----------------+

例 示例 2:使用 regexp_like

以下示例中的查询将搜索词与 regexp_like 函数匹配的站点的总受欢迎度分数相加,然后将它们从最高到最低排序。

WITH dataset AS ( SELECT ARRAY[ CAST( ROW('aws.amazon.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']), MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']), MAP(ARRAY['term', 'count'], ARRAY['iot', '74']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('news.cnn.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['politics', '241']), MAP(ARRAY['term', 'count'], ARRAY['technology', '211']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']), MAP(ARRAY['term', 'count'], ARRAY['iot', '170']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('netflix.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']), MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']), MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']), MAP(ARRAY['term', 'count'], ARRAY['iot', '4']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ) ] AS items ), sites AS ( SELECT sites.hostname, sites.flaggedactivity FROM dataset, UNNEST(items) t(sites) ) SELECT hostname, array_agg(flags['term']) AS terms, SUM(CAST(flags['count'] AS INTEGER)) AS total FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags) WHERE regexp_like(flags['term'], 'politics|bigdata') GROUP BY (hostname) ORDER BY total DESC

此查询返回两个站点:

+------------------------------------+ | hostname | terms | total | +----------------+-------------------+ | news.cnn.com | politics | 241 | +----------------+-------------------+ | aws.amazon.com | big data | 10 | +----------------+-------------------+