左外连接Left-Outer-Join的基于SQL，MapReduce，sparkRDD，sparkDataFrame以及spark SQL的实现案例及对比

最新推荐文章于 2021-03-06 23:04:02 发布

Ahaxian

最新推荐文章于 2021-03-06 23:04:02 发布

阅读量1.2k

点赞数 2

分类专栏： hadoop学习之路 MapReduce学习之路文章标签：大数据 spark 数据库 mapreduce sql

本文链接：https://blog.csdn.net/weixin_46031805/article/details/113800992

版权

hadoop学习之路同时被 2 个专栏收录

9 篇文章 1 订阅

订阅专栏

MapReduce学习之路

2 篇文章 0 订阅

订阅专栏

提示：阅读本文需要部分java,scala,spark,sql的基础。

前言

概要：本博客截取2020年数学建模国赛C题提供的部分数据作为数据源，联系现实中的需要用到join操作的场景，分别使用sql，mapreduce，spark RDD，spark DataFrame，以及spark SQL完成了left-outer-join的数据分析操作，并分析和总结了不同方式的优缺点。

一、什么是join操作?

提示：熟悉关系型数据库，了解join操作的朋友可以跳过该部分。

在能够有效的使用join操作之前，必须了解关系表以及关系型数据库设计的一些基础知识：知道什么是光系表，什么是主键？什么是外键？这么设计的好处是什么？为了方便大家理解，我将举例如下:
tableB
上图的表格为一个记录发票信息的关系表，第1行是表头，也称字段，第1行往后的每一行都是一条记录，是对每一张发票的抽象，记录了每张发票例如"发票号码"、“企业代号”、“金额"等的具体的交易信息，我们记为 “发票信息表”。然后我们再看另一张表：
tableA
上图的表格为一个记录公司信息的关系表，同样的第1行是表头，第1行往后的每一行都是描述一家公司信息的一条记录，记录了每家公司的"企业代号”、"企业名称"等信息，是对每一张发票的抽象，我们记为 “企业信息表”。

那这两个表之间有什么关系呢？我们不难看出两表之间都有一个共同的字段——“企业代号”，当然这里所谓的共同不是简单的指两个表的字段名字一样，而是指其字段对应的值域是一致的。而join操作就是通过一个或多个共同字段，将两个或多个表的信息联系起来。

既然要把多个表的信息联合起来，那为什么我们不在一开始存储信息的时候，就把这些信息就糅合在一起呢？这样把多个表分开存储对我们有什么好处呢？让我们来慢慢分析，首先看一看把之前举例的两个表合成一个表是什么样子的,我们将该表记做 “企业发票信息表”：
tableAB
通过对上述 “企业发票信息表” 的观察，我们可以发现：对于同一 “企业代号” 的记录来说，其 “企业名称”，“信誉评级”，“是否违约” 的字段的值是完全一致的！因为这几个字段都是用来描述企业信息的，仅与企业代号有关，而和其它描述发票信息的字段无关。由此不难得出以下结论：

因为同一个企业代号对应的企业信息都是一致的，若将发票信息与公司信息合起来作为一张表存储，对每一张发票重复此信息，既浪费时间又浪费存储空间。
在我们对数据操作的过程中，假如某家公司的信息变更了，如公司代号为"E14"的公司违约了，我们在使用公司信息与发票信息分开存储的方法时，只需要在"公司信息表"对"E14"对应的是否违约的字段值修改一次就好；而将两表合成一张表存储时，表中有多少条公司代号为"E14"的发票记录，就要修改多少次，而且若是修改的过程有失误，很有可能造成数据的不一致性！例如本例子中有2条记录要被改，但是若是只改了其中一条，导致同一公司代号对应的是否违约的值不一致。

到这里，我想我们就不会纠结"为什么要把现实事物的抽象分解为多个表来存储"了，而顺其自然地，为了将多个表的信息联系起来进行分析就有了join操作。接下来也可以具体的讨论一下什么是主键了，什么是外键了：

以"发票信息表"为例，每张发票以"发票号码"作为唯一标识进行区分，该标识就被称为主键；同理，以"企业信息表"为例的话，每家公司以"公司代号"就是主键，当然因为该表中每条记录的"公司名称"也是唯一的，也可以考虑作为主键。
前面讲到在进行join操作时，需要两个表有 “共同的字段”，但是这个描述并不严谨，因此为了严谨的描述，我们引入外键的概念：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

二、有那些常见的join的类型？

提示：已经了解了join类型的朋友可以跳过该部分。

本部分主要以集合关系的角度来讨论不同join的区别：现在假设有两个表，分别名为table1和table2，则：

内连接是最常见的一种连接，只连接匹配的行，即取 table1于table2的交集。

inner

LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。

left

RIGHT JOIN返回右表的全部行和左表满足ON条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。

right

FULL JOIN 会从左表和右表那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替.

full
大多时候我们需要join常用的场景也就上面4种join方式，其中最常用的是内连接inner-join和左外连接left-outer-join，left-outer-join 与 right-outer-join 的原理几乎是一样的，而left-outer-join得到的结果相当于在inner-join的基础上，并(Union)上table1独有的主键+NULL值组成的记录，因此深入理解left-outer-join的实现方法非常重要！！！所以接下来我们分别就如何使用SQL、MapReduce、Spark RDD、Spark DataFrame、Spark SQL来实现左外连接进行讨论。

提示：SQL中join的类型默认为inner-join内连接。
提示：本部分内容借鉴自: CSDN博主枫哥和java的博客：SQL的各种连接Join详解，都需要熟练掌握！。

三、使用SQL实现左外连接：

提示：能使用SQL实现左外连接的朋友可以跳过。

因为SQL是专门的数据库分析语言，而join操作又和关系型数据库紧紧相关，加上SQL简介明了，容易上手，所以后续出现的如Hive，Spark SQL等大数据开发工具都提供了SQL的语法接口，因此本篇博客在实现左外连接时还是从单独的SQL怎么实现开始讲起：

1.数据准备

data

2.实现案例

假设我们有如上两个表，图中红色线条圈出的部分分别是两表独有的"企业代号"所对应的记录，假设我们要得到每张有效发票对应"发票号码",“企业代号”,“企业代号名称"以及"价税合计”，价税合计=金额-税额，则对应的SQL语句为：

#SQL-1
SELECT 发票号码,`个体户发票记录`.企业代号,企业名称,(金额-税额) AS 价税合计		#选取哪些字段
FROM  `个体户发票记录` LEFT JOIN `个体户信息`								#从什么地方
ON `个体户信息`.`企业代号`=`个体户发票记录`.`企业代号`				#外键的对应关系
WHERE `个体户发票记录`.`发票状态`="有效发票"						#过滤条件

SQL-1的运行结果如下：

在这里插入图片描述
从上图我们不难发现，因为表1中没有企业代号为"E81"的记录，所以其对应的公司名称为NULL。同时SQL实现左外连接的核心语句是：

#1.使"个体户发票记录"表左外连接到"个体户信息"表
FROM 个体户发票记录 LEFT JOIN 个体户信息
#2.当"个体户信息"表的"企业代号"的值与"个体户发票记录"表的"企业代号"的值相等时
ON 个体户信息.企业代号=个体户发票记录.企业代号

我们可以进一步加大难度——在上述实现了左外连接和创建计算字段的基础上，实现按企业代号分组聚合计算出每个企业代号对应的"平均价税合计"，并显示出"发票代号"和"企业名称"，具体实现的语句如下：

SELECT 发票号码,`个体户发票记录`.企业代号,企业名称,AVG(金额-税额) AS 平均价税合计 #选取哪些字段
FROM  `个体户发票记录` LEFT JOIN `个体户信息`								#从什么地方
ON `个体户信息`.`企业代号`=`个体户发票记录`.`企业代号`		#外键的对应关系
WHERE `个体户发票记录`.`发票状态`="有效发票"				#过滤条件
GROUP BY 企业名称;									#按名称进行分组

SQL-2的运行结果如下：

在这里插入图片描述
对比SQL-1和SQL-2可以发现，SQL-2中，企业代号为"E45"的记录只剩一条了，因为其SQL-2进行了分组聚集，而其它"企业代号"的对应的记录没有变化是因为表2的原始数据过少，在过滤出有效发票后就仅仅剩一条数据了，这一点去看表2的原始数据就可以发现。

在此使用较少数据的目的是为了让读者对左外连接有一个较为清晰的认识，但同时因为数据量不够，涉及分组聚合时，给人的感觉就不直观了。因此接下来在使用mapreduce实现左外连接时，我会更换数据量更大的数据源。

四、使用MapReduce实现左外连接：

在看完上述使用SQL实现左外连接的例子后，相信大家已经知道什么时候需要用到左外连接,以及左外连接的内涵了，但是对于具体的内部细节是不大清楚的，所以这里我们使用mapreduce从底层来自己实现LeftOuterJoin，加深我们对其内部细节的理解。

当然在常规的大数据生产环境中mapreduce使用的并不多，用的最多的还是Spark DataFrame以及Spark SQL，不过使用mapreduce有助于我们更好的理解分布式计算框架的原理，进而学会程序调优。

1.数据准备:

因为mapreduce处理的是文件，所以原始数据存储于文件中：

发票信息：

上图文件存储了公司代号为"E14"、“E45”、“E78"的公司的所有发票信息共7729条，其中第一行记录为表头，若将其视为关系表，则其主键为"发票号码”。
个体户信息：

上图文件存储了公司代号为"E14"、“E45”、“E78"的公司的所对应的公司信息共3条，第一行记录为表头，若将其视为关系表，则其主键为"企业代号”。

2.案例及设计思路：

题目： 假设我们要对上述两个文件按企业代号进行连接操作，同时利用 “金额”-“税额”=“价税合计” 的公式新建 “价税合计”*字段，然后按企业代号进行分组聚合求价税合计平均值的操作，得到格式为 “企业名称，平均价税合计”*的结果。
思路： 在设计mapreduce程序时，首先要考虑的是如何将任务高度抽象为map()和reduce()，而在设计map()时要重点考虑：如何过滤出自己需要的数据？如何设计key来对进行分组？而在reduce设计时要重点考虑：我们期望的最终计算结果是什么？如何将聚集在一起的values聚合成期望的计算结果？针对这些问题，本案例的思路如下：

map()：

同时处理"个体户发票记录.txt"文件和"个体户信息.txt"，利用FileSplit.getInputSplit().getPath().getName()来获取map()当前处理的文件名；
假如当前处理的文件是"个体户信息.txt"（为了后续方便称乎，简称为"表a"），将文本记录按分隔符分割后取第一个和第二个字段，即"企业代号"和"企业名称"，取"企业名称"的值，并在前加上"a#"得到最终的文本字符串，其中"a#"为标识符，表示该文本记录来自表a。取"企业代号"的值作为key写入context用来分组，对应的value为之前操作得到的文本字符串；
假如当前处理的文件是"个体户发票记录.txt"（为了后续方便称乎，简称为"表b"），将文本记录按分隔符分割后取第2、4、5、6个字段，即"企业代号"、“金额”、“税额”、“发票状态”，取“发票状态”的值，判断是否为有效发票，不是则过滤掉，若是则取"金额"、“税额"的值，用”#"分割开，并在前加上"b#"得到最终的文本字符串，其中"b#"为标识符，表示该文本记录来自表b。取"企业代号"的值作为key写入context用来分组，对应的value为之前操作得到的文本字符串；

reduce()：

经过map聚集后，每个reduce对应的key为"企业代号"的文本，其对应的values是格式为："标识符#字段1#…#字段i"的一条条文本记录形成的可迭代对象。
我们定义两个空的容器类vecA和vecB（可以使用ArrayList，也可以使用Vector，二者都是线性表）用来分别存储来自"表a"和"表b"的数据。
遍历values，借助标识符"a#"和"b#“判断每一个元素是来自"表a"还是"表b”。
若当前元素来自"表a"，我们取该元素第二个字符往后的所有文本记录作为一个String类型的元素添加进vecA；
若当前元素来自"表b"，我们按"#“分割该元素得到一个String数组，取该数组的第2、3个元素转换为Double做减法运算，得到当前发票对应的“价税合计”值jshj。假设题目中不进行分组聚合操作，而只是求每一张发票对应的"公司名称"和"价税合计”，则可以直接将jshj添加进vecB；但是题目要求按企业代号分组聚合求各企业代号的价税合计平均值，因此在对values进行迭代前要在循环外，设置变量int count=0；用来统计当前"公司代号"对应的发票数量，设置变量Double sum=0.0；用来累加每张发票对应的价税合计值，在遍历结束后使用sum/count计算出"平均价税合计"，并添加进vecB。
当完成上述操作后，要做的事就很简单了——利用双层循环遍历vecA，遍历vecB，将二者的元素连接为字符串写入上下文，输出的就是对两个文件执行左外循环的结果。（如果要改成内循环的话，可以在vecB.size()=0时结束本次reduce()的调用）

本着“一图胜千言”的原则，截选部分数据，将上述思路用图画出：

map的实现思路：(1)和(2)皆由Mapper类MapJoin实现，实现时在map()函数内取当前处理文件的名称进行判断从而使用分支语句针对不同的文件进行处理
map1
map2

shuffle的过程展示：因为MapReduce编程中，shuffle是隐含的，故按之前选取的数据展示在map()结束后的shuffle过程。

shuffle

reduce设计思路图：重点展示了reduce()的输入输出，以及ReduceJoin和ReduceJoin2的区别。具体的实现内部细节参考之前的文字描述，以及代码。（代码注释较全，推荐看代码）

reduce

3.运行代码：

Mapper：MapJoin

import java.io.IOException;

import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapred.Reporter;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.lib.input.FileSplit;

public class MapJoin extends Mapper<LongWritable, Text, Text, Text>{
	
	protected void map(LongWritable key, Text value, Context context)
			throws IOException, InterruptedException {
		
		//if(key.toString().equals("0")) return; //跳过表头(即第一行记录)
		String line=value.toString();	//获取当前记录字符串
		
		FileSplit inputSplit = (FileSplit) context.getInputSplit();//获取输入分区
		String filePath = inputSplit.getPath().getName();	//获取当前输入分区的文件名
		if(filePath.contains("个体户信息")) {	//如果当前输入分区的文件名包含表A-"个体户信息"
			String[] record = line.split("	"); //分割当前记录字符串
			String id=record[0];				//取`公司代号`字段的数据
			String name=record[1];				//取`公司名称`字段的数据
			context.write(new Text(id), new Text("a#"+name)); //使用"a#"作为标识符，结合上述字段数据写入上下文
		}
		if(filePath.contains("个体户发票记录")) {//如果当前输入分区的文件名包含表B-"个体户发票记录"
			String[] record = line.split("	"); //分割当前记录字符串
			String id=record[1];				//取`公司代号`字段的数据
			String income=record[3];			//取`金额`字段的数据
			String tax=record[4];				//取`税额`字段的数据
			String cardState=record[5];			//取`发票状态`字段的数据
			if(cardState.equals("有效发票")) {	//过滤出`发票状态`字段为"有效发票"的数据
				context.write(new Text(id), new Text("b#"+income+"#"+tax));	//添加"a#"作为标识符写入上下文
			}
			
		}
		
	}
}

Reducer1：ReduceJoin —— 去表头，不分组聚集合，仅求价税合计

import java.io.IOException;
import java.util.Vector;

import org.apache.hadoop.io.DoubleWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Reducer;

public class ReduceJoin extends Reducer<Text, Text, Text, DoubleWritable> {
	/**
	 * @param key 企业代号的值
	 * @param values 根据key聚集的"a#企业名称"或"b#金额#税额"的集合
	 * @param context reduce的上下文
	 * <p>输出：|企业名称|价税合计|  #注：价税合计=金额-税额<p>
	 */
	protected void reduce(Text key, Iterable<Text> values, Reducer<Text, Text, Text, DoubleWritable>.Context context)
			throws IOException, InterruptedException {

		Vector<String> vecA = new Vector<String>(); // 存放来自表A的值
		Vector<Double> vecB = new Vector<Double>(); // 存放来自表B的值

		for (Text value : values) {
			String line = value.toString();
			if (line.startsWith("a#")) {//数据来自表a,取其"a#"后有意义的字符串，即企业名称；
				vecA.add(line.substring(2));
			} 
			else if (line.startsWith("b#")) {//数据来自表b,取其"a#"后有意义的字符串，并计算价税合计；
				String[] data=line.split("#");
				double income=Double.parseDouble(data[1]);
				double tax=Double.parseDouble(data[2]);
				double jshj=income-tax;
				vecB.add(jshj);
			}
		}
		
		int i, j;
		for (i = 0; i < vecA.size(); i ++) {
			for (j = 0; j < vecB.size(); j ++) {
				context.write(new Text(vecA.get(i)),new DoubleWritable(vecB.get(j)));
			}
		}	
	}
}

Reducer2：ReduceJoin2 —— 不去表头，分组聚集合，求平均价税合计

import java.io.IOException;
import java.util.Vector;

import org.apache.hadoop.io.DoubleWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Reducer;

public class ReduceJoin2 extends Reducer<Text, Text, Text, DoubleWritable> {
	/**
	 * @param key 企业代号的值
	 * @param values 根据key聚集的"a#企业名称"或"b#金额#税额"的集合
	 * @param context reduce的上下文
	 * <p>输出：|企业名称|价税合计的平均值|<p>#注：价税合计=金额-税额 <p>
	 */
	protected void reduce(Text key, Iterable<Text> values, Reducer<Text, Text, Text, DoubleWritable>.Context context)
			throws IOException, InterruptedException {

		Vector<String> vecA = new Vector<String>(); // 存放来自表A的值
		Vector<Double> vecB = new Vector<Double>(); // 存放来自表B的值

		int count=0;
		double sum=0;
		for (Text value : values) {
			String line = value.toString();
			if (line.startsWith("a#")) {//数据来自表a,取其"a#"后有意义的字符串，即企业名称；
				vecA.add(line.substring(2));
			} 
			else if (line.startsWith("b#")) {//数据来自表b,取其"a#"后有意义的字符串，并计算价税合计；
				String[] data=line.split("#");
				double income=Double.parseDouble(data[1]);
				double tax=Double.parseDouble(data[2]);
				double jshj=income-tax;
				sum=sum+jshj;
				count++;
			}
		}
		double avg=sum/count;
		vecB.add(avg);
		
		int i, j;
		for (i = 0; i < vecA.size(); i ++) {
			for (j = 0; j < vecB.size(); j ++) {
				context.write(new Text(vecA.get(i)),new DoubleWritable(vecB.get(j)));
			}
		}	

	}
}

Main：MainJoin —— mapreduce程序入口

import java.io.IOException;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.DoubleWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;

public class MainJoin {
	public static void main(String args[]) throws IOException, ClassNotFoundException, InterruptedException {
		Configuration conf = new Configuration();

		if (args.length != 2) {
			System.err.println("Usage: Data Deduplication <ThePathOf-TableAandB>  <outPath>");
			System.exit(2);
		}
		
		Job job = Job.getInstance(conf, "leftJion");
		job.setJarByClass(MainJoin.class);

		// 设置Map和Reduce处理类型
		job.setMapperClass(MapJoin.class);
		job.setReducerClass(ReduceJoin2.class);	//也可为ReduceJoin.class

		// 设置输出类型
		job.setMapOutputKeyClass(Text.class);
		job.setMapOutputValueClass(Text.class);
		job.setOutputKeyClass(Text.class);
		job.setOutputValueClass(DoubleWritable.class);

		// 设置输入和输出目录
		FileInputFormat.addInputPath(job, new Path(args[0]));
		FileOutputFormat.setOutputPath(job, new Path(args[1]));
		
		// 添加提示信息
		boolean result = job.waitForCompletion(true); /* true表示将运行进度等信息及时输出给用户，false的话只是等待作业结束 */
		System.exit(result ? 0 : 1);
		System.out.println(result ? "执行成功" : "执行失败");
		
	}

}

4.运行结果：

Reducer使用ReduceJoin，且在map()中去除第一行文本记录的结果：每张发票对应的"公司名称"和"价税合计"，共7171条（不是7729条，是因为"发票状态"为"作废发票"的记录被过滤了）。

个体经营E14 140.3
个体经营E14 -164.26999999999998
个体经营E14 29.000000000000004
个体经营E14 204.29
个体经营E14 218.66000000000003
…………………………………………
个体经营E45 1035.94
个体经营E45 640.4000000000001
个体经营E45 2359.1000000000004
个体经营E45 212.82000000000002
个体经营E45 1956.96
…………………………………………
个体经营E78 80375.22
个体经营E78 100577.56
个体经营E78 34385.08
个体经营E78 16282.86
个体经营E78 164316.12
个体经营E78 798.1199999999999

Reducer使用ReduceJoin,且在map()中不去表头的结果，：每家公司对应的"公司名称"和"平均价税合计"，共4条（其中仅有3家公司，因为故意没有去表头，以此通过结果验证是LeftOuterJoin而不是innerJoin，所以其中一条记录为"企业名称 NaN"）
注：去掉表头只需在map()的第一句加if(key.toString().equals("0")) return; 即可

个体经营E14 14952.102043818055
个体经营E45 35088.621385135135
个体经营E78 51733.15269230771
企业名称 NaN

五、使用Spark RDD的API进行左外连接

之前在讲MapReduce实现LeftOuterJoin时讲过，因为MapReduce过于底层开发效率不高。此外使用MapReduce进行开发时，还有以下缺点：

需要将任务高度抽象为map()和reduce()，因此灵活性较差。
MapReduce操作的对象是文件，没有结构，不容易操作。
中间结果是保存到磁盘中的，当内存足够的时候，会产生大量无效的I/O操作浪费计算资源。
MapReduce在shuffle的过程中会自动完成分组聚集以及合并排序，这个特点让MapReduce在进行需要分组和排序操作的任务时变的有弹性（有弹性指的是随着数据量变大，甚至超出了单节点的承受范围时，集群依旧能在数据量对应的线性时间完成该任务）；但是另一方面，这个特点会使不需要进行分组和排序的任务增加程序的开销。

因为上述原因，MapReduce在用实际开发场景中的比较少。而【算子众多、基于内存、管道化惰性执行】的spark的弹性分布式数据集RDD，一定程度上解决第【1、3、4】个问题；至于第2个问题Spark也提供了Spark DataFrame和Spark SQL的解决方案。在这里我们先举例讲解如何调用Spark RDD的API来实现左外连接。

1.案例数据与任务：

我们取与用MapReduce实现LetfOuterJoin的一样的数据和任务要求，即数据源为"个体户信息.txt"和"个体户发票记录.txt"，任务要求为：对两个源数据进行左外连接，并选取出"企业名称，价税合计"，以及“企业名称，平均价税合计”，前者不需要分组聚合，后者需要。

2.思路分析及实现：

读取文件"个体户信息.txt"转化为为RDD：TableA，读取"个体户发票记录.txt"转化为RDD：TableB。

/*1.读取输入文件*/
val tableA = sc.textFile("file:///D:/xmgl/data/join/个体户信息.txt")
val tableB = sc.textFile("file:///D:/xmgl/data/join/个体户发票记录.txt")

过滤掉TableA和TableB的表头得到新的RDD：a0和b0。

/*2.去掉表头 */
val headerA= tableA.first()
val headerB = tableB.first()
val a0 = tableA.filter(_ != headerA)
val b0 = tableB.filter(_ != headerB)

过滤a0和b0中的无关字段，得到a1和b1；其中a1过滤无关字段后的仅剩字段"企业代号"和"企业名称"，其类型为RDD[(String,String)]为pairRDD。

/*3.将文件文本记录按","分割，并过滤无关字段获取初始的表a1和表b1*/
val a1=a0.map(line=>line.split(",")).map(x=>(x(0),x(1)))
val b1=b0.map(line=>line.split(",")).map(x=>(x(1),x(3),x(4),x(5)))
a1.take(5)		//查看过滤无用字段后，表a1的内部细节
b1.take(5)	//查看过滤无用字段后，表b1的内部细节

过滤掉b1中"发票状态"为"作废发票"的记录，并将"企业代号"作为Key，以此得到新的pairRDD：b2。

/*4.过滤保留b1中，发票状态为"有效发票"的记录，得到表b2*/
val b2=b1.filter(_._4=="有效发票").keyBy(_._1)

再得到两个pairRDD（即a1和b2）后，即可调用pairRDD的leftOuterJoin的API进行左外连接，此时两个pairRDD依据key进行左外连接得到新的RDD：a1b2，此时a1b2的类型为RDD[(String, (String, Option[(String, String, String, String)]))]，对应元素的逻辑为 (企业代号,(企业名称,Opition((企业代号,金额,税额，有效发票)))) 。可以发现返回结果为RDD[(外键，(左表的所有values，Opition[右表的所有values]))]。

/*5.执行左外连接操作*/
val a1b2=a1.leftOuterJoin(b2)		//调用pairRDD封装好的左外连接API，连接a,b表
a1b2.take(5)	//查看a1左外连接b2后得到的rdd内部细节

我们的结果只需要"企业名称"和“价税合计”，因此我们可以保留外键，利用mapVaules()过滤掉不需要的字段，同时基于"金额"和"税额"计算出"价税合计"，得到新的RDD:jshj，其类型为RDD[(String, (String, Double))]，对应元素的逻辑为 (企业代号,(企业名称,价税合计))。

/*6.从Option数据类型中提取"企业名称"和"价税合计"*/
val jshj=a1b2.mapValues(x=>(x._1,x._2.toArray)).mapValues(x=>(x._1,x._2(0)._2,x._2(0)._3)).mapValues(x=>(x._1,x._2.toDouble-x._3.toDouble))

求每个公司的价税合计，因为每个“公司代号”对应一个唯一的“公司名称”，因此可以将jshj的元素变为 (企业名称，(价税合计，1))，然后利用reduceByKey()进行聚集，先求出每个公司对应的(企业名称，(价税合计总和，发票数))，然后再利用mapValues算子和 “平均价税合计”=“价税合计总和”/“发票数” 的计算公式求出"平均价税合计"。

/*7.分组聚合，求出每个公司的平均价税合计*/
val result=jshj.map(x=>(x._2._1,(x._2._2,1))).reduceByKey((a,b)=>(a._1+b._1,a._2+b._2)).mapValues(x=>x._1/x._2)

3.重要RDD输出展示：

scala>jshj.foreach(println)

返回结果：每张发票的对应"公司名称"和"价税合计"。

(E14,(个体经营E14,199.14))
(E14,(个体经营E14,30.0))
(E14,(个体经营E14,279.48))
(E14,(个体经营E14,434.48))
(E14,(个体经营E14,106.94))
……………………………………

提示：其实这种输出结果很容易产生歧义，因为每条记录是针对每一张发票的，但是却隐去了发票的标识，因此看上去像会很奇怪，所以最好补充上"发票号码"对每条记录进行区别。（此处忽略是为了方便后续分组聚集操作）

scala> result.foreach(println)

返回结果：每家公司的对应"公司名称"和"平均价税合计"。

(个体经营E45,35088.621385135186)
(个体经营E78,51733.1526923077)
(个体经营E14,14952.102043818186)

六、使用Spark DataFrame的API进行左外连接

在【五、使用Spark RDD的API进行左外连接】中，我们提到了Spark RDD无法解决MapReduce不便于处理结构化数据的问题，因此Spark RDD为了解决这个问题引入了Spark DataFrame。我们可以把DataFrame看作是一种特殊的RDD，它比RDD多了一个表头，内部细节可见，代码可读性更高，开发效率也更高。其即类似pandas.DataFrame，也类似关系型数据库的关系表，因此在实现左外连接时会较之前几种方法更符合人脑逻辑。

提示1：Spark的DataFrame和Python的pandas的DataFrame除了不能切片以外有很多相似点。对于有python基础的同学，可以将RDD和Spark.DataFrame类比numpy和pandas，比较它们的不同点，进而加深理解。

提示2：Spark的DataFrame除了借鉴pandas外，还借鉴了SQL，所以其有类似pandas操作的API和类似SQL的API；但是因为Spark DataFrame的本质还是一种特殊的RDD，所以这些API的本质还是RDD的API，底层逻辑也是由RDD实现的。

1.案例数据与任务：

我们取与用Spark RDD实现LetfOuterJoin的一样的数据和任务要求，即数据源为"个体户信息.txt"和"个体户发票记录.txt"，任务要求为：对两个源数据进行左外连接，并选取出"企业名称，价税合计"，以及“企业名称，平均价税合计”，前者不需要分组聚合，后者需要。

2.思路分析及代码实现：

使用Spark DataFrame时，要先构建出Spark DataFrame，其构建方式非常多样，这里我们选择基于RDD，通过构建case class，借助case calss将RDD隐式推导为Spark DataFrame的方法。

读取文件"个体户信息.txt"转化为为RDD：TableA，构建 case class Info 来将TableA变成RDD[Info]后隐式推导为DataFrame：dfA；读取"个体户发票记录.txt"转化为RDD：TableB，构建 case class Record 来将TableB变成RDD[Record]后隐式推导为DataFrame：dfB；

/*0-0.导入使用Spark SQL需要的模块*/
import org.apache.spark.sql.catalyst.encoders.ExpressionEncoder
import org.apache.spark.sql.Encoder
import spark.implicits._

/*0-1.建立表A和表B在隐式推到时需要的类*/
case class Info(企业代号:String,企业名称:String,信誉评级:String,是否违约:String)
case class Record(发票号码:String,企业代号:String,销方单位代号:String,金额:Double,税额:Double,发票状态:String)

/*1.将读取文件得到的RDD，在进行字段的过滤后，构造出表对应的类，转换为DataFrame*/
val dfA=a0.map(_.split(",")).map(x=>Info(x(0),x(1),x(2),x(3))).toDF()
val dfB=b0.map(_.split(",")).map(x=>Record(x(0),x(1),x(2),x(3).toDouble,x(4).toDouble,x(5))).toDF()
dfA.show(5)	//查看dfA的前五行记录
dfB.show(5)	//查看dfB的前五行记录

使用DataFrame的API对dfA和dfB进行左外连接得到新的DataFrame：dfAB。

/*2.使用DataFrame的API对dfA和dfB进行左外连接*/
val dfAB=dfA.join(dfB.select("企业代号","金额","税额").where(dfB("发票状态") like "有效发票"),Seq("企业代号"),"left")
dfAB.show(5)	//查看dfA与dfB左外连接产生的dfAB的前五行记录

使用DataFrame的计算字段，借助金额和税额字段创建新的字段——价税合计。

/*3.使用DataFrame的计算字段，借助金额和税额字段创建新的字段——价税合计*/
val jshj=dfAB.withColumn("价税合计",$"金额"-$"税额").select("企业名称","价税合计")
jshj.show(200)	//查看发票所对应的公司以及价税合计的前200条记录

使用DataFrame进行分组聚合求出价税合计。

val reslut=jshj.groupBy("企业名称").avg()
result.show()	//查看每家公司对应的有效发票的价税合计

3.重要代码运行结果展示：

构造出dfA和dfB以后，分别显示这两个DataFrame的前5条记录：

dfa and dfb

对dfA调用Spark DataFrame的join的API，配置参数，左外连接dfB后得到新的DataFrame：dfAB，显示dfAB的前5条记录：

在这里插入图片描述
Spark DataFrame的join算子的用法：假设有两个DataFrame：dfA和dfB。现在要用dfA连接dfB，则语法为：dfA.join(dfB,Seq(外键序列),"连接方式")。

借助dfAB的"金额"和"税额"字段，使用DataFrame的withColumn的API创建新的计算字段"价税合计"，“价税合计”=“金额”-“税额”。然后在调用类SQL接口select选取"企业名称"和"价税合计"两个字段得到新的DataFrame：jshj，最后显示jshj的前20条数据。

在这里插入图片描述

使用DataFrame的groupBy()算子和avg()算子对jshj进行分组和聚合得到最终的结果——含有每个公司的"公司名称"和"平均价税合计"信息的DataFrame：result，然后显示其所有记录。
在这里插入图片描述

七、使用Spark SQL进行左外连接

通过Spark DataFrame实现左外连接的案例，我们可以发现其开发效率和代码可读性都比MapReduce和Spark RDD高出了很多，那我们还有没有更方便的方法呢？
答案当然是肯定的，为了大力推广Spark，Spark的开发人员充分考虑到了对Spark陌生但是对SQL熟悉的数据库开发工程师的需求，推出了Spark SQL，通过Spark SQL将SQL语句转化为底层RDD进行运算。
在本博客的第三部分专门讲解了如何写SQL来实现左外连接，为的就是为本部分内容铺垫。因为当你内用SQL来实现左外连接后，所需要做的工作就仅仅是把Spark DataFrame注册为临时表，然后调用spark.sql()来输入SQL语句了。

1.案例数据与任务：

我们取与用Spark DataFrame实现LetfOuterJoin的一样的数据和任务要求，即数据源为"个体户信息.txt"和"个体户发票记录.txt"；任务要求为：对两个源数据进行左外连接，并选取出"企业名称，价税合计"，以及“企业名称，平均价税合计”，前者不需要分组聚合，后者需要。

2.思路分析及代码实现：

使用Spark SQL时，因为要先把Spark DataFrame注册为临时表，所以要先构建出Spark DataFrame，该部分代码与【六、使用Spark DataFrame进行左外连接】的第一部分代码完全一致。为了简介，该部分代码省略。
假设已经完成了第一步，构建出了dfA和dfB，此时我们使用createOrReplaceTempView("临时表名")的算子，将dfA和dfB注册为临时表：

dfA.createOrReplaceTempView("A")
dfB.createOrReplaceTempView("B")

将dfA注册为临时表"A"，dfB注册为临时表"B"以后，调用spark.sql()直接传入SQL查询语句，即可求出SQL查询语句所求的内容：

/*3-1.获取每家公司每张有效发票记录的价税合计*/
val jshjSQL=spark.sql("SELECT DISTINCT `企业名称`,`金额`-`税额` AS `价税合计` FROM A  LEFT OUTER JOIN B ON A.`企业代号`=B.`企业代号` WHERE B.`发票状态` LIKE '有效发票' ")
jshjSQL.show(200)	//查看发票所对应的公司以及价税合计的前200条记录

/*3-2.计算每家公司有效发票的平均价税合计*/
val resultSQL=spark.sql("SELECT DISTINCT `企业名称`,AVG(`金额`-`税额`) AS `平均价税合计`  FROM A  LEFT OUTER JOIN B ON A.`企业代号`=B.`企业代号` WHERE B.`发票状态` LIKE '有效发票' GROUP BY `企业名称` ") 
resultSQL.show()	//查看每家公司对应的有效发票的价税合计

提示1：传入spark.sql()里的SQL语句不能包含""，因此SQL中表示字符串时尽量使用''；

提示2：表示表名和字段名是应该使用符号 ` ` 括起来；

提示3：查询是 Spark SQL 的核心功能，Spark SQL 的查询语句模式如下：

[ WITH with_query [, …] ]
SELECT [ ALL | DISTINCT ] select_expr [, …]
[ FROM from_item [, …] ]
[ WHERE condition ]
[ GROUP BY expression [, …] ]
[ HAVING condition]
[ UNION [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, …] ]
[ LIMIT count ]

其中 from_item 为以下之一：

table_name [ [ AS ] alias [ ( column_alias [, …] ) ] ]
from_item join_type from_item [ ON join_condition | USING ( join_column [, …] ) ]

注：【提示3】参考自范东来老师的专栏“ 即学即用的Spark实战44讲”

3.重要代码运行结果展示：

利用spark.sql()查询每张发票的"发票号码"，“公司代号”和其对应的“价税合计”：

val jshjSQL=spark.sql("SELECT DISTINCT `发票号码`,`企业名称`,`金额`-`税额` AS `价税合计` FROM A  LEFT OUTER JOIN B ON A.`企业代号`=B.`企业代号` WHERE B.`发票状态` LIKE '有效发票' ")
jshjSQL.show(20)	//查看发票所对应的公司以及价税合计的前20条记录

在这里插入图片描述
提示：因为使用Spark SQL进行查询时，其改变要查询的数据非常容易，且代价很小，所以为了消除歧义，于Spark SQL的实习左外连接并新建"价税合计"的计算字段的案例中，查询结果还多选取了“发票号码”字段。

利用spark.sql()查询每个公司的“公司代号”和其对应的“平均价税合计”：

val resultSQL=spark.sql("SELECT DISTINCT `企业名称`,AVG(`金额`-`税额`) AS `平均价税合计`  FROM A  LEFT OUTER JOIN B ON A.`企业代号`=B.`企业代号` WHERE B.`发票状态` LIKE '有效发票' GROUP BY `企业名称` ") 
resultSQL.show()	//查看每家公司对应的有效发票的价税合计

在这里插入图片描述
提示1：上一处重要代码的提示说过：“因为使用Spark SQL进行查询时，其改变要查询的数据非常容易，且代价很小。”大家也可以比较一下，此处的代码与上一处的代码有什么区别？——非常明显，此处的代码仅仅是在SELECT语句省略了“发票号码”字段以及在SQL语句最后加上了GROUP BY `企业名称 `。可见Spark SQL的灵活性以及开发的高效性。

提示2：在实际的开发过程中，Spark RDD与Spark DataFrame以及Spark SQL的使用不是的决定固定的，而要根据应用场景选择最合适的方法，而且大多数时候它们是混在一起使用的，例如spark.sql()的返回值是满足查询条件的Spark DataFrame。

某混合使用的简单例子：
1.先从HDFS上读取普通的文本文件生产RDD：fileRDD
2.构建case class，将上一步得到的RDD转化为Spark DataFrame：fileDF
3.将fileDF注册为临时表，使用Spark SQL查询得到一个新的DataFrame：sqlDF
4.将sqlDF调用DataFrame的API按某字段进行分组聚合（计数）的操作，得到新的DataFrame：result

八、总结

至此，本篇博客的主要内容讲述完毕，必须得承认这篇博客有很多不足的地方，比如文字的描述，案例的选取，内容的安排，以及页面的排版等，还请读者见谅，接下来我们进行总结回顾。

1. 关于join

join的定义：用于根据两个或多个表中的列之间的关系，从这些表中查询数据的操作。
join的种类：内联接，左外联接，右外联接，全联接；可以从集合的角度更直观的理解。

2.关于leftOuterJoin的实现方式

SQL是最轻松，最简单的，但是其只能运用与传统的关系型数据库，本身不支持大数据的计算场景。
MapReduce是本博客用来实现leftOuterJoin的大数据解决方案中最靠近底层逻辑的（因为没有把leftOuterJoin封装成接口），也是开发效率和执行效率最低下的，但是了解底层逻辑有利于我们写出性能更好的Spark程序。同时也要知道MapReduce有哪些缺点，其它大数据计算框架和工具是怎么解决的。
Spark RDD是Spark最基础也是最核心的操作对象：
- 它通过丰富的算子和依据血缘关系构建DAG图在分解任务的方法，解决了MapReduce灵活性差和开发效率低的问题（pairRDD可以直接调用leftOuterJoin的算子进行左外连接）；
- 它基于内存，解决了MapReduce基于磁盘产生了大量无用I/O操作浪费资源的问题；
- 它管道化惰性执行，解决了MapReduce执行效率底下的问题；
Spark DataFrame是一种特殊的Spark RDD，在其基础上增加了表头，且内部细节透明，是对Spark RDD处理结构化数据的补充，能大幅度提示开发效率。
Spark SQL可以理解为Spark开发人员为了方便传统的数据库开发工程师使用Spark而推出的SQL接口，即使传入SQL语句,Spark SQL将其转化为Spark RDD的底层逻辑。

总结回顾结束，最后回到我写这篇博客的初衷：希望读者能够了解什么是join操作，了解join有哪些类型，了解如何使用SQL、MapReduce、Spark RDD、Spark DataFrame、Spark SQL来实现左外连接(leftOuterJoin)，并且比较他们之间的区别是什么。
若是读者能对上面的内容都有所理解了，那么本篇博客便是成功的，感谢您的观看。（当然一篇博客浓缩那么多内容，还讲那么啰嗦，从内容安排上来说是非常不合理的，很容易劝退读者，所以博主也没有报太大的期望。~~hhh,毕竟大概率也不会有什么人看，就当是练手了~~ ）

结语：因为本人的spark基础也不是很牢固，本篇博客参考其它朋友的内容不少，博客中在引用的地方都做出了声明，如有遗漏欢迎指出和补充；此外若出现了技术上或者描述上的错误，也欢迎读者私信交流讨论。

Ahaxian

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
左外连接Left-Outer-Join的基于SQL，MapReduce，sparkRDD，sparkDataFrame以及spark SQL的实现案例及对比

提示：阅读本文需要部分java,scala,spark,sql的基础。文章目录前言一、什么是join操作?二、有那些常见的join的类型？三、使用SQL实现左外连接：1.数据准备2.实现案例四、使用MapReduce实现左外连接：1.数据准备:2.案例及设计思路：3.运行代码：4.运行结果：总结前言概要：本博客截取2020年数学建模国赛C题提供的部分数据作为数据源，联系现实中的需要用到join操作的场景，分别使用sql，mapreduce，spark RDD，spark DataFrame，以及sp
复制链接

扫一扫