topic Update datatype of a column in a table in Data Engineering

Update datatype of a column in a table

mr_robot — Thu, 18 Jul 2024 20:24:11 GMT

I have a table in databricks with fields name: string, id: string, orgId: bigint, metadata: struct, now i want to rename one of the columns and change it type. In my case I want to update orgId to orgIds and change its type to map<string, string>

One approach I can think of is, add a new column to the table and write script to migrate the data from one column to the other. But there is no way to drop a column.

So I will have to create a new table altogether and do the data migration and then delete the old table.
Is there a better approach?

Re: Update datatype of a column in a table

jacovangelder — Fri, 19 Jul 2024 07:36:39 GMT

You can use REPLACE COLUMNS.

ALTER TABLE your_table_name REPLACE COLUMNS ( name STRING, id BIGINT, orgIds MAP<STRING, STRING>, metadata STRUCT<...> );

Re: Update datatype of a column in a table

szymon_dybczak — Fri, 19 Jul 2024 08:38:00 GMT

Hi @jacovangelder @mr_robot ,

I think @jacovangelder approach will not work for delta table. According to documentation:

REPLACE COLUMNS deletes all the existing columns and adds new columns specified.

And Delta format don't support column deletion.

So, the only way to do this is to overwrite to the table, as is stated in documentation:

https://docs.databricks.com/en/delta/update-schema.html#explicitly-update-schema-to-change-column-type-or-name

Re: Update datatype of a column in a table

jacovangelder — Fri, 19 Jul 2024 09:01:07 GMT

You're right, it's a metadata only change, but it will evolve the schema.
If you need to rewrite the files as well, then you'll need to overwrite.