hive的insert语句列顺序问题以及新增字段遇到的坑

讲问题之前，先简单创建一个表：

CREATE TABLE IF NOT EXISTS `my.test_table`(
  `col1` int COMMENT "第一列",
  `col2` int COMMENT "第二列"
)
COMMENT "测试表"
PARTITIONED BY (`pt` int COMMENT "测试分区")
ROW FORMAT SERDE
  "org.apache.hadoop.hive.ql.io.orc.OrcSerde"
STORED AS INPUTFORMAT
  "org.apache.hadoop.hive.ql.io.orc.OrcInputFormat"
OUTPUTFORMAT
  "org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat";

初始表有col1，col2两列，pt分区只是为了对比结果

insert语句列顺序

hive不像mysql、oracle这些数据库一样在insert的时候可以指定字段，必须在插入的时候插入的是全字段。所以我一直以为可以通过指定插入数据的别名来改变插入字段的顺序，好吧，事实证明我是错的！

我们来简单作一个设想，假如我们执行以下的sql会发生什么：

insert overwrite table my.test_table partition(pt=1) select 2 as col2, 1 as col1 from my.online_table;

按常规逻辑来说，查询的时候应该是col1字段都为1，col2字段都为2。但是事实上执行

select col1, col2 from my.test_table;

结果是:

2    1
2    1
2    1
2    1
....

事实上，hive并不关心你执行insert语句所用的别名，别名代表的字段可以不存在，甚至比别名都可以相同。下面的语句执行也是一样的效果：

insert overwrite table my.test_table partition(pt=1) select 2 as invalid_col, 1 as invalid_col from my.online_table;

是不是觉得很惊喜。所以，请严格保证insert语句中的字段和建表语句中的字段的顺序一致！！！

对新增字段插入数据再查询发现是NULL

hive比较特殊的地方，在于它的表结构和数据其实是分开的。这个会导致，对hive分区表新增字段后，在执行插入分区的动作，会发现其实数据文件中已经有新字段值了，但是在查询的时候新字段的值还是显示为null。

例如我执行了下面的方法新增了一列col3：

alter table my.test_table add columns(col3 int comment '第三列')

然后想插入一些数据：

insert overwrite table my.test_table partition(pt=1) select 1 as col1, 2 as col2, 3 as col3 from my.online_table; 

结果查询col1,col2,col3发现结果其实是：

1    2    NULL
1    2    NULL
...1

这是因为你对表结构进行了改变，但是历史分区的数据却没有做改变(新增分区不会出现这个情况)。

为了解决上面的问题，可以采用两种方式：

• 如果已经执行添加操作，并且没有带cascade，可以尝试下面的方法：

使用replace 恢复表结构，这样历史的分区数据都不会消失
alter table industry_db.product replace
columns(product_name string comment ‘产品名’);

• 在新增的时候加上cascade关键词

alter table my.test_table add columns(col3 int comment '第三列') cascade

官方文档描述如下：

The CASCADE|RESTRICT clause is available in Hive 1.1.0. ALTER TABLE ADD|REPLACE COLUMNS 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 changes only to table metadata.

如上所述，在1.1.0中表和分区的元数据就是分开处理的，在增加字段的时候添加CASCADE能同时更新表和分区 对于，在添加字段的时候没有指定的cascade的情况

因为我们在重跑数据的时候，虽然HDFS上的数据更新了，但是我们查询的时候仍然查询的是旧的元数据信息（即Mysql中的信息）

注意：对于执行了add column语句之后新生成的分区，是不会有问题的，Hive会自动维护新分区中的元数据。

转载请注明出处： hive的insert语句列顺序问题以及新增字段遇到的坑 - Boblim - 博客园