LIMIT with OFFSET in Hive & Impala

When the query output zooms over the top of the screen before we see it, we can use LIMIT with OFFSET to display it one page at a time. The OFFSET clause in a SELECT query requires the return set to begin some number of rows behind the logical first item. Because the result set is numbered from zero, OFFSET 0 produces the same effect as not using the OFFSET clause. Always use this clause in conjunction with ORDER BY to specify which item should be first, second, and so on, and LIMIT to limit the result set to items 0-9, 10-19, 100-199, and so on.

The OFFSET capability in Hive may be found in later versions of 2.0. Those using previous versions must rely on Windowing features such as ROW NUMBER.

Example:
SELECT * FROM SampleData ORDER BY RandCol LIMIT 3 OFFSET 0
The first three rows will be generated based on the ORDERED column.

SELECT * FROM SampleData ORDER BY RandCol LIMIT 3 OFFSET 3
The next three rows will be generated based on the ORDERED column.

Offset_in_Hive_Impala

Hope you find this article helpful.

Please do subscribe to receive notifications on latest posts.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s