Issue

This Content is from Stack Overflow. Question asked by lazymcfury000

i’m new to HQL and was wondering the reason of the error below:
I was selecting the whole database which had ~9 millions of records so I was trying to get it chunk by chunk. Therefore I tried:

Everything worked fine when I used:

SELECT * FROM tableABC ORDER BY tableABC.ID LIMIT 10; //Select everything from the table with total 10 rows

However, when I tried to get them with:

SELECT * FROM tableABC ORDER BY tableABC.ID LIMIT 0,10; //Select everything from the table from row 0 to total 10 rows

I kept getting the error of “FAILED: ParseException line 1:111 missing EOF at ‘,’ near ‘0’)”. I tried using LIMIT with OFFSET, and it still showed the same error about EOF.

May I know what would be the problem?

Solution

Limit with two arguments should work in hive 2.0.0 or higher version. Could you please check your hive version using select version() and find the root cause for yourself?

If your hive is lower than 2, you can use below SQL to get the data you want. I am using row_number() to generate sequential numbers and then putting a filter on it. This may be little slower than limit x,y but shouldn’t be too much different.

select id,col_1, col_2...
from (
    select id, col_1, col_2, ... , row_number() OVER (ORDER by id) as rownum    from tableABC
    ) rs
where rownum between 0 and 50

This Question was asked in StackOverflow by lazymcfury000 and Answered by Koushik Roy It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.