All Apps and Add-ons

Splunk DB Connect 2.2.0 - "Method not supported" exception while connecting to Hive (Hortonworks)

Explorer

I'm running Splunk 6.2 and trying to connect Hive through DB connect. I am able to add the DB type in (db_connection_types.conf) but i am not able to create any connection successfully. It looks like splunk makes some calls to hive JDBC but the method is not there in the hive driver class. Below are the exceptions,

While validating connection - Validating connection with URL [jdbc:hive2://myhiveserver:10000/default] failed: java.sql.SQLException:,Method not supported

While executing Query - External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (select * from mytable limit 1 ) t", caused by: AvroRemoteException(u'java.sql.SQLException: java.sql.SQLException:java.sql.SQLException: Method not supported',). "

Below are my configutations,

db_connection_types.conf

[hiveServer2]
displayName = Hive Server 2
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass  = org.apache.hive.jdbc.HiveDriver
port = 10000
jdbcUrlFormat = jdbc:hive2://<host>:<port>/<database>
testQuery = select * from table limit 1
database = default
useConnectionPool = false
 #Got connection pool error without above settings

db_connections.conf

    [myHive]
    connection_type = hiveServer2
    database = default
    host = myhiveserver
    identity = hiveCred
    jdbcUrlFormat = jdbc:hive2://<host>:<port>/<database>
    jdbcUseSSL = 0
    port = 10000
    readonly = 0
    disabled = 0

The versions of different tools are,
Splunk - 6.2 and 6.4
DB Connect v2 - 2.2.0
Java - jdk1.8.0_66
Hadoop - Hortonworks Hadoop 2.7.1.2.3.4.0-3485
Hive - 1.2.1.2.3.4.0-3485
Command ran - | dbxquery connection=myHive query="select * from table limit 1 "

Also i verified that i could execute the below command in beeline and run queries,

beeline -u jdbc:hive2://myhiveserver:10000/default

Any suggestion on the next steps would be helpful.

0 Karma

SplunkTrust
SplunkTrust

Hi. Did you ever get this to work?

I think I am running into this with Hive 1.2. Apparently it was fixed in 2.0 but I don't have the option of running that right now.

https://issues.apache.org/jira/browse/HIVE-11501

0 Karma

Explorer

I couldn't get this work and left this option. If you think the issue is when you set the connection as read only, did it work when you uncheck the read only option?

SplunkTrust
SplunkTrust

Actually, in the end, in order to get the Apache JDBC driver to work with us for Hive 1.2 we had to do the following.

1) readonly=0 as discussed above
2) there were at least three other methods that had be manually patched. If you would like to collaborate on this, let me know.

The GOOD news is that when I did end up using the JDBC driver for data that is not well partitioned I got much better performance.

0 Karma

Explorer

That is great work burwell! I would also love to hear the specifics of what you did. Could you share? It would be greatly appreciated. 🙂

0 Karma

SplunkTrust
SplunkTrust

We have a minimal working set of changes but some features are missing such as the ability to kill running jobs and some field names with null table names. There are work arounds for some of these issues such as the shortnames=1 to remove the table name, but the current status is not production ready.

We are working to push the Hive JDBC changes upstream to Apache.

Anyone who is interested in collaborating with me on testing and improving, please send mail to my last name at yahoo-inc.com. You should already be able to recompile Hive from source in order to apply these changes.

0 Karma

Explorer

Good work burwell 🙂
Could you share the information so that i can try?

0 Karma

Splunk Employee
Splunk Employee

Have you seen some of the details regarding the versions in this blog?
http://blogs.splunk.com/2015/02/25/splunk-db-connect-cloudera-hive-jdbc-connector/

0 Karma

Explorer

The blog neatly explained different steps required to establish hive connection using DB Connect 1. But it didn't help me out because of the below reasons,

1) Wanted to use DB Connect v2 as it the supported version going forward (V1 support lifecycle ends on July 29, 2016 and works only till splunk 6.3). I don't want this to be a problem while upgrading splunk.

2) We are using hortonworks hiveserver 2 whereas the blog talks about cloudera. I have taken the hortonworks hive jdbc driver but I'm getting the "Method not supported" exception.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!