spark任务异常（改变执行计划导致虚拟内存占满）

今天凌晨6点的时候，发现一个每天正常运行的任务，今天突然失败了，失败的理由如下：

Diagnostics: Container [pid=107685,containerID=container_1597642620576_3671_02_000001] is running beyond virtual memory limits. Current usage: 1.6 GB of 4 GB physical memory used; 8.4 GB of 8.4 GB virtual memory used. Killing container.

大致的意思 就是 虚拟内存占满了，yarn检测到之后，让nodemanager去kill 我这个任务了。可以好端端的，为什么会突然出现这种情况。

查看了这个失败的任务，在内存有限的情况下，发现 spark的执行计划中，出现了多次的broadcast的join操作，正常来说，去join这几张近百万的表，应该使用sort join，而不是broadcast才对，后面我打印了 spark的查询计划的统计信息

== Optimized Logical Plan == Project [bill_code#519, use_day#1831, send_site_id#521, name#544, send_date#523, latest_scan_type_id#529, first_center_scan_time#573, weight#1832, rec_settle_site_id#1820L, pro_type#1833, data_source#540, avg_cnt#1958L, ref_weight#1976, is_bulky#1977, CASE WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 2.00)) && (pro_type#1833 = 1)) THEN 5.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 2.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 5.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 7.5 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 8.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 10.5 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 15.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 18.5 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 20.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 25.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 25.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 35.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 35.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 50.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 45.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 55.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 70.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 65.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 80.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 75.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 90.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 85.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 100.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 4.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 7.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 10.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 14.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 18.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 18.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 25.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 23.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 35.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 33.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 50.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 43.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 53.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 70.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 63.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 80.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 73.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 90.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 83.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 100.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 3.5 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 6.5 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 10.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 13.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 18.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 16.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 25.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 21.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 35.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 29.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 50.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 38.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 46.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 70.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 55.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 80.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 63.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 90.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 72.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 100.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 3.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 6.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 6.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 9.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 12.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 16.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 15.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 22.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 20.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 30.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 28.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 43.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 37.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 51.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 43.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 53.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 68.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 60.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 77.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 68.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 85.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 2.5 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 5.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 5.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 12.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 14.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 13.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 19.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 18.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 26.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 25.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 37.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 35.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 44.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 40.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 51.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 50.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 58.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 55.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 66.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 65.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 73.0 END AS fee#1995], Statistics(sizeInBytes=2.84E 38 B, hints=none) - Join LeftOuter, (bill_code#519 = bill_code#1975), Statistics(sizeInBytes=3.04E 38 B, hints=none) :- Project [use_day#1831, send_site_id#521, name#544, bill_code#519, send_date#523, latest_scan_type_id#529, first_center_scan_time#573, weight#1832, rec_settle_site_id#1820L, pro_type#1833, data_source#540, avg_cnt#1958L], Statistics(sizeInBytes=1.83E 31 B, hints=none) : - Join LeftOuter, (((use_day#1831 = use_day#1899) && (send_site_id#521 = send_site_id#1847)) && (name#544 = name#1870)), Statistics(sizeInBytes=2.33E 31 B, hints=none) : :- Project [bill_code#519, send_date#523, cast(send_date#523 as date) AS use_day#1831, send_site_id#521, name#544, latest_scan_type_id#529, first_center_scan_time#573, CASE WHEN (coalesce(cast(weight#1684 as decimal(12,2)), coalesce(cast(item_weight#1539 as decimal(12,2)), 0.00)) > 50.00) THEN 50.00 ELSE coalesce(cast(weight#1684 as decimal(12,2)), coalesce(cast(item_weight#1539 as decimal(12,2)), 0.00)) END AS weight#1832, if (isnull(cast(send_site_id#521 as bigint))) null else UDF:getSettleId(cast(send_site_id#521 as bigint)) AS rec_settle_site_id#1820L, CASE WHEN if (isnull(if (isnull(cast(send_site_id#521 as bigint))) null else UDF:getSettleId(cast(send_site_id#521 as bigint)))) null else UDF:getProId(if (isnull(cast(send_site_id#521 as bigint))) null else UDF:getSettleId(cast(send_site_id#521 as bigint))) IN (62357,62590,62648,62707) THEN 1 ELSE 2 END AS pro_type#1833, data_source#540], Statistics(sizeInBytes=1.87E 24 B, hints=none) : : - Join LeftOuter, (bill_code#519 = bill_code#1679), Statistics(sizeInBytes=2.03E 24 B, hints=none) : : :- Project [bill_code#519, send_date#523, send_site_id#521, first_center_scan_time#573, data_source#540, name#544, latest_scan_type_id#529, item_weight#1539], Statistics(sizeInBytes=4.1 PB, hints=none) : : : - Join LeftOuter, (bill_code#519 = bill_code#713), Statistics(sizeInBytes=4.8 PB, hints=none) : : : :- Project [bill_code#519, send_date#523, send_site_id#521, cast(split(split(centers#538, #)[0], ,)[0] as timestamp) AS first_center_scan_time#573, data_source#540, name#544, latest_scan_type_id#529], Statistics(sizeInBytes=24.6 MB, hints=none) : : : : - Filter (isnotnull(data_source#540) && (data_source#540 = SHOPEE)), Statistics(sizeInBytes=98.4 MB, hints=none) : : : : - Relation[bill_code#519,provide_site_id#520,send_site_id#521,first_scan_site_id#522,send_date#523,max_weight#524,rec_scan_value#525,rec_upload_value#526,sign_value#527,latest_scan_site_id#528,latest_scan_type_id#529,latest_scan_time#530,start_site_id#531,ele_weight#532,last_center_scan_time#533,last_site_id#534,dispatch_site_id#535,sign_date#536,is_center_scan#537,centers#538,warehouse_code#539,data_source#540,is_cancel#541,tri_sort_code#542,mobile#543,name#544,pt#545] parquet, Statistics(sizeInBytes=98.4 MB, hints=none) : : : - Aggregate [bill_code#713], [bill_code#713, max(value#1530).item_weight AS item_weight#1539], Statistics(sizeInBytes=198.2 MB, hints=none) : : : - Project [bill_code#713, named_struct(updated_time, CASE WHEN isnotnull(UPDATED_TIME#709) THEN UPDATED_TIME#709 - interval 1 hours END, item_weight, item_weight#751) AS value#1530], Statistics(sizeInBytes=242.3 MB, hints=none) : : : - Filter (((isnotnull(pt#780) && (pt#780 >= 20200728)) && (pt#780 <= 20200913)) && isnotnull(bill_code#713)), Statistics(sizeInBytes=6.5 GB, hints=none) : : : - Relation[ID#707,CREATED_TIME#708,UPDATED_TIME#709,RE_MARK#710,TX_LOGISTIC_ID#711,TRADE_NO#712,BILL_CODE#713,STATUS_CODE#714,REC_SITE_ID#715,REC_MAN_ID#716,REC_DATE#717,ACCEPT_DATE#718,DISPATCH_SITE_ID#719,DISPATCH_MAN_ID#720,DISPATCH_DATE#721,IS_CANCEL#722,CANCEL_DATE#723,CANCEL_DESC_ID#724,CANCEL_DESC_CODE#725,PREPARE_SITE#726,NAME#727,ADDRESS#728,POST_CODE#729,PHONE#730,MOBILE#731,PROV_NAME#732,CITY_NAME#733,COUNTY_NAME#734,SEND_START_TIME#735,SEND_END_TIME#736,PIECE#737,INSURANCE_VALUE#738,CONSIGNEE#739,SHIPPING_ADDRESS#740,CONSIGNEE_ZIP_CODE#741,CONSIGNEE_PHONE#742,CONSIGNEE_MOBILE#743,CONSIGNEE_PROV#744,CONSIGNEE_CITY#745,CONSIGNEE_COUNTY#746,CANCEL_SITE_ID#747,CANCEL_MAN_ID#748,CUSTOMER_ID#749,DATA_SOURCE#750,ITEM_WEIGHT#751,ITEM_VOLUME#752,ORDER_FLAG#753,CUSTOMER_SOURCE#754,ITEMS_VALUE#755,GOODS_VALUE#756,COUNTRY_NAME#757,CONSIGNEE_COUNTRY#758,INTERNAL_STATUS_ID#759,INTERNAL_STATUS_CODE#760,COD_VALUE#761,BANK_CARD_OWNER#762,BANK_CODE#763,BANK_CARD_NO#764,CERTIFICATE_NO#765,TRI_SORT_CODE#766,PROV_ID#767,CITY_ID#768,COUNTY_ID#769,CONSIGNEE_PROV_ID#770,CONSIGNEE_CITY_ID#771,CONSIGNEE_COUNTY_ID#772,ORDER_FLAG_ID#773,ORDER_TYPE#774,TRIAL_VOLUMN#775,WAREHOUSE_CODE#776,REC_DELAY_TIMES#777,REC_DELAY_OPERATION_TIME#778,REC_DELAY_REASON_ID#779,pt#780] parquet, Statistics(sizeInBytes=6.5 GB, hints=none) : : - Project [bill_code#1679, weight#1684], Statistics(sizeInBytes=419.5 MB, hints=none) : : - Filter (isnotnull(rownum#1794) && (rownum#1794 = 1)), Statistics(sizeInBytes=559.3 MB, hints=none) : : - Window [row_number() windowspecdefinition(bill_code#1679, created_time#1721 ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rownum#1794], [bill_code#1679], [created_time#1721 ASC NULLS FIRST], Statistics(sizeInBytes=559.3 MB, hints=none) : : - Project [bill_code#1679, CASE WHEN isnotnull(CREATED_TIME#1678) THEN CREATED_TIME#1678 - interval 1 hours END AS CREATED_TIME#1721, weight#1684], Statistics(sizeInBytes=512.7 MB, hints=none) : : - Filter ((((((((isnotnull(weight#1684) && isnotnull(pt#1698)) && isnotnull(is_electron_id#1683)) && (pt#1698 >= 20200817)) && (pt#1698 <= 20200902)) && (is_electron_id#1683 = 1)) && (if (isnull(cast(scan_site_id#1680 as bigint))) null else UDF:siteType(cast(scan_site_id#1680 as bigint)) = 1)) && (weight#1684 > 0.00)) && isnotnull(bill_code#1679)), Statistics(sizeInBytes=3.3 GB, hints=none) : : - Relation[ID#1677,CREATED_TIME#1678,BILL_CODE#1679,SCAN_SITE_ID#1680,SCAN_DATE#1681,SCAN_TYPE_ID#1682,IS_ELECTRON_ID#1683,WEIGHT#1684,OWNER_BAG_NO#1685,PRE_OR_NEXT_STATION_ID#1686,DATA_SOURCE#1687,IS_WITHDRAW#1688,SCAN_MAN#1689,LISTING_CODE#1690,RE_MARK#1691,LENGTH#1692,WIDTH#1693,HEIGHT#1694,SCAN_MAN_ID#1695,VOLUMETRIC_WEIGHT#1696,UPDATED_TIME#1697,pt#1698] parquet, Statistics(sizeInBytes=3.3 GB, hints=none) : - Aggregate [send_site_id#1847, use_day#1899, name#1870], [send_site_id#1847, use_day#1899, name#1870, count(1) AS avg_cnt#1958L], Statistics(sizeInBytes=11.9 MB, hints=none) : - Project [send_site_id#1847, name#1870, cast(send_date#1849 as date) AS use_day#1899], Statistics(sizeInBytes=10.2 MB, hints=none) : - Filter ((((isnotnull(data_source#1866) && (data_source#1866 = SHOPEE)) && isnotnull(name#1870)) && isnotnull(cast(send_date#1849 as date))) && isnotnull(send_site_id#1847)), Statistics(sizeInBytes=98.4 MB, hints=none) : - Relation[bill_code#1845,provide_site_id#1846,send_site_id#1847,first_scan_site_id#1848,send_date#1849,max_weight#1850,rec_scan_value#1851,rec_upload_value#1852,sign_value#1853,latest_scan_site_id#1854,latest_scan_type_id#1855,latest_scan_time#1856,start_site_id#1857,ele_weight#1858,last_center_scan_time#1859,last_site_id#1860,dispatch_site_id#1861,sign_date#1862,is_center_scan#1863,centers#1864,warehouse_code#1865,data_source#1866,is_cancel#1867,tri_sort_code#1868,mobile#1869,name#1870,pt#1871] parquet, Statistics(sizeInBytes=98.4 MB, hints=none) - Filter isnotnull(bill_code#1975), Statistics(sizeInBytes=15.8 MB, hints=none) - Relation[bill_code#1975,ref_weight#1976,is_bulky#1977] parquet, Statistics(sizeInBytes=15.8 MB, hints=none) == Physical Plan == *(21) Project [bill_code#519, use_day#1831, send_site_id#521, name#544, send_date#523, latest_scan_type_id#529, first_center_scan_time#573, weight#1832, rec_settle_site_id#1820L, pro_type#1833, data_source#540, avg_cnt#1958L, ref_weight#1976, is_bulky#1977, CASE WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 2.00)) && (pro_type#1833 = 1)) THEN 5.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 2.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 5.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 7.5 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 8.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 10.5 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 15.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 18.5 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 20.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 25.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 25.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 35.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 35.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 50.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 45.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 55.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 70.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 65.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 80.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 75.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 90.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 85.0 WHEN (((avg_cnt#1958L <= 20) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 100.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 4.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 7.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 10.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 14.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 18.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 18.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 25.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 23.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 35.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 33.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 50.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 43.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 53.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 70.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 63.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 80.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 73.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 90.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 83.0 WHEN (((avg_cnt#1958L <= 50) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 100.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 3.5 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 6.5 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 10.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 13.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 18.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 16.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 25.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 21.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 35.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 29.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 50.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 38.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 46.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 70.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 55.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 80.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 63.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 90.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 72.0 WHEN (((avg_cnt#1958L <= 100) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 100.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 3.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 6.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 6.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 9.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 12.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 16.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 15.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 22.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 20.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 30.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 28.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 43.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 37.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 51.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 43.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 60.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 53.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 68.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 60.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 77.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 68.0 WHEN (((avg_cnt#1958L <= 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 85.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 1)) THEN 2.5 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 3.00)) && (pro_type#1833 = 2)) THEN 5.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 1)) THEN 5.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 5.00)) && (pro_type#1833 = 2)) THEN 7.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 1)) THEN 12.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 10.00)) && (pro_type#1833 = 2)) THEN 14.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 1)) THEN 13.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 15.00)) && (pro_type#1833 = 2)) THEN 19.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 1)) THEN 18.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 20.00)) && (pro_type#1833 = 2)) THEN 26.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 1)) THEN 25.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 25.00)) && (pro_type#1833 = 2)) THEN 37.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 1)) THEN 35.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 30.00)) && (pro_type#1833 = 2)) THEN 44.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 1)) THEN 40.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 35.00)) && (pro_type#1833 = 2)) THEN 51.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 1)) THEN 50.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 40.00)) && (pro_type#1833 = 2)) THEN 58.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 1)) THEN 55.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 45.00)) && (pro_type#1833 = 2)) THEN 66.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 1)) THEN 65.0 WHEN (((avg_cnt#1958L > 300) && (weight#1832 <= 50.00)) && (pro_type#1833 = 2)) THEN 73.0 END AS fee#1995] - SortMergeJoin [bill_code#519], [bill_code#1975], LeftOuter :- *(18) Sort [bill_code#519 ASC NULLS FIRST], false, 0 : - Exchange(coordinator id: 1923367993) hashpartitioning(bill_code#519, 600), coordinator[target post-shuffle partition size: 62914560] : - *(17) Project [use_day#1831, send_site_id#521, name#544, bill_code#519, send_date#523, latest_scan_type_id#529, first_center_scan_time#573, weight#1832, rec_settle_site_id#1820L, pro_type#1833, data_source#540, avg_cnt#1958L] : - SortMergeJoin [use_day#1831, send_site_id#521, name#544], [use_day#1899, send_site_id#1847, name#1870], LeftOuter : :- *(13) Sort [use_day#1831 ASC NULLS FIRST, send_site_id#521 ASC NULLS FIRST, name#544 ASC NULLS FIRST], false, 0 : : - Exchange(coordinator id: 874818875) hashpartitioning(use_day#1831, send_site_id#521, name#544, 600), coordinator[target post-shuffle partition size: 62914560] : : - *(12) Project [bill_code#519, send_date#523, cast(send_date#523 as date) AS use_day#1831, send_site_id#521, name#544, latest_scan_type_id#529, first_center_scan_time#573, CASE WHEN (coalesce(cast(weight#1684 as decimal(12,2)), coalesce(cast(item_weight#1539 as decimal(12,2)), 0.00)) > 50.00) THEN 50.00 ELSE coalesce(cast(weight#1684 as decimal(12,2)), coalesce(cast(item_weight#1539 as decimal(12,2)), 0.00)) END AS weight#1832, if (isnull(cast(send_site_id#521 as bigint))) null else UDF:getSettleId(cast(send_site_id#521 as bigint)) AS rec_settle_site_id#1820L, CASE WHEN if (isnull(if (isnull(cast(send_site_id#521 as bigint))) null else UDF:getSettleId(cast(send_site_id#521 as bigint)))) null else UDF:getProId(if (isnull(cast(send_site_id#521 as bigint))) null else UDF:getSettleId(cast(send_site_id#521 as bigint))) IN (62357,62590,62648,62707) THEN 1 ELSE 2 END AS pro_type#1833, data_source#540] : : - SortMergeJoin [bill_code#519], [bill_code#1679], LeftOuter : : :- *(7) Sort [bill_code#519 ASC NULLS FIRST], false, 0 : : : - Exchange(coordinator id: 1645827082) hashpartitioning(bill_code#519, 600), coordinator[target post-shuffle partition size: 62914560] : : : - *(6) Project [bill_code#519, send_date#523, send_site_id#521, first_center_scan_time#573, data_source#540, name#544, latest_scan_type_id#529, item_weight#1539] : : : - SortMergeJoin [bill_code#519], [bill_code#713], LeftOuter : : : :- *(2) Sort [bill_code#519 ASC NULLS FIRST], false, 0 : : : : - Exchange(coordinator id: 1106343147) hashpartitioning(bill_code#519, 600), coordinator[target post-shuffle partition size: 62914560] : : : : - *(1) Project [bill_code#519, send_date#523, send_site_id#521, cast(split(split(centers#538, #)[0], ,)[0] as timestamp) AS first_center_scan_time#573, data_source#540, name#544, latest_scan_type_id#529] : : : : - *(1) Filter (isnotnull(data_source#540) && (data_source#540 = SHOPEE)) : : : : - *(1) FileScan parquet [bill_code#519,send_site_id#521,send_date#523,latest_scan_type_id#529,centers#538,data_source#540,name#544,pt#545] Batched: true, Format: Parquet, Location: InMemoryFileIndex[hdfs://xxxxx/user/hive/warehouse/nifi.db/thai9/ge_cen..., PartitionCount: 7, PartitionFilters: [], PushedFilters: [IsNotNull(data_source), EqualTo(data_source,SHOPEE)], ReadSchema: struct<bill_code:string,send_site_id:decimal(19,0),send_date:timestamp,latest_scan_type_id:decima... : : : - *(5) Sort [bill_code#713 ASC NULLS FIRST], false, 0 : : : - Exchange(coordinator id: 1106343147) hashpartitioning(bill_code#713, 600), coordinator[target post-shuffle partition size: 62914560] : : : - SortAggregate(key=[bill_code#713], functions=[max(value#1530)], output=[bill_code#713, item_weight#1539]) : : : - *(4) Sort [bill_code#713 ASC NULLS FIRST], false, 0 : : : - Exchange(coordinator id: 295986528) hashpartitioning(bill_code#713, 600), coordinator[target post-shuffle partition size: 62914560] : : : - SortAggregate(key=[bill_code#713], functions=[partial_max(value#1530)], output=[bill_code#713, max#2012]) : : : - *(3) Sort [bill_code#713 ASC NULLS FIRST], false, 0 : : : - *(3) Project [bill_code#713, named_struct(updated_time, CASE WHEN isnotnull(UPDATED_TIME#709) THEN UPDATED_TIME#709 - interval 1 hours END, item_weight, item_weight#751) AS value#1530] : : : - *(3) Filter isnotnull(bill_code#713) : : : - *(3) FileScan parquet [UPDATED_TIME#709,BILL_CODE#713,ITEM_WEIGHT#751,pt#780] Batched: true, Format: Parquet, Location: TahoeLogFileIndex[hdfs://xxxxx/user/hive/warehouse/delta.db/thai9/ge_ta..., PartitionCount: 29, PartitionFilters: [isnotnull(pt#780), (pt#780 >= 20200728), (pt#780 <= 20200913)], PushedFilters: [IsNotNull(BILL_CODE)], ReadSchema: struct<UPDATED_TIME:timestamp,BILL_CODE:string,ITEM_WEIGHT:decimal(10,2)> : : - *(11) Sort [bill_code#1679 ASC NULLS FIRST], false, 0 : : - Exchange(coordinator id: 1645827082) hashpartitioning(bill_code#1679, 600), coordinator[target post-shuffle partition size: 62914560] : : - *(10) Project [bill_code#1679, weight#1684] : : - *(10) Filter (isnotnull(rownum#1794) && (rownum#1794 = 1)) : : - Window [row_number() windowspecdefinition(bill_code#1679, created_time#1721 ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS rownum#1794], [bill_code#1679], [created_time#1721 ASC NULLS FIRST] : : - *(9) Sort [bill_code#1679 ASC NULLS FIRST, created_time#1721 ASC NULLS FIRST], false, 0 : : - Exchange(coordinator id: 1658894916) hashpartitioning(bill_code#1679, 600), coordinator[target post-shuffle partition size: 62914560] : : - *(8) Project [bill_code#1679, CASE WHEN isnotnull(CREATED_TIME#1678) THEN CREATED_TIME#1678 - interval 1 hours END AS CREATED_TIME#1721, weight#1684] : : - *(8) Filter (((((isnotnull(weight#1684) && isnotnull(is_electron_id#1683)) && (is_electron_id#1683 = 1)) && (if (isnull(cast(scan_site_id#1680 as bigint))) null else UDF:siteType(cast(scan_site_id#1680 as bigint)) = 1)) && (weight#1684 > 0.00)) && isnotnull(bill_code#1679)) : : - *(8) FileScan parquet [CREATED_TIME#1678,BILL_CODE#1679,SCAN_SITE_ID#1680,IS_ELECTRON_ID#1683,WEIGHT#1684,pt#1698] Batched: true, Format: Parquet, Location: TahoeLogFileIndex[hdfs://xxxxx/user/hive/warehouse/delta.db/thai9/ge_sc..., PartitionCount: 9, PartitionFilters: [isnotnull(pt#1698), (pt#1698 >= 20200817), (pt#1698 <= 20200902)], PushedFilters: [IsNotNull(WEIGHT), IsNotNull(IS_ELECTRON_ID), EqualTo(IS_ELECTRON_ID,1), GreaterThan(WEIGHT,0.00..., ReadSchema: struct<CREATED_TIME:timestamp,BILL_CODE:string,SCAN_SITE_ID:decimal(19,0),IS_ELECTRON_ID:decimal(... : - *(16) Sort [use_day#1899 ASC NULLS FIRST, send_site_id#1847 ASC NULLS FIRST, name#1870 ASC NULLS FIRST], false, 0 : - Exchange(coordinator id: 874818875) hashpartitioning(use_day#1899, send_site_id#1847, name#1870, 600), coordinator[target post-shuffle partition size: 62914560] : - *(15) HashAggregate(keys=[send_site_id#1847, use_day#1899, name#1870], functions=[count(1)], output=[send_site_id#1847, use_day#1899, name#1870, avg_cnt#1958L]) : - Exchange(coordinator id: 1835981515) hashpartitioning(send_site_id#1847, use_day#1899, name#1870, 600), coordinator[target post-shuffle partition size: 62914560] : - *(14) HashAggregate(keys=[send_site_id#1847, use_day#1899, name#1870], functions=[partial_count(1)], output=[send_site_id#1847, use_day#1899, name#1870, count#2014L]) : - *(14) Project [send_site_id#1847, name#1870, cast(send_date#1849 as date) AS use_day#1899] : - *(14) Filter ((((isnotnull(data_source#1866) && (data_source#1866 = SHOPEE)) && isnotnull(name#1870)) && isnotnull(cast(send_date#1849 as date))) && isnotnull(send_site_id#1847)) : - *(14) FileScan parquet [send_site_id#1847,send_date#1849,data_source#1866,name#1870,pt#1871] Batched: true, Format: Parquet, Location: InMemoryFileIndex[hdfs://xxxxx/user/hive/warehouse/nifi.db/thai9/ge_cen..., PartitionCount: 7, PartitionFilters: [], PushedFilters: [IsNotNull(data_source), EqualTo(data_source,SHOPEE), IsNotNull(name), IsNotNull(send_site_id)], ReadSchema: struct<send_site_id:decimal(19,0),send_date:timestamp,data_source:string,name:string> - *(20) Sort [bill_code#1975 ASC NULLS FIRST], false, 0 - Exchange(coordinator id: 1923367993) hashpartitioning(bill_code#1975, 600), coordinator[target post-shuffle partition size: 62914560] - *(19) Project [bill_code#1975, ref_weight#1976, is_bulky#1977] - *(19) Filter isnotnull(bill_code#1975) - *(19) FileScan parquet [bill_code#1975,ref_weight#1976,is_bulky#1977] Batched: true, Format: Parquet, Location: InMemoryFileIndex[hdfs://xxxxx/user/hive/analysis/q9file/sparkjob/Spark..., PartitionFilters: [], PushedFilters: [IsNotNull(bill_code)], ReadSchema: struct<bill_code:string,ref_weight:decimal(9,2),is_bulky:int>

才发现的确是走了broadcast,因为发现这些近百万的文件只有5、6个文件，而且很小，只有1.5M，

那么1.5M*5=7.5M，小于10M，那么走broadcast就情有可原了，只是我这边的文件都是经过snappy压缩的文件，真实数据是接近百万的。

现在问题原因找到之后，就开始进行解决：

办法1:增加 dirver memory的内存，从之前的默认2G 增加到3G。

办法2:取消spark的broadcast的操作，设置 spark.sql.autoBroadcastJoinThreshold = -1

虽然这两种办法都能解决，但是 从根源上，我还是不是很理解，为何 增加了dirver memory的内存之后，虚拟内存就不会爆掉，任务也可以正常走了，这个后续 还是要看源码，要走的路还有很长，记录本次调整。