1．使用hive实现WordCount
(1) 创建数据库
create database wordcount;
(2) 创建外部表
create external table word_data(line string) row format delimited fields terminated by ',' location '/home/hadoop/worddata';
(3) 映射数据表
load data inpath '/home/hadoop/worddata' into table word_data;
(4) 这里假设我们的数据存放在hadoop下，路径为：/home/hadoop/worddata，里面主要是一些单词文件，内容大概为：
hello man
what are you doing now
my running
hello
kevin
hi man
执行了上述hql就会创建一张表src_wordcount，内容是这些文件的每行数据，每行数据存在字段line中，select * from word_data;就可以看到这些数据
(5) 根据MapReduce的规则，我们需要进行拆分，把每行数据拆分成单词，这里需要用到一个hive的内置表生成函数（UDTF）：explode(array)，参数是array，其实就是行变多列：
create table words(word string); insert into table words select explode(split(line, " ")) as word from word_data;
(6) 查看words表内容
OK
hello
man
what
are
you
doing
now
my
running
hello
kevin
hi
man
split是拆分函数，跟java的split功能一样，这里是按照空格拆分，所以执行完hql语句，words表里面就全部保存的单个单词
(7) group by统计单词
select word, count(*) from wordcount.words group by word;
wordcount.words 库名称.表名称，group by word这个word是create table words(word string) 命令创建的word string
结果：
are 1
doing 1
hello 2
hi 1
kevin 1
man 2
my 1
now 1
running 1
what 1
you 1
2．使用hive求TOP N
rank() over() dense_rank() over() row_number() over()
3．使用Hive进行行列转换
１、问题
hive如何将
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
变为：
a b 1,2,3
c d 4,5,6
—————————————————————————-
２、数据
test.txt
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
—————————————————————————–
３、答案
1.建表
drop table tmp_jiangzl_test; create table tmp_jiangzl_test ( col1 string, col2 string, col3 string ) row format delimited fields terminated by '\t' stored as textfile; -- 加载数据 load data local inpath '/home/jiangzl/shell/test.txt' into table tmp_jiangzl_test;
2.处理
select col1,col2,concat_ws(',',collect_set(col3)) from tmp_jiangzl_test group by col1,col2;
二、列转行
１、问题
hive如何将
a b 1,2,3
c d 4,5,6
变为：
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
2、答案
(1). 建表
drop table tmp_jiangzl_test; create table tmp_jiangzl_test ( col1 string, col2 string, col3 string ) row format delimited fields terminated by '\t' stored as textfile;
(2). 处理：
select col1, col2, col5 from tmp_jiangzl_test a lateral view explode(split(col3,',')) b AS col5;
4．使用Hive进留存率统计
游戏公司等会很关注用户留存率问题,这里给出一个模板
SET mapreduce.job.queuename=xxx; SET mapreduce.job.name=xxx; SET mapreduce.job.reduces=19;
select '日期', '注册用户数', '次日留存率', '2日留存率', '3日留存率', dim_date ,total_cnt ,concat_ws('% | ', cast(round(dif_1cnt*100/total_cnt, 2) as string), cast(dif_1cnt as string)) ,concat_ws('% | ', cast(round(dif_2cnt*100/total_cnt, 2) as string), cast(dif_2cnt as string)) ,concat_ws('% | ', cast(round(dif_3cnt*100/total_cnt, 2) as string), cast(dif_3cnt as string)) ,concat_ws('% | ', cast(round(dif_4cnt*100/total_cnt, 2) as string), cast(dif_4cnt as string)) from ( select p1.state dim_date ,p1.device_os ,count(distinct p1.user_id) total_cnt ,count(distinct if(datediff(p3.state,p1.state) = 1, p1.user_id, null)) dif_1cnt ,count(distinct if(datediff(p3.state,p1.state) = 2, p1.user_id, null)) dif_2cnt ,count(distinct if(datediff(p3.state,p1.state) = 3, p1.user_id, null)) dif_3cnt ,count(distinct if(datediff(p3.state,p1.state) = 4, p1.user_id, null)) dif_4cnt from ( select from_unixtime(unix_timestamp(cast(partition_date as string), 'yyyyMMdd'), 'yyyy-MM-dd') state, user_id from user_active_day where partition_date between date1 and date2 and user_is_new = 1 group by 1,2 )p1 --日新增用户名单(register_date,user_id) left outer join ( select from_unixtime(unix_timestamp(cast(partition_date as string), 'yyyyMMdd'), 'yyyy-MM-dd') state, user_id from active_users where partition_date between date1 and date2 group by 1,2 )p3 --期间活跃用户(active_date,user_id) on (p3.user_id = p1.user_id) group by 1,2 ) p4;
原文地址：http://www.cnblogs.com/yeyuzhuanjia/p/16834197.html
