tsql: support Azure Synapse OPENROWSET WITH schema clause #8151

Summary

The tsql dialect cannot parse an Azure Synapse OPENROWSET table source when it is followed by the documented WITH (...) schema definition. This prevents SQLMesh from parsing valid Synapse views and caused a QESD Synapse lineage build to fail.

OPENROWSET appears to be partially supported: SQLGlot can parse the table-valued function and its BULK options, but the WITH (...) AS alias schema clause is rejected.

Reproduction

Tested with SQLGlot 30.17.0:

import sqlglot

sql = """
SELECT sample_id
FROM OPENROWSET(
    BULK (
        '/lake-landing/reference data/area_type/*.*',
        '/lake-landing/this_file_doesnt_exist.parquet'
    ),
    DATA_SOURCE = 'datasource_gbrwlake',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2
)
WITH (
    sample_id VARCHAR(50)
) AS area_type
"""

sqlglot.parse_one(sql, read="tsql")

Actual result:

sqlglot.errors.ParseError: Expecting ).

The parse error is reported at the WITH schema clause. The same construct failed in SQLGlot 30.8.0, which was the version installed by SQLMesh 0.236.1 in the CI job.

For comparison, removing the WITH (...) clause allows the corresponding table source to parse:

SELECT *
FROM OPENROWSET(
    BULK ('file.csv'),
    DATA_SOURCE = 'datasource_gbrwlake',
    FORMAT = 'csv'
) AS source_table

Expected behavior

The T-SQL parser should represent the OPENROWSET source, its options, the WITH column definitions, and the table alias in the AST, and should round-trip the statement for the tsql dialect. A focused parser and generator test for Azure Synapse OPENROWSET ... WITH (...) syntax would be useful.

Related issues and compatibility context

• SQLGlot #7428 reports Azure Synapse-specific T-SQL syntax that is unsupported. The maintainer response says Azure Synapse is not officially supported and considers the issue out of scope.
• SQLGlot #5142 documents additional Azure SQL/T-SQL syntax gaps and describes this support as low priority, while welcoming a well-tested PR.
• SQLMesh #5948 records that SQLMesh 0.236.1 pins SQLGlot to ~=30.8.0 and that independently overriding SQLGlot to >=30.14.0 exposed import incompatibilities. SQLGlot 30.17.0 still produces the OPENROWSET ... WITH (...) parse error, so upgrading SQLGlot alone does not resolve this syntax gap.

If Azure Synapse-specific syntax is intentionally out of scope for the core tsql dialect, please advise whether this should be implemented as a separate dialect or whether a narrowly scoped, well-tested OPENROWSET contribution would be considered.