Forum Discussion
Advocate ILakehouse tables still visible in SQL endpoint view - how do I remove them?
Hi nioj2S,
Kindly contact your tenant administrator or Fabric administrator for further assistance in resolving the issue.
If the issue persists, we sincerely recommend reaching out to the Microsoft Support Team through the link provided below. Submitting a support ticket will allow you to receive personalized insights related to your account and potential resolutions:
Microsoft Fabric Support and Status | Microsoft FabricIf you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.
I was in touch with Microsoft Support and the issue was that the original dataflow had managed to create a schema in the lakehouse, which was a lakehouse that did not support schemas. The steps they came up with to resolve the issue was:
1 - Create a Lakehouse with schema support.
2 - Using a Fabric notebook:
2.1 - Create the FIRMA1 schema in the new Lakehouse.
2.2 - Create a table called bl_project in the new schema with only 1 column.
2.3 - Copy the contents of the folder into the Lakehouse with the orphan tables.
3 - Wait for metadata synchronization to occur and check whether the orphan table returns the expected test column.
4 - Using a Fabric notebook:
4.1 - In the Lakehouse with the orphan tables, delete the bl_project folder.
5 - After metadata synchronization, check whether the bl_project table was removed.
6 - Repeat this process for all orphan tables.
7 - At the end, delete the FIRMA1 schema from the SQL endpoint.
Also, there was some issue with casing, so we had to use a piece of code to make sure that Spark ignored/didn't ignore capital ("BL_PROJECT" vs "bl_project").
This solved the issue. 🙂
Community SupportHi nioj2S,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
The Lakehouse and the SQL Endpoint in Microsoft Fabric represent different ways of interacting with the same underlying data. Below is an explanation of their functionalities and steps to resolve your issue:
- Lakehouse View:
- This provides a file-system-oriented perspective where data is organized into folders and files, such as Parquet and Delta Lake formats.
- Actions performed in the Lakehouse View primarily affect the underlying data storage.
- SQL Endpoint:
- This offers a SQL-based interface (using T-SQL) to query and manage data in the Lakehouse.
- The SQL Endpoint maintains its own metadata catalog, which includes schemas, tables, and views, independent of the file system structure.
When you delete a table through the Lakehouse View, only the associated data files are removed. However, the schema associated with the table remains intact in the SQL Endpoint's metadata. This behavior is intentional, as it allows users to recreate tables or reuse schemas for other purposes.
- Schemas in SQL are logical containers for database objects, such as tables, views, and stored procedures.
- Even if a schema is empty (i.e., contains no objects), it still exists as a logical entity in the SQL Endpoint's metadata.
- The Lakehouse View focuses on data files and does not provide an option to delete schemas, as it does not manage SQL metadata.
To remove the schema, you need to use T-SQL within the SQL Endpoint. Please follow the steps below:
- Open a query editor connected to your Lakehouse's SQL Endpoint.
- Execute the following T-SQL command to delete the schema:
DROP SCHEMA SchemaName;
Replace SchemaName with the actual name of the schema you wish to delete. - If the schema contains any other objects (ex: views or stored procedures), the DROP SCHEMA command will fail. You must delete those objects first using appropriate T-SQL commands.
If you find the response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.
Hi Friends,
Any luck on this?
Thanks
Ramana