Hive通过查询语句向表中插入数据注意事项

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

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. create table employees (  
  2.   id int comment 'id',  
  3.   name string comment 'name')  
  4. partitioned by (country string, state string)  
  5. row format delimited fields terminated by ',';  

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. create table staged_employees (  
  2.   id int comment 'id',  
  3.   user_name string comment 'user name')  
  4. partitioned by (cnty string, st string);  

 

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

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. load data local inpath '${env:HOME}/test.txt'  
  2. into table employees  
  3. partition (country = 'US', state = 'CA');  

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. load data local inpath '${env:HOME}/test2.txt'  
  2. overwrite into table employees  
  3. partition (country = 'CN', state = 'BJ');  

以上语句的执行过程如图1所示。


 

图1 给employees加载数据

 

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

 


 

图2 employees中准备好的数据

 

INSERT OVERWRITE的歧义

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. insert overwrite table staged_employees  
  2. partition (cnty = 'US', st = 'CA')  
  3. select * from employees e  
  4. where e.country = 'US' and e.state = 'CA';  

大家看看这条sql有没有问题？最终的执行结果如图3所示。

 

图3 SemanticException [Error 10044]

由于图3中的文字太小，这里把这些错误提示信息列在下边：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. 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进行了调整，调整后的清单如下：

 

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. insert overwrite table staged_employees  
  2. partition (cnty = 'US', st = 'CA')  
  3. select e.id, e.name from employees e  
  4. where e.country = 'US' and e.state = 'CA';  

执行这条sql的过程如图4所示。



 

图4 正确执行insert overwrite

 

我们看看staged_employees表中，现在有哪些数据（如图5所示）：

 


 

图5 staged_employees中的数据

 

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

 

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

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. load data local inpath '${env:HOME}/test3.txt'  
  2. into table employees  
  3. partition (country = 'CA', state = 'ML');  

执行上面sql的过程如图6所示。


 

图6 加载新的数据

 

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

 


 

图7

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. from employees e  
  2. insert into table staged_employees  
  3. partition (cnty = 'CA', st = 'ML')  
  4. select * where e.country = 'CA' and e.state = 'ML';  

执行这条sql的过程如图8所示。


 

图8 SemanticException [Error 10044]

 

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

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. from employees e  
  2. insert into table staged_employees  
  3. partition (cnty = 'CA', st = 'ML')  
  4. select e.id, e.name where e.country = 'CA' and e.state = 'ML';  

现在执行这条sql，发现可以成功执行，如图9所示。


 

图9

 

现在来看看staged_employees中的数据（如图10所示），看来的确将分区数据插入了。

 


 

图10 staged_employees中的数据

 

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

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

 

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. from employees e  
  2. insert into table staged_employees  
  3. partition (cnty = 'US', st = 'CA')  
  4. select e.id, e.name where e.country = 'US' and e.state = 'CA';  

这条sql很明显是向staged_employees中再次插入country等于US，state等于CA分区的数据，根据INSERT INTO的通常含义，应当是向表中追加，我们执行这段sql来验证一下，如图11所示。


 

图11

 

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

 


 

图12

 

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

 

我们将sql进行调整，即将INSERT INTO改为INSERT OVERWRITE：

 

 

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. from employees e  
  2. insert overwrite table staged_employees  
  3. partition (cnty = 'US', st = 'CA')  
  4. select e.id, e.name where e.country = 'US' and e.state = 'CA';  

执行这条sql的过程如图13所示。


 

图13

 

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

 


 

图14

 

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

 

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

 

 

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. from employees e  
  2. insert overwrite table staged_employees  
  3. partition (cnty = 'US', st = 'CA')  
  4. select e.id, e.name where e.country = 'US' and e.state = 'CA'  
  5. insert overwrite table staged_employees  
  6. partition (cnty = 'CA', st = 'ML')  
  7. select e.id, e.name where e.country = 'CA' and e.state = 'ML';  

 

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

 


 

图15

 

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

 

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

 

 

 

[sql] view plain copy 在CODE上查看代码片派生到我的代码片
  1. from employees e  
  2. insert overwrite table staged_employees  
  3. partition (cnty = 'US', st = 'CA')  
  4. select e.id, e.name where e.country = 'US' and e.state = 'CA'  
  5. insert into table staged_employees  
  6. partition (cnty = 'CN', st = 'BJ')  
  7. select e.id, e.name where e.country = 'CN' and e.state = 'BJ';  

 

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

 


 

图16

 

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

 


 

图17

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

转自：http://blog.csdn.net/beliefer/article/details/51860510

posted @ 2016-11-23 21:11  记忆残留  阅读(24298)  评论(0)  编辑  收藏  举报