HCatalog - Alter Table
This chapter explains how to alter the attributes of a table such as changing its table name, changing column names, adding columns, and deleting or replacing columns.
Alter Table Statement
You can use the ALTER TABLE statement to alter a table in Hive.
The statement takes any of the following syntaxes based on what attributes we wish to modify in a table.
ALTER TABLE name RENAME TO new_name ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...]) ALTER TABLE name DROP [COLUMN] column_name ALTER TABLE name CHANGE column_name new_name new_type ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
Some of the scenarios are explained below.
Rename To… Statement
The following query renames a table from employee to emp.
./hcat –e "ALTER TABLE employee RENAME TO emp;"
The following table contains the fields of employee table and it shows the fields to be changed (in bold).
|Field Name||Convert from Data Type||Change Field Name||Convert to Data Type|
The following queries rename the column name and column data type using the above data −
./hcat –e "ALTER TABLE employee CHANGE name ename String;" ./hcat –e "ALTER TABLE employee CHANGE salary salary Double;"
Add Columns Statement
The following query adds a column named dept to the employee table.
./hcat –e "ALTER TABLE employee ADD COLUMNS (dept STRING COMMENT 'Department name');"
The following query deletes all the columns from the employee table and replaces it with emp and name columns −
./hcat – e "ALTER TABLE employee REPLACE COLUMNS ( eid INT empid Int, ename STRING name String);"
Drop Table Statement
This chapter describes how to drop a table in HCatalog. When you drop a table from the metastore, it removes the table/column data and their metadata. It can be a normal table (stored in metastore) or an external table (stored in local file system); HCatalog treats both in the same manner, irrespective of their types.
The syntax is as follows −
DROP TABLE [IF EXISTS] table_name;
The following query drops a table named employee −
./hcat –e "DROP TABLE IF EXISTS employee;"
On successful execution of the query, you get to see the following response −
OK Time taken: 5.3 seconds