I am trying to compare two datasets after loading it using Apache Spark:
final SparkSession sparkSession=SparkSession.builder().appName("Final Project").master("local[3]").getOrCreate();
final DataFrameReader reader = sparkSession.read();
reader.option("header", "true");
Dataset<Row> mainDF = reader.csv(mainFile);
Dataset<Row> compareDF = reader.csv(compareFile);
mainDF.createOrReplaceTempView("main");
compareDF.createOrReplaceTempView("compare");
Dataset<Row> joinDF = sparkSession.sql("SELECT * FROM (SELECT 'main' AS main, main.* FROM main) main NATURAL FULL JOIN (SELECT 'compare' AS compare, compare.* FROM compare) compare WHERE main IS NULL OR compare IS NULL");
joinDF.coalesce(1).write().mode("overwrite").
format("csv").option("header", "true").
save("src/main/resources/comparePrototype/Test");
Now, my data consists of almost 200k-300k rows and has columns that would have values including but not limited to "null", " ", "NULL", etc. Each meaning that there is no data within the column. My joinDF query should result into a dataset which has all the data that is different from each other in main and compare datasets. For example, I take a CSV:
id,first_name,last_name,email,gender,ip_address,test
1,Aurelia,Wayvill,awayvill0@theatlantic.com,Female,132.57.62.243,NULL
2,Carey,Winfrey,cwinfrey1@soundcloud.com,Male,138.31.65.57,
3,La verne,Jeannel,ljeannel2@ftc.gov,Female,5.171.43.17,null
4,Norry,Sammut,nsammut3@ihg.com,Female,177.59.155.91,
And another CSV to compare it against:
id,first_name,last_name,email,gender,ip_address,test
1,Aurelia,Wayvill,awayvill0@theatlantic.com,Female,132.57.62.243,
2,Carey,Winfrey,cwinfrey1@soundcloud.com,Male,138.31.65.57,
3,La verne,Jeannel,ljeannel2@ftc.gov,Female,5.171.43.17,
4,Norry,Sammut,nsammut3@ihg.com,Female,177.59.155.91,
Running the above code snippet gives me a CSV file that should ideally be empty or I can also work with the case where it only has 3 data rows from ids 1 to 3 since they have different test column values. But, somehow it also contains data row 4 and I don't know how to stop it from comparing it against rows that have empty columns.
Any idea on how to proceed with the same? Or any changes I should do in my SQL query?