Connection without specifying catalog name in connection string causes an error

[RiyadBen]

Hello,
I'm trying to create an engine connection which could manage multiple catalogs at once

connection_uri = "databricks://token:XXXXXX@DB_HOST?http_path=/sql/1.0/warehouses/DWH_ID"
engine = create_engine(connection_uri, future=True)
meta_inspector = inspect(self.engine)

Later when calling for example
tables = meta_inspector.get_table_names('test_schema')
I get the following error

backend-1  | sqlalchemy.exc.DatabaseError: (databricks.sql.exc.ServerOperationError) Fail to execute the command as the target schema `None.test_schema` is not in the current catalog. Please set the current catalog with 'USE CATALOG None' first.
backend-1  | [SQL: SHOW VIEWS FROM `None`.`test_schema`]

After further inspection I found the current method being called:

databricks-sql-python/src/databricks/sqlalchemy/base.py

Line 278 in 62eb1d4

_target_catalog = self.catalog

It could be fixed with adding on Line 278 the following:
_target_catalog = kwags.get("catalog") or self.catalog
and referencing the catalog name in the method get_table_names
tables = meta_inspector.get_table_names(schema='test_schema',catalog='test_catalog')

NOTE : I'm avoiding creating a different engine for every catalog due to slow response from Databricks
I'm hesitant in opening a PR for this as it's not well tested and I'm not sure if there is an alternative to what i'm trying to achieve , so any feedback would be great !

[susodapop]

This is an interesting request and makes sense to me.

This suggestion:

_target_catalog = kwargs.get("catalog") or self.catalog

could work, and seems consistent with the interface that SQLAlchemy defines for get_table_names. Since we run the dialect compliance test suite, so long as the ComponentReflectionTest suite still passes with this change then it should be good for our use case. I'm going to consult internally to ensure this is the path we want to take.

Question for you: are their other inspector / reflection methods that we'd like to support this catalog flag?

[RiyadBen]

Hello @susodapop ,
Thank you for your reply
So far the only method that i've come across that needs catalog flag without major changes is get_view_names

[HanSeoulOh]

I didn't seem to get a bug or error when I didn't specify a catalog or schema, however it would be nice to be able to select an arbitrary catalog/schema from one engine instead of managing multiple. Is there a way to do this currently?