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:
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[ROW FORMAT row_format]
[STORED AS file_format]
For example, suppose you want to create a Table student, with the following attributes:
id, the student’s ID number, the type is
name, the type is String,
age, the type is
score, the type is
hive (user_db)> CREATE TABLE IF NOT EXISTS students_db.student ( id int,
> name String,
> age int, score Float)
> COMMENT 'Description of the table'
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> LINES TERMINATED BY '\n'
> STORED AS TEXTFILE;
Time taken: 2.357 seconds
hive (user_db)> describe students_db.student;
Time taken: 0.04 seconds, Fetched: 4 row(s)
- If you’re not currently working in the target database, you can prefix a database name,
students_dbin this case,
- By adding the option
IF NOT EXISTS, Hive ignores the statement in case the table already exists.
- 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 TABLEstatements 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:
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: