Lakehouse federation for SQL server: database name with spaces

[MaartenH]

We're currently using lakehouse federation for various sources (Snowflake, SQL Server); usually succesful. However we've encountered a case where one of the databases on the SQL Server has spaces in its name, e.g. 'My Database Name'. We've tried various escaping attempts:

- My Database Name
- 'My Database Name'
- "My Database Name"
- [My Database Name]
- '[My Database Name]'
- "[My Database Name]"
- `My Database Name`
- My\ Database\ Name

All efforts give an error, mostly [DATA_SOURCE_OPTION_CONTAINS_INVALID_CHARACTERS].

Connectivity has been validated from the cluster used. Database can be connected to through other means, including the MSSQL JDBC driver via a Databricks notebook.

I cannot find anything about whether spaces in database names are supported for SQL Server federation. Anyone with experience on this end?

[BigRoux]

Let me dig in and see if I can find anything for you on this.

Cheers, BigRoux.

[MaartenH]

Hi @BigRoux ,

Did you manage to find any additional information on the subject?

Thx!

[BigRoux]

I have not.

[MaartenH]

@BigRoux Did you managed to find anything in your excavation efforts?

[SP_6721]

Hi @MaartenH 

Can you try creating the foreign catalog like this?

CREATE FOREIGN CATALOG your_catalog_name USING CONNECTION your_connection_name
OPTIONS (
   database '[My Database Name]'
);

(Do check that the Foreign catalog name must follow Unity Catalog rules: no spaces/special characters)

[MaartenH]

Forgot to mention that we tried that as well. It creates the catalog (which you can do through the UI as well) but gives the same error when trying to query the catalog.

[Nivethan]

Hi @MaartenH,

In Catalog and Schema names having space is not allowed by default while creation, even if notated with backsticks(``). Current best possibility is to rename the schema with only allowed values which you can find here for best practices of using names in catalog and schema creation: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-names

Please let us know if any additional guidance is required.


Best Regards,
Nivethan V

[MaartenH]

@NivethanI believe that is mostly in respect to the naming convention for Unity Catalog items themselves. The  names we use for UC items are entirely composed of lowercase letters and underscores in our setup. The database name with spaces in this case, is a source system. We have no control over the naming scheme of that database and it shouldn't matter as a regular JDBC connection works fine within Databricks. So I think you're mixing up two different things in your answer.

[SAKBAR]

I am having the similar issue where table name is having spaces and I cannot see those tables in the foreign catalog.schema in databricks. It seems that the Lakehouse Federation not supporting spaces in database name and table name, however col name with spaces are coming in smoothly.

Any solution to this issue?