Problem

In Databricks Runtime 12.2 LTS or below, when you try to DROP a field inside a nested column, you receive an error. 

Example table with nested fields

%sql
CREATE OR REPLACE TABLE nested_table1 (id int, data map<int, map<int, struct<a: int, b: string>>>)

Example DROP command

%sql
ALTER TABLE nested_table1 DROP COLUMN data.value.value.b;

Error message

AnalysisException: [INVALID_FIELD_NAME] Field name `data`.`value`.`value`.`b` is invalid: `data`.`value` is not a struct.; line 1 pos 0

Cause

Using DROP on fields inside a nested column is not supported in Databricks Runtime 12.2 LTS and below.  

Solution

First, enable column mapping mode for the table using the command below, if it is not already enabled.

  %sql
  ALTER TABLE <table-name> SET TBLPROPERTIES (
    'delta.columnMapping.mode' = 'name'
  )

Then, use Databricks Runtime 13.3 LTS or above to DROP the field inside a nested column. After dropping the column you can return to your original Databricks Runtime version to access the table.