FileAlreadyExistsException error while analyzing table in Notebook

[Miasu]

Databricks experts, 

I'm new to Databricks, and encounter an issue with the ANALYZE TABLE command in the Notebook. 

I created two tables nyc_taxi and nyc_taxi2, from one csv file.

When executing the following command in Notebook, 

analyze table nyc_taxi2 compute statistics for columns passenger_count;

A FileAlreadyExistsException error was raised, [FileAlreadyExistsException: Operation failed: "The specified path, or an element of the path, exists and its resource type is invalid for this operation.", 409, GET,......, PathConflict, "The specified path, or an element of the path, exists and its resource type is invalid for this operation.]

However, the same command for the nyc_taxi table worked well, with no error raised. I also tried the SQL Editor, it worked fine with the code. Only having issues with running the command in Notebook. I'm very confused as to why this happened. 

The only difference between nyc_taxi and nyc_taxi2 is that I created nyc_taxi using the UI, and created the nyc_taxi2 using the Databricks SQL commands, given below. 

CREATE TABLE nyc_taxi2

(vendor_id String,

pickup_datetime timestamp,

dropoff_datetime timestamp,

passenger_count int,

trip_distance double,

pickup_longitude double,

pickup_latitude double,

rate_code int,

store_and_fwd_flag string,

dropoff_longitude double,

dropoff_latitude double,

payment_type string,

fare_amount double,

surcharge double,

mta_tax double,

tip_amount double,

tolls_amount double,

total_amount double)

USING CSV

OPTIONS("path"="/users/myfolder/nyc_taxi.csv","header" = "true");

Can anyone direct what could be the reason for this problem? 
Thanks for the help!

[Kaniz_Fatma]

Hi @Miasu, To investigate and resolve the issue at hand, there are several steps that can be taken. Firstly, it is important to check for any existing resources that may already have the same name as "nyc_taxi2" in the given path, which is "/users/myfolder/nyc_taxi.csv". The presence of conflicting files, directories, or tables could lead to issues. 

Using Databricks File System (dbfs) commands, you can thoroughly inspect the directory and ensure that there are no conflicting resources. In the interim, as a temporary workaround, you can use the following command to delete the existing table before creating a new one: "dbutils.fs.rm("dbfs:/user/hive/warehouse/nyc_taxi2", true)". Please make sure to replace the placeholder path, "dbfs:/user/hive/warehouse/nyc_taxi2", with the actual path where the table is stored.

Let's explore some steps that can help resolve the issue at hand: 

One possible factor contributing to discrepancies between nyc_taxi and nyc_taxi2 is the differing methods used to create these tables. While nyc_taxi was created using the UI, nyc_taxi2 was created through Databricks SQL commands. It's important to review the table creation process thoroughly to ensure that no discrepancies exist that could potentially lead to this issue. Pay special attention to the schema, data types, and options employed during the creation of both tables. 

Additionally, it's crucial to confirm that you possess the necessary permissions to create and manage tables within the designated location. Take a moment to check for any potential issues related to ownership or permissions in the directory where the table data is stored. These factors could potentially contribute to the issue at hand and should be carefully examined.

To thoroughly investigate and resolve the issue, keep in mind these essential steps and considerations: - If you're using Delta Lake for ACID transactions, there may be additional factors to consider. Make sure the separate directories for metadata and transaction logs do not have any conflicts. - In case of any inconsistencies in metadata, it can also be a root cause for issues. Try refreshing the table's metadata with the command: REFRESH TABLE nyc_taxi2 - To gain more detailed information about the error, enable logging and review the Databricks logs. - Don't forget to also check the Databricks job logs for any potential clues that may help in troubleshooting the issue.

Always keep in mind that Databricks offers a robust platform, but even minor details can affect its performance in unforeseen ways. By diligently following these steps, you can successfully identify and resolve any issues related to using the ANALYZE TABLE command for nyc_taxi2.

[Miasu]

Hi @Kaniz_Fatma , thank you for your reply! 

I realized that another main difference between nyc_taxi and nyc_taxi2 is that nyc_taxi created using the UI, is a managed table, whereas nyc_taxi2 created using the SQL command is an external table. The locations are also different, nyc_taxi is stored under "dbfs:/user/hive/warehouse/myschema.db/nyc_taxi"; nyc_taxi2 is stored under "

dbfs:/users/feirxu/nyc_taxi.csv".  Could this be the reason for the error? If so, could you advise how to resolve this issue? 

I checked the documentation, and it says "With the UI, you can only create external tables. (https://learn.microsoft.com/en-us/azure/databricks/archive/legacy/data-tab#--create-a-table)", why the table I created using the UI is a managed table, while the table created using the SQL commands is an external table?

Did I miss any part?  

Thank you!