大数据Spark “蘑菇云”行动第90课：Hive中Join电影店铺系统案例和性能优化、Index和Bucket案例实战

大数据Spark “蘑菇云”行动第90课：Hive中Join电影店铺系统案例和性能优化、Index和Bucket案例实战


电影数据集下载
http://grouplens.org/datasets/movielens/
http://grouplens.org/datasets/movielens/1m/
http://files.grouplens.org/datasets/movielens/ml-1m-README.txt
http://files.grouplens.org/datasets/movielens/ml-1m.zip


1,hive的map join
小表 -> hashtables ->广播到 map端 ->大表做一行行的匹配
2,semi join  传递表的key传过去,


三个表的数据结构


RATINGS FILE DESCRIPTION     UserID::MovieID::Rating::Timestamp
USERS FILE DESCRIPTION       UserID::Gender::Age::Occupation::Zip-code
MOVIES FILE DESCRIPTION      MovieID::Title::Genres


hive> create database hivestudy
show databases;
use hivestudy;
show tables;








建立用户表://不支持LONG
CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zip-code String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS 


TERMINATED BY '::';


LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/users.dat' INTO TABLE users PARTITION(dt='20161101');
建立评分表:
CREATE TABLE ratings(UserID BigInt, MovieID BigInt, Rating Double, Timestamped String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '::';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/ratings.dat' INTO TABLE ratings;


建立电影表
CREATE TABLE movies(MovieID BigInt, Title String, Genres String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '::';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/movies.dat' INTO TABLE movies;






DROP TABLE users;
CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS 


TERMINATED BY '::';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/users.dat' INTO TABLE users PARTITION(dt='20161101');
CREATE TABLE ratings(UserID BigInt, MovieID BigInt, Rating Double, Timestamped String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '::';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/ratings.dat' INTO TABLE ratings;
CREATE TABLE movies(MovieID BigInt, Title String, Genres String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '::';
LOAD DATA LOCAL INPATH '/root/Documents/data/moviesData/movies.dat' INTO TABLE movies;






hive>select count(1) from ratings;




RATINGS     UserID::MovieID::Rating::Timestamp
USERS       UserID::Gender::Age::Occupation::Zip-code
MOVIES      MovieID::Title::Genres
 
hive>  SELECT  users.UserID, users.Age, users.Gender FROM ratings JOIN users ON (ratings.UserID = users.UserID) WHERE ratins.MovieID = 2116 ;
空结果,原因是数据没有导入进来
select * from users limit 10; //null


删除表,重来,
drop tables  users;


转义字符,转不了
CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS 


TERMINATED BY '\:\:';


用ascii编码:
CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS 


TERMINATED BY '\u3A\u3A';


CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS 


TERMINATED BY '\\58';


数据清洗换个办法:将::用\t 换表符替换掉!
现在好了 


CREATE TABLE users(UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String) PARTITIONED BY (dt String) ROW FORMAT DELIMITED FIELDS 


TERMINATED BY '\t';




重新测试join
RATINGS     UserID::MovieID::Rating::Timestamp
USERS       UserID::Gender::Age::Occupation::Zip-code
MOVIES      MovieID::Title::Genres
 


hive>  SELECT  users.UserID, users.Age, users.Gender FROM ratings JOIN users ON (ratings.UserID = users.UserID) WHERE ratins.MovieID = 2116 ;


hive> SELECT  users.UserID, users.Age, users.Gender,movies.title,movies.gendre  FROM ratings JOIN users ON (ratings.UserID = users.UserID) JOIN movies 


ON (rating.movie.id = movies .movid) WHERE ratins.MovieID = 2116 ;


HIVE很诡异的一个用法,map端 速度快一点

hive>SELECT  /*+MAP JOIN(MOVIES)*/ users.UserID, users.Age, users.Gender,movies.title,movies.gendre  FROM ratings JOIN users ON (ratings.UserID = users.UserID) JOIN movies ON (rating.movie.id = movies .movid) WHERE ratins.MovieID = 2116 ;

MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多


这样就不会由于数据倾斜导致某个reduce上落数据太多而失败。于是原来的sql可以通过使用hint的方式指定join时使用mapjoin。