【头歌】Hive内置函数 - 详解

【提示】点击每一关参考答案可以快速复制。

目录

第1关：函数的查询、描述和调用

任务描述

相关知识

编程要求

测试说明

参考答案

第2关：Hive标准函数

任务描述

相关知识

编程要求

测试说明

参考答案 

第3关：Hive聚合函数

任务描述

相关知识

编程要求

测试说明

参考答案

第4关：Hive日期函数

任务描述

相关知识

编程要求

测试说明

参考答案

第5关：表生成函数

任务描述

相关知识

编程要求

测试说明

参考答案

第6关：分组排序取TopN

任务描述

相关知识

编程要求

测试说明

参考答案

---

第1关：函数的查询、描述和调用

任务描述

本关任务：查一下add函数的用法，并尝试使用它。

相关知识

本关我们将学习Hive中函数的基本用法。

函数的查询

Hive中的函数比较多，我们不需要记住所有函数的意义和用法。当需要使用某个函数的时候，可以直接在Hive交互界面查询。

首先，在命令行输入hive进入交互模式，如下： （以下所有图片中的红色箭头均表示用户的输入）

成功进入后如下：

查看Hive的所有函数

查看所有函数的命令是：

show functions

结果如下：

当然，结果非常多，上图只展示了极少的部分。

查看某个函数的含义和用法

以上图中的abs函数为例子： 查看函数含义：

desc function abs

结果如下：

上面的英文的意思是：abs函数的含义是返回x的绝对值。

我们还可以看下这个函数使用的例子：

desc extended function abs

到这里，这个函数是干什么的、以及怎么用，就一目了然了。

函数的调用

上面给出了函数具体使用的例子，但是我们毕竟没有亲自用过，俗话说“实践出真知”，那就试用下abs函数。

一些不得不做的准备工作

首先，例子里给的src其实是一个表，所以我们需要先建一个表，名字就叫test_table吧，没必要和例子一模一样：

create table test_table(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;

然后，往表里插入一些数据，要不然select查不出任何东西。

hive没有insert关键字，插入数据需要从磁盘中导入。在当前命令行所在的目录下新建一个a.txt文件，内容如下：

一行内的分隔符是\t，这个分隔符我们在建表的时候实际上已经指定了。

然后执行下面的语句就导入了：

load data local inpath 'a.txt' into table test_table;

通过

select * from test_table

看下有没有导入成功：

我这里是成功了，如果没有成功，你得重新尝试。

开始实践

一番折腾后，现在，可以来试试abs函数了：

select abs(0) from test_table limit 1;

看下输出：

没错，结果是0。

再来看下负数的abs，如下：

select abs(-2) from test_table limit 1;

输出：

这一关是教大家如何去了解并实践一个函数，就不再给例子了，需要大家多多尝试。

编程要求

根据提示，在右侧编辑器Begin和End之间补充代码，使用Hive中的ceil函数求数字66666的ceil的结果，我们已经准备了表test_table，而且表中已经有若干的数据。

测试说明

平台会执行你补充的sql文件，然后将输出和正确的结果进行对比，从而判断你的程序是否正确。

无输入，正确的输出如下：

66666

参考答案

#代码文件


drop table if exists test_table;
create table test_table(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;
load data local inpath 'step1/data.txt' into table test_table;

--------- Begin ---------
select ceil(66666) from test_table limit 1;
--------- End ---------

---

第2关：Hive标准函数

任务描述

本关任务：按要求查询数据。

相关知识

本关将学习Hive中的一些最基本的函数。

先准备一些数据

我们需要先准备一些数据，使用的表还是第一关中介绍的test_table表，向其中导入如下的数据，导入方法见第一关。

第一列对应test_table表的id，第二列对应name。

关系函数

关系函数作用在where条件子句中，用来筛选符合要求的列。

相等关系

判断两个值是否相等使用=即可，如下，查找id等于15的数据：

select * from test_table where id=15;

结果如下： （以下所有截图中的红色箭头均为后期添加，表示运行的结果）。

查找名字为tom的数据：

select * from test_table where name='tom';

字符串类型必须放在单引号中。

结果如下：

不等关系

不等于使用<>来表示，比如查找所有id不等于100的数据：

select * from test_table where id<>100;

结果如下：

其它的诸如大于、小于、大于等于，符号和数学基本相同，用法和上面相同。

like比较

对于字符串来说，很多时候还需要用到正则表达式的比较，在hive中通过like关键字来实现：

select * from test_table where name like 't%';

表示查找名字以t开头的记录，其结果如下：

%表示匹配任意数量的字符串，而_表示匹配单个字符串。

算数运算

Hive中还可以执行算数运算，如下是加法：

select 1+2 from test_table limit 1;

结果如下：

还可以是位运算，比如按位与：

select 4&8 from test_table limit 1;

结果如下：

编程要求

在右侧的编辑器的Begin和End之间补充代码，查询表test_table中所有的name不等于jerry的记录。表中的数据和上面开始准备的数据完全相同。

测试说明

平台会执行你补充的sql文件，然后查看运算结果是否符合正确的结果。

没有输入，正确的输出如下：

13 tom
12 tom
14 tom
100 tom
1 tom

参考答案 

#代码文件


drop table if exists test_table;
create table test_table(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;
load data local inpath 'step2/data.txt' into table test_table;

---- 求名字不等于jerry的所有记录 ----
--------- Begin ---------

select * from test_table where name<>'jerry';

--------- End ---------

---

第3关：Hive聚合函数

任务描述

本关任务：求id大于12的所有行的id的和。

相关知识

本关将学习Hive中的聚合函数。什么是聚合函数，简单来说，就是求一组数据的最大值、最小值、数量、平均值等反映数据集体特征的函数。

下面我们一个一个来学习。

先准备一些数据

我们需要先准备一些数据，使用的表还是第一关中介绍的test_table表，向其中导入如下的数据，导入方法见第一关。

第一列对应test_table表的id，第二列对应name。

计数函数count

常规用法

count用来统计select语句查出来的记录的数量，比如，统计test_table表当前的数据的数量：

select count(1) from test_table;

这里的语法很简单，select表示查询数据，from后面跟着你要查询的表的名字。如果只想查询指定的行，还可以使用where来控制查询条件。

运行结果如下： (本关的所有图片中，红色箭头均指向输出，下不赘述)

可以看到，结果正确。

这里的count和mysql中的count原理完全不同，它使用的是Hadoop中的MapReduce计算模型。这就是为什么安装Hive之前必须得安装Hadoop。

需要特别提醒的是，如果你是在Windows系统下使用的hive，需要用管理员身份打开hadoop下的sbin目录下的start-all.cmd。否则会报错。

求指定的列

上面说了，我们可以使用where来控制查询指定的列，比如我们只想知道id大于10的列的数量，则命令如下：

select count(1) from test_table where id>10;

结果如下：

本关的剩余部分介绍的所有函数，都可以使用where子句来查询指定的行，篇幅限制，我们不再给每一个函数提供where的例子。

求最大值和求最小值

求年龄字段的最大值的命令如下：

select max(id) from test_table;

求年龄字段的最小值的命令如下：

select min(id) from test_table;

这里的语法也很简单，max和min分别是求最大和求最小的关键字，括号里面是要求的列。

运行结果如下： 求最大：

求最小：

求均值

求均值的命令如下：

select avg(id) from test_table;

avg是求均值关键字，括号里是需要被计算均值的列的名字。

结果如下：

我们可以手工演算一下，结果正确。

求和

我们还可以求所有的行中，同一列的和，命令如下：

select sum(id) from test_table;

它的运行结果如下：

分组聚合函数group by

有的时候，我们需要对数据分组，再查它的聚合特征，比如下面的数据：

我们要查name分别为tom和jerry的所有id的和，显然，可以使用where先查tom，再去查jerry，但是有更简单的办法：

select sum(id),name from test_table group by name;

group by name的意思是先使用name字段将所有的数据分为两组：name为tom的为一组，name为jerry的为一组，然后对这两组，分别执行求和操作。

结果如下：

可以看到，我们一次性求出了tom和jerry旗下的id的和。

编程要求

在右侧的编辑器的Begin和End之间补充代码，求表test_table中所有的id大于12的记录的id的和。表中的数据如下：

测试说明

平台会执行你补充的sql文件，然后将输出和正确的结果进行对比，从而判断你的程序是否正确。

无输入，正确的输出如下：

171

参考答案

#代码文件


drop table if exists test_table;
create table test_table(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;
load data local inpath 'step3/data.txt' into table test_table;

---- 求id大于12的记录的id值的和 ----
--------- Begin ---------

select sum(id) from test_table where id > 12;

--------- End ---------

---

第4关：Hive日期函数

任务描述

本关任务：将指定的时间戳转换为普通的时间。

相关知识

本关我们将学习Hive中日期函数的基本用法。所谓日期函数，即用来进行日期计算的函数。

使用的表还是上面几关中出现了很多次的test_table。

将时间戳转换为普通时间

时间戳：从1970年1月1日0点0分0秒距指定时间的秒数。比如1463308943指的实际上是2016年5月15号18点42分23秒。就是说这两个时间之间相距1463308943秒钟。

转换命令如下：

select from_unixtime(1463308943,'yyyyMMdd HH:mm:ss') from test_table limit 1;

其中yyyy表示结果中的年份将以2016这种四位数的形式展现，MM表示月份以05这种两位数的形式展现，dd表示两位数的天，HH表示24小时制，mm和ss分别表示分钟和秒钟以两位数的形式展示。

运行结果如下：

普通时间转时间戳

接着上面的讨论，怎么反过来将普通时间转为时间戳：

select unix_timestamp('20210606 13:01:03','yyyyMMdd HH:mm:ss') from test_table;

同样，这里需要说明普通时间的格式。

最终的转换结果如下：

求当前时间

获取当前的时间戳的命令如下：

select unix_timestamp() from test_table;

它的输出如下：

计算相差天数

我们还可以使用Hive计算两个时间相差的天数：

select datediff('2019-12-08','2012-05-09') from test_table;

结果是：

对时间进行加或者减

对指定的日期减去一天：

select date_sub('2021-06-06',1) from test_table;

结果如下：

指定日期加上100天：

select date_add('2021-06-06',100) from test_table;

结果如下：

编程要求

根据提示，在右侧编辑器Begin和End之间补充代码，将时间戳1463308946转换为类似2012-02-02 02:02:02格式的普通时间。

我们已经在系统中创建了表test_table以供执行sql。

测试说明

平台会执行你补充的sql文件，然后将输出和正确的结果进行对比，从而判断你的程序是否正确。

无输入，正确的输出如下：

2016-05-15 10:42:26

参考答案

#代码文件


drop table if exists test_table;
create table test_table(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;
load data local inpath 'step4/data.txt' into table test_table;

---- 将1463308946转换为格式类似 2012-02-02 02:02:02 的普通时间 ----
--------- Begin ---------

select from_unixtime(1463308946,'yyyy-MM-dd HH:mm:ss') from test_table limit 1;

--------- End ---------

---

第5关：表生成函数

任务描述

本关任务：修改指定表的字段的名字。

相关知识

本关，我们将学习在Hive中如何创建表、更新表和删除表。

创建表

表是Hive中的基本单位，一个表用来表示现实世界中的一类事物，比如学生表、教师表、课程表等。

表由若干个字段组成，每一个字段有自己的名字和类型。

比如学生表有名字为name的字段，表示学生的名字，它的类型是string，即字符串。我们的hive是在mysql数据库的基础上使用的，它可以用的字段类型如下所示：

mysql	hive	含义
varchar	string	字符串
int	int	4Byte有符号整数
bigint	bigint	8Byte有符号整数
tinyint	tinyint	1Byte有符号整数
float	float	单精度浮点数
timestamp	timestamp	时间

建立一个学生表：学生名字，字符串；学生年龄，整数；出生日期：时间戳。

则命令如下：

create table student(name string, age int, birth timestamp);

语法也很简单，create关键字后面跟着表的名字，括号里面是字段，字段第一部分是名字，后面是类型，空格隔开。

运行之后，可以通过：

desc student;

看下我们的表有没有建好，如下：

这样的输出就表示表建好了。

删除表

全部删除

可以通过drop关键字来删除表，这样表里的数据和表本身都会被删除：

drop table if exists student;

怎么验证删除是否成功，老办法，还是用desc student;命令，结果如下：

报错了，找不到表student，所以删除成功了。

只删除数据

还可以通过关键字truncate删除表中的全部数据，但是保留表本身：

truncate table if exists student;

这里我们不给演示的例子了，大家可以自己试试，记得尝试之间要先往表里导入一些数据。

调整表的结构

建立好的表，还可以修改其中的字段或者表的名字。

改名字

将student表名字改为stud：

alter table student rename to stud;

看下现存的所有的表：

show tables;

如下：

所以，名字改成功了！

改字段

将stud中的birth的名字改为birthday：

alter table stud change column birth birthday timestamp;

其中column后面跟着的依次是原来的字段的名字、新的字段名、新的字段类型。

看下结果：

编程要求

在右侧的编辑器的Begin和End之间补充代码，将表test_table的字段id的名字改为age，类型不变。

测试说明

平台会执行你补充的sql文件，然后将输出和正确的结果进行对比，从而判断你的程序是否正确。

无输入，正确的输出如下：

age int
name string

参考答案

#代码文件


drop table if exists test_table;
create table test_table(id int,name string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;
---- 把列id的名字改为age，类型不变----
--------- Begin ---------

alter table test_table change column id age int;

--------- End ---------
desc test_table;

---

第6关：分组排序取TopN

任务描述

本关任务：编写一个分组排序的Hive。

相关知识

本关将学习在Hive中如何对数据进行分组和排序。

我们使用的表的结构如下所示：

这三列分别表示名字、学科和成绩。

我们插入的数据如下所示：

每一行从左到右分别是名字、学科和成绩。

使用row_number的分组排序

不同于传统的mysql语法，在hive中可以使用row_number关键字同时完成分组和排序，它的语法如下所示：

row_number() over (partition by column_a order by column_b)

含义是先根据列column_a进行分组，然后根据列column_b进行排序。我们先来看下排序。

先排序

对于上面的数据，我们先按照成绩排序：

select *, row_number() over (order by score) as rn from student_grades;

结果如下：

再分组

然后我们再按照学科分组，按照成绩排序：

select *, row_number() over (partition by course order by score) as rn from student_grades;

看下结果：

结果正确，我们用不同颜色的框把不同的学科标示出来了。

特别注意下，每个分组后面还有一个序号，这个后面会用到。

取TopN

那么，如何取TopN呢，比如我们只想知道每一个学科的前两名和成绩，则命令如下：

select * from
(
select
*, row_number() over (partition by course order by score desc) as rn
from student_grades
)
temp_table
where temp_table.rn <=2;

这个sql看起来复杂，其实也好理解。

首先，括号里面先执行，结果就是我们上面看到的，总共有四列，第四列的名字就是rn；

然后我们把这个运算结果看作一个新的表temp_table，使用select从这个新的表里进行选择，并且指定列rn的值小于等于2，这个实际上就取出了前两名。

看下结果：

编程要求

根据提示，在右侧编辑器Begin和End之间补充代码，实现对本例中开头部分给出的数据，按照name分组，然后select出前两名的全部信息以及序号（row_number()生成的数字）。

测试说明

平台会执行这一条命令，将获得的输出和正确的输出进行对比，从而判断你的程序是否正确。

测试输入：（无）； 预期输出：

kaths math 96 1
kaths english 87 2
lrry math 69 1
lrry english 67 2
xiaoming english 100 1
xiaoming math 72 2

输出首先按照名字分组，然后取出这个名字下的成绩的前两名。

参考答案

#代码文件


drop table if exists student_grades;
create table student_grades(name string, course string, score int) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile;
load data local inpath 'step6/data.txt' into table student_grades;
---- 按name分组，取出前两名的信息----
--------- Begin ---------

select * from 
(
    select 
    *, row_number() over (partition by name order by score desc) as rn
    from student_grades
) 
temp_table
where temp_table.rn <=2;

--------- End ---------

---

至此，所有内容都完成辣。如果存在任何问题欢迎大佬指教🥰！