Error creating delta table over an existing delta schema

study_community — Sun, 23 Jan 2022 07:51:16 GMT

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 ?

Re: Error creating delta table over an existing delta schema

Anonymous — Mon, 24 Jan 2022 15:52:39 GMT

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.

Re: Error creating delta table over an existing delta schema

-werners- — Tue, 25 Jan 2022 14:28:51 GMT

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.

topic Re: Error creating delta table over an existing delta schema in Data Engineering

Error creating delta table over an existing delta schema

Re: Error creating delta table over an existing delta schema

Re: Error creating delta table over an existing delta schema