Hive commands notes

hive> insert overwrite directory '/result' select * from userbehavior;

hive> insert overwrite local directory '/hive_test/result' select * from userbehavior;

hive> create table userbehavior as select /*+mapjoin(r)*/ r.userid,m.movieid,m.title,r.rating from movie m join rate r on m.movieid=r.movieid limit 10;

hive> set hive.groupby.skewindata = true;

hive> set hive.enforce.bucketing = true;开启bucket功能

#create bucket

hive> create external table rating_table
(
userid int,
movieid string,
rating string
)
clustered by (userid) into 32 buckets;

hive> create external table rating_table
(
userid string,
movieid string,
rating string
)
partitioned by (dt string)
row format delimited fields terminated by '\t'
lines terminated by '\n';

#UDF

hive> add jar /hive_test/hive_udf.jar;
hive> create temporary function upper_func as 'test.Uppercase';
hive> select movieid, title, upper_func(title) from movie_table;

#UDTF

hive> add jar /hive_test/hive_udtf.jar;  
hive> create temporary function explode_func as 'test.Expolde';

create external table udtf_test_table
(data string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/hive_test';

hive> select explode_func(data) from udtf_test_table;

#UDAF

add jar /hive_test/hive_udaf.jar;
create temporary function gen_ts_max as 'test.GenMaxRow';

create external table udaf_test_table
(id STRING,
ts STRING,
data STRING
)
row format delimited fields terminated by '\t'
stored as textfile
location '/hive_test';

select id, gen_ts_max(ts, data) from udaf_test_table group by id;

select id,mm.col0 as ts, mm.col1 as data
from(
select id, gen_ts_max(ts, data) as mm from udaf_test_table group by id
) s;