练习

2、把预处理之后的数据进行入库到hive中
2.1创建数据库和表
创建数据库名字为：video
创建原始数据表：
视频表：video_ori 用户表：video_user_ori
创建ORC格式的表：
视频表：video_orc 用户表：video_user_orc
给出创建原始表语句
创建video_ori视频表：
create table video_ori(
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
row format delimited
fields terminated by “:”
collection items terminated by “,”
stored as textfile;
创建video_user_ori用户表：
create table video_user_ori(
uploader string,
videos int,
friends int)
row format delimited
fields terminated by “,”
stored as textfile;
请写出ORC格式的建表语句：
创建video_orc表
create table video_orc(
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
row format delimited
fields terminated by “:”
collection items terminated by “,”
stored as orcfile;

创建video_user_orc表：
create table video_user_orc(
uploader string,
videos int,
friends int)
row format delimited
fields terminated by “,”
stored as orcfile;

2.2分别导入预处理之后的视频数据到原始表video_ori和导入原始用户表的数据到video_user_ori中
请写出导入语句：
video_ori：

video_user_ori：

2.3从原始表查询数据并插入对应的ORC表中
请写出插入语句：
video_orc：
insert into video.video_orc(videoid, uploader, age, category, length, views, rate, ratings, comments, relatedid) SELECT t.* FROM video.video_ori t;

video_user_orc：
insert into video.video_user_orc(uploader, videos, friends) SELECT * from video.video_user_ori;

3、对入库之后的数据进行hivesql查询操作
3.1从视频表中统计出视频评分为5分的视频信息，把查询结果保存到/export/rate.txt
请写出sql语句：

insert overwrite local directory ‘/export/rate.txt’ row format delimited fields terminated by ‘:’ collection items terminated by ‘,’ select * from video.video_orc where rate = 5;

3.2从视频表中统计出评论数大于100条的视频信息,把查询结果保存到/export/comments.txt
请写出sql语句：

insert overwrite local directory ‘/export/comments.txt’ row format delimited fields terminated by ‘:’ collection items terminated by ‘,’ select * from video.video_orc where comments>100;

4、把hive分析出的数据保存到hbase中
4.1创建hive对应的数据库外部表
请写出创建rate外部表的语句：
create external table rate(
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
row format delimited
fields terminated by “:”
collection items terminated by “,”;

请写出创建comments外部表的语句：
create external table comments(
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
row format delimited
fields terminated by “:”
collection items terminated by “,”;

4.2加载第3步的结果数据到外部表中
请写出加载语句到rate表：
load data local inpath ‘/export/rate.txt’ into table video.rate;

请写出加载语句到comments表：
load data local inpath ‘/export/comments.txt’ into table video.comments;

4.3创建hive管理表与HBase进行映射
给出此步骤的语句
Hive中的rate，comments两个表分别对应hbase中的hbase_rate，hbase_comments两个表
创建hbase_rate表并进行映射：
create table hbase_rate2( rowkey STRING,
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
stored by ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
with serdeproperties(“hbase.columns.mapping” = “cf:videoId,cf:uploader,cf:age,cf:category,cf:length,cf:views,cf:rate,cf:ratings,cf:comments,cf:relatedId”)
tblproperties(“hbase.table.name” = “hbase_rate2”);

创建hbase_comments表并进行映射：
create table hbase_comments( rowkey STRING,
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
stored by ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
with serdeproperties(“hbase.columns.mapping” = “cf:videoId,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 overwrite select，插入hbase_rate表的语句
insert overwrite table hbase_rate2 select row_number() over (),rate.* from rate;

请写出通过insert overwrite select，插入hbase_comments表的语句
insert overwrite table hbase_comments select row_number() over (),comments.* from comments;