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 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); Use Parquet to demostrate column drop
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') |
+------------------------------------------------------------------------------+—+
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)
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
No comments:
Post a Comment