cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Alter Delta table column datatype

HariharaSam
Contributor

Hi ,

I am having a delta table and table contains data and I need to alter the datatype for a particular column.

For example :

Consider the table name is A and column name is Amount with datatype Decimal(9,4).

I need alter the Amount column datatype from Decimal(9,4) to Decimal(18,4).

Is there any ALTER command similar to what we have in SQL server to alter the column datatype.

Can anyone suggest any ideas on this.

1 ACCEPTED SOLUTION

Accepted Solutions

saipujari_spark
Databricks Employee
Databricks Employee

Hi @HariharaSam 

The following documents the info about how to alter a Delta table schema.

https://docs.databricks.com/delta/update-schema.html

Thanks,
Saikrishna Pujari
Sr. Spark Technical Solutions Engineer, Databricks

View solution in original post

6 REPLIES 6

-werners-
Esteemed Contributor III

At the moment you will have to rewrite the data as you cannot change the type of a column.

https://docs.delta.io/latest/delta-batch.html#change-column-type-or-name

But (others may correct me if I'm wrong here) I think type change is in the dev backlog.

Noopur_Nigam
Databricks Employee
Databricks Employee

183530
New Contributor III

from pyspark.sql.functions import *

spark.read.table("schema.table") \

.withColumn("namecolumn",col("namecolumn").cast("string")) \

.write.format("delta").mode("overwrite").option("overwriteSchema",true).saveAsTable("schema.table")

virajrathod
New Contributor II

.write.format("delta").mode("overwrite").option("overwriteSchema",true).saveAsTable("schema.table")
Need to add double quotes around true..
.option("overwriteSchema","true")
Key-value should be string, not Boolean

Lakshay
Databricks Employee
Databricks Employee

You will need to write the data to a new table. Read the data in spark and cast the data and write it to a new table.

saipujari_spark
Databricks Employee
Databricks Employee

Hi @HariharaSam 

The following documents the info about how to alter a Delta table schema.

https://docs.databricks.com/delta/update-schema.html

Thanks,
Saikrishna Pujari
Sr. Spark Technical Solutions Engineer, Databricks

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group