开始使用
本教程将指导您完成使用 Amazon Athena 来查询数据。您将根据 Amazon Simple Storage Service 中存储的示例数据创建表、查询表,并检查查询的结果。
本教程使用的是实时资源,因此,您需要为您运行的查询付费。您无需为本教程使用的位置中的示例数据付费,但如果您将自己的数据文件上载到 Amazon S3,则会收取费用。
先决条件
-
如果您尚未完成此操作,注册 AWS 账户。
-
使用与您在 Athena 中使用的相同 AWS 区域(例如,美国西部(俄勒冈州))和账户,按照相应的步骤在 Amazon S3 中创建存储桶以保存您的 Athena 查询结果。您需要将此存储桶配置为查询输出位置。
步骤 1:创建数据库
首先,您需要在 Athena 中创建一个数据库。
创建 Athena 数据库
从 https://console.aws.amazon.com/athena/
打开 Athena 控制台。 -
如果这是您首次在目前的 AWS 区域 中访问 Athena 控制台,选择 Explore the query editor(浏览查询编辑器)以打开查询编辑器。否则,Athena 会在查询编辑器中打开。
-
选择 Edit Settings(编辑设置)以在 Amazon S3 中设置查询结果位置。
-
对于 Manage settings(管理设置),执行以下操作之一:
-
在 Location of query result(查询结果位置)文本框中,输入您在 Amazon S3 中为查询结果创建的存储桶路径。在路径前添加前缀
s3://
。 -
选择 Browse S3(浏览 S3),选择您为当前区域创建的 Amazon S3 存储桶,然后选择 Choose(选择)。
-
-
选择保存。
-
选择 Editor(编辑器)以切换到查询编辑器。
-
在导航窗格的右侧,您可以使用 Athena 查询编辑器输入和运行查询和语句。
-
要创建一个名为
mydatabase
的数据库,请输入以下 CREATE DATABASE 语句。CREATE DATABASE mydatabase
-
选择 Run(运行)或者按
Ctrl+ENTER
。 -
在左侧的 Database(数据库)列表中,选择
mydatabase
使其成为当前数据库。
步骤 2:创建表
现在,您有了数据库,可以为其创建一个 Athena 表。您创建的表将基于位置 s3://athena-examples-
中的示例 Amazon CloudFront 日志数据,其中 myregion
/cloudfront/plaintext/myregion
是您当前的 AWS 区域。
示例日志数据采用制表符分隔值(TSV)格式,这意味着将制表符用作分隔符来分隔字段。数据类似于以下示例。为了便于阅读,摘录中的制表符已转换为空格,并缩短了最后一个字段。
2014-07-05 20:00:09 DFW3 4260 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-1.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:09 DFW3 4252 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-2.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:10 AMS1 4261 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-3.jpeg 200 - Mozilla/5.0[...]
要让 Athena 能够读取此数据,可以创建如下所示的简单 CREATE
EXTERNAL TABLE
语句。创建表的语句定义映射到数据的列,指定如何分隔数据,并指定包含示例数据的 Amazon S3 位置。请注意,由于 Athena 要扫描文件夹中的所有文件,LOCATION
子句会指定 Amazon S3 文件夹位置,而不是特定文件。
暂勿使用此示例,因为其有一个重要限制,稍后将对此进行解释。
CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
`Date` DATE,
Time STRING,
Location STRING,
Bytes INT,
RequestIP STRING,
Method STRING,
Host STRING,
Uri STRING,
Status INT,
Referrer STRING,
ClientInfo STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
LOCATION 's3://athena-examples-my-region
/cloudfront/plaintext/';
此示例创建了一个名为 cloudfront_logs
的表,并为每个字段指定名称和数据类型。这些字段将成为表中的列。由于 date
是一个预留关键字,因此使用反引号(`)字符进行转义。ROW FORMAT DELIMITED
意味着 Athena 将使用一个名为 LazySimpleSerDe 的默认库来完成解析数据的实际工作。该示例还指定字段以制表符分隔(FIELDS TERMINATED BY '\t'
),并且文件中的每条记录以换行符(LINES TERMINATED BY '\n
)结束。最后,LOCATION
子句指定要读取的实际数据所在的 Amazon S3 中的路径。
如果您有自己的选项卡或逗号分隔数据,只要字段不包含嵌套信息,就可以使用如上述示例所示的 CREATE
TABLE
语句。不过,如果有类似 ClientInfo
这样的列,其中包含使用不同分隔符的嵌套信息,则需要采用不同的方法。
从 ClientInfo 字段中提取数据
查看示例数据,以下为最终字段 ClientInfo
的完整示例:
Mozilla/5.0%20(Android;%20U;%20Windows%20NT%205.1;%20en-US;%20rv:1.9.0.9)%20Gecko/2009040821%20IE/3.0.9
正如您所看到的,此字段是多值的。由于上述所示的示例 CREATE
TABLE
语句将制表符指定为字段分隔符,所以无法将 ClientInfo
字段中的单独组件分解为单独的列。因此,需要一个新的 CREATE TABLE
语句。
要根据 ClientInfo
字段内的值创建列,可以使用包含正则表达式组的正则表达式CREATE TABLE
语句中使用正则表达式,请使用类似下面的语法。此语法指示 Athena 使用 Regex SerDe 库和您指定的正则表达式。
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ("input.regex" = "
regular_expression
")
正则表达式可用于根据复杂的 CSV 或 TSV 数据创建表,但可能难以编写和维护。幸运的是,还有其他库可以用于 JSON、Parquet 和 ORC 等格式。有关更多信息,请参阅 支持的 SerDes 和数据格式。
现在您已准备就绪,可在 Athena 查询编辑器中创建表。已为您准备好 CREATE
TABLE
语句和正则表达式。
在 Athena 中创建表
-
在导航窗格中,对于 Database(数据库),请确保选择了
mydatabase
。 -
要在查询编辑器中扩大空间,您可以选择箭头图标来折叠导航窗格。
-
要创建新查询的选项卡,请在查询编辑器中选择加号 (+)。您最多可以同时打开十个查询选项卡。
-
要关闭一个或多个查询选项卡,请选择加号旁边的箭头。要一次关闭所有选项卡,请选择箭头,然后选择 Close all tabs(关闭所有选项卡)。
-
在查询窗格中,输入以下
CREATE EXTERNAL TABLE
语句。正则表达式将操作系统、浏览器和浏览器版本信息从日志数据中的ClientInfo
字段划分出来。注意
以下示例中使用的正则表达式旨在处理
athena-examples
Amazon S3 位置中公开可用的示例 CloudFront 日志数据,仅供说明。有关同时查询标准和实时 CloudFront 日志文件的最新正则表达式,请参阅 查询 Amazon CloudFront 日志。CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs ( `Date` DATE, Time STRING, Location STRING, Bytes INT, RequestIP STRING, Method STRING, Host STRING, Uri STRING, Status INT, Referrer STRING, os STRING, Browser STRING, BrowserVersion STRING ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$" ) LOCATION 's3://athena-examples-
myregion
/cloudfront/plaintext/'; -
在
LOCATION
语句中,将myregion
替换为您当前使用的 AWS 区域(例如,us-west-1
)。 -
选择运行。
表
cloudfront_logs
已创建,并显示在mydatabase
数据库的 Tables(表)列表中。
步骤 3:查询数据
现在,您已根据 Amazon S3 中的数据在 Athena 中创建了 cloudfront_logs
表,您可以针对该表运行 SQL 查询,并在 Athena 中查看结果。有关在 Athena 中使用 SQL 的更多信息,请参阅 Athena 的 SQL 参考。
运行查询
-
选择加号(+)以打开新的查询选项卡,然后在查询窗格中输入以下 SQL 语句。
SELECT os, COUNT(*) count FROM cloudfront_logs WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05' GROUP BY os
-
选择运行。
结果与以下内容类似:
-
要将查询结果保存到
.csv
文件,选择 Download results(下载结果)。 -
要查看或运行之前的查询,选择 Recent queries(最近的查询)选项卡。
-
要从 Recent queries(最近的查询)选项卡下载以前查询的结果,请选择查询,然后选择 Download results(下载结果)。查询保留 45 天。
-
要将一个或多个最近的 SQL 查询字符串下载到 CSV 文件,请选择 Download CSV(下载 CSV)。
有关更多信息,请参阅 使用查询结果、最近查询和输出文件。
保存查询
您可以使用某个名称来保存您在查询编辑器中创建或编辑的查询。Athena 会将这些查询存储在 Saved queries(已保存的查询)选项卡中。您可以使用 Saved queries(已保存的查询)选项卡来回调、运行、重命名或删除已保存的查询。有关更多信息,请参阅 使用已保存的查询。
键盘快捷键和提前输入建议
Athena 查询编辑器提供了许多键盘快捷键,可用于运行查询、格式化查询、行操作以及查找和替换等操作。有关更多信息和快捷键完整列表,请参阅 AWS 大数据博客中的通过在 Amazon Athena 查询编辑器中使用键盘快捷键提高工作效率
Athena 查询编辑器支持提前输入代码建议,以实现更快的查询创作体验。为了帮助您以更高的准确性和更高的效率编写 SQL 查询,该编辑器提供了以下功能:
-
在您输入时,将实时显示关键字、局部变量、片段和目录项的建议。
-
当您在数据库名称或表名称后面输入一个点时,该编辑器会显示一系列的表或列,方便您从中进行选择。
-
当您将光标悬停在片段建议上时,摘要会显示该片段的语法和用法的简要概述。
-
为了提高代码的可读性,还更新了关键字及其突出显示规则,使其与 Trino 和 Hive 的最新语法保持一致。
该功能已默认启用。要启用或禁用该功能,使用该查询编辑器窗口右下角的代码编辑器首选项(齿轮图标)。
连接到其他数据来源
本教程使用 Amazon S3 中 CSV 格式的数据来源。有关将 Athena 与 AWS Glue 结合使用的信息,请参阅 使用 AWS Glue 连接到 Simple Storage Service(Amazon S3)中的数据来源。您可以使用 ODBC 和 JDBC 驱动程序、外部 Hive 元存储和 Athena 数据来源连接器,将 Athena 连接到各种数据来源。有关更多信息,请参阅 连接到数据源。