HiveServer2 via a proxy
Team, Any ideas on how to connect to HiveServer2 from Beeline via a proxy server. I have tried everything I can think of and am using http as a transport mode, but it still doesn't work. Thanks, Andy
Use of functions in the WHERE clause.
Team, Looks like Hive doesn't like functions in the WHERE clause. Any ideas on how to deal with this? Also, I feel like this used to be supported in the past. Am I missing something? SELECT y, m, d FROM emp_table WHERE concat(y, m, d) > '20181216' limit 10; FAILED: SemanticException InvalidObjectException(message:Operator 'concat(y, m, d) > '20181216'' expects to have a column name on a left side, but got 'concat(y, m, d)' (Service: AWSGlue; Status Code: 400; Error Code: InvalidInputException; This is on Hive 2.3.4 on AWS EMR (metadata on AWS Glue). This works on AWS Athena (Presto) by the way. Thanks, Andy
ODBC-hiveserver2 question
Team, Is ADD JAR from HDFS (ADD JAR hdfs:///hive_jars/hive-contrib-2.1.1.jar;) supported in hiveserver2 via an ODBC connection? Some relevant points: - I am able to do it in Hive 2.1.1 via JDBC (beeline), but not via an ODBC client. - In Hive 1.2.1, I can add a jar from the local node, but not a JAR on HDFS. - Some old blogs online say HiveServer2 doesn't support "ADD JAR " period. But thats not what I experience via beeline. Let me know your thoughts and experiences. Thanks, Andy
Hive update question
Hi Team, Whats the best way to do an update on one table from another? Variations of this syntax below doesn't seem to work: UPDATE table_x SET column_1 = B.column_1 FROM table_y B WHERE column_2 = B.column_2 ; Thanks, Andy
Error with insert overwrite to s3n
Team, I am hitting this error discussed below and I am on Hive 1.2: http://qnalist.com/questions/6161150/hive-error-when-trying-to-write-data-to-s3n Gopal says in that thread: "Looks like the insert overwrite isn't triggering the CopyTask - MoveTask is the wrong operation for cross-FS operations. You can try looking at the Explain plan and confirm that." I see MoveTask in my explain plan too. Is there a JIRA for this yet? Any thoughts on a workaround? Thanks, Andy
Metastore and SerDe jars
Team, I know I can do a "ADD JAR" on the Hive CLI to use SerDe and UDF jars. If I am using the metastore thrift API, is there a way to add/register SerDe jars via the API? Basically, whats the metastore API equivalent of CLI's "ADD JAR "? I tried setting the hive.aux.jars.path property in the hive-site.xml to a HDFS path with the SerDe jars and restarted the metastore.. But it doesn't seem to work. Thanks, Andy
Re: UDF reflect
Thanks Szehon and Peyman, I want to call hashCode() on the UUID object. This object is stored in the table as a string, but I can convert it to UUID. Thats not the problem. Basically the question is, how do we call this reflect UDF on methods that takes no arguments? How do I do the following using the reflect UDF? (fromString(uid_str)).hashCode() Thanks, Andy On Thu, Apr 3, 2014 at 6:18 AM, Peyman Mohajerian mohaj...@gmail.comwrote: Maybe your intention is the following: reflect(java.util.UUID, randomUUID) On Thu, Apr 3, 2014 at 2:33 AM, Szehon Ho sze...@cloudera.com wrote: Hi, according to the description of the reflect UDF, you are trying to call java.util.UUID.hashcode(uidString), which doesnt seem to be an existing method on either java 6/7. http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html#hashCode() Thanks Szehon On Wed, Apr 2, 2014 at 2:13 PM, Andy Srine andy.sr...@gmail.com wrote: Hi guys, I am trying to use the reflect UDF for an UUID method and am getting an exception. I believe this function should be available in java 1.6.0_31 the system is running. select reflect(java.util.UUID, hashCode, uid_str) my_uid, ... My suspicion is, this is because the hive column I am calling this on is a string and not an UUID. So I nested the reflects as shown below to go from a string to an UUID first and then to hashCode it. reflect(java.util.UUID, hashCode, reflect(java.util.UUID, fromString, uid_str)) my_uid, In either case, I always get the exception below though the row of data it prints has no null for the uid_str column. Any ideas? at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:565) at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:143) ... 8 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: UDFReflect getMethod at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:164) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.evaluate(ExprNodeGenericFuncEvaluator.java:163) at org.apache.hadoop.hive.ql.exec.KeyWrapperFactory$ListKeyWrapper.getNewKey(KeyWrapperFactory.java:113) at org.apache.hadoop.hive.ql.exec.GroupByOperator.processOp(GroupByOperator.java:794) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:83) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:548) ... 9 more Caused by: java.lang.NoSuchMethodException: java.util.UUID.hashCode(null) at java.lang.Class.getMethod(Class.java:1605) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:160) Thanks, Andy -- Thanks, Andy
Re: UDF reflect
Thanks Edward. But inline groovy is available on Hive 13 right? I am using an older version. Best, Andy On Thu, Apr 3, 2014 at 11:37 AM, Edward Capriolo edlinuxg...@gmail.comwrote: You can write UDF's in groovy now. That pretty much means. You can just write a quick method inline now. Makese udf reflect much less useful. On Thu, Apr 3, 2014 at 2:22 PM, Andy Srine andy.sr...@gmail.com wrote: Thanks Szehon and Peyman, I want to call hashCode() on the UUID object. This object is stored in the table as a string, but I can convert it to UUID. Thats not the problem. Basically the question is, how do we call this reflect UDF on methods that takes no arguments? How do I do the following using the reflect UDF? (fromString(uid_str)).hashCode() Thanks, Andy On Thu, Apr 3, 2014 at 6:18 AM, Peyman Mohajerian mohaj...@gmail.comwrote: Maybe your intention is the following: reflect(java.util.UUID, randomUUID) On Thu, Apr 3, 2014 at 2:33 AM, Szehon Ho sze...@cloudera.com wrote: Hi, according to the description of the reflect UDF, you are trying to call java.util.UUID.hashcode(uidString), which doesnt seem to be an existing method on either java 6/7. http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html#hashCode() Thanks Szehon On Wed, Apr 2, 2014 at 2:13 PM, Andy Srine andy.sr...@gmail.comwrote: Hi guys, I am trying to use the reflect UDF for an UUID method and am getting an exception. I believe this function should be available in java 1.6.0_31 the system is running. select reflect(java.util.UUID, hashCode, uid_str) my_uid, ... My suspicion is, this is because the hive column I am calling this on is a string and not an UUID. So I nested the reflects as shown below to go from a string to an UUID first and then to hashCode it. reflect(java.util.UUID, hashCode, reflect(java.util.UUID, fromString, uid_str)) my_uid, In either case, I always get the exception below though the row of data it prints has no null for the uid_str column. Any ideas? at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:565) at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:143) ... 8 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: UDFReflect getMethod at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:164) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.evaluate(ExprNodeGenericFuncEvaluator.java:163) at org.apache.hadoop.hive.ql.exec.KeyWrapperFactory$ListKeyWrapper.getNewKey(KeyWrapperFactory.java:113) at org.apache.hadoop.hive.ql.exec.GroupByOperator.processOp(GroupByOperator.java:794) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:83) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:548) ... 9 more Caused by: java.lang.NoSuchMethodException: java.util.UUID.hashCode(null) at java.lang.Class.getMethod(Class.java:1605) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:160) Thanks, Andy -- Thanks, Andy -- Thanks, Andy
Re: UDF reflect
Just to add to my previous question, I see this example in Hive documentation for the reflect UDF. What object or string is the isEmpty method working on in the example below? What I am trying to do with hashCode() is something similar. SELECT reflect(java.lang.String, valueOf, 1), reflect(java.lang.String, isEmpty), reflect(java.lang.Math, max, 2, 3), reflect(java.lang.Math, min, 2, 3), reflect(java.lang.Math, round, 2.5), reflect(java.lang.Math, exp, 1.0), reflect(java.lang.Math, floor, 1.9) FROM src LIMIT 1; 1 true 3 2 3 2.7182818284590455 1.0 Thanks, Andy On Thu, Apr 3, 2014 at 11:22 AM, Andy Srine andy.sr...@gmail.com wrote: Thanks Szehon and Peyman, I want to call hashCode() on the UUID object. This object is stored in the table as a string, but I can convert it to UUID. Thats not the problem. Basically the question is, how do we call this reflect UDF on methods that takes no arguments? How do I do the following using the reflect UDF? (fromString(uid_str)).hashCode() Thanks, Andy On Thu, Apr 3, 2014 at 6:18 AM, Peyman Mohajerian mohaj...@gmail.comwrote: Maybe your intention is the following: reflect(java.util.UUID, randomUUID) On Thu, Apr 3, 2014 at 2:33 AM, Szehon Ho sze...@cloudera.com wrote: Hi, according to the description of the reflect UDF, you are trying to call java.util.UUID.hashcode(uidString), which doesnt seem to be an existing method on either java 6/7. http://docs.oracle.com/javase/7/docs/api/java/util/UUID.html#hashCode() Thanks Szehon On Wed, Apr 2, 2014 at 2:13 PM, Andy Srine andy.sr...@gmail.com wrote: Hi guys, I am trying to use the reflect UDF for an UUID method and am getting an exception. I believe this function should be available in java 1.6.0_31 the system is running. select reflect(java.util.UUID, hashCode, uid_str) my_uid, ... My suspicion is, this is because the hive column I am calling this on is a string and not an UUID. So I nested the reflects as shown below to go from a string to an UUID first and then to hashCode it. reflect(java.util.UUID, hashCode, reflect(java.util.UUID, fromString, uid_str)) my_uid, In either case, I always get the exception below though the row of data it prints has no null for the uid_str column. Any ideas? at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:565) at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:143) ... 8 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: UDFReflect getMethod at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:164) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.evaluate(ExprNodeGenericFuncEvaluator.java:163) at org.apache.hadoop.hive.ql.exec.KeyWrapperFactory$ListKeyWrapper.getNewKey(KeyWrapperFactory.java:113) at org.apache.hadoop.hive.ql.exec.GroupByOperator.processOp(GroupByOperator.java:794) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:83) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:548) ... 9 more Caused by: java.lang.NoSuchMethodException: java.util.UUID.hashCode(null) at java.lang.Class.getMethod(Class.java:1605) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:160) Thanks, Andy -- Thanks, Andy -- Thanks, Andy
UDF reflect
Hi guys, I am trying to use the reflect UDF for an UUID method and am getting an exception. I believe this function should be available in java 1.6.0_31 the system is running. select reflect(java.util.UUID, hashCode, uid_str) my_uid, ... My suspicion is, this is because the hive column I am calling this on is a string and not an UUID. So I nested the reflects as shown below to go from a string to an UUID first and then to hashCode it. reflect(java.util.UUID, hashCode, reflect(java.util.UUID, fromString, uid_str)) my_uid, In either case, I always get the exception below though the row of data it prints has no null for the uid_str column. Any ideas? at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:565) at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:143) ... 8 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: UDFReflect getMethod at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:164) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.evaluate(ExprNodeGenericFuncEvaluator.java:163) at org.apache.hadoop.hive.ql.exec.KeyWrapperFactory$ListKeyWrapper.getNewKey(KeyWrapperFactory.java:113) at org.apache.hadoop.hive.ql.exec.GroupByOperator.processOp(GroupByOperator.java:794) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:83) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:800) at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:548) ... 9 more Caused by: java.lang.NoSuchMethodException: java.util.UUID.hashCode(null) at java.lang.Class.getMethod(Class.java:1605) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFReflect.evaluate(GenericUDFReflect.java:160) Thanks, Andy
