Doris--基础--08--Broadcast/Shuffle Join

Doris–基础–08–Broadcast/Shuffle Join

---

1、介绍

doris在join操作的时候时候，默认使用broadcast的方式进行join，即将小表通过广播的方式广播到大表所在的节点，形成内存hash，然后流式读出大表数据进行hashjoin。

但如果小表的数据量也很大的时候，就会造成内存溢出，此时需要通过shuffle join的方式进行，也被称为partition join，即将大表小表都按照join的key进行hash，然后进行分布式join。

2、准备数据

CREATE DATABASE test_db;
show databases;

use test_db;
CREATE TABLE IF NOT EXISTS test_db.table11
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);

insert into test_db.table11 values(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10);
insert into test_db.table11 values(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22); 
insert into test_db.table11 values(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5); 
insert into test_db.table11 values(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11); 
insert into test_db.table11 values(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3); 



CREATE TABLE IF NOT EXISTS test_db.table12
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);

 
insert into test_db.table12 values(10000,'2017-10-01','北京',20,0,'2017-10-01 06:00:00',20,10,10);
insert into test_db.table12 values(10001,'2017-10-01','北京',30,1,'2017-10-01 17:05:45',2,22,22); 
insert into test_db.table12 values(10002,'2017-10-02','上海',20,1,'2017-10-02 12:59:12',200,5,5); 
insert into test_db.table12 values(10003,'2017-10-02','广州',32,0,'2017-10-02 11:20:00',30,11,11); 
insert into test_db.table12 values(10004,'2017-10-01','深圳',35,0,'2017-10-01 10:00:15',100,3,3); 

3、测试

3.1、使用 Broadcast Join(默认)

select sum(table12.cost) from table12 join table11 where table12.user_id = 10000;

3.2、显示指定braodcast

select sum(table12.cost) from table12 join [broadcast] table11 where table12.user_id = 10000;

3.3、使用suffle join

select sum(table12.cost) from table12 join [shuffle] table11 where table12.user_id = 10000;