Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Offset and Limit is not working in hive

avatar
Contributor

I want to use offset and limit function in hive. Its working on SQL. Is there any other option/ cmd for offset and limit on hive.

9 REPLIES 9

avatar

Hello @Anjali Shevadkar!
Not sure If I get it, but AFAIK you can't use OFFSET clause in Hive.

If you're using a version >= than hive 2.0, you're able to use this feature:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-LIMITCla...

Otherwise using a window function like (row number) should work to do this.

Ps: I made a quick search here, and there'an incubating project called HiveMall, they have a function related to rowid.

https://github.com/apache/incubator-hivemall/blob/a31d0aab3f419812cae7da426f6dcdc9a7be72b5/docs/gitb...

Hope this helps!

avatar
Contributor

Hi @Vinicius Higa Murakami I have tried row number() but it gives error. This error is because of HIve version. for particular version row number is not working.

Dag submit failed due to Invalid TaskLaunchCmdOpts defined for Vertex Map 1 :

avatar

Hello @Anjali Shevadkar!
Take a look at the comment made by @Sindhu, seems you're hitting this issue https://community.hortonworks.com/content/supportkb/171945/errordag-submit-failed-due-to-invalid-tas...
Hope this helps 🙂

avatar
@Anjali Shevadkar

It seems like you are hitting "Dag submit failed due to Invalid TaskLaunchCmdOpts defined for Vertex Map 1 : Invalid/conflicting GC options found".

Please check and share the values of the following parameters "tez.am.launch.cmd-opts" and "hive.tez.java.opts" they should not be conflicting. Specially the GC options. Remove +UseParallelGC from either of the properties to address the issue.

This is because "-XX:+UseG1GC and -XX:+UseParallelGC" Should never be used together."

avatar
New Contributor

@Vinicius Higa Murakami I enabled Hive 2.0 and when tried the below query, I am getting an exception

<small>org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:38 missing EOF at ',' near '1'<br></small>

select * from dummy_schema.dummy1 limit 1,2

Can you help on using the limit clause in the case where offset needs to be applied.

avatar

Hello @Ek Im!

Have you tried to add the ORDER BY?

select * from dummy_schema.dummy1 order by <SOME_COLUMN> limit 1,2;

Hope this helps

avatar
New Contributor

Hi @Vinicius Higa Murakami,

Adding "Order by" clause din't help. I am trying to run the query in Hive view 2.0 , after enabling HiveServer2 Interactive, Is this right ?

avatar
New Contributor

Hi @Vinicius Higa Murakami,

The problem is resolved, I was using Ambari Hive 2.0 view to execute the queries. When I used the Hive 2.1 JDBC drivers and jars it was working as expected. Thanks for the help

avatar
Rising Star

can you check all the functions in hive , before going to use with

"show funcitons" cmd in hive CLI