Re: Alter Delta table column datatype

HariharaSam · ‎07-21-2022

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.

-werners- · ‎07-25-2022

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 · ‎08-30-2022

Hi @Hariharan Sambath Please check following document: https://docs.databricks.com/spark/2.x/spark-sql/language-manual/alter-table-or-view.html#change-colu...

183530 · ‎04-12-2023

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 · ‎07-17-2023

.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 · ‎07-19-2023

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 · ‎07-19-2023

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