Change data format in an existing DB table

[Pien]

I got errors of incompatible filetypes while converting to pyspark df, so I changed all columns to string types. Now I'm trying to add this df to an existing table (where not everything was a string type). And I'm getting an error of incompatible datatypes: 

How can I change the existing table to string type? It is a sql database table and the data is in the dbfs.

[Anonymous · Accepted solution]

@Pien Derkx​ :

You can alter the existing table to change the data type of columns using SQL syntax. Here's an example of how to do it:

-- Replace <table_name> and <column_name> with the actual table and column names
ALTER TABLE <table_name> ALTER COLUMN <column_name> TYPE STRING;

This will change the data type of the specified column to string. You can run this command for each column that you need to change.

Alternatively, you can create a new table with the desired schema and then copy the data from the old table to the new table using SQL. Here's an example of how to do it:

-- Create a new table with the desired schema
CREATE TABLE <new_table_name> (
  <column1_name> STRING,
  <column2_name> STRING,
  ...
);
 
-- Copy the data from the old table to the new table
INSERT INTO <new_table_name> SELECT CAST(<column1_name> AS STRING), CAST(<column2_name> AS STRING), ... FROM <old_table_name>;

This will create a new table with the desired schema and copy the data from the old table to the new table, converting the data types in the process. Once the new table is created, you can drop the old table and rename the new table to the old table name.

[Anonymous]

Hi @Pien Derkx​ 

Hope everything is going great.

Just wanted to check in if you were able to resolve your issue. If yes, would you be happy to mark an answer as best so that other members can find the solution more quickly? If not, please tell us so we can help you. 

Cheers!