[SPARK-48824] Add SQL syntax in create/replace table to create an identity column

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Carmen Kwan
• Reporter: Carmen Kwan
• Created: 05/Jul/24 17:54
• Updated: 15/Sep/24 05:35
• Resolved: 15/Sep/24 05:35

Description

Add SQL support for creating identity columns. Identity Column syntax should be flexible such that users can specify 

• whether identity values are always generated by the system
• (optionally) the starting value of the column 
• (optionally) the increment/step of the column

The SQL syntax support should also allow flexible ordering of the increment and starting values, as both variants are used in the wild by other systems (e.g. PostgreSQL Oracle). That is, we should allow both

START WITH <start> INCREMENT BY <step>

and

INCREMENT BY <step> START WITH <start>

.

For example, we should be able to define

CREATE TABLE default.example (
  id LONG GENERATED ALWAYS AS IDENTITY,
  id2 LONG GENERATED BY DEFAULT START WITH 0 INCREMENT BY -10,
  id3 LONG GENERATED ALWAYS AS IDENTITY INCREMENT BY 2 START WITH -8,
  value LONG
)

This will enable defining identity columns in Spark SQL for data sources that support it. 

Issue Links

• links to: GitHub Pull Request #47614