TSQL: table structure options for Azure Synapse not supported

[keen85]

Azure Synapse Dedicated SQL Pool uses TSQL dialect, with some additions:
For CREATE TABLE the "structure" of the table (heap, clustered column store, clustered index) can be specified directly.
This seems not to be supported by sqlglot as of version 30.1.0.

Fully reproducible code snippet

import sqlglot

sqlglot.parse_one(
    read="tsql",
    sql="CREATE TABLE t1 (id INT) WITH (HEAP)",
)  # ✅ works

sqlglot.parse_one(
    read="tsql",
    sql="CREATE TABLE t1 (id INT) WITH (CLUSTERED COLUMNSTORE INDEX)",
)  # ❌ fails

sqlglot.parse_one(
    read="tsql",
    sql="CREATE TABLE t1 (id INT) WITH (CLUSTERED INDEX (id))",
) # ❌ fails

sqlglot.errors.ParseError: Expecting (. Line 1, Col: 52.
  CREATE TABLE t1 (id INT) WITH (CLUSTERED COLUMNSTORE INDEX)

Official Documentation
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=azure-sqldw-latest#table-structure-options

I tried to define a custom dialect, but it did not help

import sqlglot
from sqlglot import TokenType
from sqlglot.dialects.tsql import TSQL
from sqlglot.tokens import Tokenizer

class DsqlDialect(TSQL):
    class Tokenizer(Tokenizer):
        KEYWORDS = {
            **Tokenizer.KEYWORDS,
            "CLUSTERED COLUMNSTORE INDEX": TokenType.INDEX,
        }

[georgesittas]

This is out of scope for the core team. Note that Azure Synapse is not officially supported, to my knowledge.

[keen85]

@georgesittas , understood.

Still, could you point me in the right direction (some bullet points what needs to be done / docs / example of a similar issue), so that maybe I can work around it via custom dialect?

I was wonder if this is a small thing because the first example from my original post (with HEAP) already does work.

This is what I got so far:

import sqlglot
from sqlglot import Generator, exp
from sqlglot.dialects.tsql import TSQL
from sqlglot.expressions import Property

class CcixProperty(Property):
    arg_types = {}

class DsqlDialect(TSQL):
    class Generator(Generator):
        TRANSFORMS = {
            **Generator.TRANSFORMS,
            CcixProperty: lambda *_: "CLUSTERED COLUMNSTORE INDEX",
        }

        PROPERTIES_LOCATION = {
            **Generator.PROPERTIES_LOCATION,
            CcixProperty: exp.Properties.Location.POST_WITH,
        }