Hive通过查询语句向表中插入数据过程中发现的坑

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/beliefer/article/details/51860510

前言

最近在学习使用Hive(版本0.13.1)的过程中,发现了一些坑,它们或许是Hive提倡的比关系数据库更加*的体现(同时引来一些问题),或许是一些bug。总而言之,这些都需要使用Hive的开发人员额外注意。本文旨在列举我发现的3个通过查询语句向表中插入数据过程中的问题,希望大家注意。

数据准备

为了验证接下来出现的问题,需要先准备两张表employees和staged_employees,并准备好测试数据。首先使用以下语句创建表employees:

  create table employees (
    id int comment 'id',
    name string comment 'name')
  partitioned by (country string, state string)
  row format delimited fields terminated by ',';

employees的结构比较简单,有id、name、country、state四个字段,其中country和state都是分区字段。特别需要提醒的是这里显示的给行格式指定了字段分隔符为逗号,因为默认的字段分隔符\001不便于笔者准备数据。然后创建表staged_employees:

  create table staged_employees (
    id int comment 'id',
    user_name string comment 'user name')
  partitioned by (cnty string, st string);

staged_employees也有4个字段,除了字段名不同之外,和employees的4个字段的含义是相同的。

我们首先使用以下语句给employees的country等于US,state等于CA的分区加载一些数据:

  load data local inpath '${env:HOME}/test.txt'
  into table employees
  partition (country = 'US', state = 'CA');

再给employees的country等于CN,state等于BJ的分区加载一些数据:

  load data local inpath '${env:HOME}/test2.txt'
  overwrite into table employees
  partition (country = 'CN', state = 'BJ');
以上语句的执行过程如图1所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图1 employees加载数据

最后我们看看employees中准备好的数据,如图2所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图2 employees中准备好的数据

INSERT OVERWRITE的歧义

由于staged_employees中还没有数据,所以我们查询employees的数据,并插入staged_employees中:

insert overwrite table staged_employees
partition (cnty = 'US', st = 'CA')
select * from employees e
where e.country = 'US' and e.state = 'CA';
大家看看这条sql有没有问题?最终的执行结果如图3所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图3 SemanticException [Error 10044]
由于图3中的文字太小,这里把这些错误提示信息列在下边:

FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different ''CA'': Table insclause-0 has 2 columns, but query has 4 columns.
我们的sql应该没有问题吧?仔细查看提示信息,说是“表只有2列,但是查询有4列”。刚才说过,我建的两张表除了字段名称的差异,其结构完全一样。两张表都有4个字段(2个普通字段和2个分区字段),为什么说staged_employees只有2列呢?这是因为Hive遵循读时模式且遵循相对宽松的语法,在插入或装载数据时,不会验证数据与表的模式是否匹配。只有在读取数据时才会验证。因此在向表staged_employees插入数据时不会验证,而查询读取employees表中的数据时会验证。我对sql进行了调整,调整后的清单如下:

insert overwrite table staged_employees
partition (cnty = 'US', st = 'CA')
select e.id, e.name from employees e
where e.country = 'US' and e.state = 'CA';
执行这条sql的过程如图4所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图4 正确执行insert overwrite

我们看看staged_employees表中,现在有哪些数据(如图5所示):

Hive通过查询语句向表中插入数据过程中发现的坑

图5 staged_employees中的数据

熟悉MySQL等关系型数据库的同学可能要格外注意此问题了!

FROM ... INSERT ... SELECT的歧义

本节正式开始之前,向employees表中再加载一些数据:

  load data local inpath '${env:HOME}/test3.txt'
  into table employees
  partition (country = 'CA', state = 'ML');
执行上面sql的过程如图6所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图6 加载新的数据

这时表employees的数据如图7所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图7

Hive提供了一种特别的INSERT语法,我们不妨先看看其使用方式,sql如下:

from employees e
insert into table staged_employees
partition (cnty = 'CA', st = 'ML')
select * where e.country = 'CA' and e.state = 'ML';
执行这条sql的过程如图8所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图8 SemanticException [Error 10044]

可以看到这里再次出现了之前提到的问题,我们依然按照之前的方式进行修改,sql如下:

from employees e
insert into table staged_employees
partition (cnty = 'CA', st = 'ML')
select e.id, e.name where e.country = 'CA' and e.state = 'ML';
现在执行这条sql,发现可以成功执行,如图9所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图9

现在来看看staged_employees中的数据(如图10所示),看来的确将分区数据插入了。

Hive通过查询语句向表中插入数据过程中发现的坑

图10 staged_employees中的数据

FROM ... INSERT ... SELECT存在bug

我们继续使用FROM ... INSERT ... SELECT语法向staged_employees中插入数据,sql如下:

from employees e
insert into table staged_employees
partition (cnty = 'US', st = 'CA')
select e.id, e.name where e.country = 'US' and e.state = 'CA';
这条sql很明显是向staged_employees中再次插入country等于US,state等于CA分区的数据,根据INSERT INTO的通常含义,应当是向表中追加,我们执行这段sql来验证一下,如图11所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图11

我们看看这时staged_employees中的数据,如图12所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图12

的确印证了,INSERT INTO是用于追加的。

我们将sql进行调整,即将INSERT INTO改为INSERT OVERWRITE:

from employees e
insert overwrite table staged_employees
partition (cnty = 'US', st = 'CA')
select e.id, e.name where e.country = 'US' and e.state = 'CA';
执行这条sql的过程如图13所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图13

我们看看这时staged_employees中的数据,如图14所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图14

这说明INSERT OVERWRITE是用于覆盖的。

根据官方文档说明,这种FROM ... INSERT ... SELECT语法中的INSERT ... SELECT是可以有多个的,于是我编写以下sql,用来向表staged_employees中覆盖“country等于CA,state等于ML”分区的数据,并且覆盖“country等于US,state等于CA”分区的数据。

from employees e
insert overwrite table staged_employees
partition (cnty = 'US', st = 'CA')
select e.id, e.name where e.country = 'US' and e.state = 'CA'
insert overwrite table staged_employees
partition (cnty = 'CA', st = 'ML')
select e.id, e.name where e.country = 'CA' and e.state = 'ML';

执行以上sql的过程如图15所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图15

由于都是覆盖更新,所以staged_employees中的数据并未发生改变。

根据官方文档,以上sql中还可以将INSERT OVERWRITE和INSERT INTO进行混用,sql如下:

from employees e
insert overwrite table staged_employees
partition (cnty = 'US', st = 'CA')
select e.id, e.name where e.country = 'US' and e.state = 'CA'
insert into table staged_employees
partition (cnty = 'CN', st = 'BJ')
select e.id, e.name where e.country = 'CN' and e.state = 'BJ';

这段sql将覆盖“country等于US,state等于CA”分区的数据,并且追加“country等于CN,state等于BJ”分区的数据。执行这段sql的过程如图16所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图16

最后,我们来看看staged_employees中的数据,如图17所示。

Hive通过查询语句向表中插入数据过程中发现的坑

图17

从图17中看到,“country等于CN,state等于BJ”分区的数据如我们所愿追加到表staged_employees中了。“country等于US,state等于CA”分区的数据并没有被覆盖,而是追加。这很明显是一个bug,希望大家注意!


后记:个人总结整理的《深入理解Spark:核心思想与源码分析》一书现在已经正式出版上市,目前京东、当当、天猫等网站均有销售,欢迎感兴趣的同学购买。


Hive通过查询语句向表中插入数据过程中发现的坑

京东:http://item.jd.com/11846120.html 

当当:http://product.dangdang.com/23838168.html 



上一篇:蓝桥杯JAVA-15.LCS最长公共子序列模板(JAVA实现)


下一篇:Hadoop Hive概念学习系列之hive的数据压缩(七)