[Spark] Spark SQL Join Types with examples

最新推荐文章于 2022-12-18 19:08:11 发布
最新推荐文章于 2022-12-18 19:08:11 发布
阅读量192 收藏
点赞数
文章标签： spark
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mouliu6141/article/details/108674213
版权

Spark SQL supports all basic join operations available in traditional SQL, though Spark Core Joins has huge performance issues when not designed with care as it involves data shuffling across the network, in the other hand Spark SQL Joins comes with more optimization by default (thanks to DataFrames & Dataset) however still there would be some performance issues to consider while using.

(道出了data shuffle影响性能的原因关键：即shuffle需要跨越网络的数据交换)

In this Join tutorial, you will learn different Join syntaxes and using different Join types on two or more DataFrames and Datasets using Scala examples.

  • Join Syntax
  • Join Types
  • Self Join
  • Using spark.sql
  • Using joinType objects
  • Join multiple Tables
  • Source Code

1. Spark SQL Join Syntax

1) join(right: Dataset[_]): DataFrame
2) join(right: Dataset[_], usingColumn: String): DataFrame
// 在两张表中的join字段只会保留一份
3) join(right: Dataset[_], usingColumns: Seq[String]): DataFrame
4) join(right: Dataset[_], usingColumns: Seq[String], joinType: String): DataFrame
// 在两张表中的join字段都会保留
5) join(right: Dataset[_], joinExprs: Column): DataFrame
6) join(right: Dataset[_], joinExprs: Column, joinType: String): DataFrame

Alternatively, you can also write Join expression using where() and filter() methods on DataFrame and can have Joins on multiple columns.

2. Spark Join Types

JoinTypeJoin StringEquivalent SQL Join
Inner.sqlinnerINNER JOIN
FullOuter.sqlouter, full, fullouter, full_outerFULL OUTER JOIN
LeftOuter.sqlleft, leftouter, left_outerLEFT JOIN
RightOuter.sqlright, rightouter, right_outerRIGHT JOIN
Cross.sqlcross
LeftAnti.sqlanti, leftanti, left_anti
LeftSemi.sqlsemi, leftsemi, left_semi

注意：cross, leftanti, leftsemi三者都是从spark 2.0开始。

For Syntax 4 & 5 you can use either JoinType or “Join String” defined on the above table for joinType”string argument. When you use JoinType, you should import org.apache.spark.sql.catalyst.plans._ as this package defines JoinType objects.

The rest of the tutorial explains Join Types using syntax 6.

Before we jump into Spark SQL Join examples, first, let’s create an emp and dept DataFrame’s. here, column emp_id is unique on emp and dept_id is unique on the dept dataset’s and emp_dept_id from emp has a reference to dept_id on dept dataset.

// 注意学习这种构造df的方法
val emp = Seq((1,"Smith",-1,"2018","10","M",3000),
    (2,"Rose",1,"2010","20","M",4000),
    (3,"Williams",1,"2010","10","M",1000),
    (4,"Jones",2,"2005","10","F",2000),
    (5,"Brown",2,"2010","40","",-1),
      (6,"Brown",2,"2010","50","",-1)
  )
  
  val empColumns = Seq("emp_id","name","superior_emp_id","year_joined",
       "emp_dept_id","gender","salary")
  import spark.sqlContext.implicits._
  val empDF = emp.toDF(empColumns:_*)
  empDF.show(false)

  val dept = Seq(("Finance",10),
    ("Marketing",20),
    ("Sales",30),
    ("IT",40)
  )

  val deptColumns = Seq("dept_name","dept_id")
  val deptDF = dept.toDF(deptColumns:_*)
  deptDF.show(false)

This print “emp” and “dept” DataFrame to console.

Emp Dataset
+------+--------+---------------+-----------+-----------+------+------+
|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|
+------+--------+---------------+-----------+-----------+------+------+
|1     |Smith   |-1             |2018       |10         |M     |3000  |
|2     |Rose    |1              |2010       |20         |M     |4000  |
|3     |Williams|1              |2010       |10         |M     |1000  |
|4     |Jones   |2              |2005       |10         |F     |2000  |
|5     |Brown   |2              |2010       |40         |      |-1    |
|6     |Brown   |2              |2010       |50         |      |-1    |
+------+--------+---------------+-----------+-----------+------+------+

Dept Dataset
+---------+-------+
|dept_name|dept_id|
+---------+-------+
|Finance  |10     |
|Marketing|20     |
|Sales    |30     |
|IT       |40     |
+---------+-------+

2.1 Inner Join

Inner join is the default join in Spark and it’s mostly used, this joins two datasets on key columns and where keys don’t match the rows get dropped from both datasets (emp & dept).

empDF.join(deptDF, empDF("emp_dept_id") === deptDF("dept_id"), "inner").show(false)

2.2 Outer, Full, Fullouter Join

Outer a.k.a full, fullouter join returns all rows from both datasets, where join expression doesn’t match it returns null on respective record columns.

// 以下三者等价
empDF.join(deptDF,empDF("emp_dept_id") === deptDF("dept_id"),"outer").show(false)
empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"full").show(false)
empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"fullouter").show(false)

From our “emp” dataset’s “emp_dept_id” with value 60 doesn’t have a record on “dept” hence dept columns have null and “dept_id” 30 doesn’t have a record in “emp” hence you see null’s on emp columns. Below is the result of the above Join expression.

+------+--------+---------------+-----------+-----------+------+------+---------+-------+
|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|
+------+--------+---------------+-----------+-----------+------+------+---------+-------+
|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |
|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |
|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |
|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |
|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |
|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |
|null  |null    |null           |null       |null       |null  |null  |Sales    |30     |
+------+--------+---------------+-----------+-----------+------+------+---------+-------+

2.3 Left, Leftouter Join

Left a.k.a Leftouter join returns all rows from the left dataset regardless of match found on the right dataset when join expression doesn’t match, it assigns null for that record and drops records from right where match not found.

empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"left")
	.show(false)
empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"leftouter")
	.show(false)

From our dataset, “emp_dept_id” 6o doesn’t have a record on “dept” dataset hence, this record contains null on “dept” columns (dept_name & dept_id). and “dept_id” 30 from “dept” dataset dropped from the results. Below is the result of the above Join expression.

+------+--------+---------------+-----------+-----------+------+------+---------+-------+
|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|dept_name|dept_id|
+------+--------+---------------+-----------+-----------+------+------+---------+-------+
|1     |Smith   |-1             |2018       |10         |M     |3000  |Finance  |10     |
|2     |Rose    |1              |2010       |20         |M     |4000  |Marketing|20     |
|3     |Williams|1              |2010       |10         |M     |1000  |Finance  |10     |
|4     |Jones   |2              |2005       |10         |F     |2000  |Finance  |10     |
|5     |Brown   |2              |2010       |40         |      |-1    |IT       |40     |
|6     |Brown   |2              |2010       |50         |      |-1    |null     |null   |
+------+--------+---------------+-----------+-----------+------+------+---------+-------+

2.4 Righ, Rightouter Join

Righta.k.a Rightouter join is opposite of left join, here it returns all rows from the right dataset regardless of math found on the left dataset, when join expression doesn’t match, it assigns null for that record and drops records from left where match not found.

empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"right")
	.show(false)
empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"rightouter")
	.show(false)

From our example, the right dataset “dept_id” 30 doesn’t have it on the left dataset “emp” hence, this record contains null on “emp” columns. and “emp_dept_id” 60 dropped as a match not found on left. Below is the result of the above Join expression.

2.5 Left Semi Join（特别关注）

leftsemi join is similar to inner join difference being leftsemi join returns all columns from the left dataset and ignores all columns from the right dataset. In other words, this join returns columns from the only left dataset for the records match in the right dataset on join expression, records not matched on join expression are ignored from both left and right datasets.

即只保留左表的字段，并且数据条数和inner join之后相同（左表字段存在，但右边字段null的数据会被删掉）。semi join可以被inner join替代（即inner join + select 左表所有列字段）

The same result can be achieved using select on the result of the inner join however, using this join would be efficient.

  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"leftsemi")
    .show(false)

Below is the result of the above join expression.

leftsemi join
+------+--------+---------------+-----------+-----------+------+------+
|emp_id|name    |superior_emp_id|year_joined|emp_dept_id|gender|salary|
+------+--------+---------------+-----------+-----------+------+------+
|1     |Smith   |-1             |2018       |10         |M     |3000  |
|2     |Rose    |1              |2010       |20         |M     |4000  |
|3     |Williams|1              |2010       |10         |M     |1000  |
|4     |Jones   |2              |2005       |10         |F     |2000  |
|5     |Brown   |2              |2010       |40         |      |-1    |
+------+--------+---------------+-----------+-----------+------+------+

注意和2.3 left join的结果作对比。只存在左表的字段，且第6条数据被删掉。

2.6 Left Anti Join（特别，可用于求差集）

leftanti join does the exact opposite of the leftsemi, leftanti join returns only columns from the left dataset for non-matched records.

相当于左表和右表的差集（关联后，在左表但不在右表）

注意：从2.0开始

empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"), "leftanti")
    .show(false)

Yields below output

+------+-----+---------------+-----------+-----------+------+------+
|emp_id|name |superior_emp_id|year_joined|emp_dept_id|gender|salary|
+------+-----+---------------+-----------+-----------+------+------+
|6     |Brown|2              |2010       |50         |      |-1    |
+------+-----+---------------+-----------+-----------+------+------+

3. Self Join

Joins are not complete without a self join, Though there is no self-join type available, we can use any of the above-explained join types to join DataFrame to itself. below example use inner self join

虽然spark没有帮我们实现自关联self join，但是我们可以自己写代码实现

// 注意观察用法
empDF.as("emp1").join(empDF.as("emp2"),
	col("emp1.superior_emp_id") === col("emp2.emp_id"),"inner")
  .select(col("emp1.emp_id"),col("emp1.name"),
          col("emp2.emp_id").as("superior_emp_id"),
          col("emp2.name").as("superior_emp_name"))
  .show(false)

Here, we are joining emp dataset with itself to find out superior emp_id and name for all employees.

+------+--------+---------------+-----------------+
|emp_id|name    |superior_emp_id|superior_emp_name|
+------+--------+---------------+-----------------+
|2     |Rose    |1              |Smith            |
|3     |Williams|1              |Smith            |
|4     |Jones   |2              |Rose             |
|5     |Brown   |2              |Rose             |
|6     |Brown   |2              |Rose             |
+------+--------+---------------+-----------------+

4. Spark SQL Join

Since Spark SQL support native SQL syntax, we can also write join operations after creating temporary tables on DataFrame’s and using spark.sql()

这是平淡无奇的方式，因为没有用到dataframe的方法，还是再走sql的老路子。

  empDF.createOrReplaceTempView("EMP")
  deptDF.createOrReplaceTempView("DEPT")
//SQL JOIN
  val joinDF = spark.sql("select * from EMP e, DEPT d where e.emp_dept_id == d.dept_id")
  joinDF.show(false)

  val joinDF2 = spark.sql("select * from EMP e INNER JOIN DEPT d ON e.emp_dept_id == d.dept_id")
  joinDF2.show(false)

5. Using joinType objects

All Join objects are defined at joinTypes class, In order to use these you need to import org.apache.spark.sql.catalyst.plans.{LeftOuter,Inner,....}.

要注意这是从spark 2.0开始，才可以像Inner.sql这么用

  import org.apache.spark.sql.catalyst.plans.{LeftOuter,Inner}
  println("cross join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),LeftOuter.sql)
    .show(false)
  println("Inner join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),Inner.sql)
    .show(false)

6. Spark Join on multiple DataFrame’s

When you need to join more than two tables, you either use SQL expression after creating a temporary view on the DataFrame or use the result of join operation to join with another DataFrame like chaining them. for example

df1.join(df2,df1('id1') === df2('id2'),"inner")
   .join(df3,df1('id1') === df3('id3'),"inner")

For more details, I would recommend reading How to Join multiple tables

7. Source Code of Spark SQL Join Example

package com.sparkbyexamples.spark.dataframe.join

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.col
object JoinExample extends App {

  val spark: SparkSession = SparkSession.builder()
    .master("local[1]")
    .appName("SparkByExamples.com")
    .getOrCreate()

  spark.sparkContext.setLogLevel("ERROR")

  val emp = Seq((1,"Smith",-1,"2018","10","M",3000),
    (2,"Rose",1,"2010","20","M",4000),
    (3,"Williams",1,"2010","10","M",1000),
    (4,"Jones",2,"2005","10","F",2000),
    (5,"Brown",2,"2010","40","",-1),
      (6,"Brown",2,"2010","50","",-1)
  )
  val empColumns = Seq("emp_id","name","superior_emp_id","year_joined","emp_dept_id","gender","salary")
  import spark.sqlContext.implicits._
  val empDF = emp.toDF(empColumns:_*)
  empDF.show(false)

  val dept = Seq(("Finance",10),
    ("Marketing",20),
    ("Sales",30),
    ("IT",40)
  )

  val deptColumns = Seq("dept_name","dept_id")
  val deptDF = dept.toDF(deptColumns:_*)
  deptDF.show(false)


  println("Inner join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"inner")
    .show(false)

  println("Outer join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"outer")
    .show(false)
  println("full join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"full")
    .show(false)
  println("fullouter join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"fullouter")
    .show(false)

  println("right join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"right")
    .show(false)
  println("rightouter join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"rightouter")
    .show(false)

  println("left join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"left")
    .show(false)
  println("leftouter join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"leftouter")
    .show(false)

  println("leftanti join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"leftanti")
    .show(false)

  println("leftsemi join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"leftsemi")
    .show(false)

  println("cross join")
  empDF.join(deptDF,empDF("emp_dept_id") ===  deptDF("dept_id"),"cross")
    .show(false)

  println("Using crossJoin()")
  empDF.crossJoin(deptDF).show(false)

  println("self join")
  empDF.as("emp1").join(empDF.as("emp2"),
    col("emp1.superior_emp_id") === col("emp2.emp_id"),"inner")
    .select(col("emp1.emp_id"),col("emp1.name"),
      col("emp2.emp_id").as("superior_emp_id"),
      col("emp2.name").as("superior_emp_name"))
      .show(false)

  empDF.createOrReplaceTempView("EMP")
  deptDF.createOrReplaceTempView("DEPT")

  //SQL JOIN
  val joinDF = spark.sql("select * from EMP e, DEPT d where e.emp_dept_id == d.dept_id")
  joinDF.show(false)

  val joinDF2 = spark.sql("select * from EMP e INNER JOIN DEPT d ON e.emp_dept_id == d.dept_id")
  joinDF2.show(false)

}
simidagogogo
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
spark-examples_2.10-1.6.4-SNAPSHOT.jar
07-13
解决Spark与Hbase版本升级后Pyspark写入，报错找不到StringToImmutableBytesWritableConverte类和找不到:org.apache.hadoop.hbase.client.Put类中的add方法的问题
大数据技术架构(组件)33——Spark：Spark SQL--Join Type
sysocc的博客
02-12 652
1.5、如果用户没有指定任何Join Hint，那么就根据Join的策略Broadcast Hash Join ---> Shuffle Hash Join --> Sort Merge Join ---> Cartesian Product Join --> Broadcast Nested Loop Join顺序选择Join策略。当然用户也可以手动选择策。1、Shuffle阶段：对两个表参与Join的keys使用相同的分区算法和分区数进行分区，目的就是为了保障相同的key都分配到相同的分区里面。
Spark SQL 之 Join 实现
weixin_30258027的博客
09-20 841
原文地址：Spark SQL 之 Join 实现 Spark SQL 之 Join 实现 涂小刚2017-07-19217标签：spark，数据库 Join作为SQL中一个重要语法特性，几乎所有稍微复杂一点的数据分析场景都离不开Join，如今Spark SQL(Dataset/DataFrame)已经成为Spark应用程序开发的主流，作为开发者，我们有必要了解Jo...
Spark中常见join操作
徐先森的博客
05-22 5078
spark中的连接操作
spark broadcast side join实例
ZG_24的博客
07-04 1856
在用spark进行大数据处理时，join是少不了的操作。由于join会导致shuffle，所以当数据量较大时，会导致效率很低，甚至任务失败。         为了有效解决shuffle阶段引起的效率过低问题，我们可以使用broadcast map side join来避免shuffle阶段导致的效率变低问题。        使用map side join时，我们可以把一个较小的
Spark 简单实例（基本操作）
网络空间发展与战略研究
07-12 2万+
目录[-] 1、准备文件 2、加载文件 3、显示一行 4、函数运用  （1）map  （2）collecct  （3）filter  （4）flatMap  （5）union  （6） join  （7）lookup  （8）groupByKey  （9）sortByKey 1、准备文件 ? 1
Machine Learning with Spark - 2nd Edition
01-07
the book will generally take a practical, applied approach with a focus on using examples and code to illustrate how to effectively use the features of Spark and MLlib, as well as other well-known ...
original-spark-examples-2.4.3.jar.zip
04-07
源码编译制作的parcel包，亲测可用。 pyspark访问hbase2报错的解决方案，下载spark2.4.3的源码重新打包。
spark-examples:探索 Spark 的示例
06-29
spark-examples Anagrams、BigramAnalysis、Inverted index、vihicle 使用在这一点上是稳定的，所有其他类都是 WIP。 一些示例依赖于本地文件，我将修复这些输入路径。 为了从本地机器运行示例，请确保您的路径中...
learning-spark-examples-master
06-01
learning-spark-examples-master
SparkSQL join
weixin_72649242的博客
12-18 949
SparkSQL join_type
SparkSQL的3种Join实现
doggggggggggggggggggggggggggggggggggggie的专栏
12-12 1万+
本文简单介绍SparkSQL中的几种Join实现。SparkSQL会根据用户配置，对不同大小的表应用不同的Join策略，兼顾效率和稳定性。
spark sql 之join等函数用法
zhousishuo
06-15 3万+
spark sql join cast row_number()
Spark SQL 支持哪些类型的 JOIN ？
Shockang的博客
03-26 2507
前言 本文隶属于专栏《大数据技术体系》，该专栏为笔者原创，引用请注明来源，不足和错误之处请在评论区帮忙指出，谢谢！ 本专栏目录结构和参考文献请见大数据技术体系 7 种 JOIN Spark 3.3.0 版本支持 7 种类型的 JOIN。 INNER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN LEFT SEMI JOIN LEFT ANTI JOIN CROSS JOIN pyspark 支持 pandas 的merge_asof，
Spark SQL 测试JoinType中所有join的类型，便于理解
weixin_34005042的博客
05-25 1416
准备 测试数据 trade订单号 卖家 买家 买家城市 1 A 小王 北京 2 B 小李 天津 3 A 小刘 北京 order所属订单号 买家 商品名称 价格 发货时间 1 小王 电视 12 2015-08-01 09:08:31 1 小王 冰箱 24 2015-08-01 09:08:14 2 小李 空调 12 ...
Spark SQL Join类型
sperospera的博客
04-12 1900
Spark SQL join工作是通过使用executors 操作DataFrame的多个分区，具体的过程和性能依赖join type和datasets的特征。join的过程中，包含了shuffle join 和broadcast join； 在两个大的datasets join的过程中，进行了 shuffle join，使左右的dataset的分区数据到达executors。如： ...
Spark DataFrame中的join类型
热门推荐
anjingwunai的专栏
07-17 4万+
欢迎使用Markdown编辑器写博客本Markdown编辑器使用StackEdit修改而来，用它写博客，将会带来全新的体验哦：www hello 百度 Markdown和扩展Markdown简洁的语法 代码块高亮 图片链接和图片上传 LaTex数学公式 UML序列图和流程图 离线写博客 导入导出Markdown文件 丰富的快捷键 sdfa sdsd* sd *快捷键 加粗 Ctrl +
Spark SQL中Join连接概念以及示例代码
gym02的博客
04-18 599
Join连接 类型 内连接：A表和B表符合条件的数据进行连接 左外连接：A表的所有数据和B表符合条件的数据进行连接 右外连接：B表的所有数据和A表符合条件的数据进行连接 全外连接：A表和B符合条件数据连接，并显示两张表未连接的数据 笛卡尔连接（交叉连接）：A表的每一条数据和B表每一条数据进行连接 左半开连接（left semi join）:等价于数据库中in，枚举查询 左反连接（left anti...
Spark 系列（十二）—— Spark SQL JOIN操作
黑白影的博客
06-08 1万+
一、 数据准备 本文主要介绍Spark SQL的多表连接，需要预先准备测试数据。分别创建员工和部门的Datafame，并注册为临时视图，代码如下： val spark = SparkSession.builder().appName("aggregations").master("local[2]").getOrCreate() val empDF = spark.read.json("/usr...
give me some examples about spark sql
最新发布
08-06
Spark SQL是Apache Spark的一个模块，提供了基于SQL的数据处理功能。下面是一些Spark SQL的示例： 1. 创建DataFrame：可以通过从文件或其他数据源读取数据创建DataFrame，也可以从已有的RDD或DataFrame创建。 2. ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值