How to change the column datatype of a delta table

Shukla, Mayank - Vendor 1

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-04-18T22:05:16.79+00:00
Hello @Shukla, Mayank - Vendor ,

Thanks for the question and using MS Q&A platform.

Not sure if you have got a chance to see these threads where a similar topic has been discussed. Please have a look at these and let me know if you have further questions.

Change the datatype of a column in delta table

How to change column type in Databricks in PySpark

Related document - Change column type or name

Hope this helps.

Thank you
Shukla, Mayank - Vendor 1 Reputation point

2022-04-18T22:35:23.397+00:00

Hi Kranthi,

I have already gone through the links which you have shared.
They are trying to explain an alternative way by creating a dummy table and insert data into dummy table.
It doesn't apply to me, I have millions of records in the delta table. Dropping table and deleting data files will take hours so I wanted to know if there's a way to use an ALTER command.
Shukla, Mayank - Vendor 1 Reputation point

2022-05-05T17:29:23.74+00:00

@KranthiPakala-MSFT , thank you for your response.

As you have mentioned that ALTER statement wont work in this scenario so we have to rely on the alternative solution which you provided.

1 answer

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-05-03T19:49:33.29+00:00
Hello @Shukla, Mayank - Vendor ,

As per the discussion with internal team, unfortunately there is no ALTER TABLE ALTER COLUMN at this moment. The possible workaround suggested is to change a datatype by reading the data in datafram using PySpark as shown below:

df = df.withColumn("id", col("colname").cast("newdatatype")) # changes a column datatype.

Then to write it back to the delta table:

df.write.format("delta").mode("overwrite").option("overwriteSchema", "true").save(folder_path) # overwrites the delta table and is another version:

If you have any feedback regarding this, I would recommend you to please log a feature request in IDEAS forum here -

https://feedback.azure.com/d365community/forum/2efba7dc-ef24-ec11-b6e6-000d3a4f0da0

https://docs.databricks.com/resources/ideas.html

All the feedback shared in this forum are actively monitored and reviewed by product team who are responsible for building it and will take action accordingly. Please do share the feedback link once it is posted so that others can up-vote and comment on it to help increase the priority of the feature request.

Hope this info helps.
Please sign in to rate this answer.

1 person found this answer helpful.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-05-05T17:22:53.71+00:00

Hello @Shukla, Mayank - Vendor ,

Just checking in to see if the above information was helpful. And, if you have any further query do let us know.
Sign in to comment