Drop table that don't have dbo schema

[jovianaditya]

So I'm altering the schema in SQL analytics endpoint. From dbo into another schema, just called it "test", and the table's name is workshop.

How can I drop my test.workshop table?

Already try to drop the table from notebook using code like this :

%%sql

DROP TABLE IF EXISTS ADLSGen2Prd.test.workshop

(ADLSGen2Prd is my lakehouse name)

but it show me the error message
Error: [REQUIRES_SINGLE_PART_NAMESPACE] spark_catalog requires a single-part namespace, but got `ADLSGen2Prd`.`test`. org.apache.spark.sql.errors.QueryCompilationErrors$.requiresSinglePartNamespaceError(QueryCompilationErrors.scala:1263)

Is there any solution?

[v-gchenna-msft]

Hi  @jovianaditya ,

In order to drop a table in the Lakehouse you have to use a Notebook to do this. The SQL Endpoint for a lakehouse is ready only.

Here it the code to do this in the Notebook using Spark SQL:

# Syntax of DROP
DROP TABLE tableName

Hope it is helpful. Please let me know incase of further queries.

[jovianaditya]

Yes like I said in the post, I've already tried to drop the table using notebook. But the problem is my table schema is not dbo. So when I'm using the code like u gave me it will cause an error. Is there any solution?

[v-gchenna-msft]

Hi @jovianaditya ,

I would like to understand how were you able create a new schema at initial step? 
As I cannot find any way to create a new Schema in Lakehouse.

[jovianaditya]

You can create a new schema via SQL Endpoint.

Just use CREATE SCHEMA youschemaname

and ALTER SCHEMA yourschemaname TRANSFER dbo.yourtablename

That's how I change my table schema from dbo to others.

[AndyDDC]

Hi @jovianaditya it's not possible to refer to lakehouses using 3 part naming in the Lakehouse (yes you can do it in the SQL Endpoint but that's SQL, not Spark).  You'll need to remove the schema part in your Notebook.  EG just use LakehouseName.TableName. 

Try dropping the table again by just using the LakehouseName.TableName in your notebook

[jovianaditya]

I also already tried that method. Using my notebook : LakehouseName.TableName to drop my table. But I think when I'm doing that, the notebook will refer to the dbo schema table, so nothing will happen and my test.workshop table will still be there. Maybe it's a bug that cannot drop the table that don't use dbo schema? Because when using the notebook, it will refer to only dbo schema and the notebook still don't have the capability to define schema.

[AndyDDC]

There is no concept of the dbo schema in the Lakehouse side of things as that's Spark, schemas are in the SQL Endpoint yes, but the lakehouse will ignore them.  I tried your scenario and after altering a custom schema and moving a table into it via the sql endpoint, I was able to drop the table in a notebook using lakhousename.tablename without specifying a schema.

[jovianaditya]

Still not works for me. I created a table from a parquet file named workshop using notebook. So that table automatically gets a dbo schema. In SQL endpoint I'm altering that table from dbo schema into "test" schema. When I'm trying to drop the table from notebook with this line of code :

%%sql

DROP TABLE test.workshop

it not works and give me this error messages:
Error: [TABLE_OR_VIEW_NOT_FOUND] The table or view `spark_catalog`.`test`.`workshop` cannot be found. Verify the spelling and correctness of the schema and catalog. If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog. To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS..

[v-gchenna-msft]

Hi @jovianaditya ,

Can you re-transfer your schema changes by executing like this.

Eg:
Query 1 (Executed) -
ALTER SCHEMA newschemaname TRANSFER dbo.yourtablename

Query 2 (Re transfer to older schema)
ALTER SCHEMA dbo TRANSFER newschemaname.yourtablename


now you can drop the table from dbo schema. Can you give a try?

Please share some screenshots of the errors if you are facing any issue.

[jovianaditya]

Your method actually works, I can transfer my workshop table back into the dbo schema. But when I'm open my notebook there is no workshop table in my lakehouse. The workshop table can only be found in SQL Endpoint. Is it a bug?

Because when Im trying to query SELECT TOP 100 from that table in SQL Endpoint it show me error like this:

And when I'm trying to drop that table from my notebook it also give me the same error like before (table not found)

This is my workshop table in SQL endpoint:

And this is the list of table in my lakehouse:

Already try to refresh the page but it still the same.

[v-gchenna-msft · Accepted solution]

Hi @jovianaditya ,

It looks like a bug, I am also facing the same issue at my end.

My observation is when we ran below code we will be land up to this scenario

%%sql

DROP TABLE test.workshop

On execution of above code it is going to drop the table, but not reflecting the changes in UI.
When we try to preview the data using SQL Query we will get Table not found.

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Comming to your issue, you can leave as it is for now if it is not a blocker. 
You can also create new lakehouse if you are still at the beginning.

Incase if this is acting like a blocker to you, I suggest you to raise a support ticket to reach our support team: https://support.fabric.microsoft.com/support

Hope it is helpful. Please let me know incase of further queries.