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

["Ozoubia (via GitHub)" <gi...@apache.org>]

GitHub user Ozoubia created a discussion: Recommended IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris)

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](https://github.com/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!

GitHub link: https://github.com/apache/superset/discussions/44346

----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

Re: [D] Recommended IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris) [superset]

["TayfurYldz (via GitHub)" <gi...@apache.org>]

GitHub user TayfurYldz added a comment to the discussion: Recommended IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris)

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.

GitHub link: https://github.com/apache/superset/discussions/44346#discussioncomment-18504938

----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

Re: [D] Recommended IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris) [superset]

["FrancescoCastaldi (via GitHub)" <gi...@apache.org>]

GitHub user FrancescoCastaldi added a comment to the discussion: Recommended IRIS connector for Superset 6.x? (TOP / row limit issues with sqlalchemy-iris)

When migrating to Superset 6.x with InterSystems IRIS, the `SELECT TOP n` syntax error occurs because older dialect versions did not translate standard SQLAlchemy 1.4/2.0 limit clauses appropriately for Superset's query execution engine.

### Recommended Version Matrix
For Superset 6.x, update your packages to:
- `sqlalchemy-iris>=0.20.1`
- `intersystems-irispython>=5.4.0`

In `sqlalchemy-iris` 0.20.1, the compiler introduces updated handling in `get_select_precolumns()` and `limit_clause()`. If your IRIS database server supports modern SQL pagination, it uses `LIMIT ... OFFSET ...`; otherwise, it prepends `TOP <n>` to the column list.

### Superset Configuration (Custom Engine Spec)
Because Superset does not bundle a dedicated `IrisEngineSpec` in core, SQL Lab wraps queries in subqueries for row limiting unless configured. You can define a lightweight engine spec in `superset_config.py` to instruct Superset how to handle limits:

```python
# superset_config.py
from superset.db_engine_specs.base import BaseEngineSpec

class IrisEngineSpec(BaseEngineSpec):
    engine = "iris"
    engine_name = "InterSystems IRIS"
    allows_subqueries = True
    supports_simple_limit = False

    @classmethod
    def apply_limit_to_sql(cls, sql: str, limit: int, database) -> str:
        # Prevent appending LIMIT if IRIS compiler expects TOP
        if "select top" in sql.lower():
            return sql
        return super().apply_limit_to_sql(sql, limit, database)
```

In the Superset UI (Databases -> Edit -> Advanced -> Other -> Engine Parameters), pass:
```json
{
  "connect_args": {
    "auto_commit": true
  }
}
```
This configuration provides stable pagination across SQL Lab and Explore in Superset 6.x.


GitHub link: https://github.com/apache/superset/discussions/44346#discussioncomment-19164452

----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org