CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-athena
pkaramol
pkaramol

Reputation: 19312

Athena query timeout for bucket containing too many log entries

I am running a simple Athena query as in

SELECT * FROM "logs"
WHERE parse_datetime(requestdatetime,'dd/MMM/yyyy:HH:mm:ss Z')
BETWEEN parse_datetime('2021-12-01:00:00:00','yyyy-MM-dd:HH:mm:ss')
AND
parse_datetime('2021-12-21:19:00:00','yyyy-MM-dd:HH:mm:ss');

However this times out due to the default DML 30 min timeout.

The entries of the path I am querying are a few millions.

Is there a way to address this in Athena or is there a better suited alternative for this purpose?

Upvotes: 0

Views: 1508

Answers (1)

Parsifal
Parsifal

Reputation: 4486

This is normally solved with partitioning. For data that's organized by date, partition projection is the way to go (versus an explicit partition list that's updated manually or via Glue crawler).

That, of course, assumes that your data is organized by the partition (eg, s3://mybucket/2021/12/21/xxx.csv). If not, then I recommend changing your ingest process as a first step.

You my want to change your ingest process anyway: Athena isn't very good at dealing with a large number of small files. While the tuning guide doesn't give an optimal filesize, I recommend at least a few tens of megabytes. If you're getting a steady stream of small files, use a scheduled Lambda to combine them into a single file. If you're using Firehose to aggregate files, increase the buffer sizes / time limits.

And while you're doing that, consider moving to a columnar format such as Parquet if you're not already using it.

Upvotes: 1

Related Questions