Error creating delta table over an existing delta schema

[study_community]

I created a delta table through a cluster over a dbfs location .

Schema :

create external table tmp_db.delta_data(

delta_id int ,

delta_name varchar(20) ,

delta_variation decimal(10,4) ,

delta_incoming_timestamp timestamp,

delta_date date generated always as (cast (split(cast(delta_incoming_timestamp as string),' ')[0] as date)) comment 'Generated', -- cound use date(col)

delta_time string generated always as (cast(split(cast(split(cast(delta_incoming_timestamp as string),' ')[1] as string),'\\.')[0] as string)) comment 'Generated',

delta_milliseconds string generated always as (cast(split(cast(split(cast(delta_incoming_timestamp as string),' ')[1] as string),'\\.')[1] as string)) comment 'Generated'

) 

using delta 

partitioned by (delta_date)

location 'dbfs:/tmp/tables_base/tmp_db/delta_data';

It got created for the first time. I inserted few rows into the table.

I dropped the table as it is a external table and created the table in another databricks cluster. I'm getting the below error.

```

== Specified ==

root

-- delta_id: integer (nullable = true)

-- delta_name: varchar(20) (nullable = true)

-- delta_variation: decimal(10,4) (nullable = true)

-- delta_incoming_timestamp: timestamp (nullable = true)

-- delta_date: date (nullable = true)

-- delta_time: string (nullable = true)

-- delta_milliseconds: string (nullable = true)

== Existing ==

root

-- delta_id: integer (nullable = true)

-- delta_name: string (nullable = true)

-- delta_variation: decimal(10,4) (nullable = true)

-- delta_incoming_timestamp: timestamp (nullable = true)

-- delta_date: date (nullable = true)

-- delta_time: string (nullable = true)

-- delta_milliseconds: string (nullable = true)

== Differences==

- Specified metadata for field delta_name is different from existing schema:

Specified: {}

Existing: {"__CHAR_VARCHAR_TYPE_STRING":"varchar(20)"}

- Specified type for delta_name is different from existing schema:

Specified: varchar(20)

Existing: string

```

I don't understand why this metadata got added to a varchar column and why the varchar column got saved as string. And If it is a default behavior , How can I specify the config {"__CHAR_VARCHAR_TYPE_STRING":"varchar(20)"} for that particular column ?

[Anonymous]

Hello again, @Thulasitharan Govindaraj​ - Thank you for your question and your patience. As usual, we'll give our members a chance to respond and we'll come back to this if we need to.

[-werners- · Accepted solution]

varchartype is only available as from spark 3.1 I think.

https://spark.apache.org/docs/latest/sql-ref-datatypes.html

The link is for spark 3.2, and 3.1 also has varchartype. So can you check your spark version?

Also if the table definition still exists you need to either use schema drift or else remove the table completely, or explicitely change the col types.