Hive与Hbase整合

Hive与Hbase整合

我们这边开始使用hbase做实时查询，但是分析的任务还是得交给hive,hive计算的结果导入到hbase.
hive提供了几个jar包，帮助我们实现：

• 创建与hbase共享的表，数据（数据和表两边都有)
• 映射来自hbase的表到hive
• hive查询的结果直接导入hbase

启动hive

启动命令如下，主要是指定jar包，以及hbase使用的zookeeper的地址

bin/hive --auxpath /opt/CDH/hive/lib/hive-hbase-handler-0.10.0-cdh4.3.2.jar,/opt/CDH/hive/lib/hbase-0.94.6-cdh4.3.2.jar,/opt/CDH/hive/lib/zookeeper-3.4.5-cdh4.3.2.jar,/opt/CDH/hive/lib/guava-11.0.2.jar -hiveconf hbase.zookeeper.quorum=192.168.253.119,192.168.253.130

测试表

我们先在hive中创建测试表:

//create hive tmp table
CREATE TABLE pokes (foo INT, bar STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

//test.txt数据格式：1  hello
//插入数据到hive表
LOAD DATA  INPATH '/user/mapred/test.txt' OVERWRITE INTO TABLE pokes;

创建hive-hbase表

在hive中创建表时，制定映射到对应的hbase表，默认两边的表名字一样。

//create table share with hbase
hive> CREATE TABLE hbase_hive_table(key int, value string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val") ;

切换到hbase shell,查看一下表是否存在：

hbase(main):007:0> describe 'hbase_hive_table'
DESCRIPTION                                                        ENABLED                            
 {NAME => 'hbase_hive_table', FAMILIES => [{NAME => 'cf1', DATA_BL true                               
 OCK_ENCODING => 'NONE', BLOOMFILTER => 'NONE', REPLICATION_SCOPE                                     
 => '0', VERSIONS => '3', COMPRESSION => 'NONE', MIN_VERSIONS => '                                    
 0', TTL => '2147483647', KEEP_DELETED_CELLS => 'false', BLOCKSIZE                                    
  => '65536', IN_MEMORY => 'false', ENCODE_ON_DISK => 'true', BLOC                                    
 KCACHE => 'true'}]}                                                                                  
1 row(s) in 0.0800 seconds

写数据测试

//insert test
hive> INSERT OVERWRITE TABLE hbase_hive_table SELECT * FROM pokes WHERE foo=1;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201407241659_0007, Tracking URL = http://centos149:50030/jobdetails.jsp?jobid=job_201407241659_0007
Kill Command = /opt/CDH/hadoop/share/hadoop/mapreduce1/bin/hadoop job  -kill job_201407241659_0007
Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0
2014-08-07 16:15:14,505 Stage-0 map = 0%,  reduce = 0%
2014-08-07 16:15:20,010 Stage-0 map = 100%,  reduce = 0%, Cumulative CPU 2.46 sec
2014-08-07 16:15:21,087 Stage-0 map = 100%,  reduce = 0%, Cumulative CPU 2.46 sec
2014-08-07 16:15:22,190 Stage-0 map = 100%,  reduce = 0%, Cumulative CPU 2.46 sec
2014-08-07 16:15:23,200 Stage-0 map = 100%,  reduce = 100%, Cumulative CPU 2.46 sec
MapReduce Total cumulative CPU time: 2 seconds 460 msec
Ended Job = job_201407241659_0007
1 Rows loaded to hbase_hive_table
MapReduce Jobs Launched: 
Job 0: Map: 1   Cumulative CPU: 2.46 sec   HDFS Read: 196 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 460 msec
OK
Time taken: 34.594 seconds

我们切换到hbase shell,查看一下表是否已经写入信息：

hbase(main):005:0> scan 'hbase_hive_table'
ROW                        COLUMN+CELL                                                                
 1                         column=cf1:val, timestamp=1407399353262, value=hello

如果想要提高写入hbase表的速度，可以添加如下设置，关闭wal预写日志

//hbase write maybe slow, because of  wal, so set to false
set hive.hbase.wal.enabled=false;

Reference

https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration