Reply
Contributor
Posts: 44
Registered: ‎09-26-2014

HIVE ODBC driver - cannot select table

Just insalled the latest version of HIVE ODBC driver (2.5.12) and created a linked server in Microsoft SQL Server 2014.

Tried a simple select * from table, or even one integer column such as select ID from table, but the queries failed with this

 

OLE DB provider "MSDASQL" for linked server "CLOUDERA-HIVE" returned message "[Cloudera][HiveODBC] (35) Error from Hive: error code: '0' error message: 'ExecuteStatement finished with operation state: ERROR_STATE'.".
Msg 7320, Level 16, State 2, Line 4
Cannot execute the query "SELECT "Tbl1002"."row_id" "Col1004" FROM "HIVE"."default"."test_table" "Tbl1002"" against OLE DB provider "MSDASQL" for linked server "CLOUDERA-HIVE".

 

Any ideas?

Thanks,

Tomas

 

PS. Tried to enable Fast SQL Prepare, but didnt help.

Tried to enable Use Async exec - didnt help.

Native query - dont work on MS SQL.

 

 

New Contributor
Posts: 2
Registered: ‎12-27-2014

Re: HIVE ODBC driver - cannot select table

For me I can select * from the table, whereas if want to select a particular column, hive odbc fails. 

Explorer
Posts: 7
Registered: ‎05-05-2014

Re: HIVE ODBC driver - cannot select table

Hi  Thomas ,

 

Sorry for posting this question here .I hope you can solve my issue .

 

I have installed cloudera CDH4.7.1 A 3 cluster.I am trying to load data from hive to oracle database using Oracle loader for hadoop.I have integrated hadoop with Oracle Data Integrater ODI .For trigering OLH from ODI we have knowledge module File-Hive to Oracle (OLH-OSCH).

 

I am getting following error:

5/01/08 00:20:47 INFO loader.OraLoader: Oracle Loader for Hadoop Release 3.0.0 - Production
 
Copyright (c) 2011, 2014, Oracle and/or its affiliates. All rights reserved.
 
 
15/01/08 00:20:47 INFO loader.OraLoader: Built-Against: hadoop-2.2.0-cdh5.0.0-beta-2 hive-0.12.0-cdh5.0.0-beta-2 avro-1.7.3 jackson-1.8.8
15/01/08 00:20:48 INFO utils.OraLoaderConf: property "oracle.hadoop.loader.loaderMapFile" is deprecated; use "oracle.hadoop.loader.loaderMap.*" instead
15/01/08 00:21:03 INFO loader.OraLoader: oracle.hadoop.loader.loadByPartition is disabled because table: I_SAMPLE_TRG is not partitioned
15/01/08 00:21:03 INFO loader.OraLoader: oracle.hadoop.loader.enableSorting disabled, no sorting key provided
15/01/08 00:21:03 INFO loader.OraLoader: Reduce tasks set to 0 because of no partitioning or sorting. Loading will be done in the map phase.
15/01/08 00:21:04 INFO output.DBOutputFormat: Setting map tasks speculative execution to false for : oracle.hadoop.loader.lib.output.JDBCOutputFormat
15/01/08 00:21:08 WARN loader.OraLoader: Sampler error: the number of reduce tasks must be greater than one; the configured value is 0 . Job will continue without sampled information.
15/01/08 00:21:08 INFO loader.OraLoader: Sampling time=0D:0h:0m:0s:79ms (79 ms)
15/01/08 00:21:08 INFO loader.OraLoader: Submitting OraLoader job OraLoader
15/01/08 00:21:15 WARN mapred.JobClient: Use GenericOptionsParser for parsing the arguments. Applications should implement Tool for the same.
15/01/08 00:21:16 WARN conf.HiveConf: hive-site.xml not found on CLASSPATH
15/01/08 00:21:18 WARN conf.HiveConf: DEPRECATED: Configuration property hive.metastore.local no longer has any effect. Make sure to provide a valid value for hive.metastore.uris if you are connecting to a remote metastore.
15/01/08 00:21:18 INFO hive.metastore: Trying to connect to metastore with URI thrift://172.16.12.217:10000
15/01/08 00:21:18 INFO hive.metastore: Connected to metastore.
15/01/08 00:21:38 INFO mapred.JobClient: Cleaning up the staging area hdfs://n1.bias.com:8020/user/oracle/.staging/job_201501072220_0005
15/01/08 00:21:38 ERROR security.UserGroupInformation: PriviledgedActionException as:oracle (auth:SIMPLE) cause:java.io.IOException: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out
java.io.IOException: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out
at oracle.hadoop.loader.lib.input.HiveToAvroInputFormat.getSplits(HiveToAvroInputFormat.java:451)
at org.apache.hadoop.mapred.JobClient.writeNewSplits(JobClient.java:1107)
at org.apache.hadoop.mapred.JobClient.writeSplits(JobClient.java:1124)
at org.apache.hadoop.mapred.JobClient.access$600(JobClient.java:178)
at org.apache.hadoop.mapred.JobClient$2.run(JobClient.java:1023)
at org.apache.hadoop.mapred.JobClient$2.run(JobClient.java:976)
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:1438)
at org.apache.hadoop.mapred.JobClient.submitJobInternal(JobClient.java:976)
at org.apache.hadoop.mapreduce.Job.submit(Job.java:582)
at oracle.hadoop.loader.OraLoader.monitorAndPrintJob(OraLoader.java:652)
at oracle.hadoop.loader.OraLoader.run(OraLoader.java:580)
at oracle.hadoop.loader.OraLoader.run(OraLoader.java:412)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:84)
at oracle.hadoop.loader.OraLoader.main(OraLoader.java:940)
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.hadoop.util.RunJar.main(RunJar.java:208)
Caused by: org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:129)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:84)
at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:378)
at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:297)
at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:204)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:69)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_get_table(ThriftHiveMetastore.java:879)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.get_table(ThriftHiveMetastore.java:865)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTable(HiveMetaStoreClient.java:788)
at oracle.hadoop.loader.lib.input.HiveToAvroInputFormat.getSplits(HiveToAvroInputFormat.java:259)
... 21 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:152)
at java.net.SocketInputStream.read(SocketInputStream.java:122)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:235)
at java.io.BufferedInputStream.read1(BufferedInputStream.java:275)
at java.io.BufferedInputStream.read(BufferedInputStream.java:334)
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:127)
 
Thanks in advance
please reply as I have client demo 14 jan 2015.
Thanks&Regards
Vishal
Contributor
Posts: 44
Registered: ‎09-26-2014

Re: HIVE ODBC driver - cannot select table

Hi Visahl, no idea what would be the problem, in my case the HIVE connector simply did not read the metadata correctly.

I am sorry, but I cannot help you

Good luck

T

 

Explorer
Posts: 7
Registered: ‎05-05-2014

Re: HIVE ODBC driver - cannot select table

Hi Thomas ,the issue has been solved.My aim was to integrate Oracle Data Integrater with hadoop for loading data from Hive to Oracle database using OLH(oracle loader for hadoop.If you want to know the solution ,I can share with you.

 

Thanks 

Vishal

New Contributor
Posts: 2
Registered: ‎08-04-2015

Re: HIVE ODBC driver - cannot select table

Hi Tomas, have you solve this problem yet ? I have encountered the same problem through using ODBC. And I have tried Microsoft Driver for Hive and Cloudera Driver for Apache Hive but both of them didn't work.

New Contributor
Posts: 1
Registered: ‎04-06-2016

Re: HIVE ODBC driver - cannot select table

Hi i had the same issue trying to query hiveserver2 from tableau 9.1, i resolved it by configuring the ODBC driver as user hdfs, i logged on to the hiveserver2 as user hdfs and i was able to execute the queries

Highlighted
New Contributor
Posts: 2
Registered: ‎07-22-2016

Re: HIVE ODBC driver - cannot select table

Hi Vishal,

 

      I am having the same issues, clould you please provide the setps, what you did to slove the problem.

 

Thanks 

Satish