Support for connectors with three part identifiers

[Praveen2112]

Some data-sources has support for 3-part identifiers (database.schema.table) for example SqlServer, currently we have 1:1 mapping with a Trino catalog to a specific database. So for each database we need a unique catalog - this comes with a maintenance cost of having too many catalogs, as we add more databases we might need to restart the server and pushdown (like join) might be a bit tricky to implement. The following lists the solutions to tackle this issue -

• Polymorphic table functions

We could have a PTF which could capture the three part identifier for a given table and resolve to a corresponding ConnectorTableHandle. One issue with this approach is that - it is not BI tool friendly like listing down all tables from all the databases.

• Virtual catalog

What if we could have multiple catalogs which resolves to a same connector i.e the table name would be in this format sqlserver_database.schema.table - the additional part (i.e database) can be captured as part of SPI (It would be backward compatible) objects - this allows to list tables across all the databases.

Will be updating other possible solutions.

cc: @trinodb/maintainers

[arhimondr]

Should we simply have 2 levels? (database and schema) for each connector?

[findepi]

Should we simply have 2 levels? (database and schema) for each connector?

That would imply 3 levels (and 4-part identifiers for actual tables).
BI tools working with Trino wouldn't be able to ask for metadata because it would be non-standard (think java.sql.DatabaseMetaData), so we would need to do something to squash n-levels back into n-1... So we should just do this within each connector that has such challenge.

[Praveen2112]

Or maybe the engine could handle the squashing and it could be transparent to the connector. The connector could inform the engine that it could accept another level and the engine would generate a catalog name corresponding to it

[ebyhr]

There's relevant issue #1135