示例

• 将表名从users修改为people：

  ALTER TABLE users RENAME TO people;

• 在表users中增加名为zip的列：

  ALTER TABLE users ADD COLUMN zip varchar;

• 从表users中删除名为zip的列：

  ALTER TABLE users DROP COLUMN zip;

• 将表users中列名id更改为user_id：

  ALTER TABLE users RENAME COLUMN id TO user_id;

• 修改分区操作：

  --创建两个分区表
  CREATE TABLE IF NOT EXISTS hetu_int_table5 (eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee Names' partitioned by (dt timestamp,country String, year int, bonus decimal(10,3)) STORED AS TEXTFILE;
   
  CREATE TABLE IF NOT EXISTS hetu_int_table6 (eid int, name String, salary String, destination String, dept String, yoj int) COMMENT 'Employee Names' partitioned by (dt timestamp,country String, year int, bonus decimal(10,3)) STORED AS TEXTFILE;
   
  --添加分区
  ALTER TABLE hetu_int_table5 ADD IF NOT EXISTS PARTITION (dt='2008-08-08 10:20:30.0', country='IN', year=2001, bonus=500.23) PARTITION (dt='2008-08-09 10:20:30.0', country='IN', year=2001, bonus=100.50) ;
   
  --查看分区
  show partitions hetu_int_table5;
             dt            | country | year |  bonus  
  -------------------------|---------|------|---------
   2008-08-09 10:20:30.000 | IN      | 2001 | 100.500 
   2008-08-08 10:20:30.000 | IN      | 2001 | 500.230 
  (2 rows)
   
  --删除分区
  ALTER TABLE hetu_int_table5 DROP IF EXISTS PARTITION (dt=timestamp '2008-08-08 10:20:30.0', country='IN', year=2001, bonus=500.23);
   
  --查看分区
  show partitions hetu_int_table5;
             dt            | country | year |  bonus  
  -------------------------|---------|------|---------
   2008-08-09 10:20:30.000 | IN      | 2001 | 100.500 
  (1 row)
   
  --迁移分区示例
  CREATE SCHEMA part_test;
  CREATE TABLE hetu_exchange_partition1 (a string, b string) PARTITIONED BY (ds string);
  CREATE TABLE part_test.hetu_exchange_partition2 (a string, b string) PARTITIONED BY (ds string);
  ALTER TABLE hetu_exchange_partition1 ADD PARTITION (ds='1');
   
  --查看分区
   show partitions hetu_exchange_partition1;
   ds 
  ----
   1  
  (1 row)
   
  show partitions part_test.hetu_exchange_partition2;
   ds 
  ----
  (0 rows)
   
  --迁移分区，从 T1 到 T2
  ALTER TABLE part_test.hetu_exchange_partition2 EXCHANGE PARTITION (ds='1') WITH TABLE hetu_exchange_partition1;
   
  --再次查看分区，可以看到分区迁移成功
  show partitions hetu_exchange_partition1;
   ds 
  ----
   (0 row)
   
  show partitions part_test.hetu_exchange_partition2;
   ds 
  ----
  1
  (1 rows)
   
  --重命名分区
  CREATE TABLE IF NOT EXISTS hetu_rename_table ( eid int, name String, salary String, destination String, dept String, yoj int) 
  COMMENT 'Employee details' 
  partitioned by (year int) 
  STORED AS TEXTFILE;
   
  ALTER TABLE hetu_rename_table ADD IF NOT EXISTS PARTITION (year=2001);
   
  SHOW PARTITIONS hetu_rename_table;
  year 
  ------
   2001 
  (1 row)
   
  ALTER TABLE hetu_rename_table PARTITION (year=2001) rename to partition (year=2020);
   
  SHOW PARTITIONS hetu_rename_table;
  year 
  ------
   2020 
  (1 row)
   
  --修改分区表
  create table altercolumn4(a integer, b string) partitioned by (c integer);
   
  --修改表的文件格式 
  alter table altercolumn4 SET FILEFORMAT textfile;
   
  insert into altercolumn4 values (100, 'Daya', 500);
   
  alter table altercolumn4 partition (c=500) change column b empname string comment 'changed column name to empname' first;
   
  --修改分区表的存储位置（需要先在hdfs上创建目录，执行语句后，无法查到之前插入的那条数据）
  alter table altercolumn4 partition (c=500) set Location '/user/hive/warehouse/c500';
   
  --修改列 b 改名为name，同时类型从integer转为string
  create table altercolumn1(a integer, b integer) stored as textfile;
   
  alter table altercolumn1 change column b name string;
   
  --修改altercolumn1的存储属性
  ALTER TABLE altercolumn1 CLUSTERED BY(a, name) SORTED BY(name) INTO 25 BUCKETS;
   
  --查看altercolumn1的属性
  describe formatted altercolumn1;
                                  Describe Formatted Table                                
  ----------------------------------------------------------------------------------------
   # col_name      data_type      comment                                                 
   a      integer                                                                         
   name      varchar                                                                      
                                                                                          
   # Detailed Table Information                                                           
   Database:                   default                                                    
   Owner:                      admintest                                                  
   LastAccessTime:             0                                                          
   Location:                   hdfs://hacluster/user/hive/warehouse/altercolumn1          
   Table Type:                 MANAGED_TABLE                                              
                                                                                          
   # Table Parameters:                                                                    
          STATS_GENERATED_VIA_STATS_TASK  workaround for potential lack of HIVE-12730              
          numFiles                0                                                                  
          numRows                 0                                                                  
          orc.compress.size       262144                                                             
          orc.compression.codec   GZIP                                                              
          orc.row.index.stride    10000                                                              
          orc.stripe.size         67108864                                                           
          presto_query_id         20210325_025238_00034_f63xj@default@HetuEngine                     
          presto_version                                                                             
          rawDataSize             0                                                                  
          totalSize               0                                                                  
          transient_lastDdlTime   1616640758                                                        
                                                                                                                                     
   # Storage Information                                                                  
   SerDe Library:              org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe         
   InputFormat:                org.apache.hadoop.mapred.TextInputFormat                   
   OutputFormat:               org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 
   Compressed:                 No                                                         
   Num Buckets:                25                                                         
   Bucket Columns:             [a, name]                                                  
   Sort Columns:               [SortingColumn{columnName=name, order=ASCENDING}]          
   Storage Desc Params:                                                                   
          serialization.format    1                                                                  
  (1 row)
   
  Query 20210325_090522_00091_f63xj@default@HetuEngine, FINISHED, 1 node
  Splits: 1 total, 1 done (100.00%)
  0:00 [0 rows, 0B] [0 rows/s, 0B/s]