Update datatype of a column in a table

[mr_robot]

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? 

[jacovangelder]

You can use REPLACE COLUMNS.

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

[Slash]

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

[jacovangelder]

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.