Create table issue in Azure Databricks

Jayesh Dave 296 Reputation points
2021-03-20T20:23:58.257+00:00

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)

Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,072 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Vaibhav Chaudhari 38,681 Reputation points
    2021-03-21T04:14:48.343+00:00

    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


  2. PRADEEPCHEEKATLA-MSFT 85,511 Reputation points Microsoft Employee
    2021-03-22T07:16:28.377+00:00

    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:

    80030-image.png

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

    80111-image.png

    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.


  3. Jayesh Dave 296 Reputation points
    2021-03-25T05:02:15.857+00:00

    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
    ---------------------------^^^