Hive Create Table: Internal, Alter & Drop with Examples

By : David Carter David Carter
Hours Updated

⚡ Smart Summary

Creating, altering and dropping tables in Apache Hive uses familiar SQL-style DDL, but the outcome depends on whether the table is internal, which Hive owns, or external, which merely describes files.

  • 🧱 CREATE TABLE: One statement defines the columns, the row format and, optionally, the storage location of a Hive table.
  • 🏷️ ALTER TABLE: RENAME changes the table name, and ADD COLUMNS or CHANGE COLUMN edit the schema without reloading data.
  • 🗑️ DROP TABLE: Dropping an internal table deletes both schema and data, while dropping an external table removes only the metadata.
  • 🔒 Internal is owned: An internal or managed table lives under the warehouse directory and Hive controls its full lifecycle.
  • 🔗 External is referenced: An external table points at files that other tools also read, so those files survive the drop.
  • 🔎 Verify the type: DESCRIBE FORMATTED reports MANAGED_TABLE or EXTERNAL_TABLE, which removes any guesswork before a drop.
Read More

Hive create, alter and drop table commands with examples

Table operations such as creation, altering, and dropping tables in Hive can be observed in this walkthrough. Each operation is shown first as a live session, then as a reusable statement.

How to Create, Alter and Drop a Table in Hive

In the below screenshot, we are creating a table with columns and altering the table name.

  1. Creating table guru_sample with two column names such as “empid” and “empname”
  2. Displaying tables present in the guru99 database
  3. guru_sample displaying under tables
  4. Altering table “guru_sample” as “guru_sampleNew”
  5. Again, when you execute the “show” command, it will display the new name guru_sampleNew

The session below runs all five steps in order at the hive> prompt, and the two SHOW TABLES calls before and after the rename make the effect visible.

Hive session creating guru_sample and renaming it to guru_sampleNew

Dropping table guru_sampleNew:

A single DROP TABLE statement removes the renamed table, and Hive answers with OK.

Hive shell dropping the guru_sampleNew table with an OK response

Hive CREATE TABLE Syntax and Common Clauses

The example above uses the shortest possible form. The documented statement accepts several optional clauses, and each one decides something the example leaves at its default.

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [COMMENT col_comment], ...)]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type, ...)]
  [CLUSTERED BY (col_name, ...) INTO num_buckets BUCKETS]
  [ROW FORMAT row_format]
  [STORED AS file_format]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)];
Clause What it controls
EXTERNAL Creates an external table, so DROP leaves the data files in place
TEMPORARY Creates a session-scoped table that disappears when the session ends
IF NOT EXISTS Suppresses the error when a table of that name already exists
PARTITIONED BY Splits the table into one directory per key value
CLUSTERED BY … INTO n BUCKETS Hashes rows into a fixed number of files
ROW FORMAT / STORED AS Sets the delimiter or SerDe and the file format, such as TEXTFILE, ORC or Parquet
LOCATION Points the table at a specific HDFS path instead of the warehouse default
TBLPROPERTIES Attaches metadata key-value pairs, including external.table.purge

A related form, CREATE TABLE new_table LIKE existing_table, copies a schema without copying any rows. Structural clauses such as PARTITIONED BY and CLUSTERED BY are covered in detail in the guide to Hive partitions and buckets.

Table types and its Usage

Coming to tables, it is just like the way that we create in traditional relational databases. The functionalities such as filtering and joins can be performed on the tables.

Hive deals with two types of table structures, Internal and External tables, depending on the loading and design of schema in Hive. The choice is not cosmetic: it decides who owns the data files and what happens to them when the table is dropped.

RELATED ARTICLES

Internal Tables in Hive

  • Internal Table is tightly coupled in nature. In this type of table, first we have to create the table and load the data.
  • We can call this one as data on schema.
  • By dropping this table, both data and schema will be removed.
  • The stored location of this table will be at /user/hive/warehouse.
  • Internal tables are also called managed tables, and only they support TRUNCATE, ARCHIVE, MERGE, CONCATENATE and ACID transactions.

When to Choose Internal Table?

  • If the processing data is available in the local file system
  • If we want Hive to manage the complete lifecycle of data including the deletion

Sample code Snippet for Internal Table

  1. To create the internal table 
     Hive>CREATE TABLE guruhive_internaltable (id INT,Name STRING);
	 Row format delimited 
	 Fields terminated by '\t';
  2. Load the data into internal table 
        Hive>LOAD DATA INPATH '/user/guru99hive/data.txt' INTO table guruhive_internaltable;
  3. Display the content of the table 
        Hive>select * from guruhive_internaltable;
  4. To drop the internal table 
        Hive>DROP TABLE guruhive_internaltable;

If you dropped the guruhive_internaltable, including its metadata and its data will be deleted from Hive. Unless PURGE is specified, the files are moved to the HDFS trash folder rather than removed immediately.

From the following screenshot, we can observe the output of all four statements in one session, ending with the successful drop.

Hive session creating, loading, querying and dropping guruhive_internaltable

In the above code and from the screenshot we do the following things,

  • Create the internal table
  • Load the data into internal table
  • Display the content of the table
  • To drop the internal table

External Tables in Hive

  • External Table is loosely coupled in nature. Data will be available in HDFS. The table is going to be created on HDFS data.
  • In other words, we can say it is creating schema on data.
  • At the time of dropping the table it drops only schema, the data will be still available in HDFS as before.
  • External tables provide an option to create multiple schemas for the data stored in HDFS instead of deleting the data every time whenever schema updates
  • From Hive 4.0.0, setting the table property external.table.purge to true makes DROP delete the data as well

When to Choose External Table?

  • If the processing data is available in HDFS
  • Useful when the files are being used outside of Hive

Sample code Snippet for External Table

  1. Create External table 
    Hive>CREATE EXTERNAL TABLE guruhive_external(id INT,Name STRING)
	 Row format delimited
	 Fields terminated by '\t'
	 LOCATION '/user/guru99hive/guruhive_external';
  2. If we are not specifying the location at the time of table creation, we can load the data manually 
        Hive>LOAD DATA INPATH '/user/guru99hive/data.txt' INTO TABLE guruhive_external;
  3. Display the content of the table 
      Hive>select * from guruhive_external;
  4. To drop the external table 
      Hive>DROP TABLE guruhive_external;

From the following screenshot, we can observe the output. Note that the drop at the end removes the schema only – the file under the LOCATION path is untouched.

Hive session creating, loading, querying and dropping guruhive_external

In the above code, we do the following things

  • Create the External table
  • Load the data into External table
  • Display the content of the table
  • Dropping external table

Difference between Internal Vs External Tables

Feature Internal External
Schema Data on Schema Schema on Data
Storage Location /user/hive/warehouse HDFS location given by LOCATION
Data availability Within local file system Within HDFS
Effect of DROP Deletes schema and data Deletes schema only, unless external.table.purge is true
TRUNCATE support Supported Not supported
ACID transactions Supported Not supported

The Apache Hive documentation states the rule plainly: Hive assumes it owns the data for managed tables and assumes it does not for external ones, and every difference above follows from that single assumption.

ALTER TABLE and DROP TABLE Command Reference

The screenshots above show only a rename and a drop. These are the statements a practitioner reaches for most often on an existing table.

Statement Purpose
ALTER TABLE table_name RENAME TO new_name; Renames the table; for a managed table this also moves its HDFS directory
ALTER TABLE table_name ADD COLUMNS (col_name data_type); Appends one or more columns to the end of the schema
ALTER TABLE table_name CHANGE COLUMN old_name new_name data_type; Renames a column or changes its type
ALTER TABLE table_name REPLACE COLUMNS (…); Replaces the whole column list with a new one
ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=’TRUE’); Converts a managed table into an external one, and FALSE reverses it
DROP TABLE [IF EXISTS] table_name [PURGE]; Removes the table; PURGE skips the trash folder so the data cannot be recovered
TRUNCATE [TABLE] table_name; Removes all rows but keeps the schema; managed tables only

Two safeguards are worth building into any script. IF EXISTS keeps a drop from failing on a table that was already removed, and DESCRIBE FORMATTED confirms whether the target is MANAGED_TABLE or EXTERNAL_TABLE before the drop runs.

FAQs

Run DESCRIBE FORMATTED table_name. The Table Type row reports either MANAGED_TABLE or EXTERNAL_TABLE. Checking before a drop is the cheapest way to avoid deleting data that other jobs still read.

Yes. ALTER TABLE table_name SET TBLPROPERTIES (‘EXTERNAL’=’TRUE’) flips the type in the metastore, and FALSE flips it back. The data files stay where they are, so no reload is needed.

Normally no, only the metastore entry goes. From Hive 4.0.0, however, an external table carrying the property external.table.purge set to true has its data deleted by DROP as well, so check the properties first.

A plain CREATE TABLE now produces a managed, transactional ORC table by default rather than a simple text table. Adding the EXTERNAL keyword is what keeps the older, non-ACID behaviour on a Hive 3 cluster.

TRUNCATE removes every row but keeps the schema, and it works on managed tables only. DROP removes the schema too. TRUNCATE is the safer choice when the table definition should survive a reload.

No. Hive folds identifiers to lowercase in the metastore, so GURU_SAMPLE and guru_sample refer to the same table. String values inside the data remain case-sensitive, which is why a WHERE comparison can still miss rows.

Yes. Machine learning features in catalog tools profile column cardinality, null rates and query patterns, then propose data types, file formats and partition keys. Treat the output as a draft, because the model cannot see planned workloads.

Copilot completes CREATE, ALTER and DROP statements from a short comment, and agentic assistants can produce a whole migration script. Review the EXTERNAL keyword and any PURGE option, because a wrong guess there deletes real data.

Summarize this post with: