用hbase做数据库,但由于hbase没有类sql查询方式,所以操作和计算数据非常不方便,于是整合hive,让hive支撑在hbase数据库层面 的hql查询.hive也即做数据仓库.
一.配置
1.将hbase/conf下面的hive-hbase-handler-0.11.0.jar hbase-0.94.7.jar zookeeper-3.4.5.jar hive-contrib-0.11.0.jar protobuf-java-2.4.0a.jar拷贝到hive/lib下面。
2.在hive-site.xml中添加
<property>
<name>hive.aux.jars.path</name> <value>file:///usr/local/hive/lib/hive-hbase-handler-0.11.0.jar,file:///usr/local/hive/lib/hbase-0.94.7.jar,file:///usr/local/hive/lib/zookeeper-3.4.5.jar,file:///usr/local/hive/lib/hive-contrib-0.11.0.jar,file:///usr/local/hive/lib/protobuf-java-2.4.0a.jar</value>
</property>
3.修该hive-env.sh
export HIVE_CONF_DIR=...
export HADOOP_HEAPSIZE=...
4.拷贝hbase-0.94.7.jar到所有节点的hadoop/lib下面
5.拷贝hbase-site.xml到所有节点的hadoop/conf下面
6.启动hbase
7.hive --auxpath /usr/local/hive/lib/hive-hbase-handler-0.11.0.jar,/usr/local/hive/lib/hbase-0.94.7.jar,/usr/local/hive/lib/zookeeper-3.4.5.jar-hiveconf hbase.zookeeper.quorum=slave1,slave2
/*********************************************************************/
hive -hiveconfhbase.zookeeper.quorum=slave1,slave2 集群启动方式
hive -hiveconfhbase.master=master:60000 单节点启动方式
/*********************************************************************/
二.实战:
HIVE
1.创建hbase识别的数据库
CREATE TABLE hbase_table_1(key int, value string)
STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key,cf1:val")
TBLPROPERTIES ("hbase.table.name" ="xyz");
hbase.table.name 定义在hbase的table名称(如果省略,则hbase中的表名和hive中的表名一样)
hbase.columns.mapping 定义在hbase的列族
2.使用sql导入数据
CREATE TABLE pokes (foo INT, bar STRING);
hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt'OVERWRITE INTO TABLE pokes;
(这个文件位于hive的安装目录下,examples/files/kv1.txt)
INSERT OVERWRITE TABLE hbase_table_1 SELECT * FROM pokesWHERE foo=86;
3查看数据
hive> select * from hbase_table_1;
hbase
1.登录hbase
[root@master hbase]# ./bin/hbase shell
2.查看表结构
hbase(main):001:0> describe 'xyz'
DESCRIPTION ENABLED
{NAME => 'xyz', FAMILIES => [{NAME => 'cf1',COMPRESSION => 'NONE', VE true
RSIONS => '3', TTL => '2147483647', BLOCKSIZE =>'65536', IN_MEMORY =>
'false', BLOCKCACHE => 'true'}]}
1 row(s) in 0.7460 seconds
3.查看加载的数据
hbase(main):002:0> scan 'xyz'
ROW COLUMN+CELL
86 column=cf1:val, timestamp=1297690405634, value=val_86
可以看到,在hive中添加的数据86,已经在hbase中了
4.添加数据
' hbase(main):008:0> put'xyz','100','cf1:val','www.360buy.com'
0 row(s) in 0.0630seconds
Hive
参看hive中的数据
hive> select * from hbase_table_1;
OK
100 www.360buy.com
86 val_86
Time taken: 8.661 seconds
刚刚在hbase中插入的数据,已经在hive里了
hive访问已经存在的hbase
使用CREATE EXTERNAL TABLE
CREATE EXTERNAL TABLE hbase_table_2(key int, value string)
STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" ="cf1:val")
TBLPROPERTIES("hbase.table.name" ="some_existing_table");
例如:
CREATE EXTERNAL TABLE hbase_table_3(key int, value1 int,value2 string)
STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" ="stuinfo:age,stuinfo:name")
TBLPROPERTIES("hbase.table.name"= "students");
多列和多列族(Multiple Columns and Families)
1.创建数据库
CREATE TABLE hbase_table_2(key int, value1 string, value2 int,value3 int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" =":key,a:b,a:c,d:e"
);
2.插入数据
INSERT OVERWRITE TABLE hbase_table_2 SELECT foo, bar,foo+1, foo+2
FROM pokes WHERE foo=98 OR foo=100;
这个有3个hive的列(value1和value2,value3),2个hbase的列族(a,d)
Hive的2列(value1和value2)对应1个hbase的列族(a,在hbase的列名称b,c),hive的另外1列(value3)对应列(e)位于列族(d)
3.登录hbase查看结构
hbase(main):003:0> describe "hbase_table_2"
DESCRIPTION ENABLED
{NAME => 'hbase_table_2', FAMILIES => [{NAME =>'a', COMPRESSION => 'N true
ONE', VERSIONS => '3', TTL => '2147483647', BLOCKSIZE=> '65536', IN_M
EMORY => 'false', BLOCKCACHE => 'true'}, {NAME =>'d', COMPRESSION =>
'NONE', VERSIONS => '3', TTL => '2147483647',BLOCKSIZE => '65536', IN
_MEMORY => 'false', BLOCKCACHE => 'true'}]}
1 row(s) in 1.0630 seconds
4.查看hbase的数据
hbase(main):004:0> scan 'hbase_table_2'
ROW COLUMN+CELL
100 column=a:b, timestamp=1297695262015, value=val_100
100 column=a:c, timestamp=1297695262015, value=101
100 column=d:e, timestamp=1297695262015, value=102
98 column=a:b, timestamp=1297695242675, value=val_98
98 column=a:c, timestamp=1297695242675, value=99
98 column=d:e, timestamp=1297695242675, value=100
2 row(s) in 0.0380 seconds
5.在hive中查看
hive> select * from hbase_table_2;
OK
100 val_100 101 102
98 val_98 99 100
Time taken: 3.238 seconds