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.
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
- 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
Figure 3
Execution chart after the next day
Figure 1
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
Link to original text
This article is the original content of Alibaba cloud and can not be reproduced without permission.