RJDBC - Could not open client transport with JDBC Uri

Question

I'm trying to connect to my Biginsights Enterprise cluster from RJDBC:

hiveconnection <- dbConnect(drv,
     "jdbc:hive2://xxxxx:10001/default",
     "xxxxx",
     "xxxxx",
     ssl="true",
     sslTrustStore="mytruststore.jks",
     trustStorePassword="xxxxx",
     hive.server2.transport.mode="http",
     hive.server2.thrift.http.path="gateway/default/hive
)

However, I'm hitting the issue:

Could not open client transport with JDBC Uri

Answer 1

This works for me:

username = 'changeme'
password = 'changeme'

# change this to your hostname
url = 'jdbc:hive2://bi4c-xxxx-master-3.bi.services.bluemix.net:10000/default;ssl=true;sslTrustStore=/change/to/yourpath/to/truststore.jks;trustStorePassword=mypassword;' 

library(rJava)
library(RJDBC)

hive.class.path = list.files(path=c('/change/to/yourpath/to/build/hivedrivers/'), pattern="jar", full.names=T);
.jinit(classpath=hive.class.path,parameters="")

drv <- JDBC("org.apache.hive.jdbc.HiveDriver","hive-jdbc-2.0.0.jar",identifier.quote="`")

conn <- dbConnect(drv,url,username,password)
show_databases <- dbGetQuery(conn, "show databases")
print(show_databases)

---

Setup Steps

I used gradle to download all the hive jdbc dependencies to a folder. I next created a build.gradle file:

repositories {
    mavenCentral()
}
configurations {
    drivers
}
dependencies {
    drivers "org.apache.hive:hive-jdbc:2.0.0"
}
task CopyDrivers(type: Copy) {
    from configurations.drivers
        into "$buildDir/hivedrivers"
}

Then in the terminal window I changed to directory of my build.gradle file, I executed:

$ gradle CopyDrivers

I created the truststore with:

$ BI_HOST=bi4c-xxxxx-master-3.bi.services.bluemix.net
$ openssl s_client -showcerts -connect $BI_HOST:9443 < /dev/null | openssl x509 -outform PEM > certificate
$ rm -f truststore.jks
$ keytool -import -trustcacerts -alias biginsights -file certificate -keystore truststore.jks -storepass mypassword -noprompt