Hive分区表修改字段
Change Column Name/Type/Position/Comment
1ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
2 [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
- ALTER TABLE CHANGE语句可以修改表的字段名称，字段类型，字段顺序，字段注释或者这些组合。且默认情况下只修改表的元数据信息，即RESTRICT模式。
- 修改分区表时加上CASCADE关键字，可以修改所有分区的元数据信息。
示例：
10: jdbc:hive2://cdh-slave2:10000> desc alter_column;
2+--------------------------+-----------------------+-----------------------+--+
3| col_name | data_type | comment |
4+--------------------------+-----------------------+-----------------------+--+
5| id | varchar(3) | |
6| num | varchar(3) | |
7| data_dt | date | |
8| | NULL | NULL |
9| # Partition Information | NULL | NULL |
10| # col_name | data_type | comment |
11| | NULL | NULL |
12| data_dt | date | |
13+--------------------------+-----------------------+-----------------------+--+
140: jdbc:hive2://cdh-slave2:10000> show partitions alter_column;
15+---------------------+--+
16| partition |
17+---------------------+--+
18| data_dt=2022-03-21 |
19| data_dt=2022-03-22 |
20+---------------------+--+
21
220: jdbc:hive2://cdh-slave2:10000> desc alter_column;
23+-------+--------+-------------+--+
24| a.id | a.num | a.data_dt |
25+-------+--------+-------------+--+
26| 101 | 034 | 2022-03-21 |
27| 102 | 006 | 2022-03-21 |
28| 103 | 012 | 2022-03-22 |
29| 104 | 036 | 2022-03-22 |
30+-------+--------+-------------+--+
31
32## 修改字段类型(不加cascade)
33
340: jdbc:hive2://cdh-slave2:10000> alter table alter_column change num num decimal(3);
35
36## 查看表元数据信息
370: jdbc:hive2://cdh-slave2:10000> desc alter_column;
38+--------------------------+-----------------------+-----------------------+--+
39| col_name | data_type | comment |
40+--------------------------+-----------------------+-----------------------+--+
41| id | varchar(3) | |
42| num | decimal(3,0) | |
43| data_dt | date | |
44| | NULL | NULL |
45| # Partition Information | NULL | NULL |
46| # col_name | data_type | comment |
47| | NULL | NULL |
48| data_dt | date | |
49+--------------------------+-----------------------+-----------------------+--+
50
51## 查看分区元数据信息
520: jdbc:hive2://cdh-slave2:10000> desc alter_column partition(data_dt='2022-03-21');
53+--------------------------+-----------------------+-----------------------+--+
54| col_name | data_type | comment |
55+--------------------------+-----------------------+-----------------------+--+
56| id | varchar(3) | |
57| num | varchar(3) | |
58| data_dt | date | |
59| | NULL | NULL |
60| # Partition Information | NULL | NULL |
61| # col_name | data_type | comment |
62| | NULL | NULL |
63| data_dt | date | |
64+--------------------------+-----------------------+-----------------------+--+
65
66## 修改回varchar,加cascade修改
670: jdbc:hive2://cdh-slave2:10000> alter table alter_column change num num varchar(3);
680: jdbc:hive2://cdh-slave2:10000> alter table alter_column change num num decimal(3) cascade;
69
70## 查看分区元数据信息
710: jdbc:hive2://cdh-slave2:10000> desc alter_column partition(data_dt='2022-03-21');
72+--------------------------+-----------------------+-----------------------+--+
73| col_name | data_type | comment |
74+--------------------------+-----------------------+-----------------------+--+
75| id | varchar(3) | |
76| num | decimal(3,0) | |
77| data_dt | date | |
78| | NULL | NULL |
79| # Partition Information | NULL | NULL |
80| # col_name | data_type | comment |
81| | NULL | NULL |
82| data_dt | date | |
83+--------------------------+-----------------------+-----------------------+--+
84