hive增加，删除，修改字段注意事项

目录

 

1 无分区表

1.1准备无分区表和数据

1.2 新增字段

1.3 删除字段

1.4 修改字段

2 有分区表

2.1 准备分区表和数据

2.2 新增字段

2.3 已存在的分区新增字段无法插入数据

2.4 使用CASCADE覆盖表分区的列元数据

---

1 无分区表

1.1准备无分区表和数据

创建表无分区表

CREATE TABLE IF NOT EXISTS test_hive_tb1 (id BIGINT, name STRING) ;

插入2条数据

INSERT INTO TABLE test_hive_tb1 VALUES(100001,"lisi");
INSERT INTO TABLE test_hive_tb1 VALUES(100002,"wangwu");

查看

hive> select * from test_hive_tb1;
OK
100001	lisi
100002	wangwu

1.2 新增字段

ALTER TABLE test_hive_tb1 ADD COLUMNS(age Int);

插入第三条数据

INSERT INTO TABLE test_hive_tb1 VALUES(100003,"zhangsan",20);

查看

hive> select * from test_hive_tb1;
OK
100001	lisi	NULL
100002	wangwu	NULL
100003	zhangsan	20

可见字段新增前的数据在字段新增后默认新增的字段是NULL，新增后插入的数据正常插入

后面执行insert overwrite

把age is NULL的人的age赋值成默认年龄18岁

insert overwrite table test_hive_tb1 select Id ,name ,(case when age is NULL then 18 else age end) age from test_hive_tb1;

查看结果

hive> select * from test_hive_tb1;
OK
100003	zhangsan	20
100002	wangwu	18
100001	lisi	18

可见所有数据都可以更新，尤其是10001,10002这两条NULL已经更新成18（这一点注意和后面对比）

埋个伏笔：

执行一下如下命令：

ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) cascade;

可见多了 cascade 

然后报错如下：

hive (liucf)> ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) cascade;
FAILED: NullPointerException null
20/08/15 11:34:38 ERROR ql.Driver: FAILED: NullPointerException null
java.lang.NullPointerException
	at org.apache.hadoop.hive.metastore.Warehouse.makePartName(Warehouse.java:548)
	at org.apache.hadoop.hive.metastore.Warehouse.makePartName(Warehouse.java:490)
	at org.apache.hadoop.hive.ql.metadata.Partition.getName(Partition.java:199)
	at org.apache.hadoop.hive.ql.hooks.Entity.doComputeName(Entity.java:358)
	at org.apache.hadoop.hive.ql.hooks.Entity.computeName(Entity.java:348)
	at org.apache.hadoop.hive.ql.hooks.Entity.<init>(Entity.java:221)
	at org.apache.hadoop.hive.ql.hooks.WriteEntity.<init>(WriteEntity.java:105)
	at org.apache.hadoop.hive.ql.parse.DDLSemanticAnalyzer.addInputsOutputsAlterTable(DDLSemanticAnalyzer.java:1520)
	at org.apache.hadoop.hive.ql.parse.DDLSemanticAnalyzer.addInputsOutputsAlterTable(DDLSemanticAnalyzer.java:1497)
	at org.apache.hadoop.hive.ql.parse.DDLSemanticAnalyzer.analyzeAlterTableModifyCols(DDLSemanticAnalyzer.java:2742)
	at org.apache.hadoop.hive.ql.parse.DDLSemanticAnalyzer.analyzeInternal(DDLSemanticAnalyzer.java:287)
	at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:250)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:603)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1425)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1493)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1339)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1328)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:239)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:187)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:409)
	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:836)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:772)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:699)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:313)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:227)

可见 加了 cascade 后会去执行org.apache.hadoop.hive.ql.metadata.Partition.getName(Partition.java:199) 去找分区，因为我这个是无分区的所以报空指针异常

执行下面这个就没问题

ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) RESTRICT;

hive (liucf)> ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) RESTRICT;
OK
Time taken: 0.044 seconds
hive (liucf)> desc test_hive_tb1;
OK
col_name	data_type	comment
id                  	bigint              	                    
stu_age_string      	string              	                    
name                	string              	                    
sex                 	int                 	                    
Time taken: 0.026 seconds, Fetched: 4 row(s)
hive (liucf)> select * from test_hive_tb1;
OK
id	stu_age_string	name	sex
100003	20	zhangsan	NULL
100002	18	wangwu	NULL
100001	18	lisi	NULL

 因为：

The CASCADE|RESTRICT clause is available in Hive 1.1.0. ALTER TABLE CHANGE COLUMN with CASCADE command changes the columns of a table’s metadata, and cascades the same change to all the partition metadata. RESTRICT is the default, limiting column change only to table metadata.

ALTER TABLE CHANGE COLUMN CASCADE clause will override the table partition’s column metadata regardless of the table or partition’s protection mode. Use with discretion.

The column change command will only modify Hive’s metadata, and will not modify data. Users should make sure the actual data layout of the table/partition conforms with the metadata definition.

翻译一下就是：

级联|限制子句在Hive 1.1.0中可用。使用CASCADE命令更改表元数据的列，并将相同的更改级联到所有分区元数据。RESTRICT 是默认值，仅将列更改限制为表元数据。

无论表或分区的保护模式如何，ALTER TABLE CHANGE COLUMN CASCADE子句都将覆盖表分区的列元数据。使用自由裁量权。

column change命令只会修改Hive的元数据，不会修改数据。用户应该确保表/分区的实际数据布局符合元数据定义。

所以：

ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) cascade; 会去级联分区的元数据没有分区就报错空指针

RESTRICT 是默认值

ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) RESTRICT ;

等价于

ALTER TABLE test_hive_tb1 ADD COLUMNS(sex Int) ;

1.3 删除字段

下面删除age字段

注意hive没有像mysql里：ALTER TABLE `tableName` drop COLUMN `columeName`;

这样的语法，但是可以通过REPLACE实现

ALTER TABLE test_hive_tb1 REPLACE COLUMNS (id BIGINT, name STRING);

 

查看：

hive> desc test_hive_tb1;
OK
id                  	bigint              	                    
name                	string              	                    
Time taken: 0.055 seconds, Fetched: 2 row(s)
hive> select * from test_hive_tb1;
OK
100003	zhangsan
100002	wangwu
100001	lisi
Time taken: 0.056 seconds, Fetched: 3 row(s)
hive> 

可见字段age已经删除，查询结果也只有id，name,两个字段

那么实际底层数据有没有一并删除呢？

下面我们再把age加回来，按照上面的结论，如果再查的结果是age是NULL说明字段值也一并删除了，如果还能查出来删除前的值说明只是表结构删除了数据并没删除

ALTER TABLE test_hive_tb1 ADD COLUMNS(age Int);

hive> ALTER TABLE test_hive_tb1 ADD COLUMNS(age Int);
OK
Time taken: 0.146 seconds
hive> select * from test_hive_tb1;
OK
100003	zhangsan	20
100002	wangwu	18
100001	lisi	18
Time taken: 0.036 seconds, Fetched: 3 row(s)

可见REPLACE删除字段并不能删除字段的值只是改变了表结构而已

1.4 修改字段

① 下面把age字段修改成stu_age

ALTER TABLE test_hive_tb1 CHANGE age stu_age INT;

查看

hive> desc test_hive_tb1;
OK
id                  	bigint              	                    
name                	string              	                    
stu_age             	int                 	                    
Time taken: 0.031 seconds, Fetched: 3 row(s)
hive> select * from test_hive_tb1;
OK
100003	zhangsan	20
100002	wangwu	18
100001	lisi	18

② 把stu_age_string 移动到name前面

ALTER TABLE test_hive_tb1 CHANGE stu_age stu_age_string string AFTER id;

注意 AFTER 和修改字段一起使用不然会报错，我试过

查看

hive (liucf)> desc test_hive_tb1;
OK
col_name	data_type	comment
id                  	bigint              	                    
stu_age_string      	string              	                    
name                	string              	                    
Time taken: 0.028 seconds, Fetched: 3 row(s)
hive (liucf)> select * from test_hive_tb1;
OK
id	stu_age_string	name
100003	zhangsan	20
100002	wangwu	18
100001	lisi	18

可见字段是变了位置但是值并没有改变，stu_age_string 显示的是原来的名字   name显示的是原来的年龄

注意:列位置更换后数据位置还是不动的,若想让数据跟着字段一起移动,需更新表数据,
使用insert overwrite table 从表中将移动之前对应的数据插入到移动之后对应的字段中

比如：把name,stu_age_string的位置和desc test_hive_tb1 表后的字段位置调换一下就可以了

insert overwrite table test_hive_tb1 select Id ,name,stu_age_string from test_hive_tb1;

hive (liucf)> select * from test_hive_tb1;
OK
id	stu_age_string	name
100003	20	zhangsan
100002	18	wangwu
100001	18	lisi

可见数据关系对应 上了

2 有分区表

2.1 准备分区表和数据

创建分区表

CREATE TABLE IF NOT EXISTS test_hive_tb2 (id BIGINT, name STRING) partitioned by(dt string);

插入2条测试数据 

INSERT INTO TABLE test_hive_tb2 partition(dt=20200815) VALUES(200001,"lisi");
INSERT INTO TABLE test_hive_tb2 partition(dt=20200816) VALUES(200002,"wangwu");

 

hive (liucf)> select * from test_hive_tb2;
OK
id	name	dt
200001	lisi	20200815
200002	wangwu	20200816

2.2 新增字段

ALTER TABLE test_hive_tb2 ADD COLUMNS(age Int);

2.3 已存在的分区新增字段无法插入数据

再插入2条测试数据

INSERT INTO TABLE test_hive_tb2 partition(dt=20200816) VALUES(200003,"zhangsan",20);
INSERT INTO TABLE test_hive_tb2 partition(dt=20200817) VALUES(200004,"maliu",22);

hive (liucf)> desc test_hive_tb2;
OK
col_name	data_type	comment
id                  	bigint              	                    
name                	string              	                    
age                 	int                 	                    
dt                  	string              	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
dt                  	string              	                    
Time taken: 0.052 seconds, Fetched: 9 row(s)
hive (liucf)> select * from test_hive_tb2;
OK
id	name	age	dt
200001	lisi	NULL	20200815
200002	wangwu	NULL	20200816
200003	zhangsan	NULL	20200816
200004	maliu	22	20200817

可见 ，新增字段位置默认在partition字段前面，

已经存在的分区，无论是新增的数据比如200003还是之前已经有的数据200001，200002 新增字段都是NULL

新增加字段后建立的分区可以正常插入数据

使用insert overwrite试试插入数据

因为有多个分区这里使用动态分区插入数据所以要先临时窗口设置如下

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000000;
set hive.exec.max.dynamic.partitions.pernode=100000;
set hive.exec.max.created.files=100000;
set hive.optimize.sort.dynamic.partition=true;
set mapreduce.job.name=LiucfTest

执行insert overwrite

insert overwrite table test_hive_tb2 partition (dt)  select Id ,name ,(case when age is NULL then 18 else age end) age,dt from test_hive_tb2 ;

执行后查看结果：

hive (liucf)> select * from test_hive_tb2;
OK
id	name	age	dt
200001	lisi	NULL	20200815
200003	zhangsan	NULL	20200816
200002	wangwu	NULL	20200816
200004	maliu	22	20200817

可见分区 dt=20200815,20200816两个分区 新增的age 即使使用insert overwrite也无法插入数据

这说明，单纯的执行 ALTER TABLE test_hive_tb2 ADD COLUMNS(age Int); 只是改变了表结构，并不能级联到已经存在的表的分区的元数据。可见可通过新增字段前删除所有分区，然后再重新导入数据是可以让所有分区都能对新增字段插入数据。但是这样的做法不是很好，下面有更好的解决方法

2.4 使用CASCADE覆盖表分区的列元数据

为了验证效果，创建第三个分区表

CREATE TABLE IF NOT EXISTS test_hive_tb3 (id BIGINT, name STRING) partitioned by(dt string);

插入数据

INSERT INTO TABLE test_hive_tb3 partition(dt=20200815) VALUES(200001,"lisi");
INSERT INTO TABLE test_hive_tb3 partition(dt=20200816) VALUES(200002,"wangwu");

增加列age

ALTER TABLE test_hive_tb3 ADD COLUMNS(age Int) cascade;

可见并没有像无分区表那样报空指针异常FAILED: NullPointerException null

再插入数据

INSERT INTO TABLE test_hive_tb3 partition(dt=20200816) VALUES(200003,"zhangsan",20);
INSERT INTO TABLE test_hive_tb3 partition(dt=20200817) VALUES(200004,"maliu",22);

hive (liucf)> select * from test_hive_tb3;
OK
id	name	age	dt
200001	lisi	NULL	20200815
200002	wangwu	NULL	20200816
200003	zhangsan	20	20200816
200004	maliu	22	20200817
Time taken: 0.05 seconds, Fetched: 4 row(s)

可见新增字段age前的分区dt=20200816（id=20003）和新增字段后的新分区dt=20200817 (id=20004)的age字段都能正常插入数据了

下面试试insert overwrite更新整个表的数据

insert overwrite table test_hive_tb3 partition (dt)  select Id ,name ,(case when age is NULL then 18 else age end) age,dt from test_hive_tb3 ;

查看结果

hive (liucf)> select * from test_hive_tb3;
OK
id	name	age	dt
200001	lisi	18	20200815
200003	zhangsan	20	20200816
200002	wangwu	18	20200816
200004	maliu	22	20200817
Time taken: 0.046 seconds, Fetched: 4 row(s)

可见都可以正常插入了

另外：还有一种场景也可以使用CASCADE新增字段后不用修改原有表

我们使用flume收集数据到hdfs 按天分目录存储在hdfs ，然后创建外链表，每天每天对不同的目录创建外链形成分区表的数据，有字段 a,b,c,d四个字段，使用hive可以正常查询a,b,c,d四个字段，后面因为业务需要，增加一个字段e,然后就在flume写入hdfs 的文件的原有字段上增加了字段e的数据写入，原有表结构没有改变的情况下还只能查询 a,b,c,d四个字段的值。然对表使用ALTER TABLE mytable ADD COLUMNS(e Int) ; 后发现和上面测试的已经已经有的分区新增的字段即使hdfs已经写有数据也无法显示字段e的真实值，只会显示NULL，痛苦的是要删除外链表各个老分区后重新给每一天加外链才能显示字段e的值，这显然很麻烦。后面试用 ALTER TABLE mytable ADD COLUMNS(e Int) CASCADE 在增加字段的时候加上CASCADE可以改变所有分区的元数据，一劳永逸不用那么麻烦，直接的原有分区只要hdfs 上的数据写入的e字段的值就能正常查询显示hive外链表e字段的值了