Select all except particular column in spark sql

Question

I want to select all columns in a table except StudentAddress and hence I wrote following query:

select `(StudentAddress)?+.+` from student;

It gives following error in Squirrel Sql client. org.apache.spark.sql.AnalysisException: cannot resolve '(StudentAddress)?+.+' given input columns

I am joining multiple very wide tables so after performing one join, I need to drop one of the joined column to remove ambiguity for next join. Currently, I am specifying all the column names I want in select but functionality like except columns would be very flexible. Thanks. — Patel, Apr 27 '17 at 13:49

score 8 · Answer 1 · answered Sep 13 '22 at 21:35

Using spark sql try with

select * except(<columns to be excluded>) from tablename

Example:

select * from tmp
#+----+----+----+----+
#|col1|col2|col3|col4|
#+----+----+----+----+
#|a   |b   |c   |d   |
#+----+----+----+----+


#exclude col1,col2
select * except(col1,col2) from table_name
#+----+----+
#|col3|col4|
#+----+----+
#|c   |d   |
#+----+----+

score 7 · Accepted Answer · edited Jun 26 '19 at 00:07

7

You can use drop() method in the DataFrame API to drop a particular column and then select all the columns.

For example:

val df = hiveContext.read.table("student")
val dfWithoutStudentAddress = df.drop("StudentAddress")

edited Jun 26 '19 at 00:07

Jayson Minard

84,842
38
184
227

answered Nov 17 '17 at 04:09

Neeraj Malhotra

271
3
6

2

The select("*") isn't necessary after the drop. – Tim Gautier Apr 23 '19 at 20:42

Select all except particular column in spark sql

2 Answers2