Hive-day10 DQL和Hive内置函数

Hive查询语法(DQL)

全局排序

• order by 会对输入做全局排序，因此在执行MapReduce任务时只有一个reducer，这会导致当输入规模较大时，需要较长的计算时间。因为所有的数据都要经过唯一的reducer，所以数据大的情况下，一个reducer的处理速度有限
• 使用 order by子句排序 :ASC（ascend）升序（默认）| DESC（descend）降序
• order by放在select语句的结尾

select * from 表名 order by 字段名1[，别名2...];

4.2 局部排序

• sort by 不是全局排序,其在数据进入reducer前完成排序。
• 如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。ASC（ascend）升序（默认）| DESC（descend）降序
• 设置reduce个数

set mapreduce.job.reduce=3;
set mapred.reduce.tasks=3;

• 查看reduce个数

set mapreduce.job.reduce;

• 排序

select * from 表名 sort by 字段名[,字段名...];

4.3 分区排序

distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

类似MR中partition,进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）

对于distrbute by 进行测试，一定要多分配reduce进行处理，否则无法看到distribute by的效果。

设置reduce个数

set mapreduce.job.reduce=7;

• 排序

select * from 表名 distribute by 字段名[,字段名...];

4.3 分区并排序

• cluster by（字段）除了具有Distribute by的功能外，还会对该字段进行排序
• cluster by = distribute by + sort by 只能默认升序，不能使用倒序

select * from 表名 sort cluster by 字段名[,字段名...];
select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];


5、Hive内置函数

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

-- 1.查看系统自带函数
show functions;
-- 2.显示自带的函数的用法
desc function upper;
-- 3.详细显示自带的函数的用法
desc function extended upper;


5.1 内置函数分类

关系操作符：包括 = 、 <> 、 <= 、>=等

算数操作符：包括 + 、 - 、 *、／等

逻辑操作符：包括AND 、 && 、 OR 、 || 等

复杂类型构造函数：包括map、struct、create_union等

复杂类型操作符：包括A[n]、Map[key]、S.x

数学操作符：包括ln(double a)、sqrt(double a)等

集合操作符：包括size(Array)、sort_array(Array)等

类型转换函数： binary(string|binary)、cast(expr as )

日期函数：包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等

条件函数：包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等

字符串函数：包括acat(string|binary A, string|binary B…)等

其他：xpath、get_json_objectscii(string str)、con

5.2 UDTF hive中特殊的一个功能（进一出多）

-- UDF 进一出一


-- UDAF 进多出一
-- collect_set()和collect_list()都是对多列转成一行，区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type))   ':' 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
select 字段名,concat_ws(':',collect_set(列名)) as 别名 from 表名 group by id;

-- UDTF 进一出多
-- explode  可以将一组数组的数据变成一列表
select  explode(split(列名,"数据的分隔符")) from 表名;
-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,列名 from 表1,lateral view explode(split(表1.列名,"数据的分隔符"))新列名 as 别列名;

-- 创建数据库表
create table t_movie1(
id int,
name string,
types string
)
row format delimited fields terminated by ','
lines terminated by '\n';

-- 电影数据  movie1.txt
-- 加载数据到数据库 load data inpath '/shujia/movie1.txt' into table t_movie1;
1,这个杀手不太冷,剧情-动作-犯罪
2,七武士,动作-冒险-剧情
3,勇敢的心,动作-传记-剧情-历史-战争
4,东邪西毒,剧情-动作-爱情-武侠-古装
5,霍比特人,动作-奇幻-冒险

-- explode  可以将一组数组的数据变成一列表
select  explode(split(types,"-")) from t_movie1;

-- lateral view 表生成函数，可以将explode的数据生成一个列表
select id,name,type from t_movie1 lateral view explode(split(types,"-")) typetable as type;

-- 创建数据库表
create table t_movie2(
id int,
name string,
type string
)
row format delimited fields terminated by ','
lines terminated by '\n';

-- 电影数据 movie2.txt
-- 加载数据到数据库 load data inpath '/shujia/movie2.txt' into table t_movie2;
1,这个杀手不太冷,剧情
1,这个杀手不太冷,动作
1,这个杀手不太冷,犯罪
2,七武士,动作
2,七武士,冒险
2,七武士,剧情
3,勇敢的心,动作
3,勇敢的心,传记
3,勇敢的心,剧情
3,勇敢的心,历史
3,勇敢的心,战争
4,东邪西毒,剧情
4,东邪西毒,动作
4,东邪西毒,爱情
4,东邪西毒,武侠
4,东邪西毒,古装
5,霍比特人,动作
5,霍比特人,奇幻
5,霍比特人,冒险

-- collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type))   ':' 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
select id,concat_ws(':',collect_set(type)) as types from t_movie2 group by id;

5.3 WordCount案例

数据准备

hello,world
hello,bigdata
like,life
bigdata,good

建表

create table wc
(
line string
)
row format delimited fields terminated by ','

导入数据

load data local inpath '/usr/local/soft/data/wc1.txt' into table wc;

步骤1：先对一行数据进行切分

select split(line,',') from wc;

步骤2：将行转列

select explode(split(line,',')) from wc; 

步骤3：将相同的进行分组统计

select w.word,count(*) from (select explode(split(line,',')) as word from wc) w group by w.word;