Once a table is created, we can still modify its metadata, such as adding new columns and changing the column's data type. In HQL, we use the ALTER command to modify the table's metadata. However, alter table is not able to update the data accordingly. We should make sure the actual data conforms to the metadata definition manually, otherwise the query will return nothing in expectation.

The following are examples for altering tables in HQL:

1. Rename a table with the ALTER statement. This is quite often used as data backup:

      > ALTER TABLE cte_employee RENAME TO cte_employee_backup;
      No rows affected (0.237 seconds)

2. Change the table properties with TBLPROPERTIES:

      > ALTER TABLE c_employee SET TBLPROPERTIES 
      ('comment'='New comments');
      No rows affected (0.239 seconds)

3. Change the table's row format and SerDe (SerDe is introduced in Chapter 8, Extensibility Considerations) with SERDEPROPERTIES:

      > ALTER TABLE employee_internal SET SERDEPROPERTIES 
      ('field.delim' = '$');
      No rows affected (0.148 seconds)

4. Change the table's file format with FILEFORMAT:

      > ALTER TABLE c_employee SET FILEFORMAT RCFILE;
      No rows affected (0.235 seconds) 

5. Change the table's location, a full URI of HDFS, with LOCATION:

      > ALTER TABLE c_employee SET LOCATION 
      'hdfs://localhost:9000/tmp/employee'; 
      No rows affected (0.169 seconds)

6. Enable/Disable the table's protection; NO_DROP or OFFLINE. NO_DROP prevents a table from being dropped, while OFFLINE prevents data (not metadata) from being queried in a table:

      > ALTER TABLE c_employee ENABLE NO_DROP; 
      > ALTER TABLE c_employee DISABLE NO_DROP; 
      > ALTER TABLE c_employee ENABLE OFFLINE;
      > ALTER TABLE c_employee DISABLE OFFLINE;

7. Enable concatenation in an RCFile, or ORC table if it has many small files:

      > ALTER TABLE c_employee SET FILEFORMAT ORC; -- Convert to ORC
      No rows affected (0.160 seconds)
      

      > ALTER TABLE c_employee CONCATENATE;
      No rows affected (0.165 seconds)

8. Change the column's data type, position (with AFTER or FIRST), and comment:

      > DESC employee_internal; -- Check column type before alter
      +----------------+-------------------------------+----------+
      |    col_name    |          data_type            | comment  |
      +----------------+-------------------------------+----------+
      | employee_name  | string                        |          |
      | work_place     | array<string>                 |          |
      | gender_age     | struct<gender:string,age:int> |          |
      | skills_score   | map<string,int>               |          |
      | depart_title   | map<string,array<string>>     |          |
      +----------------+-------------------------------+----------+
      5 rows selected (0.119 seconds)
      

      > ALTER TABLE employee_internal 
      > CHANGE name employee_name string AFTER gender_age;
      No rows affected (0.23 seconds)
      

      > DESC employee_internal; -- Verify type and order changes above
      +----------------+-------------------------------+----------+
      |    col_name    |          data_type            | comment  |
      +----------------+-------------------------------+----------+
      | work_place     | array<string>                 |          |
      | gender_age     | struct<gender:string,age:int> |          |
      | employee_name  | string                        |          |
      | skills_score   | map<string,int>               |          |
      | depart_title   | map<string,array<string>>     |          |
      +----------------+-------------------------------+----------+
      5 rows selected (0.214 seconds)
      

      > ALTER TABLE employee_internal 
      > CHANGE employee_name name string COMMENT 'updated' FIRST;
      No rows affected (0.238 seconds)
      
      > DESC employee_internal; -- Verify changes by FRIST keywords
      +---------------+-------------------------------+----------+
      |   col_name    |          data_type            | comment  |
      +---------------+-------------------------------+----------+
      | name          | string                        | updated  |
      | work_place    | array<string>                 |          |
      | gender_age    | struct<gender:string,age:int> |          |
      | skills_score  | map<string,int>               |          |
      | depart_title  | map<string,array<string>>     |          |
      +---------------+-------------------------------+----------+
      5 rows selected (0.119 seconds)

9. Add new columns to a table:

      > ALTER TABLE c_employee ADD COLUMNS (work string);
      No rows affected (0.184 seconds)
      

      > DESC c_employee;      
      +-----------+------------+----------+
      | col_name  | data_type  | comment  |
      +-----------+------------+----------+
      | name      | string     |          |
      | work      | string     |          |
      +-----------+------------+----------+
      2 rows selected (0.115 seconds)

10. Replace all columns in a table using the new columns specified:

      > ALTER TABLE c_employee REPLACE COLUMNS (name string);
      No rows affected (0.132 seconds)
      

      > DESC c_employee; -- Verify the changes
      +-----------+------------+----------+
      | col_name  | data_type  | comment  |
      +-----------+------------+----------+
      | name      | string     |          |
      +-----------+------------+----------+
      1 row selected (0.129 seconds)