AWS

Making OFFSET LIMIT queries in AWS Athena

How to use OFFSET in AWS Athena queries

Bartosz Mikulski 29 Nov 2020 – 1 min read

There is no OFFSET support in AWS Athena, but we can use a workaround to get the same behavior.

Let’s assume that I want to execute this query:

SELECT * FROM some_table
ORDER BY column_A
OFFSET 20 LIMIT 10 -- this doesn't work

to get the desired outcome, I need three things:

a window function that assigns a number to every row
ordering method that sorts the table, so I get a deterministic outcome
filtering function that selects only the rows I want

SELECT * FROM (
    SELECT row_number() over(ORDER BY column_A) AS rn, * FROM some_table)
WHERE rn BETWEEN 20 AND 30; -- 30 = 20 (offset) + 10 (limit)

Older post

How to get an alert if an AWS lambda does not get invoked during the last 24 hours

How to get a notification when AWS Lambda stops begin used

Newer post

What is s3:TestEvent, and why does it break my event processing?

S3 sends s3:TestEvent to SQS after setting up the bucket notifications

MLOps AWS

How to deploy a Transformer-based model with custom preprocessing code to Sagemaker Endpoints using BentoML

Deploy a machine learning model with custom inference code to a Sagemaker Endpoint using BentoML
Published on: 01 Oct 2021

AWS MLOps

Multimodel deployment in Sagemaker Endpoints

How to deploy multiple models in a single Sagemaker Endpoint?
Published on: 28 May 2021

AWS

How to deploy a REST API AWS Lambda using Chalice and AWS Code Pipeline

How to create a REST API Endpoint using AWS Lambda, Chalice, and AWS Code Pipeline
Published on: 16 Apr 2021