示例

• 将表名从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;

• 给users表添加一个creator属性，值为user1：

  可通过DESCRIBE EXTENDED/FORMATTED TABLE查看添加结果：

   ALTER TABLE users set tblproperties('creator' = 'user1'); --查看新增的属性DESCRIBE FORMATTED users;                           Describe Formatted Table                           ------------------------------------------------------------------------------ # col_name      data_type      comment                                        id      integer                                                               name      varchar                                                                                                                                           # Detailed Table Information                                                  Database:                   default                                           Owner:                      admintest                                         LastAccessTime:             0                                                 Location:                   hdfs://hacluster/user/hive/warehouse/users        Table Type:                 MANAGED_TABLE                                                                                                                   # Table Parameters:                                                                  STATS_GENERATED_VIA_STATS_TASK  workaround for potential lack of HIVE-12730            creator                 user1                                                            numFiles                0                                                                numRows                 0                                                                orc.compress.size       262144                                                           orc.compression.codec   GZIP                                                            orc.row.index.stride    10000                                                            orc.stripe.size         67108864                                                         presto_query_id         20210308_023136_00031_jiwsq@default@HetuEngine                   presto_version                                                                           rawDataSize             0                                                                totalSize               0                                                                transient_lastDdlTime   1615170696                                                                                                                                                                                    # Storage Information                                                         SerDe Library:              org.apache.hadoop.hive.ql.io.orc.OrcSerde         InputFormat:                org.apache.hadoop.hive.ql.io.orc.OrcInputFormat   OutputFormat:               org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat  Compressed:                 No                                                Num Buckets:                -1                                                Bucket Columns:             []                                                Sort Columns:               []                                                serialization.format:       1                                                (1 row) Query 20210308_032152_00046_jiwsq@default@HetuEngine, FINISHED, 1 nodeSplits: 1 total, 1 done (100.00%)0:00 [0 rows, 0B] [0 rows/s, 0B/s]

• 修改分区操作：

  --创建两个分区表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 到 T2ALTER 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（对列属性修改需要先修改Hive数据源properties属性，新增“hive.orc.use-column-names=true”配置，否则会报错）create table altercolumn1(a integer, b integer); 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 nodeSplits: 1 total, 1 done (100.00%)0:00 [0 rows, 0B] [0 rows/s, 0B/s]