Connectivity between R and Hive

Question

I am trying to establish a connection between RStudio (on my machine) and Hive (which is setup on a different server). Here's my R code:

install.packages("RJDBC",dep=TRUE)
require(RJDBC)

drv <- JDBC(driverClass = "org.apache.hive.jdbc.HiveDriver", 
       classPath = list.files("C:/Users/37/Downloads/hive-jdbc-0.10.0.jar",
       pattern="jar$",full.names=T),
       identifier.quote="'")

Here is the error I get while executing the above commands:

Error in .jfindClass(as.character(driverClass)1) : class not found conn <- dbConnect(drv, "jdbc:hive2://65.11.23.453:10000/default", "admin", "admin")

I downloaded the jar files from here and placed them in the CLASSPATH. Please advise if am doing anything wrong and how I could get this to work.

Thanks.

Answer 1

If you have a cloudera, check version and download jars for that. Example CDH 5.9.1 hadoop-common-2.6.0-cdh5.9.1.jar hive-jdbc-1.1.1-standalone.jar

copy the jars into a folder into R host and execute:

 library("DBI")

 library("rJava")

 library("RJDBC")

#init of the classpath (works with hadoop 2.6 on CDH 5.4 installation)
cp = c("/home/youruser/rlibs/hadoop-common-2.6.0-cdh5.9.1.jar", "/home/youruser/rlibs/hive-jdbc-1.1.1-standalone.jar")
.jinit(classpath=cp)

#initialisation de la connexion
drv <- JDBC("org.apache.hive.jdbc.HiveDriver", /home/youruser/rlibs/hive-jdbc-1.1.1-standalone.jar", identifier.quote="`")

conn <- dbConnect(drv,"jdbc:hive2://HiveServerHostInYourCluster:10000/default;", "YourUserHive", "xxxx")

#working with the connexion
show_databases <- dbGetQuery(conn, "show databases")
show_databases

Answer 2

I tried this sample code and it worked for me:

library(RJDBC)

#Load Hive JDBC driver
hivedrv <- JDBC("org.apache.hadoop.hive.jdbc.HiveDriver",
                c(list.files("/home/amar/hadoop/hadoop",pattern="jar$",full.names=T),
                  list.files("/home/amar/hadoop/hive/lib",pattern="jar$",full.names=T)))

#Connect to Hive service
hivecon <- dbConnect(hivedrv, "jdbc:hive://ip:port/default")
query = "select * from mytable LIMIT 10"
hres <- dbGetQuery(hivecon, query)

Answer 3

Same error happened to me earlier when I was trying to use RJDBC to connect to Cassandra, it was solved by putting the Cassandra JDBC dependencies in your JAVA ClassPath.

See this answer:

Answer 4

For anyone who finds this post there are a couple things you can try to fix the problem:

1.) reinstall rJava from source install.packages("rJava","http://rforge.net/",type="source")

2.) Initiate java debugger for loading and try to connect again .jclassLoader()$setDebug(1L)

3.) I've had to use both Sys.setenv(JAVA_HOME = /Path/to/java) before and utilize dyn.load('/Library/Java/JavaVirtualMachines/jdk1.8.0_121.jdk/Contents/Home/jre/lib/server/libjvm.dylib') to find the right jvm library.

4.) As stated rJava load error in RStudio/R after "upgrading" to OSX Yosemite, you can also create a link between the the libjvm.dylib to /usr/local/lib

sudo ln -f -s $(/usr/libexec/java_home)/jre/lib/server/libjvm.dylib /usr/local/lib

If all of these fail, a uninstall and install of R has also worked for me in the past.

Answer 5

This has helped me so far.

1) First check if the hive service is running, if not restart it.

sudo service hive-server2 status
sudo service hive-server2 restart

2) install rJava and RJDBCin R.

library(rJava)
library(RJDBC)

options(java.parameters = '-Xmx8g')
hadoop_jar_dirs <- c('/usr/lib/hadoop/lib',
                     '/usr/lib/hadoop',
                     '/usr/lib/hive/lib')
clpath <- c()
for (d in hadoop_jar_dirs) {
  clpath <- c(clpath, list.files(d, pattern = 'jar', full.names = TRUE))
}
.jinit(classpath = clpath)
.jaddClassPath(clpath)

hive_jdbc_jar <- '/usr/lib/hive/lib/hive-jdbc-2.1.1.jar'
hive_driver <- 'org.apache.hive.jdbc.HiveDriver'
hive_url <- 'jdbc:hive2://localhost:10000/default'
drv <- JDBC(hive_driver, hive_jdbc_jar)
conn <- dbConnect(drv, hive_url)
show_databases <- dbGetQuery(conn, "show databases")

show_databases

Make sure to give correct path to hadoop_jar_dirs, hive_jdbc_jar and hive_driver.

Answer 6

I wrote a package for dealing with this (and kerberos):

devtools::install_github('nfultz/hiveuberjar')

require(DBI)
con <- dbConnect(hiveuberjar::HiveUber(), url="jdbc://host:port/schema")
dbListTables(con)
dbGetQuery(con, "Select count(*) from nfultz.iris limit 10")