函数分类,HIVE CLI命令,简单函数,聚合函数,集合函数,特殊函数(窗口函数,分析函数,混合函数,UDTF),常用函数Demo

简介: 1.1. 函数分类1.2. HIVE CLI命令显示当前会话有多少函数可用 SHOW FUNCTIONS;显示函数的描述信息 DESC FUNCTION concat;显示函数的扩展描述信息 DESC FUNCTION EXTENDED concat;1.3. 简单函数函数的计算粒度为单条记录。 关系运算 数学运算 逻辑运算 数值计算 类型转换 日期函数 条件函数 字

1.1. 函数分类


1.2. HIVE CLI命令

显示当前会话有多少函数可用 
SHOW FUNCTIONS;

显示函数的描述信息 
DESC FUNCTION concat;


显示函数的扩展描述信息 
DESC FUNCTION EXTENDED concat;

1.3. 简单函数

函数的计算粒度为单条记录。 
关系运算 
数学运算 
逻辑运算 
数值计算 
类型转换 
日期函数 
条件函数 
字符串函数 
统计函数

1.4. 聚合函数

函数处理的数据粒度为多条记录。 
sum()—
求和 
count()—
求数据量 
avg()—
求平均直 
distinct—
求不同值数 
min—
求最小值 
max—
求最大值

1.5. 集合函数

复合类型构建 
复杂类型访问 
复杂类型长度

1.6. 特殊函数

窗口函数

应用场景 
用于分区排序 
动态Group By 
Top N 
累计计算 
层次查询

Windowing functions

lead
lag
FIRST_VALUE
LAST_VALUE

分析函数

Analytics functions

RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE

混合函数

java_method(class,method [,arg1 [,arg2])reflect(class,method [,arg1 [,arg2..]])hash(a1 [,a2...])

UDTF

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,‘ columnAlias)*  fromClause: FROM baseTable (lateralView)*  

ateral view用于和split, explodeUDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTFUTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。

1.7. 常用函数Demo

create table employee(
id string,
money double,
type string)row format delimited 
fields terminated by \t 
lines terminated by \n 
stored as textfile;
load data local inpath '/home/tuzq/software/hivedata/employee.txt' into table employee;
优先级依次为NOT AND OR

select id,money from employee where (id = '2' or id = '3' or id = '4' orid = '5') AND (money > 120 AND money < 250);

 

数据准备:在/home/tuzq/software/hivedata下创建employee.txt,

数据内容如下:

[root@hadoop1 hivedata]# cat employee.txt

1     100.0      fds

2     150.0      hdfs

3     200.0      hdfs

4     35.0 hadoop

5     280.7      hive

[root@hadoop1 hivedata]#

hive中的显示效果如下:

带有条件的查询:

cast类型转换

select cast(1.5 as int);



if判断

if(con,‘‘,‘‘);
 
hive (default)> select if(2>1,‘YES‘,‘NO‘);
YES
case when con then ‘‘ when con then ‘‘ else  ‘‘ end (‘‘里面类型要一样)

select case when id='1' then 'A0001'when id='2' then 'A0002' when id='3' then 'A0003' when id='4' then 'A0004' endfrom employee;

查询的显示效果如下:

get_json_object

get_json_object(json 解析函数,用来处理json,必须是json格式)select get_json_object(‘{"name":"jack","age":"20"}‘,‘$.name‘);


URL解析函数

parse_url(string urlString, string partToExtract [, string keyToExtract])
select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘HOST‘) from
employee limit 1;


字符串连接函数: concat 
语法: concat(string A, stringB…) 
返回值: string 
说明:返回输入字符串连接后的结果,支持任意个输入字符串 
举例:

hive> select concat(‘abc‘,‘def’,‘gh‘) from lxw_dual;
abcdefgh


带分隔符字符串连接函数: concat_ws 
语法: concat_ws(string SEP,string A, string B…) 
返回值: string 
说明:返回输入字符串连接后的结果, SEP 表示各个字符串间的分隔符

concat_ws(string SEP, array<string>)
举例:
hive> select concat_ws(‘,‘,‘abc‘,‘def‘,‘gh‘) from lxw_dual;
abc,def,gh


再如案例:


列出该字段所有不重复的值,相当于去重
collect_set(id)  //返回的是数组
列出该字段所有的值,列出来不去重 
collect_list(id)   //返回的是数组
select collect_set(id) from taborder;
求和
sum(money)
统计列数
count(*)
select sum(num),count(*) from taborder;


窗口函数

first_value(第一行值)
 
first_value(money) over (partition by id order by money)
select ch,num,first_value(num) over (partition by ch order by num) from taborder;
 
 
 
 
rows between 1 preceding and 1 following (当前行以及当前行的前一行与后一行)
hive (liguodong)> select ch,num,first_value(num) over (partition by ch order by num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) from taborder;
 
 
last_value 最后一行值
hive (liguodong)> select ch,num,last_value(num) over (partition by ch) from taborder;
 
 
 lead
 去当前行后面的第二行的值
 lead(money,2) over (order by money)
 
 lag 
 去当前行前面的第二行的值
 lag(money,2) over (order by money)
 ```
 
 ```
 select ch, num, lead(num,2) over (order by num) from taborder;
 
 
 select ch, num, lag(num,2) over (order by num) from taborder;

rank排名

rank() over(partition by id order by money)
select ch, num, rank() over(partition by ch order by num) as rank from taborder;
select ch, num, dense_rank() over(partition by ch order by num) as dense_rank from taborder;


cume_dist

cume_dist (相同值的最大行号/行数)
cume_dist() over (partition by id order by money)
 
percent_rank (相同值的最小行号-1)/(行数-1)
第一个总是从0开始
percent_rank() over (partition by id order by money)
 
 
 select ch,num,cume_dist() over (partition by ch order by num) as cume_dist,
 percent_rank() over (partition by ch order by num) as percent_rank
 from taborder;
 ntile分片 
 ntile(2) over (order by money desc)  分两份 
 
 select ch,num,ntile(2) over (order by num desc) from taborder;


混合函数

select id,java_method("java.lang,Math","sqrt",cast(id as double)) as sqrt from hiveTest;

UDTF

 select id,adid 
 from employee 
 lateral view explode(split(type,‘B‘)) tt as adid;
 explode 把一列转成多行
 
hive (liguodong)>  select id,adid
                >  from hiveDemo
                >  lateral view explode(split(str,‘,‘)) tt as adid;


正则表达式 
使用正则表达式的函数 
regexp_replace(string subject A,stringB,string C)
 
regexp_extract(string subject,stringpattern,int index)

hive> select regexp_replace(‘foobar‘, oo|ar‘, ‘‘) from lxw_dual;
fb
 
hive> select regexp_replace(‘979|7.10.80|8684‘, ‘.*\\|(.*)‘,1) from hiveDemo limit 1;
 
 
hive> select regexp_replace(‘979|7.10.80|8684‘, ‘(.*?)\\|(.*)‘,1) from hiveDemo limit 1;

 

目录
相关文章
|
2月前
|
SQL 存储 Java
Hive UDF UDTF UDAF 自定义函数详解
Hive UDF UDTF UDAF 自定义函数详解
53 2
Hive UDF UDTF UDAF 自定义函数详解
|
2月前
|
SQL HIVE
hive高频函数(一)
hive高频函数(一)
24 0
|
1月前
|
SQL 数据采集 数据可视化
基于Hive的招聘网站的大数据分析系统
基于Hive的招聘网站的大数据分析系统
|
1月前
|
SQL 关系型数据库 MySQL
基于Hive的天气情况大数据分析系统(通过hive进行大数据分析将分析的数据通过sqoop导入到mysql,通过Django基于mysql的数据做可视化)
基于Hive的天气情况大数据分析系统(通过hive进行大数据分析将分析的数据通过sqoop导入到mysql,通过Django基于mysql的数据做可视化)
|
2月前
|
SQL XML JSON
Hive函数全解——思维导图 + 七种函数类型
Hive函数全解——思维导图 + 七种函数类型
48 2
Hive函数全解——思维导图 + 七种函数类型
|
2月前
|
SQL 数据采集 存储
Hive实战 —— 电商数据分析(全流程详解 真实数据)
关于基于小型数据的Hive数仓构建实战,目的是通过分析某零售企业的门店数据来进行业务洞察。内容涵盖了数据清洗、数据分析和Hive表的创建。项目需求包括客户画像、消费统计、资源利用率、特征人群定位和数据可视化。数据源包括Customer、Transaction、Store和Review四张表,涉及多个维度的聚合和分析,如按性别、国家统计客户、按时间段计算总收入等。项目执行需先下载数据和配置Zeppelin环境,然后通过Hive进行数据清洗、建表和分析。在建表过程中,涉及ODS、DWD、DWT、DWS和DM五层,每层都有其特定的任务和粒度。最后,通过Hive SQL进行各种业务指标的计算和分析。
436 1
Hive实战 —— 电商数据分析(全流程详解 真实数据)
|
2月前
|
SQL 分布式计算 HIVE
Hive Cli / HiveServer2 中使用 dayofweek 函数引发的BUG!
在Hive 3.1.2和Spark 3.0.2集群环境中,遇到`dayofweek`函数bug。当`create_date`为字符串类型时,`dayofweek`函数结果错位。修复方法是将`create_date`转换为`date`类型。在Spark SQL中,原始代码能正常运行,未出现此问题。因此建议在Hive中使用转换后的日期类型以避免错误。
|
2月前
|
SQL HIVE
【Hive SQL】字符串操作函数你真的会用吗?
本文介绍了SQL中判断字符串是否包含子串的几种方法。`IN`函数判断元素是否完全等于给定元素组中的某项,而非包含关系。`INSTR`和`LOCATE`函数返回子串在字符串中首次出现的位置,用于检测是否存在子串。`SUBSTR`则用于提取字符串的子串。`LIKE`用于模糊匹配,常与通配符配合使用。注意`IN`并非用于判断子串包含。
|
2月前
|
SQL HIVE UED
【Hive SQL 每日一题】分析电商平台的用户行为和订单数据
作为一名数据分析师,你需要分析电商平台的用户行为和订单数据。你有三张表:`users`(用户信息),`orders`(订单信息)和`order_items`(订单商品信息)。任务包括计算用户总订单金额和数量,按月统计订单,找出最常购买的商品,找到平均每月最高订单金额和数量的用户,以及分析高消费用户群体的年龄和性别分布。通过SQL查询,你可以实现这些分析,例如使用`GROUP BY`、`JOIN`和窗口函数来排序和排名。
|
2月前
|
SQL Java HIVE
Hive高频面试题之UDTF实现多行输出
Hive高频面试题之UDTF实现多行输出
18 0