7)Hive Data Definition Language(DDL)- Table Examples

Hive Data Definition Language(DDL)- Table Examples

Examples:

Hive Table Creation with complex data types:

Let's review the complex data types as we learned in the previous post briefly.

Hive Complex Data Types:
Generally in hive and all other databases almost have same data types like primitive data types,string data types,Date/Time data types and Miscellaneous Data Type but in hive data types there is also an another data type feature is called complex data types.This complex data types are very useful and powerful in our hive.

Hive supports 4 types of complex data types:

•     ARRAY
•     MAP
•     STRUCT
•     UNIONTYPE

1)HIVE ARRAY:
ARRAY is an ordered collection of elements and all elements in the array is must be same data type.

Syntax:

ARRAY<data_type>

Note:
Hive array is like JAVA Array's

Example:

DATASET:

1|Manohar|23
2|Chandu|24
3|Govind|23
4|Chella|24
5|Prasath|23

Description:
FIELDS:
1- ID,
2-NAME,
3-AGE( array of elements of same type)

Hive Query(HQL) to create table:
Query:

CREATE TABLE IF NOT EXISTS MANO_DB.ARRAYS
(
    ID INT,
    NAME STRING,
    AGE ARRAY<SMALLINT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'

STORED AS TEXTFILE;

 Load dataset into table:

 LOAD DATA LOCAL INPATH '/home/mano/DataSets/Hive/Complex_types/dataset_Array' OVERWRITE INTO TABLE ARRAYS;

2)HIVE MAP:

Map is an unordered collection of key-value pairs.

Keep in mind ==> Keys must be of primitive types and Values can be of any type.

Note:
We should define the ROW FORMAT DELIMITED CLAUSE ==>map keys terminated by char

Syntax:

MAP<primitive_type,data_type>

Example:

DATASET:

1|Manohar|23|Developer:true
2|Chandu|24|Developer:true
3|Govind|23|Developer:true
4|Chella|24|Developer:false
5|Prasath|23|Developer:false

Description:
FIELDS:
1- ID,
2-NAME,
3-AGE( array of elements of same type)
4.WORK(MAP- key-values pairs)

Hive Query(HQL) to create table:
Query:

CREATE TABLE IF NOT EXISTS MANO_DB.MAPS
(
ID SMALLINT,
NAME STRING,
AGE ARRAY<SMALLINT>,
WORK MAP<STRING,BOOLEAN>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

Load dataset into table:

LOAD DATA LOCAL INPATH '/home/mano/DataSets/Hive/Complex_types/dataset_Map' OVERWRITE INTO TABLE MAPS;

3)HIVE STRUCT:
HIVE STRUCT is a collection of elements of different types.we can use any data type to specify this struct data type.Elements in STRUCT type are accessed using the DOT (.) notation.

Note:
we should define the ROW FORMAT DELIMITED CLAUSE ==>collection items terminated BY char
Syntax:

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

Example:

DATASET:

1|Manohar|23|Developer:true|CHN,TN
2|Chandu|24|Developer:true|NL,AP
3|Govind|23|Developer:true|CHN,TN
4|Chella|24|Developer:false|BL,KN
5|Prasath|23|Developer:false|CHN,TN

Description:
FIELDS:
1- ID,
2-NAME,
3-AGE( array of elements of same type)
4.WORK(MAP- key-values pairs)

5.ADDRESS(STRUCT - collections of city and state values) 

Hive Query(HQL) to create table:
Query:

CREATE TABLE IF NOT EXISTS MANO_DB.STRUCTS
(
ID SMALLINT,
NAME STRING,
AGE ARRAY<SMALLINT>,
WORK MAP<STRING,BOOLEAN>,
ADDRESS STRUCT<CITY:STRING,STATE:STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

Load dataset into table:

LOAD DATA LOCAL INPATH '/home/mano/DataSets/Hive/Complex_types/dataset_STRUCT' OVERWRITE INTO TABLE STRUCTS;

4)HIVE UNIONTYPE:
 HIVE UNIONTYPE is collection of Heterogeneous data types.

Syntax:
UNIONTYPE < data_type, data_type, ... >

Example need to check, will update soon:)

UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>

Real Time Example using all common data types:

DATASET: