This is a logical operation in Impala that modifies database or table metadata in the metastore database that Impala and Hive share. The majority of ALTER TABLE operations do not really rewrite, relocate, or otherwise modify the data files. The only exception is the RENAME TO clause, which can cause HDFS files to be migrated to alternate directories.
When you do an ALTER TABLE action, you may need to perform physical filesystem activities as well, such as rewriting data files to incorporate extra fields or converting them to a different file format.
In this blog, you’ll see the usage of ALTER to modify the database ownership, add or rename table columns, replace the table columns and modify the views.
Database:
Syntax:
ALTER DATABASE database_name SET OWNER USER user_name;
ALTER DATABASE database_name SET OWNER ROLE role_name;
Example:
ALTER DATABASE TestDB SET OWNER USER myUser;
ALTER DATABASE TestDB SET OWNER ROLE DataReader;
Table:
Syntax:
ALTER TABLE [old_db_name.]old_table_name RENAME TO [new_db_name.]new_table_name
ALTER TABLE name ADD [IF NOT EXISTS] COLUMNS (col_spec[, col_spec …])
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec …])
ALTER TABLE name ADD COLUMN [IF NOT EXISTS] col_spec
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name col_spec
ALTER TABLE name SET OWNER USER user_name ALTER TABLE name SET OWNER ROLE role_name
Examples:
ALTER TABLE tbStudent RENAME TO tbStudentDetails
The above command will rename the table from the existing database.
ALTER TABLE TestDB.tbStudent RENAME TO TestDB1.tbStudentDetails
The above command will move the table to a different database with a different name.
ALTER TABLE tbStudent ADD [IF NOT EXISTS] COLUMNS (iActiveFlag INT)
The above command will help in adding a new column to the table if not exists.
ALTER TABLE tbTestTable REPLACE COLUMNS (TestID INT, TestName STRING)
The above command will help in replacing the existing columns with a new set of columns.
ALTER TABLE tbTestTable DROP COLUMN TestName
ALTER TABLE tbTestTable DROP TestName;
The keyword “COLUMN” is optional when removing a column from a table.
ALTER TABLE tbTestTable CHANGE TestID BIGINT;
The above command will help in changing the data type of the column.
ALTER TABLE tbTestTable SET OWNER USER myUser
ALTER TABLE tbTestTable SET OWNER ROLE DataReader
The above command will help in changing the owner of the table.
View:
Syntax:
ALTER VIEW [database_name.]view_name
[(column_name [COMMENT ‘column_comment’][, …])]
AS select_statement;
Example:
ALTER VIEW TestDB.vwStudentDetails (StudentID INT, StudentName STRING)
AS
SELECT StudentID, StudentName FROM tbStudentDetails;
ALTER VIEW [database_name.]view_name
RENAME TO [database_name.]view_name;
Example:
ALTER VIEW TestDB.vwStudentDetails RENAME TO TestDB.vwStudentInformation;
The above command will rename the existing view. It also helps in moving a view from one database to another.
ALTER VIEW [database_name.]view_name SET OWNER USER user_name;
ALTER VIEW [database_name.]view_name SET OWNER ROLE role_name;
Example:
ALTER VIEW vwStudentDetails SET OWNER USER myUser;
ALTER VIEW vwStudentDetails SET OWNER ROLE DataReader;
The above commands will change the owner of the view.
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 
One comment