Hive data types are categorized into two types: primitive and complex. String and Int are the most useful primitive types, which are supported by most HQL functions. The details of primitive types are as follows:

ay contain a set of any type of fields. Complex types allow the nesting of types. The details of complex types a

Hive has three main complex types: ARRAY, MAP, and STRUCT. These data types are built on top of the primitive data types. ARRAY and MAP are similar to that in Java. STRUCT is a record type, which may contain a set of any type of fields. Complex types allow the nesting of types. The details of complex types are as follows:

The following is a short exercise for all the commonly-used data types. The details of the CREATE, LOAD, and SELECT statements will be introduced in later chapters. Let's take a look at the exercise:

1.  Prepare the data as follows:

      $vi employee.txt
      Michael|Montreal,Toronto|Male,30|DB:80|Product:Developer^DLead
      Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
      Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect
      Lucy|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead

2. Log in to beeline with the JDBC URL:

      $beeline -u "jdbc:hive2://localhost:10000/default"

3. Create a table using various data types (> indicates the beeline interactive mode):

      > CREATE TABLE employee (
      >   name STRING,
      >   work_place ARRAY<STRING>,
      >   gender_age STRUCT<gender:STRING,age:INT>,
      >   skills_score MAP<STRING,INT>,
      >   depart_title MAP<STRING,ARRAY<STRING>>
      > )
      > ROW FORMAT DELIMITED
      > FIELDS TERMINATED BY '|'
      > COLLECTION ITEMS TERMINATED BY ','
      > MAP KEYS TERMINATED BY ':'
      > STORED AS TEXTFILE;
      No rows affected (0.149 seconds) 

4. Verify that the table has been created:

      > !table employee
      +---------+------------+------------+--------------+---------+
      |TABLE_CAT|TABLE_SCHEMA| TABLE_NAME |  TABLE_TYPE  | REMARKS |
      +---------+------------+------------+--------------+---------+
      |         |default     | employee   | MANAGED_TABLE|         |
      +---------+------------+------------+--------------+---------+
      
      > !column employee
      -------------+-------------+-------------+-------------------+
      | TABLE_SCHEM | TABLE_NAME  | COLUMN_NAME | TYPE_NAME        |
      +-------------+-------------+-------------+------------------+
      | default     | employee    | name        | STRING           |
      | default     | employee    | work_place  | array<string>    |
      | default     | employee    | gender_age  |
      struct<gender:string,age:int>|
      | default     | employee    | skills_score| map<string,int>  |
      | default     | employee    | depart_title|  
      map<string,array<string>>   |
      +-------------+-------------+-------------+------------------+

5. Load data into the table:

      > LOAD DATA INPATH '/tmp/hivedemo/data/employee.txt' 
      > OVERWRITE INTO TABLE employee;
      No rows affected (1.023 seconds)

6. Query the whole array and each array element in the table:

      > SELECT work_place FROM employee;
      +----------------------+
      |      work_place      |
      +----------------------+
      | [Montreal, Toronto]  |
      | [Montreal]           |
      | [New York]           |
      | [Vancouver]          |
      +----------------------+
      4 rows selected (27.231 seconds)

      > SELECT 
      > work_place[0] as col_1, work_place[1] as col_2, 
      > work_place[2] as col_3 
      > FROM employee;
      +------------+----------+--------+
      |   col_1    |  col_2   | col_3  |
      +------------+----------+--------+
      | Montreal   | Toronto  |        |
      | Montreal   |          |        |
      | New York   |          |        |
      | Vancouver  |          |        |
      ------------+----------+---------+
      4 rows selected (24.689 seconds)

7. Query the whole struct and each struct attribute in the table:

      > SELECT gender_age FROM employee;
      +------------------+
      |    gender_age    |
      +------------------+
      | [Male, 30]       |
      | [Male, 35]       |
      | [Female, 27]     |
      | [Female, 57]     |
      +------------------+
      4 rows selected (28.91 seconds)
      

      > SELECT gender_age.gender, gender_age.age FROM employee;
      +------------+------+
      |   gender   | age  |
      +------------+------+
      | Male       | 30   |
      | Male       | 35   |
      | Female     | 27   |
      | Female     | 57   |
      +------------+------+
      4 rows selected (26.663 seconds)

8. Query the whole map and each map element in the table:

      > SELECT skills_score FROM employee;
      +--------------------+
      |    skills_score    |
      +--------------------+
      | {DB=80}            |
      | {Perl=85}          |
      | {Python=80}        |
      | {Sales=89, HR=94}  |
      +--------------------+
      4 rows selected (32.659 seconds)
      

      > SELECT 
      > name, skills_score['DB'] as DB, skills_score['Perl'] as Perl,
      > skills_score['Python'] as Python, 
      > skills_score['Sales'] as Sales,
      > skills_score['HR'] as HR
      > FROM employee;
      +----------+-----+-------+---------+--------+-----+
      |   name   | db  | perl  | python  | sales  | hr  |
      +----------+-----+-------+---------+--------+-----+
      | Michael  | 80  |       |         |        |     |
      | Will     |     | 85    |         |        |     |
      | Shelley  |     |       | 80      |        |     |
      | Lucy     |     |       |         | 89     | 94  |
      +----------+-----+-------+---------+--------+-----+
      4 rows selected (24.669 seconds)

9. Query the composite type in the table:

      > SELECT depart_title FROM employee;
      +---------------------------------+
      |          depart_title           |
      +---------------------------------+
      | {Product=[Developer, Lead]}     |
      | {Test=[Lead], Product=[Lead]}   |
      | {Test=[Lead], COE=[Architect]}  |
      | {Sales=[Lead]}                  |
      +---------------------------------+
      4 rows selected (30.583 seconds)
      

      > SELECT 
      > name, depart_title['Product'] as Product, depart_title['Test'] 
      as Test,
      > depart_title['COE'] as COE, depart_title['Sales'] as Sales
      > FROM employee;
      +--------+--------------------+---------+-------------+------+
      |   name |      product       |  test   |     coe     |sales |
      +--------+--------------------+---------+-------------+------+
      | Michael| [Developer, Lead]  |         |             |      |
      | Will   | [Lead]             | [Lead]  |             |      |
      | Shelley|                    | [Lead]  | [Architect] |      |
      | Lucy   |                    |         |             |[Lead]|
      +--------+--------------------+---------+-------------+------+
      4 rows selected (26.641 seconds)
      

      > SELECT
      > name, depart_title['Product'][0] as product_col0, 
      > depart_title['Test'][0] as test_col0 
      > FROM employee;
      +----------+---------------+------------+
      |   name   | product_col0  | test_col0  |
      +----------+---------------+------------+
      | Michael  | Developer     |            |
      | Will     | Lead          | Lead       |
      | Shelley  |               | Lead       |
      | Lucy     |               |            |
      +----------+---------------+------------+
      4 rows selected (26.659 seconds)