---
description: Hive Create Table - Table Operations such as Creation, Altering, and Dropping tables in Hive can be observed in this section.
title: "Hive Create Table: Internal, Alter & Drop with Examples"
image: https://www.guru99.com/images/hive-create-table.png
---

[Skip to content](#main)

**⚡ 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](https://www.guru99.com/images/hive-create-table.png)

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]()](https://www.guru99.com/images/Hive/120415_1301_Dataoperati3.png)

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]()](https://www.guru99.com/images/Hive/120415_1301_Dataoperati4.png)

## 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](https://www.guru99.com/hive-partitions-buckets-example.html).

## 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](https://www.guru99.com/introduction-hive.html). The choice is not cosmetic: it decides who owns the data files and what happens to them when the table is dropped.

**Don't Miss:**

- [Hive View and Indexing: Create with Examples](https://www.guru99.com/hive-indexes-view-example.html)
- [Hive Join & SubQuery Tutorial with Examples](https://www.guru99.com/hive-join-subquery.html)
- [Hive Functions: Built-in & UDF (User Defined) Example](https://www.guru99.com/hive-user-defined-functions.html)
- [Top 40 Hive Interview Questions and Answers (2026)](https://www.guru99.com/hive-interview-questions.html)

## 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]()](https://www.guru99.com/images/Hive/120415_1301_Dataoperati5.png)

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]()](https://www.guru99.com/images/Hive/120415_1301_Dataoperati6.png)

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](https://hive.apache.org/docs/latest/language/managed-vs--external-tables/) 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

🔍 How do I check whether a Hive table is managed or external?

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.

🔄 Can a managed table be converted to external without reloading data?

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.

🧹 Does DROP TABLE ever delete the data of an external table?

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.

🧊 What changed for CREATE TABLE in Hive 3?

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.

✂️ What is the difference between TRUNCATE TABLE and DROP TABLE?

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.

🔤 Are Hive table and column names case-sensitive?

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.

🤖 Can AI help design a Hive table schema?

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.

🧑‍💻 Can GitHub Copilot generate Hive DDL statements?

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:

ChatGPTPerplexityGrokGoogle AI

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.**

##### Sign up for the newsletter

Subscribe for Free

You have successfully subscribed.  
Please check your inbox.

![AI-Newsletter]() Chosen by over **350,000+** professionals

[Scroll to top](#wrapper)

×

Search for:

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/hive-create-table.png","url":"https://www.guru99.com/images/hive-create-table.png","width":"700","height":"250","caption":"Hive Create Table","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/hive-create-alter-drop-table.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/hive","name":"Hive"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/hive-create-alter-drop-table.html","name":"Hive Create Table: Internal, Alter &#038; Drop with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/hive-create-alter-drop-table.html#webpage","url":"https://www.guru99.com/hive-create-alter-drop-table.html","name":"Hive Create Table: Internal, Alter &#038; Drop with Examples","dateModified":"2026-10-10T17:00:33+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/hive-create-table.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/hive-create-alter-drop-table.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"Hive","headline":"Hive Create Table: Internal, Alter &#038; Drop with Examples","description":"Hive Create Table - Table Operations such as Creation, Altering, and Dropping tables in Hive can be observed in this section.","keywords":"hive","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"dateModified":"2026-10-10T17:00:33+05:30","image":{"@id":"https://www.guru99.com/images/hive-create-table.png"},"copyrightYear":"2026","name":"Hive Create Table: Internal, Alter &#038; Drop with Examples","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"How do I check whether a Hive table is managed or external?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can a managed table be converted to external without reloading data?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Does DROP TABLE ever delete the data of an external table?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What changed for CREATE TABLE in Hive 3?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"What is the difference between TRUNCATE TABLE and DROP TABLE?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Are Hive table and column names case-sensitive?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can AI help design a Hive table schema?","acceptedAnswer":{"@type":"Answer","text":"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."}},{"@type":"Question","name":"Can GitHub Copilot generate Hive DDL statements?","acceptedAnswer":{"@type":"Answer","text":"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."}}]}],"@id":"https://www.guru99.com/hive-create-alter-drop-table.html#schema-1151994","isPartOf":{"@id":"https://www.guru99.com/hive-create-alter-drop-table.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/hive-create-alter-drop-table.html#webpage"}}]}
```
