精华内容
下载资源
问答
  • 背景join是SQL中最常见的操作,写SQL最经常的场景就是几张表各种joinjoin操作也是各种操作中最耗时的操作之一。作为一个Spark SQL Boy,有必要详细了解一下Spark的join策略。MR中的join介绍Spark的join策略之前,...

    背景

    join是SQL中最常见的操作,写SQL最经常的场景就是几张表各种join,join操作也是各种操作中最耗时的操作之一。

    作为一个Spark SQL Boy,有必要详细了解一下Spark的join策略。

    MR中的join

    介绍Spark的join策略之前,先介绍一下MR中是如何实现join操作的。

    MR中的join分为Map端join和Reduce端join。

    数据准备如下:

    订单表

    1001  01  11002  02  21003  03  31004  01  41005  02  51006  03  6

    商品表

    01  小米02  华为03  格力

    Map join:

    1. 加载阶段:把小表加载到内存里面;

    2. Hash join:构建Hash表做查询。

    只在Map端处理数据,没有Reduce,优点是并行度非常高,没有Shuffle,不会出现数据倾斜。

    Mapper类

    public class JoinMapper extends Mapper {    HashMap<String,String> pdMap=new HashMap<>();    TableBean k = new TableBean();    @Override    protected void setup(Context context) throws IOException, InterruptedException {        URI[] cacheFiles = context.getCacheFiles();        String path = cacheFiles[0].getPath().toString();        InputStreamReader inputStream = new InputStreamReader(new FileInputStream(path));        BufferedReader bis = new BufferedReader(inputStream);        String line;        while((line=bis.readLine())!=null){            String[] fields = line.split("\t");            pdMap.put(fields[0],fields[1]);        }        bis.close();        inputStream.close();    }    @Override    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {        String line = value.toString();        String[] fields = line.split("\t");        String pId = fields[1];        String pdName = pdMap.get(pId);        k.setAmount(Integer.parseInt(fields[2]));        k.setId(fields[0]);        k.setPid(fields[1]);        k.setPname(pdName);        k.setFlag("order");        context.write(k, NullWritable.get());    }}

    Reduce join:

    1. Map阶段:分片做循环,join的key为进入Reduce Task的key,value是一个对象;

    2. Reduce阶段:同一个key进入同一个Reduce Task,封装输出对象。

    Mapper类

    public class TableBeanMapper extends Mapper<LongWritable, Text, Text, TableBean> {    FileSplit filesplit;    String name;    TableBean v = new TableBean();    Text k = new Text();    @Override    protected void setup(Context context) throws IOException, InterruptedException {        InputSplit inputSplit = context.getInputSplit();        filesplit = (FileSplit) inputSplit;        Path path = filesplit.getPath();        name = path.getName();    }    @Override    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {        String s = value.toString();        if (name.startsWith("order")) {            String[] split = s.split("\t");            v.setId(split[0]);            v.setPid(split[1]);            v.setAmount(Integer.parseInt(split[2]));            v.setPname("");            v.setFlag("order");            k.set(split[1]);        } else {            String[] split = s.split("\t");            v.setId("");            v.setPid(split[0]);            v.setAmount(0);            v.setPname(split[1]);            v.setFlag("pd");            k.set(split[0]);        }        context.write(k, v);    }}

    Reducer类

    import org.apache.commons.beanutils.BeanUtils;import org.apache.hadoop.io.NullWritable;import org.apache.hadoop.io.Text;import org.apache.hadoop.mapreduce.Reducer;import java.io.IOException;import java.lang.reflect.InvocationTargetException;import java.util.ArrayList;public class TableBeanReducer extends Reducer<Text, TableBean, TableBean, NullWritable> {    @Override    protected void reduce(Text key, Iterable values, Context context) throws IOException, InterruptedException {        ArrayList tableBeanArrayList = new ArrayList();        TableBean tableBean = new TableBean();        for (TableBean value : values) {            if (value.getFlag().equals("order")) {                TableBean tableBean1 = new TableBean();                try {                    BeanUtils.copyProperties(tableBean1, value);                    tableBeanArrayList.add(tableBean1);                } catch (IllegalAccessException e) {                    e.printStackTrace();                } catch (InvocationTargetException e) {                    e.printStackTrace();                }            } else {                try {                    BeanUtils.copyProperties(tableBean, value);                } catch (IllegalAccessException e) {                    e.printStackTrace();                } catch (InvocationTargetException e) {                    e.printStackTrace();                }            }        }        for (TableBean bean : tableBeanArrayList) {            bean.setPname(tableBean.getPname());            context.write(bean, NullWritable.get());        }    }}

    Spark join策略

    Spark SQL执行引擎最常使用的3种策略,分别是Broadcast Hash join,Shuffle Hash join和Sort merge join,剩下两种分别是Nested loop join和Cartesian product join。

    Broadcast Hash join

    相当于MR中的Map join,分为两个阶段。

    1. 广播阶段:把小表广播到大表数据所在的executors上

    2. Hash join:在每个executor上做Hash join

    Broadcast Hash join计算发生在本地的executors上,不需要shuffle,并行度也非常的高。

    Spark通过spark.sql.autoBroadcastJoinThreshold参数(默认10MB),将小表广播出去。

    以下是某个Spark SQL的执行计划,在该SQL中Spark选择logdate_xxx_t2表作为广播表。

    == Physical Plan ==Execute CreateHiveTableAsSelectCommand CreateHiveTableAsSelectCommand [Database:default}, TableName: logdate_xxx_dwd, InsertIntoHiveTable]+- *(1) Project [x3#29, x4#30, x5#31, x6#33, CASE WHEN isnotnull(x7#35) THEN 1 ELSE 0 END AS flag#26]   +- *(1) BroadcastHashJoin [x4#30, x3#29], [x4#35, x3#34],    LeftOuter, BuildRight      :- HiveTableScan [x3#29, x4#30, x5#31, x6#33], HiveTableRelation `default`.`logdate_xxx_ods`,       org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [x3#29, x4#30, x5#31, x6#33], [x6#33]      +- BroadcastExchange HashedRelationBroadcastMode(List(input[1, string, true], input[0, string, true]))         +- HiveTableScan [x1#34, x2#35], HiveTableRelation          `default`.`logdate_xxx_t2`,          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [x1#34, x2#35]

    Shuffle Hash join

    1. shuffle阶段:根据join的key,把参与join的表的数据分为若干个区(默认使用的是hash partitonner),使得两个表相同的key进入同一个partition;

    2. Hash join阶段:每个分区做基本的Hash join。

    ae40ccb68d429b8f829e626a2d131ff1.png

    02d16622ed43e495284ee7454678b9fa.png

    Shuffle Hash join存在数据倾斜的问题。数据倾斜是在实践中碰到最多的问题之一。

    5b1f4a036e803d5a2313829a4a32c491.png

    如何使得Shuffle Hash join更高效?首先要明白Shuffle Hash join的瓶颈在哪里:

    1. 数据分布,准确来说应该是参与join的key的分布,对Shuffle Hash join性能有极大的影响,具体表现在大部分Task执行的速度非常快,而某个Task执行地非常慢,Spark UI中可以观察到该任务Shuffle的数据量非常的大

    2. Shuffle Hash join存在并行度不够的问题。

    databricks的工程师在演讲中提到如下几种方法:

    Shuffle Hash join有非常多优化的手段,比如在数据储存方面,Spark 推荐使用parquet文件格式,parquet文件格式能够让Spark非常高效自动地识别用Shuffle join还是Broadcast join,而且parquet格式对重复数据的encoding非常地友好,输出的数据量会小一点;Spark默认的并行度为200,把key的分区重新打乱,增大并行度;Spark的新特性adaptive shuffling,动态地调整执行计划和分区数。

    417bb7360690b5f7d9ea3737e0551ab0.png

    Sort Merge join

    跟MR中Reduce join非常相似,回顾MR的执行过程。

    4d3178d7ef12cc87b224387905b8aa2b.png

    1. shuffle阶段:跟Shuffle Hash join一样;

    2. sort阶段:每个分区对key进行排序(MR框架中,默认会在溢写阶段对分区内的数据进行排序,这是为了方便后续同一个key进入一个分区);

    3. merge阶段:两个sorted 的数据进行查找,线性复杂度。

    Spark SQL 默认的join策略为Sort Merge join。该策略要求key要可排序,基本数据类型都是可排序的。

    当Hash表无法放入内存或者构建Hash表的时间比排序时间长的时候,使用Sort Merge join是一种好的选择。

    Nested Loop Join & Cartesian join

    对参与join的所有表做Cartesian的遍历,适用范围最广,复杂度最高,速度最慢。

    感悟

    刚学Spark的时候,总是能搜到databricks的博客,最近懵懵懂懂看了一两篇,写得真的不错,尽管大部分内容还是不太懂。

    老祖宗说的好呀,知其然,知其所以然,深入之后才发现,掌握的知识水平测度为零。

    PS:放上代码,就是为了凑篇幅。

    展开全文
  • SQL--LEFT JOIN 右表多数据解决方案

    万次阅读 2018-07-08 16:48:16
    用到SQL中的LEFT JOIN时,在右表中存在多记录,即一对多的情况时, 表结构与数据: CREATE TABLE `image` ( `id` int(11) NOT NULL AUTO_INCREMENT, `productId` int(11) DEFAULT NULL, `name` varchar(255) ...

    用到SQL中的LEFT JOIN时,在右表中存在多条记录,即一对多的情况时,
    表结构与数据:

    CREATE TABLE `image` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `productId` int(11) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of image
    -- ----------------------------
    INSERT INTO `image` VALUES ('1', '1', '商品1的图片1');
    INSERT INTO `image` VALUES ('2', '1', '商品1的图片2');
    INSERT INTO `image` VALUES ('3', '1', '商品1的图片3');
    INSERT INTO `image` VALUES ('4', '2', '商品2的图片1');
    INSERT INTO `image` VALUES ('5', '2', '商品2的图片2');
    INSERT INTO `image` VALUES ('6', '3', '商品2的图片1');
    
    CREATE TABLE `product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of product
    -- ----------------------------
    INSERT INTO `product` VALUES ('1', '商品1');
    INSERT INTO `product` VALUES ('2', '商品2');
    INSERT INTO `product` VALUES ('3', '商品3');

    执行左关联查询:

    SELECT * FROM product AS p LEFT JOIN image AS img ON p.id=img.productId

    执行结果:
    这里写图片描述
    发现一对多关联然后全部查询出来了。
    现在只需要改查询商品的最后一张图片,入商品1对应的商品图片3。
    方案1:
    先按图片降序排序,然后按商品id进行分组,即得每一个商品的最后一张图片信息。

    SELECT * FROM (SELECT * FROM image ORDER BY id DESC) AS temp_image GROUP BY temp_image.productId

    方案2:
    根据产品id分组求出每个分组的最大图片id,在外面套一层查询图片信息即可

    SELECT * FROM  image WHERE id IN (SELECT CONCAT(MAX(id),',') AS id FROM image GROUP BY productId)

    执行结果:
    这里写图片描述

    展开全文
  • https://blog.csdn.net/sinat_30397435/article/details/52492272
    展开全文
  • Left join 会增多数据记录数的处理方案 A表,左连结到B表,如果B表中关联关键字不唯一时,A表的会增加记录数,也就是B中关联关键字重复的行数(设为N),会在A表中有该关联关键字的记录会重复(N次)。 有两种...

    Left join 会增多数据记录条数的处理方案

    A表 ,左连结到B表,如果B表中关联关键字不唯一时,A表的会增加记录条数,也就是B中关联关键字重复的行数(设为N),会在A表中有该关联关键字的记录会重复(N次)。

    有两种解决方案

    ======第一方案 

      if not ViewExists(QryList.Connection, '收付款x') then
      begin
        STR := 'create view 收付款x  as ';
        STR := str +
          ' SELECT A.HTCODE,a.日期,A.收款,A.付款,B.承包人 ,B.项目名称,B.合同金额  from 收付款MX A ';
        STR := str +
          ' left join (select MAX(合同编号) as 合同编号,max(项目名称) as 项目名称,max(合同金额) as 合同金额,MAX(承包人) as 承包人 FROM CONTRACT  group by 合同编号) B ON trim(A.HTCODE)=trim(B.合同编号) B ON trim(A.HTCODE)=trim(B.合同编号) ';
        EXECSQL(QryList, STR);
      end;

    这里用的GROUP BY 去重复

    ======第一方案 

    (1)先建一个去重的视图

      if not ViewExists(QryList.Connection, '合同LIST') then
      begin
        STR := 'create view 合同LIST AS '
          + 'select MAX(合同编号) as 合同编号,max(项目名称) as 项目名称,max(合同金额) as 合同金额,MAX(承包人) as 承包人 FROM CONTRACT  group by 合同编号 ';
        EXECSQL(QryList, STR);
      end;

    (2)再关联到去重的视图

      if not ViewExists(QryList.Connection, '收付款x') then
      begin
        STR := 'create view 收付款x  as ';
        STR := str +
          ' SELECT A.HTCODE,a.日期,A.收款,A.付款,B.承包人 ,B.项目名称,B.合同金额  from 收付款MX A ';
        STR := str +
          ' left join 合同LIST B ON trim(A.HTCODE)=trim(B.合同编号) ';
        EXECSQL(QryList, STR);
      end;

    展开全文
  • mysql中left join的误解及笛卡尔积解释

    万次阅读 2019-05-20 16:19:26
    left join(左外连接)查询,已左表A为基础表,关联右表B查询,最多只会返回A表里面满足条件的记录。 这种想法是错误的,返回的记录数和两表的关联条件有关。 比如: select A.id,A.name,B.id,B.name from A left ...
  • (1)not in 理解 select * from student where classID in (1,2,3) 找出班级ID为1,2, 3的学生 ...(2)巧用left join代替not in select * from a where a.id not in (  select id from b ) 找出不和b表id相等...
  • 这当中使用最多的是inner join,它的数据最有效。 left join:以左边的表为主,左边表有的数据就查询出来,右表没有就展示空。 right join:相反,左表没有的就展示空。 inner join:内连接,如果匹配的有一个是空,...
  • http://bbs.csdn.net/topics/350135010 参考 select* fromaaaleftjoin bbbonaa.id=bb.cidandbb.adddate=(selectmax(adddate)frombwherecid=bb.cid) ...Left join rechargeSheet rs on rs....
  • 且一合同有多审核记录。求:A.合同状态、B.最新审核记录结果。 简单:A 1--key--n B ---》A.*+B.*。 方案1: A作为主表单独查询,遍历list, 查询每一个合同对应的B表,按照创建时间倒序取第一。 性能: 如果是...
  • 后面可以是 多个字段, 查询的数据行数,是根据 最多不同的那个字段决定的。 left join 使用 它是以左边的表 为主表 查询的,后面 跟条件 on 关联 查询, 不管 右面 有没有数据,昨天都会显示 如果 后面 ...
  • 但是利用mac函数,会出现max(字段)的值为最大,但是其他列表的值并不是与这个最大值为同一记录里的,这里就需要用到子查询,选择符合条件的记录查询出来,再进行left join SELECT s.id AS student_id, s....
  • flink sql on hive,使用了left join去关联字典表,由于字典表中某一个值对应的左边记录表中的数据数非常非常多,是其他字典值的数十倍之多,因此在join时发生了数据倾斜,这一个字典值对应的所有记录都跑到了一个...
  • 多表使用left join只是把主表里的所有数据查询出来,其他表只查询表中的符合条件的某一记录,所以速度非常快;而多表使用where内联,是把所有表的数据全查出来,然后进行比对,所以速度非常慢。 使用left join...
  • left join 在做报表查询的时候很常用。...问题1:是否可以连续left join可以。 问题2:left join 的连接顺序, select * from A left join B ON A.id=B.id left join C ON A.id=C.id A先...
  • 如何使用LEFT JOIN实现多表查询

    万次阅读 多人点赞 2018-08-01 15:50:03
    什么是LEFT JOIN请各位自行了解,废话不多说,先直接上三张表 组织表(t_organization) 部门表(t_department) 用户表(t_user) 逻辑是组织下面有部门,部门下面有用户,组织和部门通过organization_id字段...
  • left join 左连接即以左表为基准,显示坐标所有的行,右表与左表关联的数据会显示,不关联的则不显示。关键字为left join on。 **基本用法如下:  select table a left join table b on a.id = b.ta_id** ...
  • 上传图片(最多可以上传5张图片)

    千次阅读 2018-11-09 15:00:32
    &amp;lt;!DOCTYPE html&amp;gt; &amp;lt;html&amp;gt; &amp;lt;head&amp;gt; &...上传图片(最多可以上传5张图片)&amp;lt;/title&amp;gt; &amp;lt;styl
  • Oracle中left join中右表的限制条件

    千次阅读 2016-10-19 21:58:19
    Oracle中left join中右表的限制条件
  • 比如有三张表A, B, C,在A left join B leftjoin C的过程中,总的数量应该与表A个数相同,但是当有多个匹配条件时,发现结果的个数不对,经过排查,原来原因出在where上。 开始写的语句类似于: SELECT * FROM A ...
  • 在使用left join联查三个表时,发现左表本身只有六千多数据,但是联查后得到的数据量为七千多,说明sql存在问题。 原语句: select cf.FILE_ID,TO_CHAR(cf.operate_time,'yyyy-mm-dd hh:mi:ss') operate_time,cf....
  • 查询各科目选课人数(count+left join)

    万次阅读 2018-01-18 20:48:20
    select ce.cname,count(se.cid) from course ce LEFT JOIN selectcourse se on se.cid = ce.cid group by ce.cid;count(*)将nulll同样计算在其中count(指定) 可以去除null值
  • LEFT JOIN 反而比INNER JOIN 快?

    千次阅读 2008-01-31 11:55:00
    就怀疑LinkMan表里可能有不唯一与Customer相对应的数据,一查看,确实存在Linkman里同一客户有多iGrade为1(主要)的联系人记录存在,于是就整理一下,保持最多只有一的主要联系人。 然后再测试,却发现SQL2和SQL4...
  •  目前,在阿里Java开发的规范手册上明确提到Left join表,最多不得超过3个。很尬尴的是在我们本例中后台采用的是Security的安全权限框架。至少需要将,用户、角色以及权限查询出来放入缓存以做权限检验。其次,由于...
  • mysql 优化-子查询和left join(一)

    千次阅读 2015-08-17 15:26:39
    今天针对一个mysql做优化 具体如下设计到几张表 商品表: goods 会员消费表:member_consumes 会员表: members ...比如:在孕妇装分组下面会员买的最多的是那些牌子的商品 之前的sql语句是这样的SELEC
  • 在access中支持2个以上left join的方法

    千次阅读 2011-04-19 11:39:00
    http://www.cnblogs.com/jacktu/archive/2008/01/13/1037358.html<br />  今天在做基于 access数据库项目中,做外连接时,发现Access不支持两个以上的 left join 在网上查一下,有哥们说每个 left ...
  • 1、研究了下,有了新的发现:表A和表B left join时,假设表A有m行,表B有n行,那么A和B left join时,行数最多多少?是m*n行,就是笛卡尔积本身的数量,举例如下: 这是原表记录,共9,对它进行left join...
  • SQL优化——union与union all 、left join 和 inner join 及 内连接 最近在优化一个功能,原写法是视图中套用视图,视图中又有视图,且查询了多张表用不同的数据源,用了union也用了union all,还有left join 和 ...
  • CASE 可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段,但是它还具有更多用法。例如,你可以在 WHERE 子句中使用 CASE。 首先让我们看一下 CASE 的语法。在一般的 SELECT 中,其语法...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 32,210
精华内容 12,884
关键字:

leftjoin最多可以多少条