Select definite rows in Impala

Question

Let's say, that I need to select rows from 3 to 10. For MySQL I would use limit For ORACLE I would use rownum

Does Impala allow to select definite rows via such simple method? Thanks in advance.

You should read the Impala documentation. It's free. http://www.cloudera.com/documentation/enterprise/latest/topics/impala_limit.html — Samson Scharfrichter, Feb 06 '17 at 12:07
I didn't find the solution first time. However now it is clear for me. Thank you =) — Anastasia Vitchenko, Feb 07 '17 at 18:02
If somebody has this question - the solution is combination of LIMIT and OFFSET. — Anastasia Vitchenko, Feb 07 '17 at 18:03
Thanks @AnastasiaVitchenko, you should add an official answer to this question so we can upvote it. — ZeMoon, Jul 07 '21 at 06:47

score 0 · Answer 1 · answered Jun 10 '22 at 11:17

Use the OFFSET clause.

For example:

SELECT ... 
FROM ... 
ORDER BY ...
LIMIT 10 OFFSET 5

This would select 10 rows beginning with the 6th row. The offset is 0-based so OFFSET 0 would start from the first row. You need to use ORDER BY when using the OFFSET clause.

Note however if you are using this for pagination, it is not recommended to do this unless required for backwards compatibility. From the documentation:

Because Impala queries typically involve substantial amounts of I/O, use this technique only for compatibility in cases where you cannot rewrite the application logic. For best performance and scalability, wherever practical, query as many items as you expect to need, cache them on the application side, and display small groups of results to users using application logic.

Select definite rows in Impala

1 Answers1