为了更快的定位您的问题，请提供以下信息，谢谢
【详述】数据库BE节点晚上异常退出
【背景】数据导入过程中
【业务影响】数据库服务不可用，业务无法正常使用。
【是否存算分离】
【StarRocks版本】例如：3.1.4
【集群规模】例如：1fe（1 follower）+1be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/128G/万兆
【联系方式】StarRocks社区群1 （Techsun-tao）
【附件】
2024-03-23 00:00:55,413 [query] |Client=10.4.108.30:49750|User=root|AuthorizedUser=‘root’@’%’|ResourceGroup=default_wg|Catalog=default_catalog|Db=YODA_YMS|State=ERR|ErrorCode=|Time=20763|ScanBytes=0|ScanRows=0|ReturnRows=0|StmtId=5174299|QueryId=512fc531-e865-11ee-87ae-0242f4f70e87|IsQuery=false|feIp=10.4.108.30|Stmt=INSERT INTO PRODUCT_BIN_DEFINITION (product_id, bin_no, bin_type, load_time, test_program, start_time, end_time, definition, pass_fail_flag) with pbd as ( SELECT product_id, bin_no, bin_type, test_program, start_time, definition, pass_fail_flag FROM ODS.PRODUCT_BIN_DEFINITION where ‘CP_1711123208’ is not null and load_time = ‘2024-03-23 00:00:34’ QUALIFY ROW_NUMBER() OVER(PARTITION BY product_id, bin_no, bin_type,test_program,definition ORDER BY start_time DESC) = 1 ), pbd_row as ( SELECT product_id, bin_no, bin_type, test_program, start_time, definition, pass_fail_flag,concat_ws("", definition, pass_fail_flag) as value_s FROM pbd), db_pbd as ( SELECT product_id, bin_no, bin_type, load_time, test_program, start_time, end_time, definition, pass_fail_flag,concat_ws("", definition, pass_fail_flag) as value_s FROM PRODUCT_BIN_DEFINITION where (product_id, bin_no, bin_type,test_program,definition) in (SELECT DISTINCT product_id, bin_no, bin_type,test_program,definition from pbd_row) ), min_db_pbd as ( SELECT product_id, bin_no, bin_type,test_program, start_time min_start_time, definition from db_pbd QUALIFY ROW_NUMBER() OVER(PARTITION BY product_id, bin_no, bin_type,test_program,definition ORDER BY start_time ASC) = 1 ), not_exist_pbd as ( SELECT product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time, “9999-12-31 23:59:59” as end_time, definition, pass_fail_flag from pbd_row where (product_id, bin_no, bin_type,test_program,definition) not in (SELECT DISTINCT product_id, bin_no, bin_type,test_program,definition from db_pbd) ), exist_pbd as ( SELECT pbd_row.product_id, pbd_row.bin_no, pbd_row.bin_type, pbd_row.test_program, pbd_row.start_time, pbd_row.definition, pbd_row.pass_fail_flag, pbd_row.value_s,load_time,db_pbd.start_time as start_time_db,db_pbd.end_time as end_time_db,db_pbd.definition as definition_db , db_pbd.value_s as value_s_db ,db_pbd.pass_fail_flag as pass_fail_flag_db,min_start_time from pbd_row,db_pbd,min_db_pbd where pbd_row.product_id = db_pbd.product_id and pbd_row.bin_no = db_pbd.bin_no and pbd_row.bin_type = db_pbd.bin_type and pbd_row.test_program = db_pbd.test_program and pbd_row.definition = db_pbd.definition and pbd_row.product_id = min_db_pbd.product_id and pbd_row.bin_no = min_db_pbd.bin_no and pbd_row.bin_type = min_db_pbd.bin_type and pbd_row.test_program = min_db_pbd.test_program and pbd_row.definition = min_db_pbd.definition) select product_id, bin_no, bin_type, load_time, test_program, start_time,end_time_db as end_time, definition, pass_fail_flag from exist_pbd where value_s = value_s_db and min_start_time > start_time and min_start_time = start_time_db UNION select product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time,start_time_db as end_time, definition, pass_fail_flag from exist_pbd where value_s <> value_s_db and min_start_time > start_time and min_start_time = start_time_db UNION select product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time,end_time_db as end_time, definition, pass_fail_flag from exist_pbd where value_s <> value_s_db and start_time_db > start_time and end_time_db < start_time UNION select product_id, bin_no, bin_type, load_time, test_program, start_time_db as start_time,start_time as end_time, definition_db as definition, pass_fail_flag_db as pass_fail_flag from exist_pbd where value_s <> value_s_db and start_time_db > start_time and end_time_db < start_time UNION select * from not_exist_pbd|Digest=|PlanCpuCost=2.2695719203461432E9|PlanMemCost=5.058600613476569E8
be.WARNING
W0322 00:00:08.760958 119004 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=074c46855055a871-16915984c6cdefad, job_id=-1, txn_id: 4536945, label=5a7e3840-11c2-42ea-836f-cacd0abbe0ca, db=ODS
W0322 00:00:10.005353 119002 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=024380ec3b94fece-e33ac37ba2c325bc, job_id=-1, txn_id: 4536958, label=d2395243-acbf-410f-917b-2acff663d9fb, db=ODS
W0322 00:11:55.507788 118987 stream_load.cpp:149] Fail to handle streaming load, id=384aa195bfbe9833-ab4aecc608e0dba8 errmsg=too many filtered rows
W0322 00:13:18.920982 118987 stream_load.cpp:149] Fail to handle streaming load, id=07485cd0f2a8642c-b055f037658df0a5 errmsg=too many filtered rows
W0322 00:15:33.541448 118987 stream_load.cpp:149] Fail to handle streaming load, id=6c4d4d82456c04a2-e3708842773164ae errmsg=too many filtered rows
W0322 00:18:20.780578 118987 stream_load.cpp:149] Fail to handle streaming load, id=0b4c0718ceb4a24c-254ab1be3f9b30a8 errmsg=too many filtered rows
W0322 00:19:53.146229 118987 stream_load.cpp:149] Fail to handle streaming load, id=b5451fa039cf7df4-68a47b9fe80e1eba errmsg=too many filtered rows
W0322 00:24:31.867604 118987 stream_load.cpp:149] Fail to handle streaming load, id=ff4b399dfae046b5-fcf0b5944e4d38bf errmsg=too many filtered rows
W0322 00:25:39.511461 118987 stream_load.cpp:149] Fail to handle streaming load, id=bb43509a213c4bba-3b2ece2784385591 errmsg=too many filtered rows
W0322 00:26:38.783229 118987 stream_load.cpp:149] Fail to handle streaming load, id=3945d1d264972cf3-7cb7be420c10ba80 errmsg=too many filtered rows
W0322 00:31:27.866647 118987 stream_load.cpp:149] Fail to handle streaming load, id=a4469f8e1bd3fd7d-11ccd86a81bda79f errmsg=too many filtered rows
W0322 03:40:05.956961 118995 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=814b868e3e3f0443-092c6e6608639482, job_id=-1, txn_id: 4645167, label=b80ab619-aa47-4905-9fe6-884db4608246, db=ODS
W0322 03:40:07.032989 118979 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=1b43aa6b759a10db-bf24af1650e4808a, job_id=-1, txn_id: 4645182, label=a8f89b7c-fbb4-4d38-92a8-2aba7cfc6c5d, db=ODS
W0322 03:55:08.995254 118986 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=a741f7b97d56633f-26f324a5b28850a9, job_id=-1, txn_id: 4651434, label=72797d17-0e2c-4626-9bba-07964abba5b4, db=ODS
W0322 03:55:10.662103 119008 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=5e445923904a163f-e819f2f67da2839e, job_id=-1, txn_id: 4651450, label=85fc8b41-48ec-4bed-ad9e-e1301012e401, db=ODS
W0322 05:55:04.779232 118997 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=224acfec9049c9d9-b8a498f632c1ef86, job_id=-1, txn_id: 4671533, label=e6864475-bd36-465a-80cf-11b63b43d9b1, db=ODS
W0322 05:55:06.028647 118989 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=3642b185bbf6a0d0-369737e26808b2bf, job_id=-1, txn_id: 4671542, label=fc1d4d96-b2e5-42fe-867d-c08b113c1271, db=ODS
W0322 06:00:07.525797 118987 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=824e347263f3168d-5f347aa75e63a181, job_id=-1, txn_id: 4671956, label=a6a1a315-a90e-4c06-9c7f-f9d404c655ef, db=ODS
W0322 06:00:08.703712 118987 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=e649836a80eea84a-08b44dac70e0099a, job_id=-1, txn_id: 4671965, label=8c4ea0cf-7d1c-43b6-9f1e-3441981e4c92, db=ODS
W0322 06:05:04.974069 118992 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=b140c57c7b284c4a-3f45d05a3a775c85, job_id=-1, txn_id: 4672718, label=4b5fb5a8-643d-41a7-a25f-b48a29726e96, db=ODS
W0322 06:05:06.049769 118991 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=e3459f12ae7d3d57-07f6aeb2a71f0686, job_id=-1, txn_id: 4672727, label=30ec5bd3-cfbc-494f-a6c8-69e81291bf5a, db=ODS
W0322 06:15:07.801316 118993 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=5a4b145273676553-56dee75d0c340088, job_id=-1, txn_id: 4674367, label=329b7ed9-c8f6-4a8e-a648-cec46d01a192, db=ODS
W0322 06:15:08.983479 118993 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=ec4ec87f04ce2c6e-534014a5e3f5bda6, job_id=-1, txn_id: 4674376, label=e5496373-c9dd-4f25-89a9-9716ed5d1189, db=ODS
E0325 09:42:09.391243 53772 thrift_server.cpp:156] ThriftServer ‘heartbeat’ (on port: 9050) exited due to TException: Could not bind: Address already in use
E0325 09:42:09.392000 52525 thrift_server.cpp:142] ThriftServer ‘heartbeat’ (on port: 9050) did not start correctly
你好，请问在 be.INFO 和 be.WARNING 日志中，能搜索到“512fc531-e865-11ee-87ae-0242f4f70e87|”相关的内容吗？
23号 be.WARNING所有的日志
W0321 23:45:06.150869 118997 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=0d400eaf5668a416-0c7b6a811d0ee8b6, job_id=-1, txn_id: 4531382, label=7a3b3b3d-e74f-4e50-9dd4-5698ec427987, db=ODS
W0322 00:00:08.760958 119004 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=074c46855055a871-16915984c6cdefad, job_id=-1, txn_id: 4536945, label=5a7e3840-11c2-42ea-836f-cacd0abbe0ca, db=ODS
W0322 00:00:10.005353 119002 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=024380ec3b94fece-e33ac37ba2c325bc, job_id=-1, txn_id: 4536958, label=d2395243-acbf-410f-917b-2acff663d9fb, db=ODS
W0322 00:11:55.507788 118987 stream_load.cpp:149] Fail to handle streaming load, id=384aa195bfbe9833-ab4aecc608e0dba8 errmsg=too many filtered rows
W0322 00:13:18.920982 118987 stream_load.cpp:149] Fail to handle streaming load, id=07485cd0f2a8642c-b055f037658df0a5 errmsg=too many filtered rows
W0322 00:15:33.541448 118987 stream_load.cpp:149] Fail to handle streaming load, id=6c4d4d82456c04a2-e3708842773164ae errmsg=too many filtered rows
W0322 00:18:20.780578 118987 stream_load.cpp:149] Fail to handle streaming load, id=0b4c0718ceb4a24c-254ab1be3f9b30a8 errmsg=too many filtered rows
W0322 00:19:53.146229 118987 stream_load.cpp:149] Fail to handle streaming load, id=b5451fa039cf7df4-68a47b9fe80e1eba errmsg=too many filtered rows
W0322 00:24:31.867604 118987 stream_load.cpp:149] Fail to handle streaming load, id=ff4b399dfae046b5-fcf0b5944e4d38bf errmsg=too many filtered rows
W0322 00:25:39.511461 118987 stream_load.cpp:149] Fail to handle streaming load, id=bb43509a213c4bba-3b2ece2784385591 errmsg=too many filtered rows
W0322 00:26:38.783229 118987 stream_load.cpp:149] Fail to handle streaming load, id=3945d1d264972cf3-7cb7be420c10ba80 errmsg=too many filtered rows
W0322 00:31:27.866647 118987 stream_load.cpp:149] Fail to handle streaming load, id=a4469f8e1bd3fd7d-11ccd86a81bda79f errmsg=too many filtered rows
W0322 03:40:05.956961 118995 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=814b868e3e3f0443-092c6e6608639482, job_id=-1, txn_id: 4645167, label=b80ab619-aa47-4905-9fe6-884db4608246, db=ODS
W0322 03:40:07.032989 118979 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=1b43aa6b759a10db-bf24af1650e4808a, job_id=-1, txn_id: 4645182, label=a8f89b7c-fbb4-4d38-92a8-2aba7cfc6c5d, db=ODS
W0322 03:55:08.995254 118986 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=a741f7b97d56633f-26f324a5b28850a9, job_id=-1, txn_id: 4651434, label=72797d17-0e2c-4626-9bba-07964abba5b4, db=ODS
W0322 03:55:10.662103 119008 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=5e445923904a163f-e819f2f67da2839e, job_id=-1, txn_id: 4651450, label=85fc8b41-48ec-4bed-ad9e-e1301012e401, db=ODS
W0322 05:55:04.779232 118997 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=224acfec9049c9d9-b8a498f632c1ef86, job_id=-1, txn_id: 4671533, label=e6864475-bd36-465a-80cf-11b63b43d9b1, db=ODS
W0322 05:55:06.028647 118989 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=3642b185bbf6a0d0-369737e26808b2bf, job_id=-1, txn_id: 4671542, label=fc1d4d96-b2e5-42fe-867d-c08b113c1271, db=ODS
W0322 06:00:07.525797 118987 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=824e347263f3168d-5f347aa75e63a181, job_id=-1, txn_id: 4671956, label=a6a1a315-a90e-4c06-9c7f-f9d404c655ef, db=ODS
W0322 06:00:08.703712 118987 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=e649836a80eea84a-08b44dac70e0099a, job_id=-1, txn_id: 4671965, label=8c4ea0cf-7d1c-43b6-9f1e-3441981e4c92, db=ODS
W0322 06:05:04.974069 118992 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=b140c57c7b284c4a-3f45d05a3a775c85, job_id=-1, txn_id: 4672718, label=4b5fb5a8-643d-41a7-a25f-b48a29726e96, db=ODS
W0322 06:05:06.049769 118991 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=e3459f12ae7d3d57-07f6aeb2a71f0686, job_id=-1, txn_id: 4672727, label=30ec5bd3-cfbc-494f-a6c8-69e81291bf5a, db=ODS
W0322 06:15:07.801316 118993 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=5a4b145273676553-56dee75d0c340088, job_id=-1, txn_id: 4674367, label=329b7ed9-c8f6-4a8e-a648-cec46d01a192, db=ODS
W0322 06:15:08.983479 118993 stream_load.cpp:590] plan streaming load failed. errmsg=Column has no default value. column: x_origin_indexid=ec4ec87f04ce2c6e-534014a5e3f5bda6, job_id=-1, txn_id: 4674376, label=e5496373-c9dd-4f25-89a9-9716ed5d1189, db=ODS
E0325 09:42:09.391243 53772 thrift_server.cpp:156] ThriftServer ‘heartbeat’ (on port: 9050) exited due to TException: Could not bind: Address already in use
E0325 09:42:09.392000 52525 thrift_server.cpp:142] ThriftServer ‘heartbeat’ (on port: 9050) did not start correctly
E0325 09:42:09.392024 52525 starrocks_main.cpp:309] StarRocks BE HeartBeat Service did not start correctly. Error=Internal error: ThriftServer ‘heartbeat’ (on port: 9050) did not start correctly
/build/starrocks/be/src/util/thrift_server.cpp:389 event_processor->start_and_wait_for_server()
be.WARNING 日志中没有
好的。be.INFO 日志中有吗？
我找到变更点了（修改前）
INSERT INTO PRODUCT_BIN_DEFINITION
(product_id, bin_no, bin_type, load_time, test_program, start_time, end_time, definition, pass_fail_flag)
with pbd as (
SELECT product_id, bin_no, bin_type, test_program, start_time, definition, pass_fail_flag
FROM ODS.PRODUCT_BIN_DEFINITION
where :import_batch is not null
and load_time = :load_time
QUALIFY ROW_NUMBER() OVER(PARTITION BY product_id, bin_no, bin_type,definition ORDER BY start_time DESC) = 1
),
pbd_row as (
SELECT product_id, bin_no, bin_type, test_program, start_time, definition, pass_fail_flag,concat_ws("", definition, pass_fail_flag) as value_s
FROM pbd),
db_pbd as (
SELECT product_id, bin_no, bin_type, load_time, test_program, start_time, end_time, definition, pass_fail_flag,concat_ws("", definition, pass_fail_flag) as value_s
FROM PRODUCT_BIN_DEFINITION
where (product_id, bin_no, bin_type,definition) in
(SELECT DISTINCT product_id, bin_no, bin_type,definition from pbd_row)
),
min_db_pbd as (
SELECT product_id, bin_no, bin_type, start_time min_start_time, definition
from db_pbd
QUALIFY ROW_NUMBER() OVER(PARTITION BY product_id, bin_no, bin_type,definition ORDER BY start_time ASC) = 1
),
not_exist_pbd as (
SELECT product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time,
test_program, start_time, “9999-12-31 23:59:59” as end_time, definition, pass_fail_flag
from pbd_row
where (product_id, bin_no, bin_type,definition) not in
(SELECT DISTINCT product_id, bin_no, bin_type,definition from db_pbd)
),
exist_pbd as (
SELECT pbd_row.product_id, pbd_row.bin_no, pbd_row.bin_type, pbd_row.test_program, pbd_row.start_time, pbd_row.definition, pbd_row.pass_fail_flag,
pbd_row.value_s,load_time,db_pbd.start_time as start_time_db,db_pbd.end_time as end_time_db,db_pbd.definition as definition_db ,
db_pbd.value_s as value_s_db ,db_pbd.pass_fail_flag as pass_fail_flag_db,min_start_time
from pbd_row,db_pbd,min_db_pbd
where
pbd_row.product_id = db_pbd.product_id
and pbd_row.bin_no = db_pbd.bin_no
and pbd_row.bin_type = db_pbd.bin_type
and pbd_row.definition = db_pbd.definition
and pbd_row.product_id = min_db_pbd.product_id
and pbd_row.bin_no = min_db_pbd.bin_no
and pbd_row.bin_type = min_db_pbd.bin_type
and pbd_row.definition = min_db_pbd.definition)
select
product_id, bin_no, bin_type, load_time, test_program, start_time,end_time_db as end_time, definition, pass_fail_flag
from exist_pbd
where
value_s = value_s_db
and min_start_time > start_time
and min_start_time = start_time_db
UNION
select
product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time,start_time_db as end_time, definition, pass_fail_flag
from exist_pbd
where
value_s <> value_s_db
and min_start_time > start_time
and min_start_time = start_time_db
UNION
select
product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time,end_time_db as end_time, definition, pass_fail_flag
from exist_pbd
where
value_s <> value_s_db
and start_time_db > start_time
and end_time_db < start_time
UNION
select
product_id, bin_no, bin_type, load_time, test_program, start_time_db as start_time,start_time as end_time, definition_db as definition, pass_fail_flag_db as pass_fail_flag
from exist_pbd
where
value_s <> value_s_db
and start_time_db > start_time
and end_time_db < start_time
UNION
select * from not_exist_pbd
修改后
INSERT INTO PRODUCT_BIN_DEFINITION
(product_id, bin_no, bin_type, load_time, test_program, start_time, end_time, definition, pass_fail_flag)
with pbd as (
SELECT product_id, bin_no, bin_type, test_program, start_time, definition, pass_fail_flag
FROM ODS.PRODUCT_BIN_DEFINITION
where :import_batch is not null
and load_time = :load_time
QUALIFY ROW_NUMBER() OVER(PARTITION BY product_id, bin_no, bin_type,test_program,definition ORDER BY start_time DESC) = 1
),
pbd_row as (
SELECT product_id, bin_no, bin_type, test_program, start_time, definition, pass_fail_flag,concat_ws("", definition, pass_fail_flag) as value_s
FROM pbd),
db_pbd as (
SELECT product_id, bin_no, bin_type, load_time, test_program, start_time, end_time, definition, pass_fail_flag,concat_ws("", definition, pass_fail_flag) as value_s
FROM PRODUCT_BIN_DEFINITION
where (product_id, bin_no, bin_type,test_program,definition) in
(SELECT DISTINCT product_id, bin_no, bin_type,test_program,definition from pbd_row)
),
min_db_pbd as (
SELECT product_id, bin_no, bin_type,test_program, start_time min_start_time, definition
from db_pbd
QUALIFY ROW_NUMBER() OVER(PARTITION BY product_id, bin_no, bin_type,test_program,definition ORDER BY start_time ASC) = 1
),
not_exist_pbd as (
SELECT product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time,
test_program, start_time, “9999-12-31 23:59:59” as end_time, definition, pass_fail_flag
from pbd_row
where (product_id, bin_no, bin_type,test_program,definition) not in
(SELECT DISTINCT product_id, bin_no, bin_type,test_program,definition from db_pbd)
),
exist_pbd as (
SELECT pbd_row.product_id, pbd_row.bin_no, pbd_row.bin_type, pbd_row.test_program, pbd_row.start_time, pbd_row.definition, pbd_row.pass_fail_flag,
pbd_row.value_s,load_time,db_pbd.start_time as start_time_db,db_pbd.end_time as end_time_db,db_pbd.definition as definition_db ,
db_pbd.value_s as value_s_db ,db_pbd.pass_fail_flag as pass_fail_flag_db,min_start_time
from pbd_row,db_pbd,min_db_pbd
where
pbd_row.product_id = db_pbd.product_id
and pbd_row.bin_no = db_pbd.bin_no
and pbd_row.bin_type = db_pbd.bin_type
and pbd_row.test_program = db_pbd.test_program
and pbd_row.definition = db_pbd.definition
and pbd_row.product_id = min_db_pbd.product_id
and pbd_row.bin_no = min_db_pbd.bin_no
and pbd_row.bin_type = min_db_pbd.bin_type
and pbd_row.test_program = min_db_pbd.test_program
and pbd_row.definition = min_db_pbd.definition)
select
product_id, bin_no, bin_type, load_time, test_program, start_time,end_time_db as end_time, definition, pass_fail_flag
from exist_pbd
where
value_s = value_s_db
and min_start_time > start_time
and min_start_time = start_time_db
UNION
select
product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time,start_time_db as end_time, definition, pass_fail_flag
from exist_pbd
where
value_s <> value_s_db
and min_start_time > start_time
and min_start_time = start_time_db
UNION
select
product_id, bin_no, bin_type, date_format(current_timestamp(), ‘%Y-%m-%d %H:%i:%s’) as load_time, test_program, start_time,end_time_db as end_time, definition, pass_fail_flag
from exist_pbd
where
value_s <> value_s_db
and start_time_db > start_time
and end_time_db < start_time
UNION
select
product_id, bin_no, bin_type, load_time, test_program, start_time_db as start_time,start_time as end_time, definition_db as definition, pass_fail_flag_db as pass_fail_flag
from exist_pbd
where
value_s <> value_s_db
and start_time_db > start_time
and end_time_db < start_time
UNION
select * from not_exist_pbd
CREATE TABLE IF NOT EXISTS YODA_YMS.PRODUCT_BIN_DEFINITION (
product_id varchar(32) NOT NULL COMMENT “Product name”,
bin_no varchar(64) NULL COMMENT “”,
bin_type TINYINT NULL COMMENT “0:hard,1:soft”,
test_program varchar(64) NOT NULL COMMENT “”,
start_time datetime NOT NULL COMMENT “”,
end_time datetime NULL DEFAULT “9999-12-31 23:59:59” COMMENT “”,
definition varchar(128) NULL COMMENT “”,
pass_fail_flag TINYINT NOT NULL COMMENT “0:pass,1:fail”,
load_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT “Load datetimetimetime”
) ENGINE = OLAP
UNIQUE KEY(product_id,bin_no,bin_type,test_program)
COMMENT “OLAP”
DISTRIBUTED BY HASH(product_id,bin_no,bin_type) BUCKETS 32
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
CREATE TABLE IF NOT EXISTS ODS.PRODUCT_BIN_DEFINITION (
product_id varchar(32) NOT NULL COMMENT “Product name”,
bin_no int(11) NOT NULL COMMENT “”,
bin_type TINYINT NOT NULL COMMENT “0:hard,1:soft”,
test_program varchar(64) NOT NULL COMMENT “”,
start_time datetime NULL DEFAULT ‘1970-01-01 00:00:00’ COMMENT “”,
definition varchar(128) NULL COMMENT “”,
pass_fail_flag TINYINT NOT NULL COMMENT “0:pass,1:fail”,
load_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT “Load datetimetimetime”,
INDEX index_product (product_id) USING BITMAP,
INDEX index_no (bin_no) USING BITMAP,
INDEX index_type (bin_type) USING BITMAP
) ENGINE = OLAP
DUPLICATE KEY(product_id)
COMMENT “OLAP”
PARTITION BY date_trunc(‘day’,load_time)
DISTRIBUTED BY HASH(product_id) BUCKETS 6
PROPERTIES (
“replication_num” = “1”,
“partition_live_number” = “7”,
“replicated_storage” = “true”,
“compression” = “ZSTD”
);
二者的区别是会 crash 的多了 test_program 列吗？
要不加微信沟通，我的微信是 liuzihe12