topic Re: Alter Delta table column datatype in Data Engineering

Alter Delta table column datatype

HariharaSam — Fri, 22 Jul 2022 06:51:22 GMT

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.

Re: Alter Delta table column datatype

-werners- — Mon, 25 Jul 2022 07:45:43 GMT

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.

Re: Alter Delta table column datatype

Noopur_Nigam — Tue, 30 Aug 2022 21:02:27 GMT

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

Re: Alter Delta table column datatype

183530 — Wed, 12 Apr 2023 16:13:31 GMT

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")

Re: Alter Delta table column datatype

virajrathod — Mon, 17 Jul 2023 17:36:57 GMT

.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

Re: Alter Delta table column datatype

Lakshay — Wed, 19 Jul 2023 19:11:23 GMT

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.

Re: Alter Delta table column datatype

saipujari_spark — Wed, 19 Jul 2023 19:20:26 GMT

Hi @HariharaSam

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

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