Error creating table in Azure databricks Unity Catalog

Question

Hello,

I'm trying to move a copy a table from hive_metastore to unity catalog.

To that end, I'm running the below and running into errors below. I'm new to databricks so please appreciate if anyone who was experience can guide me on understand and resolve this issue?

Options:

#1:

import pyspark.pandas as ps
df = ps.read_table("hive_metastore.default.mytable")
df.to_table("samples.schema.mytable", overwriteSchema=True)

#2:

CREATE TABLE samples.tpch.ai_cat_info_company
AS SELECT * FROM hive_metastore.default.ai_cat_info_company;

#3:

df = spark.table("hive_metastore.default.mytable")

df.write.saveAsTable(
  name = "samples.schema.mytable"
)									

Option #1 & #2 errors with the message below:

AnalysisException: Cannot create table ('samples.default.mytable'). The associated location ('') is not empty and also not a Delta table.

Option #3: errors with the message below:

AnalysisException: [UC_COMMAND_NOT_SUPPORTED] Create sample tables/views is not supported in Unity Catalog.

Couple of things I've checked:

1. I've double checked that there is no table in the unity catalog and no corresponding file in dbfs.
2. I'm able to read the source table in the hive_metastore.

Thanks in advance!

Answer 1

@nihcas s - Thanks for the question and using MS Q&A platform.

Option #1 and #2 This issue generally happens when using "DROP TABLE" and "CREATE TABLE" commands. Databricks recommends to use "CREATE or REPLACE" commands to overwrite a delta table rather than dropping and recreating the table.

Option #3 By default, if you don't specify the catalog it's defaulting to the hive_metastore, and that's why you get the error.

For more details, refer to SO threads which addressing similar issue:
https://stackoverflow.com/questions/69551620/databricks-is-not-empty-but-its-not-a-delta-table
https://stackoverflow.com/questions/75855836/spark-command-not-supported-in-unity-catalog-in-databricks

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.