EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Hive Data Types

Hive Data Types

Anandkumar Murugesan
Article byAnandkumar Murugesan
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated March 22, 2023

hive data type

What is a Hive Data Types?

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 summary of the stored data from a latency perspective, and the queries go a greater amount.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 are 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, and 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 have been truncated. Character length is a 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 in their exact form, but they are not stored exactly as numeric values in the case of double.

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 due to 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 labelled 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:

Hive Data Type1

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 'https://cdn.educba.com/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

Being a relational DB and yet a Sequel connects the HIVE offers all the key properties of usual SQL databases in a very sophisticated manner, making 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 of 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 Interview Questions
  4. PL/SQL Data Types
SPSS Course Bundle - 14 Courses in 1 | 5 Mock Tests
34+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Course Bundle - 32 Courses in 1 | 4 Mock Tests
125+ Hour of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Course Bundle - 7 Courses in 1
47+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
HIVE Course Bundle - 7 Courses in 1
 23+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more