Maxcompute full outer join rewrites left anti join practice

Time:2020-11-17

Introduction:When ODS layer data synchronization, we often encounter the model of incremental total volume consolidation, that is, T-1 day increment Table + T-2 full scale = T-1 full scale. You can use the full outer join script to complete the merge, but when the amount of data is large, it is very resource consuming. This article will introduce you to the best practice of how to rewrite left anti join by full outer join when adding and updating incremental data.

background

ODS layer data synchronization is often encounteredIncremental consolidationModel of, i.eT-1 day increment scale + T-2 full scale = T-1 full scale。 You can use the full outer join script to complete the merge, but when the amount of data is large, it is very resource consuming.

insert overwrite table tb_test partition(ds='${bizdate}')
select case when a.id is not null then a.id esle b.id end as id   
      ,if(a.name is not null, a.name, b.name) as name
      ,coalesce(a.age, b.age) as age 
      A kind of --These three methods are the same, they all give priority to the fields in the delta table_

from
(
   select * from tb_test_delta where ds='${bizdate}'
) a
full outer join
(
   select * from tb_test where ds='${bizdate-1}'
) b
on a.id =b.id;

This method can be used to add and update

  • New data refers to the new data in the incremental table, but not in the full scale;
  • Updating refers to the data in both the incremental table and the full scale, but the data in the incremental table is preferred to cover the data in the historical table.
    As shown in the figure below, R2_ 1 is the incremental data after de duplication of the incremental table on the same day, m3 is the data of the day before the full scale, and J4 is the data of the day before the full scale_ 2_ 3 is the execution diagram of the full outer join.

Maxcompute full outer join rewrites left anti join practice

J4_ 2_ 3. It will be found that the incremental and total data are merged. When the data volume is large (128.8 billion), there will be a lot of shuffle overhead.At this time, the optimization scheme is to change full outer join to union all, so as to avoid join shuffle

Optimization model

Conclusion: changing full outer join to hash cluster + left join + union all can effectively reduce the computing cost, and there are two application scenarios. Firstly, the model is abstracted. Suppose there are two tables a and B. A is an incremental table and B is a full scale

with 
 a as ( select * from values  (1,'111')
                             ,(2,'two')
                             ,(7,'777') as (id,name) ) _ --Increment_

,b as ( select * from values  (1,'')
                             ,(2,'222')
                             ,(3,'333')
                             ,(4,'444') as (id,name) )  _ --Total quantity_

Scenario 1: only the newly added data are merged into the full scale

Left anti join is equivalent to not in and increment not in. After filtering, only the newly added IDs are left. The existing IDS in the total quantity are not modified

A kind of --Query the newly added ID_
select * from a left anti join b on a.id=b.id ;
A kind of --The results are as follows_
+_------------+------+_
| id         | name |
+_------------+------+_
| 7          | 777  |
+_------------+------+_
A kind of --Completely new consolidated full scale_
select * from  a _ --Increment table_
left anti join b on a.id=b.id  
union all 
select * from b  _ --Full scale_
A kind of --The results are as follows_
+_------------+------+_
| id         | name |
+_------------+------+_
| 1          |      |
| 2          | 222  |
| 3          | 333  |
| 4          | 444  |
| 7          | 777  |
+_------------+------+_

Scenario 2: merge the new data into the full scale and update the historical data

After filtering, only the historical ID is left, and then the union all increment is added and modified

A kind of --Query historical total data_
select * from b left anti join a on a.id=b.id;
A kind of --The results are as follows_
+_------------+------+_
| id         | name |
+_------------+------+_
| 3          | 333  |
| 4          | 444  |
+_------------+------+_
A kind of --Merge the new data into the full scale, and update the historical data_
select * from  b _ --Full scale_
left anti join a on a.id=b.id
union all 
select * from a ; _ --Increment table_
A kind of --The results are as follows_
+_------------+------+_
| id         | name |
+_------------+------+_
| 1          | 111  |
| 2          | two  |
| 7          | 777  |
| 3          | 333  |
| 4          | 444  |
+_------------+------+_

Optimization Practice

Step 1: modify table attributes

Modify the attributes of tables and jobs, and optimize the attributes of the original tables and jobs, which can improve the optimization effect.

set  odps.sql.reducer .instances=3072;  _ --Optional. By default, there are 1111 reducers and 1111 hash buckets_
alter table table_ name clustered by(contact_ id) sorted by(contact_ id) into 3072 buckets;_ --Required_

Step 2: code transformation according to scenario 1 or scenario 2 of the above model.

Here, the resource consumption comparison after code transformation is given

Original full outer Jian

Left anti join initialization

Original full outer Jian

After the next day

Time consumption

8h30min38s

1h4min48s

7h32min30s

32min30s

CPU consumption

29666.02 Core * Min

65705.30 Core * Min

31126.86 Core * Min

30589.29 Core * Min

MEM consumption

109640.80 GB * Min

133922.25 GB * Min

114764.80 GB * Min

65509.28 GB * Min

It can be found that the hash cluster bucket splitting operation has extra overhead in initialization, mainly hash and sort by primary key. However, it is worth it and can be done once and for all. The subsequent read speed is very fast. In the past, it took 8 hours to run every day, but now it takes 1 hour to initialize the sub bucket. In the future, it only takes 30 minutes every day.

Initialization execution diagram

Figure 1
Maxcompute full outer join rewrites left anti join practice

  • M2 is to read the full scale.
  • M4 is to read the incremental table. In the model of scenario 2, the incremental table is read twice, where:

    • R5_ 4 is to delete the primary key (row)_ Number) is used in the following union all, which contains all incremental data;
    • R1_ 4 is to delete the primary key (row)_ Number) is used for left anti join, which only contains the primary key.
  • J3_ 1_ 2 is a left anti join. After expanding it, you can see that there is still a mergjoin. However, this is only an initialization operation, and it will not be available every day. Expanded as shown in Figure 2.
  • R6_ 3_ 5 is to union all the increment and total quantity, as shown in Figure 3.
  • R7_ 6 is to write the index information to the metadata, as shown in Figure 3_ 6 sink.
    Therefore, in addition to R5 in Figure 1_ 4 and R1_ 4 is necessary to remove the weight, there is shuffle. And J3_ 1_ 2 and R6_ 3_ There are shuffles in these two places.

Figure 2
Maxcompute full outer join rewrites left anti join practice

Figure 3
Maxcompute full outer join rewrites left anti join practice

Execution chart after the next day

Figure 1
Maxcompute full outer join rewrites left anti join practice

Same as above, R3 in Figure 1_ 2 and R1_ 2. For incremental de duplication, it is necessary to shuffle the operation, which is ignored here.

Initialize J3 of execution graph_ 1_ 2 and R6_ 3_ 5 has been merged into M4_ 1_ 3. Expand it as shown in Figure 2. That is, the two steps of left anti join and union all are completed in one stage, and this stage is the map task (M4_ 1_ 3) Instead of the join task or reduce task. Moreover, the full scale does not occupy a single map task, but is also merged into M4_ 1_ 3. Therefore, there is no shuffle operation in the whole process, and the speed increase is very obvious. In other words, only one M4 is needed_ 1_ 3 to complete all to operations, directly sink to the table.

R5_ 4 is to write the index information to the metadata, as shown in Figure 2_ 4.

Figure 2
Maxcompute full outer join rewrites left anti join practice

Link to original text
This article is the original content of Alibaba cloud and can not be reproduced without permission.