0

I want to select the columns which contains values other than NULL. Suppose I have a table abc:

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

Then I would want to select Only Probability, BookingAmount and RevenueAmount columns as they contains some values other than null and ignore the rest of the column that contains only null values.

I tried doing something like - select * from abc where SnapshotDate != null and CreationDate != null and ..... But that does not help as it will look for each row.

NOTE:- Is there something like - sqlContext.sql("select case when col1 is null then dont select it else select it end) x from abc")

I am using spark 1.6.1

Is there a way to do that?

Thanks in advance

Improve this question
2
  • You could collect first row check for non null values and get the name of column then call select on your previous dataframe. Done Commented Jul 29, 2017 at 14:57
  • 3
    Possible duplicate of remove NULL columns in Spark SQL Commented Mar 2, 2018 at 19:00
Related questions
7
Spark scala remove columns containing only null values
2
remove NULL columns in Spark SQL
0
How to find columns with many nulls in Spark/Scala
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.