This is an automated email from the ASF dual-hosted git repository.
szehon-ho pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new a89c91fee7b7 [SPARK-57316][DOC] Document WITH SCHEMA EVOLUTION and BY
NAME for SQL INSERT
a89c91fee7b7 is described below
commit a89c91fee7b75df27094915112d22b106ab04a2b
Author: Thang Long Vu <[email protected]>
AuthorDate: Tue Jun 9 17:20:53 2026 -0700
[SPARK-57316][DOC] Document WITH SCHEMA EVOLUTION and BY NAME for SQL INSERT
### What changes were proposed in this pull request?
[SPARK-54971](https://issues.apache.org/jira/browse/SPARK-54971) (#53732)
added the `WITH SCHEMA EVOLUTION` syntax to the SQL `INSERT` command, but did
not add user-facing documentation for it. This PR documents the new clause on
the `INSERT TABLE` SQL reference page
(`docs/sql-ref-syntax-dml-insert-table.md`):
- Adds the optional `[ WITH SCHEMA EVOLUTION ]` clause to both syntax
forms, mirroring the grammar where the clause appears right after `INSERT` and
before `INTO` / `OVERWRITE`.
- Adds a `WITH SCHEMA EVOLUTION` entry to the Parameters section describing
the behavior.
- Adds a `BY NAME` entry to the Parameters section noting that columns and
nested fields are matched by position by default, or by name when `BY NAME` is
specified.
- Documents `BY NAME` support for `INSERT INTO ... REPLACE WHERE` (added in
#53567): updates the syntax form to `INSERT [ WITH SCHEMA EVOLUTION ] INTO [
TABLE ] table_identifier [ BY NAME ] REPLACE WHERE boolean_expression query`
and adds an "Insert By Name Using a REPLACE WHERE Statement" example.
### Why are the changes needed?
The `WITH SCHEMA EVOLUTION` syntax for SQL `INSERT` was introduced without
documentation. This page is the reference for the `INSERT` statement, so the
new clause should be documented there.
### Does this PR introduce _any_ user-facing change?
No. Documentation-only update.
### How was this patch tested?
Docs-only change. Reviewed the rendered Markdown for correctness.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Anthropic)
Closes #56370 from longvu-db/insert-schema-evolution-docs.
Authored-by: Thang Long Vu <[email protected]>
Signed-off-by: Szehon Ho <[email protected]>
(cherry picked from commit fc527bc70dad94dd89515204fd74aafc3ad9f3de)
Signed-off-by: Szehon Ho <[email protected]>
---
docs/sql-ref-syntax-dml-insert-table.md | 114 +++++++++++++++++++++++++++++++-
1 file changed, 112 insertions(+), 2 deletions(-)
diff --git a/docs/sql-ref-syntax-dml-insert-table.md
b/docs/sql-ref-syntax-dml-insert-table.md
index 6f85d4401d3b..4f78e6d81aee 100644
--- a/docs/sql-ref-syntax-dml-insert-table.md
+++ b/docs/sql-ref-syntax-dml-insert-table.md
@@ -26,14 +26,20 @@ The `INSERT` statement inserts new rows into a table or
overwrites the existing
### Syntax
```sql
-INSERT [ INTO | OVERWRITE ] [ TABLE ] table_identifier [ partition_spec ] [ (
column_list ) | [BY NAME] ]
+INSERT [ WITH SCHEMA EVOLUTION ] [ INTO | OVERWRITE ] [ TABLE ]
table_identifier [ partition_spec ] [ ( column_list ) | [BY NAME] ]
{ VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
-INSERT INTO [ TABLE ] table_identifier REPLACE WHERE boolean_expression query
+INSERT [ WITH SCHEMA EVOLUTION ] INTO [ TABLE ] table_identifier [ BY NAME ]
REPLACE WHERE boolean_expression query
```
### Parameters
+* **WITH SCHEMA EVOLUTION**
+
+ Enables automatic schema evolution for this `INSERT` operation. When
enabled, the schema
+ of the target table is automatically evolved to add new columns and widen
data types based
+ on the source query, subject to the capabilities of the underlying
connector.
+
* **table_identifier**
Specifies a table name, which may be optionally qualified with a database
name.
@@ -55,6 +61,11 @@ INSERT INTO [ TABLE ] table_identifier REPLACE WHERE
boolean_expression query
- All specified columns should exist in the table and not be duplicated
from each other. It includes all columns except the static partition columns.
- The size of the column list should be exactly the size of the data from
`VALUES` clause or query.
+* **BY NAME**
+
+ By default, columns and nested fields are matched by position between the
source query and the target
+ table. With `BY NAME`, columns and nested fields are matched by name,
allowing them to be ordered differently in the source query and target table.
+
* **VALUES ( { value `|` NULL } [ , ... ] ) [ , ( ... ) ]**
Specifies the values to be inserted. Either an explicitly specified value
or a NULL can be inserted.
@@ -259,6 +270,72 @@ SELECT * FROM students WHERE student_id = 11215017;
+------------+----------------------+----------+
```
+##### Insert By Name Using a SELECT Statement
+
+```sql
+CREATE TABLE target (n INT, text STRING, s STRUCT<a INT, b INT>);
+
+-- BY NAME matches both top-level columns and nested struct fields by name,
+-- so they may be listed in any order in the source query.
+INSERT INTO target BY NAME
+ SELECT named_struct('b', 2, 'a', 1) AS s, 0 AS n, 'data' AS text;
+-- s is {1, 2}: nested fields matched by name, not position.
+
+SELECT * FROM target;
++---+----+------+
+| n|text| s|
++---+----+------+
+| 0|data|{1, 2}|
++---+----+------+
+
+CREATE OR REPLACE TABLE target (n INT, arr ARRAY<STRUCT<a INT, b INT>>);
+
+INSERT INTO target BY NAME
+ SELECT array(named_struct('a', 1, 'b', 2)) AS arr, 0 AS n;
+-- A missing top-level column is filled with its default value (NULL here).
+INSERT INTO target BY NAME
+ SELECT array(named_struct('a', 1, 'b', 2)) AS arr;
+
+SELECT * FROM target;
++----+--------+
+| n| arr|
++----+--------+
+| 0|[{1, 2}]|
+|NULL|[{1, 2}]|
++----+--------+
+
+-- A source column whose name does not match any target column is an error.
+INSERT INTO target BY NAME
+ SELECT array(named_struct('a', 1, 'b', 2)) AS arr, 0 AS badname;
+Error: INCOMPATIBLE_DATA_FOR_TABLE.EXTRA_COLUMNS
+```
+
+##### Insert With Schema Evolution
+
+```sql
+CREATE TABLE students (student_id INT, name STRING);
+CREATE TABLE new_students (student_id INT, name STRING, address STRING);
+
+INSERT INTO students VALUES (444444, 'Bob Brown'), (555555, 'Cathy Johnson');
+INSERT INTO new_students VALUES
+ (111111, 'Ashua Hill', '456 Erica Ct, Cupertino'),
+ (222222, 'Dora Williams', '134 Forest Ave, Menlo Park');
+
+-- Evolve the students table schema to add the new address column from the
query.
+INSERT WITH SCHEMA EVOLUTION INTO students
+ SELECT * FROM new_students;
+
+SELECT * FROM students;
++----------+-------------+--------------------------+
+|student_id| name| address|
++----------+-------------+--------------------------+
+| 444444| Bob Brown| NULL|
+| 555555|Cathy Johnson| NULL|
+| 111111| Ashua Hill| 456 Erica Ct, Cupertino|
+| 222222|Dora Williams|134 Forest Ave, Menlo Park|
++----------+-------------+--------------------------+
+```
+
#### Insert Overwrite
##### Insert Using a VALUES Clause
@@ -391,6 +468,39 @@ SELECT * FROM persons;
+-------------+--------------------------+---------+
```
+##### Insert By Name Using a REPLACE WHERE Statement
+
+```sql
+-- Assuming the persons and new_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|
++-------------+--------------------------+---------+
+
+-- new_persons lists the columns in a different order than the target table.
+SELECT * FROM new_persons;
++-----------------------+---------+----------+
+| address| ssn| name|
++-----------------------+---------+----------+
+|456 Erica Ct, Cupertino|432795921|Ashua Hill|
++-----------------------+---------+----------+
+
+-- BY NAME matches the query fields to the target columns by name instead of
by position,
+-- so the column order mismatch is resolved automatically.
+INSERT INTO persons BY NAME REPLACE WHERE ssn = 123456789 SELECT * FROM
new_persons;
+
+SELECT * FROM persons;
++-----------+-----------------------+---------+
+| name| address| ssn|
++-----------+-----------------------+---------+
+|Eddie Davis|245 Market St, Milpitas|345678901|
+| Ashua Hill|456 Erica Ct, Cupertino|432795921|
++-----------+-----------------------+---------+
+```
+
##### Insert Using a TABLE Statement
```sql
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
