Created 07-16-2018 09:09 AM
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.
Created 07-16-2018 11:59 PM
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:
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.
Hope this helps!
Created 07-17-2018 05:23 AM
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 :
Created 07-17-2018 02:46 PM
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 🙂
Created 07-17-2018 05:59 AM
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."
Created 08-15-2018 03:08 PM
@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.
Created 08-15-2018 04:48 PM
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
Created 08-15-2018 06:15 PM
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 ?
Created 09-26-2018 01:21 PM
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
Created 10-04-2018 09:38 AM
can you check all the functions in hive , before going to use with
"show funcitons" cmd in hive CLI