Spark SQL----INSERT TABLE

Spark SQL----INSERT TABLE

一、描述

INSERT语句将新行插入表中或覆盖表中的现有数据。插入的行可以由值表达式指定，也可以由查询结果指定。

二、语法

INSERT[INTO| OVERWRITE ][TABLE] table_identifier [ partition_spec ][( column_list )]
    { VALUES( { value|NULL } [,...])[,(...)]| query }

INSERTINTO[TABLE] table_identifier REPLACEWHERE boolean_expression query

三、参数

• table_identifier 指定一个表名称，可以选择使用数据库名称对其进行限定。 语法：[ database_name. ] table_name
• partition_spec 一个可选参数，用于指定分区的键值对的逗号分隔列表。请注意，可以在分区规范中使用类型化的文字（例如，date’2019-01-02’）。 语法：PARTITION ( partition_col_name = partition_col_val [ , … ] )
• column_list 一个可选参数，用于指定属于table_identifier表的列的逗号分隔列表。Spark会根据指定的column列表对输入查询的列进行重新排序，以匹配表schema。 注：当前行为有一些局限性： - 所有指定的列都应存在于表中，并且不能相互重复。它包括除静态分区列之外的所有列。- column列表的大小应该与VALUES子句或查询中的数据大小完全相同。
• VALUES ( { value | NULL } [ , … ] ) [ , ( … ) ] 指定要插入的值。可以插入显式指定的值或NULL。必须使用逗号分隔子句中的每个值。可以指定多个值集来插入多行。
• boolean_expression 指定计算结果为布尔型的任何表达式。可以使用逻辑运算符（AND, OR）将两个或多个表达式组合在一起。
• query 生成要插入的行的查询。它可以是以下格式之一： - SELECT语句- Inline Table语句- FROM语句

四、例子

4.1 Insert Into

使用VALUES子句进行单行插入

CREATETABLE students (name VARCHAR(64), address VARCHAR(64))USING PARQUET PARTITIONED BY(student_id INT);INSERTINTO students VALUES('Amy Smith','123 Park Ave, San Jose',111111);SELECT*FROM students;+---------+----------------------+----------+|     name|               address|student_id|+---------+----------------------+----------+|Amy Smith|123 Park Ave, San Jose|111111|+---------+----------------------+----------+

使用VALUES子句进行多行插入

INSERTINTO students VALUES('Bob Brown','456 Taylor St, Cupertino',222222),('Cathy Johnson','789 Race Ave, Palo Alto',333333);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|+--------------+-----------------------+----------+

使用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|+-------------+--------------------------+---------+INSERTINTO students PARTITION(student_id =444444)SELECT name, address FROM persons WHERE name ="Dora Williams";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|+-------------+--------------------------+----------+

使用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|+-------------+---------------------+----------+INSERTINTO students TABLE visiting_students;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|+-------------+--------------------------+----------+

使用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|+-----------+--------------------------+----------+---------+INSERTINTO students
     FROM applicants SELECT name, address, student_id WHERE qualified =true;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|+-------------+--------------------------+----------+

为分区列值使用类型化日期文字插入

CREATETABLE students (name STRING, address  STRING) PARTITIONED BY(birthday DATE);INSERTINTO 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|

使用column列表插入

INSERTINTO 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|+---------+----------------------+----------+

同时使用分区spec和column列表进行插入

INSERTINTO 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|+------------+----------------------+----------+

4.2 Insert Overwrite

使用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|+-------------+--------------------------+----------+

使用REPLACE WHERE语句插入

-- Assuming the persons and persons2 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|+-------------+--------------------------+---------+SELECT*FROM persons2;+-------------+--------------------------+---------+|         name|                   address|      ssn|+-------------+--------------------------+---------+|   Ashua Hill|456 Erica Ct, Cupertino|432795921|+-------------+--------------------------+---------+-- in an atomic operation, 1) delete rows with ssn = 123456789 and 2) insert rows from persons2 INSERTINTO persons REPLACEWHERE ssn =123456789SELECT*FROM persons2

SELECT*FROM persons;+-------------+--------------------------+---------+|         name|                   address|      ssn|+-------------+--------------------------+---------+|  Eddie Davis|245 Market St, Milpitas|345678901|+-------------+--------------------------+---------+|   Ashua Hill|456 Erica Ct, Cupertino|432795921|+-------------+--------------------------+---------+

使用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, student_id 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|+-----------+-------------------------+----------+

为分区列值使用类型化日期文字插入

CREATETABLE students (name STRING, address  STRING) PARTITIONED BY(birthday DATE);INSERTINTO 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|+-----------+-------------------------+-----------+

使用column列表插入

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|+---------+----------------------+----------+

同时使用分区spec和column列表进行插入

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|+------------+----------------------+----------+