在 Athena 中对数据进行分区 - Amazon Athena
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

在 Athena 中对数据进行分区

通过分区您的数据,您可以限制每个查询扫描的数据量,从而提高性能并降低成本。您可按任何键对数据进行分区。一种常见的做法是根据时间对数据进行分区,通常会导致多级别分区方案。例如,每个小时都有数据传入的客户可能决定按年、月、日期和小时进行分区。另一位客户的数据来自许多不同的来源,但每天只加载一次,则可以按数据源标识符和日期进行分区。

Athena 可以使用 Apache Hive 风格的分区,其数据路径包含通过等号连接的键值对(例如,country=us/...year=2021/month=01/day=26/...)。因此,路径包含分区键的名称和每个路径所表示的值。要将新的 Hive 分区加载到分区表中,您可以使用仅适用于 Hive 风格分区的 MSCK REPAIR TABLE 命令。

Athena 还可以使用非 Hive 风格的分区方案。例如,CloudTrail 日志和 Kinesis Data Firehose 传输流在日期部分使用单独的路径组件,例如 data/2021/01/26/us/6fc7845e.json。对于此类非 Hive 样式的分区,您可以使用 ALTER TABLE ADD PARTITION 来手动添加分区。

注意事项和限制

在使用分区时,请记住以下几点:

  • 如果您查询分区表并在 WHERE 子句中指定分区,Athena 仅从该分区扫描数据。有关更多信息,请参阅表位置和分区

  • 如果分区名称在子查询的 WHERE 子句中,则 Athena 当前不会筛选该分区,而是扫描分区表中的所有数据。

  • 如果您针对包含大量对象且数据未分区的 Amazon S3 存储桶发出查询,则此类查询可能影响 Amazon S3 中的 GET 请求速率限制并导致 Amazon S3 异常。为防止错误发生,请将数据分区。另外,考虑调整 Amazon S3 的请求速率。有关更多信息,请参阅最佳实践设计模式:优化 Simple Storage Service (Amazon S3) 性能

  • 要与 Athena 结合使用的分区位置必须使用 s3 协议(例如,s3://DOC-EXAMPLE-BUCKET/folder/)。在 Athena 中,当对包含的表运行 MSCK REPAIR TABLE 查询时,使用其他协议的位置(例如,s3a://DOC-EXAMPLE-BUCKET/folder/)将导致查询失败。

  • 确保 Amazon S3 路径为小写而不是驼峰式大小写(例如,userid 而不是 userId)。如果 S3 路径为驼峰式大小写,则 MSCK REPAIR TABLE 不会将分区添加到 Amazon Glue Data Catalog。有关更多信息,请参阅MSCK REPAIR TABLE

  • 由于 MSCK REPAIR TABLE 同时扫描文件夹及其子文件夹以查找匹配的分区方案,请确保在单独的文件夹层次结构中保留单独表的数据。例如,假设您在 s3://table-a-data 中拥有表 A 的数据,在 s3://table-a-data/table-b-data 中拥有表 B 的数据. 如果两个表都按字符串进行分区,MSCK REPAIR TABLE 会将表 B 的分区添加到表 A。为了避免这种情况,请使用单独的文件夹结构,如 s3://table-a-datas3://table-b-data。请注意,此行为与 Amazon EMR 和 Apache Hive 一致。

  • 如果将 Amazon Glue Data Catalog 与 Athena 一起使用,请参阅 Amazon Glue 端点和限额,以了解每账户和每表的分区服务限额。

    • 尽管 Athena 支持查询具有 1000 万个分区的 Amazon Glue 表,但 Athena 的单次扫描读取量最多为 100 万个分区。

  • 如果您使用的是 Amazon Glue Data Catalog,请求增加分区限额,请访问 Amazon Glue 的服务限额控制台

使用分区数据创建和加载表

要创建使用分区的表,请在 CREATE TABLE 语句中使用 PARTITIONED BY 子句。PARTITIONED BY 子句定义了对数据进行分区所用的键,如以下示例所示。LOCATION 子句指定了分区数据的根位置。

CREATE EXTERNAL TABLE users ( first string, last string, username string ) PARTITIONED BY (id string) STORED AS parquet LOCATION 's3://DOC-EXAMPLE-BUCKET/folder/'

创建表之后,您在分区中加载数据以进行查询。对于 Hive 样式的分区,您可以运行 MSCK REPAIR TABLE。对于非 Hive 样式的分区,您可以使用 ALTER TABLE ADD PARTITION 来手动添加分区。

准备 Hive 风格和非 Hive 风格的数据用于查询

以下部分介绍了如何准备 Hive 风格和非 Hive 风格的数据以便在 Athena 中查询。

场景 1:以 Hive 格式存储在 Amazon S3 上的数据

在此场景中,分区存储在 Amazon S3 中的单独文件夹内。例如,以下是由 aws s3 ls 命令输出的示例广告展示的部分列表,其中列出了指定前缀下的 S3 对象:

aws s3 ls s3://elasticmapreduce/samples/hive-ads/tables/impressions/ PRE dt=2009-04-12-13-00/ PRE dt=2009-04-12-13-05/ PRE dt=2009-04-12-13-10/ PRE dt=2009-04-12-13-15/ PRE dt=2009-04-12-13-20/ PRE dt=2009-04-12-14-00/ PRE dt=2009-04-12-14-05/ PRE dt=2009-04-12-14-10/ PRE dt=2009-04-12-14-15/ PRE dt=2009-04-12-14-20/ PRE dt=2009-04-12-15-00/ PRE dt=2009-04-12-15-05/

日志存储在这里,列名称 (DT) 设置为等于日期、小时和分钟增量。当您向 DDL 提供父文件夹的位置、架构和分区列的名称时,Athena 可以查询这些子文件夹中的数据。

创建表

要从此类数据中生成一个表,请连同“dt”一起创建一个分区,如以下 Athena DDL 语句所示:

CREATE EXTERNAL TABLE impressions ( requestBeginTime string, adId string, impressionId string, referrer string, userAgent string, userCookie string, ip string, number string, processId string, browserCookie string, requestEndTime string, timers struct<modelLookup:string, requestTime:string>, threadId string, hostname string, sessionId string) PARTITIONED BY (dt string) ROW FORMAT serde 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 's3://elasticmapreduce/samples/hive-ads/tables/impressions/' ;

此表使用 Hive 的本机 JSON 串行器/解串器来读取在 Amazon S3 中存储的 JSON 数据。有关支持的格式的更多信息,请参阅支持的 SerDes 和数据格式

运行 MSCK REPAIR TABLE

运行 CREATE TABLE 查询后,在 Athena 查询编辑器中运行 MSCK REPAIR TABLE 命令来加载分区,如以下示例所示。

MSCK REPAIR TABLE impressions

运行此命令后,即可进行数据查询。

查询数据

使用分区列从展示表中查询数据。示例如下:

SELECT dt,impressionid FROM impressions WHERE dt<'2009-04-12-14-00' and dt>='2009-04-12-13-00' ORDER BY dt DESC LIMIT 100

此查询应显示与以下内容类似的结果:

2009-04-12-13-20 ap3HcVKAWfXtgIPu6WpuUfAfL0DQEc 2009-04-12-13-20 17uchtodoS9kdeQP1x0XThKl5IuRsV 2009-04-12-13-20 JOUf1SCtRwviGw8sVcghqE5h0nkgtp 2009-04-12-13-20 NQ2XP0J0dvVbCXJ0pb4XvqJ5A4QxxH 2009-04-12-13-20 fFAItiBMsgqro9kRdIwbeX60SROaxr 2009-04-12-13-20 V4og4R9W6G3QjHHwF7gI1cSqig5D1G 2009-04-12-13-20 hPEPtBwk45msmwWTxPVVo1kVu4v11b 2009-04-12-13-20 v0SkfxegheD90gp31UCr6FplnKpx6i 2009-04-12-13-20 1iD9odVgOIi4QWkwHMcOhmwTkWDKfj 2009-04-12-13-20 b31tJiIA25CK8eDHQrHnbcknfSndUk

方案 2:数据未以 Hive 格式进行分区

在以下示例中,aws s3 ls 命令显示存储在 Amazon S3 中的 ELB 日志。请注意,数据布局不使用 key=value 对,因此不是 Hive 格式。(aws s3 ls 命令的 --recursive 选项指定了列出的指定目录或前缀下的所有文件或对象。)

aws s3 ls s3://athena-examples-myregion/elb/plaintext/ --recursive 2016-11-23 17:54:46 11789573 elb/plaintext/2015/01/01/part-r-00000-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:46 8776899 elb/plaintext/2015/01/01/part-r-00001-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:46 9309800 elb/plaintext/2015/01/01/part-r-00002-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 9412570 elb/plaintext/2015/01/01/part-r-00003-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 10725938 elb/plaintext/2015/01/01/part-r-00004-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:46 9439710 elb/plaintext/2015/01/01/part-r-00005-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 0 elb/plaintext/2015/01/01_$folder$ 2016-11-23 17:54:47 9012723 elb/plaintext/2015/01/02/part-r-00006-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 7571816 elb/plaintext/2015/01/02/part-r-00007-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:47 9673393 elb/plaintext/2015/01/02/part-r-00008-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 11979218 elb/plaintext/2015/01/02/part-r-00009-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 9546833 elb/plaintext/2015/01/02/part-r-00010-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 10960865 elb/plaintext/2015/01/02/part-r-00011-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 0 elb/plaintext/2015/01/02_$folder$ 2016-11-23 17:54:48 11360522 elb/plaintext/2015/01/03/part-r-00012-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 11211291 elb/plaintext/2015/01/03/part-r-00013-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:48 8633768 elb/plaintext/2015/01/03/part-r-00014-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 11891626 elb/plaintext/2015/01/03/part-r-00015-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 9173813 elb/plaintext/2015/01/03/part-r-00016-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 11899582 elb/plaintext/2015/01/03/part-r-00017-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:49 0 elb/plaintext/2015/01/03_$folder$ 2016-11-23 17:54:50 8612843 elb/plaintext/2015/01/04/part-r-00018-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 10731284 elb/plaintext/2015/01/04/part-r-00019-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 9984735 elb/plaintext/2015/01/04/part-r-00020-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 9290089 elb/plaintext/2015/01/04/part-r-00021-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:50 7896339 elb/plaintext/2015/01/04/part-r-00022-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 8321364 elb/plaintext/2015/01/04/part-r-00023-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 0 elb/plaintext/2015/01/04_$folder$ 2016-11-23 17:54:51 7641062 elb/plaintext/2015/01/05/part-r-00024-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 10253377 elb/plaintext/2015/01/05/part-r-00025-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 8502765 elb/plaintext/2015/01/05/part-r-00026-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 11518464 elb/plaintext/2015/01/05/part-r-00027-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 7945189 elb/plaintext/2015/01/05/part-r-00028-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 7864475 elb/plaintext/2015/01/05/part-r-00029-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 0 elb/plaintext/2015/01/05_$folder$ 2016-11-23 17:54:51 11342140 elb/plaintext/2015/01/06/part-r-00030-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:51 8063755 elb/plaintext/2015/01/06/part-r-00031-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 9387508 elb/plaintext/2015/01/06/part-r-00032-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 9732343 elb/plaintext/2015/01/06/part-r-00033-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 11510326 elb/plaintext/2015/01/06/part-r-00034-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 9148117 elb/plaintext/2015/01/06/part-r-00035-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 0 elb/plaintext/2015/01/06_$folder$ 2016-11-23 17:54:52 8402024 elb/plaintext/2015/01/07/part-r-00036-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 8282860 elb/plaintext/2015/01/07/part-r-00037-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:52 11575283 elb/plaintext/2015/01/07/part-r-00038-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 8149059 elb/plaintext/2015/01/07/part-r-00039-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 10037269 elb/plaintext/2015/01/07/part-r-00040-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 10019678 elb/plaintext/2015/01/07/part-r-00041-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt 2016-11-23 17:54:53 0 elb/plaintext/2015/01/07_$folder$ 2016-11-23 17:54:53 0 elb/plaintext/2015/01_$folder$ 2016-11-23 17:54:53 0 elb/plaintext/2015_$folder$

运行 ALTER TABLE ADD PARTITION

由于数据不是 Hive 格式,因此创建表后,您不能使用 MSCK REPAIR TABLE 命令将分区添加到表中。相反,您可以使用 ALTER TABLE ADD PARTITION 命令手动添加每个分区。例如,要加载 s3://athena-examples-myregion/elb/plaintext/2015/01/01/ 中的数据,您可以运行以下查询。请注意,对于每个 Amazon S3 文件夹不需要单独的分区列,并且分区键值可能与 Amazon S3 键不同。

ALTER TABLE elb_logs_raw_native_part ADD PARTITION (dt='2015-01-01') location 's3://athena-examples-us-west-1/elb/plaintext/2015/01/01/'

如果分区已经存在,您会收到错误 Partition already exists(分区已存在)。要避免此错误,您可以使用 IF NOT EXISTS 子句。有关更多信息,请参阅ALTER TABLE ADD PARTITION。要删除分区,您可以使用 ALTER TABLE DROP PARTITION

分区投影

要避免管理分区,您可以使用分区投影。对于预先知道其结构的高度分区表,分区投影是一个选项。在分区投影中,分区值和位置是根据配置的表属性计算得出的,而不是从元数据存储库中读取出的。由于内存式计算比远程查找更快,因此使用分区投影可以显著减少查询运行时。

有关更多信息,请参阅使用 Amazon Athena 分区投影

其他资源