Sunday, December 24, 2017

Hive alter table DDL to rename table and add/replace columns

Rename Tables

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show tables;
+----------------------+--+
|       tab_name       |
+----------------------+--+
| datatypedemo         |
| employees            |
| employees2           |
| employees3           |
| hivedatatypedemo     |
| iotdatademo          |
| monthly_taxi_fleet   |
| monthly_taxi_fleet2  |
| monthly_taxi_fleet3  |
| monthly_taxi_fleet4  |
| sales_data           |
| sales_data_dup       |
+----------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet4 monthly_taxi_fleet5;
Error: Error while compiling statement: FAILED: ParseException line 1:32 cannot recognize input near 'monthly_taxi_fleet5' '<EOF>' '<EOF>' in alter table statement (state=42000,code=40000)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet4 rename to monthly_taxi_fleet5;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show tables;
+----------------------+--+
|       tab_name       |
+----------------------+--+
| datatypedemo         |
| employees            |
| employees2           |
| employees3           |
| hivedatatypedemo     |
| iotdatademo          |
| monthly_taxi_fleet   |
| monthly_taxi_fleet2  |
| monthly_taxi_fleet3  |
| monthly_taxi_fleet5  |
| sales_data           |
| sales_data_dup       |
+----------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7) COMMENT '',                                                |
|   `company` varchar(50) COMMENT '',                                          |
|   `fleet` int COMMENT '')                                                    |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'                             |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'               |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'              |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='true',                                            |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514072478',                                         |
|   'numFiles'='2',                                                            |
|   'numRows'='20',                                                            |
|   'rawDataSize'='0',                                                         |
|   'totalSize'='1230',                                                        |
|   'transient_lastDdlTime'='1514072478')                                      |
+------------------------------------------------------------------------------+—+

Add columns

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 add columns (c1 date);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7) COMMENT '',                                                |
|   `company` varchar(50) COMMENT '',                                          |
|   `fleet` int COMMENT '',                                                    |
|   `c1` date COMMENT '')                                                      |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'                             |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'               |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'              |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='false',                                           |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514072785',                                         |
|   'numFiles'='2',                                                            |
|   'numRows'='-1',                                                            |
|   'rawDataSize'='-1',                                                        |
|   'totalSize'='1230',                                                        |
|   'transient_lastDdlTime'='1514072785')                                      |
+------------------------------------------------------------------------------+—+

Drop Columns

REPLACE COLUMNS removes all existing columns and adds the new set of columns. This can be done only for tables with a native SerDe (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe). Refer to Hive SerDe for more information. REPLACE COLUMNS can also be used to drop columns. For example, "ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" will remove column 'c' from test_change's schema.

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 replace columns (month char(7), company varchar(50),fleet smallint);
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Replace columns is not supported for table default.monthly_taxi_fleet5. SerDe may be incompatible. (state=42000,code=1)

Use Parquet to demostrate column drop


0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> drop table monthly_taxi_fleet5;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> create table monthly_taxi_fleet5 (
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> month char(7), company varchar(50),fleet smallint)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> stored as parquet;


0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert overwrite table monthly_taxi_fleet5
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet3 limit 10;


0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7),                                                           |
|   `company` varchar(50),                                                     |
|   `fleet` smallint)                                                          |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'              |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'            |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'           |

| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='true',                                            |
|   'numFiles'='1',                                                            |
|   'numRows'='10',                                                            |
|   'rawDataSize'='30',                                                        |
|   'totalSize'='628',                                                         |
|   'transient_lastDdlTime'='1514073272')                                      |
+------------------------------------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet5;
+----------------------------+------------------------------+----------------------------+--+
| monthly_taxi_fleet5.month  | monthly_taxi_fleet5.company  | monthly_taxi_fleet5.fleet  |
+----------------------------+------------------------------+----------------------------+--+
| 2005-02                    | CityCab                      | 4968                       |
| 2005-02                    | Comfort                      | 10046                      |
| 2005-01                    | Premier                      | 370                        |
| 2005-01                    | TransCab                     | 560                        |
| 2005-01                    | Smart                        | 320                        |
| 2005-01                    | Individual Yellow- Top       | 696                        |
| 2005-01                    | YTC                          | 1223                       |
| 2005-01                    | SMRT                         | 2441                       |
| 2005-01                    | CityCab                      | 4965                       |
| 2005-01                    | Comfort                      | 9952                       |
+----------------------------+------------------------------+----------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 add columns (c1 date);
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Parquet does not support date. See HIVE-6384 (state=08S01,code=1)

https://issues.apache.org/jira/browse/HIVE-6384

Date added into Parquet in Hive 1.2.0 https://issues.apache.org/jira/browse/HIVE-8119


0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 add columns (c1 timestamp);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet5;

+----------------------------+------------------------------+----------------------------+-------------------------+--+
| monthly_taxi_fleet5.month  | monthly_taxi_fleet5.company  | monthly_taxi_fleet5.fleet  | monthly_taxi_fleet5.c1  |
+----------------------------+------------------------------+----------------------------+-------------------------+--+
| 2005-02                    | CityCab                      | 4968                       | NULL                    |
| 2005-02                    | Comfort                      | 10046                      | NULL                    |
| 2005-01                    | Premier                      | 370                        | NULL                    |
| 2005-01                    | TransCab                     | 560                        | NULL                    |
| 2005-01                    | Smart                        | 320                        | NULL                    |
| 2005-01                    | Individual Yellow- Top       | 696                        | NULL                    |
| 2005-01                    | YTC                          | 1223                       | NULL                    |
| 2005-01                    | SMRT                         | 2441                       | NULL                    |
| 2005-01                    | CityCab                      | 4965                       | NULL                    |
| 2005-01                    | Comfort                      | 9952                       | NULL                    |
+----------------------------+------------------------------+----------------------------+-------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7),                                                           |
|   `company` varchar(50),                                                     |
|   `fleet` smallint,                                                          |
|   `c1` timestamp)                                                            |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'              |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'            |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'           |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='false',                                           |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514073343',                                         |
|   'numFiles'='1',                                                            |
|   'numRows'='-1',                                                            |
|   'rawDataSize'='-1',                                                        |
|   'totalSize'='628',                                                         |
|   'transient_lastDdlTime'='1514073343')                                      |
+------------------------------------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 replace columns (month char(7), company varchar(50),fleet smallint);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7),                                                           |
|   `company` varchar(50),                                                     |
|   `fleet` smallint)                                                          |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'              |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'            |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'           |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='false',                                           |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514073374',                                         |
|   'numFiles'='1',                                                            |
|   'numRows'='-1',                                                            |
|   'rawDataSize'='-1',                                                        |
|   'totalSize'='628',                                                         |
|   'transient_lastDdlTime'='1514073374')                                      |
+------------------------------------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet5;
+----------------------------+------------------------------+----------------------------+--+
| monthly_taxi_fleet5.month  | monthly_taxi_fleet5.company  | monthly_taxi_fleet5.fleet  |
+----------------------------+------------------------------+----------------------------+--+
| 2005-02                    | CityCab                      | 4968                       |
| 2005-02                    | Comfort                      | 10046                      |
| 2005-01                    | Premier                      | 370                        |
| 2005-01                    | TransCab                     | 560                        |
| 2005-01                    | Smart                        | 320                        |
| 2005-01                    | Individual Yellow- Top       | 696                        |
| 2005-01                    | YTC                          | 1223                       |
| 2005-01                    | SMRT                         | 2441                       |
| 2005-01                    | CityCab                      | 4965                       |
| 2005-01                    | Comfort                      | 9952                       |
+----------------------------+------------------------------+----------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def>

Reference URLs: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/Partition/Column