You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Omkar Joshi <Om...@lntinfotech.com> on 2013/06/21 08:20:39 UTC
Difficulties in executing lag function
Hi,
I have a orders table created in Hive.
create table orders
( order_date TIMESTAMP,
product_id INT,
qty INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
I wish to execute a query similar to the following :
select product_id, order_date,
lag (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders
where product_id = 2000;
I'm referring to https://github.com/hbutani/SQLWindowing/wiki
I'm not sure I have got the syntax - do we have to switch between the modes even within the single query?
hduser@cldx-1151-1044:~/hadoop_ecosystem/apache_hive/hive_installation/hive-0.9.0/bin$ hive --service windowingCli
WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.
Hive history file=/tmp/hduser/hive_job_log_hduser_201306211707_783850714.txt
hive> wmode;
windowing mode is off
hive> select * from orders;
OK
2007-09-25 00:00:00 1000 20
2007-09-26 00:00:00 2000 15
2007-09-27 00:00:00 1000 8
2007-09-28 00:00:00 2000 12
2007-09-29 00:00:00 2000 2
2007-09-30 00:00:00 1000 4
Time taken: 37.862 seconds
hive> select product_id, order_date;wmode windowing;lag ('order_date,1') over (ORDER BY order_date) AS prev_order_date; wmode hive; from orders;
BR.recoverFromMismatchedToken
Exception in thread "main" java.lang.IncompatibleClassChangeError: the number of constructors during runtime and compile time for java.lang.Exception do not match. Expected 4 but got 5
at groovy.lang.MetaClassImpl.selectConstructorAndTransformArguments(MetaClassImpl.java:1400)
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.selectConstructorAndTransformArguments(ScriptBytecodeAdapter.java:234)
at com.sap.hadoop.windowing.WindowingException.<init>(WindowingException.groovy:21)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.codehaus.groovy.reflection.CachedConstructor.invoke(CachedConstructor.java:77)
at org.codehaus.groovy.runtime.callsite.ConstructorSite$ConstructorSiteNoUnwrapNoCoerce.callConstructor(ConstructorSite.java:102)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallConstructor(CallSiteArray.java:54)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callConstructor(AbstractCallSite.java:182)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callConstructor(AbstractCallSite.java:190)
at com.sap.hadoop.windowing.runtime.WindowingShell.parse(WindowingShell.groovy:72)
at com.sap.hadoop.windowing.runtime.WindowingShell.execute(WindowingShell.groovy:126)
at com.sap.hadoop.windowing.runtime.WindowingShell$execute.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
at com.sap.hadoop.windowing.cli.WindowingClient3.executeQuery(WindowingClient3.groovy:28)
at com.sap.hadoop.windowing.cli.WindowingClient3$executeQuery.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
at com.sap.hadoop.windowing.WindowingHiveCliDriver.processCmd(WindowingHiveCliDriver.groovy:117)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:406)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:341)
at org.apache.hadoop.hive.cli.CliDriver$processLine.call(Unknown Source)
at com.sap.hadoop.windowing.WindowingHiveCliDriver.main(WindowingHiveCliDriver.groovy:235)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
hive> wmode windowing;
hive> select product_id, order_date,lag ('order_date',1) AS prev_order_date from orders;
BR.recoverFromMismatchedToken
Exception in thread "main" java.lang.IncompatibleClassChangeError: the number of constructors during runtime and compile time for java.lang.Exception do not match. Expected 4 but got 5
at groovy.lang.MetaClassImpl.selectConstructorAndTransformArguments(MetaClassImpl.java:1400)
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.selectConstructorAndTransformArguments(ScriptBytecodeAdapter.java:234)
at com.sap.hadoop.windowing.WindowingException.<init>(WindowingException.groovy:21)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.codehaus.groovy.reflection.CachedConstructor.invoke(CachedConstructor.java:77)
at org.codehaus.groovy.runtime.callsite.ConstructorSite$ConstructorSiteNoUnwrapNoCoerce.callConstructor(ConstructorSite.java:102)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallConstructor(CallSiteArray.java:54)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callConstructor(AbstractCallSite.java:182)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callConstructor(AbstractCallSite.java:190)
at com.sap.hadoop.windowing.runtime.WindowingShell.parse(WindowingShell.groovy:72)
at com.sap.hadoop.windowing.runtime.WindowingShell.execute(WindowingShell.groovy:126)
at com.sap.hadoop.windowing.runtime.WindowingShell$execute.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
at com.sap.hadoop.windowing.cli.WindowingClient3.executeQuery(WindowingClient3.groovy:28)
at com.sap.hadoop.windowing.cli.WindowingClient3$executeQuery.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:42)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
at com.sap.hadoop.windowing.WindowingHiveCliDriver.processCmd(WindowingHiveCliDriver.groovy:117)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:406)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:341)
at org.apache.hadoop.hive.cli.CliDriver$processLine.call(Unknown Source)
at com.sap.hadoop.windowing.WindowingHiveCliDriver.main(WindowingHiveCliDriver.groovy:235)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
While the syntax error may be the reason, is there another cause?
Regards,
Omkar Joshi
________________________________
The contents of this e-mail and any attachment(s) may contain confidential or privileged information for the intended recipient(s). Unintended recipients are prohibited from taking action on the basis of information in this e-mail and using or disseminating the information, and must notify the sender and delete it from their system. L&T Infotech will not accept responsibility or liability for the accuracy or completeness of, or the presence of any virus or disabling code in this e-mail"