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?

[Louis_Frolio]

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

Cheers, BigRoux.

[MaartenH]

Hi @Louis_Frolio ,

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

Thx!

[Louis_Frolio]

I have not.

[MaartenH]

@Louis_Frolio 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?

[Data_Maverick]

I am having the similar issue where source DWH database name is having spaces and I get an error when querying via foreign catalog in databricks. It seems that the Lakehouse Federation not supporting spaces in database name .

Any recent updates on this??

[QueryingQuail]

Hello all,

We have a good amount of tables from an external ERP system that are being replicated to an existing dwh in an Azure SQL Server database.

We have set up a foreign connection for this database and we can connect to the server and database. Sadly, since almost all tables have a name like "dbo_Work Item$8f4cc4bc-7cd3-40df-8b85-d67139d9b4fd", these tables are not shown in Unity Catalog.

If I use a jdbc driver from e.g., DBeaver and connect using the same sql server user (login), I have no issue correctly seeing these tables. I assume that the tables are also correctly read by Databricks internally, but that the maybe the limitations in Unity Catalog (no spaces in object names: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-names) breaks the usability of "foreign" functionality.

Can someone from Databricks please reach out here and give some indication of:

- Whether this is the intended functionality (I think this should, at a minimum, be indicated somewhere like here: https://learn.microsoft.com/en-us/azure/databricks/query-federation/sql-server)?

- Whether this is being worked on or if it will not be fixed.

- If there are known workarounds.

Foreign connections are great for performing migrations to Databricks, but at the current point in time, the lacking functionality is also a big limitation.

[RamGoli]

space in object name in UC is not supported. https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-names

are you able to create view and federate that?

example:  CREATE VIEW JetAccounts AS SELECT * FROM [Jet Accounts]
other workaround, use spark.read.format(“jdbc”).option(…)