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.