topic Re: REPLACE TABLE AS SELECT is not working with parquet whereas it works fine for delta in Data Engineering

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

Dhruv-22 — Thu, 08 Feb 2024 09:28:13 GMT

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.

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

Dhruv-22 — Thu, 08 Feb 2024 11:45:34 GMT

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?

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

Ayushi_Suthar — Thu, 08 Feb 2024 14:23:19 GMT

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#:~:text=Parameters-,REPLACE,-If%20specified%20replaces

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

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

Dhruv-22 — Thu, 08 Feb 2024 16:42:08 GMT

Thanks for the answer