1

I am trying to execute offset query in hive where I am trying to exclude particular records, but always getting the error below:

Query: 

select * from sample order by id limit 1 OFFSET 1;

Error:

FAILED: ParseException line 1:41 missing EOF at 'OFFSET' near '1'

I tried dropping table and creating it again as suggested in of the SO post, but still getting the same error. Also I have already set TEZ engine in hive for faster data processing but the above query launches map reduce jobs.
Why is it so? When I execute another query it gives me direct results via TEZ engine.
Can anybody explain me this weird behaviour and as well as in solving my problem?

Environment: 

1) Cloudera 5.12  
2) Hive 1.1.0-cdh5.12.0
OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
whatsinthename
  • 1,828
  • 20
  • 59

2 Answers2

0

Not clear why you tagged MySQL, but OFFSET does not exist as part of HiveQL SELECT syntax.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
  • so how can i achieve this functionality in hive ?? i tried using limit(1,2) but still getting the same error – whatsinthename Dec 17 '17 at 13:27
  • Link which is mentioned by you doesn't provide any information related to supporting functions . Instead they have executed one query with limit which i have already executed but getting the same error. Can you give me more clarity on this ? Thanks – whatsinthename Dec 17 '17 at 13:44
  • It's the syntax I linked to. Your error in the question says `missing EOF at 'OFFSET'` because `OFFSET` exists in Mysql and Postgres, not HiveQL. If you want only the second row `LIMIT 1,1` is correct, but **added in Hive 2.0**, which you're not using. Your other option would be generate a row ID using `ROW_NUMBER` function, then filter `ID > 1` – OneCricketeer Dec 17 '17 at 17:11
0

You can include the OFFSET clause with the LIMIT clause to produce paged result sets, like 11-20. Always use this clause in combination with ORDER BY (so that it is clear which item should be first, second, and so on) and LIMIT (so that the result set covers a bounded range, such as items 0-9, 100-199, and so on).

Pushkar Mishra
  • 1
  • 2