同样的表、数据量、类似sql，在greenplum单机下可以查询，速度稍慢点，但可以接收。Doris运行了一会后直接提示内存不足。具体信息如下：
- SQL执行后，报下面错误。
数据源执行SQL失败:INTERNAL: java.lang.RuntimeException: SQL execute error by datasource… java.sql.SQLException: errCode = 2, detailMessage = (...)[MEM_LIMIT_EXCEEDED]Process has no memory available, cancel top memory usage query: query memory tracker <Query#Id=eb4f0b153dcd4d4d-a9345542489cbc96> consumption 25.55 GB, backend ... process memory used 11.63 GB exceed limit 12.40 GB or sys mem available 1.52 GB less than low water mark 1.55 GB. Execute again after enough memory
-
SQL中个表数据量如下：
1、dwd_40104_working_condition_di：600万
2、dwd_40104_collect_liquid_di：2151万
3、dwd_40104_collect_flow_di：2940万
4、dwd_40104_collect_pressure_di：4421万
5、dwd_40104_energy_consumption_di：1877万
6、dim开头几个表，最大不超过4万。 -
SQL语句如下
with base as (
select
fac.code as fac_code,
fac.`name` as fac_name,
poi.code as point_code,
poi.`name` as point_name,
dict.dict_code as pt,
dict.dict_name as pt_name,
dev.code as dev_code,
dev.`name` as dev_name
from
beibei.dim_40101_device_5mmf dev
LEFT join beibei.dim_40103_watch_point_5mmf poi
on
dev.watchpoint_code = poi.code
and dev.s_stat = '1'
left join beibei.dim_40102_facilities_5mmf fac
on
poi.facilities_code = fac.code
and fac.s_stat = '1'
left join beibei.dim_401_dict dict
on
dict.dict_code = poi.process_section_type
and dict.type_code = 'dc401_production_process_section'
and dict.s_stat = '1'
order by
dev_name
)
-- 液位
select
flow.fac_name,
flow.fac_code,
liq.liquid_level,
flow.instant_flow,
energy.input_power,
pressure.pressure,
wc.plum_comb,
wc.frequency,
flow.collection_time_str_2min
from
-- 流量
(
select
base.fac_name,
base.fac_code,
sum(flow.instant_flow) as instant_flow,
-- collection_time_dt,
collection_time_str_2min
from
beibei.dwd_40104_collect_flow_di flow
inner join base
on flow.device_code = base.dev_code and base.pt in ('103100100','103100300')
GROUP BY base.fac_name,base.fac_code,collection_time_str_2min
) flow
LEFT JOIN
-- 压力
(
select
base.fac_name,
base.fac_code,
max(pre.pressure) AS pressure,
-- collection_time_dt,
collection_time_str_2min
from
beibei.dwd_40104_collect_pressure_di pre
inner join base
on pre.device_code = base.dev_code and base.pt in ('103100100','103100300')
GROUP BY base.fac_name,base.fac_code,collection_time_str_2min
) pressure
ON flow.fac_code = pressure.fac_code and flow.collection_time_str_2min = pressure.collection_time_str_2min
--
LEFT JOIN
-- 电气
(
select
base.fac_name,
base.fac_code,
sum(
case
when base.pt = '103100200' then (case when energy.phase_ab_voltage < 300 then energy.phase_ab_voltage*1000 else energy.phase_ab_voltage end)*energy.phase_a_current*1.732*0.95/1000
when base.pt = '103100400' then 380*energy.phase_a_current*1.732*0.95/1000
else NULL
END
) AS input_power,
-- energy.phase_a_voltage,
-- energy.phase_b_voltage,
-- energy.phase_c_voltage,
-- energy.phase_a_current,
-- energy.phase_b_current,
-- energy.phase_c_current,
-- energy.phase_ab_voltage,
-- energy.phase_bc_voltage,
-- energy.phase_ac_voltage,
-- energy.power_factor,
-- collection_time_dt,
collection_time_str_2min
from
(select device_code,collection_time_dt,collection_time_str_2min, avg(phase_a_current) as phase_a_current, avg(phase_ab_voltage) as phase_ab_voltage from beibei.dwd_40104_energy_consumption_di GROUP BY device_code,collection_time_dt,collection_time_str_2min) energy
inner join base
on energy.device_code = base.dev_code and base.pt in ('103100200','103100400')
and energy.device_code in (
'050301000020100041200',
-- '050101000020100041201',
'050301000020100041193',
-- '050101000020100041194', '050301000020100041187', -
-- '050101000020100041184',
'050301000020100041191',
-- '050101000020100041189',
'050302000020100041219',
-- -- '050101000020100041221',
-- '070201000020100041222',
'050302000020100041215',
-- '050101000020100041217',
-- '070201000020100041214',
'050302000020100041209'
-- '050101000020100041212',
-- '070201000020100041210'
)
GROUP BY base.fac_name,base.fac_code,collection_time_str_2min
) energy
ON flow.fac_code = energy.fac_code and flow.collection_time_str_2min = energy.collection_time_str_2min
LEFT JOIN
-- 启停状态
(
select
base.fac_name,
base.fac_code,
GROUP_CONCAT(
cast(
(
case
when wc.frequency !=0 and wc.frequency is not null then wc.frequency
else NULL
end
)
as char
)
order by base.dev_name
,
','
) as frequency,
GROUP_CONCAT(
case
when wc.startup_state = '02' then replace(base.dev_name,'#送水泵','#泵')
else null
end
order by base.dev_name
,
','
) as plum_comb,
-- wc.frequency,
-- wc.startup_state,
-- collection_time_dt,
collection_time_str_2min
from
(select collection_time_str_2min,device_code, avg(frequency) as frequency, max(startup_state) as startup_state from beibei.dwd_40104_working_condition_di wc GROUP BY collection_time_str_2min,device_code ) wc
inner join base
on wc.device_code = base.dev_code and base.pt in ('103100200','103100400')
GROUP BY base.fac_name,base.fac_code,collection_time_str_2min
) wc
ON flow.fac_code = wc.fac_code and flow.collection_time_str_2min = wc.collection_time_str_2min
left join
(
select
avg(liq.liquid_level) as liquid_level,
-- collection_time_dt,
collection_time_str_2min
from
beibei.dwd_40104_collect_liquid_di liq
inner join base
on
liq.device_code = base.dev_code
and base.pt = '103090000'
GROUP BY
collection_time_str_2min
order by
collection_time_str_2min
) liq
ON
flow.collection_time_str_2min = liq.collection_time_str_2min