Hive函数 复杂函数_hive struct函数

1 解析url

假如有下面的url

'http://www.facebook.com/user/login.html?username=root&password=123456'
1

我们要获取到里面的host,path,query,username，可以使用hive的函数parse_url_tuple

select parse_url_tuple(
'http://www.facebook.com/user/login.html?username=root&password=123456',
'HOST',
'PATH',
'QUERY',
'QUERY:username'
)
1
2
3
4
5
6
7

如下图：查询出我们想要的信息了

但是字段名需要改成我们指定的

select parse_url_tuple(
'http://www.facebook.com/user/login.html?username=root&password=123456',
'HOST',
'PATH',
'QUERY',
'QUERY:username'
) as (host,path,query,username)
1
2
3
4
5
6
7

如下图，得到我们想要的信息了

2 row_number求topN

现在有上图的数据，现在需要查询出每个性别，年龄最大的两条记录
这里需要使用row_number和over配合使用，partition by是以gender进行分区

select id,age,name,gender,
row_number() over(partition by gender order by age desc) as r 
from hive_rownumber;
1
2
3

然后再把上面的查询作为子查询，条件是r<=2，就得到我们想要的结果了

select id,age,name,gender
from
(
select id,age,name,gender,
row_number() over(partition by gender order by age desc) as r 
from hive_rownumber
) t
where t.r <= 2;
1
2
3
4
5
6
7
8

如上图，得到我们想要的结果了

3 HiveServer2启动

bin/hiveserver2.sh &
1

通过客户端连接：

#-u为数据库地址 -n为用户
bin/beeline -u jdbc:hive2://hadoop001:10000/d7_hive -n hadoopadmin
1
2

连接之后，可以做一些操作了

4 复杂数据类型

官网地址：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableCreate/Drop/TruncateTable

4.1 array数组类型

有如下数据，我们需要导入到hive中

第一个字段是姓名，第二个字段是工作地点，工作地点可以用一个数组存放，首先创建数据库

create table hive_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
collection items terminated by ',';
1
2
3

上面的意思：
1.字段与字段用制表符 ‘\t’ 分开
2.集合每一元素用 ','分开

如上图，现在把准备好的数据导入hive

load data local inpath '/home/hadoopadmin/hive_array.txt' into table hive_array;
1

如上图，导入成功；下面我们查询看下数据是什么样子

题目1：查找第1个工作地点

select name,work_locations[0] from hive_array;
1

题目2: 查询每个人工作地点的数量

select name,size(work_locations) from hive_array;
1

题目3：查询在chengdu工作的有哪些人

select * from hive_array where array_contains(work_locations,'chengdu');
1

4.2 map类型

有如下数据，我们需要把数据导入到Hive

先分析一下数据，用逗号’’,’'分开的为每一个字段，第1个字段为id，第2个字段为姓名，第3个字段为亲属关系，第4个字段为年龄；其中第三个字段里用"#"号分开的为每一个亲属以及姓名。
首先要创建表

create table hive_map(id int, name string, relative map<string,string>, age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';
1
2
3
4

说明：
1.逗号",“分开的为字段
2.符号“#”分开的为每一个集合的元素
3.冒号”:"分开的为每一个元素里的key和value

把数据导入hive表中

load data local inpath '/home/hadoopadmin/hive_map.txt' into table hive_map;
1

查询hive表的数据的数据

题目1:查询出每个人的father的名字

select id,name,age,relative['father'] as father from hive_map;
1

题目2:查询出每个人的father和brother的名字

select id,name,age,relative['father'] as father,relative['brother'] as brother
from hive_map;
1
2

题目3:查询出每个人的家庭成员（也就是每个key，不要value）

select id,name,age,map_keys(relative) as members from hive_map;
1

题目4:查询出每个人的家庭成员的名字

select id,name,age,map_values(relative) as member_name from hive_map;
1

题目5:查询出有兄弟的人，以及兄弟的名称

select id,name,age,relative['brother'] as brother 
from hive_map
where relative['brother'] is not null;
1
2
3

或

select id,name,age,relative['brother'] as brother 
from hive_map
where array_contains(map_keys(relative),'brother');
1
2
3

4.3 结构体struct类型

需要将下面的数据导入到hive中

我们先分析下上面的数据，字段与字段之间用#分开，右边的数据是姓名和年龄；那么我们先创建一个表

create table hive_struct(ip string, userinfo struct<name:string, age:int>)
row format delimited fields terminated by '#'
collection items terminated by ':';
1
2
3

把本地数据导入hive_struct表中

load data local inpath '/home/hadoopadmin/hive_struct.txt' into table hive_struct;
1

查询数据如下

结构体类型查询数据就相当简单了：

select ip, userinfo.name, userinfo.age from hive_struct;
1