查询具有复杂类型和嵌套结构的数组 - Amazon Athena
创建ROW使用 CAST 更改数组中的字段名称使用 . 表示法筛选数组筛选具有嵌套值的数组使用 UNNEST 筛选数组使用 regexp_like 在数组中查找关键字
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

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

您的源数据通常包含具有复杂数据类型和嵌套结构的数组。本部分中的示例显示如何使用 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。

使用 . 表示法筛选数组

在以下示例中，使用点 . 表示法从 Amazon CloudTrail 日志表的 userIdentity 列中选择 accountId 字段。有关更多信息，请参阅查询 Amazon 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 | bigdata |  10    |
+----------------+-------------------+