【搜索引擎】Smartsys部署ES的准备工作

1. 扩展表空间

扩展SMARTSYS_HISTORY和SMARTSYS_HISTORY表空间，使当前使用率降至40%或以下。

2. 将现有历史事项表重命名

ALTER TABLE scada_event rename to scada_event_bak;
ALTER INDEX SCADA_EVENT_INDEX rename to SCADA_EVENT_INDEX_BAK;
ALTER INDEX SCADA_EVENT_INDEX2 rename to SCADA_EVENT_INDEX2_BAK;

3. 创建新的带自增字段的历史事项表

CREATE TABLE "SMARTSYS"."scada_event"
(
"ID" BIGINT IDENTITY(1,1) CLUSTER PRIMARY KEY,
"KEY_ID_TAG" VARCHAR(100) DEFAULT 'dummy' NOT NULL,
"TAG_ID" BIGINT NOT NULL,
"OCCUR_TIME" TIMESTAMP(0),
"MILLI_SECOND" INTEGER NOT NULL,
"SOE_TIME" TIMESTAMP(0),
"SOE_MSECOND" INTEGER,
"DOMAIN_ID" INTEGER,
"STATION_ID" BIGINT NOT NULL,
"REGION_ID" INTEGER,
"INFER_NAME" VARCHAR(64),
"ALARM_PRIORITY" INTEGER,
"STATUS" INTEGER,
"CONTENT" VARCHAR(256),
"CONFIRM_TIME" TIMESTAMP(0),
"CONFIRM_NODE_NAME" VARCHAR(64),
"ALARM_TYPE" INTEGER NOT NULL,
"APP_ID" INTEGER,
"CONTAINER1_ID" BIGINT,
"CONTAINER2_ID" BIGINT,
"DEV_ID" BIGINT,
"CONFIRM_USER_NAME" VARCHAR(64),
"CONTEXT" INTEGER,
"SYSID" INTEGER DEFAULT 1 NOT NULL,
"AREA_RESP" INTEGER DEFAULT (-1) NOT NULL,
"OBJ_ID" BIGINT,
"REASON_CODE" INTEGER,
"DICTIONARY" CHARACTER VARYING(512),
"VALUE" DOUBLE) STORAGE(ON "SMART_HISTORY", CLUSTERBTR);
CREATE  INDEX "SCADA_EVENT_INDEX" ON "SMARTSYS"."scada_event"("DEV_ID" ASC,"OCCUR_TIME" ASC,"OBJ_ID" ASC,"STATION_ID" ASC,"CONTAINER1_ID" ASC,"ALARM_TYPE" ASC,"STATUS" ASC) STORAGE(ON "SMART_HISTORY_INDEX", CLUSTERBTR) ;
CREATE  INDEX "SCADA_EVENT_INDEX2" ON "SMARTSYS"."scada_event"("OCCUR_TIME" ASC,"ALARM_TYPE" ASC,"STATUS" ASC,"STATION_ID" ASC,"CONTAINER1_ID" ASC,"CONTAINER2_ID" ASC) STORAGE(ON "SMART_HISTORY_INDEX", CLUSTERBTR) ;

经过对金仓和达梦数据库的测试，在执行步骤1和2的时候不需要停Smartsys系统，切换过程持续几秒钟，事项可能会存在及少量的丢失，但不会影响切换结果。

4. 根据需要将部分旧的事项数据从SCADA_EVENT_BAK中，分批导入ES。

--1. 分批次迁移历史数据，分批的目的在于将每次的数据提交量控制在5000 0000条以下，以防止临时缓冲区不足。
insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-08-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-07-01' and occur_time < '2019-08-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-06-01' and occur_time < '2019-07-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-05-01' and occur_time < '2019-06-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-04-01' and occur_time < '2019-05-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-03-01' and occur_time < '2019-04-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-02-01' and occur_time < '2019-03-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time >= '2019-01-01' and occur_time < '2019-02-01';
commit;

insert into SCADA_EVENT(KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,
CONFIRM_NODE_NAME,ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE") 
select KEY_ID_TAG,TAG_ID,OCCUR_TIME,MILLI_SECOND,SOE_TIME,SOE_MSECOND,DOMAIN_ID,STATION_ID,REGION_ID,INFER_NAME,ALARM_PRIORITY,STATUS,CONTENT,CONFIRM_TIME,CONFIRM_NODE_NAME,
ALARM_TYPE,APP_ID,CONTAINER1_ID,CONTAINER2_ID,DEV_ID,CONFIRM_USER_NAME,"CONTEXT",SYSID,AREA_RESP,OBJ_ID,REASON_CODE,"DICTIONARY","VALUE" from scada_event_bak 
where occur_time < '2019-01-01';
commit;

这一步骤需要花费较长时间，根据实测数据显示，插入效率大约为：4000 0000条/小时。完成后需要检查执行日志（打印信息），确认每条语句都已经执行成功，并提交完成。

5. 运行ES迁移程序，将新SCADA_EVENT的内容迁移至ES中，并保持该迁移程序持续运行，按既定策略定期完成数据从SCADA_EVENT到ES的同步。