Method, system and equipment for synchronizing data to southern big general database based on Flink and storage medium

Abstract

The invention discloses a method, a system and equipment for synchronizing data to a southern big general database based on Flink, and a storage medium, belongs to the technical field of synchronizing data to databases, and solves the technical problem that the Flink does not support writing data into the southern big general domestic database at present. The method comprises the following steps of: creating a new project, and adding a JDBC (JavaSouth Big Universal Database) driving dependency package, jar required by Flink and an flink-connector-mysql-cdc package; the method comprises the following steps of: acquiring a source code of an flink-connector-jdbc project which needs to be modified; the flink-connector-jdbc.jar is subjected to the transformation of adapting to the South big general database, and the flink-connector-jdbc.jar is subjected to the transformation of the South big general database; the method comprises the following steps: additionally adding an flink-connector-jdbc-gbase. Jar dependency package which is modified by being adapted to a south big general database into a project; compiling in a project by using flinksql grammar, and synchronizing the read user table in Mysql to user table definition information of a southern big general database in real time; packaging the project into jar and running the jar into an flink environment; therefore, the data can be efficiently written into the South big general database in batches in a FlinkStream and SQL mode.

Description

Technical Field

[0001] The present invention relates to the technical field of synchronizing data to a database, and more specifically, to a method, system, device and storage medium for synchronizing data to a Nanjing University general database based on Flink. Background Art

[0002] In the face of the information and innovation business, in response to the needs of information security, technical independence, industrial security and other aspects, Flink real-time data integration is not well adapted to the information and innovation database. More and more companies choose to use the Nanda general domestic database as their business database.

[0003] However, the JDBC Connector component of Flink currently does not support writing data into the Nanda general domestic database. Therefore, a method based on Flink to synchronize data to the Nanda general database is urgently needed to meet market needs. Summary of the invention

[0004] The technical problem to be solved by the present invention is to address the above-mentioned deficiencies in the prior art. One purpose of the present invention is to provide a method for synchronizing data to a Nanjing University general database based on Flink.

[0005] The second objective of the present invention is to provide a system for synchronizing data to the Nanjing University general database based on Flink.

[0006] A third object of the present invention is to provide a computer device.

[0007] A fourth objective of the present invention is to provide a computer storage medium.

[0008] In order to achieve the above-mentioned objective 1, the present invention provides a method for synchronizing data to a Nanjing University general database based on Flink, comprising the following steps:

[0009] Step 1. Create a new project and add the JDBC driver dependency package of Nanjing University General Database, the jar required by Flink, and the flink-connector-mysql-cdc package to the project;

[0010] Step 2. Obtain the source code of the flink-connector-jdbc project that needs to be modified, which is flink-connector-jdbc-gbase.jar; and adapt flink-connector-jdbc.jar to the Nanjing University General Database;

[0011] Step 3. Add the flink-connector-jdbc-gbase.jar dependency package modified to adapt to the Nanjing University General Database to the project;

[0012] Step 4. Use flinksql syntax in the project to write the user table definition information that will be read from the user table in Mysql and synchronized to the NCU general database in real time;

[0013] Step 5. Package the project into a jar and run it in the flink environment.

[0014] As a further improvement, download flink-connector-jdbc from github to adapt to the version code of Nanjing University's general database transformation.

[0015] Furthermore, the version code is not lower than version code 3.1 of flink-connector-jdbc.

[0016] Furthermore, the transformation based on the flink-connector-jdbc source code includes:

[0017] Step 2.1. Add the Maven dependency package of NCU General Database and set the scope to provided;

[0018] Step 2.2. Create a new GbaseDialectFactory class to implement the JdbcDialectFactory interface and create a dialect implementation class for the Nanjing University General Database;

[0019] Step 2.3. Add to resouces / META-INF.servies in the flink-connector-jdbc project source code

[0020] org.apache.flink.connector.jdbc.databases.gbase.dialect.GbaseDialectFactory, to create JdbcDialectFactory in spi mode;

[0021] Step 2.4. Create a new GbaseRowConverter class to serialize and deserialize row data. The GbaseRowConverter class inherits from the AbstractJdbcRowConverter abstract class. The GbaseRowConverter class is used to define the logic of converting the field values ​​of the Nanjing University General Database into the field values ​​inside Flink and the logic of converting the field values ​​of Flink into the field values ​​outside the Nanjing University General Database.

[0022] Step 2.5. Create a new GbaseDialect implementation class. The GbaseDialect implementation class inherits from the AbstractDialect abstract class. The GbaseDialect implementation class is used to define the preset SQL statements for insert, update, upsert, and delete SQL statements.

[0023] Furthermore, in step 2.2, the acceptsURL method of JdbcDialectFactory is rewritten to determine whether the prefix of the url path starts with jdbc:gbase, and the url path is used as the identifier of the current dialect factory. The create method is rewritten for the dialect factory to create the Nanjing University General Database dialect implementation class GbaseDialect.

[0024] Further, step 2.4 includes:

[0025] Step 2.4.1. Create a new converterName method that implements AbstractJdbcRowConverter and uses it as the identifier of the NCU general database type converter;

[0026] Step 2.4.2. Create a createInternalConverter method that implements AbstractJdbcRowConverter and define how to convert the values ​​of each field type of the Nanjing University general database type into the mapping relationship of the field type value inside Flink;

[0027] Step 2.4.3. Create a createExternalConverter method that implements AbstractJdbcRowConverter, and define the mapping relationship between how to convert the values ​​of each field type in Flink into the field type values ​​of the Nanjing University general database type.

[0028] Further, step 2.5 includes:

[0029] Step 2.5.1. Create a new implementation of the getRowConverter method to return a GbaseRowConverter instance for converting row data values ​​in the current dialect;

[0030] Step 2.5.2. Create a new implementation of the getLimitClause method to return the SQL statement fragment of the data interception;

[0031] Step 2.5.3. Create a new implementation of the defaultDriverName method to return the full class name of the current Nanda General driver class, which is used to load the driver class when operating the Nanda General database;

[0032] Step 2.5.4. Create a new dialectName method to set it to the name of the current dialect identified by gbase;

[0033] Step 2.5.5. Create a new supportedTypes method to set the Flink field type supported by Nanda General Database;

[0034] Step 2.5.6. Create a new implementation of the getUpsertStatement method to return the SQL statement of the upsert statement of the Nanjing University General Database based on the table name and field information provided by Flink;

[0035] Step 2.5.7. Create a new implementation of the getRowExistsStatement method, which is used to return the SQL statement statement of whether there is a data row in the Nanjing University General Database based on the table name and condition information provided by Flink;

[0036] Step 2.5.8. Create a new implementation of the getInsertIntoStatement method to return the SQL statement statement for inserting data into the NCU General Database based on the table name and field information provided by Flink;

[0037] Step 2.5.9. Create a new implementation of the getUpdateStatement method, which is used to return the SQL statement statement for updating data of the Nanjing University General Database based on the table name, field information and condition information provided by Flink;

[0038] Step 2.5.10. Create a new getDeleteStatement method to return the SQL statement for deleting data rows in the Nanjing University General Database based on the table name and condition information provided by Flink;

[0039] Step 2.5.11. Create a new implementation of the getSelectFromStatement method, which is used to return the SQL statement declaration of the query data row of the Nanjing University General Database based on the table name, condition information, and projection information provided by Flink.

[0040] In order to achieve the above-mentioned second objective, the present invention provides a system for synchronizing data to a general database of Nanjing University based on Flink, comprising:

[0041] The project creation module is used to create a new project. In the project, add the JDBC driver dependency package of Nanjing University General Database, the jar required by Flink, and the flink-connector-mysql-cdc package;

[0042] The transformation module is used to obtain the source code of the flink-connector-jdbc project that needs to be modified. The source code is: flink-connector-jdbc-gbase.jar; and flink-connector-jdbc.jar is adapted to the Nanjing University general database.

[0043] Add a module to add the flink-connector-jdbc-gbase.jar dependency package modified to adapt to the Nanjing University general database to the project;

[0044] The reading definition module is used to write the user table definition information in the project using the syntax of flinksql to read the user table in Mysql and synchronize it to the NCU general database in real time;

[0045] The running module is used to package the project into a jar and run it in the flink environment.

[0046] In order to achieve the third objective above, the present invention provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the method for synchronizing data to a Nanjing University general database based on Flink is implemented.

[0047] In order to achieve the above-mentioned fourth objective, the present invention provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the above-mentioned method of synchronizing data to a Nanjing University general database based on Flink is implemented.

[0048] Beneficial Effects

[0049] Compared with the prior art, the present invention has the following advantages:

[0050] The present invention makes targeted modifications to the Flink JDBC Connector to support the Nanjing University General Database dialect, so that data can be efficiently written in batches to the Nanjing University General Database through FlinkStream and SQL. This improvement improves the flexibility and efficiency of data processing and enhances the applicability of Flink in domestic database environments. Specific beneficial effects include:

[0051] 1. Enhance data processing capabilities: Realize real-time data integration and real-time data development of NCU general database data, improve data timeliness, and support batch and concurrent data writing to NCU general database.

[0052] 2. Improve independent controllability: Use NCU's general domestic database to reduce reliance on external storage technology and improve the controllability of system data.

[0053] 3. Promote the development of real-time domestic ecology: The effective integration of Flink and Nanjing University’s general database can promote the construction and development of the domestic information technology ecosystem.

[0054] 4. Improve system compatibility and stability: By adapting Flink to synchronize real-time data to the Nanjing University General Database, the dialect characteristics of the Nanjing University General Database can be better supported, thus improving the compatibility of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] Figure 1 is a flow chart of the present invention;

[0056] Figure 2 This is a flowchart of the logic of developing the flink-connector-jdbc-gbase.jar package in the present invention. DETAILED DESCRIPTION

[0057] The present invention will be further described below with reference to the specific embodiments in the accompanying drawings.

[0058] See also Figure 1-2 , a method for synchronizing data to a Nanda general database based on Flink, including steps 1 to 5:

[0059] Step 1. Create a new project, such as a Java Maven project, and add the Nanjing University General Database JDBC driver dependency package, the jar required by Flink, and the flink-connector-mysql-cdc package to the project.

[0060] Step 2. Get the source code of the flink-connector-jdbc project that needs to be modified, the source code is: flink-connector-jdbc-gbase.jar; and adapt flink-connector-jdbc.jar to the Nanjing University General Database. You can download the version code of flink-connector-jdbc that can adapt to the Nanjing University General Database from github. In this embodiment, the version code is not less than

[0061] Version 3.1 code of flink-connector-jdbc.

[0062] Step 3. Add the flink-connector-jdbc-gbase.jar dependency package modified to adapt to the Nanjing University General Database to the project.

[0063] Step 4. Use flinksql syntax in the project to read the user table in Mysql and synchronize it to the user table definition information of Nanjing University General Database in real time.

[0064] Step 5. Package the project into a jar and run it in the flink environment to enable efficient batch writing of data to the NCU general database through FlinkStream and SQL.

[0065] Furthermore, the transformation based on the flink-connector-jdbc source code includes steps 2.1 to 2.5:

[0066] Step 2.1. Add the Maven dependency package of NTU General Database and set the scope to provided.

[0067] Step 2.2. Create a new GbaseDialectFactory class to implement the JdbcDialectFactory interface and create the dialect implementation class for the Nanjing University General Database. Specifically, rewrite the acceptsURL method of JdbcDialectFactory to determine whether the prefix of the url path starts with jdbc:gbase, and use the url path as the identifier of the current dialect factory, rewrite the create method, and use the dialect factory to create the Nanjing University General Database dialect implementation class GbaseDialect.

[0068] Step 2.3. Add to resouces / META-INF.servies in the flink-connector-jdbc project source code

[0069] org.apache.flink.connector.jdbc.databases.gbase.dialect.GbaseDialectFactory, to create a JdbcDialectFactory in spi mode.

[0070] Step 2.4. Create a new GbaseRowConverter class to serialize and deserialize row data. The GbaseRowConverter class inherits from the AbstractJdbcRowConverter abstract class. The GbaseRowConverter class is used to define the logic of converting the field values ​​of the Nanjing University General Database into the field values ​​inside Flink and the logic of converting the field values ​​of Flink into the field values ​​outside the Nanjing University General Database. Specifically, it includes steps 2.4.1 to 2.4.3:

[0071] Step 2.4.1. Create a new converterName method that implements AbstractJdbcRowConverter and uses it as the identifier of the NCU general database type converter.

[0072] Step 2.4.2. Create a createInternalConverter method that implements AbstractJdbcRowConverter and define the mapping relationship between the values ​​of each field type of the Nanjing University General Database type and the field type values ​​inside Flink.

[0073] Step 2.4.3. Create a createExternalConverter method that implements AbstractJdbcRowConverter, and define the mapping relationship between how to convert the values ​​of each field type in Flink into the field type values ​​of the Nanjing University general database type.

[0074] Step 2.5. Create a new GbaseDialect implementation class. The GbaseDialect implementation class inherits from the AbstractDialect abstract class. The GbaseDialect implementation class is used to define the preset SQL statements for insert, update, upsert, and delete. Specifically, it includes steps 2.5.1 to 2.5.11:

[0075] Step 2.5.1. Create a new implementation of the getRowConverter method to return a GbaseRowConverter instance for converting row data values ​​in the current dialect.

[0076] Step 2.5.2. Create a new implementation of the getLimitClause method to return the SQL statement fragment for data interception.

[0077] Step 2.5.3. Create a new implementation of the defaultDriverName method to return the full class name of the current Nanda General driver class, which is used to load the driver class when operating the Nanda General database.

[0078] Step 2.5.4. Create a new implementation of the dialectName method to set it to the name of the current dialect identified by gbase.

[0079] Step 2.5.5. Create a new implementation of the supportedTypes method to set the Flink field types supported by the Nanjing University General Database.

[0080] Step 2.5.6. Create a new implementation of the getUpsertStatement method to return the SQL statement of the upsert statement of the Nanjing University General Database based on the table name and field information provided by Flink.

[0081] Step 2.5.7. Create a new implementation of the getRowExistsStatement method, which is used to return the SQL statement statement to query whether there is a data row in the Nanjing University General Database based on the table name and condition information provided by Flink.

[0082] Step 2.5.8. Create a new implementation of the getInsertIntoStatement method to return the SQL statement for inserting data into the Nanjing University General Database based on the table name and field information provided by Flink.

[0083] Step 2.5.9. Create a new implementation of the getUpdateStatement method, which is used to return the SQL statement statement for updating data in the Nanjing University General Database based on the table name, field information, and condition information provided by Flink.

[0084] Step 2.5.10. Create a new getDeleteStatement method to return the SQL statement for deleting data rows in the Nanjing University General Database based on the table name and condition information provided by Flink.

[0085] Step 2.5.11. Create a new implementation of the getSelectFromStatement method, which is used to return the SQL statement declaration of the query data row of the Nanjing University General Database based on the table name, condition information, and projection information provided by Flink.

[0086] A system for synchronizing data to a NCU general database based on Flink, including:

[0087] Create a new project module, which is used to create a new project. Add the JDBC driver dependency package of Nanjing University General Database, the jar required by Flink, and the flink-connector-mysql-cdc package to the project.

[0088] The transformation module is used to obtain the source code of the flink-connector-jdbc project that needs to be modified. The source code is: flink-connector-jdbc-gbase.jar; and flink-connector-jdbc.jar is adapted to the Nanjing University general database.

[0089] Add a module to add the flink-connector-jdbc-gbase.jar dependency package modified to adapt to the Nanjing University General Database to the project;

[0090] The reading definition module is used to use the syntax of flinksql in the project to read the user table definition information in Mysql and synchronize it to the user table definition information of Nanjing University General Database in real time;

[0091] The running module is used to package the project into a jar and run it in the flink environment.

[0092] A computer device includes a memory and a processor, wherein the memory stores a computer program, and the processor implements the above-mentioned method of synchronizing data to a Nanjing University general database based on Flink when executing the computer program.

[0093] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the above-mentioned method for synchronizing data to a Nanjing University general database based on Flink.

[0094] The technical point of the present invention is to adapt the FlinkJDBC connector to the Nanjing University General Dialect, so as to realize Flink's real-time, efficient and stable real-time processing of the Nanjing University General Database, thereby meeting the needs of real-time development of the Nanjing University General Domestic Database in the big data environment and improving the security and controllability of the data.

[0095] The above are only preferred embodiments of the present invention. It should be pointed out that for those skilled in the art, several modifications and improvements can be made without departing from the structure of the present invention, which will not affect the effect of the implementation of the present invention and the practicality of the patent.

Claims

1. A method for synchronizing data to a Nanjing University general database based on Flink, characterized in that: The steps include: Step 1. Create a new project and add the JDBC driver dependency package of Nanjing University General Database, the jar required by Flink, and the flink-connector-mysql-cdc package to the project; Step 2. Obtain the source code of the flink-connector-jdbc project that needs to be modified, which is flink-connector-jdbc-gbase.jar; and adapt flink-connector-jdbc.jar to the Nanjing University General Database; Step 3. Add the flink-connector-jdbc-gbase.jar dependency package modified to adapt to the Nanjing University General Database to the project; Step 4. Use flinksql syntax in the project to write the user table definition information that will be read from the user table in Mysql and synchronized to the NCU general database in real time; Step 5. Package the project into a jar and run it in the flink environment.

2. The method for synchronizing data to the NCU general database based on Flink according to claim 1 is characterized in that: Download flink-connector-jdbc from github to adapt to the version code of Nanjing University General Database transformation.

3. The method for synchronizing data to the NCU general database based on Flink according to claim 2 is characterized in that: The version code is not lower than version 3.1 of flink-connector-jdbc.

4. The method for synchronizing data to the NCU general database based on Flink according to claim 1 is characterized in that: The transformation based on flink-connector-jdbc source code includes: Step 2.

1. Add the Maven dependency package of NCU General Database and set the scope to provided; Step 2.

2. Create a new GbaseDialectFactory class to implement the JdbcDialectFactory interface and create a dialect implementation class for the Nanjing University General Database; Step 2.

3. Add to resouces / META-INF.servies in the flink-connector-jdbc project source code org.apache.flink.connector.jdbc.databases.gbase.dialect.GbaseDialectFactory, to create JdbcDialectFactory in spi mode; Step 2.

4. Create a new GbaseRowConverter class to serialize and deserialize row data. The GbaseRowConverter class inherits from the AbstractJdbcRowConverter abstract class. The GbaseRowConverter class is used to define the logic of converting the field values ​​of the Nanjing University General Database into the field values ​​inside Flink and the logic of converting the field values ​​of Flink into the field values ​​outside the Nanjing University General Database. Step 2.

5. Create a new GbaseDialect implementation class. The GbaseDialect implementation class inherits from the AbstractDialect abstract class. The GbaseDialect implementation class is used to define the preset SQL statements for insert, update, upsert, and delete SQL statements.

5. The method for synchronizing data to the NCU general database based on Flink according to claim 4 is characterized in that: In step 2.2, rewrite the acceptsURL method of JdbcDialectFactory to determine whether the prefix of the url path starts with jdbc:gbase, and use the url path as the identifier of the current dialect factory. Rewrite the create method and use the dialect factory to create the Nanjing University General Database Dialect implementation class GbaseDialect.

6. The method for synchronizing data to the NCU general database based on Flink according to claim 5 is characterized in that: Step 2.4 includes: Step 2.4.

1. Create a new converterName method that implements AbstractJdbcRowConverter and uses it as the identifier of the NCU general database type converter; Step 2.4.

2. Create a createInternalConverter method that implements AbstractJdbcRowConverter and define how to convert the values ​​of each field type of the Nanjing University general database type into the mapping relationship of the field type value inside Flink; Step 2.4.

3. Create a createExternalConverter method that implements AbstractJdbcRowConverter, and define the mapping relationship between how to convert the values ​​of each field type in Flink into the field type values ​​of the Nanjing University general database type.

7. The method for synchronizing data to the NCU general database based on Flink according to claim 6 is characterized in that: Step 2.5 includes: Step 2.5.

1. Create a new implementation of the getRowConverter method to return a GbaseRowConverter instance for converting row data values ​​in the current dialect; Step 2.5.

2. Create a new implementation of the getLimitClause method to return the SQL statement fragment of the data interception; Step 2.5.

3. Create a new implementation of the defaultDriverName method to return the full class name of the current Nanda General driver class, which is used to load the driver class when operating the Nanda General database; Step 2.5.

4. Create a new dialectName method to set it to the name of the current dialect identified by gbase; Step 2.5.

5. Create a new supportedTypes method to set the Flink field type supported by Nanda General Database; Step 2.5.

6. Create a new implementation of the getUpsertStatement method to return the SQL statement of the upsert statement of the Nanjing University General Database based on the table name and field information provided by Flink; Step 2.5.

7. Create a new implementation of the getRowExistsStatement method, which is used to return the SQL statement statement of whether there is a data row in the Nanjing University General Database based on the table name and condition information provided by Flink; Step 2.5.

8. Create a new implementation of the getInsertIntoStatement method to return the SQL statement statement for inserting data into the NCU General Database based on the table name and field information provided by Flink; Step 2.5.

9. Create a new implementation of the getUpdateStatement method, which is used to return the SQL statement statement for updating data of the Nanjing University General Database based on the table name, field information and condition information provided by Flink; Step 2.5.

10. Create a new getDeleteStatement method to return the SQL statement for deleting data rows in the Nanjing University General Database based on the table name and condition information provided by Flink; Step 2.5.

11. Create a new implementation of the getSelectFromStatement method, which is used to return the SQL statement declaration of the query data row of the Nanjing University General Database based on the table name, condition information, and projection information provided by Flink.

8. A system for synchronizing data to NCU general database based on Flink, characterized in that: include: The project creation module is used to create a new project. In the project, add the JDBC driver dependency package of Nanjing University General Database, the jar required by Flink, and the flink-connector-mysql-cdc package; The transformation module is used to obtain the source code of the flink-connector-jdbc project that needs to be modified. The source code is: flink-connector-jdbc-gbase.jar; and flink-connector-jdbc.jar is adapted to the Nanjing University general database. Add a module to add the flink-connector-jdbc-gbase.jar dependency package modified to adapt to the Nanjing University general database to the project; The reading definition module is used to write the user table definition information in the project using the syntax of flinksql to read the user table in Mysql and synchronize it to the NCU general database in real time; The running module is used to package the project into a jar and run it in the flink environment.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the method for synchronizing data to the Nanjing University general database based on Flink according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for synchronizing data to the Nanjing University general database based on Flink according to any one of claims 1 to 7 is implemented.

Images