1. 常用函数
1.1 系统内置函数
1. 查看系统内置的函数
hive (default)> show functions;
2. 显示自带的函数的用法
hive (default)> desc function upper;
3. 显示详细自带的函数用户
hive (default)> desc function extended upper;
OK
tab_name
upper(str) - Returns str with all characters changed to uppercase
Synonyms: ucase
Example:
> SELECT upper('Facebook') FROM src LIMIT 1;
'FACEBOOK'
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFUpper
Function type:BUILTIN
1.2 常用内置函数
1.2.1 空字段赋值
函数说明
NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。
案例
- 查询：如果员工的 comm 为 NULL，则用-1 代替
hive (default)> select comm,nvl(comm,-1) from emp;
- 查询：如果员工的 comm 为 NULL，则用领导 id 代替
hive (default)> select comm,nvl(comm,mgr) from emp;
1.2.2 CASE WHEN THEN ELSE END
1. 数据准备
| name | dept_id | sex |
|---|---|---|
| 悟空 | A | 男 |
| 大海 | A | 男 |
| 宋宋 | B | 男 |
| 凤姐 | A | 女 |
| 婷姐 | B | 女 |
| 婷婷 | B | 女 |
2.需求： 求出不同部门男女各多少人。结果如
dept_Id 男 女
A 2 1
B 1 2
3. 查询
select
dept_id,
sum(case when sex='男' then 1 else 0 end) male_count,
sum(case when sex='女' then 1 else 0 end) fmale_count
from emp_sex
group by dept_id;
1.2.3 行转列
1. 函数说明
1. CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
2. CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间
3. COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段
注意: CONCAT_WS must be "string or array<string>
2. 数据准备
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
苍老师 白羊座 B
3. 建表并导入
create external table if not exists person_info(
name string comment '姓名',
constellation string comment '星座',
blood_type string comment '血型'
)
row format delimited fields terminated by '\t';
load data local inpath '/opt/data/person_info.txt' into table person_info;
4. 查询
- 第一步，先将星座和血型合在一起
hive (default)> select name, concat_ws(',',constellation,blood_type) as c_b from person_info;
OK
name c_b
孙悟空 白羊座,A
大海 射手座,A
宋宋 白羊座,B
猪八戒 白羊座,A
凤姐 射手座,A
苍老师 白羊座,B
- 将姓名先去重后合在一起以第一步的c_b字段分组
hive (default)> select
> t1.c_b,
> concat_ws('|',collect_set(t1.name))
> from
> (
> select name, concat_ws(',',constellation,blood_type) as c_b from person_info
> ) t1
> group by t1.c_b;
OK
t1.c_b _c1
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师
在 Group by 子句中，Select 查询的列，要么需要是 Group by 中的列，要么得是用聚合函数（比如 sum、count 等）加工过的列。不支持直接引用非 Group by 的列。这一点和 MySQL 有所区别
1.2.4 列转行
函数说明
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
1. 数据准备
| movie | category |
|---|---|
| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
| 《战狼 2》 | 战争,动作,灾难 |
2. 需求
将电影分类中的数组数据展开。结果如下：
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼 2》 战争
《战狼 2》 动作
《战狼 2》 灾难
3. 建表并导入数据
create external table if not exists movie_info(
movie string,
category string
)
row format delimited fields terminated by '\t';
load data local inpath '/opt/data/movie_info.txt' into table movie_info;
4. 查询
select
movie,
category_name
from
movie_info
lateral view explode(split(category,",")) movie_info_tmp as category_name;
1.2.5 窗口函数
1. 函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。
2. 语法结构
over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
字段解释
(1). order by
order by 排序
(2). partition by
partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算的。
(3). rows between 开始位置 and 结束位置
是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的
3. 与over()一起常用的函数
1.聚合类
avg()、sum()、max()、min()
2.排名类
row_number() 按照值排序时产生一个自增编号，不会重复
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位
3.其他类
lag(列名,往前第几行的数据,[行数为null时的默认值，不指定为null])
lead(列名,往后第几行的数据,[行数为null时的默认值，不指定为null])
ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号
4. 数据准备：name，orderdate，cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
6. 需求
（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的 cost 按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前 20%时间的订单信息
7. 建表导入数据
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/data/business.txt" into table business;
8. 查询数据
(1) 查询在 2017 年 4 月份购买过的顾客及总人数
1. 根据用户名分组求出2017-04月份的用户，及其对应的次数
select
name,
count(*)
from business
where substring(orderdate,1,7)='2017-04'
group by name;
结果
name _c1
jack 1
mart 4
2. 使用窗口函数统计总人数
select
name,
count(*) over()
from business
where substring(orderdate,1,7)='2017-04'
group by name;
结果
name count_window_0
jack 2
mart 2
(2) 查询顾客的购买明细及月购买总额
1. 查询明细
select name,orderdate,cost from business;
2. 加上月购买总额,以月份为分区
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
(3) 将每个顾客的 cost 按照日期进行累加
将顾客按照姓名分组，以日期升序排序，组内的每条数据将之前的金额累加
select
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
from business;
(4) 查询每个顾客上次的购买时间
select
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate) as time1 ,
lag(orderdate,2) over(partition by name order by orderdate) as time2
from business;
(5) 查询前 20%时间的订单信息
select
*
from(
select
name,
orderdate,
cost,
ntile(5) over(order by orderdate) as sorted
from business
) t
where sorted=1;
1.2.6 Rank函数
1. 函数说明
ANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
2. 数据准备
| name | subject | score |
|---|---|---|
| 孙悟空 | 语文 | 87 |
| 孙悟空 | 数学 | 95 |
| 孙悟空 | 英语 | 68 |
| 大海 | 语文 | 94 |
| 大海 | 数学 | 56 |
| 大海 | 英语 | 84 |
| 宋宋 | 语文 | 64 |
| 宋宋 | 数学 | 86 |
| 宋宋 | 英语 | 84 |
| 婷婷 | 语文 | 65 |
| 婷婷 | 数学 | 85 |
| 婷婷 | 英语 | 78 |
3. 需求
计算每门学科成绩排名
4. 创建表并导入数据
create external table if not exists score(
name string,
subject string,
score int
)
row format delimited fields terminated by "\t";
hive (default)> load data local inpath '/opt/data/score.txt' into table score;
5. 查询
select
name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;
1.3 自定义函数
1.3.1 自定义函数概述
1. 自定义函数种类
- UDF（User-Defined-Function）: 一进一出
- UDAF（User-Defined Aggregation Function）：多进一出
- UDTF（User-Defined Table-Generating Functions）：一进多出 2. 编程步骤
- 继承 Hive 提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
- 实现类中的抽象方法
- 在 hive 的命令行窗口创建函数
添加 jar
add jar linux_jar_path
创建 function
create [temporary] function [dbname.]function_name AS class_name;
- 在 hive 的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;
1.3.2 自定义UDF函数
1. 需求
自定义一个 UDF 实现计算给定字符串的长度,例：
hive(default)> select my_len("abcd");
4
2. 导入依赖
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
3. 创建一个类
public class MyUDF extends GenericUDF {
/**
* @param arguments 输入参数类型的鉴别器对象
* @return 返回值类型的鉴别器对象
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
//判断输入的个数
if (arguments.length!=1){
throw new UDFArgumentException("参数个数不为1");
}
//判断输入的参数类型
if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
throw new UDFArgumentTypeException(0,"输入数据类型错误");
}
//函数本身返回值为int，需要返回int类型的鉴别器对象
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
/**
* 函数的逻辑处理
* @param deferredObjects
* @return
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
// 1. 取出输入数据
String input = deferredObjects[0].get().toString();
//判断输入数据是否为null
if (input==null){
return 0;
}
return input.length();
}
@Override
public String getDisplayString(String[] strings) {
return "";
}
}
4. 打包修改jar包名为myudf.jar并上传
5. 将jar包添加到hive的classpath
hive (default)> add jar /opt/data/myudf.jar;
Added [/opt/data/myudf.jar] to class path
Added resources: [/opt/data/myudf.jar]
6. 创建临时函数与jar包关联
hive (default)> create temporary function my_len as "com.wsl.udf.MyUDF";
7. 使用
hive (default)> select ename ,my_len(ename) from emp;
OK
ename _c1
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5
CLARK 5
SCOTT 5
KING 4
TURNER 6
ADAMS 5
JAMES 5
FORD 4
1.3.3 自定义UDTF函数
1. 需求
自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：
hive(default)> select myudtf("hello,world,hadoop,hive", ",");
hello
world
hadoop
hive
2. 实现
public class MyUDTF extends GenericUDTF {
private ArrayList<String> outList = new ArrayList<>();
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//1.定义输出数据的列名和类型
List<String> fieldNames = new ArrayList<>();
List<ObjectInspector> fieldOIs = new ArrayList<>();
//2.添加输出数据的列名和类型
fieldNames.add("lineToWord");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return
ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,
fieldOIs);
}
@Override
public void process(Object[] args) throws HiveException {
//1.获取原始数据
String arg = args[0].toString();
//2.获取数据传入的第二个参数，此处为分隔符
String splitKey = args[1].toString();
//3.将原始数据按照传入的分隔符进行切分
String[] fields = arg.split(splitKey);
//4.遍历切分后的结果，并写出
for (String field : fields) {
//集合为复用的，首先清空集合
outList.clear();
//将每一个单词添加至集合
outList.add(field);
//将集合内容写出
forward(outList);
}
}
@Override
public void close() throws HiveException {
}
}
3. 打包修改jar包名为myudf.jar并上传
4. 将jar包添加到hive的classpath
hive (default)> add jar /opt/data/myudtf.jar;
5. 创建临时函数与jar包关联
hive (default)> create temporary function myudtf as "com.wsl.udtf.MyUDTF";
6. 使用
hive (default)> select myudtf("hello,world,hadoop,hive",",");
OK
linetoword
hello
world
hadoop
hive
2. 其他函数
2.1 日期函数
1. 常用日期函数
unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');
from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);
current_date：当前日期
select current_date;
current_timestamp：当前的日期加时间
select current_timestamp;
to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');
year：获取年
select year('2020-10-28 12:12:12');
month：获取月
select month('2020-10-28 12:12:12');
day：获取日
select day('2020-10-28 12:12:12');
hour：获取时
select hour('2020-10-28 12:12:12');
minute：获取分
select minute('2020-10-28 12:12:12');
second：获取秒
select second('2020-10-28 12:12:12');
weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');
dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');
months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');
add_months：日期加减月
select add_months('2020-10-28',-3);
datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');
date_add：日期加天数
select date_add('2020-10-28',4);
date_sub：日期减天数
select date_sub('2020-10-28',-4);
last_day：日期的当月的最后一天
select last_day('2020-02-30');
date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
2.2 取整函数
常用取整函数
round： 四舍五入
select round(3.14);
select round(3.54);
ceil： 向上取整
select ceil(3.14);
select ceil(3.54);
floor： 向下取整
select floor(3.14);
select floor(3.54);
2.3 字符串函数
常用字符串操作函数
upper： 转大写
select upper('low');
lower： 转小写
select lower('low');
length： 长度
select length("atguigu");
trim： 前后去空格
select trim(" atguigu ");
lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');
rpad： 向右补齐，到指定长度
select rpad('atguigu',9,'g');
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');
2.4 集合函数
集合操作
size： 集合中元素的个数
select size(friends) from test3;
map_keys： 返回map中的key
select map_keys(children) from test3;
map_values: 返回map中的value
select map_values(children) from test3;
array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;
sort_array： 将array中的元素排序
select sort_array(friends) from test3;
grouping_set:多维分析
