https://community.databricks.com/t5/data-engineering/bug-in-unity-catalog-registering-external-database-that-is-case/m-p/62246#M31933 <P>Hi Kaniz, thank you for your feedback.<BR />Databases that I have to link cannot be changed, other teams and products rely on those databases as well, this change would be disruptive.</P><P>I think this is a limitation in the unity catalog behaviour, as it should take in account that external databases could have a case sensitive collation, either:<BR /><BR />- keeping the name of the tables as they are, leaving up to the user to deal with case issues for external tables.<BR />- remapping that internally and hide it to the user<BR />- providing some API where to hook on in the database registration, or perform it programmatically.<BR /><BR />So I'll open up a ticket support for it.<BR /><BR />Thank you<BR /><BR /></P> Wed, 28 Feb 2024 15:59:02 GMT AurelioGesino 2024-02-28T15:59:02Z https://community.databricks.com/t5/data-engineering/bug-in-unity-catalog-registering-external-database-that-is-case/m-p/61987#M31866 <P>I successfully registered in my Unity Catalog an external Database ```dwcore``` that is hosted on SQL server.<BR />I first added the connection in "External Data": tested the connection and it was successful.<BR />I then added the database on top: tested the connection and it was successful.</P><P>Then on the unity catalog explorer I checked I saw that the tables can be seen under the "dbo" schema.<BR />I can see all table listed on the tree on the left.<BR />But then, if I try to query any table, the table is not found by Spark.<BR /><BR />I tested with another postgres database and everything works like a charm.<BR />I think the issue is this: my SQL server table names are case sensitive.<BR /><BR />So for example I have a table "AllAlarms" that in the external catalog gets registered all lowercase.<BR />It becomes "allalarms".<BR />So when I try to query the table in the unity catalog the real SQL table does not get resolved, because I'm querying with a lowercase name when it should have been registered registering its real case.<BR />I tried as well to set Spark to be&nbsp;</P><PRE>spark.sql.caseSensitive', True</PRE><P>but that does not help, because the error occurs when the table is registered in the catalog.</P><P>So I think this is a bug with Databricks.<BR /><BR /><BR /><BR /></P> Mon, 26 Feb 2024 15:00:23 GMT https://community.databricks.com/t5/data-engineering/bug-in-unity-catalog-registering-external-database-that-is-case/m-p/61987#M31866 AurelioGesino 2024-02-26T15:00:23Z https://community.databricks.com/t5/data-engineering/bug-in-unity-catalog-registering-external-database-that-is-case/m-p/62246#M31933 <P>Hi Kaniz, thank you for your feedback.<BR />Databases that I have to link cannot be changed, other teams and products rely on those databases as well, this change would be disruptive.</P><P>I think this is a limitation in the unity catalog behaviour, as it should take in account that external databases could have a case sensitive collation, either:<BR /><BR />- keeping the name of the tables as they are, leaving up to the user to deal with case issues for external tables.<BR />- remapping that internally and hide it to the user<BR />- providing some API where to hook on in the database registration, or perform it programmatically.<BR /><BR />So I'll open up a ticket support for it.<BR /><BR />Thank you<BR /><BR /></P> Wed, 28 Feb 2024 15:59:02 GMT https://community.databricks.com/t5/data-engineering/bug-in-unity-catalog-registering-external-database-that-is-case/m-p/62246#M31933 AurelioGesino 2024-02-28T15:59:02Z