Alter Delta table column datatype

[HariharaSam]

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-]

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]

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]

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]

.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]

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 · Accepted solution]

Hi @HariharaSam 

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

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