Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Offset and Limit is not working in hive

Highlighted

Offset and Limit is not working in hive

New 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

Re: Offset and Limit is not working in hive

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!

Re: Offset and Limit is not working in hive

New 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 :

Re: Offset and Limit is not working in hive

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 :)

Re: Offset and Limit is not working in hive

@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."

Re: Offset and Limit is not working in hive

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.

Re: Offset and Limit is not working in hive

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

Re: Offset and Limit is not working in hive

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 ?

Re: Offset and Limit is not working in hive

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

Re: Offset and Limit is not working in hive

New Contributor

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

"show funcitons" cmd in hive CLI