impala ALTER TABLE

The Alter table statement in Impala is used to make changes to a given table. W ith this statement, we can add, delete, or modify columns from existing tables, or rename them.

This chapter explains various types of alter statements through syntax and examples. Let's start by assuming that my_db a table called customers in Impala's database that has the following data

ID   NAME     AGE   ADDRESS    SALARY
--- --------- ----- ----------- --------
1   Ramesh    32    Ahmedabad   20000
2   Khilan    25    Delhi       15000
3   Hardik    27    Bhopal      40000
4   Chaitali  25    Mumbai      35000
5   kaushik   23    Kota        30000
6   Komal     22    Mp          32000

Also, if you get a list my_db tables in the database, you can find the customers table in it, as shown below.

[quickstart.cloudera:21000] > show tables;

Query: show tables 
+-----------+ 
| name      | 
+-----------+ 
| customers | 
| employee  | 
| student   | 
| student1  | 
+-----------+

Change the name of the table

Grammar

ALTER TABLE renames the basic syntax of an existing table as follows -

ALTER TABLE [old_db_name.]old_table_name RENAME TO [new_db_name.]new_table_name

Cases

The following is an example of using an alter statement to change the table name. H ere we change the name of the table customer to the user.

[quickstart.cloudera:21000] > ALTER TABLE my_db.customers RENAME TO my_db.users;

After performing the above query, Impala changes the name of the table as needed and displays the following message.

Query: alter TABLE my_db.customers RENAME TO my_db.users

You can use the show tables statement to verify the list of tables in the current database. Y ou can find a table named users instead of customers.

Query: show tables 
+----------+ 
| name     | 
+----------+ 
| employee | 
| student  | 
| student1 | 
| users    | 
+----------+ 
Fetched 4 row(s) in 0.10s

Add columns to the table

Grammar

ALTER TABLE adds columns to an existing table with the following basic syntax -

ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])

Cases

The following query is an example of how to add columns to an existing table. H ere we add two columns in the account_no and phone_number (both bigint data types).

[quickstart.cloudera:21000] > ALTER TABLE users ADD COLUMNS (account_no BIGINT, 
phone_no BIGINT);

When you execute the query above, it adds the specified column to a table named student and displays the following message.

Query: alter TABLE users ADD COLUMNS (account_no BIGINT, phone_no BIGINT)

If you verify the pattern of the table user, you can find the newly added column in it, as shown below.

quickstart.cloudera:21000] > describe users;
 
Query: describe users 
+------------+--------+---------+ 
| name       | type   | comment | 
+------------+--------+---------+ 
| id         | int    |         | 
| name       | string |         | 
| age        | int    |         |
| address    | string |         | 
| salary     | bigint |         | 
| account_no | bigint |         | 
| phone_no   | bigint |         | 
+------------+--------+---------+ 
Fetched 7 row(s) in 0.20s

Remove the column from the table

Grammar

The basic syntax from ALTER TABLE to DROP COLUMN in the existing table is as follows -

ALTER TABLE name DROP [COLUMN] column_name

Cases

The following query is an example of removing columns from an existing table. H ere we remove the column account_no the name.

[quickstart.cloudera:21000] > ALTER TABLE users DROP account_no;

When executing the query above, Impala deletes the column named count and does not display the following message.

Query: alter TABLE users DROP account_no

If you verify the pattern of the table user, you will not find a column named account_no after deletion.

[quickstart.cloudera:21000] > describe users; 

Query: describe users 
+----------+--------+---------+ 
| name     | type   | comment | 
+----------+--------+---------+ 
| id       | int    |         | 
| name     | string |         | 
| age      | int    |         | 
| address  | string |         | 
| salary   | bigint |         | 
| phone_no | bigint |         |
+----------+--------+---------+ 
Fetched 6 row(s) in 0.11s

Change the name and type of the column

Grammar

ALTER TABLE changes the name of the column in the existing table and the basic syntax of the data type is as follows -

ALTER TABLE name CHANGE column_name new_name new_type

Cases

The following is an example of using an alter statement to change the name and data type of a column. H ere we change the phone_no the column to e-mail and its data type to a string.

[quickstart.cloudera:21000] > ALTER TABLE users CHANGE phone_no e_mail string;

When the query above is executed, Impala performs the specified changes, displaying the following message.

Query: alter TABLE users CHANGE phone_no e_mail string

You can use the describe statement to validate the metadata of the table user. Y ou can observe that Impala has made the necessary changes to the specified column.

[quickstart.cloudera:21000] > describe users; 
Query: describe users 
+----------+--------+---------+ 
| name     | type   | comment | 
+----------+--------+---------+ 
| id       | int    |         | 
| name     | string |         | 
| age      | int    |         | 
| address  | string |         | 
| salary   | bigint |         | 
| phone_no | bigint |         |
+----------+--------+---------+ 
Fetched 6 row(s) in 0.11s

Use Hue to change the table

Open the Impala query editor and type the alter statement in it, and then click the execute button, as shown in the screenshot below.

When you perform the query above, it changes the name of the table customer to the user. I n the same way, we can execute all the alter queries.

---