1 修改字段类型、字段名、字段注释、类型长度、字段默认值

mysql修改字段类型：	 
--能修改字段类型、类型长度、默认值、注释
--对某字段进行修改
ALTER  TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度  新默认值  新注释;
 -- COLUMN关键字可以省略不写

alter  table table1 modify  column column1  decimal(10,1) DEFAULT NULL COMMENT '注释'; -- 正常，能修改字段类型、类型长度、默认值、注释

alter  table table1 modify column1  decimal(10,2) DEFAULT NULL COMMENT '注释'; 
-- 正常，能修改字段类型、类型长度、默认值、注释

mysql修改字段名：
ALTER  TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;	 
alter  table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释'; -- 正常，此时字段名称没有改变，能修改字段类型、类型长度、默认值、注释
alter  table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常，能修改字段名、字段类型、类型长度、默认值、注释
alter  table table1 change column2 column1 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常，能修改字段名、字段类型、类型长度、默认值、注释
alter  table table1 change column1 column2; -- 报错 

mysql> alter table white_user change column name nick_name  varchar(50) null comment '昵称'; -- 正确
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



2 修改表名

ALTER TABLE 旧表名 RENAME TO 新表名 ;

mysql> show tables ;
+-------------------+
| Tables_in_db_test |
+-------------------+
| white_user   |
+-------------------+
1 row in set (0.00 sec)



mysql> alter table white_user rename to white_user_new ;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables ;
+-------------------+
| Tables_in_db_test |
+-------------------+
| white_user_new    |
+-------------------+
1 row in set (0.00 sec)

3 修改表的注释

ALTER TABLE 表名 COMMENT '新注释'

mysql> alter table  white_user_new comment '新表-白名单表' ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table white_user_new ;
 CREATE TABLE `white_user_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='新表-白名单表' 




4

在指定位置插入新字段

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 是否可为空 COMMENT '注释' AFTER 指定某字段 ;
--COLUMN关键字可以省略不写

mysql> alter table white_user_new add column erp varchar(50) not null comment 'erp账号' after name ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
--在name字段后面添加erp字段

mysql> show create table white_user_new ;
CREATE TABLE `white_user_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `erp` varchar(50) NOT NULL COMMENT 'erp账号', 
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='新表-白名单表'      




mysql> alter table white_user_new add position varchar(50) not null comment '岗位' after name ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
--在name字段后面添加position字段。

mysql> show create table white_user_new ;                                      
CREATE TABLE `white_user_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `position` varchar(50) NOT NULL COMMENT '岗位',
  `erp` varchar(50) NOT NULL COMMENT 'erp账号',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='新表-白名单表'      



mysql> alter table white_user_new add  mobile varchar(50) not null comment '手机号码' before position ;
--报错，在position字段前添加mobile字段，不能使用before关键字


 

5 删除字段

ALTER TABLE 表名 DROP [COLUMN] 字段名 ;
--COLUMN关键字可以省略不写

mysql> alter table white_user_new drop column position ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table white_user_new drop erp ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table white_user_new ;                                      
CREATE TABLE `white_user_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='新表-白名单表'      





 

更多企业内的技术应用和使用技巧，请移步至我的公众号【程序员实用技能】

Hive 表修改字段类型

Alter table 表名  change column 原字段名称  现字段名称  数据类型

 

新增字段表

alter table 表名 add columns(字段名 数据类型)

 

 

一、需求场景

众所周知impala和hive是共用一天元数据。因为前期设计impala外部表的时候考虑不足，某些字段设置成int类型，但是随着业务增长数值已经超过了int的最大值，不能满足业务需求，所以要对某些int类型的字段进行修改字段类型为string。但是parquert在存储上，做了加密，二级制存储压缩，因为Impala对Parquet文件中列的顺序很敏感，所以在表的列定义与Parquet文件的列定义顺序不一致时，会导致Impala查询返回的结果与预期不一致。可以参考Impala的JIRA，https://issues.apache.org/jira/browse/IMPALA-779

场景:

impala修改表字段语法为：

ALTER TABLE name CHANGE column_name new_name new_type

#问题复现

ALTER TABLE test.page_mv CHANGE ware_goods_id ware_goods_id STRING;



在impala中我修改page_mv表(parquet表)的ware_goods_id字段从int改成string类型后select查询报错

ERROR: File '/user/hive/warehouse/page_mv/p_point_day=2019-10-21/p_point_type=1/fd4c71a99_data.0.parq' has an incompatible Parquet schema for column 'page_mv.ware_goods_id'. Column type: STRING, Parquet schema:
optional int32 ware_goods_id [i:69 d:1 r:0]

于是乎我改回int类型，查询恢复正常。

ALTER TABLE test.page_mv CHANGE ware_goods_id ware_goods_id STRING;



但是我确实需要把ware_goods_id字段类型从int改成string！！！于是我再次尝试

ALTER TABLE test.page_mv CHANGE ware_goods_id ware_goods_id STRING;



然后新建一张page_mv_tmp表定义里面ware_goods_id字段是string类型，

CREATE EXTERNAL TABLE test.page_mv_tmp (                                                  
  data_key STRING COMMENT '数据键md5',                                                                                                                                 
  ware_goods_id STRING COMMENT '入库商品',                                                                                                                          
)                                                                                                         
PARTITIONED BY (                                                                                          
  p_point_day STRING COMMENT '日期',                                                                      
  p_point_type INT COMMENT '埋点类型'                                                                     
)                                                                                                         
STORED AS PARQUET                                                                                         
LOCATION 'hdfs://nameservice1/user/hive/warehouse/test/page_mv';
--这里可以选择指定原来的hdfs路径，也可以选择重新指定一个新的hdfs路径。因为这张是外部表只是对hdfs路径进行映射而已。

这方面如果想了解可以参考外部表和内部表区别
hive1.1.0建立外部表关联HDFS文件

然后执行

insert overwrite table test.page_mv_tmp partition(p_point_day,p_point_type)  select * from page_mv

报错说字段类型不匹配！

 

Expression 'page_mv.ware_goods_id' (type: INT) is not compatible with column 'ware_goods_id' (type: STRING)

这下看来直接alter parquet表是不可能成功的

二、解决过程

在insert overwrite进刚才新建的page_mv_tmp时对这个ware_goods_id字段进行强转,这里用到的是impala的动态分区原理

insert overwrite table test.page_mv_tmp partition(p_point_day,p_point_type)
select data_key,CAST(ware_goods_id AS string),p_point_day,p_point_type from test.page_mv;

通过这样处理把原来page_mv的数据重新导入到page_mv_tmp表中了，而且是动态分区导入到指定日期分区里面，相当于数据自己对号入座了。

全部导入完成后对page_mv_tmp修改表名即可完成对ware_goods_id字段的类型转换(从int转到string类型)

ALTER TABLE test.page_mv_tmp RENAME TO test.page_mv;

总结：其实上述整个过程并不是真正的对parquet进行修改字段类型，只是新建了一张修改好字段类型的新表，然后把原来表的数据以动态分区的方式导入到新表中，再重命名为原来的表名完成的。因为parquet是parquert在存储上，做了加密，二级制存储压缩，不可以修改的，包括去hive中执行也是不行的！

 

修改字段名称：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

 

修改字段类型：

ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型 新类型长度 新默认值 新注释;  其中column可省略

 

删除字段

alter table desktop drop column software_category_id