查询具有复杂类型和嵌套结构的数组 - Amazon Athena
创建ROW使用 CAST 更改数组中的字段名称使用 . 表示法筛选数组筛选具有嵌套值的数组使用 UNNEST 筛选数组使用 regexp_like 在数组中查找关键字

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

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