Hive Advanced Data type

The advanced data types for hive mainly include: array type, map type, struct type, collection type, which are described in detail below.

1) Array type
Array_type:array<data_type>

--Build a table statement

CREATE TABLE Test.array_table (
Name String,
Age int,
Addr Array<string>
)
Row format delimited terminated by ', '
Collection items terminated by ': '
;

hive> desc test.array_table;
Ok
Name string
Age int
Addr Array<string>

--Test data

Chavin,28,beijing:shanghai:shenyang
Nope,28,beijing:shanghai:taiyuan
Wzq,28,beijing:dalian:shenyang:fuxin
Zhangshuai,28,beijing:shenyang:fuxin

--Load data

hive> Load Data local inpath '/home/hadoop/array_table.txt ' into table test.array_table;
Loading Data to Table test.array_table
Ok
Time taken:0.918 seconds

--Query

Hive> select * from Test.array_table;
Ok
Chavin ["Beijing", "Shanghai", "Shenyang"]
Nope ["Beijing", "Shanghai", "Taiyuan"]
wzq ["Beijing", "Dalian", "Shenyang", "Fuxin"]
Zhangshuai ["Beijing", "Shenyang", "Fuxin"]
Time taken:0.199 seconds, Fetched:4 row (s)

Hive> Select addr[1] from test.array_table;
Ok
Shanghai
Shanghai
Dalian
Shenyang
Time taken:0.186 seconds, Fetched:4 row (s)

Hive> Select addr[0] from test.array_table;
Ok
Beijing
Beijing
Beijing
Beijing
Time taken:0.212 seconds, Fetched:4 row (s)

2) Map type
Map_type:map<primary_type,data_type>

--Build a table statement

CREATE TABLE Test.map_table (
Name String,
Detail map<string,string>
)
Row format delimited fields terminated by ' \ t '
Collection items terminated by ', '
Map keys terminated by ': '
;

hive> desc test.map_table;
Ok
Name string
Detail map<string,string>
Time taken:0.146 seconds, Fetched:2 row (s)

--Test data

Chavin Age:28,addr:beijing,phone:15998888888,email:[email protected]
Nope Age:28,addr:beijing,phone:13899998888,email:[email protected]
WZQ Age:28,addr:shanghai,phone:13899998888,email:[email protected]
Zhangshuai Age:28,addr:tianjing,phone:13899998888,email:[email protected]

--Load data

hive> Load Data local inpath '/home/hadoop/map.txt ' into table test.map_table;
Loading Data to Table test.map_table
Ok
Time taken:0.641 seconds

--Querying data

Hive> select * from Test.map_table;
OK
chavin    {"Age": "$", "addr": "Beijing", "Phone": "15998888888", "email": "[email  Protected] "}
nope    {" Age ":" $ "," addr ":" Beijing "," Phone ":" 13899998888 "," email ":" [email  Protected] "}
wzq    {" Age ":" $ "," addr ":" Shanghai "," Phone ":" 13899998888 "," email ":" [email  Protected] "}
zhangshuai    {" Age ":" $ "," addr ":" Tianjing "," Phone ":" 13899998888 "," email ":" [ Email protected] "}
Time taken:0.183 seconds, Fetched:4 row (s)

Hive> Select Detail from test.map_table;
Ok
{"Age": "addr", "E": "Beijing", "Phone": "15998888888", "email": "[email protected]"}
{"Age": "addr", "E": "Beijing", "Phone": "13899998888", "email": "[email protected]"}
{"Age": "addr", "E": "Shanghai", "Phone": "13899998888", "email": "[email protected]"}
{"Age": "addr", "E": "Tianjing", "Phone": "13899998888", "email": "[email protected]"}
Time taken:0.177 seconds, Fetched:4 row (s)

hive> Select name,detail[' email ' from test.map_table;
Ok
Chavin [email protected]
Nope [email protected]
WZQ [email protected]
Zhangshuai [email protected]
Time taken:0.179 seconds, Fetched:4 row (s)

3) Structure type
Struct_type:struct<col_name:data_type,... ... >

--Build a table statement

CREATE TABLE Test.struct_table (
name      string,
detail    Struct<age:int,phone:string,email:string>
)
Row format delimited fields terminated by ' \ T '
Collection Items terminated by ', '
;

hive> desc test.struct_table;
Ok
Name string
Detail struct<age:int,phone:string,email:string>
Time taken:0.139 seconds, Fetched:2 row (s)

--Test data

Chavin 28,15998888888,[email protected]
Nope 28,13899998888,[email protected]
WZQ 28,13899998888,[email protected]
Zhangshuai 28,13899998888,[email protected]

--Load data

hive> Load Data local inpath '/home/hadoop/struct.txt ' into table test.struct_table;
Loading Data to Table test.struct_table
Ok
Time taken:0.564 seconds

--Querying data

Hive> select * from Test.struct_table;
Ok
Chavin {"Age": +, "phone": "15998888888", "email": "[email protected]"}
Nope {"Age": +, "phone": "13899998888", "email": "[email protected]"}
wzq {"Age": +, "phone": "13899998888", "email": "[email protected]"}
Zhangshuai {"Age": +, "phone": "13899998888", "email": "[email protected]"}
Time taken:0.183 seconds, Fetched:4 row (s)

Hive> select Name,detail.email from test.struct_table;
Ok
Chavin [email protected]
Nope [email protected]
WZQ [email protected]
Zhangshuai [email protected]
Time taken:0.869 seconds, Fetched:4 row (s)

4) Collection type
Union_type:uniontype<data_type,data_type,... ... >

--Build a table statement

CREATE TABLE Test.union_table (
Detail uniontype<int,string>
)
Row format delimited fields terminated by ' \ t '
Collection items terminated by ', '
;

hive> desc test.union_table;
Ok
Detail uniontype<int,string>
Time taken:0.141 seconds, Fetched:2 row (s)

Hive Advanced Data type