Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "database structure synchronization ElasticSearch index objects". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

cause

Due to the characteristics of the business, sometimes the data will need complex query logic to get, because the database itself does not support the complex query, then synchronizing the data to the capability middleware has become a common solution, and synchronization to Elasticsearch is one of them.

Scheme selection

The data source we use is MySql, and we choose to synchronize to ES. The idea is to subscribe to Binlog to reduce intrusiveness. There are also some solutions, such as go-mysql-elasticsearch, canal, gravity

However, these schemes usually only support one-to-one indexing, that is, one index per table, while the one-to-one and one-to-many relationships in the data table can not be reflected in the index. And this relationship is often needed in the business. Therefore, the overall consideration is to do a plug-in based on gravity.

Gravity is mobike's open source data synchronization middleware, the current data source support: MySql and Mongo,TiDB and PostgreSQL in development, synchronization target support: MySQL/TiDB and Kafka,Elasticsearch are still in the bate stage, and support 6. 5.

EsModel synchronization plug-in

Project address: gravity Welcome star:).

Synchronization strategy

Support the synchronization of the main table and one-to-one and a pair of multi-word tables, which can be synchronized into an index structure.

One-to-one relationships support synchronization in the form of tiles or sub-objects.

Support ES version: 6, 7

For example, there are four tables: student, student_class, student_detail, student_parent, where student is the student table (master table), student_class student class (one-to-one child table), student_detail student details (one-to-one child table), and student_parent student parents (one-to-many child table). Student_class synchronizes in the form of sub-objects and student_detail synchronizes in tiled form.

The Sql script is as follows:

DROP TABLE IF EXISTS `student`; CREATE TABLE `student` (`id` bigint (20) NOT NULL, `name` varchar (64) NOT NULL DEFAULT'', `highday` date NOT NULL DEFAULT '1970-01-01-01mm, `high`tinyint (11) NOT NULL DEFAULT' 0mm, `sex` tinyint (4) NOT NULL DEFAULT '1century, `create_ time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 -Records of student-- BEGIN;INSERT INTO `student` VALUES (1, 'Zhang San', '2010-02-05', 156,1, '2019-08-29 19 VALUES 55 VALUES') INSERT INTO `student`VALUES (2,'Li Si', '2010-03-05mm, 176,2,' 2019-08-2919purr 55336'); INSERT INTO `student`VALUES (3, 'Wang Ping', '2010-03-05mm, 176,2,' 2019-08-29 2009INSERT INTO 03'); -Table structure for student_class-- DROP TABLE IF EXISTS `student_ class` CREATE TABLE `student_ class` (`id` bigint (20) NOT NULL, `student_ id` bigint (20) NOT NULL DEFAULT '0mm, `name` varchar (255i) NOT NULL DEFAULT'', `student_ count` int (11) NOT NULL DEFAULT '0mm, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 -Records of student_class-- BEGIN;INSERT INTO `student_ class`VALUES (1, 1, 'Class 1', 32); INSERT INTO `student_ class` VALUES (2, 2, Class 2, 12); COMMIT -Table structure for student_detail---DROP TABLE IF EXISTS `student_ detail` CREATE TABLE `student_ detail` (`id`bigint (20) NOT NULL, `student_ id` bigint (20) NOT NULL DEFAULT '0mm, `introduce`varchar (255NOT NULL DEFAULT''), `mobile`varchar (64) NOT NULL DEFAULT'', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 -Records of student_detail-- BEGIN;INSERT INTO `student_ detail`VALUES (1,1, 'introduction to Zhang San', '1888888888888'); INSERT INTO `student_ detail` VALUES (2,2, 'introduction to Li Si', '13333333333'); COMMIT -Table structure for student_parent---DROP TABLE IF EXISTS `student_ parent` CREATE TABLE `student_ parent` (`id` bigint (20) NOT NULL, `create_ id` bigint (20) NOT NULL DEFAULT '0mm, `name` varchar (255) NOT NULL DEFAULT'', `day` date NOT NULL DEFAULT '1970-01-01-01mm, `sex` tinyint (11) parent`, `create_ time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 -Records of student_parent-- BEGIN;INSERT INTO `student_ parent` VALUES (1, 1, 'Father Zhang San', '1980-02-02, 1,' 2019-08-29 20 20 student_ 0015) INSERT INTO `student_ parent` VALUES (2, 1, 'mother Zhang San', '1982-07-07, 2,' 2019-08-29 2000 VALUES 58'); INSERT INTO `parent` VALUES (3, 2,'Li four father', '1979-03-03', 1, '2019-08-29 2000 RV 58') INSERT INTO `student_ parent` VALUES (4, 2,'Li Si Mu', '1981-06-06, 2,' 2019-08-29 20 20-00 VALUES 58')

The synchronization configuration is as follows:

# name required name = "mysql2esmodelDemo" # the name of the library used internally to save sites, heartbeats, etc. Default is _ gravityinternal-db-name = "_ gravity" # # definition of the Input plug-in It is defined here to use mysql# [input] type = "mysql" mode = "replication" [input.config.source] host = "192.168.1.148" username = "root" password = "mysqldev" port = 3306max-idle = 10max-open = 10 [output] type = "esmodel" [output.config] # ignore 400 (bad request) return # when the index name is irregular and parsing error, Elasticsearch returns 400error # defaults false, that is, an exception is thrown in case of failure It must be handled manually. These requests ignore-bad-request = true## destination Elasticsearch configuration #-required # [output.config.server] # connected Elasticsearch address are ignored when set to true. Urls = ["whether node sniffing is performed by http://192.168.1.152:9200"]#. Default is falsesniff = false# timeout. Default is 1000mstimeout = 500authenticated # destination authentication configuration #-optional # [output.config.server.auth] username = "" password = "" [output.config.routes]] match-schema = "test" # main table match-table = "student" # index name index-name= "student_index" # type name, es7 item invalid type-name= "student" # number of shards-num=1# copies of replicas-num=0# failed retries column included by retry-count=3# Default all include-column = [] # excluded columns, default no exclude-column = [] # column name escape policy [output.config.routes.convert-column] name = "studentName" [[output.config.routes.one-one]] match-schema = "test" match-table = "student_detail" # Foreign key column fk-column = "student_id" # columns, default all include-column = [] # excluded columns, default does not have exclude-column = [] # mode 1: sub-object, 2 index tiling mode = attribute object name, mode 1 is valid, default is source table name hump structure property-name = "studentDetail" # attribute prefix, mode 2 is valid Default is source table name hump structure property-pre = "sd_" [output.config.routes.one-one.convert-column] introduce = "introduceInfo" [[output.config.routes.one-one]] match-schema = "test" match-table = "student_class" # Foreign key column fk-column = "student_id" # columns, default all include-column = [] # excluded columns, default does not have exclude-column = [] # mode, 1: sub-object 2 Index tiling mode = attribute object name, valid for mode 1, default is source table name hump structure property-name = "studentClass" # attribute prefix, valid for mode 2 Default is the source table name hump structure property-pre = "sc_" [output.config.routes.one-one.convert-column] name = "className" [[output.config.routes.one-more]] match-schema = "test" match-table = "student_parent" # Foreign key column fk-column = "student_id" # columns, default all include-column = [] # excluded columns, default does not have exclude-column = [] # attribute object name The default is source table name hump structure property-name = "studentParent" [output.config.routes.one-more.convert-column] name = "parentName"

Compile to the project root directory make and execute the synchronization command

. / bin/gravity-config. / docs/2.0/example-mysql2esmodel.toml

The index structure after synchronization is:

{"state": "open", "settings": {"index": {"creation_date": "1567160065596", "number_of_shards": "1", "number_of_replicas": "0", "uuid": "noe_V-RdTr6QaFDy4fPRjA" "version": {"created": "7030199"}, "provided_name": "student_index"}} "mappings": {"_ doc": {"properties": {"birthday": {"type": "date"}, "studentParent": {"type": "nested" "properties": {"birthday": {"type": "date"}, "parentName": {"type": "text" "fields": {"keyword": {"ignore_above": 256 "type": "keyword"}}, "create_time": {"type": "date"} "sex": {"type": "long"}, "student_id": {"type": "long"} "id": {"type": "long"}, "high": {"type": "long"} "create_time": {"type": "date"}, "sex": {"type": "long"}, "studentName": {"type": "text" "fields": {"keyword": {"ignore_above": 256, "type": "keyword"} "sd_student_id": {"type": "long"}, "introduceInfo": {"type": "text" "fields": {"keyword": {"ignore_above": 256, "type": "keyword"} "sd_id": {"type": "long"}, "id": {"type": "long"}, "sd_mobile": {"type": "text" "fields": {"keyword": {"ignore_above": 256, "type": "keyword"} "studentClass": {"properties": {"sc_id": {"type": "long"}, "className": {"type": "text" "fields": {"keyword": {"ignore_above": 256 "type": "keyword"}}, "sc_student_count": {"type": "long"} "sc_student_id": {"type": "long"}}

Sample data are:

{"_ index": "student_index", "_ type": "_ doc", "_ id": "2", "_ version": 5, "_ score": 1, "_ source": {"studentClass": {"className": "Class 2", "sc_id": 2, "sc_student_count": 12 "sc_student_id": 2}, "sd_student_id": 2, "introduceInfo": "introduction to Li Si", "sd_id": 2, "sd_mobile": "13333333333", "studentParent": [{"birthday": "1981-06-06T00:00:00+08:00" "parentName": "Li Si Mother", "create_time": "2019-08-29T20:00:58+08:00", "sex": 2, "student_id": 2, "id": 4} {"birthday": "1979-03-03T00:00:00+08:00", "parentName": "Li Si's father", "create_time": "2019-08-29T20:00:58+08:00", "sex": 1, "student_id": 2 "id": 3}], "birthday": "2010-03-05T00:00:00+08:00", "high": 176, "create_time": "2019-08-29T19:55:36+08:00", "sex": 2, "studentName": "Li Si" This is the end of the introduction of "id": 2}} "database structure synchronization ElasticSearch index objects" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.