hive 操作复杂结构数据 array，map，struct，json

use test03;

//array类型
create table person(
name string,
work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

//导入数据
LOAD DATA LOCAL INPATH '/data/log/fuzha_1.txt' OVERWRITE INTO TABLE person;

select * from person
//返回的结果
//biansutao    ["beijing","shanghai","tianjin","hangzhou"]
//linan    ["changchu","chengdu","wuhan"]

//在字段类型为array中查找是否 包含 ,不包含某元素
select * from person where array_contains(work_locations,'beijing')
select * from person where !array_contains(work_locations,'beijing')

//根据序号来取
select name,work_locations[0],work_locations[1],work_locations[2],work_locations[3] from person

//map类型
create table score(name string, fenshu map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

//导入数据
LOAD DATA LOCAL INPATH '/data/log/fuzha_2.txt' OVERWRITE INTO TABLE score;

select * from score
//返回的结果
//biansutao    {"shuxue":80,"yuwen":89,"english":95}
//jobs    {"yuwen":60,"shuxue":80,"english":99}

//根据key查询
select name,fenshu['shuxue'] from score

//根据key查询 并判断
select if(fenshu['shuxue'] is null, 0, fenshu['shuxue']) from score

//用UDTF把结果变成多行
select explode(fenshu) from score

//注意，Explode单独使用只能单个字段，如果要和别的字段一起使用必须使用lateral view explode
select name,dekey,devalue
from score
LATERAL VIEW explode(fenshu) dedView as dekey,devalue
//返回结果
//biansutao    shuxue    80
//biansutao    yuwen    89
//biansutao    english    95
//jobs    yuwen    60
//jobs    shuxue    80
//jobs    english    99

//取map字段的全部key和value
select name,map_keys(fenshu),map_values(fenshu) from   score
//返回结果
//biansutao    ["shuxue","yuwen","english"]    [80,89,95]
//jobs    ["yuwen","shuxue","english"]    [60,80,99]

//查看map长度即有多少键值对
select size(fenshu)from score

//struct类型
CREATE TABLE if not exists student(id int,course struct<kemu:string,chenji:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

//导入数据
load data local inpath '/data/log/fuzha_3.txt' overwrite into table student

//查询
select id,course.kemu,course.chenji from student

 

//json类型数据
create table json(data string)

//导数据
load data local inpath '/data/log/json_1.txt' overwrite into table json

//根据key取每个，查询
select get_json_object(data,'$.movie'),get_json_object(data,'$.rate'),get_json_object(data,'$.timeStamp'),get_json_object(data,'$.uid') from json;

//查询 json_tuple时，可以显著提高效率
select json_tuple(data,'movie','rate','timeStamp','uid') from json

//查询 ，列取别名 需要使用lateral view 视图方法来写
select a.* from json lateral view json_tuple(data,'movie','rate','timeStamp','uid') a as f1,f2,f3,f4;