Create Clustered Columnstore index(MS SQL Server) using SqlAlchemy?

Question

How to create Clustered Columnstore index on a table using SqlAlchemy python library?

I can see support for clustered indexes in the docs here but could not find how to add columnstore keyword to it.

Answer 1

You can solve this by listening to the table after_create event and injecting some custom SQL:

engine = sqlalchemy.create_engine(...)
metadata = MetaData(engine)
table = students = Table(
   'students', metadata, 
   Column('id', Integer), 
   Column('name', String(100)), #Must be fixed length!
   Column('lastname', String(100)),
)

event.listen(
    table, 
    "after_create", 
    DDL("CREATE CLUSTERED COLUMNSTORE INDEX ix_%(table)s_columnstore ON %(fullname)s")
)

table.create()

Here's the documentation

More info on DDL()

Answer 2

sqlalchemy allows you to inject custom SQL handlers, so this should work:

# Create a custom mssql_columnstore argument for Indexes:
Index.argument_for("mssql", "columnstore", None)

# Custom SQL generator for columnstore indexes
@compiles(CreateIndex, "mssql")
def compile_create_index(create, compiler, **kw):
    preparer = compiler.preparer
    mssql_opts = create.element.dialect_options["mssql"]

    columnstore = mssql_opts.get("columnstore", None)
    if(not columnstore):
        stmt = compiler.visit_create_index(create, **kw)
        return stmt

    name = preparer.format_index(create.element)
    table_name = preparer.format_table(create.element.table)
    stmt = f"CREATE CLUSTERED COLUMNSTORE INDEX {name} ON {table_name}"
    return stmt

engine = makeEngine()
metadata = MetaData(engine)
table = students = Table(
   'students', metadata, 
   Column('id', Integer), 
   Column('name', String(100)), 
   Column('lastname', String(100)),
   # Use the columnstore index flag here:
   Index('idx_id', mssql_columnstore = True),
)


table.create()

I based this code on a previous stackoverflow answer