I want to add a column to my delta table via Databricks SQL. Following the syntax on the Microsoft site https://learn.microsoft.com/en-us/azure/databricks/delta/update-schema and Databricks site https://docs.databricks.com/sql/language-manual/sql-ref-syntax-ddl-alter-table.html However, in every situation the column was added at the end of the schema instead of at the defined place. I can't figure out what is going wrong.
I tried the following:
ALTER TABLE table_name ADD COLUMNS (test STRING FIRST)
ALTER TABLE table_name ADD COLUMN (test STRING FIRST)
ALTER TABLE table_name ADD COLUMN test STRING FIRST
ALTER TABLE table_name ADD COLUMN test STRING AFTER col1
ALTER TABLE table_name ADD COLUMN test STRING AFTER col1
ALTER TABLE table_name ADD COLUMN test STRING AFTER table_name.col1\
And the same with data-type DECIMAL and DECIMAL(16,2)
---- UPDATE ----
The problem seems to be related to the type of table you are working on. I could add my columns in the specified locations for an external table in my azure blob storage. However for a managed table in the dbfs:/user/hive/warehouse/, I was not able to add the columns in the desired location.
