Using ALTER TABLE to add new column into Array(Struct) Column on Databricks

Question

I have a DeltaTable with several columns that are ArrayTypes, containing StructTypes.

I'm trying to add an extra column into the StructType, but I am running into issues because it is wrapped in an ArrayType.

Hoping someone has a way to do this, or similar method that doesn't involve me backing up the table, creating the table again from scratch and restoring the data from the back-up.

I am currently using this command:

spark.sql(f"ALTER TABLE {db}.ads ADD COLUMNS (response.monkey boolean AFTER dq_conveyed)")

Which would work if it was only a StructType, but is failing because it's an array

This is the current Schema I'm trying to make the change to:

StructField(response,ArrayType(StructType(
    StructField(encounter_uid,StringType,true),
    StructField(patient_uid,StringType,true),
    StructField(call_sign,StringType,true),
    StructField(time_resource_allocated,StringType,true),
    StructField(time_resource_arrived_at_receiving_location,StringType,true),
    StructField(time_of_patient_handover,StringType,true),
    StructField(time_clear,StringType,true),
    StructField(response_type,StringType,true),
    StructField(time_resource_mobilised,StringType,true),
    StructField(time_resource_arrived_on_scene,StringType,true),
    StructField(time_stood_down,StringType,true),
    StructField(time_resource_left_scene,StringType,true),
    StructField(highest_skill_level_on_vehicle,StringType,true),
    StructField(responding_organisation_type,StringType,true),
    StructField(dq_conveyed,BooleanType,true)),
true),true)

I'm following this documentation:

https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-alter-table.html

scott-sandre-databricks · Accepted Answer · 2021-09-02 00:04:50Z

6

You can do:

spark.sql(f"ALTER TABLE {db}.ads ADD COLUMNS (response.element.monkey boolean AFTER dq_conveyed)")

Notice the element keyword. Let me know if this doesn't work for you!

answered Sep 2, 2021 at 0:04

scott-sandre-databricks

1611 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lester Drake Over a year ago

Unfortunately that hasn't worked either. I get the following error: Error in SQL statement: AnalysisException: Couldn't find column response.element in And then in prints out the schema of the table, which does contain and element as you've suggested |-- response: array (nullable = true) | |-- element: struct (containsNull = true)

scott-sandre-databricks Over a year ago

which version of DBR are you using?

Lester Drake Over a year ago

Hi, I have long since discovered that this is due to using an older version of the DBR.

Lester Drake Over a year ago

Just wanted to confirm that I have now had an opportunity to test the solution in DBR 10.4 and this solution including ".element." where there is an array does work!

Collectives™ on Stack Overflow

Using ALTER TABLE to add new column into Array(Struct) Column on Databricks

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related