hive - Presto 与大表（9 亿条记录）的完全连接比 Hive 慢得多
问题描述
我正在做 Hive 与 Presto 的基准性能测试。以下是条件和事实：
- 一个大表（ecu_data）和一个小临时表的完全连接。这两个表都是一个以 OSS 作为存储的 Hive 外部表。文件格式为 Parquet。
- 数据大小：ecu_data - 9 亿条记录，临时表 - 约 100 万条记录
- Presto 集群：3 个节点。每个节点的内存池：通用池 - 15GB，保留池 - 禁用，净空池 - 2GB
- 性能：蜂巢 - 148 分钟；Presto - 240 分钟
3 个 presto 集群的 CPU/内存利用率水平听起来很低。（cpu 17% 已使用）
ecu_data/temp 表的 Presto 全连接。
#!/bin/bash
echo "
INSERT INTO ${PST_HIVE_DEST_DB}.${PST_GWM_TOTAL_TBL}
SELECT CASE
WHEN tb1.vin IS NOT NULL THEN tb1.vin
WHEN tb2.vin IS NOT NULL THEN tb2.vin
ELSE NULL END AS vin,
CASE
WHEN tb1.ts IS NOT NULL THEN tb1.ts
WHEN tb2.ts IS NOT NULL THEN tb2.ts
ELSE NULL END AS ts,
CASE
WHEN tb1.oem IS NOT NULL THEN tb1.oem
WHEN tb2.oem IS NOT NULL THEN tb2.oem
ELSE NULL END AS oem,
CASE
WHEN tb1.cartype IS NOT NULL THEN tb1.cartype
WHEN tb2.cartype IS NOT NULL THEN tb2.cartype
ELSE NULL END AS cartype,
CASE
WHEN tb1.brand IS NOT NULL THEN tb1.brand
WHEN tb2.brand IS NOT NULL THEN tb2.brand
ELSE NULL END AS brand,
CASE
WHEN tb1.createtime IS NOT NULL THEN tb1.createtime
WHEN tb2.createtime IS NOT NULL THEN tb2.createtime
ELSE NULL END AS createtime,
CASE
WHEN tb1.rollingcounter IS NOT NULL THEN tb1.rollingcounter
WHEN tb2.rollingcounter IS NOT NULL THEN tb2.rollingcounter
ELSE NULL END AS rollingcounter,
c_abnorflam,
c_brkeopera,
tcuquery,
synchrosiglextc,
cvoflg,
cvorelease,
intkmanifpres,
intkmanifpresphyslimit,
airmass,
tolegrrate,
engoiltemp,
inneegrrate,
accepedalangle,
ignadvanangle,
innetoqtargval,
igntoqtargval,
relaircharge,
canispurgvolm,
throttvlvposi,
engspd,
engreverseflg,
engrohsig0,
engrohsig1,
engrohsig2,
engrohsig3,
uegolam,
resetapplienvit,
resetid,
resetval,
resetnum,
resetidlastim,
altudcorcfactor,
intkvvtposi,
intkvvttargposi,
exhautvvtposi,
exhautvvttargposi,
oilinjtpulwidth,
hegooxysensv,
railpres_ctrl,
CASE
WHEN tb1.engtemp IS NOT NULL THEN tb1.engtemp
WHEN tb2.engtemp IS NOT NULL THEN tb2.engtemp
ELSE NULL END AS engtemp,
gpfcarbload,
flgdrvbehavr,
tolnum_shrpacc,
avgacc_shrpacc,
avgv_shrpacc,
avgengspd_shrpacc,
avgairchrg_shrpacc,
time_shrpacc,
tolnum_shrpdec,
avgdec_shrpdec,
avgv_shrpdec,
avgengspd_shrpdec,
avgairchrg_shrpdec,
time_shrpdec,
tolnum_shrptrn,
avgtrn_shrptrn,
avgv_shrptrn,
time_shrptrn,
drvmode,
engineworkconareanum1,
oilcorrectfactornum,
oilcorrectfactorreliability,
flg_shrpacc,
flg_shrpdec,
flg_shrptrn,
flg_drvhabt,
CASE
WHEN tb1.engmodeltemp IS NOT NULL THEN tb1.engmodeltemp
WHEN tb2.engmodeltemp IS NOT NULL THEN tb2.engmodeltemp
ELSE NULL END AS engmodeltemp,
CASE
WHEN tb1.engmodelreftemp IS NOT NULL THEN tb1.engmodelreftemp
WHEN tb2.engmodelreftemp IS NOT NULL THEN tb2.engmodelreftemp
ELSE NULL END AS engmodelreftemp,
normaldrivingpetrolwear,
intensedrivingpetrolwear,
airconditionerpetrolwear,
idlepetrolwear,
cycletotaloilwear,
pasttotaloilwear10sec,
remainingoillevel,
dtcmalfunctioninfo,
ccf,
CASE
WHEN tb1.regedemandmax IS NOT NULL THEN tb1.regedemandmax
WHEN tb2.regedemandmax IS NOT NULL THEN tb2.regedemandmax
ELSE NULL END AS regedemandmax,
engineworkconareanum2,
oiltemp_coolwatertemp_areanum,
CASE
WHEN tb1.regedemandmaxid IS NOT NULL THEN tb1.regedemandmaxid
WHEN tb2.regedemandmaxid IS NOT NULL THEN tb2.regedemandmaxid
ELSE NULL END AS regedemandmaxid,
gpfstate,
CASE
WHEN tb1.ambienttemp IS NOT NULL THEN tb1.ambienttemp
WHEN tb2.ambienttemp IS NOT NULL THEN tb2.ambienttemp
ELSE NULL END AS ambienttemp,
CASE
WHEN tb1.ashinflu IS NOT NULL THEN tb1.ashinflu
WHEN tb2.ashinflu IS NOT NULL THEN tb2.ashinflu
ELSE NULL END AS ashinflu,
gpftemp,
oxymass,
gpfcarbloadmeas,
CASE
WHEN tb1.gpfcarbloadmodel IS NOT NULL THEN tb1.gpfcarbloadmodel
WHEN tb2.gpfcarbloadmodel IS NOT NULL THEN tb2.gpfcarbloadmodel
ELSE NULL END AS gpfcarbloadmodel,
CASE
WHEN tb1.drivingmileage IS NOT NULL THEN tb1.drivingmileage
WHEN tb2.drivingmileage IS NOT NULL THEN tb2.drivingmileage
ELSE NULL END AS drivingmileage,
dsmready1,
lamcontlfactor1,
timnormstate,
lamcontlfactor2,
distacmilactvat,
locatoxysens,
distacdiagcodclear,
obdrequrt,
dsmready2,
numwamup,
coolwatertempdst_snsr,
engspd_raw,
intkairtemp_snsr,
airmass_snsr,
absotdveposi1,
evapvaporpres,
oxygsenslsu,
oxygsensorv,
vesysv,
accepedalposid,
relfuellvl,
accepedalposie,
secoxysensfueltrim,
reldveposi,
coolwatertempdst,
coolwatertempust,
lamcontlfactor3,
logtrmfueltrim,
absotdveposi2,
manifpres,
railpres_raw,
desirddveposi,
commddtycyclfuelprgcontl,
ambientpres,
coolwatertempust2,
airfuelratio,
catalsttemp,
relairmass,
relaircharge_absladval,
airfuelcommdratio,
ingangle_cyl1,
shrtrmfueltrim,
carspd,
posstatus,
lat,
lon,
londir,
speedkn,
tracktrue,
magvar,
modeind,
tirprsfl,
tirtempfl,
tirprsfr,
tirtempfr,
tirprsrl,
tirtemprl,
tirprsrr,
tirtemprr,
iuprnumavltr,
iuprnumasnsrmnt,
caucoderltim,
caucodetwc,
iuprnumadiagtwc,
iuprdenodiagtwc,
iuprnumadyndiaglsu,
iuprnumapladiaglsu,
caucodedyndiaglsu,
iuprnumarltim,
iuprnumavltl,
caucodepladiaglsu,
iuprnumadtesk1,
iuprdenodtesk1,
caucodevltl,
iuprnumarohdtesk,
iuprdenorohdtesk,
caucodevltr,
caucodesnsrmnt,
caucodedtesk1,
caucoderohdtesk,
genldeno,
battvolt,
tolengstrtnum,
tolengstrtsuccnum,
drivingmileagecycl,
tolruntim,
runtimidl,
dvefd,
runtimaccelow,
runtimaccehigh,
faltlvlthrottzeroslv,
runtimdecehigh,
runtimdecelow,
airmasslv,
fairmasslv,
faltlvlthrottairmasslv,
throttvsens1zeroslv,
throttvsens1fzeroslv,
throttvsens2zeroslv,
throttvsens2fzeroslv,
faltlvlmaxhdr,
summassflw,
sumpistnblow,
faltlvlminhdr,
sumoilcomsumtcrkcs,
falllvldskv,
frmnumlamclose,
faltlvlad,
railpres_fild,
railpres_rel,
faltlvlinptlogic,
faltlvlsyn,
faltnumpedalposd,
faltnumpedalpose,
oxysensfd,
faltlvlclmpswth,
faltlvlidlspdmin,
faltlvlidlspdmax,
faltlvlidlspdnpl,
t_engstrt,
t_engstal,
c_suprknk,
c_hspsecinjectiondyn,
c_preigndetct,
engspdgradt1cycl,
filtgradtmanifpres,
preignratio,
knkratio,
ignangle,
relaircharge_cyl,
\"1stpreignnum\",
\"2ndpreignnum\",
\"3rdpreignnum\",
\"4thpreignnum\",
intkairtemp,
reloilpres,
thermostatdiagflg,
thermostatdiagena,
drivingcyclnum_hot,
airmass1,
engmodelreftemp_end,
drivingcyclnum_cold,
stkchecksen1high,
stkchecksen1low,
stkchecksen2high,
stkchecksen2low,
toltempsensmeanval,
tfa1coldstrt,
tfa2coldstrt,
cendiagstat,
intkairtempsensval1,
intkairtempsensval2,
airmassfilt,
engstptim,
CASE
WHEN tb1.year IS NOT NULL THEN tb1.year
WHEN tb2.year IS NOT NULL THEN tb2.year
ELSE NULL END AS year,
CASE
WHEN tb1.month IS NOT NULL THEN tb1.month
WHEN tb2.month IS NOT NULL THEN tb2.month
ELSE NULL END AS month,
CASE
WHEN tb1.day IS NOT NULL THEN tb1.day
WHEN tb2.day IS NOT NULL THEN tb2.day
ELSE NULL END AS day
FROM
(SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_ECU_DATA} WHERE createtime < to_iso8601(current_date)) AS tb1
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_DEST_DB}.${PST_GWM_TOTAL_TBL_TEMP} WHERE createtime < to_iso8601(current_date)) AS tb2 ON (tb1.vin = tb2.vin AND tb1.ts = tb2.ts)
;
"
ecu_data 表 DDL：
CREATE EXTERNAL TABLE `test_gwm_ecu_data`(
`vin` string COMMENT '?????',
`ts` string COMMENT '???,???yyyy-MM-dd HH:mm:ss.SSS',
`oem` string COMMENT '???',
`cartype` string COMMENT '??',
`brand` string COMMENT '??',
`rollingcounter` string COMMENT '???',
`createtime` string COMMENT '????',
`engineworkconareanum1` string COMMENT '?????????1',
`oilcorrectfactornum` string COMMENT '??????????',
`oilcorrectfactorreliability` string COMMENT '?????????',
`engtemp` string COMMENT '????',
`flg_shrpacc` string COMMENT '?????????',
`flg_shrpdec` string COMMENT '?????????',
`flg_shrptrn` string COMMENT '?????????',
`flg_drvhabt` string COMMENT '??????????',
`engmodeltemp` string COMMENT '???????????????',
`engmodelreftemp` string COMMENT '????????????????????',
`normaldrivingpetrolwear` string COMMENT '?????????',
`intensedrivingpetrolwear` string COMMENT '?????????',
`airconditionerpetrolwear` string COMMENT '???????',
`idlepetrolwear` string COMMENT '???????',
`cycletotaloilwear` string COMMENT '???????',
`pasttotaloilwear10sec` string COMMENT '10????',
`remainingoillevel` string COMMENT '????',
`dtcmalfunctioninfo` string COMMENT 'DTC?????',
`ccf` string COMMENT 'CCF?',
`regedemandmax` string COMMENT '????',
`engineworkconareanum2` string COMMENT '?????????2',
`oiltemp_coolwatertemp_areanum` string COMMENT '????-??????',
`regedemandmaxid` string COMMENT '????',
`gpfstate` string COMMENT 'GPF????',
`ambienttemp` string COMMENT '????',
`ashinflu` string COMMENT '????',
`gpftemp` string COMMENT 'GPF?????',
`oxymass` string COMMENT '???',
`gpfcarbloadmeas` string COMMENT 'GPF??????',
`gpfcarbloadmodel` string COMMENT 'GPF??????',
`drivingmileage` string COMMENT '????????')
COMMENT ' GWM ecu?????'
PARTITIONED BY (
`year` int,
`month` int,
`day` int)
STORED AS PARQUET
LOCATION
'ks3://knocking-test/perf-test/test_gwm_ecu_data';
临时表 DDL
#!/bin/bash
echo "
INSERT INTO ${PST_HIVE_DEST_DB}.${PST_GWM_TOTAL_TBL_TEMP}
SELECT CASE
WHEN tb1.vin IS NOT NULL THEN tb1.vin
WHEN tb2.vin IS NOT NULL THEN tb2.vin
WHEN tb3.vin IS NOT NULL THEN tb3.vin
WHEN tb4.vin IS NOT NULL THEN tb4.vin
WHEN tb5.vin IS NOT NULL THEN tb5.vin
WHEN tb6.vin IS NOT NULL THEN tb6.vin
WHEN tb7.vin IS NOT NULL THEN tb7.vin
WHEN tb8.vin IS NOT NULL THEN tb8.vin
ELSE NULL END AS vin,
CASE
WHEN tb1.ts IS NOT NULL THEN tb1.ts
WHEN tb2.ts IS NOT NULL THEN tb2.ts
WHEN tb3.ts IS NOT NULL THEN tb3.ts
WHEN tb4.ts IS NOT NULL THEN tb4.ts
WHEN tb5.ts IS NOT NULL THEN tb5.ts
WHEN tb6.ts IS NOT NULL THEN tb6.ts
WHEN tb7.ts IS NOT NULL THEN tb7.ts
WHEN tb8.ts IS NOT NULL THEN tb8.ts
ELSE NULL END AS ts,
CASE
WHEN tb1.oem IS NOT NULL THEN tb1.oem
WHEN tb2.oem IS NOT NULL THEN tb2.oem
WHEN tb3.oem IS NOT NULL THEN tb3.oem
WHEN tb4.oem IS NOT NULL THEN tb4.oem
WHEN tb5.oem IS NOT NULL THEN tb5.oem
WHEN tb6.oem IS NOT NULL THEN tb6.oem
WHEN tb7.oem IS NOT NULL THEN tb7.oem
WHEN tb8.oem IS NOT NULL THEN tb8.oem
ELSE NULL END AS oem,
CASE
WHEN tb1.cartype IS NOT NULL THEN tb1.cartype
WHEN tb2.cartype IS NOT NULL THEN tb2.cartype
WHEN tb3.cartype IS NOT NULL THEN tb3.cartype
WHEN tb4.cartype IS NOT NULL THEN tb4.cartype
WHEN tb5.cartype IS NOT NULL THEN tb5.cartype
WHEN tb6.cartype IS NOT NULL THEN tb6.cartype
WHEN tb7.cartype IS NOT NULL THEN tb7.cartype
WHEN tb8.cartype IS NOT NULL THEN tb8.cartype
ELSE NULL END AS cartype,
CASE
WHEN tb1.brand IS NOT NULL THEN tb1.brand
WHEN tb2.brand IS NOT NULL THEN tb2.brand
WHEN tb3.brand IS NOT NULL THEN tb3.brand
WHEN tb4.brand IS NOT NULL THEN tb4.brand
WHEN tb5.brand IS NOT NULL THEN tb5.brand
WHEN tb6.brand IS NOT NULL THEN tb6.brand
WHEN tb7.brand IS NOT NULL THEN tb7.brand
WHEN tb8.brand IS NOT NULL THEN tb8.brand
ELSE NULL END AS brand,
CASE
WHEN tb1.createtime IS NOT NULL THEN tb1.createtime
WHEN tb2.createtime IS NOT NULL THEN tb2.createtime
WHEN tb3.createtime IS NOT NULL THEN tb3.createtime
WHEN tb4.createtime IS NOT NULL THEN tb4.createtime
WHEN tb5.createtime IS NOT NULL THEN tb5.createtime
WHEN tb6.createtime IS NOT NULL THEN tb6.createtime
WHEN tb7.createtime IS NOT NULL THEN tb7.createtime
WHEN tb8.createtime IS NOT NULL THEN tb8.createtime
ELSE NULL END AS createtime,
CASE
WHEN tb2.rollingcounter IS NOT NULL THEN tb2.rollingcounter
WHEN tb3.rollingcounter IS NOT NULL THEN tb3.rollingcounter
WHEN tb4.rollingcounter IS NOT NULL THEN tb4.rollingcounter
WHEN tb5.rollingcounter IS NOT NULL THEN tb5.rollingcounter
WHEN tb6.rollingcounter IS NOT NULL THEN tb6.rollingcounter
WHEN tb7.rollingcounter IS NOT NULL THEN tb7.rollingcounter
WHEN tb8.rollingcounter IS NOT NULL THEN tb8.rollingcounter
ELSE NULL END AS rollingcounter,
c_abnorflam,
c_brkeopera,
tcuquery,
synchrosiglextc,
cvoflg,
cvorelease,
intkmanifpres,
intkmanifpresphyslimit,
airmass,
tolegrrate,
engoiltemp,
inneegrrate,
accepedalangle,
ignadvanangle,
innetoqtargval,
igntoqtargval,
relaircharge,
canispurgvolm,
throttvlvposi,
CASE
WHEN tb3.engspd IS NOT NULL THEN tb3.engspd
WHEN tb7.engspd IS NOT NULL THEN tb7.engspd
ELSE NULL END AS engspd,
engreverseflg,
engrohsig0,
engrohsig1,
engrohsig2,
engrohsig3,
uegolam,
resetapplienvit,
resetid,
resetval,
resetnum,
resetidlastim,
CASE
WHEN tb3.altudcorcfactor IS NOT NULL THEN tb3.altudcorcfactor
WHEN tb6.altudcorcfactor IS NOT NULL THEN tb6.altudcorcfactor
WHEN tb7.altudcorcfactor IS NOT NULL THEN tb7.altudcorcfactor
ELSE NULL END AS altudcorcfactor,
intkvvtposi,
intkvvttargposi,
exhautvvtposi,
exhautvvttargposi,
oilinjtpulwidth,
hegooxysensv,
railpres_ctrl,
CASE
WHEN tb3.engtemp IS NOT NULL THEN tb3.engtemp
WHEN tb7.engtemp IS NOT NULL THEN tb7.engtemp
WHEN tb8.engtemp IS NOT NULL THEN tb8.engtemp
ELSE NULL END AS engtemp,
CASE
WHEN tb3.gpfcarbload IS NOT NULL THEN tb3.gpfcarbload
WHEN tb6.gpfcarbload IS NOT NULL THEN tb6.gpfcarbload
ELSE NULL END AS gpfcarbload,
flgdrvbehavr,
tolnum_shrpacc,
avgacc_shrpacc,
avgv_shrpacc,
avgengspd_shrpacc,
avgairchrg_shrpacc,
time_shrpacc,
tolnum_shrpdec,
avgdec_shrpdec,
avgv_shrpdec,
avgengspd_shrpdec,
avgairchrg_shrpdec,
time_shrpdec,
tolnum_shrptrn,
avgtrn_shrptrn,
avgv_shrptrn,
time_shrptrn,
drvmode,
CASE
WHEN tb8.engmodeltemp IS NOT NULL THEN tb8.engmodeltemp
ELSE NULL END AS engmodeltemp,
CASE
WHEN tb8.engmodelreftemp IS NOT NULL THEN tb8.engmodelreftemp
ELSE NULL END AS engmodelreftemp,
CASE
WHEN tb6.regedemandmax IS NOT NULL THEN tb6.regedemandmax
ELSE NULL END AS regedemandmax,
CASE
WHEN tb6.regedemandmaxid IS NOT NULL THEN tb6.regedemandmaxid
ELSE NULL END AS regedemandmaxid,
CASE
WHEN tb4.ambienttemp IS NOT NULL THEN tb4.ambienttemp
WHEN tb6.ambienttemp IS NOT NULL THEN tb6.ambienttemp
WHEN tb7.ambienttemp IS NOT NULL THEN tb7.ambienttemp
WHEN tb8.ambienttemp IS NOT NULL THEN tb8.ambienttemp
ELSE NULL END AS ambienttemp,
CASE
WHEN tb6.ashinflu IS NOT NULL THEN tb6.ashinflu
ELSE NULL END AS ashinflu,
CASE
WHEN tb6.gpfcarbloadmodel IS NOT NULL THEN tb6.gpfcarbloadmodel
ELSE NULL END AS gpfcarbloadmodel,
CASE
WHEN tb4.drivingmileage IS NOT NULL THEN tb4.drivingmileage
WHEN tb6.drivingmileage IS NOT NULL THEN tb6.drivingmileage
WHEN tb7.drivingmileage IS NOT NULL THEN tb7.drivingmileage
ELSE NULL END AS drivingmileage,
dsmready1,
lamcontlfactor1,
timnormstate,
lamcontlfactor2,
distacmilactvat,
locatoxysens,
distacdiagcodclear,
obdrequrt,
dsmready2,
numwamup,
coolwatertempdst_snsr,
engspd_raw,
intkairtemp_snsr,
airmass_snsr,
absotdveposi1,
evapvaporpres,
oxygsenslsu,
oxygsensorv,
vesysv,
accepedalposid,
relfuellvl,
accepedalposie,
secoxysensfueltrim,
reldveposi,
coolwatertempdst,
CASE
WHEN tb4.coolwatertempust IS NOT NULL THEN tb4.coolwatertempust
WHEN tb8.coolwatertempust IS NOT NULL THEN tb8.coolwatertempust
ELSE NULL END AS coolwatertempust,
lamcontlfactor3,
logtrmfueltrim,
absotdveposi2,
manifpres,
railpres_raw,
desirddveposi,
commddtycyclfuelprgcontl,
ambientpres,
coolwatertempust2,
airfuelratio,
catalsttemp,
relairmass,
relaircharge_absladval,
airfuelcommdratio,
ingangle_cyl1,
shrtrmfueltrim,
CASE
WHEN tb4.carspd IS NOT NULL THEN tb4.carspd
WHEN tb7.carspd IS NOT NULL THEN tb7.carspd
WHEN tb8.carspd IS NOT NULL THEN tb8.carspd
ELSE NULL END AS carspd,
posstatus,
lat,
lon,
londir,
speedkn,
tracktrue,
magvar,
modeind,
tirprsfl,
tirtempfl,
tirprsfr,
tirtempfr,
tirprsrl,
tirtemprl,
tirprsrr,
tirtemprr,
iuprnumavltr,
iuprnumasnsrmnt,
caucoderltim,
caucodetwc,
iuprnumadiagtwc,
iuprdenodiagtwc,
iuprnumadyndiaglsu,
iuprnumapladiaglsu,
caucodedyndiaglsu,
iuprnumarltim,
iuprnumavltl,
caucodepladiaglsu,
iuprnumadtesk1,
iuprdenodtesk1,
caucodevltl,
iuprnumarohdtesk,
iuprdenorohdtesk,
caucodevltr,
caucodesnsrmnt,
caucodedtesk1,
caucoderohdtesk,
genldeno,
battvolt,
tolengstrtnum,
tolengstrtsuccnum,
drivingmileagecycl,
tolruntim,
runtimidl,
dvefd,
runtimaccelow,
runtimaccehigh,
faltlvlthrottzeroslv,
runtimdecehigh,
runtimdecelow,
airmasslv,
fairmasslv,
faltlvlthrottairmasslv,
throttvsens1zeroslv,
throttvsens1fzeroslv,
throttvsens2zeroslv,
throttvsens2fzeroslv,
faltlvlmaxhdr,
summassflw,
sumpistnblow,
faltlvlminhdr,
sumoilcomsumtcrkcs,
falllvldskv,
frmnumlamclose,
faltlvlad,
railpres_fild,
railpres_rel,
faltlvlinptlogic,
faltlvlsyn,
faltnumpedalposd,
faltnumpedalpose,
oxysensfd,
faltlvlclmpswth,
faltlvlidlspdmin,
faltlvlidlspdmax,
faltlvlidlspdnpl,
t_engstrt,
t_engstal,
c_suprknk,
c_hspsecinjectiondyn,
c_preigndetct,
engspdgradt1cycl,
filtgradtmanifpres,
preignratio,
knkratio,
ignangle,
relaircharge_cyl,
\"1stpreignnum\",
\"2ndpreignnum\",
\"3rdpreignnum\",
\"4thpreignnum\",
intkairtemp,
reloilpres,
thermostatdiagflg,
thermostatdiagena,
drivingcyclnum_hot,
airmass1,
engmodelreftemp_end,
drivingcyclnum_cold,
stkchecksen1high,
stkchecksen1low,
stkchecksen2high,
stkchecksen2low,
toltempsensmeanval,
tfa1coldstrt,
tfa2coldstrt,
cendiagstat,
intkairtempsensval1,
intkairtempsensval2,
airmassfilt,
engstptim,
CASE
WHEN tb1.year IS NOT NULL THEN tb1.year
WHEN tb2.year IS NOT NULL THEN tb2.year
WHEN tb3.year IS NOT NULL THEN tb3.year
WHEN tb4.year IS NOT NULL THEN tb4.year
WHEN tb5.year IS NOT NULL THEN tb5.year
WHEN tb6.year IS NOT NULL THEN tb6.year
WHEN tb7.year IS NOT NULL THEN tb7.year
WHEN tb8.year IS NOT NULL THEN tb8.year
ELSE NULL END AS year,
CASE
WHEN tb1.month IS NOT NULL THEN tb1.month
WHEN tb2.month IS NOT NULL THEN tb2.month
WHEN tb3.month IS NOT NULL THEN tb3.month
WHEN tb4.month IS NOT NULL THEN tb4.month
WHEN tb5.month IS NOT NULL THEN tb5.month
WHEN tb6.month IS NOT NULL THEN tb6.month
WHEN tb7.month IS NOT NULL THEN tb7.month
WHEN tb8.month IS NOT NULL THEN tb8.month
ELSE NULL END AS month,
CASE
WHEN tb1.day IS NOT NULL THEN tb1.day
WHEN tb2.day IS NOT NULL THEN tb2.day
WHEN tb3.day IS NOT NULL THEN tb3.day
WHEN tb4.day IS NOT NULL THEN tb4.day
WHEN tb5.day IS NOT NULL THEN tb5.day
WHEN tb6.day IS NOT NULL THEN tb6.day
WHEN tb7.day IS NOT NULL THEN tb7.day
WHEN tb8.day IS NOT NULL THEN tb8.day
ELSE NULL END AS day
FROM
(SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_GPS} WHERE createtime < to_iso8601(current_date)) AS tb1
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_DRVHABT} WHERE createtime < to_iso8601(current_date)) AS tb2 ON (tb1.vin = tb2.vin AND tb1.ts = tb2.ts)
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_ABNORMALFLAMEOUT} WHERE createtime < to_iso8601(current_date)) AS tb3 ON (tb1.vin = tb3.vin AND tb1.ts = tb3.ts)
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_FAULTOUTPUT} WHERE createtime < to_iso8601(current_date)) AS tb4 ON (tb1.vin = tb4.vin AND tb1.ts = tb4.ts)
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_PERIOD30S} WHERE createtime < to_iso8601(current_date)) AS tb5 ON (tb1.vin = tb5.vin AND tb1.ts = tb5.ts)
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_POWERON} WHERE createtime < to_iso8601(current_date)) AS tb6 ON (tb1.vin = tb6.vin AND tb1.ts = tb6.ts)
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_SUPERKNOCK} WHERE createtime < to_iso8601(current_date)) AS tb7 ON (tb1.vin = tb7.vin AND tb1.ts = tb7.ts)
FULL OUTER JOIN (SELECT * FROM ${PST_HIVE_SRC_DB}.${PST_HIVE_SRC_WTDIAGNOSISMODEL} WHERE createtime < to_iso8601(current_date)) AS tb8 ON (tb1.vin = tb8.vin AND tb1.ts = tb8.ts)
;
"
解决方案
推荐阅读
- c++ - 如何组织 Velodyne HL-32 的无组织点云
- python - 在 python 虚拟环境中访问包
- mapbox - 地图框 GL JS。缩放到高度
- chart.js - 只是在chart.js中显示每小时作为时间滴答
- flutter - 如何使用 `form.validate()` 调用特定的验证器而不是同时调用所有的验证器？
- python - 在 Django 中一次查找最大项目的数量
- facebook - 如何检查指向用户/页面内容的 Facebook 链接是否仍然有效？
- button - 将选定的条形图（vizframe 条形图）连接到按钮
- csv - 如何定义 Raku 语法来解析 TSV 文本？
- python - 使用字典来键入一系列值