Modifying Hive Tables with ALTER TABLE

Learn how to use the `ALTER TABLE` statement in Hive to modify existing tables. This guide covers renaming tables, adding columns, changing column definitions, and replacing columns, providing a flexible way to manage your Hive data schema.

ALTER TABLE in Hive

Introduction to ALTER TABLE

The `ALTER TABLE` statement in Hive allows you to modify existing tables. This includes renaming tables, adding columns, changing column definitions, and replacing columns. Hive's `ALTER TABLE` provides a SQL-like interface for these operations.

Renaming Tables

Syntax

ALTER TABLE old_table_name RENAME TO new_table_name;
      

Adding Columns

Syntax

ALTER TABLE table_name ADD COLUMNS (column_name data_type);
      

New columns are added with NULL values for existing rows.

Modifying Columns

Syntax

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_data_type;
      

This renames a column and optionally changes its data type.

Replacing Columns

In Hive, you can't directly drop columns. Instead, you replace the entire column set using `REPLACE COLUMNS`.

Syntax

ALTER TABLE table_name REPLACE COLUMNS (column1 data_type, column2 data_type, ...);
      

Illustrative Examples (using a sample 'Employees' table)

Let's assume you have an 'Employees' table initially:

1. Renaming the Table

ALTER TABLE Employees RENAME TO EmployeeData;
      

(The table is now named 'EmployeeData'.)

2. Adding a 'HireDate' Column

ALTER TABLE EmployeeData ADD COLUMNS (HireDate DATE);
      

3. Modifying the 'Department' Column

ALTER TABLE EmployeeData CHANGE COLUMN Department DepartmentName VARCHAR(100);
      

(The 'Department' column is now named 'DepartmentName' and has a larger VARCHAR size.)

4. Replacing Columns

ALTER TABLE EmployeeData REPLACE COLUMNS (
  EmployeeID INT,
  FullName STRING,
  HireDate DATE
);