Hive复杂数据类型 struct

简介

struct类型，类似于java编程语言中对象实例的模板，即类的结构体。如地址类型的结构体:

public class Address{
	String provinces;
	String city;
	String street;
	.......
}

使用struct类型来定义一个字段的类型，语法格式为:

create table tableName(
........
colName struct<subName1:Type,subName2:Type,........>
........
)

调用语法：

colName.subName

案例演示：

1）数据准备
这个数据先前可以用array处理,在这里也可以用Struct处理

zhangsan	90,87,63,76
lisi	60,30,78,0
wangwu	89,25,81,9

create table if not exists struct1(
    name string,
    score struct<chines:int,math:int,english:int,nature:int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';
load data local inpath '/data/arr1.txt' into table struct1;
select * from struct1;

result

+--------+----------------------------------------------------+
|name    |score                                               |
+--------+----------------------------------------------------+
|zhangsan|{"chines":78,"math":89,"english":92,"nature":96}    |
|lisi    |{"chines":67,"math":75,"english":83,"nature":94}    |
|王五      |{"chines":23,"math":12,"english":null,"nature":null}|
+--------+----------------------------------------------------+

**2）需求：**查询数学大于35分的学生的英语和语文成绩：

select name,
       score.english,
       score.chines
from struct1
where score.math > 35
+--------+-------+------+
|name    |english|chines|
+--------+-------+------+
|zhangsan|92     |78    |
|lisi    |83     |67    |
+--------+-------+------+

综合案例演示

1）数据准备

-- 主管信息表如下：
manager(uid uname belong tax addr)

-- 数据如下：
1	xdd	ll,lw,lg,lc,lz	wx:600,gongjijin:1200,shebao:450	北京,西城,中南海
2	lkq	lw,lg,lc,lz,lq	wx:1000,gongjijin:600,shebao:320	河北,石家庄,中山路
3	zs	lw,lg,lc	    wx:2000,gongjijin:300,shebao:10	    江西,南昌,八一大道

drop table manager;
create table manager (
    uid int,
    uname string,
    belong array<string>,
    tax map<string,int>,
    addr struct<province:string,city:string,street:string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
load data local inpath '/data/manager.txt' into table manager;

select * from manager;

+---+-----+--------------------------+-------------------------------------------+-------------------------------------------------+
|uid|uname|belong                    |tax                                        |addr                                             |
+---+-----+--------------------------+-------------------------------------------+-------------------------------------------------+
|1  |xdd  |["ll","lw","lg","lc","lz"]|{"wx":600,"gongjijin":1200,"shebao":450}   |{"province":"北京","city":"西城","street":"中南海"}     |
|2  |lkq  |["lw","lg","lc","lz","lq"]|{"wx":1000,"gongjijin":600,"shebao":320}   |{"province":"河北","city":"石家庄","street":"中山路"}    |
|3  |zs   |["lw","lg","lc"]          |{"    wx":2000,"gongjijin":300,"shebao":10}|{"province":"    江西","city":"南昌","street":"八一大道"}|
+---+-----+--------------------------+-------------------------------------------+-------------------------------------------------+

**2）查询需求：**下属个数大于4个，公积金小于1200，省份在河北的数据

select *
from manager
where size(belong)>4
and tax['gongjijin']<2000
and addr.province='河北'
+---+-----+--------------------------+----------------------------------------+---------------------------------------------+
|uid|uname|belong                    |tax                                     |addr                                         |
+---+-----+--------------------------+----------------------------------------+---------------------------------------------+
|2  |lkq  |["lw","lg","lc","lz","lq"]|{"wx":1000,"gongjijin":600,"shebao":320}|{"province":"河北","city":"石家庄","street":"中山路"}|
+---+-----+--------------------------+----------------------------------------+---------------------------------------------+

**扩展说明）**嵌套数据类型

举例说明：map<string,array<map<string,struct>>>
嵌套类型：所有元素分隔符都要自己去定义

默认分隔符：^A	对应输入方式:ctrl+V ctrl+A    

总结

• 关键看源文件中某个字段的数据特征,因为并不是每个字段都是比较简单的类型,诸如string之类,如果某个字段类型比较复杂,就可以考虑应用hive中的array,map,struct等类型进行处理
• map取值用[] struct取值用.