HiveSQL_添加数据insert 二-【官方】百战程序员_IT在线教育培训机构


xxxxxxxxxx
insert overwrite table tablename1 [partition (partcol1=val1, partcol2=val2 ...) [if not exists]] select_statement1 from from_statement;
insert into table tablename1 [partition (partcol1=val1, partcol2=val2 ...)] select_statement1 from from_statement;

insert overwrite:表示覆盖的方式

insert into:表示追加的方式。

实操演示：

首先创建一张表person2：


xxxxxxxxxx
create table person2(
id int comment "唯一标识id",
name string comment "名称",
likes array<string> comment "爱好",
address map<string,string> comment "地址"
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":"
lines terminated by "\n";

将person表中的数据添加到person2中


xxxxxxxxxx
hive> insert into table person2  select * from person;
Query ID = root_20211111150434_2efb7477-d01a-4d04-a41b-408e06454a67
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
......
Stage-Stage-1: Map: 1   Cumulative CPU: 3.2 sec   HDFS Read: 6448 HDFS Write: 568 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 200 msec
OK
Time taken: 40.463 seconds
hive> select * from person2;
OK
1   小明1 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
2   小明2 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
3   小明3 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
4   小明4 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
5   小明5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6   小明6 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
7   小明7 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
8   小明8 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
9   小明9 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
Time taken: 0.601 seconds, Fetched: 9 row(s)
hive> insert into table person2  select * from person;
Query ID = root_20211111150539_7e3d0282-1a93-4d73-a31a-5c45c3bbf518
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
......
SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 570 msec
OK
Time taken: 34.366 seconds
hive> select * from person2;
OK
1   小明1 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
2   小明2 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
3   小明3 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
4   小明4 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
5   小明5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6   小明6 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
7   小明7 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
8   小明8 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
9   小明9 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
1   小明1 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
2   小明2 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
3   小明3 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
4   小明4 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
5   小明5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6   小明6 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
7   小明7 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
8   小明8 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
9   小明9 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}

insert overwrite:清空之前数据，新入新的数据


xxxxxxxxxx
hive> insert overwrite table person2  select * from person;
Query ID = root_20211111150904_3ccbd244-dd24-4a51-9b40-eef5fe85ede9
Total jobs = 3
Launching Job 1 out of 3
......
OK
Time taken: 29.986 seconds
hive> select * from person2;
OK
1   小明1 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
2   小明2 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
3   小明3 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
4   小明4 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
5   小明5 ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6   小明6 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
7   小明7 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
8   小明8 ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
9   小明9 ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
Time taken: 0.289 seconds, Fetched: 9 row(s)

适用场景：将一个复杂的表tableA(假如有100个列)，向将该表中的一些列的数据添加tableB中，另外一些列的数据放到tableC中。


xxxxxxxxxx
create table person2_1(
id int comment "唯一标识id",
name string comment "名称"
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":"
lines terminated by "\n";

create table person2_2(
id int comment "唯一标识id",
likes array<string> comment "爱好",
address map<string,string> comment "地址"
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":"
lines terminated by "\n";

将这两张表也添加hive中：


xxxxxxxxxx
hive> show tables;
OK
person
person2
person2_1
person2_2

将表person2中的数据分别放入到person2_1和person2_2中：

解决办法一：


xxxxxxxxxx
insert into table person2_1  select id,name from person2;
insert into table person2_2  select id,likes,address from person2;

如上解决版本会执行两次查询，效率较低。

解决办法二：

语法格式：


xxxxxxxxxx
from from_statement
insert overwrite table tablename1 [partition (partcol1=val1, partcol2=val2 ...) [if not exists]] select_statement1
[insert overwrite table tablename2 [partition ... [if not exists]] select_statement2]

sql语句：


xxxxxxxxxx
from person2
insert into table person2_1 select id,name
insert into table person2_2 select id,likes,address;

操作演示：


xxxxxxxxxx
hive> from person2
    > insert into table person2_1 select id,name
    > insert into table person2_2 select id,likes,address;
Query ID = root_20211111152050_79d388d9-3af3-47d3-88ab-57a2c67e5921
Total jobs = 3
Launching Job 1 out of 3
......
Time taken: 60.344 seconds
hive> select * from person2_1;
OK
1   小明1
2   小明2
3   小明3
4   小明4
5   小明5
6   小明6
7   小明7
8   小明8
9   小明9
Time taken: 0.304 seconds, Fetched: 9 row(s)
hive> select * from person2_2;
OK
1   ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
2   ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
3   ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
4   ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
5   ["lol","movie"] {"beijing":"xisanqi","shanghai":"pudong"}
6   ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}
7   ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
8   ["lol","book"]  {"beijing":"xisanqi","shanghai":"pudong"}
9   ["lol","book","movie"]  {"beijing":"xisanqi","shanghai":"pudong"}


			
						
			
				  HiveSQL_添加数据insert 一 HiveSQL_默认分隔符  			
			
				
	北京市昌平区回龙观镇南店村综合商业楼2楼226室
		©2014-2023 百战卓越（北京）科技有限公司 All Rights Reserved. 
		京ICP备14032124号-2