Altering and Dropping Tables in Hive

9/11/2025
All Articles

Altering and Dropping Tables in Hive

Altering and Dropping Tables in Hive

Altering and Dropping Tables in Hive

The ALTER TABLE statement in Hive provides various functionalities to modify the structure and properties of an existing table. Renaming a Table.

Step 1: Altering Tables in Hive

Hive allows you to modify table structure and properties using the ALTER TABLE command.

1.1 Add Columns

Add new columns to an existing table (only appends at the end):

ALTER TABLE employees ADD COLUMNS (birth_date STRING, hire_date STRING);

⚡ Note: Hive does not support removing specific columns directly.

1.2 Change Column Name or Data Type

Rename a column or change its data type:

ALTER TABLE employees CHANGE COLUMN name full_name STRING;

✅ Make sure to specify the full column definition again when renaming.

1.3 Replace All Columns

Replace the entire schema (column list) of the table:

ALTER TABLE employees REPLACE COLUMNS (
  emp_id INT,
  full_name STRING,
  salary DOUBLE
);

1.4 Change Table Location

Update the HDFS location of a table (mostly for external tables):

ALTER TABLE customers SET LOCATION '/new/location/customers';

1.5 Change File Format

Convert table storage format to another type (e.g., from TEXTFILE to PARQUET):

ALTER TABLE employees SET FILEFORMAT PARQUET;

1.6 Change Table Properties

Add or update table properties:

ALTER TABLE employees SET TBLPROPERTIES ('comment'='Employee master table');

Step 2: Dropping Tables in Hive

You can drop tables to remove them from the Hive Metastore.

2.1 Drop a Managed Table

Managed tables delete both metadata and data from the warehouse location:

DROP TABLE employees;

2.2 Drop an External Table

External tables only remove metadata — data files remain intact:

DROP TABLE customers;

⚠️ This is useful when you want to keep the raw data files but remove the table definition.

Step 3: Other Table Maintenance Commands

  • View table schema and details:

DESCRIBE FORMATTED employees;

  • Rename a table:

ALTER TABLE employees RENAME TO employees_archive;

  • Recover partitions if data is added manually:

MSCK REPAIR TABLE logs;

Summary

  • Use ALTER TABLE to modify schema, properties, location, or file format.

  • Use DROP TABLE carefully—managed tables delete data, external tables do not.

  • Always back up important data and Hive Metastore before making changes.

Article