Create table issue in Azure Databricks

Question

Hello:

I need help to see where I am doing wrong in creation of table & am getting couple of errors. Any help is greatly appreciated.

CODE:-

%sql

CREATE OR REPLACE TEMPORARY VIEW Table1
USING CSV
OPTIONS (
-- Location of csv file
path "/mnt/XYZ/SAMPLE.csv",
-- Header in the file
header "true", inferSchema "true");

%sql
SELECT * FROM Table1

%sql

CREATE OR REPLACE TABLE DBName.Tableinput
COMMENT 'This table uses the CSV format'
AS SELECT * FROM Table1;

Errors:-
Error in SQL statement: AnalysisException: REPLACE TABLE AS SELECT is only supported with v2 tables.;
Error in SQL statement: ParseException: mismatched input 'NOT' expecting {<EOF>, ';'}(line 1, pos 27)

Error in SQL statement: ParseException:
mismatched input '/' expecting {'(', 'CONVERT', 'COPY', 'OPTIMIZE', 'RESTORE', 'ADD', 'ALTER', 'ANALYZE', 'CACHE', 'CLEAR', 'COMMENT', 'COMMIT', 'CREATE', 'DELETE', 'DESC', 'DESCRIBE', 'DFS', 'DROP', 'EXPLAIN', 'EXPORT', 'FROM', 'GRANT', 'IMPORT', 'INSERT', 'LIST', 'LOAD', 'LOCK', 'MAP', 'MERGE', 'MSCK', 'REDUCE', 'REFRESH', 'REPLACE', 'RESET', 'REVOKE', 'ROLLBACK', 'SELECT', 'SET', 'SHOW', 'START', 'TABLE', 'TRUNCATE', 'UNCACHE', 'UNLOCK', 'UPDATE', 'USE', 'VALUES', 'WITH'}(line 2, pos 0)

Answer 1

For the second create table script, try removing REPLACE from the script. It should work

CREATE TABLE DBName.Tableinput
COMMENT 'This table uses the CSV format'
AS SELECT * FROM Table1;

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Hello @Jayesh Dave ,

Make sure you are are using Spark 3.0 and above to work with command. - REPLACE TABLE AS SELECT.

Note: REPLACE TABLE AS SELECT is only supported with v2 tables.

Apache Spark’s DataSourceV2 API for data source and catalog implementations. Spark DSv2 is an evolving API with different levels of support in Spark versions:

As per my repro, it works well with Databricks Runtime 8.0 version.

For more details, refer:

https://iceberg.apache.org/spark/

https://databricks.com/session/improving-apache-sparks-reliability-with-datasourcev2

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

------------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Answer 3

It is working with CREATE OR REPLACE TABLE .

if you run with CREATE OR REPLACE TABLE IF NOT EXISTS databasename.Table =name it is not working and giving error.

Error:-

com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException:
mismatched input 'NOT' expecting {<EOF>, ';'}(line 1, pos 27)

== SQL ==
CREATE OR REPLACE TABLE IF NOT EXISTS databasename.Tablename
---------------------------^^^