Hive Data Types

What is a Hive?

Before understanding the Hive Data Types first we will study the hive. Hive is a data warehousing technique of Hadoop. Hadoop is the data storage and processing segment of Big data platform. Hive holds its position for sequel data processing techniques. Like other sequel environments hive can be reached through sequel queries. The major offerings by hive are data analysis, ad-hoc querying and summarize the stored data from a latency perspective, the queries go a greater amount.

Hive Data Types

Datatypes are classified into two types:

• Primitive Data Types
• Collective Data Types

1. Primitive Data Types

Primitive means were ancient and old. all datatypes listed as primitive are legacy ones. the important primitive datatypes areas listed below:

Type	Size (byte)	Example
TinyInt	1	20
SmallInt	2	20
Int	4	20
Bigint	8	20
Boolean	Boolean true/False	FALSE
Double	8	10.2222
Float	4	10.2222
String	Sequence of characters	ABCD
Timestamp	Integer/float/string	2/3/2012 12:34:56:1234567
Date	Integer/float/string	2/3/2019

Hive Data Types are Implemented using JAVA

Ex: Java Int is used for implementing the Int data type here.

• Character arrays are not supported in HIVE.
• Hive relies on delimiters to separate its fields, hive on coordinating with Hadoop allows to increase the write performance and read performance.
• Specifying the length of each column is not expected in the hive database.
• String literals can be articulated within either double quotes (“) single quotes (‘).
• In a newer version of the hive, Varchar types are introduced and they form a span specifier of (amid 1 and 65535), So for a character string, this acts as the largest length of value which it can accommodate. When a value exceeding this length is inserted then the rightmost elements of that values are been truncated. Character length is resolution with the figure of code points controlled by the character string.
• All integer literals (TINYINT, SMALLINT, BIGINT) are considered as INT datatypes basically, and only the length exceeds the actual int level it gets transmuted into a BIGINT or any other respective type.
• Decimal literals afford defined values and superior collection for floating-point values when compared to the DOUBLE type. Here numeric values are stored on their exact form, but in the case of double, they are not stored exactly as numeric values.

Date Value Casting Process

Casting Performed	Result
cast(date as date)	Same date value
cast(timestamp as date)	A local time zone is used to evaluate the Year/month/date values here and printed in the output.
cast(string as date)	A corresponding date value will be prompted as a result of this casting but we need to ensure the string is of the format ‘YYYY-MM-DD’ Null will be returned when the string value fails to make a valid match.
cast(date as timestamp)	According to the current local timezone, a timestamp value will be created for this casting process
cast(date as string)	YYYY-MM-DD is formed for the year/month/date value and the output will be of string format.

2. Collection Data Types

There are four collection datatypes in the hive they are also termed as complex data types.

• ARRAY
• MAP
• STRUCT
• UNIONTYPE

1. ARRAY: A sequence of elements of a common type that can be indexed and the index value starts from zero.

Code:

array (‘anand’, ‘balaa’, ‘praveeen’);

2. MAP: These are elements that are declared and retrieved using key-value pairs.

Code:

‘firstvalue’ -> ‘balakumaran’ , ‘lastvalue’ -> ‘pradeesh’ is represented as map(‘firstvalue’, ‘balakumaran’, ‘last’, ‘PG’). Now ‘balakumaran ‘ can be retrived with map[‘first’].

3. STRUCT: As in C, the struct is a datatype that accumulates a set of fields that are labeled and can be of any other data type.

Code:

For a column D of type STRUCT {Y INT; Z INT} the Y field can be retrieved by the expression D.Y

4. UNIONTYPE: Union can hold any one of the specified data types.

Code:

CREATE TABLE test(col1 UNIONTYPE<INT, DOUBLE, ARRAY<VARCHAR>>)

Output:

Various Delimiters used in Complex Data Types are listed below,

Delimiter	Code	Description
\n	\n	Record or row delimiter
^A  (Ctrl+A)	\001	Field delimiter
^B  (Ctrl+B)	\002	STRUCTS and ARRAYS
^C  (Ctrl+C)	\003	MAP’s

Complex Datatypes Example

Below are the examples of Complex Datatypes:

1. TABLE CREATION

Code:

create table store_complex_type (
emp_id int,
name string,
local_address STRUCT<street:string, city:string,country:string,zipcode:bigint>,
country_address MAP<STRING,STRING>,
job_history array<STRING>)
row format delimited fields terminated by ','
collection items terminated by ':'
map keys terminated by '_';

2. SAMPLE TABLE DATA

Code:

100 , Shan , 4th : CHN : IND : 600101 , CHENNAI_INDIA , SI : CSC
101 , Jai ,1th : THA : IND : 600096 , THANJAVUR_INDIA , HCL : TM
102 , Karthik , 5th : AP : IND : 600089 , RENIKUNDA_INDIA ,CTS : HCL

3. LOADING THE DATA

Code:

load data local inpath '/home/cloudera/Desktop/Hive_New/complex_type.txt' overwrite into table store_complex_type;

4. VIEWING THE DATA

Code:

select emp_id, name, local_address.city, local_address.zipcode, country_address['CHENNAI'], job_history[0] from store_complex_type where emp_id='100';

Conclusion – Hive Data Types

Being an on relational DB and yet a Sequel connects the HIVE offers all the key properties of usual SQL databases in a very sophisticated manner which makes this one among the more efficient structured data processing units in Hadoop.

Recommended Articles

This is a guide to Hive Data Type. Here we discuss two types in hive data types with proper examples. You can also go through our other related articles to learn more –

1. What is a Hive?
2. Hive Alternatives
3. Hive Built-in Functions
4. Hive Interview Questions
5. PL/SQL Data Types
6. Examples of Python Built-in Functions
7. Different Types of SQL Data with Examples