??????????????????????????????????????????1.11.2??????????????????????????????????????????????????????????????????????????????????????????????????????????debug??????????
??????????????????????
------------------ ???????? ------------------
??????:
"user-zh"
<[email protected]>;
????????: 2020??11??13??(??????) ????9:50
??????: "[email protected]"<[email protected]>;
????: ????: ??????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 <[email protected]>
????????: 2020??11??13?? 1:44
??????: user-zh <[email protected]>
????: ??????flink-1.11.2 ????checkpoint????
??????????????????????????kafka sink??
?? 2020??11??12?? 21:16???? ????<[email protected]> ??????
????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)
