ALTER Not Working in Databricks for external Table.

[Taj2019]

Hi Team,

i am getting error alter not suported . 

col1 DOUBLE NOT CONVERTING TO col1 DECIMAL(31,5) VIA ALTER STATEMENT.
ERROR-
liquibase.exception.DatabaseException: [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: 0A000, Query:
ALTER TABL***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: Error running query: [NOT_SUPPORTED_CHANGE_COLUMN]
org.apache.spark.sql.catalyst.ExtendedAnalysisException: [NOT_SUPPORTED_CHANGE_COLUMN] ALTER TABLE ALTER/CHANGE COLUMN is not supported for.. Any suggestion , would be appreciate.

[szymon_dybczak]

Hi  @Taj2019 ,

ALTER TABLE ... ALTER COLUMN ... TYPE is supported only for Delta tables and only for supported type-widening conversions. Databricks currently supports conversions such as FLOAT -> DOUBLE, integer types -> DECIMAL, and increasing the precision/scale of an existing DECIMAL.
It does not list DOUBLE → DECIMAL as a supported in-place conversion, so enabling delta.enableTypeWidening will not solve this particular change.

The easiest fix would be to overwrite or recreate this table from scratch.

from pyspark.sql import functions as F

(
    spark.read.table("catalog.schema.my_table")
    .withColumn("col1", F.col("col1").cast("decimal(31,5)"))
    .write
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .saveAsTable("catalog.schema.my_table")
)

If my answer was helpful, please consider marking it as accepted solution.

[ashukasma]

Databricks Delta Lake doesn't support changing column data types directly via ALTER COLUMN, especially for type conversions like DOUBLE to DECIMAL.

Based on the documentation, Databricks doesn't support arbitrary type changes via ALTER COLUMN (like DOUBLE to DECIMAL). Here are your options

Option 1: Recreate Table with New Schema (Recommended)

Option 2: Add New Column & Migrate (Safer - Preserves History)

Option 3: For Liquibase - Use Table Recreation, Since you're using Liquibase, modify your changeset to use:

• CREATE OR REPLACE TABLE instead of ALTER TABLE
• Or use Liquibase's loadUpdateData with a new table definition

[Taj2019]

thanks for the soltion. if i use CREATE OR REPLACE TABLE instead of ALTER TABLE,  i have external table then delta_log will overirde or not? if i do full load then it shold be with new changes, it should n't go for update