查询按年、月、日分区的 Athena 表

Question

>我有一个按年、月、日分区的雅典娜表，定义如下

CREATE EXTERNAL TABLE `my_table`(
`price` double) 
PARTITIONED BY ( 
`year` int, 
`month` int, 
`day` int) 
ROW FORMAT SERDE 
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'

我需要在日期之间查询它。例如，在我看来，选项是：

SELECT avg(price) 
FROM my_table 
WHERE year = 2018 AND month = 1

结果：运行时间：4.89 秒，扫描的数据：20.72MB

SELECT avg(price) 
FROM my_table 
WHERE cast(date_parse(concat(cast(year as varchar(4)),'-',
cast(month as varchar(2)),'-',
cast(day as varchar(2))
), '%Y-%m-%d') as date) 
BETWEEN Date '2018-01-01' AND Date '2018-01-31'

结果：运行时间：8.64 秒，扫描的数据：20.72MB

所以，我想 Athena 足够聪明，即使在转换串联分区列时也能使用分区功能，那么为什么它需要大约两倍的时间呢？后台到底是怎么回事？

非常感谢。

Answer 1

Athena 将使用 filterPredicate 在这种情况下，您可以使用 EXPLAIN ANALYSE 语句来检查这一点：

EXPLAIN ANALYZE SELECT count(*) FROM "db"."table" 
where year||month||day >= '20220629';

...
- ScanFilterProject[table = awsdatacatalog:HiveTableHandle{schemaName=db, tableName=table, analyzePartitionValues=Optional.empty}, grouped = false, 
filterPredicate = ("concat"("concat"("year", "month"), "day") >= CAST('20220629' AS varchar))] => [[]]
CPU: 2.57s (99.04%), Output: 12424 rows (0B)
Input avg.: 49.11 rows, Input std.dev.: 54.32%
LAYOUT: db.table
month := month:string:-1:PARTITION_KEY
:: [[06], [07]]
year := year:string:-1:PARTITION_KEY
:: [[2022]]
day := day:string:-1:PARTITION_KEY
:: [[05], [06], [07], [11], [12], [13], [14], [15], [16], [17], [18], [19], [29], [30]]
Input: 12424 rows (5.68kB), Filtered: 0.00%
...

Answer 2

我不会依赖分区列的列串联。相反，最好使用具有分区投影的单个日期类型分区。

下面是如何对名为 creation_date 的列执行此操作的示例

PARTITIONED BY (
`creation_date` string
)
TBLPROPERTIES (
'projection.enabled' = 'true',
'projection.creation_date.type' = 'date',
'projection.creation_date.interval.unit' = 'DAYS',
'projection.creation_date.range' = '2000-01-01,NOW',
'projection.creation_date.format' = 'yyy-MM-dd'
)

这将允许简单、精心策划的查询(例如，日期在 2021-12-01 和 2022-01-20 之间(，并且使用分区投影应该可以防止分区限制 # 的任何问题。

此外，以下是我对 Athena 如何使用串联和强制分区列的行为的其他经验。

当我将分区投影与年/月/分区一起使用时，Athena 不够聪明，无法在投射和连接年和月时使用分区。

Athena 扫描了每年整年的分区文件，与我直接使用 OR 语句提取两个月相比，花费的时间要长得多 (10 倍(。

以下查询涵盖 2020 年12 月至 2021 年 1 月的数据，它(不幸的是(扫描了 2020 年和 2021 年分区中的所有数据：

EXPLAIN ANALYZE SELECT * FROM "telemetry_test"."sdmym-day-batch-parquet-labelled" WHERE sid='19009' AND mid='1000' AND di='e5781489-fa7f-4bc5-8858-0c3cc6ba80c7' 
AND (cast(year as varchar)||cast(month as varchar)<='202101' OR cast(year as varchar)||cast(month as varchar)>='202012');

哪个返回

Query Plan
Fragment 1 
CPU: 12.28s, Input: 4210560 rows (132.80MB), Data Scanned: 63.15MB; per task: std.dev.: 2880.00, Output: 4210560 rows (145.35MB)
Output layout: [ts, value, sid, di, mid, year, month]
- ScanFilter[table = awsdatacatalog:HiveTableHandle{schemaName=telemetry_test, tableName=sdmym-day-batch-parquet-labelled, analyzePartitionValues=Optional.empty}, grouped = false, filterPredicate = (("concat"(CAST("year" AS varchar), CAST("month" AS varchar)) <= CAST('202101' AS varchar)) OR ("concat"(CAST("year" AS varchar), CAST("month" AS varchar)) >= CAST('202012' AS varchar)))] => [[ts, value, sid, di, mid, year, month]]
CPU: 12.27s (100.00%), Output: 4210560 rows (145.35MB)
Input avg.: 5760.00 rows, Input std.dev.: 0.00%
LAYOUT: telemetry_test.sdmym-day-batch-parquet-labelled
month := month:int:-1:PARTITION_KEY
:: [[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]]
year := year:int:-1:PARTITION_KEY
:: [[2020], [2021], [2022]]
di := di:string:-1:PARTITION_KEY
:: [[e5781489-fa7f-4bc5-8858-0c3cc6ba80c7]]
mid := mid:string:-1:PARTITION_KEY
:: [[1000]]
value := value:double:1:REGULAR
sid := sid:string:-1:PARTITION_KEY
:: [[19009]]
ts := ts:string:0:REGULAR
Input: 4210560 rows (132.80MB), Filtered: 0.00%