Apache Hive Usage Example - Create Hive Table

In this post, I describe how to create a Hive Table.

Create Table Statement

The syntax and example to create a Hive table is as follows:

Syntax

For example, suppose you want to create a Table student, with the following attributes:

1. id, the student’s ID number, the type is int,
2. name, the type is String,
3. age, the type is int,
4. score, the type is Float,

Note:

1. If you’re not currently working in the target database, you can prefix a database name, students_db in this case,
2. By adding the option IF NOT EXISTS, Hive ignores the statement in case the table already exists.
3. Pay Attention. There is no warn if the schema specified differs from the schema in the table that already exists. If want to use the new schema, you can drop the old table, thus losing your data, and then re-create it. A better way is to use ALTER TABLE statements to change the existing table schema instead.

The Data types Supported By Hive HCatLoader:

Hive 0.12.0 and earlier releases support reading these Hive primitive data types with HCatLoader:

• boolean
• int
• long
• float
• double
• string
• binary

and these complex data types:

• map – key type should be string
• List<any type>
• struct<any type fields>

Types in Hive 0.13.0 and Later added support for reading these Hive data types with HCatLoader:

• tinyint
• smallint
• date
• timestamp
• decimal
• char(x)
• varchar(x)