remove NULL columns in Spark SQL

Question

How to remove columns containing only null values from a table? Suppose I have a table -

SnapshotDate    CreationDate    Country Region  CloseDate   Probability BookingAmount   RevenueAmount   SnapshotDate1   CreationDate1   CloseDate1
        null            null       null   null       null   25  882000  0            null            null         null
        null            null       null   null       null   25  882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null
        null            null       null   null       null   0   882000  0            null            null         null

So I would just like to have Probability, BookingAmount and RevenueAmount columns and ignore the rest.

Is there a way to dynamically select the columns?

I am using spark 1.6.1

Sascha Vetter · Accepted Answer · 2018-04-20 16:19:00Z

2

I solved this with a global groupBy. This works for numeric and non-numeric columns:

case class Entry(id: Long, name: String, value: java.lang.Float)

val results = Seq(
  Entry(10, null, null),
  Entry(10, null, null),
  Entry(20, null, null)
)

val df: DataFrame = spark.createDataFrame(results)

// mark all columns with null only
val row = df
  .select(df.columns.map(c => when(col(c).isNull, 0).otherwise(1).as(c)): _*)
  .groupBy().max(df.columns.map(c => c): _*)
  .first

// and filter the columns out
val colKeep = row.getValuesMap[Int](row.schema.fieldNames)
  .map{c => if (c._2 == 1) Some(c._1) else None }
  .flatten.toArray
df.select(row.schema.fieldNames.intersect(colKeep)
  .map(c => col(c.drop(4).dropRight(1))): _*).show(false)

+---+
|id |
+---+
|10 |
|10 |
|20 |
+---+

Edit: I removed the shuffling of columns. The new approach keeps the given order of the columns.

edited Apr 20, 2018 at 16:19

answered Mar 2, 2018 at 18:40

Sascha Vetter

2,5161 gold badge22 silver badges36 bronze badges

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

Comments

elarib · Accepted Answer · 2017-07-26 12:40:56Z

-1

You can add custom udf, and it in Spark SQL.

sqlContext.udf.register("ISNOTNULL", (str: String) => Option(str).getOrElse(""))

And with Spark SQL you can do :

SELECT ISNOTNULL(Probability) Probability, ISNOTNULL(BookingAmount) BookingAmount, ISNOTNULL(RevenueAmount) RevenueAmount FROM df

answered Jul 26, 2017 at 12:40

elarib

6741 gold badge8 silver badges17 bronze badges

1 Comment

Shubham Kedia Over a year ago

Hi @elarib. It worked but it didn't remove those columns which were nulls. It replaces the NULL with blanks. I do not need those columns at all

Collectives™ on Stack Overflow

remove NULL columns in Spark SQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related