Azure Synapse Unable to drop created table

Ong Jing Long 1

I have created a table in the synapse notebook using the following method.

path = "synfs:/{}{}/".format(jobId,mountPoint)
spark.sql(f"CREATE TABLE test_table USING DELTA LOCATION '{path}'")

and a table named test_table is created under this section.

However, once the session closes, and a new session is started, I realised that the path used to create this test_table would not exist since the path is specific to the previous session due to the jobId and mount.
java.util.concurrent.ExecutionException: org.apache.hadoop.fs.PathNotFoundException:

Tried dropping the table using spark.sql("DROP TABLE IF EXISTS test_table ") but that didn't work either.

Are there other ways to drop this table?

Saurabh Sharma 23,791 Reputation points Microsoft Employee

2022-02-25T01:54:39.267+00:00

Hi @Ong Jing Long ,

Thanks for using Microsoft Q&A!!
If I understand correctly, you are trying to drop the table using in a spark session different than session in which the table was created using Synapse Notebook.
I have tried deleting the table using a different session and it worked fine to me. (See GIF below)

Can you please confirm if you are trying to run individual cell which has a Drop Statement spark.sql("DROP TABLE IF EXISTS test_table ") and it is not working?
In case you are running all commands (mount, create table and delete table) in a single cell or running All the whole notebook with individual cells having mount, create table and delete table commands then you will be getting error like below while running all cells using "Run All" as your new session will not have the path, however, you should be able to delete the table.

Please let me know if your case is different or you are getting any other errors while deleting the table.

Thanks
Saurabh
Ong Jing Long 1 Reputation point

2022-02-25T02:39:38.91+00:00

Hi @Saurabh Sharma ,

Thank you for the response. I have tried to run just the individual cell with the drop statement (stopped the current session and started a new one by running that cell according to what you had done in the gif) but I am still getting the error (same error message as what you have shown in your image).

I have also tried running all cells using "Run All" but the same error shows up.

Thank you!
Saurabh Sharma 23,791 Reputation points Microsoft Employee

2022-02-25T19:09:05.3+00:00

Hi @Ong Jing Long ,

That's very strange behavior. I would suggest you create a support ticket for this issue. In case you have a limitation, please let me know and I will help providing a one-time free support ticket.

Thanks
Saurabh
Banta 1 Reputation point

2022-06-03T08:45:27.247+00:00

Hi, I am also facing this same issue, I am trying to delete a table from Lake Database and its giving me that same error and referring the path contain old jobId.

I have created that table in first session, jobId was 1 on that time.
My currently jobId is 7.

I am trying to delete that table now which i have created in first session. It is giving me below error.

Py4JJavaError: An error occurred while calling o210.sql.
: java.util.concurrent.ExecutionException: org.apache.hadoop.fs.PathNotFoundException:
<path with jobId - 1>: No such file or directory

Share via

Azure Synapse Unable to drop created table