hbase+hive结合使用

 

 

1 hbase到hive的使用:

 

相当于从hive上创建一个外表，快捷引用到hbase表，这么做能利用上hive的sql查询能力，方便查询hbase数据。

 

a) hbase准备表和数据:

 

create "test","base","cf","data"
put "test","key1","base:name","baseName1"
put "test","key2","base:name","baseName2"
put "test","key3","base:name","baseName3"
put "test","key1","base:age","baseAge1"
put "test","key2","base:age","baseAge2"
put "test","key3","base:age","baseAge3"

put "test","key1","data:name","dataName1"
put "test","key2","data:name","dataName2"
put "test","key3","data:name","dataName3"
put "test","key1","data:age","dataAge1"
put "test","key2","data:age","dataAge2"
put "test","key3","data:age","dataAge3"

put "test","key1","cf:name","cfName1"
put "test","key2","cf:name","cfName2"
put "test","key3","cf:name","cfName3"
put "test","key1","cf:age","cfAge1"
put "test","key2","cf:age","cfAge2"
put "test","key3","cf:age","cfAge3"

 

 

准备的表逻辑结构:

 

b) 创建hbase映射的hive表:

 

 语法如下：

create external table  hbase_hive_1(key String, value String)   指定hive表和列
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties   指定hive到hbase应用的类
("hbase.columns.mapping"="info:des,info:area")  指定hbase的列，  其中hbase的key即使不写也会被hive识别到，如果写写法为 ("hbase.columns.mapping"=":key,info:des,info:area")   
tblproperties("hbase.table.name"="f_stu")   指定hbase的表

 

   这就是hive的外表，快捷链接，hive和hbase映射涉及到的hive/lib/hive-hbase-handler-0.14.0.jar这个包

 

hbase-hive仅仅是一个映射，如果hbase数据变更，那么hive这个映射的数据也会变更， 这个做过测试。

 

这样，就能依靠hbase来 新增/修改单条记录， 然后利用hive这个外表来实现hbase数据统计(完美结合)

 

 对上述hbase表增加hive外表写法:

create external table  hbase_hive_3(key String, basename String, baseage String, dataname string, dataage string)     
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties   
("hbase.columns.mapping"="base:name,base:age,data:name,data:age")  
tblproperties("hbase.table.name"="test") 

 

hive查询结果:

hive (default)> select * from hbase_hive_3 ;       
OK
hbase_hive_3.key        hbase_hive_3.basename   hbase_hive_3.baseage    hbase_hive_3.dataname hbase_hive_3.dataage
key1    baseName111     baseAge1        dataName1       dataAge1
key2    baseName2       baseAge2        dataName2       dataAge2
key3    baseName3       baseAge3        dataName3       dataAge3

 

 

2 hbase到hive的使用:

 

 

a)  stu数据; 以\t间隔

1	zhangsan
2	lisi
3	wangwu

 

b) hive 准备原表数据:

hive>create table stu(id string,name string) row format delimited fields terminated by '\t';
hive>load data local inpath '/opt/stu' into table stu;

 

c) hbase 创建空表，用于装载hive原表数据:

 

hbase>create 'stu', 'base'

 

d) hive创建hbase目标表的映射表：

hive>create external table stu_hbase(key string, name string) 
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
with serdeproperties ("hbase.columns.mapping" = ":key,base:name") 
tblproperties ("hbase.table.name" = "stu");

 

e) 将hive原表数据拷贝一份到 hive-hbase映射表:

hive>insert overwrite table stu_hbase select * from stu;   此时关联hbase的表hbase_table就有数据了。

 

f) 检测hbase 表数据：

hbase(main):009:0> scan 'stu'
ROW                      COLUMN+CELL
 1                       column=base:name, timestamp=1456725485282, value=zhangsan
 2                       column=base:name, timestamp=1456725485282, value=lisi
 3                       column=base:name, timestamp=1456725485282, value=wangwu

此时stu里面有数据了。

 

 感觉所谓的hive到hbase，还是在hive中创建了一个hbase的外表，只不过此时hbase表没有数据,然后将hive中创建的这个habse外表数据装载hive原来表的数据而已。

 

3 总结:

      目前，在工作中，只遇到过 以hbase表为基础，创建hive外表关联的场景，目的是给数据库的人员查询使用。