在hbase中創(chuàng)建表后,我們只能在hbase shell中使用scan查詢數據,這對于熟悉SQL的使用者不怎么習慣,不過我們可以在hive中創(chuàng)建外部表來訪問hbase表中的數據,例子如下:1.這里hbase中的表oss_user_label_action_data已經存在=> #<#:0xd5a1b0>hbase(main):067:0> scan 'oss_user_label_action_data',LIMIT=>1ROW 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 seconds2.創(chuàng)建外部表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中的,我們可以創(chuàng)建另外一個hive中的表,將hbase中的數據加載到hive本地創(chuàng)建另外一個表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 DELIMITEDFIELDS 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 --
