回复：flink-1.11.2 执行checkpoint失败
Locked
 
|
首先，我想知道你是否使用了kafka sink？
在 2020年11月12日 21:16，史 正超<[hidden email]> 写道： 执行checkpoint失败，报下面的错。 2020-11-12 21:04:56 org.apache.flink.util.FlinkRuntimeException: Exceeded checkpoint tolerable failure threshold. at org.apache.flink.runtime.checkpoint.CheckpointFailureManager.handleJobLevelCheckpointException(CheckpointFailureManager.java:66) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1673) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1650) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.access$600(CheckpointCoordinator.java:91) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator$CheckpointCanceller.run(CheckpointCoordinator.java:1783) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) |
|
|
没有，用的是jdbc sink，先是 三张change log的 left join，然后 再分别与两张mysql维表的join，执行checkpoint超时。sql太复杂了，我越写越没信心。。。
``` CREATE VIEW fcbox_send_fat_view AS SELECT REPLACE(SUBSTR(client_post_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN SendRevenue(express_company_id, COALESCE(freight, 0)) ELSE CAST(0 AS BIGINT) END) AS send_donation_revenue, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN total_amount ELSE CAST(0 AS BIGINT) END) AS send_amount, FIRST_VALUE(0) AS send_order_count, COUNT(CASE WHEN send_status IN (103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS in_transit_send_count, COUNT(send_id) AS drop_send_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS effect_drop_send_count, COUNT(CASE WHEN send_status in (105,106,109) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS drop_cancel_send_count, COUNT(DISTINCT CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN point_code END) AS drop_send_ed_count, COUNT(DISTINCT CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN user_id END) AS drop_send_psn_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND send_type = 2 THEN send_id END) AS wechat_scan_drop_send_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND send_type = 3 THEN send_id END) AS alipay_scan_drop_send_count, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) <= 12 THEN send_id END) AS courier_get_count_morrow_12, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND ((TimeDayDiff(client_post_time, courier_get_time) > 1) OR (TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) > 12)) THEN send_id END) AS courier_get_count_morrow_gt_12, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_day, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) <= 12 THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_morrow_12, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND ((TimeDayDiff(client_post_time, courier_get_time) > 1) OR (TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) > 12)) THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_morrow_gt_12, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 THEN send_id END) AS drop_send_count_00_07, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 THEN send_id END) AS drop_send_count_07_17, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 THEN send_id END) AS drop_send_count_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_17_24, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN payment_amount ELSE CAST(0 AS BIGINT) END) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status > 100 AND s.client_post_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.client_post_time)) <= 1 ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.point_code = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(client_post_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(user_create_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, FIRST_VALUE(0) AS send_donation_revenue, FIRST_VALUE(0) AS send_amount, COUNT(1) AS send_order_count, FIRST_VALUE(0) AS in_transit_send_count, FIRST_VALUE(0) AS drop_send_count, FIRST_VALUE(0) AS effect_drop_send_count, FIRST_VALUE(0) AS drop_cancel_send_count, FIRST_VALUE(0) AS drop_send_ed_count, FIRST_VALUE(0) AS drop_send_psn_count, FIRST_VALUE(0) AS wechat_scan_drop_send_count, FIRST_VALUE(0) AS alipay_scan_drop_send_count, FIRST_VALUE(0) AS courier_get_count_02 , FIRST_VALUE(0) AS courier_get_count_02_06, FIRST_VALUE(0) AS courier_get_count_06_12, FIRST_VALUE(0) AS courier_get_count_12_24, FIRST_VALUE(0) AS courier_get_count_24, FIRST_VALUE(0) AS courier_get_count_day, FIRST_VALUE(0) AS courier_get_count_morrow_12, FIRST_VALUE(0) AS courier_get_count_morrow_gt_12, FIRST_VALUE(0) AS courier_get_internet_day, FIRST_VALUE(0) AS courier_get_internet_morrow_12, FIRST_VALUE(0) AS courier_get_internet_morrow_gt_12, FIRST_VALUE(0) AS drop_send_count_00_07, FIRST_VALUE(0) AS drop_send_count_07_17, FIRST_VALUE(0) AS drop_send_count_17_24, FIRST_VALUE(0) AS courier_get_count_day_00_07, FIRST_VALUE(0) AS courier_get_count_day_07_17, FIRST_VALUE(0) AS courier_get_count_day_17_24, FIRST_VALUE(0) AS courier_get_count_02_00_07, FIRST_VALUE(0) AS courier_get_count_02_06_00_07, FIRST_VALUE(0) AS courier_get_count_06_12_00_07, FIRST_VALUE(0) AS courier_get_count_12_24_00_07, FIRST_VALUE(0) AS courier_get_count_24_00_07, FIRST_VALUE(0) AS courier_get_count_02_07_17, FIRST_VALUE(0) AS courier_get_count_02_06_07_17, FIRST_VALUE(0) AS courier_get_count_06_12_07_17, FIRST_VALUE(0) AS courier_get_count_12_24_07_17, FIRST_VALUE(0) AS courier_get_count_24_07_17, FIRST_VALUE(0) AS courier_get_count_02_17_24, FIRST_VALUE(0) AS courier_get_count_02_06_17_24, FIRST_VALUE(0) AS courier_get_count_06_12_17_24, FIRST_VALUE(0) AS courier_get_count_12_24_17_24, FIRST_VALUE(0) AS courier_get_count_24_17_24, FIRST_VALUE(0) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, 'NA' AS throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status >= 100 AND (s.change_flag <> 3 or s.change_flag is null) AND s.user_create_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.user_create_time)) <= 1 ) t1 JOIN edbasic.basic_district_name_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.sender_city_name = d1.dis_name ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(user_create_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(closeTime, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, -99 AS payType, direction, IF(boxType IS NOT NULL, boxType, -99) AS boxType, SUM(SendRevenue(express_company_id, COALESCE(freight, 0))) AS send_freight_collect_revenue, FIRST_VALUE(0) AS send_donation_revenue, FIRST_VALUE(0) AS send_amount, FIRST_VALUE(0) AS send_order_count, FIRST_VALUE(0) AS in_transit_send_count, FIRST_VALUE(0) AS drop_send_count, FIRST_VALUE(0) AS effect_drop_send_count, FIRST_VALUE(0) AS drop_cancel_send_count, FIRST_VALUE(0) AS drop_send_ed_count, FIRST_VALUE(0) AS drop_send_psn_count, FIRST_VALUE(0) AS wechat_scan_drop_send_count, FIRST_VALUE(0) AS alipay_scan_drop_send_count, FIRST_VALUE(0) AS courier_get_count_02 , FIRST_VALUE(0) AS courier_get_count_02_06, FIRST_VALUE(0) AS courier_get_count_06_12, FIRST_VALUE(0) AS courier_get_count_12_24, FIRST_VALUE(0) AS courier_get_count_24, FIRST_VALUE(0) AS courier_get_count_day, FIRST_VALUE(0) AS courier_get_count_morrow_12, FIRST_VALUE(0) AS courier_get_count_morrow_gt_12, FIRST_VALUE(0) AS courier_get_internet_day, FIRST_VALUE(0) AS courier_get_internet_morrow_12, FIRST_VALUE(0) AS courier_get_internet_morrow_gt_12, FIRST_VALUE(0) AS drop_send_count_00_07, FIRST_VALUE(0) AS drop_send_count_07_17, FIRST_VALUE(0) AS drop_send_count_17_24, FIRST_VALUE(0) AS courier_get_count_day_00_07, FIRST_VALUE(0) AS courier_get_count_day_07_17, FIRST_VALUE(0) AS courier_get_count_day_17_24, FIRST_VALUE(0) AS courier_get_count_02_00_07, FIRST_VALUE(0) AS courier_get_count_02_06_00_07, FIRST_VALUE(0) AS courier_get_count_06_12_00_07, FIRST_VALUE(0) AS courier_get_count_12_24_00_07, FIRST_VALUE(0) AS courier_get_count_24_00_07, FIRST_VALUE(0) AS courier_get_count_02_07_17, FIRST_VALUE(0) AS courier_get_count_02_06_07_17, FIRST_VALUE(0) AS courier_get_count_06_12_07_17, FIRST_VALUE(0) AS courier_get_count_12_24_07_17, FIRST_VALUE(0) AS courier_get_count_24_07_17, FIRST_VALUE(0) AS courier_get_count_02_17_24, FIRST_VALUE(0) AS courier_get_count_02_06_17_24, FIRST_VALUE(0) AS courier_get_count_06_12_17_24, FIRST_VALUE(0) AS courier_get_count_12_24_17_24, FIRST_VALUE(0) AS courier_get_count_24_17_24, FIRST_VALUE(0) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT r.*, s.freight, s.express_company_id, s.express_company_name, s.channel_source, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction FROM (SELECT * FROM route_client_get_queue_kafka WHERE TimeOutOfRange(TO_TIMESTAMP(closeTime)) <= 1) r JOIN (SELECT * FROM fcbox_send_binlog WHERE send_from = 1 AND client_post_time IS NOT NULL AND (change_flag <> 3 OR change_flag IS NULl)) s ON s.express_no = r.expressId AND s.receiver_contact = r.receiverMobile ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.assetCode = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(closeTime, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), -99, direction, IF(boxType IS NOT NULL, boxType, -99); ``` ________________________________ 发件人: 601049502 <[hidden email]> 发送时间: 2020年11月13日 1:44 收件人: user-zh <[hidden email]> 主题: 回复：flink-1.11.2 执行checkpoint失败 首先，我想知道你是否使用了kafka sink？ 在 2020年11月12日 21:16，史 正超<[hidden email]> 写道： 执行checkpoint失败，报下面的错。 2020-11-12 21:04:56 org.apache.flink.util.FlinkRuntimeException: Exceeded checkpoint tolerable failure threshold. at org.apache.flink.runtime.checkpoint.CheckpointFailureManager.handleJobLevelCheckpointException(CheckpointFailureManager.java:66) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1673) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1650) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.access$600(CheckpointCoordinator.java:91) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator$CheckpointCanceller.run(CheckpointCoordinator.java:1783) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) |
|
|
sql简化后类似这样， 做checkpoint超时
CREATE VIEW fcbox_send_fat_view AS SELECT REPLACE(SUBSTR(client_post_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, count(xxx) FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status > 100 AND s.client_post_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.client_post_time)) <= 1 ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.point_code = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(client_post_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(user_create_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, count(xxx) FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, 'NA' AS throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status >= 100 AND (s.change_flag <> 3 or s.change_flag is null) AND s.user_create_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.user_create_time)) <= 1 ) t1 JOIN edbasic.basic_district_name_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.sender_city_name = d1.dis_name ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(user_create_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(closeTime, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, -99 AS payType, direction, IF(boxType IS NOT NULL, boxType, -99) AS boxType, count(xxx) FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT r.*, s.freight, s.express_company_id, s.express_company_name, s.channel_source, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction FROM (SELECT * FROM route_client_get_queue_kafka WHERE TimeOutOfRange(TO_TIMESTAMP(closeTime)) <= 1) r JOIN (SELECT * FROM fcbox_send_binlog WHERE send_from = 1 AND client_post_time IS NOT NULL AND (change_flag <> 3 OR change_flag IS NULl)) s ON s.express_no = r.expressId AND s.receiver_contact = r.receiverMobile ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.assetCode = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(closeTime, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), -99, direction, IF(boxType IS NOT NULL, boxType, -99); ________________________________ 发件人: 史 正超 <[hidden email]> 发送时间: 2020年11月13日 1:50 收件人: [hidden email] <[hidden email]> 主题: 回复: 回复：flink-1.11.2 执行checkpoint失败 没有，用的是jdbc sink，先是 三张change log的 left join，然后 再分别与两张mysql维表的join，执行checkpoint超时。sql太复杂了，我越写越没信心。。。 ``` CREATE VIEW fcbox_send_fat_view AS SELECT REPLACE(SUBSTR(client_post_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN SendRevenue(express_company_id, COALESCE(freight, 0)) ELSE CAST(0 AS BIGINT) END) AS send_donation_revenue, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN total_amount ELSE CAST(0 AS BIGINT) END) AS send_amount, FIRST_VALUE(0) AS send_order_count, COUNT(CASE WHEN send_status IN (103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS in_transit_send_count, COUNT(send_id) AS drop_send_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS effect_drop_send_count, COUNT(CASE WHEN send_status in (105,106,109) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS drop_cancel_send_count, COUNT(DISTINCT CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN point_code END) AS drop_send_ed_count, COUNT(DISTINCT CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN user_id END) AS drop_send_psn_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND send_type = 2 THEN send_id END) AS wechat_scan_drop_send_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND send_type = 3 THEN send_id END) AS alipay_scan_drop_send_count, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) <= 12 THEN send_id END) AS courier_get_count_morrow_12, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND ((TimeDayDiff(client_post_time, courier_get_time) > 1) OR (TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) > 12)) THEN send_id END) AS courier_get_count_morrow_gt_12, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_day, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) <= 12 THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_morrow_12, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND ((TimeDayDiff(client_post_time, courier_get_time) > 1) OR (TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) > 12)) THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_morrow_gt_12, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 THEN send_id END) AS drop_send_count_00_07, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 THEN send_id END) AS drop_send_count_07_17, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 THEN send_id END) AS drop_send_count_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_17_24, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN payment_amount ELSE CAST(0 AS BIGINT) END) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status > 100 AND s.client_post_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.client_post_time)) <= 1 ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.point_code = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(client_post_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(user_create_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, FIRST_VALUE(0) AS send_donation_revenue, FIRST_VALUE(0) AS send_amount, COUNT(1) AS send_order_count, FIRST_VALUE(0) AS in_transit_send_count, FIRST_VALUE(0) AS drop_send_count, FIRST_VALUE(0) AS effect_drop_send_count, FIRST_VALUE(0) AS drop_cancel_send_count, FIRST_VALUE(0) AS drop_send_ed_count, FIRST_VALUE(0) AS drop_send_psn_count, FIRST_VALUE(0) AS wechat_scan_drop_send_count, FIRST_VALUE(0) AS alipay_scan_drop_send_count, FIRST_VALUE(0) AS courier_get_count_02 , FIRST_VALUE(0) AS courier_get_count_02_06, FIRST_VALUE(0) AS courier_get_count_06_12, FIRST_VALUE(0) AS courier_get_count_12_24, FIRST_VALUE(0) AS courier_get_count_24, FIRST_VALUE(0) AS courier_get_count_day, FIRST_VALUE(0) AS courier_get_count_morrow_12, FIRST_VALUE(0) AS courier_get_count_morrow_gt_12, FIRST_VALUE(0) AS courier_get_internet_day, FIRST_VALUE(0) AS courier_get_internet_morrow_12, FIRST_VALUE(0) AS courier_get_internet_morrow_gt_12, FIRST_VALUE(0) AS drop_send_count_00_07, FIRST_VALUE(0) AS drop_send_count_07_17, FIRST_VALUE(0) AS drop_send_count_17_24, FIRST_VALUE(0) AS courier_get_count_day_00_07, FIRST_VALUE(0) AS courier_get_count_day_07_17, FIRST_VALUE(0) AS courier_get_count_day_17_24, FIRST_VALUE(0) AS courier_get_count_02_00_07, FIRST_VALUE(0) AS courier_get_count_02_06_00_07, FIRST_VALUE(0) AS courier_get_count_06_12_00_07, FIRST_VALUE(0) AS courier_get_count_12_24_00_07, FIRST_VALUE(0) AS courier_get_count_24_00_07, FIRST_VALUE(0) AS courier_get_count_02_07_17, FIRST_VALUE(0) AS courier_get_count_02_06_07_17, FIRST_VALUE(0) AS courier_get_count_06_12_07_17, FIRST_VALUE(0) AS courier_get_count_12_24_07_17, FIRST_VALUE(0) AS courier_get_count_24_07_17, FIRST_VALUE(0) AS courier_get_count_02_17_24, FIRST_VALUE(0) AS courier_get_count_02_06_17_24, FIRST_VALUE(0) AS courier_get_count_06_12_17_24, FIRST_VALUE(0) AS courier_get_count_12_24_17_24, FIRST_VALUE(0) AS courier_get_count_24_17_24, FIRST_VALUE(0) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, 'NA' AS throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status >= 100 AND (s.change_flag <> 3 or s.change_flag is null) AND s.user_create_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.user_create_time)) <= 1 ) t1 JOIN edbasic.basic_district_name_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.sender_city_name = d1.dis_name ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(user_create_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(closeTime, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, -99 AS payType, direction, IF(boxType IS NOT NULL, boxType, -99) AS boxType, SUM(SendRevenue(express_company_id, COALESCE(freight, 0))) AS send_freight_collect_revenue, FIRST_VALUE(0) AS send_donation_revenue, FIRST_VALUE(0) AS send_amount, FIRST_VALUE(0) AS send_order_count, FIRST_VALUE(0) AS in_transit_send_count, FIRST_VALUE(0) AS drop_send_count, FIRST_VALUE(0) AS effect_drop_send_count, FIRST_VALUE(0) AS drop_cancel_send_count, FIRST_VALUE(0) AS drop_send_ed_count, FIRST_VALUE(0) AS drop_send_psn_count, FIRST_VALUE(0) AS wechat_scan_drop_send_count, FIRST_VALUE(0) AS alipay_scan_drop_send_count, FIRST_VALUE(0) AS courier_get_count_02 , FIRST_VALUE(0) AS courier_get_count_02_06, FIRST_VALUE(0) AS courier_get_count_06_12, FIRST_VALUE(0) AS courier_get_count_12_24, FIRST_VALUE(0) AS courier_get_count_24, FIRST_VALUE(0) AS courier_get_count_day, FIRST_VALUE(0) AS courier_get_count_morrow_12, FIRST_VALUE(0) AS courier_get_count_morrow_gt_12, FIRST_VALUE(0) AS courier_get_internet_day, FIRST_VALUE(0) AS courier_get_internet_morrow_12, FIRST_VALUE(0) AS courier_get_internet_morrow_gt_12, FIRST_VALUE(0) AS drop_send_count_00_07, FIRST_VALUE(0) AS drop_send_count_07_17, FIRST_VALUE(0) AS drop_send_count_17_24, FIRST_VALUE(0) AS courier_get_count_day_00_07, FIRST_VALUE(0) AS courier_get_count_day_07_17, FIRST_VALUE(0) AS courier_get_count_day_17_24, FIRST_VALUE(0) AS courier_get_count_02_00_07, FIRST_VALUE(0) AS courier_get_count_02_06_00_07, FIRST_VALUE(0) AS courier_get_count_06_12_00_07, FIRST_VALUE(0) AS courier_get_count_12_24_00_07, FIRST_VALUE(0) AS courier_get_count_24_00_07, FIRST_VALUE(0) AS courier_get_count_02_07_17, FIRST_VALUE(0) AS courier_get_count_02_06_07_17, FIRST_VALUE(0) AS courier_get_count_06_12_07_17, FIRST_VALUE(0) AS courier_get_count_12_24_07_17, FIRST_VALUE(0) AS courier_get_count_24_07_17, FIRST_VALUE(0) AS courier_get_count_02_17_24, FIRST_VALUE(0) AS courier_get_count_02_06_17_24, FIRST_VALUE(0) AS courier_get_count_06_12_17_24, FIRST_VALUE(0) AS courier_get_count_12_24_17_24, FIRST_VALUE(0) AS courier_get_count_24_17_24, FIRST_VALUE(0) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT r.*, s.freight, s.express_company_id, s.express_company_name, s.channel_source, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction FROM (SELECT * FROM route_client_get_queue_kafka WHERE TimeOutOfRange(TO_TIMESTAMP(closeTime)) <= 1) r JOIN (SELECT * FROM fcbox_send_binlog WHERE send_from = 1 AND client_post_time IS NOT NULL AND (change_flag <> 3 OR change_flag IS NULl)) s ON s.express_no = r.expressId AND s.receiver_contact = r.receiverMobile ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.assetCode = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(closeTime, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), -99, direction, IF(boxType IS NOT NULL, boxType, -99); ``` ________________________________ 发件人: 601049502 <[hidden email]> 发送时间: 2020年11月13日 1:44 收件人: user-zh <[hidden email]> 主题: 回复：flink-1.11.2 执行checkpoint失败 首先，我想知道你是否使用了kafka sink？ 在 2020年11月12日 21:16，史 正超<[hidden email]> 写道： 执行checkpoint失败，报下面的错。 2020-11-12 21:04:56 org.apache.flink.util.FlinkRuntimeException: Exceeded checkpoint tolerable failure threshold. at org.apache.flink.runtime.checkpoint.CheckpointFailureManager.handleJobLevelCheckpointException(CheckpointFailureManager.java:66) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1673) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1650) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.access$600(CheckpointCoordinator.java:91) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator$CheckpointCanceller.run(CheckpointCoordinator.java:1783) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) |
|
|
In reply to this post by 史 正超
这个问题是升级之后才出的问题吗？还是直接在1.11.2版本开发的？如果是在之前版本开发的，并且在之前版本可以正常运行。可以考虑是否是组件版本不兼容的问题，可以把debug日志打出来 看看有什么细节报错信息
------------------ 原始邮件 ------------------ 发件人: "user-zh" <[hidden email]>; 发送时间: 2020年11月13日(星期五) 上午9:50 收件人: "[hidden email]"<[hidden email]>; 主题: 回复: 回复：flink-1.11.2 执行checkpoint失败 没有，用的是jdbc sink，先是 三张change log的 left join，然后 再分别与两张mysql维表的join，执行checkpoint超时。sql太复杂了，我越写越没信心。。。 ``` CREATE VIEW fcbox_send_fat_view AS SELECT REPLACE(SUBSTR(client_post_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN SendRevenue(express_company_id, COALESCE(freight, 0)) ELSE CAST(0 AS BIGINT) END) AS send_donation_revenue, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN total_amount ELSE CAST(0 AS BIGINT) END) AS send_amount, FIRST_VALUE(0) AS send_order_count, COUNT(CASE WHEN send_status IN (103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS in_transit_send_count, COUNT(send_id) AS drop_send_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS effect_drop_send_count, COUNT(CASE WHEN send_status in (105,106,109) AND (change_flag <> 3 OR change_flag IS NULL) THEN send_id END) AS drop_cancel_send_count, COUNT(DISTINCT CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN point_code END) AS drop_send_ed_count, COUNT(DISTINCT CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN user_id END) AS drop_send_psn_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND send_type = 2 THEN send_id END) AS wechat_scan_drop_send_count, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND send_type = 3 THEN send_id END) AS alipay_scan_drop_send_count, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) <= 12 THEN send_id END) AS courier_get_count_morrow_12, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND ((TimeDayDiff(client_post_time, courier_get_time) > 1) OR (TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) > 12)) THEN send_id END) AS courier_get_count_morrow_gt_12, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_day, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) <= 12 THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_morrow_12, SUM(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND ((TimeDayDiff(client_post_time, courier_get_time) > 1) OR (TimeDayDiff(client_post_time, courier_get_time) = 1 AND CAST(SUBSTR(courier_get_time, 12, 2) AS INT) > 12)) THEN (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600/60 ELSE CAST(0 AS BIGINT) END) AS courier_get_internet_morrow_gt_12, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 THEN send_id END) AS drop_send_count_00_07, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 THEN send_id END) AS drop_send_count_07_17, COUNT(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 THEN send_id END) AS drop_send_count_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND SUBSTR(client_post_time, 1, 10) = SUBSTR(courier_get_time, 1, 10) THEN send_id END) AS courier_get_count_day_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 0 AND HOUR(TO_TIMESTAMP(client_post_time)) < 8 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_00_07, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 8 AND HOUR(TO_TIMESTAMP(client_post_time)) < 17 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_07_17, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 0 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 2 THEN send_id END) AS courier_get_count_02_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 2 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 6 THEN send_id END) AS courier_get_count_02_06_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 6 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 12 THEN send_id END) AS courier_get_count_06_12_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 12 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 < 24 THEN send_id END) AS courier_get_count_12_24_17_24, COUNT(CASE WHEN send_status IN (102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) AND courier_get_time IS NOT NULL AND HOUR(TO_TIMESTAMP(client_post_time)) >= 17 AND HOUR(TO_TIMESTAMP(client_post_time)) < 24 AND (UNIX_TIMESTAMP(courier_get_time) - UNIX_TIMESTAMP(client_post_time))/3600 >= 24 THEN send_id END) AS courier_get_count_24_17_24, SUM(CASE WHEN send_status IN (101, 102, 103, 104) AND (change_flag <> 3 OR change_flag IS NULL) THEN payment_amount ELSE CAST(0 AS BIGINT) END) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status > 100 AND s.client_post_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.client_post_time)) <= 1 ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.point_code = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(client_post_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(user_create_time, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, IF(pay_type IS NOT NULL, pay_type, -99) AS payType, direction, IF(box_type IS NOT NULL, box_type, -99) AS boxType, FIRST_VALUE(0) AS send_freight_collect_revenue, FIRST_VALUE(0) AS send_donation_revenue, FIRST_VALUE(0) AS send_amount, COUNT(1) AS send_order_count, FIRST_VALUE(0) AS in_transit_send_count, FIRST_VALUE(0) AS drop_send_count, FIRST_VALUE(0) AS effect_drop_send_count, FIRST_VALUE(0) AS drop_cancel_send_count, FIRST_VALUE(0) AS drop_send_ed_count, FIRST_VALUE(0) AS drop_send_psn_count, FIRST_VALUE(0) AS wechat_scan_drop_send_count, FIRST_VALUE(0) AS alipay_scan_drop_send_count, FIRST_VALUE(0) AS courier_get_count_02 , FIRST_VALUE(0) AS courier_get_count_02_06, FIRST_VALUE(0) AS courier_get_count_06_12, FIRST_VALUE(0) AS courier_get_count_12_24, FIRST_VALUE(0) AS courier_get_count_24, FIRST_VALUE(0) AS courier_get_count_day, FIRST_VALUE(0) AS courier_get_count_morrow_12, FIRST_VALUE(0) AS courier_get_count_morrow_gt_12, FIRST_VALUE(0) AS courier_get_internet_day, FIRST_VALUE(0) AS courier_get_internet_morrow_12, FIRST_VALUE(0) AS courier_get_internet_morrow_gt_12, FIRST_VALUE(0) AS drop_send_count_00_07, FIRST_VALUE(0) AS drop_send_count_07_17, FIRST_VALUE(0) AS drop_send_count_17_24, FIRST_VALUE(0) AS courier_get_count_day_00_07, FIRST_VALUE(0) AS courier_get_count_day_07_17, FIRST_VALUE(0) AS courier_get_count_day_17_24, FIRST_VALUE(0) AS courier_get_count_02_00_07, FIRST_VALUE(0) AS courier_get_count_02_06_00_07, FIRST_VALUE(0) AS courier_get_count_06_12_00_07, FIRST_VALUE(0) AS courier_get_count_12_24_00_07, FIRST_VALUE(0) AS courier_get_count_24_00_07, FIRST_VALUE(0) AS courier_get_count_02_07_17, FIRST_VALUE(0) AS courier_get_count_02_06_07_17, FIRST_VALUE(0) AS courier_get_count_06_12_07_17, FIRST_VALUE(0) AS courier_get_count_12_24_07_17, FIRST_VALUE(0) AS courier_get_count_24_07_17, FIRST_VALUE(0) AS courier_get_count_02_17_24, FIRST_VALUE(0) AS courier_get_count_02_06_17_24, FIRST_VALUE(0) AS courier_get_count_06_12_17_24, FIRST_VALUE(0) AS courier_get_count_12_24_17_24, FIRST_VALUE(0) AS courier_get_count_24_17_24, FIRST_VALUE(0) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, 'NA' AS throw_area_name FROM ( SELECT s.*, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction, i.transport_time, i.send_type, o.pay_type FROM fcbox_send_binlog s LEFT JOIN fcbox_send_item_binlog i ON s.send_id = i.send_id LEFT JOIN fcbox_order_binlog o ON s.send_id = o.send_id WHERE s.send_from = 1 AND s.send_status >= 100 AND (s.change_flag <> 3 or s.change_flag is null) AND s.user_create_time IS NOT NULL AND TimeOutOfRange(TO_TIMESTAMP(s.user_create_time)) <= 1 ) t1 JOIN edbasic.basic_district_name_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.sender_city_name = d1.dis_name ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(user_create_time, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), IF(pay_type IS NOT NULL, pay_type, -99), direction, IF(box_type IS NOT NULL, box_type, -99) UNION ALL SELECT REPLACE(SUBSTR(closeTime, 1, 10), '-', '') AS staDate, disCode, IF(dis_name IS NOT NULL, dis_name, 'NA') AS disName, dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA') AS expressCompanyId, IF(express_company_name IS NOT NULL, express_company_name, 'NA') AS expressCompanyName, IF(throw_area_name IS NOT NULL, throw_area_name, 'NA') AS throwAreaName, IF(channel_source IS NOT NULL, channel_source, -99) channelSource, -99 AS payType, direction, IF(boxType IS NOT NULL, boxType, -99) AS boxType, SUM(SendRevenue(express_company_id, COALESCE(freight, 0))) AS send_freight_collect_revenue, FIRST_VALUE(0) AS send_donation_revenue, FIRST_VALUE(0) AS send_amount, FIRST_VALUE(0) AS send_order_count, FIRST_VALUE(0) AS in_transit_send_count, FIRST_VALUE(0) AS drop_send_count, FIRST_VALUE(0) AS effect_drop_send_count, FIRST_VALUE(0) AS drop_cancel_send_count, FIRST_VALUE(0) AS drop_send_ed_count, FIRST_VALUE(0) AS drop_send_psn_count, FIRST_VALUE(0) AS wechat_scan_drop_send_count, FIRST_VALUE(0) AS alipay_scan_drop_send_count, FIRST_VALUE(0) AS courier_get_count_02 , FIRST_VALUE(0) AS courier_get_count_02_06, FIRST_VALUE(0) AS courier_get_count_06_12, FIRST_VALUE(0) AS courier_get_count_12_24, FIRST_VALUE(0) AS courier_get_count_24, FIRST_VALUE(0) AS courier_get_count_day, FIRST_VALUE(0) AS courier_get_count_morrow_12, FIRST_VALUE(0) AS courier_get_count_morrow_gt_12, FIRST_VALUE(0) AS courier_get_internet_day, FIRST_VALUE(0) AS courier_get_internet_morrow_12, FIRST_VALUE(0) AS courier_get_internet_morrow_gt_12, FIRST_VALUE(0) AS drop_send_count_00_07, FIRST_VALUE(0) AS drop_send_count_07_17, FIRST_VALUE(0) AS drop_send_count_17_24, FIRST_VALUE(0) AS courier_get_count_day_00_07, FIRST_VALUE(0) AS courier_get_count_day_07_17, FIRST_VALUE(0) AS courier_get_count_day_17_24, FIRST_VALUE(0) AS courier_get_count_02_00_07, FIRST_VALUE(0) AS courier_get_count_02_06_00_07, FIRST_VALUE(0) AS courier_get_count_06_12_00_07, FIRST_VALUE(0) AS courier_get_count_12_24_00_07, FIRST_VALUE(0) AS courier_get_count_24_00_07, FIRST_VALUE(0) AS courier_get_count_02_07_17, FIRST_VALUE(0) AS courier_get_count_02_06_07_17, FIRST_VALUE(0) AS courier_get_count_06_12_07_17, FIRST_VALUE(0) AS courier_get_count_12_24_07_17, FIRST_VALUE(0) AS courier_get_count_24_07_17, FIRST_VALUE(0) AS courier_get_count_02_17_24, FIRST_VALUE(0) AS courier_get_count_02_06_17_24, FIRST_VALUE(0) AS courier_get_count_06_12_17_24, FIRST_VALUE(0) AS courier_get_count_12_24_17_24, FIRST_VALUE(0) AS courier_get_count_24_17_24, FIRST_VALUE(0) AS pay_amount FROM ( SELECT t3.*, dis_name FROM ( SELECT t2.*, disCode, dislv FROM ( SELECT t1.*, dis_code, throw_area_name FROM ( SELECT r.*, s.freight, s.express_company_id, s.express_company_name, s.channel_source, CASE WHEN sender_province_id = receiver_province_id AND sender_city_id = receiver_city_id THEN 1 WHEN sender_province_id = receiver_province_id AND sender_city_id <> sender_city_id THEN 2 WHEN sender_province_id <> receiver_province_id THEN 3 ELSE -99 END AS direction FROM (SELECT * FROM route_client_get_queue_kafka WHERE TimeOutOfRange(TO_TIMESTAMP(closeTime)) <= 1) r JOIN (SELECT * FROM fcbox_send_binlog WHERE send_from = 1 AND client_post_time IS NOT NULL AND (change_flag <> 3 OR change_flag IS NULl)) s ON s.express_no = r.expressId AND s.receiver_contact = r.receiverMobile ) t1 JOIN edbasic.basic_edinfo_16 FOR SYSTEM_TIME AS OF t1.proctime d1 ON t1.assetCode = d1.ed_code ) t2, LATERAL TABLE(RecursionDiscode(dis_code)) AS T(disCode, dislv) ) t3 JOIN edbasic.basic_district_16 FOR SYSTEM_TIME AS OF t3.proctime d2 ON t3.disCode = d2.dis_code ) GROUP BY REPLACE(SUBSTR(closeTime, 1, 10), '-', ''), disCode, IF(dis_name IS NOT NULL, dis_name, 'NA'), dislv, IF(express_company_id IS NOT NULL, express_company_id, 'NA'), IF(express_company_name IS NOT NULL, express_company_name, 'NA'), IF(throw_area_name IS NOT NULL, throw_area_name, 'NA'), IF(channel_source IS NOT NULL, channel_source, -99), -99, direction, IF(boxType IS NOT NULL, boxType, -99); ``` ________________________________ 发件人: 601049502 <[hidden email]> 发送时间: 2020年11月13日 1:44 收件人: user-zh <[hidden email]> 主题: 回复：flink-1.11.2 执行checkpoint失败 首先，我想知道你是否使用了kafka sink？ 在 2020年11月12日 21:16，史 正超<[hidden email]> 写道： 执行checkpoint失败，报下面的错。 2020-11-12 21:04:56 org.apache.flink.util.FlinkRuntimeException: Exceeded checkpoint tolerable failure threshold. at org.apache.flink.runtime.checkpoint.CheckpointFailureManager.handleJobLevelCheckpointException(CheckpointFailureManager.java:66) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1673) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.abortPendingCheckpoint(CheckpointCoordinator.java:1650) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator.access$600(CheckpointCoordinator.java:91) at org.apache.flink.runtime.checkpoint.CheckpointCoordinator$CheckpointCanceller.run(CheckpointCoordinator.java:1783) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) |
«
Return to Apache Flink 中文用户邮件列表
|
1 view|%1 views
| Free forum by Nabble | Edit this page |