drop column in a table/view using spark sql only

Question

i have 30 columns in a table i.e table_old

i want to use 29 columns in that table except one . that column is dynamic. i am using string interpolation.

the below sparksql query i am using

 drop_column=now_current_column

var table_new=spark.sql(s"""alter table table_old drop $drop_column""")

but its throwing error

 mismatched input expecting 'partition'

i dont want to drop the column using dataframe. i requirement is to drop the column in a table using sparksql only

Answer 1

As mentioned in previous answer, DROP COLUMN is not supported by spark yet.

But, there is a workaround to achieve the same, without much overhead. This trick works for both EXTERNAL and InMemory tables. The code snippet below works for EXTERNAL table, you can easily modify it and use it for InMemory tables as well.

val dropColumnName = "column_name"
val tableIdentifier = "table_name"
val tablePath = "table_path"

val newSchema=StructType(spark.read.table(tableIdentifier).schema.filter(col => col.name != dropColumnName))

spark.sql(s"drop table ${tableIdentifier}")
spark.catalog.createTable(tableIdentifier, "orc", newSchema, Map("path" -> tablePath))

orc is the file format, it should be replaced with the required format. For InMemory tables, remove the tablePath and you are good to go. Hope this helps.

Answer 2

DROP COLUMN (and in general majority of ALTER TABLE commands) are not supported in Spark SQL.

If you want to drop column you should create a new table:

CREATE tmp_table AS 
SELECT ... -- all columns without drop TABLE
FROM table_old

and then drop the old table or view, and reclaim the name.