Finding null entries in a Spark Dataset using JAVA

Viewed 165

A dataset is created using data; x:y:z:t After performing a count on this data set

Dataset<Row> testDF = tbHitDf.groupBy("x", "y", "z", "t").count()
                .sort("x", "y", "z").toDF("x", "y", "z", "t", "counts");

Now it could be sometimes that the data it is filled as

| x| y |x| t|counts|
+--+--+--+--+------+
| 1| 1| 2| 2|     1|
| 1| 1| 2| 3|     1|
| 1| 1| 2| 4|     2|
| 1| 1| 2| 6|     5|
| 1| 1| 2| 8|     4|

In which I understand why the t values of 1, 5, and 7 are missing because there is no data there. But this is the exact data I want to know, i.e. for which values x:y:z:t are there no entries.

I have tried to create an empty dataset

| x| y |x| t|
+--+--+--+--+
| 1| 1| 2| 1|
| 1| 1| 2| 2|
| 1| 1| 2| 3|
| 1| 1| 2| 4|
| 1| 1| 2| 5| 
| 1| 1| 2| 6|
| 1| 1| 2| 7|

then join them

Dataset<Row> joinDf = emptyData.join(testDF,
testDF.col("x").equalTo(emptyData.col("x"))
.and(testDF.col("y").equalTo(emptyData.col("y")))
.and(testDF.col("z").equalTo(emptyData.col("z")))
.and(testDF.col("t").equalTo(emptyData.col("t"))));

But this only joins the data that overlap for both datasets. How can i find the null entries of a dataset?

Edit: I have tried creating sql temp views, i.e.;

testDF.createOrReplaceTempView("DataView");
emptyData.createOrReplaceTempView("testView");

Then use sql statements LEFT JOIN etc.

spSession.sql("SELECT  testView.t FROM testView LEFT JOIN DataView ON DataView.x = testView.x AND DataView.y = testView.y AND DataView.z = testView.z  WHERE DataView.t IS NULL")

But this also does not achieve the goal, as it returns an empty list.

When I try to subtract the datasets using .except I get a strange occurrence i.e.,

Dataset<Row> subDf = dataDF.except(emptyDF);

yields

| x| y| z| t|
+--+--+--+--+
| 1| 1| 2| 2|
| 1| 1| 2| 3|
| 1| 1| 2| 4|
| 1| 1| 2| 6|
| 1| 1| 2| 8|

These are the common, I want the uncommon so I try the reverse

Dataset<Row> subDf = emptyDF.except(dataDF);

and get

| x| y| z| t|
+--+--+--+--+
| 1| 1| 2| 1|
| 1| 1| 2| 2|
| 1| 1| 2| 3|
| 1| 1| 2| 4|
| 1| 1| 2| 5|

Which is very confusing, I was expecting the 1, 5, 7 ... to show up.

0 Answers
Related