Recommended IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris)

[Ozoubia]

Hi everyone,

I'm running Superset with InterSystems IRIS as a data source and I'm planning to move to Superset 6.x.
My current setup uses:

• apache/superset:4.0.2 (official image)
• sqlalchemy-iris==0.18.1 (caretdev/sqlalchemy-iris)
• intersystems-irispython==5.4.0

While testing Superset 6.x with the same connector, I ran into problems with queries involving
row limits. IRIS uses SELECT TOP n rather than LIMIT, and some generated queries didn't work.

A few questions:

1. Is there a newer or recommended connector/dialect for IRIS that works well with Superset 6.x?
2. Has anyone successfully used IRIS with Superset 6.x, and if so, with which package versions?
3. Are there alternative approaches (e.g. a different driver, JDBC/ODBC bridge, or engine spec settings)
   that others use for IRIS?

Any pointers would be appreciated. Thanks!

[TayfurYldz]

There does not appear to be a Superset-specific IRIS engine spec in Superset 6.x, so I would treat the SQLAlchemy dialect as the component that must get IRIS pagination syntax right rather than trying to fix this with a Superset engine-spec override.

I checked current Superset main (79af673bca50b4bbcb7882fe131b305abd952be0) and the versioned 6.1 docs. IRIS is not among the bundled engine specs/supported-database entries. Superset's documented generic contract for other databases is a Python DB-API driver plus a SQLAlchemy dialect.

The important part for your specific TOP problem is that sqlalchemy-iris 0.18.1 is behind the current dialect. Current caretdev/sqlalchemy-iris is at 0.20.1/0.20.1b2 source (de65a0b1...), and its IRISCompiler has explicit pagination handling:

• for servers without modern pagination, simple row limits are emitted by get_select_precolumns() as TOP <n>;
• _use_top() decides when that form is valid;
• for dialect/server combinations with modern pagination, limit_clause() emits LIMIT ... OFFSET ... instead.

That means I would not carry 0.18.1 unchanged into a Superset 6 migration and then compensate in Superset. First test the current sqlalchemy-iris release against the actual IRIS server version, because pagination belongs to that compiler and has changed since the version in the question.

A useful isolation test before involving Superset is to compile representative SQLAlchemy statements with the IRIS dialect (plain .limit(n), limit+offset, ordered query, subquery) and verify the emitted SQL against your IRIS version. If those compile incorrectly, that is a sqlalchemy-iris issue. If they compile correctly outside Superset but Superset still produces invalid SQL, then a minimal Superset query/log showing the extra transformation would identify the integration gap.

I also would not switch to JDBC/ODBC merely to work around TOP: Superset's normal database integration is SQLAlchemy/DB-API, and an alternative transport does not by itself solve SQLAlchemy compilation semantics.

So the safest upgrade path I can substantiate is: Superset 6.x + a current sqlalchemy-iris/IRIS DB-API combination, tested first at the dialect layer. I can't claim a specific pair is certified by Superset, because IRIS currently has no bundled Superset engine spec or version matrix.