hbase與hive整合
1. hive中有數據 --> 創建hive管理表映射hbase
例如:
1)hive創建內部表
create table course.hbase_score(id int,cname string,score int)
stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
with serdeproperties("hbase.columns.mapping" = "cf:name,cf:score")
tblproperties("hbase.table.name" = "hbase_score");
2)插入數據
insert overwrite table course.hbase_score select id,cname,score from course.score;
2. habse中有數據 --> 創建hive外部表映射hbase
例如:
CREATE external TABLE course.hbase2hive(id int, name string, score int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf:name,cf:score")
TBLPROPERTIES("hbase.table.name" ="hbase_hive_score");
hbase與sqoop整合
1. mysql中表數據導入hbase中
例如:
bin/sqoop import \
--connect jdbc:mysql://192.168.1.5:3306/library \
--username root \
--password root \
--table book \
--columns "id,name,price" \
--column-family "info" \
--hbase-create-table \
--hbase-row-key "id" \
--hbase-table "hbase_book" \
--num-mappers 1 \
--split-by id
2. hbase中的數據導入mysql中
ps:
sqoop不支持直接將hbase中的數據導出,我們可以通過hive關聯兩者:
hbase -> hive外部表 -> hive內部表 -> 通過sqoop -> mysql
例如:
1)創建hive外部表映射hbase中的表數據
CREATE EXTERNAL TABLE course.hbase2mysql (id int,name string,price int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" =
":key,info:name, info:price"
)
TBLPROPERTIES( "hbase.table.name" = "hbase_book",
"hbase.mapred.output.outputtable" = "hbase2mysql");
2)將hive外部表數據插入到hive內部表中
CREATE TABLE course.hbase2mysqlin(id int,name string,price int);
3)通過sqoop將hive內部表數據導到mysql中
sqoop export -connect jdbc:mysql://192.168.43.98:3306/library -username root
-password root -table book -export-dir /user/hive/warehouse/course.db/hbase2mysqlin
--input-fields-terminated-by '\001' --input-null-string '\\N'
--input-null-non-string '\\N';