1.Hive内部表,语句如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE ods.s01_buyer_calllogs_info_ts( key string comment "hbase rowkey" , buyer_mobile string comment "手机号" , contact_mobile string comment "对方手机号" , call_date string comment "发生时间" , call_type string comment "通话类型" , init_type string comment "0-被叫,1-主叫" , other_cell_phone string comment "对方手机号" , place string comment "呼叫发生地" , start_time string comment "发生时间" , subtotal string comment "通话费用" , use_time string comment "通话时间(秒)" ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,record:buyer_mobile,record:contact_mobile,record:call_date,record:call_type,record:init_type,record:other_cell_phone,record:place,record:start_time,record:subtotal,record:use_time" ) TBLPROPERTIES( "hbase.table.name" = "s01_buyer_calllogs_info_ts" ); |
建好表之后,进入hbase shell执行list能看到表s01_buyer_calllogs_info_ts,hive drop掉此表时,hbase也被drop。
2.Hive外部表,语句如下,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | create 'buyer_calllogs_info_ts' , 'record' , {SPLITS_FILE => 'hbase_calllogs_splits.txt' } CREATE EXTERNAL TABLE ods.s10_buyer_calllogs_info_ts( key string comment "hbase rowkey" , buyer_mobile string comment "手机号" , contact_mobile string comment "对方手机号" , call_date string comment "发生时间" , call_type string comment "通话类型" , init_type string comment "0-被叫,1-主叫" , other_cell_phone string comment "对方手机号" , place string comment "呼叫发生地" , start_time string comment "发生时间" , subtotal string comment "通话费用" , use_time string comment "通话时间(秒)" ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,record:buyer_mobile,record:contact_mobile,record:call_date,record:call_type,record:init_type,record:other_cell_phone,record:place,record:start_time,record:subtotal,record:use_time" ) TBLPROPERTIES( "hbase.table.name" = "buyer_calllogs_info_ts" ); |
从方式需要先在hbase建好表,然后在hive中建表,hive drop掉表,hbase表不会变。
3.Hive映射HBase的列族
CREATE EXTERNAL TABLE hiveFromHbase(
rowkey string,
f1 map<STRING,STRING>,
f2 map<STRING,STRING>,
f3 map<STRING,STRING>
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:,f2:,f3:")
TBLPROPERTIES ("hbase.table.name" = "hbase_test");
hive> select * from hiveFromHbase;
OK
r1 {"name":"zhangsan"} {"age":"20"} {"sex":"male"}
r2 {"name":"lisi"} {"age":"30"} {"sex":"female"}
r3 {"name":"wangwu"} {"age":"40"} {"sex":"male"}
Time taken: 0.158 seconds, Fetched: 3 row(s)