要仅查询一天的数据而不让 Athena 读取所有天的所有数据,您需要创建一个分区表 https://docs.aws.amazon.com/athena/latest/ug/partitions.html(看第二个例子)。分区表与常规表类似,但它们包含额外的元数据,用于描述特定分区键组合的数据所在的位置。当您运行查询并指定分区键的条件时,Athena 可以确定要读取哪些位置以及要跳过哪些位置。
如何配置表的分区键取决于数据的分区方式。在您的情况下,分区是按时间划分的,并且时间戳具有每小时的粒度。您可以选择多种不同的方法来对表中的分区进行编码,哪种方法最好取决于您要运行的查询类型。您说您想按天查询,这是有道理的,并且在这种情况下效果很好。
有两种设置方法:传统方法和新方法。新方法使用了几天前发布的功能,如果您尝试查找更多示例,您可能找不到很多,因此我也将向您展示传统方法。
使用分区投影
使用以下 SQL 创建表(您必须自己填写列,因为您说您已经成功创建了一个表,已经只使用该表中的列 - 还要修复 S3 位置):
CREATE EXTERNAL TABLE cszlos_firehose_data (
-- fill in your columns here
)
PARTITIONED BY (
`date` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 's3://cszlos-data/is/here/'
TBLPROPERTIES (
"projection.enabled" = "true",
"projection.date.type" = "date",
"projection.date.range" = "2020/06/01,NOW",
"projection.date.format" = "yyyy/MM/dd",
"projection.date.interval" = "1",
"projection.date.interval.unit" = "DAYS",
"storage.location.template" = "s3://cszlos-data/is/here/${date}"
)
这将创建一个分区表date
(请注意,您需要在查询中引用此内容,例如SELECT * FROM cszlos_firehose_data WHERE "date" = …
,因为它是保留字,如果您想避免引用它,请使用其他名称,dt
似乎很流行,另请注意,它在 DDL 中使用反引号进行转义,在 DML 语句中使用双引号进行转义)。当您查询此表并指定条件时date
, e.g. … WHERE "date" = '2020/06/05'
,Athena 将仅读取指定日期的数据。
该表使用分区投影 https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html,这是一个新功能,您可以将属性放入TBLPROPERTIES
告诉 Athena 有关分区键以及如何查找数据的部分 - 在这里,我告诉 Athena 假设 S3 上存在从 2020 年 6 月 1 日到查询运行时间的数据(调整必要的开始日期),这意味着,如果您指定该时间之前或“现在”之后的日期,Athena 将知道没有此类数据,甚至不会尝试读取这些天的任何内容。这storage.location.template
属性告诉 Athena 在哪里可以找到特定日期的数据。如果您的查询指定了日期范围,例如… WHERE "date" > '2020/06/05'
。 Athena 将生成每个日期(由projection.date.interval
属性并格式化为projection.date.format
格式)并读取数据s3://cszlos-data/is/here/2020/06/06
, s3://cszlos-data/is/here/2020/06/07
, etc.
你可以找到完整的文档中的 Kinesis Data Firehose 示例 https://docs.aws.amazon.com/athena/latest/ug/partition-projection-kinesis-firehose-example.html。它展示了如何使用完整的每小时粒度的分区,但您不希望这样做,所以请坚持上面的示例。
传统方式
传统方式与上面类似,但是您必须手动添加分区,以便 Athena 找到它们。首先使用以下 SQL 创建表(再次添加之前实验中的列,并修复 S3 位置):
CREATE EXTERNAL TABLE cszlos_firehose_data (
-- fill in your columns here
)
PARTITIONED BY (
`date` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 's3://cszlos-data/is/here/'
这与上面的 SQL 完全相同,但没有表属性。如果您现在尝试对该表运行查询,您将不会得到任何结果。原因是您需要告诉 Athena 分区表的分区信息,然后它才能知道在哪里查找数据(分区表必须有一个LOCATION
,但这实际上与常规表的含义不同)。
您可以通过多种不同的方式添加分区,但最直接的交互式使用是使用ALTER TABLE ADD PARTITION https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.html。您可以在一条语句中添加多个分区,如下所示:
ALTER TABLE cszlos_firehose_data ADD
PARTITION (`date` = '2020-06-06') LOCATION 's3://cszlos-data/is/here/2020/06/06'
PARTITION (`date` = '2020-06-07') LOCATION 's3://cszlos-data/is/here/2020/06/07'
PARTITION (`date` = '2020-06-08') LOCATION 's3://cszlos-data/is/here/2020/06/08'
PARTITION (`date` = '2020-06-09') LOCATION 's3://cszlos-data/is/here/2020/06/09'
如果您开始阅读有关分区表的更多信息,您可能还会遇到MSCK REPAIR TABLE
语句作为加载分区的一种方式。不幸的是,这个命令真的很慢,而且它只适用于 Hive 风格的分区数据(例如…/year=2020/month=06/day=07/file.json
) – 所以你不能使用它。