REPLACE TABLE AS SELECT is not working with parquet whereas it works fine for delta

[Dhruv-22]

I am working on Azure Databricks, with Databricks Runtime version being - 14.3 LTS (includes Apache Spark 3.5.0, Scala 2.12). I am facing the following issue.

Suppose I have a view named v1 and a database f1_processed created from the following command

CREATE DATABASE IF NOT EXISTS f1_processed
LOCATION "abfss://processed@formula1dl679student.dfs.core.windows.net/"

Then if I run the following command it runs fine.

CREATE OR REPLACE TABLE f1_processed.circuits
AS
SELECT * FROM v1;

However, if I specify the format like in the following code

CREATE OR REPLACE TABLE f1_processed.circuits
USING PARQUET
AS
SELECT * FROM v1;

An error is thrown

[UNSUPPORTED_FEATURE.TABLE_OPERATION] The feature is not supported: 
Table `spark_catalog`.`f1_processed`.`circuits` does not support REPLACE TABLE AS SELECT. 
Please check the current catalog and namespace to make sure the qualified table name is expected, 
and also check the catalog implementation which is configured by "spark.sql.catalog". SQLSTATE: 0A000

As seen from the first command, REPLACE TABLE AS SELECT is supported and the error is wrong. Any help is appreciated.

[Kaniz]

Hi @Dhruv-22, Let’s explore potential solutions:

Table Compatibility:

• Ensure that the table f1_processed.circuits exists and is compatible with the PARQUET format.
• Verify that the qualified table name is correctly specified.
• Check the current catalog and namespace configuration.

Catalog Configuration:

• Confirm the catalog implementation configured by the "spark.sql.catalog" property.
• Make sure it aligns with the expected behaviour for REPLACE TABLE AS SELECT operations.

Workaround:

• If the issue persists, consider an alternative approach:
  • Create a new table with the desired schema and format.
  • Use an INSERT INTO statement to populate the new table with data from v1.
  • Drop the original table if necessary.

Remember that Databricks Runtime 14.3 LTS includes Apache Spark™ 3.5.0, so verifying compatibility and configuration settings is essential.

[Dhruv-22]

Hi Kaniz, I wanted to know why this is happening. I know how to avoid this. Could you please help in understanding the error and its cause?

[Ayushi_Suthar · Accepted solution]

Hi @Dhruv-22 

We understand that you are facing the following error when using REPLACE TABLE AS SELECT  on the Parquet Table but at this moment the REPLACE TABLE AS SELECT operation you're trying to perform is not supported for Parquet tables.

According to our documentation, the REPLACE clause is only supported for Delta Lake tables. If you're working with Parquet, you might need to consider converting your tables to Delta format to use this feature. 

Please refer to this document: https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-create-table-using.html#:~:tex...

Please let me know if this helps and leave a like if this helps, followups are appreciated.
Kudos
Ayushi

[Dhruv-22]

Thanks for the answer