INSERT OVERWRITE
使用新值覆盖表中的现有数据。 通过值表达式或查询的结果指定插入的行。
语法
INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ] [ ( column_list ) ]
{ VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
参数
table_identifier
[database_name.] table_name
:表名,可选择使用数据库名称进行限定。delta.`<path-to-table>`
:现有 Delta 表的位置。
partition_spec
一个可选参数,用于指定分区键值对的逗号分隔列表。
语法:
PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] )
column_list
一个可选参数,用于指定属于
table_identifier
表列的逗号分隔列表。注意
当前行为有一些限制:
- 所有指定的列应存在于表中,并且不能相互重复。 它包含除静态分区列之外的所有列。
- 列列表的大小应与
VALUES
子句或查询的数据大小完全相同。
VALUES ( { value | NULL } [ , … ] ) [ , ( … ) ]
要插入的值。 可以插入显式指定的值或 NULL。 必须使用逗号分隔子句中的每个值。 可以指定多个值集以插入多行。
查询
生成要插入的行的查询。 可以采用以下格式之一:
SELECT
语句TABLE
语句FROM
语句
示例
本节内容:
- 使用
VALUES
子句进行插入 - 使用
SELECT
语句进行插入 - 使用
TABLE
语句进行插入 - 使用
FROM
语句进行插入 - 使用类型化日期文本作为分区列值进行插入
- 插入列列表
- 插入分区规范和列列表
使用 VALUES
子句进行插入
-- Assuming the students table has already been created and populated.
SELECT * FROM students;
+-------------+--------------------------+----------+
| name| address|student_id|
+-------------+--------------------------+----------+
| Amy Smith| 123 Park Ave, San Jose| 111111|
| Bob Brown| 456 Taylor St, Cupertino| 222222|
|Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
|Dora Williams|134 Forest Ave, Menlo Park| 444444|
|Fleur Laurent| 345 Copper St, London| 777777|
|Gordon Martin| 779 Lake Ave, Oxford| 888888|
| Helen Davis| 469 Mission St, San Diego| 999999|
| Jason Wang| 908 Bird St, Saratoga| 121212|
+-------------+--------------------------+----------+
INSERT OVERWRITE students VALUES
('Ashua Hill', '456 Erica Ct, Cupertino', 111111),
('Brian Reed', '723 Kern Ave, Palo Alto', 222222);
SELECT * FROM students;
+----------+-----------------------+----------+
| name| address|student_id|
+----------+-----------------------+----------+
|Ashua Hill|456 Erica Ct, Cupertino| 111111|
|Brian Reed|723 Kern Ave, Palo Alto| 222222|
+----------+-----------------------+----------+
使用 SELECT
语句进行插入
-- Assuming the persons table has already been created and populated.
SELECT * FROM persons;
+-------------+--------------------------+---------+
| name| address| ssn|
+-------------+--------------------------+---------+
|Dora Williams|134 Forest Ave, Menlo Park|123456789|
+-------------+--------------------------+---------+
| Eddie Davis| 245 Market St, Milpitas|345678901|
+-------------+--------------------------+---------+
INSERT OVERWRITE students PARTITION (student_id = 222222)
SELECT name, address FROM persons WHERE name = "Dora Williams";
SELECT * FROM students;
+-------------+--------------------------+----------+
| name| address|student_id|
+-------------+--------------------------+----------+
| Ashua Hill| 456 Erica Ct, Cupertino| 111111|
+-------------+--------------------------+----------+
|Dora Williams|134 Forest Ave, Menlo Park| 222222|
+-------------+--------------------------+----------+
使用 TABLE
语句进行插入
-- Assuming the visiting_students table has already been created and populated.
SELECT * FROM visiting_students;
+-------------+---------------------+----------+
| name| address|student_id|
+-------------+---------------------+----------+
|Fleur Laurent|345 Copper St, London| 777777|
+-------------+---------------------+----------+
|Gordon Martin| 779 Lake Ave, Oxford| 888888|
+-------------+---------------------+----------+
INSERT OVERWRITE students TABLE visiting_students;
SELECT * FROM students;
+-------------+---------------------+----------+
| name| address|student_id|
+-------------+---------------------+----------+
|Fleur Laurent|345 Copper St, London| 777777|
+-------------+---------------------+----------+
|Gordon Martin| 779 Lake Ave, Oxford| 888888|
+-------------+---------------------+----------+
使用 FROM
语句进行插入
-- Assuming the applicants table has already been created and populated.
SELECT * FROM applicants;
+-----------+--------------------------+----------+---------+
| name| address|student_id|qualified|
+-----------+--------------------------+----------+---------+
|Helen Davis| 469 Mission St, San Diego| 999999| true|
+-----------+--------------------------+----------+---------+
| Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
+-----------+--------------------------+----------+---------+
| Jason Wang| 908 Bird St, Saratoga| 121212| true|
+-----------+--------------------------+----------+---------+
INSERT OVERWRITE students
FROM applicants SELECT name, address, id applicants WHERE qualified = true;
SELECT * FROM students;
+-----------+-------------------------+----------+
| name| address|student_id|
+-----------+-------------------------+----------+
|Helen Davis|469 Mission St, San Diego| 999999|
+-----------+-------------------------+----------+
| Jason Wang| 908 Bird St, Saratoga| 121212|
+-----------+-------------------------+----------+
使用类型化日期文本作为分区列值进行插入
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 INTO 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 OVERWRITE students (address, name, student_id) VALUES
('Hangzhou, China', 'Kent Yao', 11215016);
SELECT * FROM students WHERE name = 'Kent Yao';
+---------+----------------------+----------+
| name| address|student_id|
+---------+----------------------+----------+
|Kent Yao | Hangzhou, China| 11215016|
+---------+----------------------+----------+
插入分区规范和列列表
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|
+------------+----------------------+----------+