Hive Create Table

A table in Hive is basically a logical representation of data stored in HDFS. Even though the actual data sits in files (like CSV, text, ORC, or Parquet files), Hive allows you to query it as if it were a structured table using HiveQL (which is similar to SQL).

Tables help you:

  • Organize data into rows and columns.
  • Apply filters, joins, and calculations.
  • Work with big data easily without writing complex code.

For example, you can have a table called employee_table where you store details like employee ID, name, salary, and department.

Why Do We Need to Create Tables in Hive?

Without tables, Hive won’t know how to read or process the data stored in Hadoop. Tables provide:

  • A schema (structure) for the data: what columns exist, what types of values are expected.
  • A storage location: where the actual data is kept in HDFS.
  • A way to query the data easily using simple HiveQL instead of complicated MapReduce code.
Syntax -

Hive makes creating tables very easy with a simple CREATE TABLE command. The basic structure looks like this:

CREATE TABLE table_name (
  column1_name column1_datatype,
  column2_name column2_datatype,
  ...
);

Types of Tables -

In Hive, you can create two main types of tables:

Managed Table (Internal Table) -

  • Hive manages both the table and the data.
  • If you drop the table, the data in HDFS is also deleted.
  • Default storage location: /user/hive/warehouse/tablename.

External Table -

  • Hive only manages the table structure; the data is stored outside Hive.
  • Dropping the table does not delete the actual data.
  • You must specify the LOCATION manually.
Examples -

Scenario1: Create a Simple Managed Table 

CREATE TABLE employee_table (
  emp_id INT,
  emp_name STRING,
  salary DOUBLE,
  department STRING
);

This creates a table named employee_table with four columns.

Scenario2: Create a Table with a Specific File Format

CREATE TABLE sales_table (
  sale_id INT,
  product STRING,
  sale_amount DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

This table expects data in CSV format stored as a text file in Hive.

Scenario3: Create an External Table

CREATE EXTERNAL TABLE customer_table (
  customer_id INT,
  customer_name STRING,
  city STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/external/customer_data';

This tells Hive to treat the data at the given HDFS location as a table without controlling the actual files.

How to Check the Table in Hive?

After you create the table, you can check if it’s there by using:

SHOW TABLES;

To see the structure of the table:

DESCRIBE employee_table;

To see full details (including location and properties):

DESCRIBE FORMATTED employee_table;