question

SunShine-2583 avatar image
0 Votes"
SunShine-2583 asked SunShine-2583 commented

Create table issue in Azure Databricks

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered SunShine-2583 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Vaibhav:

Thanks for your reply.

It is working without REPLACE, I want to know why it is not working with REPLACE AND IF EXISTS ?????

Error says "EPLACE TABLE AS SELECT is only supported with v2 tables.;" what does that mean, ?? which version is ??

Thanks

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @SunShine-2583,

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.



image.png (33.7 KiB)
image.png (68.2 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Pradeep:

Thank you for your reply.

I have attached screenshot and my DBR is 7.6 & Spark is 3.0.1, is that an issue?

Thank you for your help & time.80238-dbc-1.png


0 Votes 0 ·
dbc-1.png (32.4 KiB)

Hello @SunShine-2583,

Thanks for bringing this to our attention. It looks like a issue with the Databricks runtime. Could you please try using Databricks Runtime 8.0 version?

When I tried with Databricks Runtime version 7.6, got the same error message as above:

  Error in SQL statement: AnalysisException: REPLACE TABLE AS SELECT is only supported with v2 tables.;

80464-image.png

0 Votes 0 ·
image.png (198.1 KiB)

Hello @SunShine-2583,
Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

2 Votes 2 ·
SunShine-2583 avatar image
0 Votes"
SunShine-2583 answered SunShine-2583 commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @SunShine-2583,

While using CREATE OR REPLACE TABLE, it is not necessary to use IF NOT EXISTS.

Note: Only one of the ("OR REPLACE", "IF NOT EXISTS") should be used.

You need to use CREATE OR REPLACE TABLE database.tablename

81471-image.png

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.





1 Vote 1 ·
image.png (71.3 KiB)

Thank for clarification, its bit confusing. Thank you again.

0 Votes 0 ·

Hello @SunShine-2583,
Glad to know that it helped.
If the above answers were helpful, click “Accept Answer” or “Up-Vote”, which might be beneficial to other community members reading this thread.

0 Votes 0 ·
SunShine-2583 avatar image SunShine-2583 PRADEEPCHEEKATLA-MSFT ·

I am not seeing "Accept Answer" fro your replies?

0 Votes 0 ·