a) 准备测试数据
这里准备的csv文件data_test.csv,内容没用''包裹,逗号作为列分隔符
171301,燕青,男,27,发展部
171207,武松,男,39,开发部
171307,李逵,男,41,开发部
320812,宋江,男,45,战略部
321009,顾大嫂,女,38,后勤部
171312,卢俊义,男,43,发展部
b) hbase创建表
create ‘test’,{NAME=>'info',COMPRESSION => 'SNAPPY',DATA_BLOCK_ENCODING => 'FAST_DIFF'} ;
c) hive中创建表
create table tbl_csv_test
(
key String,
name string,
sex String,
age String,
department String
) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties
(
"separatorChar" = ",",
"escapeChar" = "\\"
)
STORED AS TEXTFILE;
这是hive创建对于格式表最全的写法，如下
CREATE TABLE csv_table(a string, b string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = "\t",
"quoteChar" = "'",
"escapeChar" = "\\"
)
STORED AS TEXTFILE;
d) 将csv文件中的数据导入到hive表
load data local inpath 'data_test.csv' into table tbl_csv_test;
e）创建外部表
CREATE EXTERNAL TABLE hive_hbase_external_table(
key String,
name string,
sex String,
age String,
department String
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:name,info:sex,info:age,info:department")
TBLPROPERTIES ("hbase.table.name" = "test","hbase.mapred.output.outputtable" = "test");
f) 将内部表表数据导入外部表
insert into hive_hbase_external_table select * from tbl_csv_test ;