hive操作hbase

hive操作hbase

1.  启动hadoop

./start-all.sh

2.  启动hbase

./start-hbase.sh

3.  修改hive的配置文件

cd /home/hadoop/hive/conf

vi hive-site.xml

添加内容：(辅助jar包),zookeeper地址

<property>    <name>hive.aux.jars.path</name>  <value>file:///home/hadoop/hive/lib/hive-hbase-handler-0.9.0.jar,file:///home/hadoop/hive/lib/hbase-0.92.0.jar,file:///home/hadoop/hive/lib/zookeeper-3.4.3.jar</value>       </property> <property>     <name>hbase.zookeeper.quorum</name>     <value>slave1,slave2,slave3</value>   </property>

 

4.  启动hive

hive  -hiveconf hbase.zookeeper.quorum=slave1,slave2,slave3

 

这里，-hiveconf hbase.zookeeper.quorum=slave1,slave2,slave3是指向zookeeper

5.  建立关联表

5.1.  创建表语句

这里我们要查询的表在hbase中已经存在，所以，使用CREATE EXTERNAL TABLE来建立，如下：

单列：

CREATE EXTERNAL TABLE hbase_table_2(key string,value int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = "content:info") TBLPROPERTIES ("hbase.table.name" = "stat");

多列：

CREATE EXTERNAL TABLE hbase_table_3(key string,notename string,createtime string,status string,content string) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = "ni:nn,ni:ct,ni:st,ci:c") TBLPROPERTIES ("hbase.table.name" = "n");

 

参数说明：

 hbase.columns.mapping指向对应的列族；

多列时，content:info，content:info2；

多列族时，content1:info,content2:info；

 hbase.table.name指向对应的表；

 hbase_table(key string, value string)，这个是关联表

5.2.  对应的hbase中的结构

HBase中要查询的表的结构，

hbase(main):001:0> describe 'test'                               

{NAME => 'stat', FAMILIES => [{NAME => 'content',DATA_BLOCK_ENCODING => 'NON true                                     

 E',BLOOMFILTER => 'NONE', REPLICATION_SCOPE => '0', VERSIONS => '3',COMPRES                                           

 SION=> 'NONE', MIN_VERSIONS => '0', TTL => '2147483647',KEEP_DELETED_CELLS                                           

 => 'false', BLOCKSIZE => '65536',IN_MEMORY => 'false', ENCODE_ON_DISK => 'tr                                           

 ue',BLOCKCACHE => 'true'}]}  

5.3.  表中的数据

hbase_table(key string, value string)中的key对应的stat表中的row，value字段对应的是stat表中的value

hbase(main):008:0> scan 'stat' ROW                             COLUMN+CELL                                                                                Apache                         column=content:info, timestamp=1433791930042, value=1                                     Hadoop                         column=content:info, timestamp=1433791930042, value=5                                      The                            column=content:info, timestamp=1433791930042, value=2                                     a                              column=content:info, timestamp=1433791930042, value=1

 

6.  查询

hive> select * from hbase_table_2; 

7.  在hbase中新增

在hbase中新增数据，hive中会实时的查询到。

8.  java代码操作

8.1.   启动hive的server端

./hive --service hiveserver

8.2.  导包

将hive下的包导入

8.3.  代码

package com.itcast.hive.jdbc;   import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;   public class HiveJDBC {            /**           * @param args           */          public static void main(String[] args) {                    try {                             Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");                             Connection con = DriverManager.getConnection("jdbc:hive://master:10000/default","","");                             Statement stmt = con.createStatement();                             String querySQL="select * from hbase_table_2";                             ResultSet res = stmt.executeQuery(querySQL);                              while (res.next()) {                             System.out.println(res.getString(1)+"\t");                             }                    } catch (ClassNotFoundException e) {                             // TODO Auto-generated catch block                             e.printStackTrace();                    } catch (SQLException e) {                             // TODO Auto-generated catch block                             e.printStackTrace();                    }          }   }