系统内置函数
查看系统函数
show functions;
查看函数的用法
desc function upper;
查看函数的详细用法
desc function extended upper;
常用内置函数
空字段赋值
NVL：为 NULL 赋值，格式 : NVL( value，default_value)
如 : value = NULL， NVL 返回 default_value ，否则返回 value ，如两个参数都为 NULL ，则返回 NULL
员工的 comm = NULL，则用 -1 代替
select comm, nvl(comm, -1) from emp;
员工的 comm = NULL ，则用领导 id 代替
select comm, nvl(comm, mgr) from emp;
CASE
创建本地 emp_sex.txt ，导入数据
vim emp_sex.txt
内容 :
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
创建 hive 表并导入数据
create table emp_sex(
name string,
dept_id string,
sex string
)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive-3.1.2/datas/emp_sex.txt' into table emp_sex;
求出不同部门男女各多少人
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;
select
dept_id,
sum(if(sex = '男', 1, 0)) man,
sum(if(sex = '女', 1, 0)) female
from
emp_sex
group by
dept_id;
行转列
返回输入字符串连接后的结果，支持任意个输入字符串 :
CONCAT(string A/col, string B/col…)
分隔符将被加到被连接的字符串之间 , 跳过分隔符参数后的任何 NULL 和空字符串 :
CONCAT_WS(separator, str1, str2, ...)
CONCAT_WS必须是stringorarray<string>
将某字段的值进行去重汇总，产生 array 类型字段 , 只接受基本数据类型
COLLECT_SET(col)
需求
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师
创建文件
vim person_info.txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
苍老师 白羊座 B
创建 hive 表并导入数据
create table person_info(
name string,
constellation string,
blood_type string
)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive-3.1.2/datas/person_info.txt" into table person_info;
查询数据
SELECT
t1.c_b,
CONCAT_WS("|", collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',', constellation, blood_type) c_b
FROM person_info
)t1
GROUP BY
t1.c_b
列转行
将一列中 array 或 map 拆分成多行
EXPLODE(col)
将一列数据拆成多行数据 , 并与 split, explode 一起使用，对拆分后的数据进行聚合
LATERAL VIEW udtf(expression) tableAlias AS columnAlias
需求
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
创建文件
vim movie_info.txt
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
创建 hive 表并导入数据
create table movie_info(
movie string,
category string
)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive-3.1.2/datas/movie_info.txt" into table movie_info;
查询数据
SELECT
movie,
category_name
FROM
movie_info
lateral VIEW
explode(split(category, ",")) movie_info_tmp AS category_name;
窗口函数
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 类型
指定的字段进行分区， 将分区字段的值相同的数据划分到相同的区 , 区中的每条数据开启一个窗口 , 窗口大小 = 当前分区数据集的大小
over(partition by ....)
指定的字段对数据进行排序 , 每条数据开启一个窗口 , 窗口大小 = 从开始到当前行
over(order by ....)
指定的字段进行分区， 将分区字段的值相同的数据划分到相同的区, 在每个区中会按照指定的字段进行排序. 每条数据开启一个窗口 , 窗口大小 = 从开始到当前行
over(partition by .... order by ....)
指定每条数据的窗口大小
over(partition by ... order by ... rows between ... and ....)
- order by全局排序 或者 窗口函数中排序
- distribute by : 分区
- sort by : 区内排序
- cluster by : 分区排序
- partition by : 窗口函数中分区
- partitioned by : 建表指定分区字段
- clustered by : 建表指定分桶字段
需求
- 查询在2017年4月份购买过的顾客及总人数
- 查询顾客的购买明细及月购买总额
- 上述的场景, 将每个顾客的cost按照日期进行累加
- 查询每个顾客上次的购买时间
- 查询前20%时间的订单信息
创建文件
vim business.txt
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
创建hive表
create table business(
name string,
orderdate string,
cost int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/hive-3.1.2/datas/business.txt" into table business;
查询数据
查询在 2017 年 4 月份购买过的顾客及总人数
select
name,
count(*) over()
from
business
where
substring(orderdate, 1, 7) = '2017-04'
group by
name;
select
t1.name,
count(t1.name) over()
from(
select
distinct(name)
from
business
where
substring(orderdate, 0, 7) = '2017-04'
)t1
查询顾客的购买明细及所有顾客的月购买总额
select
name,
orderdate,
cost,
sum(cost) over(partition by substring(orderdate, 0, 7)) month_cost
from
business
select
name,
orderdate,
cost,
sum(cost) over(partition by month(orderdate))
from
business;
查询顾客的购买明细及每个顾客的月购买总额
select
name,
orderdate,
cost,
sum(cost) over(partition by name, substring(orderdate, 0, 7)) name_month_cost
from
business
将每个顾客的 cost 按照日期进行累加
select
name,
orderdate,
cost,
sum(cost) over() as sample1, -- 所有行相加
sum(cost) over(partition by name) as sample2, -- 按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3, -- 按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row) as sample4, -- 和sample3一样, 由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, -- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING) as sample7 -- 当前行及后面所有行
from
business;
rows 必须跟在 Order by 子句后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
将所有顾客的 cost 按照日期进行累加
select
name,
orderdate,
cost,
sum(cost) over(order by orderdate) lj
from
business;
求所有顾客的购买明细及按照日期进行排序后
- 所有顾客的 cost 第一行 到 当前行 累加
- 所有顾客的 cost 上一行 到 当前行 的累加和
- 所有顾客的 cost 上一行 到 下一行 的累加和
- 所有顾客的 cost 当前行 到 下一行 的累加和
- 所有顾客的 cost 当前行 到 最后一行的累加和
select
name,
orderdate,
cost,
sum(cost) over(order by orderdate rows between unbounded preceding and current row) f_c,
sum(cost) over(order by orderdate rows between 1 preceding and current row) p_c,
sum(cost) over(order by orderdate rows between 1 preceding and 1 following) p_n,
sum(cost) over(order by orderdate rows between current row and 1 following) c_n,
sum(cost) over(order by orderdate rows between current row and unbounded following) c_l
from
business;
查询每个顾客上次的购买时间 及 下一次的购买时间
select
name,
orderdate,
cost,
lag(orderdate, 1, '1900-01-01') over(partition by name order by orderdate) as time1,
lag(orderdate, 2) over (partition by name order by orderdate) as time2,
lead(orderdate ,1 ,'9999-01-01') over(partition by name order by orderdate) p_orderdate
from
business;
查询前 20% 时间的订单信息
select
*
from (
select
name,
orderdate,
cost,
ntile(5) over(order by orderdate) sorted
from
business
) t
where
sorted = 1;
Rank
RANK(): 排序相同时会重复，总数不会变DENSE_RANK(): 排序相同时会重复，总数会减少ROW_NUMBER(): 根据顺序计算
需求
计算每门学科成绩排名
创建文件
vim score.txt
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
创建 hive 表
create table score(
name string,
subject string,
score int
)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive-3.1.2/datas/score.txt' into table score;
查询数据
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;
日期函数
返回当前或指定时间的时间戳
select unix_timestamp();
select unix_timestamp("2021-12-22" 'yyyy-MM-dd');
将时间戳转为日期格式
select from_unixtime(162843200);
当前日期
select current_date;
当前的日期加时间
select current_timestamp;
抽取日期部分
select to_date('2021-12-22 11:11:11')
获取年
select year('2021-12-22 11:11:11');
获取月
select month('2021-12-22 11:11:11');
获取日
select day('2021-12-22 11:11:11');
获取时
select hour('2021-12-22 11:11:11');
获取分
select minute('2021-12-22 11:11:11');
获取秒
select second('2021-12-22 11:11:11');
当前时间是一年中的第几周
select weekofyear('2021-12-22 11:11:11');
当前时间是一个月中的第几天
select dayofmonth('2021-12-22 11:11:11');
两个日期间的月份
select months_between('2021-12-22', '2021-10-22');
日期加减月
select add_months('2021-12-22', -6);
两个日期相差的天数
select datediff('2021-12-22', '2021-11-22');
日期加天数
select date_add('2021-12-22', 4);
日期减天数
select date_sub('2021-12-22', 5);
日期的当月的最后一天
select last_day('2021-12-30');
格式化日期
select date_format('2021-12-30 11:11:11', 'yyyy/MM/dd HH:mm:ss');
取整函数
四舍五入
select round(3.14);
select round(3.54);
向上取整
select ceil(3.14);
select ceil(3.54);
向下取整
select floor(3.14);
select floor(3.54);
字符串操作函数
转大写
select upper('low');
转小写
select lower('LOW');
长度
select length('cpuCode');
前后去空格
select trim(' cpu ');
向左补齐，到指定长度
select lpad('cpuCod', 7, 'e');
向右补齐，到指定长度
select rpad('puCode', 7, 'c');
正则表达式匹配目标字符串
select regexp_replace('2021/10/11', '/', '-');
集合操作
集合中元素的个数
select size(friends) from test2;
返回map中的key
select map_keys(children) from test2;
返回map中的value
select map_values(children) from test2;
判断array中是否包含某个元素
select array_contains(friends, 'cpuCode') from test2;
将array中的元素排序
select sort_array(friends) from test2;
多维分析
grouping sets
create table testgrouping (
id int,
name string,
sex string,
deptno int
)
row format delimited fields terminated by ',';
统计每个部门各多少人， 男女各多少人， 每个部门中男女各多少人 :
select
deptno,
sex,
count(id)
from
testgrouping
group by
deptno, sex
grouping sets(deptno, sex, (deptno, sex))
自定义函数
当 Hive 的内置函数无法满足业务需要时，就可以考虑使用用户自定义函数（UDF：user-defined function）
用户自定义函数类别分为 ：
- UDF（User-Defined-Function）: 一进一出
- UDAF（User-Defined Aggregation Function）: 聚集函数，多进一出, 如 ：
count/max/min - UDTF（User-Defined Table-Generating Functions）: 一进多出, 如 :
lateral view explode()
官方文档地址 : https://cwiki.apache.org/confluence/display/Hive/HivePlugins
自定义步骤
- 继承 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;
自定义 UDF 函数
自定义一个 UDF 实现计算给定字符串的长度
select my_len("couCode");
创建一个 Maven 工程
导入依赖 :
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
创建一个类
package com.cpucode.hive;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
/**
* 自定义UDF函数，需要继承 GenericUDF 类
* 需求: 计算指定字符串的长度
*/
public class MyStringLength extends GenericUDF {
/**
*
* @param arguments 输入参数类型的鉴别器对象
* @return 返回值类型的鉴别器对象
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
// 判断输入参数的个数
if(arguments.length != 1){
throw new UDFArgumentLengthException("Input Args Length Error!!!");
}
// 判断输入参数的类型
if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
throw new UDFArgumentTypeException(0, "Input Args Type Error!!!");
}
//函数本身返回值为int，需要返回int类型的鉴别器对象
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
/**
* 函数的逻辑处理
* @param arguments 输入的参数
* @return 返回值
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
if(arguments[0].get() == null){
return 0 ;
}
return arguments[0].get().toString().length();
}
@Override
public String getDisplayString(String[] children) {
return "";
}
}
打成 jar 包上传到服务器 /opt/module/hive-3.1.2/datas/myudf.jar
将 jar 包添加到 hive 的 classpath
add jar /opt/module/hive-3.1.2/datas/myudf.jar;
创建临时函数与开发好的 java class 关联
create temporary function my_len as "com.cpucode.hive.MyStringLength";
即可在 hql 中使用自定义的函数
select
ename,
my_len(ename) ename_len
from
emp;
自定义 UDTF 函数
自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词
select myudtf("cpu,code,hadoop,hive", ",");
代码实现
package com.cpucode.udtf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.List;
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 {
}
}
打成 jar 包上传到服务器 /opt/module/hive/data/myudtf.jar
将 jar 包添加到 hive 的 classpath 下
add jar /opt/module/hive-3.1.2/data/myudtf.jar;
创建临时函数与开发好的java class关联
create temporary function myudtf as "com.cpucode.hive.MyUDTF";
使用自定义的函数
select myudtf("cpu,code,hadoop,hive", ",");
若有收获，就点个赞吧
0 人点赞
