HIVE CREATE TABLE Statement

CREATE TABLE statement is used to create a new HIVE table.

Syntax:

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(column1 datatype [COMMENT column1_comment], column2 datatype [COMMENT column2_comment]...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION 'full_hdfs_path']

CREATE TABLE Example - Managed Table

The following SQL statement creates a table called "Employee":

CREATE TABLE Employee (
EMP_ID bigint,
NAME string,
city string,
SALARY string,
);

CREATE TABLE Example - External Table

Let us suppose we have a file named 'employee.csv' in HDFS in '/data/employee.csv' location.The sample file is displayed below:

employee.csv

emp_id,name,city,salary
101,John,California,50000
102,Tom,Mumbai,40000
103,Harry,Delhi,80000
104,Rahul,Bangalore,60000

Csv files are comma separated files. As you can see above emp_id, name, city, salary are the headers and just below are the values. And as the name suggests (Comma separated values), all the values are separated by commas.

Now, when we are creating a table with the CREATE command, we will be telling HIVE the exact location of the 'employee.csv' file. And HIVE will intelligently map the table with the 'employee.csv' file.

The CREATE command is listed below:

CREATE EXTERNAL TABLE if not exists Employee (
EMP_ID bigint;
NAME string;
city string;
SALARY string;
)
comment "The table contains Employee information"
row format delimited fields terminated by ','
location '/data/';

The EXTERNAL keyword in the CREATE command says the table is an external table and the 'location' keyword mentions the path of the external file(/data/employee.csv). Most importantly the 'row format delimited' statement tells HIVE that every element of 'employee.csv' is separated by ','.

Note : The order of the attributes declared in the external table should be of the same order as the csv file.

When we trigger a select query, HIVE will return the same values in the form of a table.

emp_id	name	city	salary
101	John	California	50000
102	Tom	Mumbai	40000
103	Harry	Delhi	80000
104	Rahul	Bangalore	60000
105	Sakil	Delhi	90000

Note : For the above external table, if we delete the 'employee' table the actual data i.e employee.csv in '/data/' location won't be deleted, but the table will be dropped. i.e. the select query won't be returning results anymore.

❮❮ PREV NEXT ❯❯