Data Types in Hive

Hive data types are categorized into two types. They are the primitive and complex data types.

The primitive data types include Integers, Boolean, Floating point numbers and strings. The below table lists the size of each data type:

Type        Size
----------------------
TINYINT     1 byte
SMALLINT    2 byte
INT         4 byte
BIGINT      8 byte
FLOAT       4 byte (single precision floating point numbers)
DOUBLE      8 byte (double precision floating point numbers)
BOOLEAN     TRUE/FALSE value
STRING      Max size is 2GB.

The complex data types include Arrays, Maps and Structs. These data types are built on using the primitive data types.

Arrays: Contain a list of elements of the same data type. These elements are accessed by using an index. For example an array, “fruits”, containing a list of elements [‘apple’, ’mango’, ‘orange’], the element “apple” in the array can be accessed by specifying fruits[1].

Maps: Contains key, value pairs. The elements are accessed by using the keys. For example a map, “pass_list” containing the “user name” as key and “password” as value, the password of the user can be accessed by specifying pass_list[‘username’]

Structs: Contains elements of different data types. The elements can be accessed by using the dot notation. For example in a struct, ”car”, the color of the car can be retrieved as specifying car.color

The create table statement containing the complex type is shown below.
CREATE TABLE complex_data_types
(
  Fruits     ARRAY<string>,
  Pass_list  MAP<string,string>,
  Car        STRUCT<color:string, wheel_size:float>
);

Hive Built-in Functions

Functions in Hive are categorized as below.


Numeric and Mathematical Functions: These functions mainly used to perform mathematical calculations.

Date Functions: These functions are used to perform operations on date data types like adding the number of days to the date etc.

String Functions: These functions are used to perform operations on strings like finding the length of a string etc.

Conditional Functions: These functions are used to test conditions and returns a value based on whether the test condition is true or false.

Collection Functions: These functions are used to find the size of the complex types like array and map. The only collection function is SIZE. The SIZE function is used to find the number of elements in an array and map. The syntax of SIZE function is

SIZE( Array<A> ) and SIZE( MAP<key,value> )

Type Conversion Function: This function is used to convert from one data type to another. The only type conversion function is CAST. The syntax of CAST is
CAST( expr as <type> )

The CAST function converts the expr into the specified type.

Table Generating Functions: These functions transform a single row into multiple rows. EXPLODE is the only table generated function. This function takes array as an input and outputs the elements of array into separate rows. The syntax of EXPLODE is
EXPLODE( ARRAY<A> )

When you use the table generating functions in the SELECT clause, you cannot specify any other columns in the SELECT clause. 

Date Functions in Hive

In fact the dates are treated as strings in Hive. The date functions are listed below.


UNIX_TIMESTAMP()

This function returns the number of seconds from the Unix epoch (1970-01-01 00:00:00 UTC) using the default time zone.

UNIX_TIMESTAMP( string date )

This function converts the date in format 'yyyy-MM-dd HH:mm:ss' into Unix timestamp. This will return the number of seconds between the specified date and the Unix epoch. If it fails, then it returns 0.
Example: UNIX_TIMESTAMP('2000-01-01 00:00:00') returns 946713600

UNIX_TIMESTAMP( string date, string pattern )

This function converts the date to the specified date format and returns the number of seconds between the specified date and Unix epoch. If it fails, then it returns 0.
Example: UNIX_TIMESTAMP('2000-01-01 10:20:30','yyyy-MM-dd') returns 946713600

FROM_UNIXTIME( bigint number_of_seconds  [, string format] )

The FROM_UNIX function converts the specified number of seconds from Unix epoch and returns the date in the format 'yyyy-MM-dd HH:mm:ss'.
Example: FROM_UNIXTIME( UNIX_TIMESTAMP() ) returns the current date including the time. This is equivalent to the SYSDATE in oracle.

TO_DATE( string timestamp )

The TO_DATE function returns the date part of the timestamp in the format 'yyyy-MM-dd'.
Example: TO_DATE('2000-01-01 10:20:30') returns '2000-01-01'

YEAR( string date )

The YEAR function returns the year part of the date.
Example: YEAR('2000-01-01 10:20:30') returns 2000

MONTH( string date )

The MONTH function returns the month part of the date.
Example: YEAR('2000-03-01 10:20:30') returns 3

DAY( string date ), DAYOFMONTH( date )

The DAY or DAYOFMONTH function returns the day part of the date.
Example: DAY('2000-03-01 10:20:30') returns 1

HOUR( string date )

The HOUR function returns the hour part of the date.
Example: HOUR('2000-03-01 10:20:30') returns 10

MINUTE( string date )

The MINUTE function returns the minute part of the timestamp.
Example: MINUTE('2000-03-01 10:20:30') returns 20

SECOND( string date )

The SECOND function returns the second part of the timestamp.
Example: SECOND('2000-03-01 10:20:30') returns 30

WEEKOFYEAR( string date )

The WEEKOFYEAR function returns the week number of the date.
Example: WEEKOFYEAR('2000-03-01 10:20:30') returns 9

DATEDIFF( string date1, string date2 )

The DATEDIFF function returns the number of days between the two given dates.
Example: DATEDIFF('2000-03-01', '2000-01-10')  returns 51

DATE_ADD( string date, int days )

The DATE_ADD function adds the number of days to the specified date
Example: DATE_ADD('2000-03-01', 5) returns '2000-03-06'

DATE_SUB( string date, int days )

The DATE_SUB function subtracts the number of days to the specified date
Example: DATE_SUB('2000-03-01', 5) returns ‘2000-02-25’

Conditional Functions in Hive

Hive supports three types of conditional functions. These functions are listed below:



IF( Test Condition, True Value, False Value )

The IF condition evaluates the “Test Condition” and if the “Test Condition” is true, then it returns the “True Value”. Otherwise, it returns the False Value.
Example: IF(1=1, 'working', 'not working') returns 'working'

COALESCE( value1,value2,... )

The COALESCE function returns the fist not NULL value from the list of values. If all the values in the list are NULL, then it returns NULL.
Example: COALESCE(NULL,NULL,5,NULL,4) returns 5


CASE Statement

The syntax for the case statement is:
CASE   [ expression ]
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       WHEN conditionn THEN resultn
       ELSE result
END

Here expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition1, condition2, ... conditionn).

All the conditions must be of same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

All the results must be of same datatype. This is the value returned once a condition is found to be true.

IF no condition is found to be true, then the case statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL

Example:
CASE   Fruit
       WHEN 'APPLE' THEN 'The owner is APPLE'
       WHEN 'ORANGE' THEN 'The owner is ORANGE'
       ELSE 'It is another Fruit'
END

The other form of CASE is

CASE
       WHEN Fruit = 'APPLE' THEN 'The owner is APPLE'
       WHEN Fruit = 'ORANGE' THEN 'The owner is ORANGE'
       ELSE 'It is another Fruit'

END

Numeric and Mathematical Functions in Hive

The Numerical functions are listed below in alphabetical order. Use these functions in SQL queries.


ABS( double n )

The ABS function returns the absolute value of a number.
Example: ABS(-100)

ACOS( double n )

The ACOS function returns the arc cosine of value n. This function returns Null if the value n is not in the range of -1<=n<=1.
Example: ACOS(0.5)

ASIN( double n )

The ASIN function returns the arc sin of value n. This function returns Null if the value n is not in the range of -1<=n<=1.
Example: ASIN(0.5)

BIN( bigint n )

The BIN function returns the number n in the binary format.
Example: BIN(100)

CEIL( double n ), CEILING( double n )

The CEILING or CEILING function returns the smallest integer greater than or equal to the decimal value n.
Example: CEIL(9.5)

CONV( bigint n, int from_base, int to_base )

The CONV function converts the given number n from one base to another base.
EXAMPLE: CONV(100, 10,2)

COS( double n )

The COS function returns the cosine of the value n. Here n should be specified in radians.
Example: COS(180*3.1415926/180)

EXP( double n )

The EXP function returns e to the power of n. Where e is the base of natural logarithm and its value is 2.718.
Example: EXP(50)

FLOOR( double n )

The FLOOR function returns the largest integer less than or equal to the given value n.
Example: FLOOR(10.9)

HEX( bigint n)

This function converts the value n into hexadecimal format.
Example: HEX(16)
HEX( string n )

This function converts each character into hex representation format.
Example: HEX(‘ABC’)

LN( double n )

The LN function returns the natural log of a number.
Example: LN(123.45)

LOG( double base, double n )

The LOG function returns the base logarithm of the number n.
Example: LOG(3, 66)

LOG2( double n )

The LOG2 function returns the base-2 logarithm of the number n.
Example: LOG2(44)

LOG10( double n )

The LOG10 function returns the base-10 logarithm of the number n.
Example: LOG10(100)

NEGATIVE( int n ),  NEGATIVE( double n )

The NEGATIVE function returns –n
Example: NEGATIVE(10)

PMOD( int m, int n ), PMOD( double m, double n )

The PMOD function returns the positive modulus of a number.
Example: PMOD(3,2)

POSITIVE( int n ), POSITIVE( double n )

The POSITIVE function returns n
Example: POSITIVE(-10)

POW( double m, double n ), POWER( double m, double n )

The POW or POWER function returns m value raised to the n power.
Example: POW(10,2)

RAND( [int seed] )

The RAND function returns a random number. If you specify the seed value, the generated random number will become deterministic.
Example: RAND( )

ROUND( double value [, int n] )

The ROUND function returns the value rounded to n integer places.
Example: ROUND(123.456,2)

SIN( double n )

The SIN function returns the sin of a number. Here n should be specified in radians.
Example: SIN(2)

SQRT( double n )

The SQRT function returns the square root of the number
Example: SQRT(4)

UNHEX( string n )

The UNHEX function is the inverse of HEX function. It converts the specified string to the number format.
Example: UNHEX(‘AB’)

String Functions in Hive

The string functions in Hive are listed below:


ASCII( string str )

The ASCII function converts the first character of the string into its numeric ascii value.
Example1: ASCII('hadoop') returns 104
Example2: ASCII('A') returns 65

CONCAT( string str1, string str2... )

The CONCAT function concatenates all the stings.
Example: CONCAT('hadoop','-','hive') returns 'hadoop-hive'

CONCAT_WS( string delimiter, string str1, string str2... )

The CONCAT_WS function is similar to the CONCAT function. Here you can also provide the delimiter, which can be used in between the strings to concat.
Example: CONCAT_WS('-','hadoop','hive') returns 'hadoop-hive'

FIND_IN_SET( string search_string, string source_string_list )

The FIND_IN_SET function searches for the search string in the source_string_list and returns the position of the first occurrence in the source string list. Here the source string list should be comma delimited one. It returns 0 if the first argument contains comma.
Example: FIND_IN_SET('ha','hao,mn,hc,ha,hef') returns 4

LENGTH( string str )

The LENGTH function returns the number of characters in a string.
Example: LENGTH('hive') returns 4

LOWER( string str ),  LCASE( string str )

The LOWER or LCASE function converts the string into lower case letters.
Example: LOWER('HiVe') returns 'hive'

LPAD( string str, int len, string pad )

The LPAD function returns the string with a length of len characters left-padded with pad.
Example: LPAD('hive',6,'v') returns 'vvhive'

LTRIM( string str )

The LTRIM function removes all the trailing spaces from the string.
Example: LTRIM('   hive') returns 'hive'

REPEAT( string str, int n )

The REPEAT function repeats the specified string n times.
Example: REPEAT('hive',2) returns 'hivehive'

RPAD( string str, int len, string pad )

The RPAD function returns the string with a length of len characters right-padded with pad.
Example: RPAD('hive',6,'v') returns 'hivevv'

REVERSE( string str )

The REVERSE function gives the reversed string
Example: REVERSE('hive') returns 'evih'

RTRIM( string str )

The RTRIM function removes all the leading spaces from the string.
Example: LTRIM('hive   ') returns 'hive'

SPACE( int number_of_spaces )

The SPACE function returns the specified number of spaces.
Example: SPACE(4) returns '    '

SPLIT( string str, string pat )

The SPLIT function splits the string around the pattern pat and returns an array of strings. You can specify regular expressions as patterns.
Example: SPLIT('hive:hadoop',':') returns ["hive","hadoop"]

SUBSTR( string source_str, int start_position [,int length]  ),  SUBSTRING( string source_str, int start_position [,int length]  )

The SUBSTR or SUBSTRING function returns a part of the source string from the start position with the specified length of characters. If the length is not given, then it returns from the start position to the end of the string.
Example1: SUBSTR('hadoop',4) returns 'oop'
Example2: SUBSTR('hadoop',4,2) returns 'oo'

TRIM( string str )

The TRIM function removes both the trailing and leading spaces from the string.
Example: TRIM('   hive   ') returns 'hive'

UPPER( string str ), UCASE( string str )

The UPPER or UCASE function converts the string into upper case letters.
Example: UPPER('HiVe') returns 'HIVE'

Comparison between Hive Internal Tables vs External Tables

1. Objective

In our previous Apache Hive blog, we have discussed Hive Data Models – Table, Partition, Bucket in detail. In this blog, we are going to discuss the two types of Hive Table such as Internal Table (Managed Table) and External table. At last, we will also cover the difference between Hive Internal tables vs External Tables in this Hive tutorial.

2. Apache Hive Internal and External Tables

Hive is an open source data warehouse system used for querying and analyzing large datasets. Data in Apache Hive can be categorized into Table, Partition, and Bucket. The table in Hive is logically made up of the data being stored. Hive has two types of tables which are as follows:

• Managed Table (Internal Table)
• External Table

Hive Managed Tables-

It is also know an internal table. When we create a table in Hive, it by default manages the data. This means that Hive moves the data into its warehouse directory.

Hive External Tables-

We can also create an external table. It tells Hive to refer to the data that is at an existing location outside the warehouse directory.

Let’s now discuss the Hive Internal tables vs External tables comparison.

3. Featured Difference between Hive Internal Tables vs External Tables

Here we are going to cover the comparison between Hive Internal tables vs External tables on the basis of different features. Let’s discuss them one by one-

3.1. LOAD and DROP Semantics

We can see the main difference between the two table type in the LOAD and DROP semantics.

• Managed Tables – When we load data into a Managed table, then Hive moves data into Hive warehouse directory.

For example:

1. CREATE TABLE managed_table (dummy STRING);
2. LOAD DATA INPATH '/user/tom/data.txt' INTO table managed_table;

This moves the file hdfs://user/tom/data.txt into Hive’s warehouse directory for the managed_table table, which is hdfs://user/hive/warehouse/managed_table.

Further, if we drop the table using:

DROP TABLE managed_table

Then this will delete the table metadata including its data. The data no longer exists anywhere. This is what it means for HIVE to manage the data.

• External Tables – External table behaves differently. In this, we can control the creation and deletion of the data. The location of the external data is specified at the table creation time:

1. CREATE EXTERNAL TABLE external_table(dummy STRING)
2. LOCATION '/user/tom/external_table';
3. LOAD DATA INPATH '/user/tom/data.txt' INTO TABLE external_table;

Now, with the EXTERNAL keyword, Apache Hive knows that it is not managing the data. So it doesn’t move data to its warehouse directory. It does not even check whether the external location exists at the time it is defined. This very useful feature because it means we create the data lazily after creating the table.

The important thing to notice is that when we drop an external table, Hive will leave the data untouched and only delete the metadata.

3.2. Security

• Managed Tables – Hive solely controls the Managed table security. Within Hive, security needs to be managed; probably at the schema level (depends on organization).
• External Tables – These tables’ files are accessible to anyone who has access to HDFS file structure. So, it needs to manage security at the HDFS file/folder level.

3.3. When to use Managed and external table

Use Managed table when –

• We want Hive to completely manage the lifecycle of the data and table.
• Data is temporary

Use External table when –

• Data is used outside of Hive. For example, the data files are read and processed by an existing program that does not lock the files.
• We are not creating a table based on the existing table.
• We need data to remain in the underlying location even after a DROP TABLE. This may apply if we are pointing multiple schemas at a single data set.
• The hive shouldn’t own data and control settings, directories etc., we may have another program or process that will do these things.

4. Conclusion

In conclusion, Managed tables are like normal database table in which we can store data and query on. On dropping Managed tables, the data stored in them is also deleted and data is lost forever. While dropping External tables will delete metadata but not the data.

Configure Hive Metastore to MySQL 

1. Objective

This Hive tutorial describes how to configure Hive Metastore to MySQL. Hive stores its metadata (schema-related information, partitioning information, etc.) into the database, Hive is shipped with Derby database. Derby is an embedded database backed by local disk. Derby is a single threaded database which doesn’t allow multiple connections, it is not production ready. In this tutorial, we will change the Metastore of Hive to MySQL.

2. Hive Introduction

Apache Hive is a data warehouse on the top of Hadoop. Using Hive we can run ad-hoc queries for the analysis of data. Hive saves us from writing complex Map-Reduce jobs, rather than that we can submit merely SQL queries. Hive converts SQL queries into MapReduce job and submits the same to the cluster.

Hive is very fast and scalable and is highly extensible. The hive consists of a huge user base, with the help of Hive thousands of jobs on the cluster can be run by hundreds of users at a time. As Hive is similar to SQL, hence it becomes very easy for the SQL developers to learn and implement Hive Queries.

3. Steps to Configure Hive Metastore to MySQL

Follow the steps given below to easily configure Hive Metastore to MySQL-

3.1. Install MySQL

$sudo apt-get install mysql-server

3.2. Copy MySQL connector to lib directory

Download MySQL connector (mysql-connector-java-5.1.35-bin.jar) and copy it into the $HIVE_HOME/lib directory

Note: $HIVE_HOME refers hive installation directory

3.3. Edit / Create configuration file hive-site.xml

Add following entries in the hive-site.xml (present in $HIVE_HOME/conf)

1. <property>
2. <name>javax.jdo.option.ConnectionURL</name>
3. <value>jdbc:mysql://localhost/hcatalog?createDatabaseIfNotExist=true</value>
4. </property>
5. <property>
6. <name>javax.jdo.option.ConnectionUserName</name>
7. <value>your_username</value>
8. </property>
9. <property>
10. <name>javax.jdo.option.ConnectionPassword</name>
11. <value>your_password</value>
12. </property>
13. <property>
14. <name>javax.jdo.option.ConnectionDriverName</name>
15. <value>com.mysql.jdbc.Driver</value>
16. </property>

Now start hive terminal, it will be connected to MySQL. Now you can open multiple hive connections, which was not possible with Derby database.

Hive DDL Commands

Hive DDL Commands : Types of DDL Hive Commands

1. Objective

In this Hive tutorial, we will learn about Hive DDL Commands. However, there are many types of Hive DDL Commands. So, in this article, we will learn about each Hive commands individually. Also, we will cover syntax of each DDL Commands, as well as examples of Hive DDL Commands to understand well.

What is Hive DDL commands

2. Introduction to Hive DDL Commands

There are several types of Hive DDL commands, we commonly use. such as:

1. Create Database Statement
2. Hive Show Database
3. Drop database
4. Creating Hive Tables
5. Browse the table
6. Altering and Dropping Tables
7. Hive Select Data from Table
8. Hive Load Data

Let’s discuss each Hive DDL commands in detail:

a. Create Database Statement

Basically, in Apache Hive, the database is a namespace or a collection of tables.

• Syntax-

hive> CREATE SCHEMA userdb;  

hive> SHOW DATABASES;

Or,

CREATE DATABASE [IF NOT EXISTS] db_name;

• For Example-

hive> CREATE DATABASE IF NOT EXISTS TRAINING;

OK

Time taken: 9.253 seconds

hive>

b. Hive Show Database

However, with this Hive DDL commands, we generally display the databases present in Hive. Moreover, to see all available databases in Hive below is the syntax:

• Syntax-

hive> DROP DATABASE IF EXISTS userdb;  

Or,

SHOW DATABASES;

• For Example-

hive> SHOW DATABASES;

OK

default

training

Time taken: 2.346 seconds, Fetched: 2 row(s)

hive>

c. Drop database

The Syntax & Example of Drop database – Hive DDL commands are:

• Syntax-

hive> DROP DATABASE IF EXISTS userdb;  

Or,

DROP DATABASE IF EXISTS db_name;

• For Example-

hive> DROP DATABASE IF EXISTS TRAINING;

OK

Time taken: 1.165 seconds

hive>

d. Creating Hive Tables

However, with two columns “Create a table” called Sonoo. Although, the first being an integer and the other a string.

hive> CREATE TABLE Sonoo(foo INT, bar STRING);  

To be more specific, create a table is what we call  HIVE_TABLE with two columns and a partition column called ds. Moreover, the partition column is a virtual column. However,  it is not part of the data itself but is derived from the partition that a particular dataset is loaded into. In addition, tables are assumed to be of text input format and the delimiters are assumed to be ^A(ctrl-a), by default.

hive> CREATE TABLE HIVE_TABLE (foo INT, bar STRING) PARTITIONED BY (ds STRING);

Or,

CREATE [TEMPORARY ] [EXTERNAL] TABLE [IF NOT EXISTS] db_name table_name;

• For Example-

hive> CREATE TABLE IF NOT EXISTS test(col1 char(10),col2 char(20));

OK

Time taken: 1.1 seconds

hive>

e. Browse the table

The Syntax of Browse the table – Hive DDL commands are:

• Syntax-

hive>  Show tables;  

f. Altering and Dropping Tables

The Syntax of Altering and Dropping Tables – Hive DDL commands are:

• Syntax-

hive> ALTER TABLE Sonoo RENAME TO Kafka;  

hive> ALTER TABLE Kafka ADD COLUMNS (col INT);  

hive> ALTER TABLE HIVE_TABLE ADD COLUMNS (col1 INT COMMENT ‘a comment’);  

hive> ALTER TABLE HIVE_TABLE REPLACE COLUMNS (col2 INT, weight STRING, baz INT COMMENT ‘baz replaces new_col1’);  

Let’s discuss both individually:

i. Hive Drop Table

Generally, with these Hive DDL commands, we remove the table data and their metadata. Moreover, to drop tables in Hive below is the syntax:

• Syntax-

DROP TABLE [IF EXISTS] table_name;

• For Example-

hive> DROP TABLE test1;

OK

Time taken: 1.165 seconds

hive>

ii. Hive Alter Table

Basically, with these Hive DDL commands, we can alter table to modify attributes of Hive table.

• Syntax-

ALTER TABLE table_name ADD COLUMNS (column1, column2) ;

ALTER TABLE table_name RENAME TO table_new_name;

• For Example-

hive> ALTER TABLE test1 ADD COLUMNS(col3 char(10),col4 char(10));

OK

Time taken: 0.56 seconds

hive>

hive> ALTER TABLE test1 RENAME TO test2;

OK

Time taken: 0.343 seconds

hive>

g. Hive Select Data from Table

However, to select the columns from a table we use Hive select Data from Table command. Moreover,  Syntax for it is:

• Syntax-

SELECT [ALL | DISTINCT ] select_col, select_col FROM table WHERE

where_condition [GROUP BY col_list] [HAVING having_con] [ORDER BY

col_list][LIMIT number];

• For Example-

hive> SELECT * FROM test;

OK

abc 100

 bcd 102

cde 103

def 104

Time taken: 2.036 seconds, Fetched: 4 row(s)

hive>

h. Hive Load Data

To load the data into the Hive table, we use Hive Load Data command. Moreover, to load data to hive table from external file, below is the syntax.

• Syntax-

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE table_name;

• For Example-

hive> LOAD DATA LOCAL INPATH ‘sample.txt’ INTO TABLE test2;

Loading data to table default.test2

Table default.test2 stats: [numFiles=1, numRows=0, totalSize=32, rawDataSize=0]

OK

Time taken: 2.797 seconds

hive>

3. Conclusion

As a result, we have seen all Hive DDL commands: Create Database Statement, Hive Show Database, Drop database, Creating Hive Tables, Browse the table, Altering and Dropping Tables, Hive Select Data from Table, and Hive Load Data with syntax and examples. Still, if you have doubt, feel free to ask in the comment section.

Hive Metastore

Hive Metastore – Different Ways to Configure Hive Metastore

---

1. Objective

In this tutorial, we are going to introduce Hive Metastore in detail. Metastore is the central repository of Hive Metadata. It stores the meta data for Hive tables and relations. For example, Schema and Locations etc. This Hive tutorial will cover what is Hive Metastore, how the Hive Metastore works, what is Derby in Hive, how to Configure Hive Metastore and What are the Databases Supported by Hive? We will discuss the answer to all the above questions in detail.

2. Hive Metastore

Metastore is the central repository of Apache Hive metadata. It stores metadata for Hive tables (like their schema and location) and partitions in a relational database. It provides client access to this information by using metastore service API.

Hive metastore consists of two fundamental units:

1. A service that provides metastore access to other Apache Hive services.
2. Disk storage for the Hive metadata which is separate from HDFS storage.

3. Hive Metastore Modes

There are three modes for Hive Metastore deployment:

• Embedded Metastore
• Local Metastore
• Remote Metastore

Let’s now discuss the above three Hive Metastore deployment modes one by one-

3.1. Embedded Metastore

In Hive by default, metastore service runs in the same JVM as the Hive service. It uses embedded derby database stored on the local file system in this mode. Thus both metastore service and hive service runs in the same JVM by using embedded Derby Database. But, this mode also has limitation that, as only one embedded Derby database can access the database files on disk at any one time, so only one Hive session could be open at a time.

If we try to start the second session it produces an error when it attempts to open a connection to the metastore. So, to allow many services to connect the Metastore, it configures Derby as a network server. This mode is good for unit testing. But it is not good for the practical solutions.

3.2. Local Metastore

Hive is the data-warehousing framework, so hive does not prefer single session. To overcome this limitation of Embedded Metastore, for Local Metastore was introduced. This mode allows us to have many Hive sessions i.e. many users can use the metastore at the same time. We can achieve by using any JDBC compliant like MySQL which runs in a separate JVM or different machines than that of the Hive service and metastore service which are running in the same JVM.

This configuration is called as local metastore because metastore service still runs in the same process as the Hive. But it connects to a database running in a separate process, either on the same machine or on a remote machine. Before starting Apache Hive client, add the JDBC / ODBC driver libraries to the Hive lib folder.

MySQL is a popular choice for the standalone metastore. In this case, the javax.jdo.option.ConnectionURL property is set to jdbc:mysql://host/dbname? createDatabaseIfNotExist=true, and javax.jdo.option.ConnectionDriverName is set to com.mysql.jdbc.Driver. The JDBC driver JAR file for MySQL (Connector/J) must be on Hive’s classpath, which is achieved by placing it in Hive’s lib directory.

3.2. Remote Metastore

Moving further there is another metastore configuration called Remote Metastore. In this mode, metastore runs on its own separate JVM, not in the Hive service JVM. If other processes want to communicate with the metastore server they can communicate using Thrift Network APIs. We can also have one more metastore servers in this case to provide more availability. This also brings better manageability/security because the database tier can be completely firewalled off. And the clients no longer need share database credentials with each Hiver user to access the metastore database.

To use this remote metastore, you should configure Hive service by setting hive.metastore.uris to the metastore server URI(s). Metastore server URIs are of the form thrift://host:port, where the port corresponds to the one set by METASTORE_PORT when starting the metastore server.

4. Databases Supported by Hive

Hive supports 5 backend databases which are as follows:

• Derby
• MySQL
• MS SQL Server
• Oracle
• Postgres

5. Conclusion

In conclusion, we can say that Hive Metadata is a central repository for storing all the Hive metadata information. Metadata include various types of information like the structure of tables, relations etc. Above we have also discussed all the three metastore modes in detail. you can also Learn the other big data technologies likeApache Hadoop, Spark, Flink etc in detail.

Apache Hive Data Types

1. Objective

In our Previous blog, we have discussed the Apache Hive introduction and Hive Architecture in detail. Now in this blog, we are going to cover Apache Hive Data Types with examples. We will discuss different types of data types in Hive: Hive Primitive Data type, Hive Complex Data types, Hive Literals, Hive Column Datatypes etc. We will also cover the data types that fall into these categories.

Apache Hive Data Types

2. Apache Hive Data Types

Hive Data types are used for specifying the column/field type in Hive tables.

3. Types of Data Types in Hive

Mainly Hive Data Types are classified into 5 major categories, let’s discuss them one by one:

a. Primitive Data Types in Hive

Primitive Data Types also divide into 4 types which are as follows:

• Numeric Data Type
• Date/Time Data Type
• String Data Type
• Miscellaneous Data Type

Let us now discuss these Hive Primitive data types one by one-

Primitive Data Types in Hive

i. Numeric Data Type

The Hive Numeric Data types also classified into two types-

• Integral Data Types
• Floating Data Types

* Integral Data Types

Integral Hive data types are as follows-

• TINYINT (1-byte (8 bit) signed integer, from -128 to 127)
• SMALLINT (2-byte (16 bit) signed integer, from -32, 768 to 32, 767)
• INT (4-byte (32-bit) signed integer, from –2,147,483,648to 2,147,483,647)
• BIGINT (8-byte (64-bit) signed integer, from –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)

* Floating Data Types

Floating Hive data types are as follows-

• FLOAT (4-byte (32-bit) single-precision floating-point number)
• DOUBLE (8-byte (64-bit) double-precision floating-point number)
• DECIMAL (Arbitrary-precision signed decimal number)

ii. Date/Time Data Type

The second category of Apache Hive primitive data type is Date/Time data types. The following Hive data types comes into this category-

• TIMESTAMP (Timestamp with nanosecond precision)
• DATE (date)
• INTERVAL

iii. String Data Type

String data types are the third category under Hive data types. Below are the data types that come into this-

• STRING (Unbounded variable-length character string)
• VARCHAR (Variable-length character string)
• CHAR (Fixed-length character string)

iv. Miscellaneous Data Type

Miscellaneous data types has two types of Hive data types-

• BOOLEAN (True/false value)
• BINARY (Byte array)

b. Complex Data Types in Hive

In this category of Hive data types following data types are come-

• Array
• MAP
• STRUCT
• UNION

let’s now discuss the Hive Complex data types with the example-

Hive Complex data types

i. ARRAY

An ordered collection of fields. The fields must all be of the same type.

Syntax: ARRAY<data_type>

E.g. array (1, 2)

ii. MAP

An unordered collection of key-value pairs. Keys must be primitives; values may be any type. For a particular map, the keys must be the same type, and the values must be the same type.

Syntax: MAP<primitive_type, data_type>

E.g. map(‘a’, 1, ‘b’, 2).

iii. STRUCT

A collection of named fields. The fields may be of different types.

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

E.g. struct(‘a’, 1 1.0),[b] named_struct(‘col1’, ‘a’, ‘col2’, 1,  ‘col3’, 1.0)

iv. UNION

A value that may be one of a number of defined data. The value is tagged with an integer (zero-indexed) representing its data type in the union.

Syntax: UNIONTYPE<data_type, data_type, …>

E.g. create_union(1, ‘a’, 63)

c. Column Data Types in Hive

Column Hive data types are furthermore divide into 6 categories:

• Integral Type
• Strings
• Timestamp
• Dates
• Decimals
• Union Types

Let us discuss these Hive Column data types one by one-

Hive Column Data Types

i. Integral type

In this category of Hive data types following 4 data types are come-

• TINYINT
• SMALLINT
• INT/INTEGER
• BIGINT

By default, Integral literals are assumed to be INT. When the data range exceeds the range of INT, we need to use BIGINT.  If the data range is smaller than the INT, we uses SMALLINT. And TINYINT is smaller than SMALLINT.

Table	Postfix	Example
TINYINT	Y	100Y
SMALLINT	S	100S
BIGINT	L	100L

ii. Strings

The string data types in Hive, can be specified with either single quotes (‘) or double quotes (“). Apache Hive use C-style escaping within the strings.

Data Type	Length
VARCHAR	1 to 65355
CHAR	255

* VARCHAR

Varchar- Hive data types are created with a length specifier (between 1 and 65355). It defines the maximum number of characters allowed in the character string.

* Char

Char – Hive data types are similar to VARCHAR. But they are fixed-length meaning that values shorter than the specified length value are padded with spaces but trailing spaces are not important during comparisons. 255 is the maximum fixed length.

iii. Timestamp

Hive supports traditional UNIX timestamp with operational nanosecond precision. Timestamps in text files use format ”YYYY-MM-DD HH:MM:SS.fffffffff” and “yyyy-mm-dd hh:mm:ss.ffffffffff”.

iv. Dates

DATE values are described in particular year/month/day (YYYY-MM-DD) format.E.g. DATE ‘2017-­01-­01’. These types don’t have a time of day component. This type supports range of values for 0000-­01-­01 to 9999-­12-­31.

v. Decimals

In Hive DECIMAL type is similar to Big Decimal format of java. This represents immutable arbitrary precision. The syntax and example are below:

Apache Hive 0.11 and 0.12 has the precision of the DECIMAL type fixed. And it’s limited to 38 digits.

Apache Hive 0.13 users can specify scale and precision when creating tables with the DECIMAL data type using DECIMAL (precision, scale) syntax.  If the scale is not specified, then it defaults to 0 (no fractional digits). If no precision is specified, then it defaults to 10.

1. CREATE TABLE foo (
2. a DECIMAL, -- Defaults to decimal(10,0)b DECIMAL(9, 7)
3. b DECIMAL(9, 7)
4. )

vi. Union Types

Union Hive data types are the collection of heterogeneous data types. By using create union we can create an instance. The syntax and example are below:

1. CREATE TABLE union_test(foo UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>);
2. SELECT foo FROM union_test;
3. {0:1}
4. {1:2.0}
5. {2:["three","four"]}
6. {3:{"a":5,"b":"five"}}
7. {2:["six","seven"]}
8. {3:{"a":8,"b":"eight"}}
9. {0:9}
10. {1:10.0}

d. Literals Data Types in Hive

In Hive data types following literals are used:

Apache Hive Literals Data Types

• Floating Point Types
• Decimal Type

i. Floating Point Types

These are nothing but numbers with decimal points. This type of data is composed of the DOUBLE data types in Hive.

ii. Decimal Type

This type is nothing but floating point value with the higher range than the DOUBLE data type. The decimal type range is approximate -10-308 to 10308.

e. Null Value Data Types in Hive

In this Hive Data Types, missing values are represented by the special value NULL.

3. Conclusion

Hence, in this Apache Hive data type tutorial, we have discussed all the data types in Hive in detail. Hope this blog will help you to understand all the data types in hive easily. In the next section, we will discuss the Hive Operators in detail.