You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-zh@flink.apache.org by 史 正超 <sh...@outlook.com> on 2020/11/13 01:50:44 UTC
回复: 回复：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 <60...@qq.com>
发送时间: 2020年11月13日 1:44
收件人: user-zh <us...@flink.apache.org>
主题: 回复：flink-1.11.2 执行checkpoint失败
首先，我想知道你是否使用了kafka sink？
在 2020年11月12日 21:16，史 正超<sh...@outlook.com> 写道：
执行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)
回复：回复: 回复：flink-1.11.2 执行checkpoint失败
Posted by ﹏〆﹎゛ 独家记忆 <60...@qq.com>.
这个问题是升级之后才出的问题吗？还是直接在1.11.2版本开发的？如果是在之前版本开发的，并且在之前版本可以正常运行。可以考虑是否是组件版本不兼容的问题，可以把debug日志打出来 看看有什么细节报错信息
------------------ 原始邮件 ------------------
发件人: "user-zh" <shizhengchao@outlook.com>;
发送时间: 2020年11月13日(星期五) 上午9:50
收件人: "user-zh@flink.apache.org"<user-zh@flink.apache.org>;
主题: 回复: 回复：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 <601049502@qq.com>
发送时间: 2020年11月13日 1:44
收件人: user-zh <user-zh@flink.apache.org>
主题: 回复：flink-1.11.2 执行checkpoint失败
首先，我想知道你是否使用了kafka sink？
在 2020年11月12日 21:16，史 正超<shizhengchao@outlook.com> 写道：
执行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)
回复: 回复：flink-1.11.2 执行checkpoint失败
Posted by 史 正超 <sh...@outlook.com>.
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);
________________________________
发件人: 史 正超 <sh...@outlook.com>
发送时间: 2020年11月13日 1:50
收件人: user-zh@flink.apache.org <us...@flink.apache.org>
主题: 回复: 回复：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 <60...@qq.com>
发送时间: 2020年11月13日 1:44
收件人: user-zh <us...@flink.apache.org>
主题: 回复：flink-1.11.2 执行checkpoint失败
首先，我想知道你是否使用了kafka sink？
在 2020年11月12日 21:16，史 正超<sh...@outlook.com> 写道：
执行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)