Hive collection data type example

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> create table empsubquery
. . . . . . . . . . . . . . . . . . . . . . .> ( empid int,
. . . . . . . . . . . . . . . . . . . . . . .>   firstname varchar(30),
. . . . . . . . . . . . . . . . . . . . . . .>   lastname varchar(30),
. . . . . . . . . . . . . . . . . . . . . . .>   tenure int,
. . . . . . . . . . . . . . . . . . . . . . .>   address struct<street:string,city:string>,
. . . . . . . . . . . . . . . . . . . . . . .>   subordinates array<string>);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into empsubquery
. . . . . . . . . . . . . . . . . . . . . . .> select 1,'Donghua','Luo',2,named_struct("street","Serangoon Road","city","Singapore"),array("Larry","Tom") union all
. . . . . . . . . . . . . . . . . . . . . . .> select 2,'Larry','Elison',5,named_struct("street","Victor Street","city","New York"),array("Tom") union all
. . . . . . . . . . . . . . . . . . . . . . .> select 3,'Tom','Kyte',4,named_struct("street","Victor Street","city","New York"),array("Tiger","Leon") union all
. . . . . . . . . . . . . . . . . . . . . . .> select 4,'Tiger','Hood',3,named_struct("street","Eliz Road","city","London"),array("Jack");

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat vertical
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from empsubquery;
empsubquery.empid         1
empsubquery.firstname     Donghua
empsubquery.lastname      Luo
empsubquery.tenure        2
empsubquery.address       {"street":"Serangoon Road","city":"Singapore"}
empsubquery.subordinates  ["Larry","Tom"]

empsubquery.empid         2
empsubquery.firstname     Larry
empsubquery.lastname      Elison
empsubquery.tenure        5
empsubquery.address       {"street":"Victor Street","city":"New York"}
empsubquery.subordinates  ["Tom"]

empsubquery.empid         3
empsubquery.firstname     Tom
empsubquery.lastname      Kyte
empsubquery.tenure        4
empsubquery.address       {"street":"Victor Street","city":"New York"}
empsubquery.subordinates  ["Tiger","Leon"]

empsubquery.empid         4
empsubquery.firstname     Tiger
empsubquery.lastname      Hood
empsubquery.tenure        3
empsubquery.address       {"street":"Eliz Road","city":"London"}
empsubquery.subordinates  ["Jack"]

4 rows selected (0.271 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat table
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select firstname,address.street,address.city from empsubquery;
+------------+-----------------+------------+--+
| firstname  |     street      |    city    |
+------------+-----------------+------------+--+
| Donghua    | Serangoon Road  | Singapore  |
| Larry      | Victor Street   | New York   |
| Tom        | Victor Street   | New York   |
| Tiger      | Eliz Road       | London     |
+------------+-----------------+------------+--+
4 rows selected (16.004 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select firstname,subordinates[0] subordinates_0,subordinates[1] subordinates_1 from empsubquery;
+------------+-----------------+-----------------+--+
| firstname  | subordinates_0  | subordinates_1  |
+------------+-----------------+-----------------+--+
| Donghua    | Larry           | Tom             |
| Larry      | Tom             | NULL            |
| Tom        | Tiger           | Leon            |
| Tiger      | Jack            | NULL            |
+------------+-----------------+-----------------+--+
4 rows selected (15.304 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def>