topic Re: Creating a delta table Mismatch Input Error in Data Engineering

Creating a delta table Mismatch Input Error

Brose — Wed, 01 Dec 2021 03:09:29 GMT

I am trying to create a delta table for streaming data, but I am getting the following error; Error in SQL statement: ParseException:

mismatched input 'CREATE' expecting {<EOF>, ';'}(line 2, pos 0).

My statement is as follows;

%sql

DROP TABLE IF EXISTS table1

CREATE TABLE table1 (

col_1 STRING,

col_2 STRING,

col_date_1 DATE,

col_dat_2 DATE,

col_3 Double,

col_4 Double,

) USING DELTA

I can't figure out why I am getting this error.

Re: Creating a delta table Mismatch Input Error

-werners- — Wed, 01 Dec 2021 10:17:39 GMT

can you try putting a semicolon (;) at the end of the drop table statement?

Re: Creating a delta table Mismatch Input Error

Brose — Wed, 01 Dec 2021 16:24:37 GMT

I did that but still getting the same error.

Thanks @werners

Re: Creating a delta table Mismatch Input Error

-werners- — Wed, 01 Dec 2021 16:44:22 GMT

can you try with CREATE OR REPLACE table1 (instead of drop and create)?

Re: Creating a delta table Mismatch Input Error

Brose — Wed, 01 Dec 2021 16:50:50 GMT

Thanks again @werners, I tried that but still getting the same error

Re: Creating a delta table Mismatch Input Error

-werners- — Fri, 03 Dec 2021 08:44:42 GMT

strange,

can you try to execute both statements in separated cells?

Like that you can see where the problem exactly is

Re: Creating a delta table Mismatch Input Error

jose_gonzalez — Tue, 07 Dec 2021 00:45:28 GMT

hi @Ambrose Walker ,

Here is the link to the docs https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table-datasource.html#create-table-using

Here is the syntax:

CREATE TABLE [ IF NOT EXISTS ] table_identifier

[ ( col_name1 col_type1 [ COMMENT col_comment1 ], ... ) ]

USING data_source

[ OPTIONS ( key1 [ = ] val1, key2 [ = ] val2, ... ) ]

[ PARTITIONED BY ( col_name1, col_name2, ... ) ]

[ CLUSTERED BY ( col_name3, col_name4, ... )

[ SORTED BY ( col_name [ ASC | DESC ], ... ) ]

INTO num_buckets BUCKETS ]

[ LOCATION path ]

[ COMMENT table_comment ]

[ TBLPROPERTIES ( key1 [ = ] val1, key2 [ = ] val2, ... ) ]

[ AS select_statement ]

Re: Creating a delta table Mismatch Input Error

Brose — Tue, 07 Dec 2021 01:13:13 GMT

Thank you @Werner Stinckens I was able to resolve this issue. Your assistance is much appreciated

Re: Creating a delta table Mismatch Input Error

Brose — Tue, 07 Dec 2021 01:13:51 GMT

Thanks Jose.

Re: Creating a delta table Mismatch Input Error

Anonymous — Tue, 07 Dec 2021 21:26:39 GMT

@Ambrose Walker - If Jose's answer resolved your issue, would you be happy to mark that post as best? That will help others find the solution more quickly.