da数据HIve写入映射流程

数据入库命令【命令】
2.1
create database video;

create table video_ori(
videoId string,
uploader string,
age string,
category string,
length string,
views string,
rate string,
ratings string,
comments string,
relatedId string)
row format delimited
fields terminated by “:”
stored as textfile;

create table video_user_ori(
uploader string,
videos string,
friends string)
row format delimited
fields terminated by “,”
stored as textfile;

create table video_orc(
videoId string,
uploader string,
age string,
category string,
length string,
views string,
rate string,
ratings string,
comments string,
relatedId string)
row format delimited
fields terminated by “:”
stored as ORC;

create table video_user_orc(
uploader string,
videos string,
friends string)
row format delimited
fields terminated by “,”
stored as ORC;

2.2
load data local inpath ‘/opt/part-r-00000’ ovewrite into table video_ori;

load data local inpath ‘/opt/user.txt’ into table video_user_ori;

2.3
insert into table video_orc select * from video_ori;

insert into table video_user_orc select * from video_user_ori;

3.数据的分析阶段
3.1
hive -e "select * from video.video_orc where rate=5 " > 5.txt
3.2
hive -e "select * from video.video_orc where comments >100 " > 100.txt

4.数据保存到数据库阶段
4.1建表语句
创建rate外部表的语句：
create external table rate(
videoId string,
uploader string,
age string,
category string,
length string,
views string,
rate string,
ratings string,
comments string,
relatedId string)
row format delimited
fields terminated by “\t”
stored as textfile;

创建comments外部表的语句：
create external table comments(
videoId string,
uploader string,
age string,
category string,
length string,
views string,
rate string,
ratings string,
comments string,
relatedId string)
row format delimited
fields terminated by “\t”
stored as textfile;
4.2
数据加载语句
load data local inpath ‘/opt/5.txt’ into table rate;
load data local inpath ‘/opt/100.txt’ into table comments;
4.3
创建hive hbase映射表
create table video.hbase_rate(
videoId string,
uploader string,
age string,
category string,
length string,
views string,
rate string,
ratings string,
comments string,
relatedId string)
stored by ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
with serdeproperties(“hbase.columns.mapping” = “cf:uploader,cf:age,cf:category,cf:length,cf:views,cf:rate,cf:ratings,cf:comments,cf:relatedId”)
tblproperties(“hbase.table.name” = “hbase_rate”);

create table video.hbase_comments(
videoId string,
uploader string,
age string,
category string,
length string,
views string,
rate string,
ratings string,
comments string,
relatedId string)
stored by ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
with serdeproperties(“hbase.columns.mapping” = “cf:uploader,cf:age,cf:category,cf:length,cf:views,cf:rate,cf:ratings,cf:comments,cf:relatedId”)
tblproperties(“hbase.table.name” = “hbase_comments”);

4.4
插入数据
insert into table hbase_rate select * from rate;

insert into table hbase_comments select * from comments;