INSERT
描述
将新行插入表中，并可选择截断表或分区。 通过值表达式或查询的结果指定插入的行。
语法
INSERT [ INTO | OVERWRITE ] [ TABLE ] table_identifier [ partition_spec ] [ ( column_list ) ]
{ VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
INSERT INTO [ TABLE ] table_identifier REPLACE WHERE boolean_expression query参数
table_identifier
标识要插入到的表
语法:
[ database_name. ] table_namepartition_spec todo: 待禁用
An optional parameter that specifies a comma-separated list of key and value pairs
for partitions. Note that one can use a typed literal (e.g., date'2019-01-02') in the partition spec.语法:
PARTITION ( partition_col_name = partition_col_val [ , ... ] )column_list
表中列的可选列表。 insert 命令最多可以指定表中的任何特定列一次。
注意事项:
- 所有指定的列都应存在于表中，且不得相互重复。
- 指定列表的数目应与 VALUES 子句或查询中的数据个数完全一致。
VALUES ( { value
|NULL } [ , ... ] ) [ , ( ... ) ]指定要插入的值。可以插入明确指定的值或 NULL。 每个值之间必须使用逗号分隔。可以指定多组值以插入多行数据。
boolean_expression
指定任意为 boolean 类型结果的表达式。可以使用逻辑运算符（AND、OR）将两个或多个表达式组合在一起。
query
一个生成要插入行的查询。该查询可以采用以下语句之一：
- SELECT 语句
- Inline Table 语句
FROM语句
示例
Insert Into
使用 VALUES 子句插入单行数据
CREATE TABLE person (name STRING, age INT, salary DOUBLE);
INSERT INTO person VALUES ('p1', 1, 1.0);
SELECT * FROM person;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 1| 1.0|
+---------+----------------------+----------+使用 VALUES 子句插入多行数据
INSERT INTO person VALUES
('p1', 1, 1.0),
('p2', 2, 2.0),
('p3', 3, 3.0)
SELECT * FROM person;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 1| 1.0|
+---------+----------------------+----------+
| p2| 2| 2.0|
+---------+----------------------+----------+
| p3| 3| 3.0|
+---------+----------------------+----------+使用 SELECT 语句插入数据
SELECT * FROM person;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 1| 1.0|
+---------+----------------------+----------+
| p2| 2| 2.0|
+---------+----------------------+----------+
| p3| 3| 3.0|
+---------+----------------------+----------+
insert into employees
select name, age, salary from person where name = "p1";
SELECT * FROM employees;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 1| 1.0|
+---------+----------------------+----------+使用 TABLE 语句插入数据
SELECT * FROM leaders;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p9| 8| 9.0|
+---------+----------------------+----------+
INSERT INTO employees TABLE leaders;
SELECT * FROM employees order by salary;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 1| 1.0|
+---------+----------------------+----------+
| p9| 8| 9.0|
+---------+----------------------+----------+使用 From 子句插入数据
SELECT * FROM leaders;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p9| 8| 9.0|
+---------+----------------------+----------+
INSERT INTO leaders
FROM employees SELECT name, age, salary WHERE name = "p2";
SELECT * FROM leaders order by salary;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p2| 2| 2.0|
+---------+----------------------+----------+
| p9| 8| 9.0|
+---------+----------------------+----------+todo：不支持 待删除 在某一个 partition 中插入数据
Insert Using a Typed Date Literal for a Partition Column Value
CREATE TABLE students (name STRING, address STRING) PARTITIONED BY (birthday DATE);
INSERT INTO students PARTITION (birthday = date'2019-01-02')
VALUES ('Amy Smith', '123 Park Ave, San Jose');
SELECT * FROM students;
+-------------+-------------------------+-----------+
| name| address| birthday|
+-------------+-------------------------+-----------+
| Amy Smith| 123 Park Ave, San Jose| 2019-01-02|
+-------------+-------------------------+-----------+Insert with a column list
INSERT INTO boss (name, age, salary) VALUES
('p0', 18, double('infinity'));
SELECT * FROM boss;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p0| 18| Infinity|
+---------+----------------------+----------+todo：不支持 待删除 在某一个 partition 中插入数据
Insert with both a partition spec and a column list
INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES
('Hangzhou, China', 'Kent Yao Jr.');
SELECT * FROM students WHERE student_id = 11215017;
+------------+----------------------+----------+
| name| address|student_id|
+------------+----------------------+----------+
|Kent Yao Jr.| Hangzhou, China| 11215017|
+------------+----------------------+----------+Insert Overwrite
使用 VALUES 子句插入数据
SELECT * FROM employees order by salary;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 1| 1.0|
+---------+----------------------+----------+
| p9| 8| 9.0|
+---------+----------------------+----------+
INSERT OVERWRITE employees VALUES
('p4', 3, 0.5),
('p8', 7, 7);
SELECT * FROM employees;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p4| 3| 0.5|
+---------+----------------------+----------+
| p8| 7| 7.0|
+---------+----------------------+----------+使用 SELECT 子句插入数据
SELECT * FROM employees;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p4| 3| 0.5|
+---------+----------------------+----------+
| p8| 7| 7.0|
+---------+----------------------+----------+
INSERT OVERWRITE leaders
SELECT name, age, salary FROM employees WHERE name = "p8";
SELECT * FROM leaders;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p8| 7| 7.0|
+---------+----------------------+----------+使用 replace where 语句插入数据
SELECT * FROM employees;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p4| 3| 0.5|
+---------+----------------------+----------+
| p8| 7| 7.0|
+---------+----------------------+----------+
SELECT * FROM leaders;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p8| 7| 7.0|
+---------+----------------------+----------+
INSERT INTO leaders REPLACE WHERE age = 7 SELECT * FROM employees
SELECT * FROM leaders order by salary;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p4| 3| 0.5|
+---------+----------------------+----------+
| p8| 7| 7.0|
+---------+----------------------+----------+使用 TABLE 子句插入数据
SELECT * FROM freshes;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p6| 6| 1.5|
+---------+----------------------+----------+
| p7| 8| 2.0|
+---------+----------------------+----------+
INSERT OVERWRITE employees TABLE freshes;
SELECT * FROM employees;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p6| 6| 1.5|
+---------+----------------------+----------+
| p7| 8| 2.0|
+---------+----------------------+----------+使用 From 子句插入数据
SELECT * FROM employees;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p6| 6| 1.5|
+---------+----------------------+----------+
| p7| 8| 2.0|
+---------+----------------------+----------+
INSERT OVERWRITE leaders
FROM employees SELECT name, age, salary WHERE age = 8;
SELECT * FROM leaders;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p7| 8| 2.0|
+---------+----------------------+----------+todo：不支持 待删除 在某一个 partition 中插入数据
Insert Using a Typed Date Literal for a Partition Column Value
CREATE TABLE students (name STRING, address STRING) PARTITIONED BY (birthday DATE);
INSERT INTO students PARTITION (birthday = date'2019-01-02')
VALUES ('Amy Smith', '123 Park Ave, San Jose');
SELECT * FROM students;
+-------------+-------------------------+-----------+
| name| address| birthday|
+-------------+-------------------------+-----------+
| Amy Smith| 123 Park Ave, San Jose| 2019-01-02|
+-------------+-------------------------+-----------+
INSERT OVERWRITE students PARTITION (birthday = date'2019-01-02')
VALUES('Jason Wang', '908 Bird St, Saratoga');
SELECT * FROM students;
+-----------+-------------------------+-----------+
| name| address| birthday|
+-----------+-------------------------+-----------+
| Jason Wang| 908 Bird St, Saratoga| 2019-01-02|
+-----------+-------------------------+-----------+Insert with a column list
INSERT OVERWRITE boss (name, age, salary) VALUES
('p1', 28, double('infinity'));
SELECT * FROM boss;
+---------+----------------------+----------+
| name| age| salary|
+---------+----------------------+----------+
| p1| 28| Infinity|
+---------+----------------------+----------+todo：不支持 待删除 在某一个 partition 中插入数据
Insert with both a partition spec and a column list
INSERT OVERWRITE students PARTITION (student_id = 11215016) (address, name) VALUES
('Hangzhou, China', 'Kent Yao Jr.');
SELECT * FROM students WHERE student_id = 11215016;
+------------+----------------------+----------+
| name| address|student_id|
+------------+----------------------+----------+
|Kent Yao Jr.| Hangzhou, China| 11215016|
+------------+----------------------+----------+