Left Anti join in Spark?

by

You can use the “left anti” join type – either with DataFrame API or with SQL (DataFrame API supports everything that SQL supports, including any join condition you need):

DataFrame API:

df.as("table1").join(
  df2.as("table2"),
  $"table1.name" === $"table2.name" && $"table1.age" === $"table2.howold",
  "leftanti"
)

SQL:

sqlContext.sql(
  """SELECT table1.* FROM table1
    | LEFT ANTI JOIN table2
    | ON table1.name = table2.name AND table1.age = table2.howold
  """.stripMargin)

NOTE: it’s also worth noting that there’s a shorter, more concise way of creating the sample data without specifying the schema separately, using tuples and the implicit toDF method, and then “fixing” the automatically-inferred schema where needed:

import spark.implicits._
val df = List(
  ("mike", 26, true),
  ("susan", 26, false),
  ("john", 33, true)
).toDF("name", "age", "isBoy")

val df2 = List(
  ("mike", "grade1", 45, "baseball", new java.sql.Date(format.parse("1957-12-10").getTime)),
  ("john", "grade2", 33, "soccer", new java.sql.Date(format.parse("1978-06-07").getTime)),
  ("john", "grade2", 32, "golf", new java.sql.Date(format.parse("1978-06-07").getTime)),
  ("mike", "grade2", 26, "basketball", new java.sql.Date(format.parse("1978-06-07").getTime)),
  ("lena", "grade2", 23, "baseball", new java.sql.Date(format.parse("1978-06-07").getTime))
).toDF("name", "grade", "howold", "hobby", "birthday").withColumn("birthday", $"birthday".cast(DateType))

More Related Contents:

  1. Apache Spark – Scala – ReduceByKey – with keys repeating up to twice only
  2. Write to multiple outputs by key Spark – one Spark job
  3. How does HashPartitioner work?
  4. Spark 2.0 Dataset vs DataFrame
  5. Spark – load CSV file as DataFrame?
  6. Task not serializable: java.io.NotSerializableException when calling function outside closure only on classes not objects
  7. How to define partitioning of DataFrame?
  8. Case class equality in Apache Spark
  9. Why accesing DataFrame from UDF results in NullPointerException?
  10. Dropping a nested column from Spark DataFrame
  11. Spark dataframe write method writing many small files
  12. Filling gaps in timeseries Spark
  13. Reading DataFrame from partitioned parquet file
  14. java.lang.NoClassDefFoundError: org/apache/spark/streaming/twitter/TwitterUtils$ while running TwitterPopularTags
  15. How to use COGROUP for large datasets
  16. What are possible reasons for receiving TimeoutException: Futures timed out after [n seconds] when working with Spark [duplicate]
  17. Parsing multiline records in Scala
  18. Why does join fail with “java.util.concurrent.TimeoutException: Futures timed out after [300 seconds]”?
  19. Create new Dataframe with empty/null field values
  20. How to Define Custom partitioner for Spark RDDs of equally sized partition where each partition has equal number of elements?
  21. DataFrame-ified zipWithIndex
  22. Derive multiple columns from a single column in a Spark DataFrame
  23. How to explode an array into multiple columns in Spark
  24. Why is join not possible after show operator?
  25. Spark : how to run spark file from spark shell
  26. How can I connect to a postgreSQL database into Apache Spark using scala?
  27. Spark Dataframe Random UUID changes after every transformation/action
  28. Spark column string replace when present in other column (row)
  29. How to vectorize DataFrame columns for ML algorithms?
  30. How to set hadoop configuration values from pyspark

Leave a Comment