SELECT - Amazon Athena
摘要参数获取 Amazon S3 中源数据的文件位置 转义单引号其他资源

SELECT

从零个或多个表中检索数据行。

注意

本主题提供了摘要信息以供参考。本文档不包含有关使用 SELECT 和 SQL 语言的综合信息。有关使用特定于 Athena 的 SQL 的信息，请参阅 Amazon Athena 中 SQL 查询的注意事项和限制 和 在 Amazon Athena 中运行 SQL 查询。有关在 Athena 中创建数据库、创建表和在表上运行 SELECT 查询的示例，请参见 开始使用。

摘要

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT [ count | ALL ] ]
注意

SQL SELECT 语句中的保留字必须用双引号括起来。有关更多信息，请参阅 要在 SQL SELECT 语句中转义的保留关键字。

参数

[ WITH with_query [, ....]]

您可以使用 WITH 来展平嵌套查询或简化子查询。

从 Athena 引擎版本 3 开始支持使用 WITH 子句创建递归查询。最大递归深度为 10。

WITH 子句在查询中位于 SELECT 列表之前，定义一个或多个子查询，以便在 SELECT 查询中使用。

每个子查询均定义一个临时表，与可在 FROM 子句中引用的视图定义类似。只有在查询运行时才使用这些表。

with_query 语法为：

subquery_table_name [ ( column_name [, ...] ) ] AS (subquery)

其中：

  • subquery_table_name 是临时表的唯一名称，该临时表用于定义 WITH 子句子查询的结果。每个 subquery 都必须具有一个可在 FROM 子句中引用的表名称。

  • column_name [, ...] 是可选的输出列名称列表。列名称数目必须等于或小于 subquery 定义的列数。

  • subquery 是任意查询语句。

[ ALL | DISTINCT ] select_expression

select_expression 确定要选择的行。select_expression 可以使用以下格式之一：

expression [ [ AS ] column_alias ] [, ...]
row_expression.* [ AS ( column_alias [, ...] ) ]
relation.*
*

  • expression [ [ AS ] column_alias ] 语法指定输出列。可选 [AS] column_alias 语法指定要用于输出中的列的自定义标题名称。

  • 对于 row_expression.* [ AS ( column_alias [, ...] ) ]，row_expression 是数据类型为 ROW 的任意表达式。行的字段定义要包含在结果中的输出列。

  • 对于 relation.*，relation 的列包含在结果中。此语法不允许使用列别名。

  • 星号 * 指定所有列都包含在结果集中。

  • 在结果集中，列的顺序与 select 表达式的规范顺序相同。如果 select 表达式返回多列，则列顺序遵循源关系或行类型表达式中使用的顺序。

  • 指定列别名后，别名将覆盖先前存在的列或行字段名称。如果 select 表达式没有列名，则输出中将显示索引为零的匿名列名（_col0、_col1、_col2, ...）。

  • 默认值为 ALL。使用 ALL 会被视为与省略它相同；将会选定所有列的所有行，并保留重复项。

  • 当列包含重复值时，请使用 DISTINCT 仅返回不同的值。

FROM from_item [, ...]

指示要查询的输入，其中 from_item 可以是视图、联接构造或如下所述的子查询。

from_item 可以是：

  • table_name [ [ AS ] alias [ (column_alias [, ...]) ] ]

    其中，table_name 是要从中选择行的目标表的名称，alias 是要提供 SELECT 语句输出的名称，column_alias 定义指定 alias 的列。

- 或者 -

  • join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

    其中，join_type 为以下值之一：

    • [ INNER ] JOIN

    • LEFT [ OUTER ] JOIN

    • RIGHT [ OUTER ] JOIN

    • FULL [ OUTER ] JOIN

    • CROSS JOIN

    • ON join_condition | USING (join_column [, ...])，其中，如果使用 join_condition，您可以为多个表中的联接键指定列名称；如果使用 join_column，则要求 join_column 在两个表中都存在。

[ WHERE condition ]

根据您指定的 condition 筛选结果，其中 condition 通常具有以下语法。

column_name operator value [[[AND | OR] column_name operator value] ...]

运算符可以是比较器 =、>、<、>=、<=、<>、!= 之一。

以下子查询表达式也可以用于 WHERE 子句。

  • [NOT] BETWEEN integer_A AND integer_B – 指定两个整数之间的范围，如以下示例所示。如果列数据类型为 varchar，则必须先将列转换为整数。

    SELECT DISTINCT processid FROM "webdata"."impressions"
WHERE cast(processid as int) BETWEEN 1500 and 1800
ORDER BY processid

  • [NOT] LIKE value – 搜索指定的模式。使用百分号 (%) 作为通配符，如以下示例所示。

    SELECT * FROM "webdata"."impressions"
WHERE referrer LIKE '%.org'

  • [NOT] IN (value[, value[, ...]) – 指定列的可能值列表，如以下示例所示。

    SELECT * FROM "webdata"."impressions"
WHERE referrer IN ('example.com','example.net','example.org')
[ GROUP BY [ ALL | DISTINCT ] grouping_expressions [, ...]]

将 SELECT 语句的输出分成多个具有匹配值的行。

ALL 和 DISTINCT 确定重复的分组集是否各自产生不同的输出行。如果省略，则会采用 ALL。

grouping_expressions 允许您执行复杂的分组操作。您可以使用复杂分组操作在单个查询中执行需要聚合多个列集的分析。

grouping_expressions 元素可以是对输入列执行的任何函数，如 SUM、AVG 或者 COUNT。

GROUP BY 表达式可以按照不在 SELECT 语句的输出中显示的输入列名称对输出进行分组。

所有输出表达式都必须是存在于 GROUP BY 子句中的聚合函数或列。

您可以使用单个查询来执行需要聚合多个列集的分析。

Athena 支持使用 GROUPING SETS、CUBE 和 ROLLUP 的复杂聚合。GROUP BY GROUPING SETS 将指定要分组的多个列表。GROUP BY CUBE 为给定的列集生成所有可能的分组集。GROUP BY ROLLUP 为给定的列集生成所有可能的小计。复杂的分组操作不支持对由输入列组成的表达式进行分组。只允许使用列名。

通常，您可以使用 UNION ALL 实现和这些 GROUP BY 操作相同的结果，但使用 GROUP BY 的查询具有一次读取数据的优点，而 UNION ALL 三次读取底层数据，因此可能会在数据源发生变化时造成不一致的结果。

[ HAVING condition ]

与聚合函数和 GROUP BY 子句一起使用。控制哪些组处于选中状态，从而消除不满足 condition 的组。此筛选在计算组和聚合之后发生。

[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] union_query] ]

UNION、INTERSECT 和 EXCEPT 将多个 SELECT 语句的结果合并到单个查询中。ALL 或 DISTINCT 控制包含在最终结果集中的行的唯一性。

UNION 将第一个查询生成的行与第二个查询生成的行组合在一起。为了消除重复，UNION 会构建一个散列表，这会消耗内存。为了更好的性能，如果您的查询不要求消除重复项，请考虑使用 UNION ALL。将会按从左到右的顺序处理多个 UNION 子句，除非您使用圆括号显式定义处理顺序。

INTERSECT 仅返回第一个和第二个查询的结果中存在的行。

EXCEPT 返回第一个查询结果中的行，不包括第二个查询找到的行。

ALL 会导致包含所有行，即使这些行是相同的。

DISTINCT 只导致唯一行包含在组合结果集中。

[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...]]

按一个或多个输出 expression 对结果集进行排序。

当子句包含多个表达式时，将根据第一个 expression 对结果集进行排序。然后，第二个 expression 应用于具有第一个表达式中的匹配值的行，以此类推。

每个 expression 可以指定 SELECT 中的输出列或按位置指定输出列的序号（从 1 开始）。

在任何 GROUP BY 或 HAVING 子句之后，作为最后一个步骤，会计算 ORDER BY。ASC 和 DESC 确定是按升序还是按降序对结果进行排序。默认排序顺序为升序（ASC）。默认 null 排序是 NULLS LAST，无论是按升序还是按降序排序。

[ OFFSET count [ ROW | ROWS ] ]

使用 OFFSET 子句丢弃结果集中的一些前导行。如果 ORDER BY 子句存在，则 OFFSET 子句将在排序的结果集上进行评估，并且在丢弃跳过的行后该集保持排序状态。如果查询没有 ORDER BY 子句，则将任意丢弃行。如果 OFFSET 指定的计数等于或超过结果集的大小，则最终结果为空。

LIMIT [ count | ALL ]

将结果集中的行数限制为 count。LIMIT ALL 与省略 LIMIT 子句相同。如果查询中没有 ORDER BY 子句，则结果是任意的。

TABLESAMPLE [ BERNOULLI | SYSTEM ] (percentage)

可选运算符，用于根据采样方法从表中选择行。

BERNOULLI 选择每个位于表样本中的行，概率为 percentage。将会扫描表的所有物理块，并根据样本 percentage 和在运行时计算的随机值之间的比较来跳过某些行。

借助 SYSTEM，表被划分成数据的逻辑段，而且表按此粒度采样。

将会选择特定段中的所有行，或者根据样本 percentage 和在运行时计算的随机值之间的比较来跳过该段。SYSTEM 采样取决于连接器。此方法不保证独立采样概率。

[ UNNEST (array_or_map) [WITH ORDINALITY] ]

展开数组或映射到关系。数组扩展到单个列中。映射将会扩展为两列（key、value）。

您可以将 UNNEST 与多个参数一起使用，这些参数将扩展到多个列，其中的行数与最高基数参数相同。

其他列将使用空值填补。

WITH ORDINALITY 子句将序数列添加到末尾。

UNNEST 通常和 JOIN 一起使用，可以在 JOIN 的左侧引用关系中的列。

获取 Amazon S3 中源数据的文件位置

要查看表行中数据的 Amazon S3 文件位置，可以在 SELECT 查询中使用 "$path"，如以下示例所示：

SELECT "$path" FROM "my_database"."my_table" WHERE year=2019;

这将返回以下结果：

s3://amzn-s3-demo-bucket/datasets_mytable/year=2019/data_file1.json

要返回表中数据的 S3 文件名路径的排序唯一列表，可以使用 SELECT DISTINCT 和 ORDER BY，如以下示例所示。

SELECT DISTINCT "$path" AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

要仅返回没有路径的文件名，您可以将 "$path" 作为一个参数传递给 regexp_extract 函数，如以下示例所示。

SELECT DISTINCT regexp_extract("$path", '[^/]+$') AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

要从特定文件返回数据，请在 WHERE 子句中指定文件，如以下示例所示。

SELECT *,"$path" FROM my_database.my_table WHERE "$path" = 's3://amzn-s3-demo-bucket/my_table/my_partition/file-01.csv'

有关详细信息和示例，请参阅知识中心文章：如何在 Amazon S3 源文件中查找 Athena 表中的某行？。

注意

在 Athena 中，视图不支持 Hive 或 Iceberg 隐藏的元数据列 $bucket、$file_modified_time、$file_size 和 $partition。

转义单引号

要转义单引号，请在其前面加上另一个单引号，如以下示例所示。不要将这种情况与双引号混淆。

Select 'O''Reilly'
结果

O'Reilly

其他资源

有关在 Athena 中使用 SELECT 语句的更多信息，请参阅以下资源。

有关此内容的信息 请参阅此
在 Athena 中运行查询 在 Amazon Athena 中运行 SQL 查询
使用 SELECT 创建表 从查询结果创建表（CTAS）
从 SELECT 查询中将数据插入到另一个表 INSERT INTO
在 SELECT 语句中使用内置函数 Amazon Athena 中的函数
在 SELECT 语句中使用用户定义的函数 使用用户定义函数进行查询
查询 Data Catalog 元数据 查询 AWS Glue Data Catalog