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"
