Hive Create Table Command and Examples

The syntax of creating a Hive table is quite similar to creating a table using SQL. In this article explains Hive create table command and examples to create table in Hive command line interface. You will also learn on how to load data into created Hive table.

Hive Create Table Command

Hive Create Table statement is used to create table. You can also create the table hive while importing data using Sqoop command. To use, Sqoop create Hive table command, you should specify the –create-hive-table option in Sqoop command.

You can read about Apache Sqoop import and related below posts:

In this Hive tutorial, we will stick to create table in Hive using command line. Below is the Hive create table syntax:

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
 (col_name data_type [COMMENT 'col_comment'],, ...)
 [PARTITIONED BY (col_name data_type [COMMENT 'col_comment'], ...)]
 [COMMENT 'table_comment']
 [ROW FORMAT row_format]
 [FIELDS TERMINATED BY char]
 [LINES TERMINATED BY char]
 [LOCATION 'hdfs_path']
 [STORED AS file_format];

You can provide the HDFS file location if you are creating external table

Read:

Hive Create Table Example

Below is the example to create table in Hive command line:

 CREATE TABLE IF NOT EXISTS test_table
 (col1 int COMMENT 'Integer Column',
 col2 string COMMENT 'String Column'
 )
 COMMENT 'This is test table'
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 STORED AS TEXTFILE;

You can use IF NOT EXISTS to avoid the error in case table is already present. Hive will just ignore if mentioned table is present, otherwise it will create it.

LOAD Data to Hive Table

There are two way to load data into Hive table.

Hive INSERT statement

Use this statement to insert the single row to the Hive table. You can INSERT only one row at a time. Below is the example of using Hive INSERT statement:

hive> insert into test_table values(1,'aaa');
hive> insert into test_table values(2,'bbb');

Hive LOAD DATA Statement

You can use the LOAD DATA statement to store bulk records into Hive tables. You can either load file from local directory or Hadoop HDFS file.

Hive LOAD DATA Statement Syntax

Below is the Hive LOAD DATA command syntax:

LOAD DATA [LOCAL] INPATH 'file_path' [OVERWRITE] INTO TABLE table_name 
[PARTITION (partcol1=val1, partcol2=val2 ...)]

Hive ACID Support

Hive version 0.14 and above supports ACID transactions:

You can read more in my other post:

Apache Hive Table Update using ACID Transactions and Examples

Hive LOAD DATA Statement

Below is the example of loading local file to Hive table:

hive> LOAD DATA LOCAL INPATH '/home/impadmin/test_table.csv' OVERWRITE INTO TABLE test_table;

Read other articles on Apache Hive