[SPARK-58261][SQL][SDP] Support SCD Type 2 syntax in SQL AUTO CDC by anew · Pull Request #57434 · apache/spark

anew · 2026-07-22T18:32:49Z

What changes were proposed in this pull request?

Add SQL syntax for selecting the SCD type and history-tracking columns of an AUTO CDC flow, mirroring the recently added Python/Connect surface. Both SQL AUTO CDC forms -- CREATE STREAMING TABLE ... FLOW AUTO CDC ... and CREATE FLOW ... AS AUTO CDC INTO ... -- now accept:

STORED AS SCD TYPE 1 | 2 (defaults to SCD Type 1 when omitted), and
TRACK HISTORY ON (<cols>) / TRACK HISTORY ON * EXCEPT (<cols>) under SCD Type 2.

Specifically:

Grammar: add multi-word lexer tokens SCD_TYPE_1/SCD_TYPE_2 and HISTORY/TRACK keywords; add autoCdcStoredAsClause and autoCdcTrackHistoryClause, appended in fixed order after the existing COLUMNS clause. HISTORY/TRACK are registered as non-reserved.
AST/plans: thread storedAsScdType (Int, default 1) and the track-history column lists through AutoCdcParams, AutoCdcInto, and CreateStreamingTableAutoCdc.
Registration: SqlGraphRegistrationContext.buildChangeArgs maps the SCD type onto ScdType, builds trackHistorySelection, and rejects TRACK HISTORY under SCD1 as well as specifying both track-history lists.

The SCD type and track-history columns feed the existing ChangeArgs model (storedAsScdType / trackHistorySelection), the same contract the engine's SCD2 execution work consumes.

Why are the changes needed?

SQL AUTO CDC previously only supported SCD Type 1 and rejected STORED AS SCD TYPE 2 / TRACK HISTORY at parse time. SCD Type 2 (history tracking) is a common CDC requirement and is already modeled in the engine's ChangeArgs / ScdType, so the SQL surface was the missing piece.

Does this PR introduce any user-facing change?

Yes. SQL AUTO CDC statements accept STORED AS SCD TYPE 1|2 and, under SCD Type 2, TRACK HISTORY ON .... Previously these clauses failed to parse. This is a change within the unreleased master branch only.

How was this patch tested?

New parser tests in AutoCdcParserSuite (SCD type 1/2, TRACK HISTORY include/except, all clauses combined, clause-ordering and invalid-type negative cases) and registration tests in SqlPipelineSuite (SCD2 and track-history map onto ChangeArgs; TRACK HISTORY without SCD2 is rejected). Ran AutoCdcParserSuite, SQLKeywordSuite, and the AUTO CDC SqlPipelineSuite cases.

Was this patch authored or co-authored using generative AI tooling?

Co-authored-by: Opus 4.8

### What changes were proposed in this pull request? Add SQL syntax for selecting the SCD type and history-tracking columns of an AUTO CDC flow, mirroring the recently added Python/Connect surface. Both SQL AUTO CDC forms -- `CREATE STREAMING TABLE ... FLOW AUTO CDC ...` and `CREATE FLOW ... AS AUTO CDC INTO ...` -- now accept: - `STORED AS SCD TYPE 1 | 2` (defaults to SCD Type 1 when omitted), and - `TRACK HISTORY ON (<cols>)` / `TRACK HISTORY ON * EXCEPT (<cols>)` under SCD Type 2. Specifically: - Grammar: add multi-word lexer tokens `SCD_TYPE_1`/`SCD_TYPE_2` and `HISTORY`/`TRACK` keywords; add `autoCdcStoredAsClause` and `autoCdcTrackHistoryClause`, appended in fixed order after the existing `COLUMNS` clause. `HISTORY`/`TRACK` are registered as non-reserved. - AST/plans: thread `storedAsScdType` (Int, default 1) and the track-history column lists through `AutoCdcParams`, `AutoCdcInto`, and `CreateStreamingTableAutoCdc`. - Registration: `SqlGraphRegistrationContext.buildChangeArgs` maps the SCD type onto `ScdType`, builds `trackHistorySelection`, and rejects TRACK HISTORY under SCD1 as well as specifying both track-history lists. The SCD type and track-history columns feed the existing `ChangeArgs` model (`storedAsScdType` / `trackHistorySelection`), the same contract the engine's SCD2 execution work consumes. ### Why are the changes needed? SQL AUTO CDC previously only supported SCD Type 1 and rejected `STORED AS SCD TYPE 2` / `TRACK HISTORY` at parse time. SCD Type 2 (history tracking) is a common CDC requirement and is already modeled in the engine's `ChangeArgs` / `ScdType`, so the SQL surface was the missing piece. ### Does this PR introduce any user-facing change? Yes. SQL AUTO CDC statements accept `STORED AS SCD TYPE 1|2` and, under SCD Type 2, `TRACK HISTORY ON ...`. Previously these clauses failed to parse. This is a change within the unreleased master branch only. ### How was this patch tested? New parser tests in `AutoCdcParserSuite` (SCD type 1/2, TRACK HISTORY include/except, all clauses combined, clause-ordering and invalid-type negative cases) and registration tests in `SqlPipelineSuite` (SCD2 and track-history map onto `ChangeArgs`; TRACK HISTORY without SCD2 is rejected). Ran `AutoCdcParserSuite`, `SQLKeywordSuite`, and the AUTO CDC `SqlPipelineSuite` cases. Co-authored-by: Isaac

Adding the HISTORY and TRACK non-reserved keywords for the SCD2 AUTO CDC syntax changes the output of SQL_KEYWORDS(), which the keywords.sql / keywords-enforced.sql / nonansi/keywords.sql golden-file tests assert against (run by both SQLQueryTestSuite and ThriftServerQueryTestSuite). Regenerate the .sql.out files via SPARK_GENERATE_GOLDEN_FILES=1. The multi-word SCD_TYPE_1 / SCD_TYPE_2 tokens contain spaces and digits and are excluded by the keyword regex, so only HISTORY and TRACK are added. Co-authored-by: Isaac

anew · 2026-07-22T21:54:52Z

 SECONDS: 'SECONDS';
 SCHEMA: 'SCHEMA';
 SCHEMAS: 'SCHEMAS';
+SCD_TYPE_1: 'SCD TYPE 1';


This is suboptimal as it expects excatly one space between the words.
I filed https://issues.apache.org/jira/browse/SPARK-58270 for future improvement.

szehon-ho · 2026-07-22T23:54:19Z

Two test additions would be good (either in AutoCdcParserSuite or the pipeline suite):

Explicit STORED AS SCD TYPE 1 combined with TRACK HISTORY is rejected -- currently only the implicit default-SCD1 + TRACK HISTORY case is covered.
Identifiers named after the new keywords still parse -- e.g. columns/tables named history and track (and scd, if the single-word SCD keyword suggested on the lexer is adopted) -- to guard the non-reserved classification.

The HISTORY and TRACK non-reserved keywords added for the SCD2 AUTO CDC syntax appear in DatabaseMetaData.getSQLKeywords(), which SparkConnectDatabaseMetaDataSuite asserts against with a hardcoded expected string. Insert both keywords in their sorted positions (after HANDLER and TOUCH respectively). Co-authored-by: Isaac

szehon-ho · 2026-07-23T00:07:05Z

    | ROWS
    | SCHEMA
    | SCHEMAS
+    | SCD_TYPE_1


These two (and the matching SCD_TYPE_1/SCD_TYPE_2 pair added to nonReserved below) can just be dropped from the non-reserved lists -- they don't serve the usual purpose, and removing them is safe:

Non-reserved only governs whether a token can be used as a bare identifier, but SCD_TYPE_1/SCD_TYPE_2 are multi-word literals ('SCD TYPE 1'), so they can never stand in as an identifier anyway.

autoCdcStoredAsClause references the tokens directly, so parsing STORED AS SCD TYPE 1|2 doesn't depend on their non-reserved membership.

SQLKeywordSuite's extractor regex is [A-Z_]+, which the digit in the token name fails, so these are already excluded from allCandidateKeywords and the computed non-reserved sets -- listing them here has no effect on the suite, and they were (correctly) never added to the docs table or the keywords*.sql.out golden files.

They're new in this PR, so nothing external depends on them.

Net: it's a pure deletion with no docs/golden/test changes, and scd stays a valid identifier either way (it isn't a keyword in this approach).

Good call — went a step further and reworked the lexing: SCD TYPE n is now three separate tokens (SCD, TYPE, INTEGER_VALUE) validated in the AstBuilder, instead of multi-word literals. That drops the odd non-reserved entries you flagged, and as a bonus fixes the whitespace brittleness from SPARK-58270 (which can probably be closed). SCD TYPE 3 now gives a clear "Unsupported SCD type" error too.

…erals Address review: replace the multi-word lexer literals SCD_TYPE_1 ('SCD TYPE 1') and SCD_TYPE_2 ('SCD TYPE 2') with a single SCD keyword token, and parse the clause as `STORED AS SCD TYPE type=INTEGER_VALUE`, validating the number (1 or 2) in the AstBuilder with a clear error. Benefits: - Fixes the whitespace brittleness the multi-word literals imposed (SPARK-58270): arbitrary whitespace between SCD, TYPE and the number is now accepted. - Removes the SCD_TYPE_1/SCD_TYPE_2 entries from the non-reserved lists, which had no effect (multi-word literals can never stand in as identifiers, and the digit in the token name excluded them from SQLKeywordSuite / docs / golden files). - SCD TYPE 3 (and other unsupported numbers) now fail with a clear "Unsupported SCD type" message rather than a generic parse error. SCD is a plain [A-Z_]+ keyword, so it flows into the keyword lists: added to the docs table, keywords*.sql.out golden files (regenerated), and the JDBC getSQLKeywords expected string. `scd` remains usable as an identifier (non-reserved). Co-authored-by: Isaac

…dentifiers Two test additions suggested in review: - AUTO CDC with an explicit STORED AS SCD TYPE 1 plus TRACK HISTORY is rejected (SqlPipelineSuite); previously only the implicit-default-SCD1 case was covered. - The new non-reserved keywords history, track and scd still parse as ordinary table/column identifiers (AutoCdcParserSuite), guarding their non-reserved classification against regressions. Co-authored-by: Isaac

anew · 2026-07-23T00:46:42Z

Added the two new tests: SqlPipelineSuite now covers explicit STORED AS SCD TYPE 1 + TRACK HISTORY rejection (alongside the implicit case), and AutoCdcParserSuite has a test using history, track, and scd as table/column identifiers to guard their non-reserved status.

szehon-ho · 2026-07-23T00:52:54Z

+      // supported; reject anything else with a clear error rather than a generic parse failure.
+      val storedAsScdType = Option(params.autoCdcStoredAsClause()) match {
+        case Some(c) =>
+          val scdType = c.`type`.getText.toInt


Minor robustness nit: INTEGER_VALUE is DIGIT+, so an overflowing literal such as STORED AS SCD TYPE 99999999999999999999 makes .toInt throw an uncaught NumberFormatException -- which surfaces as an internal error/stack trace rather than the intended Unsupported SCD type message this block is trying to produce.

AstBuilder already guards this pattern elsewhere (the nearest-by num, ~L2627):

val value = try n.getText.toLong catch { case _: NumberFormatException => throw ...outOfRange... }

Since only 1 and 2 are valid, the simplest fix is to branch on the literal text instead of parsing to Int -- e.g. match getText on "1" / "2" and route everything else (including oversized numbers) through the existing operationNotAllowed(...). That closes the overflow hole and keeps the clear error message.

szehon-ho · 2026-07-23T00:52:55Z

    ;

+autoCdcStoredAsClause
+    : STORED AS SCD TYPE type=INTEGER_VALUE


Minor: the label name type collides with the Scala keyword, forcing a backtick escape when accessing it in the AstBuilder. Renaming the label to scdType=INTEGER_VALUE reads better and avoids the escape.

…list The SPARK-43119 "Get SQL Keywords" test asserts against a hardcoded keyword string (a fourth keyword-list surface, alongside the docs table, the keywords*.sql.out golden files, and the JDBC getSQLKeywords string). Insert the three new non-reserved keywords in their sorted positions. Co-authored-by: Isaac

Two review nits on the STORED AS SCD TYPE handling: - INTEGER_VALUE is DIGIT+, so an oversized literal (e.g. SCD TYPE 99999999999999999999) made `.toInt` throw an uncaught NumberFormatException, surfacing as an internal error instead of the intended "Unsupported SCD type" message. Match on the token text ("1"/"2") and route everything else through operationNotAllowed, closing the overflow hole. - Rename the grammar label `type=INTEGER_VALUE` to `scdType=INTEGER_VALUE` to avoid the Scala-keyword collision that forced a backtick escape in the AstBuilder. Add a parser test for the overflowing-number case. Co-authored-by: Isaac

anew force-pushed the spark-58261-sql-syntax-scd2 branch from 964de97 to 7a67574 Compare July 22, 2026 20:06

anew changed the title ~~[WIP][SPARK-58261][SQL][SDP] Support SCD Type 2 syntax in SQL AUTO CDC~~ [SPARK-58261][SQL][SDP] Support SCD Type 2 syntax in SQL AUTO CDC Jul 22, 2026

anew commented Jul 22, 2026

View reviewed changes

szehon-ho reviewed Jul 23, 2026

View reviewed changes

anew added 2 commits July 23, 2026 00:37

szehon-ho reviewed Jul 23, 2026

View reviewed changes

anew added 2 commits July 23, 2026 01:49

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

[SPARK-58261][SQL][SDP] Support SCD Type 2 syntax in SQL AUTO CDC#57434

[SPARK-58261][SQL][SDP] Support SCD Type 2 syntax in SQL AUTO CDC#57434
anew wants to merge 7 commits into
apache:masterfrom
anew:spark-58261-sql-syntax-scd2

anew commented Jul 22, 2026

Uh oh!

anew Jul 22, 2026

Uh oh!

szehon-ho commented Jul 22, 2026

Uh oh!

szehon-ho Jul 23, 2026

Uh oh!

anew Jul 23, 2026

Uh oh!

anew commented Jul 23, 2026

Uh oh!

szehon-ho Jul 23, 2026

Uh oh!

szehon-ho Jul 23, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

anew commented Jul 22, 2026

What changes were proposed in this pull request?

Why are the changes needed?

Does this PR introduce any user-facing change?

How was this patch tested?

Was this patch authored or co-authored using generative AI tooling?

Uh oh!

anew Jul 22, 2026

Choose a reason for hiding this comment

Uh oh!

szehon-ho commented Jul 22, 2026

Uh oh!

szehon-ho Jul 23, 2026

Choose a reason for hiding this comment

Uh oh!

anew Jul 23, 2026

Choose a reason for hiding this comment

Uh oh!

anew commented Jul 23, 2026

Uh oh!

szehon-ho Jul 23, 2026

Choose a reason for hiding this comment

Uh oh!

szehon-ho Jul 23, 2026

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants