1、简单描述

• arrays: ARRAY<data_type>

• maps: MAP<primitive_type, data_type>

• structs: STRUCT<col_name : data_type [COMMENT col_comment], ...>

• union: UNIONTYPE<data_type, data_type, ...>

Hive 中对该类型的完全支持仍然不完整。如果 JOIN、WHERE 和 GROUP BY 子句中引用的 UNIONTYPE 字段的查询将会失败，Hive 没有定义语法来提取 UNIONTYPE 的 tag 或 value 字段。

复杂数据类型的构造函数：

注：create_union 中的 tag 让我们知道 union 的哪一部分正在被使用。

复杂数据类型访问元素：

2、测试

-- ------------------------------ ARRAY ------------------------------

-- ARRAY<data_type>
create table arraytest (id int,info array<string>) 
row format delimited 
fields terminated by '\t'
collection items terminated by ',' 
stored as textfile;

-- 不要忽略`collection items terminated by ',' 
-- 它表示数组元素间的分隔符
-- 如果忽略了输出是这样的：
hive> select * from arraytest;
OK
1       ["zhangsan,male"]
2       ["lisi,male"]

-- 数据 
1	zhangsan,male
2	lisi,male

-- 导入
load data local inpath '/root/data/arraytest.txt' into table arraytest;

-- 查看
hive> select * from arraytest;
OK
1       ["zhangsan","male"]
2       ["lisi","male"]

-- 索引查看数组元素
hive> select id,info[0] from arraytest;
OK
1       zhangsan
2       lisi

-- 将数组的所有元素展开输出
hive> select explode(info) from arraytest;
OK
zhangsan
male
lisi
male

-- ------------------------------ MAP ------------------------------

-- MAP<primitive_type, data_type>
create table maptest (id int,info map<string,string>) 
row format delimited 
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':' 
stored as textfile;

-- 不要忽略`map keys terminated by ':' 
-- 它表示键值间的分隔符

-- 数据 
1	name:zhangsan,sex:male
2	name:lisi,sex:male

-- 导入
load data local inpath '/root/data/maptest.txt' into table maptest;

-- 查看
hive> select * from maptest;
OK
1       {"name":"zhangsan","sex":"male"}
2       {"name":"lisi","sex":"male"}

-- 查看map元素
hive> select id,info["name"] from maptest;
OK
1       zhangsan
2       lisi


-- ------------------------------ STRUCT ------------------------------

-- STRUCT<col_name : data_type [COMMENT col_comment], ...>
create table structtest (id int,info struct<name:string,sex:string>) 
row format delimited 
fields terminated by '\t'
collection items terminated by ','
stored as textfile;

-- 数据 
1	zhangsan,male
2	lisi,male

-- 导入
load data local inpath '/root/data/structtest.txt' into table structtest;

-- 查看
hive> select * from structtest;
OK
1       {"name":"zhangsan","sex":"male"}
2       {"name":"lisi","sex":"male"}

hive> select id,info.name from structtest;
OK
1       zhangsan
2       lisi

-- ------------------------------ 综合array\map\struct ------------------------------

create table alltest(
    id int,
    name string,
    salary bigint,
    sub array<string>,
    details map<string, int>,
    address struct<city:string, state:string, pin:int>
) 
row format delimited 
fields terminated by ','
collection items terminated by '$'
map keys terminated by '#' 
stored as textfile;

-- 数据 
1,abc,40000,a$b$c,pf#500$epf#200,hyd$ap$500001
2,def,3000,d$f,pf#500,bang$kar$600038
4,abc,40000,a$b$c,pf#500$epf#200,bhopal$MP$452013
5,def,3000,d$f,pf#500,Indore$MP$452014

-- 导入数据
load data local inpath '/root/data/alltest.txt' into table alltest;

-- 查看
hive> select * from alltest;
OK
1       abc     40000   ["a","b","c"]   {"pf":500,"epf":200}    {"city":"hyd","state":"ap","pin":500001}
2       def     3000    ["d","f"]       {"pf":500}      {"city":"bang","state":"kar","pin":600038}
4       abc     40000   ["a","b","c"]   {"pf":500,"epf":200}    {"city":"bhopal","state":"MP","pin":452013}
5       def     3000    ["d","f"]       {"pf":500}      {"city":"Indore","state":"MP","pin":452014}

-- ------------------------------ UNIONTYPE ------------------------------

-- create_union(tag, val1, val2, ...)
-- Creates a union type with the value that is being pointed to by the tag parameter. 

-- ---- 简单示例：里面都是基本类型 ------

create table uniontest(
    id int,
    info uniontype<string,string>
) 
row format delimited 
fields terminated by '\t'
collection items terminated by ','
stored as textfile;

-- 插入数据：insert into
-- tag 索引后面的值是从 0 开始的
insert into table uniontest 
    values
    (1,create_union(0,"zhangsan","male")),  -- 使用 "zhangsan"
    (1,create_union(1,"zhangsan","male")),  -- 使用 "male"
    (2,create_union(0,"lisi","female")),
    (2,create_union(1,"lisi","female"));

-- 查看
hive> select * from uniontest;
OK
1       {0:"zhangsan"}
1       {1:"male"}
2       {0:"lisi"}
2       {1:"female"}

-- 数据 
1	0,zhangsan
1	1,male
2	0,lisi
2	1,female

-- 插入数据：load data
load data local inpath '/root/data/uniontest.txt' into table uniontest;

-- 查看
hive> select * from uniontest;
OK
1       {0:"zhangsan"}
1       {1:"male"}
2       {0:"lisi"}
2       {1:"female"}

-- 如果数据格式是这样的：
-- 1	0,zhangsan,male
-- 1	1,zhangsan,male
-- 2	0,lisi,female
-- 2	1,lisi,female
-- 会把后面的字符串当作一个整体，输出：
-- 1       {0:"zhangsan,male"}
-- 1       {1:"zhangsan,male"}
-- 2       {0:"lisi,female"}
-- 2       {1:"lisi,female"}


-- ---- 复杂示例：里面包含复杂类型 ------

create table uniontest_comp(
    id int,
    info uniontype<int, 
                   string,
                   array<string>,
                   map<string,string>,
                   struct<sex:string,age:string>>
) 
row format delimited 
fields terminated by '\t'
collection items terminated by ','
stored as textfile;

-- 插入数据
-- 也可以使用 `insert into table ....select ....`
insert into table uniontest_comp
    values
    (1,create_union(0,1,"zhangsan",array("male","33"),map("sex","male","age","33"),named_struct("sex","male","age","33"))),
    (1,create_union(1,1,"zhangsan",array("male","33"),map("sex","male","age","33"),named_struct("sex","male","age","33"))),
    (1,create_union(2,1,"zhangsan",array("male","33"),map("sex","male","age","33"),named_struct("sex","male","age","33"))),
    (1,create_union(3,1,"zhangsan",array("male","33"),map("sex","male","age","33"),named_struct("sex","male","age","33"))),
    (1,create_union(4,1,"zhangsan",array("male","33"),map("sex","male","age","33"),named_struct("sex","male","age","33")));

-- 查看
hive> select * from uniontest_comp;
OK
1       {0:1}
1       {1:"zhangsan"}
1       {2:["male","33"]}
1       {3:{"sex":"male","age":"33"}}
1       {4:{"sex":"male","age":"33"}}

参考：http://querydb.blogspot.com/2015/11/hive-complex-data-types.html