R通过RJDBC连接Hive相关

连接方式1

	require("DBI")
	require("rJava")
	require("RJDBC")
	
	username <- '******'
	password <- '******'
	
	# .jclassLoader()$setDebug(1L)
	cp <- dir("/opt/cloudera/parcels/CDH/jars", full.names = TRUE)
	.jinit(classpath = cp) 
	options( java.parameters = "-Xmx8g" )
	drv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/opt/cloudera/parcels/CDH/lib/hive/lib/hive-jdbc.jar", identifier.quote="`")
	# make sure disconnect the connection after the query
	conn <- dbConnect(drv, "jdbc:hive2://172.156.188.28:10000/default", username, password)
	options(width = 160)
	
	show_databases <- dbGetQuery(conn, 'select * from library limit 10')
	head(show_databases)
	
	library(knitr)
	kable(show_databases)
	
	dbDisconnect(conn)

	### http://172.16.18.2/

连接方式2

	library("DBI")
	library("rJava")
	library("RJDBC")
	
	username <- '******'
	password <- '******'

	for(l in list.files('/opt/cloudera/parcels/CDH/jars/', full.names = TRUE)){ .jaddClassPath(l)}
	
	drv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/opt/cloudera/parcels/CDH/lib/hive/lib/hive-jdbc-standalone.jar")
	
	for(l in list.files('/opt/org/apache/hadoop/2.7.3/share/hadoop/common/', full.names = TRUE)){ .jaddClassPath(l)}
	
	conn <- dbConnect(drv, "jdbc:hive2://172.156.188.28:10000/default", username, password)
	
	library(knitr)
	kable(show_databases) # table's meta data, for gollum wiki
	
	## Get data from hive
	show_databases <- dbGetQuery(conn, 'select * from library limit 10')
	
	head(show_databases)
	
	library(knitr)
	kable(show_databases)
	
	dbDisconnect(conn)

连接方式-函数

数据量太大问题

Have you tired to make increase the memory to 4g instead of 1g. I mean set options for the java parameters to be -Xmx4g instead of -Xmx1g

Simply change the java.parameters to 8GB before load RJDBC package:

	# options(java.parameters = "-Xmx8048m")
	# library("RJDBC")

You can increase the memory as below and restart your Rstudio and it worked for me.

	# memory.limit(size=10000000000024)

函数

	get_hive_data <- function(oUsername, oPassword, oSql){
	  
	  # library(DBI)
	  # library(rJava)
	  # library(RJDBC)
	  options( java.parameters = "-Xmx8048m" )
	  require(DBI)
	  require(rJava)
	  require(RJDBC)
	  username <- oUsername
	  password <- oPassword
	  cp <- dir("/opt/cloudera/parcels/CDH/jars", full.names = TRUE)
	  .jinit(classpath = cp) 
	  # options( java.parameters = "-Xmx8g" )
	
	  drv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/opt/cloudera/parcels/CDH/lib/hive/lib/hive-jdbc.jar", identifier.quote="`")
	  # make sure disconnect the connection after the query
	  conn <- dbConnect(drv, "jdbc:hive2://172.156.188.28:10000/default", username, password)
	  options(width = 160)
	  result <- dbGetQuery(conn, oSql)
	  dbDisconnect(conn)
	  return(result)
	  
	}
	
	oUsername <- '******'
	oPassword <- '******'
	oSql <- 'select * from library limit 1000000'
	# oSql <- 'select count(*) from library'
	test_data <- get_hive_data(oUsername, oPassword, oSql)
	dim(test_data)
	head(test_data)
	
	library(knitr)
	kable(test_data)