Hive常见查询操作与函数汇总

语法:

like(str, pattern)、str like pattern
str rlike regexp

返回值: BOOLEAN

说明:

在str中匹配pattern，如果匹配成功，返回true，否则返回false
如果str与regexp匹配，则返回true，否则返回false

区别：

like中的parent是sql下的简单的正则表达式，也叫通配符模式。
% 代表零个或多个字符(任意个字符)
_ 代表一个字符。
rlike中的regexp是基于java的正则表达式，功能强大。
^ 匹配字符串的开头
$ 匹配字符串的结尾
. 除换行符 \n 之外的任何单字符
[a-z] 表示a到z范围内的任何单个字符
{n} 表示出现n次
{m,n} 表示出现m到n次（等价于数学集合中[m,n]）
+、*、？分别表示出现1次或多次、0次或多次、0次或1次

-- 查找ename以A或者B或者第二个字符是A开头的ename
select ename from emp_test 
where ename like ('A%') or like(ename,'B%') or like(ename,'_A%');

select ename from emp_test 
where ename rlike('^[AB]|^.A');

-- 上述两个SQL语句执行结果都一致
ALLEN
WARD
MARTIN
BLAKE
ADAMS
JAMES

由上述需求可以看出，RLike相对比较强大，更简洁。相对于此类需求，hive提供了另外一个函数instr(str, substr)。

2、Join语句

等值Join : Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回，JOIN操作符右边的数据如果不存在，那么就使用NULL值替代。
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回，JOIN操作符左边的数据如果不存在，那么就使用NULL值替代。
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
笛卡儿积：简单来说就是A表中所有元素都与B表中每一个元素进行了连接。
产生条件
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

-- 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称

select e.empno, e.ename, d.deptno, d.dname 
from emp_test e join dept_test d on e.deptno = d.deptno;

-- 查询员工表中部门编号不在部门表中员工信息
select e.* 
from emp_test e left join dept_test d on e.deptno = d.deptno
where d.deptno is null

-- 迪卡尔积
select empno, dname from emp_test, dept_test;

注意：连接谓词中不支持or语句

-- 错误的示范
select e.empno, e.ename, d.deptno
from emp_test e join dept_test d 
    on e.deptno = d.deptno 
        or e.ename = d.ename;

该语句会报语义错误，报错信息如下：

FAILED: SemanticException [Error 10019]: Line 1:70 OR not supported in JOIN currently 'ename'

3、分组

Group By 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

Having与where语句的区别
where针对表中的列发挥作用，用于查询数据；having针对查询结果中的列发挥作用，用于筛选数据。
where后面不能写分组函数，而having后面可以使用分组函数。
where可以用于除group by分组统计语句之后；having只能用于group by分组统计语句。
where的搜索条件是在执行语句进行分组前；having的搜索条件是在执行语句进行分组之后。所以，如果where和having一起使用，where和先执行。
where子句中的条件表达式having都可以用；而having子句中有些表达式where不可以用。如一些用于集合函数（sum、count、avg、max和min）where不能跟。

-- 计算每个部门中每个岗位的最高薪水
select e.deptno,e.job,max(e.sal)
from emp_test e
group by e.deptno,e.job



-- 求每个部门的平均薪水大于2000的部门
select e.deptno,avg(e.sal)
from emp_test e
group by e.deptno
having avg(e.sal) > 2000

4、排序

备注：排序的顺序包括

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

Order By

Order By：全局排序；其会对查询结果集执行一个全局排序，这也就是说所有的数据都通过一个reduce进行处理的过程，对于大数据集，这个过程将消耗很大的时间来执行。

Sort By

Sort By：内部排序；每个Reducer内部进行排序。虽然对全局结果集来说不是排序，但这可以保证每个reduce的输出数据都是有序的，这样就可以提高后面进行的全局排序的效率了。

Distribute By

Distribute By：分区排序；类似MR中对partition进行分区，结合sort by使用。

注意：Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

以下两种写法等价：

select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

-- 按照员工编号降序和工资升序排序
select e.empno, e.ename, e.sal
from emp_test e
order by e.empno DESC, e.sal

备注：

1、使用sort buy时需要设置reduce的数量。

2、在默认情况下（set mapreduce.job.reduces=-1）,实际运行计算过程中reducer的数量会由所读取文件的大小来决定。文件默认大小是256M，即每256M对应一个reduce。比如当文件大小为1G时，会启用4个reducer处理数据；当文件大小为400M时，会启用2个reducer来处理。

3、设置reduce的方法为：set mapreduce.job.reduces=2;

-- 根据部门编号降序查看员工信息
-- 注意：设置reduce的个数为2
set mapreduce.job.reduces=2;

select e.* 
from emp_test e
sort by e.empno desc

sort by 和 distribute by

distribute by 控制 map 的输出在reduer中是如何划分的
sort by 控制reduce的输出数据是有序的
mapreduce job 中传输的所有数据都是按照键-值对的方式进行组织的，默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去。如果出现hash冲突，就会导致不同reducer的输出内容会重叠，因此，当我们结合distribute by使用时，就能有效的解决数据重叠问题。

-- 先按照部门编号分区，再按照员工编号降序排序。最后将结果导入到文件中
insert overwrite local directory '/var/lib/hadoop-hdfs/xgh/hive/out/sortby-result'
select e.*
from emp_test e
where e.deptno in (10,20,30)
distribute by e.deptno
sort by e.empno desc

6、分桶抽样

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

例如：查询表stu_buck中的数据。

select * from stu_buck tablesample(bucket 1 out of 4 on id);

说明：stu_buck 按照id共分8个桶，tablesample(bucket 1 out of 4 on id)表示共取4/8=2个桶的数据，

抽取第1和第3桶的数据。

注：tablesample是抽样语句，

语法：TABLESAMPLE(BUCKET x OUT OF y) 。

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。

x表示从第几个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

注意：x的值必须小于等于y的值，否则

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

二、函数汇总

1、查询函数

行与列的转换（同字符串连接）

行转列

语法:

CONCAT(string A/col, string B/col…)
CONCAT_WS(separator, str1, str2,...)
COLLECT_SET(col)
COLLECT_LIST(col)

返回值:

String
String
Array
List

说明:

concat：返回输入字符串连接后的结果，支持任意个输入字符串。
concat_ws：它是一个特殊形式的 CONCAT()。
第一个参数代表与剩余参数间的分隔符，分隔符将被加到被连接的字符串之间。
分隔符可以是与剩余参数一样的字符串。
如果分隔符是 NULL，返回值也将为 NULL。
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
collect_set：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。即返回去重后的对象。
collect_list: 返回包含重复对象的列表。

select collect_set(job) from emp_test

-- 输出结果
["店员","售货员","经理","分析师","总统","小店员"]


select collect_list(job) from emp_test

-- 输出结果
["店员","售货员","售货员","经理","售货员","经理","经理","分析师","总统","售货员","店员","店员","分析师","店员","小店员","小店员","小店员"]


select concat_ws("-->",collect_set(job)) from emp_test

-- 输出结果
店员-->售货员-->经理-->分析师-->总统-->小店员

列转行

语法:

EXPLODE(col)
LATERAL VIEW

说明:

将hive一列中复杂的array或者map结构拆分成多行。
lateral_view
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

select id_name,name_t.*
from emp_extend
lateral view explode(id_name) name_t as key_a,value_b

--输出结果
id_name         name_t.key_a    name_t.value_b
{7369:"SMITH"}  7369            SMITH
{7499:"ALLEN"}  7499            ALLEN
{7521:"WARD"}   7521            WARD
{7566:"JONES"}  7566            JONES
{7654:"MARTIN"} 7654            MARTIN
{7698:"BLAKE"}  7698            BLAKE

窗口函数

备注：

1、什么是窗口函数

窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不同的是，聚合函数作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口，这里，窗口是由一个 OVER 子句定义的多行记录。聚合函数对其所作用的每一组记录输出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函数，如 sum, max, min, avg, count 等也可以当作窗口函数使用。

2、窗口函数的原理

在用group-by处理数据分组时，每一行只能进入一个分组。窗口函数基于称为框(frame)的一组行，计算表的每一个输入行的返回值，每一行可以属于一个或多个框。常见用例就是查看某些值的滚动平均值，其中每一行代表一天，那么每行属于7个不同的框。

3、窗口函数的功能

窗口函数的功能分为：聚合、取值、排序、序列四种，前三种的使用场景比较常见，容易理解，最后一种(序列)的使用场景比较少。

窗口函数：

<函数子句> （）

        OVER(

                        [PARTATION BY <col>]

                        [ORDER BY <col>] [ASC/DESC]

                        (ROWS | RANGE) <范围条件>

        )

        指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化，窗口函数的语法被分成四个部分：

函数子句：指明具体操作，如sum-求和，first_value-取第一个值
partition by子句：指明分区字段，如果没有，则将所有数据作为一个分区
order by子句：指明了每个分区排序的字段和方式，如果没有，就按照表中的顺序；
窗口子句：指明相对当前记录的计算范围，可以向上（preceding），可以向下（following）,也可以使用between指明，上下边界的值，没有的话默认为当前分区。ROWS BETWEEN，也叫做window子句。

常见函数（具体使用方法，可以参考案例）：

CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。
PERCENT_RANK()：用于计算分区或结果集中行的百分位数。
对于指定的行，PERCENT_RANK()计算行的等级减1，除以评估的分区或查询结果集中的行数减1：

(rank - 1) / (total_rows - 1)

在此公式中，rank是指定行的等级，total_rows是要计算的行数

PERCENT_RANK()对于分区或结果集中的第一行，函数始终返回零。

重复的列值将接收相同的PERCENT_RANK()值。

PERCENT_RANK()是一个顺序敏感函数，因此，必须使用ORDER BY子句。

CUME_DIST() : 计算某个窗口或分区中某个值的累积分布。

如果按升序排列，则统计：小于等于当前值的行数/总行数。

如果是降序排列，则统计：大于等于当前值的行数/总行数。

RANK() 不唯一排序，排序相同时排名会重复，但后续排名会跳过，总数不会变
DENSE_RANK() 不唯一排序，排序相同时排名会重复，但后续排名不会跳过，总数会减少
ROW_NUMBER() 唯一排序，会根据顺序计算，不会生产排序相同的列号，总数不会变

注意：

        1、lag(col,n,default),参数1为列名，参数2为往上第n行，参数3为默认值（当往上第n行为空时，取默认值，缺省值为NULL）
        2、lead(col,n,default)取往后n行数据。
        3、NTILE(n)，平均分配到指定的数量（n）个桶中，将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶。

下面以几个小案例剖析窗口函数的使用方法，涉及到的数据都是伪造的。

-- （1）查询在2021年4月份购买过商品的顾客及总人数
select name,count(*) over()
from business
where substr(orderdate,1,7) = '2021-04'
group by name

-- （2）查询截止目前最近3天的销售额
select orderdate,sum (cost),
sum(sum (cost)) over (order by orderdate desc rows between current row and 2 following)
from business
group by orderdate

解释最近三天的销售额结果：

-- （2）查询顾客的购买明细，要求：
-- 返回每位顾客每笔交易明细，累计每位顾客的交易情况并汇总交易金额，统计所有顾客交易金额
select name,orderdate,cost
,sum(cost) over() sample3 -- 所有行相加
,sum(cost) over(partition by name) sample2 -- 按name分组，组内数据相加
,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) sample1 -- 由起点到当前行的聚合即按name分组，组内数据累加
from business

查询结果如下：

注意：
1、如果不将sample1滞后执行，输出的结果会按照表中的顺序，不能确保时间是递增，且按照name分组。
2、sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) 与 sum(cost) over(partition by name order by orderdate) 都能实现按name分组，组内数据累加

-- （3）查询顾客上次的购买时间,若不存在，在赋空
select name,orderdate,cost,
lag(orderdate,1,null) over(partition by name order by orderdate) pre
from business

输出结果：

-- （4）查询前20%时间的订单信息
select name,orderdate,cost,tile
from (
select name,orderdate,cost,ntile(5) over (partition by orderdate) tile
from business
) tmp
where tmp.tile = 1

-- 说明：ntile(5) over (partition by orderdate) tile 按照时间分区，将数据分为5份，取其中的1份则为前20%时间的订单信息

-- (5) 计算当前金额在总人数之间的占比以及计算金额少于等于当前金额占总人数的比
select name,orderdate,cost,percent_rank() over(order by cost) percent_rank_num,
cume_dist() over(order by cost) cume_dist_num
from business

输出结果：

注意：

当cost值相同时，rank值取小值，如：0.896551724137931该值，其rank=27，而不是29.

-- (6) 按照分数排序，测试三种不同的排序方法
select name,orderdate,cost,
rank() over(order by cost) rank_num,
dense_rank() over(order by cost) d_num,
row_number() over(order by cost) row_num
from business

输出结果：

2、数值计算函数

round函数

语法:

round(double d)
round(double a, int d)

返回值: DOUBLE

说明:

返回double类型d的整数值部分（遵循四舍五入）
返回指定精度d的double类型a的数值

注：如果d的位数大于a的位数，则将a全部输出。

select round(3.1415926535),round(3.1415926535,6),round(3.14,6)

-- 输出结果
3.0     3.141593        3.14

floor和ceil取整函数

语法:

floor(double a)
ceil(double a)

返回值: BIGINT
说明:

返回等于或者小于该double变量的最大的整数
返回等于或者大于该double变量的最小的整数

select floor(3.1415926),ceil(3.1415926);

-- 输出结果
3    4

rand随机数函数

语法:

rand()
rand(int seed)

返回值: double
说明:

返回一个0到1范围内的伪随机数。
如果指定种子seed，则会等到一个稳定的随机数序列

知识补给站：伪随机和真随机

        计算机不会产生绝对随机的随机数，计算机只能产生“伪随机数”。其实绝对随机的随机数只是一种理想的随机数，即使计算机怎样发展，它也不会产生一串绝对随机的随机数。计算机只能生成相对的随机数，即伪随机数。这是由随机数的三个特性决定的。

        随机数三个特性

随机性：完全乱序；
不可推测性：从已有的数，无法推测出下一个数；
不可重复性：随机数之间不重复。

        真随机数是伴随着物理实验的，比如：抛硬币、掷骰子、电子元件的噪音、核裂变等，它的结果符合三大特性的。

        伪随机数伪随机数是通过一定算法，获得一个随机的值，并不是真的随机。伪随机又分为强伪随机数和弱伪随机数。

强伪随机数：更加贴近真随机数，满足特性的。随机性和不可推测性，难以预测。
弱伪随机数：满足随机性，可以预测。

select rand(),rand(),rand(),
    rand(12580),rand(12580),rand(12580)

-- 输出结果（可以看到加了随机种生成的随机数一样）
0.7119959760717769      0.22502980639970172     0.45029465193520213     
0.38375053704256157     0.38375053704256157     0.38375053704256157

abs绝对值函数

语法:

abs(double a)
abs(int a)

返回值:

double
int

说明: 返回数值a的绝对值

select abs(-128),abs(-0.128),abs(128)

-- 输出结果
128     0.128   128

conv进制转换函数

语法: conv(BIGINT num, int from_base, int to_base)
返回值: string
说明: 将数值num从from_base进制转化到to_base进制

select conv(5,10,2),conv(9,10,8),conv(15,10,16)

-- 输出结果
101     11      F

说明：关于一些不常用的函数，需要用到时，读者可以具体查看一下官方文档。比如：

自然指数函数 exp(double a)
以10为底的对数函数 log10(double a)
以2为底的对数函数 log2(double a)
对数函数 log(double base, double a)
幂运算函数 pow(double a, double p)、power(double a, double p)
开平方函数 sqrt(double a)
二进制函数 bin(BIGINT a)
十六进制函数 hex(BIGINT a)
反转十六进制函数 unhex(string a)
正取余函数 pmod(int a, int b),pmod(double a, double b)
正弦函数、反正弦函数 sin(double a)、asin(double a)
余弦函数、反余弦函数 cos(double a)、acos(double a)

3、日期函数

关于hive的日期函数，读者可以参考我曾经写过的另一篇文章，更加深学习。

Hive日期格式转换详解（包含13位时间戳转毫秒时间）_烟雨蒋楠的博客-CSDN博客_hive毫秒转时间戳https://blog.csdn.net/XMZHSY/article/details/119790073?spm=1001.2014.3001.5502

备注：Unix时间戳指从1970-01-01 00:00:00 UTC到指定时间的秒数

获取当前Unix时间戳函数：unix_timestamp

语法:

unix_timestamp()
unix_timestamp(string date)
unix_timestamp(string date, string pattern)

返回值: bigint
说明:

获得当前时区的UNIX时间戳
转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

select unix_timestamp(),
unix_timestamp('2022-05-05 16:29:39'),
unix_timestamp('2022-5-5 4:29:39','yyyy-M-d h:mm:ss');

-- 输出结果
1651739927      1651739379      1651696179

备注：

yyyy：代表年
M：月份数字，一位数的月份没有前导零
MM：代表月（MM和M一样，区别就是MM表示从零开始，比如四月份，MM显示04，M显示4，后面的如同）
dd：代表日
HH：代表24小时制的小时
hh：代表12小时制的小时
mm：代表分钟
ss：代表秒
SSS：代表毫秒

Unix时间戳转日期函数：from_unixtime

语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳到当前时区的时间格式。

select from_unixtime(1651739379),
from_unixtime(1651739379,'yyyy-M-d h:mm:ss')

-- 输出结果
2022-05-05 16:29:39     2022-5-5 4:29:39

日期转年、月、日、时、分、秒、周

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。

语法: year(string date)
返回值: int
说明: 返回日期中的年。

语法: month (string date)
返回值: int
说明: 返回日期中的月份。

语法: day (string date)
返回值: int
说明: 返回日期中的天。

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。

语法: second (string date)
返回值: int
说明: 返回日期中的秒。

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。

日期的比较、增加和减少

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

select datediff('2022-05-05','2022-05-01'),
datediff('2022-05-05 17:04:05','2022-05-01 01:01:01')

-- 输出结果
4    4

4、条件函数

if函数

语法: IF(expr1,expr2,expr3)
返回值: T
说明:

当条件expr1为TRUE时，返回expr2；否则返回expr3
expr1 不能等于0也不能为空，否则会报语义异常（Error 10016）

select if(3 > 2,'true','false'),if(3 > 9,'1','2');

-- 输出结果
true    2

coalesce非空查找函数

语法: COALESCE(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select coalesce(null,'hive','hadoop'),coalesce(null,null,null);

-- 输出结果
hive    NULL

case条件判断函数

语法:

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
CASE WHEN aa THEN bb [WHEN cc THEN dd]* [ELSE ee] END

返回值: T
说明：

如果a等于b，那么返回（执行）c；如果a等于d，那么返回（执行）e；否则返回（执行）f
如果aa为TRUE，则返回（执行）bb；如果cc为TRUE，则返回（执行）dd；否则返回（执行）ee

select ename, sal, 
    case when sal <= 2000 then '列兵' 
        when sal <= 3000 then '上等兵' 
        when sal <= 4000 then '士' 
        when sal <= 5000 then '少尉' 
        else '上尉' 
    end 
from emp_test 
limit 5;

-- 输出结果
JAMES   1950.0  列兵
FORD    4000.0  士
MILLER  2300.0  上等兵
ZHANGS  4500.0  少尉
MARTIN  2250.0  上等兵



select case 10 
    when 2 then 'a' 
    when 1 then 'b' 
    when 3 then 'c' 
    else '找不到' 
end;

-- 输出结果
找不到

nvl空值转换函数

语法: nvl(T value,T default_value)
返回值: T
说明: 如果value为NULL，返回值为 default_value，否则返回value

select nvl(13,'value'),nvl(null,'空');

-- 输出结果
13    空

5、字符串函数

length：字符串长度

语法: length(string A)
返回值: int
说明：返回字符串A的长度

reverse：字符串反转

语法: reverse(string A)
返回值: string
说明：返回字符串A的反转结果

select length('hadoop and hive'),
    reverse('hadoop and hive');

-- 输出结果
15      evih dna poodah

concat、concat_ws：字符串连接（collect_set）

语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持输入任意个字符串

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

语法: COLLECT_SET(col)
返回值: array
说明：将某字段的值进行去重汇总，产生array类型字段。

select concat('hadoop',' and',' hive'),
    concat_ws('>','hadoop','and','hive')

-- 输出结果
hadoop and hive    hadoop>and>hive

select COLLECT_SET(ename) 
from emp_test
where mgr is null;

-- 输出结果
["KING","ZHANGS"]

其中emp_test中mgr为空的数据如下：

substr、substring：字符串截取

说明：substr与substring功能一样。

语法:

substr(string A, int start)
substring(string A, int start)

返回值: string
说明：返回字符串A从start位置开始，到字符串A结尾的字符串

语法:

substr(string A, int start, int len)
substring(string A, int start, int len)

返回值: string
说明：返回字符串A从start位置开始，截取长度为len的字符串

select substr('2022年3月10日16:56:56',11),
    substring('2022年3月10日16:56:56',11,5);

-- 输出结果
16:56:56        16:56

split：字符串分割函数

语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组

upper、lower：字符串大小写转换

说明：upper与ucase功能一致，lower与lcase功能一致

语法:

upper(string A)
ucase(string A)

返回值: string
说明：返回字符串A的大写格式

语法:

lower(string A)
lcase(string A)

返回值: string
说明：返回字符串A的小写格式

trim：字符串去除两边的空格函数

语法: trim(string A)
返回值: string
说明：去除字符串两边的空格

select upper('Hadoop And HIVE'),
    lcase('Hadoop And HIVE'),
    trim('  Hadoop And HIVE  ');

-- 输出结果
HADOOP AND HIVE     hadoop and hive     Hadoop And HIVE

regexp_replace：正则表达式替换函数

语法: regexp_replace(string A, string B, string C)
返回值: string
说明：

将字符串A中的符合java正则表达式B的部分替换为C
注意，在有些情况下要使用转义字符

select regexp_replace('hadoop and hive ad an','oo|ad|an','*'),
    regexp_replace('hadoop.and.hive.ad.an','\\.',' ')

-- 输出结果
h**p *d hive * *        hadoop and hive ad an

regexp_extract：正则表达式解析函数

语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明：

将字符串subject按照pattern正则表达式的规则拆分
返回index指定的字符

index是返回结果取表达式的哪一部分，默认值为1

0表示把整个正则表达式对应的结果全部返回

1表示返回正则表达式中第一个括号对应的结果，以此类推

index的数字不能大于表达式中括号的个数，否则会报错（Error 10014）

select regexp_extract('hadoopandhiveadan','(a)(.*?)(d)',0),
    regexp_extract('hadoopandhiveadan','(a)(.*?)(d)',1);


-- 输出结果
ad      a

如果查询select regexp_extract('hadoopandhiveadan','(a)(.*?)(d)',4)，则会报语义异常，如下：

FAILED: SemanticException [Error 10014]: Line 1:8 Wrong arguments '4': org.apache.hadoop.hive.ql.metadata.HiveException: Unable to execute method public java.lang.String org.apache.hadoop.hive.ql.udf.UDFRegExpExtract.evaluate(java.lang.String,java.lang.String,java.lang.Integer) on object org.apache.hadoop.hive.ql.udf.UDFRegExpExtract@443faa85 of class org.apache.hadoop.hive.ql.udf.UDFRegExpExtract with arguments {hadoopandhiveadan:java.lang.String, (a)(.*?)(d):java.lang.String, 4:java.lang.Integer} of size 3

parse_url：URL解析函数

语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明：

返回URL中指定的部分。
partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.（主机、路径、查询、引用、协议、文件、权限、用户信息）
如果没有值，返回NULL

select parse_url('https://mp.csdn.net/mp_blog/creation/editor/123372967?not_checkout=1&rsv_bp=6&rsv_n=518','HOST'),
    parse_url('https://mp.csdn.net/mp_blog/creation/editor/123372967?not_checkout=1&rsv_bp=6&rsv_n=518','USERINFO'),
    parse_url('https://mp.csdn.net/mp_blog/creation/editor/123372967?not_checkout=1&rsv_bp=6&rsv_n=518','QUERY'),
    parse_url('https://mp.csdn.net/mp_blog/creation/editor/123372967?not_checkout=1&rsv_bp=6&rsv_n=518','QUERY','rsv_n');


-- 输出结果
mp.csdn.net     NULL    not_checkout=1&rsv_bp=6&rsv_n=518       518

get_json_object：JSON解析函数

语法: get_json_object(string json_string, string path)
返回值: string
说明：

解析json的字符串json_string，返回path指定的内容。
如果输入的json字符串无效，那么返回NULL。
字段名严格区分大小写，如果大小写不匹配，那么返回NULL。path
path的写法
$ -->代表json的根对象
. -->子属性操作符
[] -->代表json_array的子脚本操作符
* --> []的通配符

-- 查询JSON中所有的area_id和第一条JSON的is_hot字段
select get_json_object('[{"PROVINCE":"天心区","CITY":"东塘街道办事处","AREA_ID":"1156430101001","PARENT_ID":"0","LEVEL":"1","info":[{"IS_HOT":"0","IS_SN":"1","IS_MAC":"0","IS_IP":"0","ADMIN_ID":"0","CREATED_AT":"2022-03-21"}]},{"PROVINCE":"湖南省","CITY":"长沙市","AREA_ID":"1156430100","PARENT_ID":"0","LEVEL":"1","info":[{"IS_HOT":"0","IS_SN":"1","IS_MAC":"0","IS_IP":"0","ADMIN_ID":"0","CREATED_AT":"2022-03-21"}]},{"PROVINCE":"长沙市","CITY":"天心区","AREA_ID":"1156430101","PARENT_ID":"0","LEVEL":"1","info":[{"IS_HOT":"0","IS_SN":"1","IS_MAC":"0","IS_IP":"0","ADMIN_ID":"0","CREATED_AT":"2022-03-21"}]}]','$.[*].AREA_ID'),
get_json_object('[{"PROVINCE":"天心区","CITY":"东塘街道办事处","AREA_ID":"1156430101001","PARENT_ID":"0","LEVEL":"1","info":[{"IS_HOT":"0","IS_SN":"1","IS_MAC":"0","IS_IP":"0","ADMIN_ID":"0","CREATED_AT":"2022-03-21"}]},{"PROVINCE":"湖南省","CITY":"长沙市","AREA_ID":"1156430100","PARENT_ID":"0","LEVEL":"1","info":[{"IS_HOT":"0","IS_SN":"1","IS_MAC":"0","IS_IP":"0","ADMIN_ID":"0","CREATED_AT":"2022-03-21"}]},{"PROVINCE":"长沙市","CITY":"天心区","AREA_ID":"1156430101","PARENT_ID":"0","LEVEL":"1","info":[{"IS_HOT":"0","IS_SN":"1","IS_MAC":"0","IS_IP":"0","ADMIN_ID":"0","CREATED_AT":"2022-03-21"}]}]','$.[0].info.IS_HOT')


-- 输出结果
["1156430101001","1156430100","1156430101"]     ["0"]

lpad、rpad：字符串补足函数

语法:

lpad(string str, int len, string pad)，
rpad(string str, int len, string pad)

返回值: string
说明：

lpad将str进行用pad进行左补足到len位
rpad将str进行用pad进行右补足到len位
注意：如果len的长度小于str，则返回缩短长度为len的str

select lpad('hive',6,'*'),rpad('hive',6,'*'),lpad('hive',2,'*')

-- 输出结果
**hive  hive**  hi

find_in_set：集合查找函数

语法: find_in_set(string str, string strList)
返回值: int
说明:

返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。
如果没有找到该str字符，则返回0

select find_in_set('hive','hadoop,and,hive,ad,an'),find_in_set('h','hadoop,and,hive,ad,an'),
	not find_in_set('h','hadoop,and,hive,ad,an')=0

-- 输出结果
3       0       false

6、集合统计函数

说明，mgr数据内容如下

cout：个数统计

语法:

count(*)、 count(expr)、count(DISTINCT expr[, expr_.])

返回值: int

说明:

count(*)统计检索出的行的个数，包括NULL值的行；
count(expr)返回指定字段的非空值的个数；
count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数

select count(*),count(mgr),count(distinct mgr) 
from emp_test

-- 输出结果
17      13      6

sum：总和统计

语法:

sum(col)，sum(DISTINCT col)

返回值: double

说明:

sum(col)统计结果集中col的相加的结果；
sum(DISTINCT col)统计结果中col不同值相加的结果

select sum(mgr),sum(distinct mgr) 
from emp_test;

-- 输出结果
100611  46575

avg：平均值统计

语法:

avg(col)，avg(DISTINCT col)

返回值: double

说明:

avg(col)统计结果集中col的平均值；
avg(DISTINCT col)统计结果中col不同值相加的平均值

select avg(mgr),avg(distinct mgr) 
from emp_test;

-- 输出结果
7739.307692307692       7762.5

min / max：最小值 / 最大值统计

语法:

min(col)、max(col)

返回值: double

说明:

min(col)统计结果集中col字段的最小值，
max(col)统计结果集中col字段的最大值

select min(mgr),max(mgr) 
from emp_test;

-- 输出结果
7566    7902

var_pop：非空集合总体方差函数

语法:

var_pop(col)

返回值: double

说明: 统计结果集中col非空集合的总体方差（忽略null）

select var_pop(mgr) 
from emp_test;

-- 输出结果
9929.289940828403

var_samp：非空集合样本方差函数

语法:

var_samp (col)

返回值: double

说明:

统计结果集中col非空集合的样本方差（忽略null）

select var_samp(mgr) 
from emp_test;

-- 输出结果
10756.73076923077

备注：stddev(col)和stddev_pop(col)都是统计结果集中非空集合的样本标准差

7、类型转换函数: cast

语法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
说明:

返回转换后的数据类型
如果转换失败，则返回NULL
type类型为hive内置类型，常见的类型有：
int、bigint、float、double、decimal
string、char
date

select cast ('20.990' as bigint),
    cast ('20.990' as int),
    cast ('20.990' as float),
    cast ('20.990' as decimal(5,2));

-- 输出结果
20      20      20.99   20.99


select cast('2022-3-9 18:12:53' as date),
    cast('2022-3-9 18:12:53' as double)

-- 输出结果
2022-03-09      NULL

三、复合类型操作

1、复合类型构建操作

Map

语法: map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型

Array

语法: array(val1, val2, …)
说明：根据输入的参数构建数组array类型

Struct

语法: struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类型

举例：

create table if not exists emp_extend(
empno_list array<int>,
id_name map<int, string>,
sal_info struct<ename:string,sal:double>
)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

备注：

ROW FORMAT DELIMITED FIELDS TERMINATED BY ：列的分隔符
COLLECTION ITEMS TERMINATED BY ：数据分隔符（Map、Struct和Array的分隔符）
MAP KEYS TERMINATED BY ：Map中key与value的分隔符
LINES TERMINATED BY : 行分隔符

向表中加载文件，查询结果如下：

2、复合类型访问操作

Map类型访问：M[key]

语法: M[key]

操作类型:

M为map类型
key为map中的key值

说明：返回map类型M中，key值为指定值的value值。

Array类型访问：A[n]

语法: A[n]

操作类型:

A为array类型
n为int类型

说明：返回数组A中的第n个变量值。数组的起始下标为0。

Struct类型访问：S.x

语法: S.x

操作类型: S为struct类型

说明：返回结构体S中的x字段。

select empno_list[2],id_name[7521],sal_info.sal
from emp_extend
where id_name[7521] is not null;

-- 输出结果
2       WARD    2250.0

3、复合类型长度统计

Array、Map类型长度函数

语法:

size(Map<k .V>)，size(Array<T>)
返回值: int

说明:

返回map类型的长度
返回array类型的长度

select size(empno_list),size(id_name)
from emp_extend

-- 输出结果
4       1
4       1
4       1
4       1
4       1
4       1

路漫漫其修远兮，吾将上下而求索。 ---屈原

烟雨蒋楠

关注

3
点赞
踩
22

收藏

觉得还不错? 一键收藏
2
评论
Hive常见查询操作与函数汇总

一、查询操作1、基本查询全表和特定列查询列别名算术运算符Limit语句2、Where语句比较运算符Like与RLike逻辑运算符3、Join语句表别名等值Join内连接左外连接右外连接满外连接多表连接迪卡尔积注意：连接谓词中不支持or4、分组Group By 语句Having语句5、排序全局排序（Order By）按照别名排序多个列排序内部排序（Sort By）分区排序（D...
复制链接

扫一扫