etl configuration - kwantu/platformconfiguration GitHub Wiki

back

Setup the mysql tables to manage the process

CREATE TABLE `etl_types` (
  `etl_type` varchar(20) NOT NULL,
  `elt_type_function` varchar(20) DEFAULT NULL,
  `is_active` char(1) DEFAULT 'T',
  PRIMARY KEY (`etl_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `etl_appcodes` (
  `uuid` varchar(50) NOT NULL,
  `appCode` varchar(20) NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `etl_control` (
  `etl_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `communityId` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `bookmark` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `recordsRetured` int(11) DEFAULT NULL,
  `totalRecords` int(11) DEFAULT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci DEFAULT 'notStarted',
  `lastRun` datetime DEFAULT NULL,
  `result` varchar(5000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pending` int(11) DEFAULT NULL,
  `processedTo` bigint(20) DEFAULT '0',
  `serverSince` bigint(20) DEFAULT '0',
  `serverBookmark` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`etl_type`,`communityId`),
  KEY `lastRun` (`lastRun`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `etl_log` (
  `etl_key` bigint(20) NOT NULL AUTO_INCREMENT,
  `etl_type` varchar(20) DEFAULT NULL,
  `communityId` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `processIdRequested` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `processIdResult` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `seq` int(11) DEFAULT NULL,
  `validDate` date DEFAULT NULL,
  `dateTimeCreated` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `profileId` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `spStatus` varchar(20) DEFAULT NULL,
  `currentStep` varchar(100) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `insertError` char(5) DEFAULT NULL,
  PRIMARY KEY (`etl_key`),
  KEY `eltType_prcessType` (`etl_type`,`type`,`etl_key`),
  KEY `insertError` (`insertError`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `etl_types` (`etl_type`, `elt_type_function`, `is_active`) VALUES('elasticSpInstance','elasticSpInstance','T');
INSERT INTO `etl_types` (`etl_type`, `elt_type_function`, `is_active`) VALUES('elasticWorkerObject','elasticWorkerObject','T');

INSERT INTO `etl_control` (`etl_type`,`communityId`,`serverSince`,`serverBookmark`)
SELECT etl_type,`communityId`,0,"" FROM `etl_types` JOIN community WHERE `accessType` = '1';
`etl_appcodes`;

INSERT INTO `etl_control` (`etl_type`,`communityId`) VALUES ('all_dbs','all_dbs');