二百七十、Kettle——ClickHouse中增量导入清洗数据错误表

一、目的

比如原始数据100条，清洗后，90条正确数据在DWD层清洗表，10条错误数据在DWD层清洗数据错误表，所以清洗数据错误表任务一定要放在清洗表任务之后。

更关键的是，Hive中原本的SQL语句，放在ClickHouse需要大改，头大！而且Kettle任务要想定时增量导入，既与清洗数据错误表最新时间相关，又与DWD层清洗表最新时间相关，搞了大半天才搞定！

二、Hive中原有代码

2.1 表结构

--21、静态排队错误数据表——动态分区  dwd_queue_error
create  table  if not exists  hurys_db.dwd_queue_error(id                  string          comment '唯一ID',device_no           string          comment '设备编号',source_device_type  string          comment '设备类型',sn                  string          comment '设备序列号 ',model               string          comment '设备型号',create_time         string       comment '创建时间',lane_no             int             comment '车道编号',lane_type           int             comment '车道类型 0:渠化1:来向2:出口3:去向4:左弯待转区5:直行待行区6:右转专用道99:未定义车道',queue_count         int             comment '排队车辆数',queue_len           float           comment '排队长度(m)',queue_head          float           comment '排队头车距停止线距离（m）',queue_tail          float           comment '排队尾车距停止线距离（m）'
)
comment '静态排队错误数据表——动态分区'
partitioned by (day string)
stored as orc
;

2.2 SQL代码

--动态插入数据
insert  overwrite  table  hurys_db.dwd_queue_error  partition(day)
select
UUID()  as  id,
t2.device_no, t2.source_device_type, t2.sn, t2.model, t2.create_time,t2.lane_no, t2.lane_type,
t2.queue_count, t2.queue_len, t2.queue_head, t2.queue_tail, t2.day
from hurys_db.ods_queue as t2
left join hurys_db.dwd_queue as t3
on t3.device_no=t2.device_no and t3.create_time=t2.create_time and t3.lane_no=t2.lane_no
where t3.device_no is null and t3.create_time is null and t3.lane_no is null and t2.day='2024-09-10'
;

原有Hive代码很简单，然后把代码变成脚本，放在海豚定时调度即可，都很简单！

三、ClickHouse中现有代码

3.1 表结构

--21 静态排队数据错误表(长期存储)
create  table  if not exists  hurys_jw.dwd_queue_error(id                  String                       comment '唯一ID',device_no           String             comment '设备编号',source_device_type  Nullable(String)             comment '设备类型',sn                  Nullable(String)             comment '设备序列号 ',model               Nullable(String)             comment '设备型号',create_time         DateTime                     comment '创建时间',lane_no             Int32              comment '车道编号',lane_type           Nullable(Int32)              comment '车道类型 0:渠化1:来向2:出口3:去向4:左弯待转区5:直行待行区6:右转专用道99:未定义车道',queue_count         Int32              comment '排队车辆数',queue_len           Decimal(10, 2)     comment '排队长度(m)',queue_head          Decimal(10, 2)     comment '排队头车距停止线距离（m）',queue_tail          Decimal(10, 2)     comment '排队尾车距停止线距离（m）',day                 Date                         comment '日期'
)
ENGINE = MergeTree
PARTITION BY day
PRIMARY KEY (day,id)
ORDER BY (day,id)
SETTINGS index_granularity = 8192;

注意：由于后面数据清洗记录表需要，因此部分清洗规则的字段不能用Nullable，这也是后面的一大坑！

3.2 SQL代码

select
generateUUIDv4()  as  id,
device_no, source_device_type, sn, model, create_time,
lane_no, lane_type, queue_count, queue_len, queue_head, queue_tail,
cast(day as String) day
from (selectt2.device_no, t2.source_device_type, t2.sn, t2.model,t2.create_time,t2.lane_no, t2.lane_type,t2.queue_count, t2.queue_len, t2.queue_head, t2.queue_tail, toDate(t2.create_time) dayfrom hurys_jw.ods_queue as t2ANTI join hurys_jw.dwd_queue as t3on t3.device_no=t2.device_no and t3.create_time=t2.create_time and t3.lane_no=t2.lane_no)
--where  create_time > ?
;

注意：1 生成uuid字段，Hive中是UUID() as id，而ClickHouse中是generateUUIDv4() as id

           2 ClickHouse中with语句好像不是支持，不知道是不是版本问题

           3 ClickHouse中有ANTI join函数

           4 Kettle里需要把Date字段的day变成cast(day as String) day

3.3 Kettle任务

3.3.1 newtime

获取目标表dwd_queue_error的最新时间create_time

3.3.2 替换NULL值

3.3.3 clickhouse输入

select 
generateUUIDv4()  as  id,
device_no, source_device_type, sn, model, create_time,
lane_no, 
lane_type, queue_count, queue_len, queue_head, queue_tail,
cast(day as String) day
from (
select t2.device_no, t2.source_device_type, t2.sn, t2.model,t2.create_time,t2.lane_no, t2.lane_type,
t2.queue_count, t2.queue_len, t2.queue_head, t2.queue_tail, toDate(t2.create_time) day
from hurys_jw.ods_queue as t2
ANTI join hurys_jw.dwd_queue as t3
on t3.device_no=t2.device_no and t3.create_time=t2.create_time and t3.lane_no=t2.lane_no
)
where  create_time > ?
;

3.3.4 字段选择

3.3.5 newtime3

获取清洗表dwd_queue的最新时间create_time3

3.3.6 替换NULL值3

3.3.7 记录关联 (笛卡尔输出)

注意：清洗表dwd_queue的最新时间create_time3要大于等于目标表dwd_queue_error的最新时间create_time

3.3.8 clickhouse输出

3.3.9 保存后先执行清洗表dwd_queue任务，再执行dwd_queue_error任务

3.3.10 配置海豚调度任务

搞定！！！