52.R连接Hive与Impala

52.1 演示环境介绍

• 已配置R环境
• CDSW运行正常
• 集群未启用认证服务
• CM和CDH版本：5.11.2
• 用sudo权限的ec2-user用户操作
• R版本：3.4.2
• CDSW版本：1.1.1

52.2 操作演示

驱动包准备

• 准备Hive的JDBC驱动和Impala的JDBC驱动
• Hive下载地址：

[ec2-user@ip-168-33-22-56 hive-test]$ wget https://downloads.cloudera.com/connectors/hive_jdbc_2.5.19.1053.zip

• Impala下载地址：

[ec2-user@ip-168-33-22-56 hive-test]$ wget https://downloads.cloudera.com/connectors/impala_jdbc_2.5.41.1061.zip

• 解压——以Hive JDBC为例

[ec2-user@ip-168-33-22-56 hive-test]$ unzip hive_jdbc_2.5.19.1053.zip
[ec2-user@ip-168-33-22-56 2.5.19.1053 GA]$ cd 2.5.19.1053\ GA/
[ec2-user@ip-168-33-22-56 2.5.19.1053 GA]$ ll
total 17112
-rw-rw-r-- 1 ec2-user ec2-user 8414253 Jul  7 16:19 Cloudera_HiveJDBC41.zip
-rw-rw-r-- 1 ec2-user ec2-user 8398497 Jul  7 16:20 Cloudera_HiveJDBC4.zip
-r--r--r-- 1 ec2-user ec2-user  559084 Jul  4 11:30 Cloudera-JDBC-Driver-for-Apache-Hive-Install-Guide.pdf
-r--r--r-- 1 ec2-user ec2-user  117144 Jul  4 11:30 Cloudera-JDBC-Driver-for-Apache-Hive-Release-Notes.pdf
-rw-rw-r-- 1 ec2-user ec2-user   22065 Aug 10 06:26 Cloudera-JDBC-Driver-for-Apache-Hive-Release-Notes.txt
[ec2-user@ip-168-33-22-56 2.5.19.1053 GA]$

• 文件说明：
  • Cloudera_HiveJDBC41.zip支持JDK7或更高版本
  • Cloudera_HiveJDBC4.zip支持JDK6或更高版本
  • Cloudera-JDBC-Driver-for-Apache-Hive-Install-Guide.pdf说明文档

[ec2-user@ip-168-33-22-56 2.5.19.1053 GA]$ unzip Cloudera_HiveJDBC41.zip 
…
  inflating: commons-codec-1.3.jar   
  inflating: commons-logging-1.1.1.jar  
  inflating: HiveJDBC41.jar          
  inflating: hive_metastore.jar      
  inflating: hive_service.jar        
  inflating: httpclient-4.1.3.jar    
  inflating: httpcore-4.1.3.jar      
  inflating: libfb303-0.9.0.jar      
  inflating: libthrift-0.9.0.jar     
  inflating: log4j-1.2.14.jar        
  inflating: ql.jar                  
  inflating: slf4j-api-1.5.11.jar    
  inflating: slf4j-log4j12-1.5.11.jar  
  inflating: TCLIServiceClient.jar   
  inflating: zookeeper-3.4.6.jar     
[ec2-user@ip-168-33-22-56 2.5.19.1053 GA]$

• 将解压的所有jar包移动到/home/ec2-user/hive-test/hivedriver目录

[ec2-user@ip-168-33-22-56 2.5.19.1053 GA]$ mv *.jar /home/ec2-user/hivedriver

命令行测试

• 运行R控制台，安装依赖包

> install.packages("DBI")
> install.packages("RJDBC")
> install.packages("rJava")

• 编写R连接Hive测试代码

[ec2-user@ip-168-33-22-56 hive-test]$ vim TestHive.R 
library("DBI")
library("rJava")
library("RJDBC")

hive.class.path = list.files(path=c("/opt/cloudera/parcels/CDH/lib/hive/lib"),pattern="jar", full.names=T);

cp = c(hive.class.path)
.jinit(classpath=cp,parameters="-Djavax.security.auth.useSubjectCredsOnly=false")

drv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/home/ec2-user/hive-test/hivedriver/HiveJDBC41.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:hive2://ip-168-33-22-56.ap-southeast-1.compute.internal:10000")
show_databases <- dbGetQuery(conn, "show databases")
show_databases

tableCount <- dbGetQuery(conn, "select count(*) from tpcds_parquet.call_center")
tableCount

• 运行测试代码

[ec2-user@ip-168-33-22-56 hive-test]$ Rscript TestHive.R
Loading required package: methods
…
  database_name
1       default
2 tpcds_parquet
3    tpcds_text
  _c0
1  24
[ec2-user@ip-168-33-22-56 hive-test]$

• 编写R连接Impala测试代码

[ec2-user@ip-168-33-22-56 hive-test]$ vim TestImpala.R 
library("DBI")
library("rJava")
library("RJDBC")

impala.class.path = list.files(path=c("/home/ec2-user/hive-test/impaladriver"),pattern="jar", full.names=T)

cp = c(impala.class.path)
.jinit(classpath=cp,parameters="-Djavax.security.auth.useSubjectCredsOnly=false")

drv <- JDBC("com.cloudera.impala.jdbc41.Driver", "/home/ec2-user/hive-test/impaladriver/ImpalaJDBC41.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:impala://ip-168-33-22-103.ap-southeast-1.compute.internal:21050")
show_databases <- dbGetQuery(conn, "show databases")
show_databases

tableCount <- dbGetQuery(conn, "select count(*) from tpcds_parquet.call_center")
tableCount

• 运行测试代码

[ec2-user@ip-168-33-22-56 hive-test]$ Rscript TestImpala.R 
Loading required package: methods
              name                                      comment
1 _impala_builtins System database for Impala builtin functions
2          default                        Default Hive database
3    tpcds_parquet                                             
4       tpcds_text                                             
  expr_0
1     24
[ec2-user@ip-168-33-22-56 hive-test]$ 

CDSW测试

• 登录CDSW，挂载主机的目录到CDSW会话Session中
  • CDSW服务所在服务器的目录
  • 进入Admin->Engines，将driver目录挂载到CDSW上
• 登录CDSW创建一个R的工程
• 创建TestHive.r和TestImpala.r
• 在CDSW中安装依赖包

install.packages("DBI")
install.packages("RJDBC")
install.packages("rJava")

• 将命令行的测试代码复制到CDSW中的TestHive.r文件中，做相应修改

library("DBI")
library("rJava")
library("RJDBC")

hive.class.path = list.files(path=c("/opt/cloudera/parcels/CDH/lib/hive/lib"),pattern="jar", full.names=T)

cp = c(hive.class.path)
.jinit(classpath=cp,parameters="-Djavax.security.auth.useSubjectCredsOnly=false")

drv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/driver/hivedriver/HiveJDBC41.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:hive2://ip-168-33-22-56.ap-southeast-1.compute.internal:10000")
show_databases <- dbGetQuery(conn, "show databases")
show_databases

tableCount <- dbGetQuery(conn, "select count(*) from tpcds_parquet.call_center")
tableCount

• 创建TestImpala.r，将命令行代码拷贝至CDSW中，并做相应修改

library("DBI")
library("rJava")
library("RJDBC")

impala.class.path = list.files(path=c("/home/ec2-user/hive-test/impaladriver"),pattern="jar", full.names=T)

cp = c(impala.class.path)
.jinit(classpath=cp,parameters="-Djavax.security.auth.useSubjectCredsOnly=false")

drv <- JDBC("com.cloudera.impala.jdbc41.Driver", "/driver/impaladriver/ImpalaJDBC41.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:impala://ip-168-33-22-103.ap-southeast-1.compute.internal:21050")
show_databases <- dbGetQuery(conn, "show databases")
show_databases

tableCount <- dbGetQuery(conn, "select count(*) from tpcds_parquet.call_center")
tableCount

常见问题

• 安装rJava包的时候报错

cpp flags   : '-I/usr/java/jdk1.6.0_31/jre/../include -I/usr/java/jdk1.6.0_31/jre/../include/linux'
java libs   : '-L/usr/java/jdk1.6.0_31/jre/lib/amd64/server -ljvm'
configure: error: One or more Java configuration variables are not set.
Make sure R is configured with full Java support (including JDK). Run
R CMD javareconf
as root to add Java support to R.

If you don't have root privileges, run
R CMD javareconf -e
to set all Java-related variables and then install rJava.

ERROR: configuration failed for package ‘rJava’
* removing ‘/usr/local/R-3.4.2/lib64/R/library/rJava’

• 解决方法：
  • 使用root用户执行命令

[root@ip-168-33-22-56 ec2-user]# source /etc/profile
[root@ip-168-33-22-56 ec2-user]# R CMD javareconf   
…
JAVA_HOME        : /usr/java/jdk1.7.0_67-cloudera/jre
Java library path: $(JAVA_HOME)/lib/amd64/server
JNI cpp flags    : -I$(JAVA_HOME)/../include -I$(JAVA_HOME)/../include/linux
JNI linker flags : -L$(JAVA_HOME)/lib/amd64/server -ljvm
Updating Java configuration in /usr/local/R-3.4.2/lib64/R
Done.
[root@ip-168-33-22-56 ec2-user]# 

大数据视频推荐：
CSDN
大数据语音推荐：
企业级大数据技术应用
大数据机器学习案例之推荐系统
自然语言处理
大数据基础
人工智能：深度学习入门到精通