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;