Hive Insert query is failing
Hi, Hive query running on Tez to insert from one table to another is failing with the below error. Both the tables have file format as ORC and all the columns in both the tables are string. Can anyone help on how it can be fixed. Hive version: 1.2.1.2.4 Error message: Vertex failed, vertexName=Map 1, vertexId=vertex_1483552897173_0276_1_00, diagnostics=[Task failed, taskId=task_1483552897173_0276_1_00_00, diagnostics=[TaskAttempt 0 failed, info=[Error: Failure while running task:java.lang.RuntimeException: java.lang.RuntimeException: java.io.IOException: java.io.IOException: ORC does not support type conversion from VARCHAR to STRING at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:173) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.run(TezProcessor.java:139) at org.apache.tez.runtime.LogicalIOProcessorRuntimeTask.run(LogicalIOProcessorRuntimeTask.java:344) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable$1.run(TezTaskRunner.java:181) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable$1.run(TezTaskRunner.java:172) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1709) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable.callInternal(TezTaskRunner.java:172) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable.callInternal(TezTaskRunner.java:168) at org.apache.tez.common.CallableWithNdc.call(CallableWithNdc.java:36) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Query: insert into table tableB select col1, col2, col3, col4, col5, col6, col7, col8 from tableA Thanks Amit Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message!
Reading hive-site.xml
Hi, I am trying to understand how hive is reading the configuration from hive-site.xml. Where we define the structure of the xml file and code used to read the hite-site.xml. Thanks Amit Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message!
RE: Error running SQL query through Hive JDBC
Below is the code snippet with the SQL query which I am running. The same query is running fine through Hive CLI. String sql = " SELECT TBL_CODE FROM DB.CODE_MAP WHERE SYSTEM_NAME='TDS' AND TABLE_NAME=TRIM('XYZ')"; System.out.println("New SQL: " + sql); String driverName = "org.apache.hive.jdbc.HiveDriver"; try { Class.forName(driverName); Connection con = DriverManager.getConnection( "jdbc:hive2://hiveservername:1/default", "username", ""); HiveStatement stmt = (HiveStatement) con.createStatement(); ResultSet res = stmt.executeQuery(sql); while (res.next()) { Object ret_obj = res.getObject(1); System.out.println(res.getString(1)); } stmt.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } From: Markovitz, Dudu [mailto:dmarkov...@paypal.com] Sent: Friday, August 05, 2016 3:04 PM To: user@hive.apache.org Subject: RE: Error running SQL query through Hive JDBC Can you please share the query? From: Amit Bajpai [mailto:amit.baj...@flextronics.com] Sent: Friday, August 05, 2016 10:40 PM To: user@hive.apache.org<mailto:user@hive.apache.org> Subject: Error running SQL query through Hive JDBC Hi, I am getting the below error when running the SQL query through Hive JDBC. Can suggestion how to fix it. org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException UDF = is not allowed at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:231) at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:217) at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:254) at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:392) at com.flex.hdp.logs.test.main(test.java:84) Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException UDF = is not allowed at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:314) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:111) at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:180) at org.apache.hive.service.cli.operation.Operation.run(Operation.java:256) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:376) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:363) 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:606) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:79) at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:37) at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:64) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1628) at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:536) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:60) at com.sun.proxy.$Proxy32.executeStatementAsync(Unknown Source) at org.apache.hiv
Error running SQL query through Hive JDBC
Hi, I am getting the below error when running the SQL query through Hive JDBC. Can suggestion how to fix it. org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException UDF = is not allowed at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:231) at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:217) at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:254) at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:392) at com.flex.hdp.logs.test.main(test.java:84) Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException UDF = is not allowed at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:314) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:111) at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:180) at org.apache.hive.service.cli.operation.Operation.run(Operation.java:256) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:376) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:363) 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:606) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:79) at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:37) at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:64) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1628) at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:536) at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:60) at com.sun.proxy.$Proxy32.executeStatementAsync(Unknown Source) at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:271) at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:401) at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313) at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.parse.SemanticException:UDF = is not allowed at org.apache.hadoop.hive.ql.exec.FunctionRegistry.getFunctionInfo(FunctionRegistry.java:677) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.getXpathOrFuncExprNodeDesc(TypeCheckProcFactory.java:810) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:1152) at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:90) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatchAndReturn(DefaultGraphWalker.java:94) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:78) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:132) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:109) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:189) at
RE: hive concurrency not working
You need to increase the value for the below hive property value in Ambari hive.server2.tez.sessions.per.default.queue If this does not fix the issue then you need to update the capacity scheduler property values. From: Raj hadoop [mailto:raj.had...@gmail.com] Sent: Wednesday, August 03, 2016 8:15 AM To: user@hive.apache.org Subject: hive concurrency not working Dear All, In need or your help, we have horton works 4 node cluster,and the problem is hive is allowing only one user at a time, if any second resource need to login hive is not working, could someone please help me in this Thanks, Rajesh Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message!
RE: Yarn Application ID for Hive query
I am running hive on Tez. I am able to get the Yarn application ID for the hive query by submitting the query through Hive JDBC and using HiveStatement. Connection con = DriverManager.getConnection("jdbc:hive2://abc:1/default","xyz", ""); HiveStatement stmt = (HiveStatement) con.createStatement(); String sql = " SELECT COMP_ID, COUNT(1) FROM tableA GROUP BY COMP_ID "; ResultSet res = stmt.executeQuery(sql); String yarn_app_id = new String(); for (String log : stmt.getQueryLog()) { if (log.contains("App id")){ yarn_app_id = log.substring(log.indexOf("App id") +7, log.length()-1); } } System.out.println("YARN Application ID: " + yarn_app_id); Now I am trying to find the Tez DAG ID for the query. From: Gerber, Bryan W [mailto:bryan.ger...@pnnl.gov] Sent: Monday, July 18, 2016 1:47 PM To: user@hive.apache.org Subject: RE: Yarn Application ID for Hive query Making Hive look like a normal SQL database is the goal of libraries like this, so it make sense that that abstraction wouldn't leak a concept like application ID. Especially because not all Hive queries generate a YARN application. That said, we went through this with JDBC access to Hive a while back to allow our user interface to cancel a query. Only relevant discussion I found was here: http://grokbase.com/t/cloudera/hue-user/1373c258xg/how-hue-beeswax-is-able-to-read-the-hadoop-job-id-that-gets-generated-by-hiveserver2 We are using this method, plus a background task that polls the YARN resource manager API to find the job with the corresponding hive.session.id. It is a lot of work for something that seems very simple. It would be nice to have access to a command or API call in HiveServer2 similar to MySQL's "SHOW PROCESSLIST" (and equivalent commands in most other databases). From: Amit Bajpai [mailto:amit.baj...@flextronics.com] Sent: Thursday, July 14, 2016 10:22 PM To: user@hive.apache.org<mailto:user@hive.apache.org> Subject: Yarn Application ID for Hive query Hi, I am using the below python program to run a hive query. How can I get the Yarn application ID using the python program for the hive query execution. import pyhs2 with pyhs2.connect(host='abc.sac.com', port=1, authMechanism="PLAIN", user='amit', password='amit', database='default') as conn: with conn.cursor() as cur: #Execute query cur.execute("SELECT COMP_ID, COUNT(1) FROM tableA GROUP BY COMP_ID") #Fetch table results for i in cur.fetch(): print i Thanks Amit Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message! Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message!
Yarn Application ID for Hive query
Hi, I am using the below python program to run a hive query. How can I get the Yarn application ID using the python program for the hive query execution. import pyhs2 with pyhs2.connect(host='abc.sac.com', port=1, authMechanism="PLAIN", user='amit', password='amit', database='default') as conn: with conn.cursor() as cur: #Execute query cur.execute("SELECT COMP_ID, COUNT(1) FROM tableA GROUP BY COMP_ID") #Fetch table results for i in cur.fetch(): print i Thanks Amit Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message!