Hive Create Table

The Hive Data Definition Language (DDL) operations that we can perform on any Hive Table are

  • CREATE
  • DROP
  • TRUNCATE 

Here i am going to show you how to create a table in hive and in the following posts i will show you how to use DROP and TRUNCATE.

Where and How  can we use Hive CREATE ?

We can use CREATE in the following ways in Hive

Hive CREATE Table

Here let us see only how to create the simple Hive Table and in other posts i have discussed about other Topics.
Syntax:

Let us take an example as Creating the Student Table in Hive, if you want to know how to create Hive Managed table click here

once we execute the above Hive Create statement, we just created the Schema called STUDENT but we have not inserted or Loaded any data into this STUDENT Table, though i am going to have separate post about Hive INSERT, let us not worry about how Hive Insert works over here.


LOAD Data into Hive Table:

Loading the Data into above created STUDENT Schema.


Using SELECT :

I am going to use Hive Select to view Hive Table Data.


CREATE  TABLE AS SELECT (CTAS)

SELECT Query is to select or project the data from Hive Table, Here let us see also how to create a new table using SELECT Query results,  I am going to select values from Student table:

Using CTAS ( CREATE TABLE AS SELECT)
We are going to create a new table called College_Student by selecting only std_names values from Student table.
Hive Query Language:

Output Results:
Let us now project the College Students.


CREATE TABLE LIKE

Don’t confuse this  with Create Table As(CTAS), because there is a difference between these 2 statements.

If we use CTAS, it creates a table with the Select query output data, hence once after creating the new Table we will have the Data but  if we use CREATE TABLE LIKE will not have a Data but the it will have exactly the schema details of alike Table from where it is created.

SchoolStudnt is the table created with exactly same schema from Student table, which was created earlier but newly created table will not have the data, It will be Empty.

Let us perform Select on this table SchoolStudent table.

I hope you have understood about how to use CREATE DDL Statement in Hive, if you have any Questions post your comments below.


Casual ClassRoom Video about Hive Create Table