Hive：insert into table 与 insert overwrite table 区别

最新推荐文章于 2025-06-25 11:41:52 发布

转载最新推荐文章于 2025-06-25 11:41:52 发布 · 423 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/yy3b2007com/p/7054128.html

文章标签：

#大数据

本文通过具体的SQL语句示例，展示了如何在Hive中创建测试表并使用INSERT和INSERT OVERWRITE进行数据操作。首先创建了一个测试表并插入了多条记录，然后使用INSERT OVERWRITE覆盖原有数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建测试表，来测试看看测试结果：

create table test(name string,pwd string,createdate string)row format delimited fields terminated by ',';

第一步：使用insert into 插入数据到表中：

insert into test(name,pwd,createdate)values('name1','pwd1','2017-06-20 14:14:09');
insert into test(name,pwd,createdate)values('name1','pwd1','2017-06-20 14:14:09');
insert into test(name,pwd,createdate)values('name2','pwd2','2017-06-20 14:14:09');
insert into test(name,pwd,createdate)values('name2','pwd2','2017-06-20 14:14:09');
0: jdbc:hive2://10.78.152.52:21066/> select * from test;
+------------+-----------+----------------------+--+
| test.name  | test.pwd  |   test.createdate    |
+------------+-----------+----------------------+--+
| name1      | pwd1      | 2017-06-20 14:14:09  |
| name1      | pwd1      | 2017-06-20 14:14:09  |
| name2      | pwd2      | 2017-06-20 14:14:09  |
| name2      | pwd2      | 2017-06-20 14:14:09  |
+------------+-----------+----------------------+--+

第二步：不清理以上插入的记录，直接执行insert overwirte，并查询分析结果：

insert overwrite table test select 'name1' as name,'pwd1' as pwd,'2017-06-20 14:14:09' as createdate;
insert overwrite table test select 'name2' as name,'pwd2' as pwd,'2017-06-20 14:14:09' as createdate;
insert overwrite table test select 'name2' as name,'pwd2' as pwd,'2017-06-20 14:14:09' as createdate;
insert overwrite table test select 'name3' as name,'pwd3' as pwd,'2017-06-20 14:14:09' as createdate;
0: jdbc:hive2://10.78.152.62:21066/> select * from test;
+------------+-----------+----------------------+--+
| test.name  | test.pwd  |   test.createdate    |
+------------+-----------+----------------------+--+
| name3      | pwd3      | 2017-06-20 14:14:09  |
+------------+-----------+----------------------+--+

第三步：不清理上边步骤执行后的结果，接着执行以下sql语句，并查询结果：

insert overwrite table test select 'name2' as name,'pwd2' as pwd,'2017-06-20 14:14:09' as createdate;
0: jdbc:hive2://10.78.152.62:21066/> select * from test;
+------------+-----------+----------------------+--+
| test.name  | test.pwd  |   test.createdate    |
+------------+-----------+----------------------+--+
| name2      | pwd2      | 2017-06-20 14:14:09  |
+------------+-----------+----------------------+--+