AWS Athena (Presto) OFFSET support

Question

I would like to know if there is support for OFFSET in AWS Athena. For mysql the following query is running but in athena it is giving me error. Any example would be helpful.

select * from employee where empSal >3000 LIMIT 300 OFFSET 20

OFFSET is now supported in Athena. Please check https://docs.aws.amazon.com/athena/latest/ug/release-note-2021-10-04.html — 027, Dec 30 '21 at 08:10

Piotr Findeisen · Accepted Answer · 2020-02-17T17:59:49.513

Athena is basically managed Presto. Since Presto 311 you can use OFFSET m LIMIT n syntax or ANSI SQL equivalent: OFFSET m ROWS FETCH NEXT n ROWS ONLY.

You can read more in Beyond LIMIT, Presto meets OFFSET and TIES.

For older versions (and this includes AWS Athena as of this writing), you can use row_number() window function to implement OFFSET + LIMIT.

For example, instead of

SELECT * FROM elb_logs
OFFSET 5 LIMIT 5 -- this doesn't work, obviously

You can execute

SELECT * FROM (
    SELECT row_number() over() AS rn, * FROM elb_logs)
WHERE rn BETWEEN 5 AND 10;

Note: the execution engine will still need to read offset+limit rows from the underlying table, but this is still much better than sending all these rows back to the client and taking a sublist there.

Warning: see https://stackoverflow.com/a/45114359/65458 for explanation why avoiding OFFSET in queries is generally a good idea.

This answer is not helpful for those using Athena because, as of writing this, Athena does not use a version of presto that supports the `OFFSET` keyword. — xno, Jun 15 '20 at 18:35
@xno that's why i am providing both: the current Presto syntax and legacy syntax for Athena. — Piotr Findeisen, Jun 16 '20 at 16:09

score 4 · Answer 2 · answered Jul 13 '18 at 06:16

4

OFFSET Is not supported by AWS Athena. You can see all the supported SELECT parameters here: SELECT

answered Jul 13 '18 at 06:16

Davide Stefanutti

61
4

thanks for reply. but then how will I fetch the in between rows using SELECT. My requirement is to fetch first 10K rows based on a condition and then take 5001 to 10K rows. – NinjaDev Jul 13 '18 at 06:59
The data scanned appears to be the same if you use or not the LIMIT parameter so I think you need to cache all the received rows and then use the cached data to take only the rows from 5001 to 10K. – Davide Stefanutti Jul 13 '18 at 12:22
1

OFFSET is now supported. Please check https://docs.aws.amazon.com/athena/latest/ug/release-note-2021-10-04.html – 027 Dec 30 '21 at 08:09

score 3 · Answer 3 · answered May 08 '20 at 17:15

It seems that the current accepted solution does not work properly with the ORDER BY keyword as the row_number() is applied before ordering. I believe an exact solution allowing you to use ORDER BY would be as follows:

SELECT * FROM (
  SELECT row_number() over() AS rn, *
  FROM ( 
    SELECT *
    FROM elb_logs
    ORDER BY id ASC 
  )
)
WHERE rn BETWEEN 5 AND 10;

score 2 · Answer 4 · answered Aug 11 '22 at 07:28

2

OFFSET is supported by Athena engine version 2

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT [ count | ALL ] ]

Link for documentation: https://docs.aws.amazon.com/athena/latest/ug/select.html

answered Aug 11 '22 at 07:28

RajendraW

29
4

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 15 '22 at 14:21

score 0 · Answer 5 · answered Jan 27 '20 at 19:22

You could limit and filter by a natural key of the data.

For example, if you had an id column in your dataset you could do the following:

SELECT id, * FROM elb_logs
WHERE id > __LAST_SEEN_ID__
ORDER BY id
LIMIT 500

So your offset would be defined implicitly, using the filter, based on the last id that you have processed.

AWS Athena (Presto) OFFSET support

5 Answers5

Linked