通过hive访问hbase中的表详解大数据
1.这里hbase中的表oss_user_label_action_data已经存在
= # #:0xd5a1b0
hbase(main):067:0 scan oss_user_label_action_data ,LIMIT= 1
ROW COLUMN+CELL
201407|31130101|8613500000001 column=info:areacode, timestamp=1415243857802, value=22
201407|31130101|8613500000001 column=info:cardtype, timestamp=1415243857802, value=1
201407|31130101|8613500000001 column=info:createtime, timestamp=1415243857802, value=20141028 11:18:34
201407|31130101|8613500000001 column=info:enable_flag, timestamp=1415243857802, value=0
201407|31130101|8613500000001 column=info:extstring, timestamp=1415243857802, value=
201407|31130101|8613500000001 column=info:labelno, timestamp=1415243857802, value=31130101
201407|31130101|8613500000001 column=info:labelvalue, timestamp=1415243857802, value=9693
201407|31130101|8613500000001 column=info:modifytime, timestamp=1415243857802, value=20141028 11:18:45
201407|31130101|8613500000001 column=info:monthno, timestamp=1415243857802, value=201407
201407|31130101|8613500000001 column=info:provcode, timestamp=1415243857802, value=1
201407|31130101|8613500000001 column=info:usernumber, timestamp=1415243857802, value=8613500000001
1 row(s) in 0.0580 seconds
2.创建外部表
CREATE EXTERNAL TABLE hive_oss_user_label_action_data(
key string,
monthno string,
usernumber string,
labelno string,
labelvalue string,
provcode string,
areacode string,
cardtype string,
extstring string,
createtime string,
modifytime string
)
STORED BY org.apache.hadoop.hive.hbase.HBaseStorageHandler
WITH SERDEPROPERTIES
( hbase.columns.mapping =
:key,info:monthno,info:usernumber,info:labelno,info:labelvalue,info:provcode,info:areacode,info:cardtype,info:extstring,info:createtime,info:modifytime )
TBLPROPERTIES( hbase.table.name = oss_user_label_action_data );
注意hbase.columns.mapping后面的字段直接不能出现空格和换行.
3.通过hive查询数据
根据rowkey查询
select * from hive_oss_user_label_action_data where key= 201407|31130101|8613500000001
根据某个字段查询
select * from hive_oss_user_label_action_data where usernumber= 8613500000001
组合查询
select * from hive_oss_user_label_action_data where usernumber= 8613500000001 and labelno= 31130101
说明:
这里我们访问的hive_oss_user_label_action_data表是虚表,数据是存储在hbase中的,我们可以创建另外一个hive中的表,
将hbase中的数据加载到hive本地
创建另外一个表
CREATE TABLE hive_oss_user_label_action_data_local(
key string,
monthno string,
usernumber string,
labelno string,
labelvalue string,
provcode string,
areacode string,
cardtype string,
extstring string,
createtime string,
modifytime string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY /t
STORED AS TEXTFILE;
将hbase中的表数据加载到本地表
INSERT OVERWRITE TABLE hive_oss_user_label_action_data_local SELECT * FROM hive_oss_user_label_action_data;
The End
转载出处:http://blog.chinaunix.net/uid-77311-id-4616135.html