Re: flink1.17.1版本 flink sql多表关联优化

[xiaohui zhang]

这种join写法会随时更新里面每一个字段，最终产出结果的业务含义是什么呢？
如果是取每个vehicle_code对应的最新统计指标值，是否可以用支持partial update的存储，用多个单独的sql直接写入目前就可以了

周先明  于2023年8月4日周五 11:01写道：

> Regular Join 默认把数据都存储在State中，通常会结合TTL来进行优化
>
> guanyq  于2023年8月3日周四 15:59写道：
>
> > 请问下多个表关联，这种flink sql如何优化呢，直接关联优点跑不动RuntimeExecutionMode.STREAMING 模式
> >
> > select
> > date_format(a.create_time, '-MM-dd HH:mm:ss') as create_time,
> > b.vehicle_code,
> > a.item_name,
> > a.item_value,
> > c.item_value as vehicle_score,
> > d.current_fault,
> > e.history_fault,
> > f.late_mileage,
> > g.fault_level_event_count,
> > h.current_fault_subsystem,
> > i.history_fault_subsystem
> > from fault_record_subsystem a
> > join mtr_vehicle_use b on a.vehicle_id = b.vehicle_id
> > join fault_record_vehicle c on a.vehicle_id = c.vehicle_id
> > join fault_record_current_count d on a.vehicle_id = d.vehicle_id
> > join fault_record_history_count e on a.vehicle_id = e.vehicle_id
> > join vehicle_usage_score f on a.vehicle_id = f.vehicle_id
> > join fault_record_level_event_count g on a.vehicle_id = g.vehicle_id
> > join fault_record_current_count_subsystem h on a.vehicle_id =
> h.vehicle_id
> > and a.item_name = h.item_name
> > join fault_record_history_count_subsystem i on a.vehicle_id =
> i.vehicle_id
> > and a.item_name = i.item_name
>

Re: flink1.17.1版本 flink sql多表关联优化

[周先明]

Regular Join 默认把数据都存储在State中，通常会结合TTL来进行优化

guanyq  于2023年8月3日周四 15:59写道：

> 请问下多个表关联，这种flink sql如何优化呢，直接关联优点跑不动RuntimeExecutionMode.STREAMING 模式
>
> select
> date_format(a.create_time, '-MM-dd HH:mm:ss') as create_time,
> b.vehicle_code,
> a.item_name,
> a.item_value,
> c.item_value as vehicle_score,
> d.current_fault,
> e.history_fault,
> f.late_mileage,
> g.fault_level_event_count,
> h.current_fault_subsystem,
> i.history_fault_subsystem
> from fault_record_subsystem a
> join mtr_vehicle_use b on a.vehicle_id = b.vehicle_id
> join fault_record_vehicle c on a.vehicle_id = c.vehicle_id
> join fault_record_current_count d on a.vehicle_id = d.vehicle_id
> join fault_record_history_count e on a.vehicle_id = e.vehicle_id
> join vehicle_usage_score f on a.vehicle_id = f.vehicle_id
> join fault_record_level_event_count g on a.vehicle_id = g.vehicle_id
> join fault_record_current_count_subsystem h on a.vehicle_id = h.vehicle_id
> and a.item_name = h.item_name
> join fault_record_history_count_subsystem i on a.vehicle_id = i.vehicle_id
> and a.item_name = i.item_name

flink1.17.1版本 flink sql多表关联优化

[guanyq]

请问下多个表关联，这种flink sql如何优化呢，直接关联优点跑不动RuntimeExecutionMode.STREAMING 模式

select
date_format(a.create_time, '-MM-dd HH:mm:ss') as create_time,
b.vehicle_code,
a.item_name,
a.item_value,
c.item_value as vehicle_score,
d.current_fault,
e.history_fault,
f.late_mileage,
g.fault_level_event_count,
h.current_fault_subsystem,
i.history_fault_subsystem
from fault_record_subsystem a
join mtr_vehicle_use b on a.vehicle_id = b.vehicle_id
join fault_record_vehicle c on a.vehicle_id = c.vehicle_id
join fault_record_current_count d on a.vehicle_id = d.vehicle_id
join fault_record_history_count e on a.vehicle_id = e.vehicle_id
join vehicle_usage_score f on a.vehicle_id = f.vehicle_id
join fault_record_level_event_count g on a.vehicle_id = g.vehicle_id
join fault_record_current_count_subsystem h on a.vehicle_id = h.vehicle_id and 
a.item_name = h.item_name
join fault_record_history_count_subsystem i on a.vehicle_id = i.vehicle_id and 
a.item_name = i.item_name