Adding a Column to Spark Table via SQL ALTER TABLE command

Question

Can I add a new column to an existing spark table using the ALTER TABLE command ?

var query = "ALTER TABLE " + "global_temp." + tableName(0) + " ADD COLUMN " + newColumnName + " " + newColumnDatatype
var drt = spark.sql(query)

The above code raises the following error.

 no viable alternative at input 'ALTER TABLE global_temp.people_ty ADD COLUMN' new_age integer

EDIT

The correct syntax is as follows

ALTER TABLE tablename ADD COLUMNS (newColumn newDataType)

But, it also throws the following error.

ALTER ADD COLUMNS does not support views.
You must drop and re-create the views for adding the new columns. Views: `global_temp`.`people_ty`

@philantrovert , people_ty is registered using createGlobalTempView — Soumyadip Ghosh
– Soumyadip Ghosh, Commented Apr 25, 2018 at 7:07
Why not do a withColumn before you create a view out of it? — philantrovert
– philantrovert, Commented Apr 25, 2018 at 7:08
Spark 2.3.0 . The syntax is ALTER TABLE tablename ADD COLUMNS (newColumn newDataType) — Soumyadip Ghosh
– Soumyadip Ghosh, Commented Apr 25, 2018 at 8:51

Reed Richards · Accepted Answer · 2021-03-03 12:51:22Z

5

In Spark SQL the syntax is as mentioned by Soumyadip Ghosh in the comments

ALTER TABLE table_identifier ADD COLUMNS ( col_spec [ , ... ] )

Works for me.

answered Mar 3, 2021 at 12:51

Reed Richards

4,3988 gold badges46 silver badges55 bronze badges

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

1 Comment

HappilyCoding Over a year ago

When trying to use that synthax i get an error. Code: spark.read.format('jdbc').options(**spark_sql_options).option('query', F'SELECT * FROM {table_name}').load().createGlobalTempView('db_second_view') Error: AnalysisException: test_table is a temp view. 'ALTER TABLE ... ADD COLUMN' expects a table..; Am I missing something? Is it because my view is backed by an actual sql database?

Collectives™ on Stack Overflow

Adding a Column to Spark Table via SQL ALTER TABLE command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related