Offset and Limit is not working in hive

[#1]

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.

[#2]

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!

[#3]

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 :

[#4]

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 🙂

[#5]

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

[#6]

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

[#7]

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

[#8]

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 ?

[#9]

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

[#10]

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

"show funcitons" cmd in hive CLI