二百七十四、Kettle——ClickHouse中对错误数据表中举行数据修复（及时）

一、目标

在完成数据洗濯、错误数据之后，须要根据修复规则对错误数据举行修复
二、Hive中原有代码

1. insert into table  hurys_db.dwd_queue  partition(day)
2. select
3.        a3.id,
4.        a3.device_no,
5.        a3.source_device_type,
6.        a3.sn,
7.        a3.model,
8.        a3.create_time,
9.        a3.lane_no,
10.        a3.lane_type,
11.        case when a3.queue_count between 0 and 100 then a3.queue_count else a2.avg_queue_count end as queue_count,
12.        case when a3.queue_len   between 0 and 500 then a3.queue_len  else a2.avg_queue_len  end as queue_len,
13.        case when a3.queue_head  between 0 and 500 then a3.queue_head else a2.avg_queue_head end as queue_head,
14.        case when a3.queue_tail  between 0 and 500 then a3.queue_tail else a2.avg_queue_tail end as queue_tail,
15.        a3.day
16. from hurys_db.dwd_queue_error as a3
17. right join (select
18.        a1.device_no,
19.        a1.create_time,
20.        a1.lane_no,
21.        round(avg(queue_count),0)     avg_queue_count,
22.        round(avg(queue_len),2)       avg_queue_len,
23.        round(avg(queue_head),2)      avg_queue_head,
24.        round(avg(queue_tail),2)      avg_queue_tail
25. from(select
26. t1.device_no, t1.create_time start_time, t2.create_time, t1.lane_no,
27. t1.queue_count, t1.queue_len, t1.queue_head, t1.queue_tail
28. from hurys_db.dwd_queue as t1
29. right join hurys_db.dwd_queue_error as t2
30. on t2.device_no=t1.device_no  and  t2.lane_no=t1.lane_no
31.     and  concat(date_sub(t2.create_time,7),substr(t2.create_time,11,10)) = t1.create_time
32. where t1.device_no is not null
33. union all
34. select
35. t1.device_no, t1.create_time start_time, t2.create_time, t1.lane_no,
36. t1.queue_count, t1.queue_len, t1.queue_head, t1.queue_tail
37. from hurys_db.dwd_queue as t1
38. right join hurys_db.dwd_queue_error as t2
39. on t2.device_no=t1.device_no  and  t2.lane_no=t1.lane_no
40.     and  concat(date_sub(t2.create_time,14),substr(t2.create_time,11,10)) = t1.create_time
41. where t1.device_no is not null
42. union all
43. select
44. t1.device_no, t1.create_time start_time, t2.create_time, t1.lane_no,
45. t1.queue_count, t1.queue_len, t1.queue_head, t1.queue_tail
46. from hurys_db.dwd_queue as t1
47. right join hurys_db.dwd_queue_error as t2
48. on t2.device_no=t1.device_no  and  t2.lane_no=t1.lane_no
49.     and  concat(date_sub(t2.create_time,21),substr(t2.create_time,11,10)) = t1.cr

复制代码