i2fMxpPQ

1CREATE TABLE ratings_all_hive (userid int, age int, gender string, occupation int, zip string, rating double, rating_time timestamp, movieid int, title string, year int, genres string) 
2COMMENT 'data loaded with serde org.apache.hadoop.hive.serde2.OpenCSVSerde' 
3ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
4WITH SERDEPROPERTIES ( "separatorChar" = "\,", "quoteChar" = "\"") 
5STORED AS TEXTFILE tblproperties("skip.header.line.count"="1");
6
7LOAD DATA INPATH  '/tmp/ratings-all.csv' OVERWRITE into table ratings_all_hive;
8
9create table ratings_all_impala as select cast (userid as int) As userid, cast (age as int) As age, gender, cast (occupation as int) As occupation, zip, cast (rating as double) As rating, rating_time, cast (movieid as int) As movieid, title, genre, cast (year as int) As year from ratings_all_hive 
10
11
12CREATE VIEW IF NOT EXISTS viewforhbase (rowkey, rating, datetime, user, movie) AS
13   SELECT concat_ws('-',concat("",movieid),concat("",userid)),rating,rating_time,map("userid",userid,"age",age,"zip",zip,"occupation",occupation,"gender",gender),map("movieid",movieid,"title",title,"year",year,"genres",genres)
14   FROM ratings_all_impala;
15   
16CREATE TABLE IF NOT EXISTS ratings_hbase (rowkey STRING, rating double, datetime timestamp, user map<string,string>, movie map<string,string>)
17STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
18WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key,rating:rating,rating:datetime,user:,movie:')
19TBLPROPERTIES ('hbase.table.name' = 'ratings_all');