HiveSQL中复杂数据类型操作

 

HiveSQL运行优化参数配置

HiveSQL常用数据处理语句

HiveSQL中复杂数据类型操作

 

Hive有三种复杂数据类型ARRAY、MAP和STRUCT，复杂数据类型允许任意层次的嵌套。

目录

array类型

map类型

struct类型

---

array类型

name与locations之间制表符分隔，locations中元素之间逗号分隔，数据样本集为：

zhangsan      beijing,shanghai,tianjin,hangzhou
lisi                 changchu,chengdu,wuhan,beijing

建表语句

create table hive_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

导入数据

load data local inpath '/home/zhangfei/data/work_locations.txt' overwrite into table hive_array;

常用查询

select * from hive_array;

zhangsan      ["beijing","shanghai","tianjin","hangzhou"]
lisi                 ["changchu","chengdu","wuhan","beijing"]

array_contains常与where子句连用

select name, work_locations[0] location from hive_array;
--取array的第一个元素  work_locations[0]，同样是采用下标的方式，下标从0开始

select name, size(work_locations) location from hive_array;
--取array的长度size(work_locations)

select * from hive_array where array_contains(work_locations,'tianjin');
--取判断为ture的数据 array_contains(work_locations,'tianjin')

select explode(work_locations) from hive_array ；
--explode()会将数组元素展开展示
注意：explode()函数只是生成了一个数据的展示方式，无法在表中产生一个新的数据列，即select name,explode(work_locations) from hive_array 会报错的

map类型

数据格式

字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k-v分隔符：":"

zhangsan,  father:xiaoming     #mother:xiaohuang  #brother:xiaoxu,28
lisi,             father:mayun         #mother:huangyi     #brother:guanyu,22
wangwu,    father:wangjianlin  #mother:ruhua        #sister:jingtian,29
mayun,      father:mayongzhen#mother:angelababy,26

建表语句

create table hive_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#'  ---条目分隔符
MAP KEYS TERMINATED BY ':'
;  
---k-v分隔符

导入数据

load data local inpath '/home/zhangfei/data/hive_map.txt' overwrite into table hive_map;

常用查询

select * from hive_map;

zhangsan        {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"}   28
lisi                   {"father":"mayun","mother":"huangyi","brother":"guanyu"}        22
wangwu          {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"}   29
mayun            {"father":"mayongzhen","mother":"angelababy"}   26

查询语句

select id, name, members['father'] father, members['mother'] mother, age from hive_map;
select id, name, map_keys(members) as relation from hive_map;
select id, name, map_values(members) as relation from hive_map;
select id,name,size(members) num from hive_map;
select * from hive_map where array_contains(map_keys(members), 'brother');
select id,name, members['brother'] brother from hive_map where array_contains(map_keys(members), 'brother');

struct类型

数据格式

说明：字段之间#分割，第二个字段之间冒号分割

192.168.1.1 # zhangsan:40
192.168.1.2 # lisi:50
192.168.1.3 # wangwu:60
192.168.1.4 # zhaoliu:70

建表语句

create table hive_struct(
ip string, info struct<name:string, age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';

导入数据

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

常用查询

select * from hive_struct;

ip                      info
192.168.1.1     {"name":"zhangsan","age":40}
192.168.1.2     {"name":"lisi","age":50}
192.168.1.3     {"name":"wangwu","age":60}
192.168.1.4     {"name":"zhaoliu","age":70}

可直接通过.访问数据

select ip, info.name from hive_struct;

192.168.1.1     zhangsan
192.168.1.2     lisi
192.168.1.3     wangwu
192.168.1.4     zhaoliu