Change the datatype of a column in delta table

user_12335838 0
Viewed 4796

Is there a SQL command that I can easily use to change the datatype of a existing column in Delta table. I need to change the column datatype from BIGINT to STRING. Below is the SQL command I'm trying to use but no luck.

%sql ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] STRING

Error I'm getting:

org.apache.spark.sql.AnalysisException
ALTER TABLE CHANGE COLUMN is not supported for changing column 'bam_user' with type 
'IntegerType' to 'bam_user' with type 'StringType'
delta-lake apache-spark-sql apache-spark
2 Answers

No Option to change the data type of column or dropping the column. You can read the data in datafame, modify the data type and with help of withColumn() and drop() and overwrite the table.

user_55246590

There is no real way to do this using SQL, unless you copy to a different table altogether. This option includes INSERT data to a new table, DROP TABLE and re-CREATE with the new structure and therefore risky.

The way to do this in python is as follows:

Let's say this is your table : CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING); INSERT INTO person VALUES (100, 'John', 30, 1, 'Street 1'), (200, 'Mary', NULL, 1, 'Street 2'), (300, 'Mike', 80, 3, 'Street 3'), (400, 'Dan', 50, 4, 'Street 4');

You can check the table structure using the following:
DESCRIBE TABLE person

IF you need to change the id to String:

This is the code: %py from pyspark.sql.functions import col

df = spark.read.table("person")

df1 = df.withColumn("id",col("id").cast("string"))

df1.write
.format ("parquet") .mode("overwrite")
.option("overwriteSchema", "true")
.saveAsTable("person")

Couple of pointers: the format is parquet in this table. That's the default for Databricks. So you can omit the "format" line (note that Python is very sensitive regarding spaces).

Re databricks:

If the format is "delta" you must specify this.

Also, if the table is partitioned, it's important to mention that in the code: For example: df1.write
.format ("delta")
.mode("overwrite")
.partitionBy("col_to_partition1", "col_to_partition2")
.option("overwriteSchema", "true")
.save(table_location)

When table_location is where the delta table is saved.

(some of this answer is based on this)

user_30979810
Related