supports multiple Kafka clusters and DBs

Domain classes

Issue-ID: DCAEGEN2-1631

Change-Id: I54a715b2d3d8e13f347e46b0faf9d120d9a60548
Signed-off-by: Guobiao Mo <guobiaomo@chinamobile.com>

2 files changed, 201 insertions, 91 deletions

diff --git a/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql b/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql
index 7c7b2fbf..c4f75fbe 100644
--- a/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql
+++ b/components/datalake-handler/feeder/src/assembly/scripts/init_db.sql
@@ -1,126 +1,144 @@
+drop DATABASE datalake;

 create database datalake;

 use datalake;

 

-CREATE TABLE `topic` (

-  `name` varchar(255) NOT NULL,

-  `correlate_cleared_message` bit(1) DEFAULT NULL,

-  `enabled` bit(1) DEFAULT 0,

-  `login` varchar(255) DEFAULT NULL,

-  `message_id_path` varchar(255) DEFAULT NULL,

-  `aggregate_array_path` varchar(2000) DEFAULT NULL,

-  `flatten_array_path` varchar(2000) DEFAULT NULL,

-  `pass` varchar(255) DEFAULT NULL,

-  `save_raw` bit(1) DEFAULT NULL,

-  `ttl` int(11) DEFAULT NULL,

-  `data_format` varchar(255) DEFAULT NULL,

-  PRIMARY KEY (`name`)

+CREATE TABLE `topic_name` (

+  `id` varchar(255) NOT NULL,

+  PRIMARY KEY (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

+CREATE TABLE `db_type` (

+  `id` varchar(255) NOT NULL,

+  `default_port` int(11) DEFAULT NULL,

+  `name` varchar(255) DEFAULT NULL,

+  `tool` bit(1) DEFAULT NULL,

+  PRIMARY KEY (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 CREATE TABLE `db` (

-  `name` varchar(255) NOT NULL,

-  `enabled` bit(1) DEFAULT 0,

-  `host` varchar(255) DEFAULT NULL,

-  `port` int(11) DEFAULT NULL,

+  `id` int(11) NOT NULL AUTO_INCREMENT,

   `database_name` varchar(255) DEFAULT NULL,

+  `enabled` bit(1) DEFAULT NULL,

   `encrypt` bit(1) DEFAULT NULL,

+  `host` varchar(255) DEFAULT NULL,

   `login` varchar(255) DEFAULT NULL,

+  `name` varchar(255) DEFAULT NULL,

   `pass` varchar(255) DEFAULT NULL,

+  `port` int(11) DEFAULT NULL,

   `property1` varchar(255) DEFAULT NULL,

   `property2` varchar(255) DEFAULT NULL,

   `property3` varchar(255) DEFAULT NULL,

-  PRIMARY KEY (`name`)

-) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-

-

-CREATE TABLE `map_db_topic` (

-  `db_name` varchar(255) NOT NULL,

-  `topic_name` varchar(255) NOT NULL,

-  PRIMARY KEY (`db_name`,`topic_name`),

-  KEY `FK_topic_name` (`topic_name`),

-  CONSTRAINT `FK_topic_name` FOREIGN KEY (`topic_name`) REFERENCES `topic` (`name`),

-  CONSTRAINT `FK_db_name` FOREIGN KEY (`db_name`) REFERENCES `db` (`name`)

+  `db_type_id` varchar(255) NOT NULL,

+  PRIMARY KEY (`id`),

+  KEY `FK3njadtw43ieph7ftt4kxdhcko` (`db_type_id`),

+  CONSTRAINT `FK3njadtw43ieph7ftt4kxdhcko` FOREIGN KEY (`db_type_id`) REFERENCES `db_type` (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 CREATE TABLE `portal` (

-  `name` varchar(255) NOT NULL DEFAULT '',

-  `enabled` bit(1) DEFAULT 0,

-  `host` varchar(500) DEFAULT NULL,

-  `port` int(5) unsigned DEFAULT NULL,

+  `name` varchar(255) NOT NULL,

+  `enabled` bit(1) DEFAULT NULL,

+  `host` varchar(255) DEFAULT NULL,

   `login` varchar(255) DEFAULT NULL,

   `pass` varchar(255) DEFAULT NULL,

-  `related_db` varchar(255) DEFAULT NULL,

+  `port` int(11) DEFAULT NULL,

+  `related_db` int(11) DEFAULT NULL,

   PRIMARY KEY (`name`),

-  KEY `FK_related_db` (`related_db`),

-  CONSTRAINT `FK_related_db` FOREIGN KEY (`related_db`) REFERENCES `db` (`name`) ON DELETE SET NULL

+  KEY `FKtl6e8ydm1k7k9r5ukv9j0bd0n` (`related_db`),

+  CONSTRAINT `FKtl6e8ydm1k7k9r5ukv9j0bd0n` FOREIGN KEY (`related_db`) REFERENCES `db` (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

+

 CREATE TABLE `design_type` (

-  `name` varchar(255) NOT NULL,

-  `display` varchar(255) NOT NULL,

+  `id` varchar(255) NOT NULL,

+  `name` varchar(255) DEFAULT NULL,

+  `note` varchar(255) DEFAULT NULL,

+  `db_type_id` varchar(255) NOT NULL,

   `portal` varchar(255) DEFAULT NULL,

-  `note` text DEFAULT NULL,

-  PRIMARY KEY (`name`),

-  KEY `FK_portal` (`portal`),

-  CONSTRAINT `FK_portal` FOREIGN KEY (`portal`) REFERENCES `portal` (`name`) ON DELETE SET NULL

-) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-

-CREATE TABLE `portal_design` (

-  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

-  `name` varchar(255) NOT NULL,

-  `submitted` bit(1) DEFAULT 0,

-  `body` text DEFAULT NULL,

-  `note` text DEFAULT NULL,

-  `topic` varchar(255) DEFAULT NULL,

-  `type` varchar(255) DEFAULT NULL,

   PRIMARY KEY (`id`),

-  KEY `FK_topic` (`topic`),

-  KEY `FK_type` (`type`),

-  CONSTRAINT `FK_topic` FOREIGN KEY (`topic`) REFERENCES `topic` (`name`) ON DELETE SET NULL,

-  CONSTRAINT `FK_type` FOREIGN KEY (`type`) REFERENCES `design_type` (`name`) ON DELETE SET NULL

+  KEY `FKm8rkv2qkq01gsmeq1c3y4w02x` (`db_type_id`),

+  KEY `FKs2nspbhf5wv5d152l4j69yjhi` (`portal`),

+  CONSTRAINT `FKm8rkv2qkq01gsmeq1c3y4w02x` FOREIGN KEY (`db_type_id`) REFERENCES `db_type` (`id`),

+  CONSTRAINT `FKs2nspbhf5wv5d152l4j69yjhi` FOREIGN KEY (`portal`) REFERENCES `portal` (`name`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-insert into db (`name`,`host`,`login`,`pass`,`database_name`) values ('Couchbase','dl_couchbase','dl','dl1234','datalake');

-insert into db (`name`,`host`) values ('Elasticsearch','dl_es');

-insert into db (`name`,`host`,`port`,`database_name`) values ('MongoDB','dl_mongodb',27017,'datalake');

-insert into db (`name`,`host`) values ('Druid','dl_druid');

-insert into db (`name`,`host`,`login`) values ('HDFS','dlhdfs','dl');

-

-

--- in production, default enabled should be off

-insert into `topic`(`name`,`enabled`,`save_raw`,`ttl`,`data_format`) values ('_DL_DEFAULT_',1,0,3650,'JSON');

-insert into `map_db_topic`(`db_name`,`topic_name`) select `name`, '_DL_DEFAULT_' from db;

 

+CREATE TABLE `design` (

+  `id` int(11) NOT NULL AUTO_INCREMENT,

+  `body` varchar(255) DEFAULT NULL,

+  `name` varchar(255) DEFAULT NULL,

+  `note` varchar(255) DEFAULT NULL,

+  `submitted` bit(1) DEFAULT NULL,

+  `design_type_id` varchar(255) NOT NULL,

+  `topic_name_id` varchar(255) NOT NULL,

+  PRIMARY KEY (`id`),

+  KEY `FKo43yi6aputq6kwqqu8eqbspm5` (`design_type_id`),

+  KEY `FKabb8e74230glxpaiai4aqsr34` (`topic_name_id`),

+  CONSTRAINT `FKabb8e74230glxpaiai4aqsr34` FOREIGN KEY (`topic_name_id`) REFERENCES `topic_name` (`id`),

+  CONSTRAINT `FKo43yi6aputq6kwqqu8eqbspm5` FOREIGN KEY (`design_type_id`) REFERENCES `design_type` (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-insert into `topic`(`name`,correlate_cleared_message,`enabled`, message_id_path,`data_format`) values ('unauthenticated.SEC_FAULT_OUTPUT',1,1,'/event/commonEventHeader/eventName,/event/commonEventHeader/reportingEntityName,/event/faultFields/specificProblem,/event/commonEventHeader/eventId','JSON');

-insert into `map_db_topic`(`db_name`,`topic_name`) select `name`, 'unauthenticated.SEC_FAULT_OUTPUT' from db;

 

-insert into `topic`(`name`,`enabled`, aggregate_array_path,flatten_array_path,`data_format`) 

-values ('unauthenticated.VES_MEASUREMENT_OUTPUT',1,

-'/event/measurementsForVfScalingFields/memoryUsageArray,/event/measurementsForVfScalingFields/diskUsageArray,/event/measurementsForVfScalingFields/cpuUsageArray,/event/measurementsForVfScalingFields/vNicPerformanceArray',

-'/event/measurementsForVfScalingFields/astriMeasurement/astriDPMeasurementArray/astriInterface',

-'JSON');

-insert into `map_db_topic`(`db_name`,`topic_name`) select `name`, 'unauthenticated.VES_MEASUREMENT_OUTPUT' from db;

+CREATE TABLE `kafka` (

+  `id` varchar(255) NOT NULL,

+  `broker_list` varchar(255) DEFAULT NULL,

+  `check_topic_interval_sec` int(11) DEFAULT 10,

+  `consumer_count` int(11) DEFAULT 3,

+  `enabled` bit(1) DEFAULT NULL,

+  `excluded_topic` varchar(255) DEFAULT NULL,

+  `group` varchar(255) DEFAULT 'datalake',

+  `included_topic` varchar(255) DEFAULT NULL,

+  `login` varchar(255) DEFAULT NULL,

+  `name` varchar(255) DEFAULT NULL,

+  `pass` varchar(255) DEFAULT NULL,

+  `secure` bit(1) DEFAULT b'0',

+  `security_protocol` varchar(255) DEFAULT NULL,

+  `timeout_sec` int(11) DEFAULT 10,

+  `zk` varchar(255) DEFAULT NULL,

+  PRIMARY KEY (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-insert into `topic`(`name`,`enabled`,  flatten_array_path,`data_format`) 

-values ('EPC',1, 

-'/event/measurementsForVfScalingFields/astriMeasurement/astriDPMeasurementArray/astriInterface',

-'JSON');

-insert into `map_db_topic`(`db_name`,`topic_name`) select `name`, 'EPC' from db;

+CREATE TABLE `topic` (

+  `id` int(11) NOT NULL,

+  `aggregate_array_path` varchar(255) DEFAULT NULL,

+  `correlate_cleared_message` bit(1) DEFAULT NULL,

+  `data_format` varchar(255) DEFAULT NULL,

+  `enabled` bit(1) DEFAULT NULL,

+  `flatten_array_path` varchar(255) DEFAULT NULL,

+  `login` varchar(255) DEFAULT NULL,

+  `message_id_path` varchar(255) DEFAULT NULL,

+  `pass` varchar(255) DEFAULT NULL,

+  `save_raw` bit(1) DEFAULT NULL,

+  `ttl_day` int(11) DEFAULT NULL,

+  `topic_name_id` varchar(255) NOT NULL,

+  PRIMARY KEY (`id`),

+  KEY `FKj3pldlfaokdhqjfva8n3pkjca` (`topic_name_id`),

+  CONSTRAINT `FKj3pldlfaokdhqjfva8n3pkjca` FOREIGN KEY (`topic_name_id`) REFERENCES `topic_name` (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-insert into `topic`(`name`,`enabled`, aggregate_array_path,`data_format`) 

-values ('HW',1,

-'/event/measurementsForVfScalingFields/memoryUsageArray,/event/measurementsForVfScalingFields/diskUsageArray,/event/measurementsForVfScalingFields/cpuUsageArray,/event/measurementsForVfScalingFields/vNicPerformanceArray',

-'JSON');

-insert into `map_db_topic`(`db_name`,`topic_name`) select `name`, 'HW' from db;

 

-insert into portal (`name`,`related_db`, host) values ('Kibana', 'Elasticsearch', 'dl_es');

-insert into portal (`name`,`related_db`) values ('Elasticsearch', 'Elasticsearch');

-insert into portal (`name`,`related_db`) values ('Druid', 'Druid');

+CREATE TABLE `map_db_design` (

+  `design_id` int(11) NOT NULL,

+  `db_id` int(11) NOT NULL,

+  PRIMARY KEY (`design_id`,`db_id`),

+  KEY `FKhpn49r94k05mancjtn301m2p0` (`db_id`),

+  CONSTRAINT `FKfli240v96cfjbnmjqc0fvvd57` FOREIGN KEY (`design_id`) REFERENCES `design` (`id`),

+  CONSTRAINT `FKhpn49r94k05mancjtn301m2p0` FOREIGN KEY (`db_id`) REFERENCES `db` (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-insert into design_type (`name`,`display`,`portal`) values ('kibana_db', 'Kibana Dashboard', 'Kibana');

-insert into design_type (`name`,`display`,`portal`) values ('kibana_search', 'Kibana Search', 'Kibana');

-insert into design_type (`name`,`display`,`portal`) values ('kibana_visual', 'Kibana Visualization', 'Kibana');

-insert into design_type (`name`,`display`,`portal`) values ('es_mapping', 'Elasticsearch Field Mapping Template', 'Elasticsearch');

-insert into design_type (`name`,`display`,`portal`) values ('druid_kafka_spec', 'Druid Kafka Indexing Service Supervisor Spec', 'Druid');

+CREATE TABLE `map_db_topic` (

+  `topic_id` int(11) NOT NULL,

+  `db_id` int(11) NOT NULL,

+  PRIMARY KEY (`db_id`,`topic_id`),

+  KEY `FKq1jon185jnrr7dv1dd8214uw0` (`topic_id`),

+  CONSTRAINT `FKirro29ojp7jmtqx9m1qxwixcc` FOREIGN KEY (`db_id`) REFERENCES `db` (`id`),

+  CONSTRAINT `FKq1jon185jnrr7dv1dd8214uw0` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

+CREATE TABLE `map_kafka_topic` (

+  `kafka_id` varchar(255) NOT NULL,

+  `topic_id` int(11) NOT NULL,

+  PRIMARY KEY (`topic_id`,`kafka_id`),

+  KEY `FKtdrme4h7rxfh04u2i2wqu23g5` (`kafka_id`),

+  CONSTRAINT `FK5q7jdxy54au5rcrhwa4a5igqi` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`id`),

+  CONSTRAINT `FKtdrme4h7rxfh04u2i2wqu23g5` FOREIGN KEY (`kafka_id`) REFERENCES `kafka` (`id`)

+) ENGINE=InnoDB DEFAULT CHARSET=utf8;

diff --git a/components/datalake-handler/feeder/src/assembly/scripts/init_db_data.sql b/components/datalake-handler/feeder/src/assembly/scripts/init_db_data.sql
new file mode 100644
index 00000000..f7d261f2
--- /dev/null
+++ b/components/datalake-handler/feeder/src/assembly/scripts/init_db_data.sql
@@ -0,0 +1,92 @@
+INSERT INTO datalake.kafka(

+   id

+  ,name

+  ,check_topic_interval_sec

+  ,consumer_count

+  ,enabled

+  ,excluded_topic

+  ,`group`

+  ,broker_list

+  ,included_topic

+  ,login

+  ,pass

+  ,secure

+  ,security_protocol

+  ,timeout_sec

+  ,zk

+) VALUES (

+  'KAFKA_1'

+  ,'main Kafka cluster' -- name - IN varchar(255)

+  ,10   -- check_topic_sec - IN int(11)

+  ,3   -- consumer_count - IN int(11)

+  ,1   -- enabled - IN bit(1)

+  ,''  -- excluded_topic - IN varchar(255)

+  ,'dlgroup'  -- group - IN varchar(255)

+  ,'message-router-kafka:9092'  -- host_port - IN varchar(255)

+  ,''  -- included_topic - IN varchar(255)

+  ,'admin'  -- login - IN varchar(255)

+  ,'admin-secret'  -- pass - IN varchar(255)

+  ,0   -- secure - IN bit(1)

+  ,'SASL_PLAINTEXT'  -- security_protocol - IN varchar(255)

+  ,10   -- timeout_sec - IN int(11)

+  ,'message-router-zookeeper:2181'  -- zk - IN varchar(255)

+);

+

+insert into db_type (`id`, `name`, tool) values ('CB', 'Couchbase', false);

+insert into db_type (`id`, `name`, tool) values ('ES', 'Elasticsearch', false);

+insert into db_type (`id`, `name`, tool,`default_port`) values ('MONGO', 'MongoDB', false, 27017);

+insert into db_type (`id`, `name`, tool) values ('DRUID', 'Druid', false);

+insert into db_type (`id`, `name`, tool) values ('HDFS', 'HDFS', false);

+insert into db_type (`id`, `name`, tool) values ('KIBANA', 'Kibana', true);

+insert into db_type (`id`, `name`, tool) values ('SUPERSET', 'Apache Superset', true);

+

+insert into db (id, db_type_id, enabled, `name`,`host`,`login`,`pass`,`database_name`) values (1, 'CB', true, 'Couchbase 1','dl-couchbase','dl','dl1234','datalake');

+insert into db (id, db_type_id, enabled, `name`,`host`) values (2, 'ES', true, 'Elasticsearch','dl-es');

+insert into db (id, db_type_id, enabled, `name`,`host`,`port`,`database_name`) values (3, 'MONGO', true, 'MongoDB 1','dl-mongodb',27017,'datalake');

+insert into db (id, db_type_id, enabled, `name`,`host`) values (4, 'DRUID', true, 'Druid','dl-druid');

+insert into db (id, db_type_id, enabled, `name`,`host`,`login`) values (5, 'HDFS', true, 'Hadoop Cluster','dl-hdfs','dl');

+insert into db (id, db_type_id, enabled, `name`,`host`) values (6, 'KIBANA', true, 'Kibana demo','dl-es');

+insert into db (id, db_type_id, enabled, `name`,`host`) values (7, 'SUPERSET', true, 'Superset demo','dl-druid');

+

+

+insert into topic_name (id) values ('_DL_DEFAULT_');

+insert into topic_name (id) values ('unauthenticated.SEC_FAULT_OUTPUT');

+insert into topic_name (id) values ('unauthenticated.VES_MEASUREMENT_OUTPUT');

+insert into topic_name (id) values ('EPC');

+insert into topic_name (id) values ('HW');

+

+-- in production, default enabled should be off

+insert into `topic`(id, `topic_name_id`,`enabled`,`save_raw`,`ttl_day`,`data_format`) values (1, '_DL_DEFAULT_',1,0,3650,'JSON');

+

+insert into `topic`(id, `topic_name_id`,correlate_cleared_message,`enabled`, message_id_path,`data_format`) 

+values (2, 'unauthenticated.SEC_FAULT_OUTPUT',1,1,'/event/commonEventHeader/eventName,/event/commonEventHeader/reportingEntityName,/event/faultFields/specificProblem,/event/commonEventHeader/eventId','JSON');

+

+insert into `topic`(id, `topic_name_id`,`enabled`, aggregate_array_path,flatten_array_path,`data_format`) 

+values (3, 'unauthenticated.VES_MEASUREMENT_OUTPUT',1,

+'/event/measurementsForVfScalingFields/memoryUsageArray,/event/measurementsForVfScalingFields/diskUsageArray,/event/measurementsForVfScalingFields/cpuUsageArray,/event/measurementsForVfScalingFields/vNicPerformanceArray',

+'/event/measurementsForVfScalingFields/astriMeasurement/astriDPMeasurementArray/astriInterface',

+'JSON');

+

+insert into `topic`(id, `topic_name_id`,`enabled`,  flatten_array_path,`data_format`) 

+values (4, 'EPC',1, '/event/measurementsForVfScalingFields/astriMeasurement/astriDPMeasurementArray/astriInterface', 'JSON');

+

+insert into `topic`(id, `topic_name_id`,`enabled`, aggregate_array_path,`data_format`) 

+values (5, 'HW',1,

+'/event/measurementsForVfScalingFields/memoryUsageArray,/event/measurementsForVfScalingFields/diskUsageArray,/event/measurementsForVfScalingFields/cpuUsageArray,/event/measurementsForVfScalingFields/vNicPerformanceArray',

+'JSON'); 

+

+

+insert into `map_db_topic`(`db_id`,`topic_id`) select db.id, topic.id from db_type, db, topic where db.db_type_id=db_type.id and db_type.tool=0;

+insert into `map_kafka_topic`(`kafka_id`,`topic_id`) select kafka.id, topic.id from kafka, topic;

+

+

+insert into design_type (id, `name`, `db_type_id`) values ('KIBANA_DB', 'Kibana Dashboard', 'KIBANA');

+insert into design_type (id, `name`, `db_type_id`) values ('KIBANA_SEARCH', 'Kibana Search', 'KIBANA');

+insert into design_type (id, `name`, `db_type_id`) values ('KIBANA_VISUAL', 'Kibana Visualization', 'KIBANA');

+insert into design_type (id, `name`, `db_type_id`) values ('ES_MAPPING', 'Elasticsearch Field Mapping Template', 'ES');

+insert into design_type (id, `name`, `db_type_id`) values ('DRUID_KAFKA_SPEC', 'Druid Kafka Indexing Service Supervisor Spec', 'DRUID');

+

+

+insert into design (id, `name`,topic_name_id, `submitted`,`body`, design_type_id) values (1, 'Kibana Dashboard on EPC test1', 'EPC',  0, 'body here', 'KIBANA_DB');

+

+insert into map_db_design (`design_id`,`db_id` ) values (1, 6);