Hive作为一个SQL接口的分析引擎,也支持复杂类型,像Map,Array等,这些知识点不复杂,日常也会比较常用,作为零散笔记记录一下。

Map数据结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
--创建
create table temp_db.map_test(
id int comment "源数据主键id",
smap map<string,string> comment "string型map",
imap map<string,int> comment "int型map"
);

--插入
insert into temp_db.map_test(id,smap,imap) select 12,map('姓名','张三') as pp,map('年龄',23,'性别',1) as dd;
insert into temp_db.map_test(id,smap,imap)select 14,map('地址','安徽') as dd,map('年级',3);

--注意,这里的map引用使用"()",有时候会错误写成"{}";此外,对于key-value值来说,是没有特定的限制的。key可以有多个。如上"姓名","地址"

--查询
select smap['姓名'] as arg1,imap['年龄'] as age from temp_db.map_test;

--key键查询
--map_keys(colName) 结果是一个Array,如果希望提取,则使用[index],如map_keys(smap)[0]
--hive和prest的index起点存在差异,hive从0开始,presto从1开始【我测试的环境是这样的】
select map_keys(smap) as smap_keys,map_keys(imap) as imap_keys from temp_db.map_test;


--value值查询
--map_values(colname)
select map_values(smap) as s_values,map_values(imap) as i_values from temp_db.map_test;


--键值对查询
--size(colName),返回对应列有多少个key-value
select size(imap) as pair_cnt from temp_db.map_test;

--map类型数据的加工,将map列拆分为key、value列

-- smap中只存在单个key-value的情况,所有lateral之后,数据有单列变成双列。但是行数没有变化
select id,skey,svalue from temp_db.map_test lateral view explode(smap) tb as skey,svalue;

-- imap中 存在多个键值对。这顿操作之后,行数会增加
select id,ikey,ivalue from temp_db.map_test lateral view explode(imap) tb as ikey,ivalue;

Array数据结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--创建
create table temp_db.array_test(
id int comment '源数据主键id',
year_arr array<string> comment '数组记录,年份',
score_arr array<string> comment '数组记录,分数'
);

--插入
insert into temp_db.array_test (id,year_arr,score_arr) select 12,array('1991','1990','1989'),array('56','20','23')

--查询
-- 注意事项,如果数组越界了,则报错。
select id,year_arr[1],year_arr[2] from temp_db.array_test

--是否包含某个值(array_contains()),Boolean型(true/false,where条件中比较合适)
select * from temp_db.array_test where array_contains(year_arr,'1990');

--拆成单条多行记录
select col1 from temp_db.array_test lateral view explode(year_arr) tb as col1

Json数据结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
--创建
create table temp_db.json_test
(id int comment '源数据库id主键',
str string comment '日志字符串');

--插入
insert into temp_db.json_test(id,str)
values (1,'{"name":"孙先生","carrer":"大数据开发工程师","dream":["开个便利店","去外面逛一逛","看本好书"],"friend":{
"friend_1":"MM",
"friend_2":"NN",
"friend_3":"BB",
"friend_4":"VV"
}
}');
insert into temp_db.json_test(id,str)
values (2,'{"name":"唐女士","carrer":"退休农民","dream":["儿子听话","带孙子"],"friend":{
"friend_1":"CC"
}
}');

--json_tuple提取数据
-- 提取一级格式下的数据
select name
from temp_db.json_test
lateral view json_tuple(str,'name') tb as name;

-- 提取二级格式下的数据(如好友1)
select good_friend_1
from temp_db.json_test
lateral view json_tuple(str,'friend') dd as good_friend
lateral view json_tuple(good_friend,'好友1') tb as good_friend_1;

-- 提取标签中所有的内容(没有的标签,返回null)
select good_friend_1,good_friend_2,good_friend_3
from temp_db.json_test
lateral view json_tuple(str,'friend') dd as good_friend
lateral view json_tuple(good_friend,'好友1','好友2','好友3') tb as good_friend_1,good_friend_2,good_friend_3;

-- 提取Array
select dream_col
from temp_db.json_test
lateral view json_tuple(str,'dream') dd as dreaming
lateral view explode(dreaming) tb as dream_col

--get_json_object提取指定的json元素内容(使用"$"的方式,"."表示对象,"[]"引用数组)
-- 获取标签对象
select get_json_object(str,'$.name') as name
from temp_db.json_test;

-- 获取标签中的数组元素
select get_json_object(str,'$.dream[0]') as good_friend
from temp_db.json_test;

-- 获取多层中的对象
select get_json_object(str,'$.friend.friend_1') as good_friend
from temp_db.json_test;

json_tuple与get_json_object都是hive自带的UDF。json_tuple 相对于 get_json_object 的优势就是一次可以解析多个 Json 字段。


扫码手机观看或分享: