hive 指定字段插入数据_Hive通过查询语句向表中插入数据注意事项

最新推荐文章于 2025-01-03 18:11:10 发布

原创最新推荐文章于 2025-01-03 18:11:10 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive 指定字段插入数据

在使用Hive 0.13.1版本时，遇到通过查询语句向表中插入数据的一些问题，包括INSERT OVERWRITE的歧义、FROM ... INSERT ... SELECT的歧义和存在的bug。例如，INSERT INTO可能导致追加而非覆盖数据，而INSERT OVERWRITE在某些情况下可能不按预期工作。解决这些问题需要理解Hive的读时模式和宽松语法特性，以及注意在插入时明确指定字段。

最近在学习使用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所示。

图1给employees加载数据

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

图2employees中准备好的数据

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所示。

图3SemanticException [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所示。

图4正确执行insert overwrite

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

图5staged_employees中的数据

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

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

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

load data local inpath '${env:HOME}/test3.txt'

into table employees

partition (country = 'CA', state = 'ML');

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

图6加载新的数据

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

图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所示。

图8SemanticException [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所示。

图9

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

图10staged_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所示。

图11

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

图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所示。

图13

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

图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所示。

图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所示。

图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

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

前言近期在学习使用Hive(版本号0.13.1)的过程中,发现了一些坑,它们也许是Hive提倡的比关系数据库更加自由的体现(同一时候引来一些问题).也许是一些bug.总而言之,这些都须要使用Hive ...

6-02使用SQL语句向表中插入数据

插入语句的语法: INSERT INTO 表() VALUES(值列表) 注意事项: 1:每次插入一行数据,不能只插入半行或几列数据. 2:每一个数据值的数据类型.精度和小数位数必须与相应的列匹配. ...

oralce使用INSERT语句向表中插入数据

INSERT INTO table[ (column [, column. . .])] VALUES (value [,value . . .]); v 插入的数据 ...

SQL语句在一个表中插入新字段

SQL语句在一个表中插入新字段: alter table 表名 add 字段名字段类型例: alter table OpenCourses add Audio varchar(50)alter ...

第18课-数据库开发及ado&period;net 连接数据库&period;增&period;删&period;改向表中插入数据并且返回自动编号&period;SQLDataReade读取数据

第18课-数据库开发及ado.net 连接数据库.增.删.改向表中插入数据并且返回自动编号.SQLDataReade读取数据 ADO.NET 为什么要学习? 我们要搭建一个平台(Web/Winform ...

初学者使用MySQL&lowbar;Workbench 6&period;0CE创建数据库和表，以及在表中插入数据。

EF Core中，通过实体类向SQL Server数据库表中插入数据后，实体对象是如何得到数据库表中的默认值的

我们使用EF Core的实体类向SQL Server数据库表中插入数据后,如果数据库表中有自增列或默认值列,那么EF Core的实体对象也会返回插入到数据库表中的默认值. 下面我们通过例子来展示,EF ...

向mysql数据表中插入数据失败的原因

1.案例代码: $sql1="insert into content(category,subject,content,username,release_date) values('{$ca ...

触发器修改后保存之前的数据表中插入数据时ID自动增长

create or replace trigger t before update on test5 for each rowbegin insert into test55 values (:old ...

随机推荐

div+css中clear用法

一开始用clear属性,只是跟float相对使用,今天看视频的时候还是不大明白,查了下资料原来是这样的哦,看咯. clear属性值有四个clear:both|left|right|none; 作用:该 ...

以libfuse为例介绍rpm打包工具rpmbuild的使用和SPEC文件的编写

一.rpmbuild命令的安装 yum install rpm-build 二.用法 rpmbuild -bb XXXX.spec或者rpmbuild -ba XXX.tar.gz 三.目录概述 rp ...

从浅到深掌握Oracle的锁

1.分别模拟insert,update和delete造成阻塞的示例,并对v$lock中的相应的信息进行说明,给出SQL演示. Insert示例会话:SQL> select * from ...

最小化安装CentOS7 + xfce4 +PHP + nginx +mariadb 开发环境

虚拟机自定义最小化安装,新增用户做为管理员,打开自动获取网络,桥接模式.所有的操作只有命令,不做解释,看不明白的可以自行搜索相关的资料. # 开头的行是注释行,# 开头的空行,我自己装机时做了快照.未 ...

bzoj 1097 [POI2007]旅游景点atr(最短路，状压DP)

[题意] 给定一个n点m边的无向图,要求1开始n结束而且顺序经过k个点,给出经过关系x,y代表y必须在x之后经过,求最短路. [思路] 先对k个点进行spfa求出最短路. 设f[s][i]代表经过点集 ...

android 进程(复习)

前台进程前台进程是用户当前正在使用的进程.只有一些前台进程可以在任何时候都存在.他们是最后一个被结束的,当内存低到根本连他们都不能运行的时候.一般来说, 在这种情况下,设备会进行内存调度,中 ...

day16

列表生成式语法[表达式 for in 遍历 if 条件]会从li一次去除所有值,进行判断如果满足条件就装到新的列表里生成数据的函数函数体中又yield关键字yield 暂停函数的执行还能返回一 ...

如何在mpvue下收集小程序的formId

什么是formId formId是小程序可以向用户发送模板消息的通行证,简单而言,你只有获取到formId,把它交给后台,后台同学才能向用户发送通知消息,而这个通行证的有效期只有七天.这是微信为了防止 ...

CentOS7系统搭建外网环境

理一下思路第一步 Vultr 注册充值10刀了: 可以支付宝支付.不再需要绑定银行卡第二步选择一个自己中意的款系统啊流量之类的购买 Deploy New Instance 第三步 ...

Linux实验楼学习之三

查看用户 who am i or who mom likes who 命令其它常用参数参数说明 -a 打印能打印的全部 -d 打印死掉的进程 -m 同am i,mom likes -q 打印当前登 ...