Method and device for dynamically constructing SQL statement based on flink cdc

Abstract

The application provides a kind of SQL sentence dynamic construction method and device based on FlinkCDC, applied in deserializer, comprising: receiving the acquisition data sent by collector, and parsing the acquisition data into general data;Based on the type of write-in library, obtain corresponding type parser and SQL builder;Based on field type, use type parser to type conversion on the field value before change and / or field value after change in general data, obtain the converted field value before change and / or field value after change;Sentence operation type in general data, and the field value before change and / or field value after change after type conversion are input into SQL builder, and SQL sentence that can be normally executed in write-in library is constructed.The application can automatically type conversion and restore executable SQL according to different write-in library types, solve the problem of data conversion failure or precision loss, and has good expansibility and reusability.

Description

Technical Field

[0001] This application relates to the field of data synchronization technology, and in particular to a method and apparatus for dynamically constructing SQL statements based on FlinkCDC. Background Technology

[0002] With increasingly stringent requirements for information technology development, the value of data is rising in public service systems such as the healthcare ecosystem. To break down data silos and ensure timely data synchronization, FlinkCDC is used to achieve real-time database synchronization, build a data warehouse, and provide data support for other systems. FlinkCDC mainly consists of three parts: a source, a deserializer, and a sink. Existing FlinkCDC functionality is quite basic, only parsing Oracle data from logs without performing database write operations. Furthermore, different databases have diverse data types, and automatically reconstructing SQL based on database type is a key goal of data synchronization. Existing solutions involve data transformation and SQL construction within the sink. This approach requires significant time to build tasks during large-scale data synchronization operations, and the resulting tasks have little reusability. Especially in hospital data synchronization scenarios with numerous databases, tables, and fields, this solution further prolongs task construction time. Summary of the Invention

[0003] To address the aforementioned technical problems, this invention provides a method and apparatus for dynamically constructing SQL statements based on FlinkCDC. This method can automatically perform type conversion and restore executable SQL according to different write database types, solving the problems of data conversion failure or precision loss, and has good scalability and reusability.

[0004] The present invention adopts the following technical solution:

[0005] A method for dynamically constructing SQL statements based on FlinkCDC, applied in a deserializer, includes:

[0006] Receive the collected data sent by the collector and parse the collected data into general data;

[0007] Retrieve the corresponding type resolver and SQL builder based on the type of the library being written;

[0008] Based on the field type, a type resolver is used to perform type conversion on the field values ​​before and / or after the change in the general data to obtain the converted field values ​​before and / or after the change.

[0009] Input the statement operation type from the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database.

[0010] Preferably, based on the field type, a type resolver is used to perform type conversion on the pre-change and / or post-change field values ​​in the general data, specifically including:

[0011] Establish a connection with the write library based on the write library type;

[0012] The system retrieves the tables that need to be synchronized, reads the field types and field names from the tables in the database, and stores them using structure information. The structure information includes the database, table, field names, and field types in the database.

[0013] Retrieve the database, table, and field names from the general data; traverse the structure information to obtain the matching field names written to the database; based on the matching field names, obtain the corresponding field types from the structure information.

[0014] Input the obtained field types into the type resolver corresponding to the database type;

[0015] Based on the input field type, perform type conversion on the pre-change and / or post-change field values ​​in the general data.

[0016] Preferably, the type resolver is configured with multiple type conversion functions that match the field type;

[0017] The type conversion of pre-change and / or post-change field values ​​in general data based on the input field type specifically includes:

[0018] Based on the type of the input field, the corresponding type conversion function is matched in the type resolver;

[0019] Type conversion functions are used to convert the values ​​of fields before and / or after the change in general data to obtain the converted values ​​of fields before and / or after the change.

[0020] Preferably, the type of database to be written and the tables to be synchronized are both configured through a JSON file in the synchronization task; the username, password, and connection method of the database to be written are also configured through a JSON file in the synchronization task.

[0021] Preferably, the step of inputting the statement operation type from the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database, specifically includes:

[0022] Retrieve the statement operation type from general data;

[0023] Retrieve the field values ​​before and / or after the change, after conversion by the type resolver;

[0024] Input the statement operation type and the converted field values ​​before and / or after the change into the SQL builder to construct different SQL statements based on different statement operation types.

[0025] Preferably, constructing different SQL statements based on different statement operation types specifically includes:

[0026] If the statement operation type is an add operation, construct the add statement based on the transformed and modified field values;

[0027] If the statement operation type is a modification operation, construct the update statement based on the converted field values ​​before and / or the field values ​​after the change;

[0028] If the statement operation type is delete, the delete statement is constructed based on the converted values ​​of the fields before the change.

[0029] Preferably, after constructing the SQL statement that can be executed normally in the database, the method further includes:

[0030] The SQL statement is sent to the writer and executed in the write database.

[0031] Secondly, a dynamic SQL statement construction device based on FlinkCDC, applied in a deserializer, includes:

[0032] The data parsing module is used to receive the collected data sent by the collector and parse the collected data into general data.

[0033] The retrieval module is used to obtain the corresponding type resolver and SQL builder based on the type of the written library;

[0034] The type resolver processing module is used to perform type conversion on the pre-change field values ​​and / or post-change field values ​​in general data based on the field type, and obtain the converted pre-change field values ​​and / or post-change field values;

[0035] The SQL builder processing module is used to input the statement operation type in the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database.

[0036] Thirdly, a computer device includes at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to execute the SQL statement dynamic construction method.

[0037] Fourthly, a computer-readable storage medium storing a computer program that causes a computer to execute in order to implement the described method for dynamically constructing SQL statements.

[0038] The present invention has the following beneficial effects:

[0039] This invention can automatically perform type conversion and restore executable SQL according to different write database types defined in the synchronization task, solving the problem of data conversion failure or precision loss. It also has good scalability and reusability, greatly improving the speed at which developers build synchronization tasks.

[0040] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Attached Figure Description

[0041] Figure 1 This is a flowchart of the method for dynamically constructing SQL statements based on FlinkCDC according to an embodiment of the present invention;

[0042] Figure 2 This is a flowchart illustrating the structure of a deserializer according to an embodiment of the present invention.

[0043] Figure 3 This is a flowchart of FlinkCDC according to an embodiment of the present invention;

[0044] Figure 4 This is a flowchart illustrating the type conversion process according to an embodiment of the present invention;

[0045] Figure 5 This is a schematic diagram illustrating the structure of the write library according to an embodiment of the present invention;

[0046] Figure 6 This is a schematic diagram of the type conversion function according to an embodiment of the present invention;

[0047] Figure 7 This is a schematic diagram of JSON file configuration according to an embodiment of the present invention;

[0048] Figure 8 This is a flowchart illustrating the SQL construction process of an embodiment of the present invention;

[0049] Figure 9 This is a structural block diagram of the FlinkCDC-based SQL statement dynamic construction device according to an embodiment of the present invention;

[0050] Figure 10 This is a schematic diagram of the structure of a computer device according to an embodiment of the present invention. Detailed Implementation

[0051] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0052] In the description of this invention, it should be noted that the terms "comprising," "including," or any other variations thereof are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0053] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the step identifiers S101, S102, S103, etc. are only used for convenience of description and do not indicate the execution order. The corresponding execution order can be adjusted as needed.

[0054] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0055] See Figure 1 and Figure 2 As shown in the figure, this embodiment presents a method for dynamically constructing SQL statements based on FlinkCDC, applied in a deserializer, including:

[0056] S101 receives the collected data sent by the collector and parses the collected data into general data;

[0057] S102, obtain the corresponding type resolver and SQL builder based on the type of the written library;

[0058] S103, Based on the field type, use a type resolver to perform type conversion on the field values ​​before and / or after the change in the general data to obtain the converted field values ​​before and / or after the change;

[0059] S104. Input the statement operation type in the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database.

[0060] See Figure 3 As shown, the three main components involved in the FlinkCDC framework are the Source, Deserializer, and Sink. After collecting data, the Source passes the raw data to the Deserializer for parsing, which extracts a general format, performs type conversion based on the type of the writing library, generates the corresponding SQL statement, and delivers the general format data to the Sink. The Sink then receives and executes the SQL statement.

[0061] In this embodiment, the type parsing and dynamic SQL construction operations are mainly performed in the deserializer. In the default deserializer, general data can be obtained, including the database type, the hierarchical information of the collected fields in the database from top to bottom (taking Oracle as an example, the hierarchical relationship from top to bottom is: Schema, Table, Field), as well as the values ​​of the fields before and / or after the changes, and the statement operation type, etc.

[0062] It should be noted that if the statement operation type is "add", the general data will only include the value of the field after the change, and the value before the change can be set to empty; if the statement operation type is "delete", the general data will only include the value of the field before the change, and the value after the change can be set to empty; if the statement operation type is "modify", the general data will include the values ​​of the field before and after the change.

[0063] In general data, the data structure of field names is as follows.

[0064]

[0065] See Figure 4 As shown, based on the field type, a type resolver is used to perform type conversion on the pre-change and / or post-change field values ​​in the general data, specifically including:

[0066] Establish a connection with the write library based on the write library type;

[0067] The system retrieves the tables that need to be synchronized, reads the field types and field names from the tables in the database, and stores them using structure information. The structure information includes the database, table, field names, and field types in the database.

[0068] Retrieve the database, table, and field names from the general data; traverse the structure information to obtain the matching field names written to the database; based on the matching field names, obtain the corresponding field types from the structure information.

[0069] Input the obtained field types into the type resolver corresponding to the database type;

[0070] Based on the input field type, perform type conversion on the pre-change and / or post-change field values ​​in the general data.

[0071] For details, see Figure 5 As shown, the structure information can be saved in the structure of "database: table: field name: field type".

[0072] Since the data types in the source database may differ from those in the writing database, it is necessary to match the general data with the field information read from the writing database and convert it into the format required by the writing database. For example, converting timestamps into date types and strings into numeric types.

[0073] In this embodiment, each database type has a dedicated type resolver, and different resolvers are obtained according to different database types during the process.

[0074] In this embodiment, the type resolver is configured with multiple type conversion functions that match the field type;

[0075] The type conversion of pre-change and / or post-change field values ​​in general data based on the input field type specifically includes:

[0076] Based on the type of the input field, the corresponding type conversion function is matched in the type resolver;

[0077] Type conversion functions are used to convert the values ​​of fields before and / or after the change in general data to obtain the converted values ​​of fields before and / or after the change.

[0078] For details, see Figure 6 As shown, taking the Data converter as an example, when the field type matches the converter, it will... Figure 5 The field type shown is subjected to a match() operation. If the match is successful, the field value will be put into toSqlValue() and the converted data value will be obtained. Figure 5 In the example, the fourth-to-last field, UPDATE_TIME, has a data type of DATE. After obtaining the Date type field, it will match the field name with the type conversion function in the defined type resolver based on the keyword "DATE". The Date enumeration has a property called "typeName", and this "DATE" type will be matched with this typeName in the match method.

[0079] In this embodiment, the type of database to be written and the tables to be synchronized are both configured through a JSON file in the synchronization task; the username, password, and connection method of the database to be written are also configured through a JSON file in the synchronization task.

[0080] For details, see Figure 7 As shown, this is the JSON file for creating the task. Within the `sink` field of the JSON file, there's a `sinkType` type. When the synchronization task starts, this `sinkType` is used to determine the database type. The `schemaList` and `tableList` fields in the `source` field specify the database and tables to be collected. Furthermore, the username, password, and connection method for writing to the database can also be defined in the same JSON file, such as the `username`, `password`, and `url` fields in the `sink` field.

[0081] See Figure 8 As shown, the step of inputting the statement operation type from the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database, specifically includes:

[0082] Retrieve the statement operation type from general data;

[0083] Retrieve the field values ​​before and / or after the change, after conversion by the type resolver;

[0084] Input the statement operation type and the converted field values ​​before and / or after the change into the SQL builder to construct different SQL statements based on different statement operation types.

[0085] Different databases may have different SQL syntaxes. This embodiment creates a SQL builder for each database type to distinguish the syntax. The process will select a different SQL builder based on the different database types.

[0086] A SQL builder is constructed for each database type. The SQL builder then constructs SQL statements according to the syntax of different databases, supplemented by the values ​​of the fields before and after the changes. To ensure that the SQL executes correctly, the values ​​before and after the changes must be converted by a type resolver.

[0087] In this embodiment, constructing different SQL statements based on different statement operation types specifically includes:

[0088] If the statement operation type is an add operation, construct the add statement based on the transformed and modified field values;

[0089] If the statement operation type is a modification operation, construct the update statement based on the converted field values ​​before and / or the field values ​​after the change;

[0090] If the statement operation type is delete, the delete statement is constructed based on the converted values ​​of the fields before the change.

[0091] Since the general data structure collected by FlinkCDC does not contain database index information or primary key information, the old data can only be used as a condition for updating and deleting. The following example of the general data will be used to illustrate the construction of add, modify and delete statements.

[0092]

[0093] The newly constructed SQL statement is as follows:

[0094] INSERT INTO table_name(fieldA,fieldB) VALUES(modifiedA,modifiedB);

[0095] The constructed modification SQL statement is as follows:

[0096] Update table_name set fieldA = "post-changeA", fieldB = "post-changeB" where fieldA = "previousA" and fieldB = "post-changeB"

[0097] B = B before the change;

[0098] The constructed delete statement is as follows:

[0099] Delete from table_name where fieldA = previous A and fieldB = previous B.

[0100] Here we can see that both deletion and modification use the old data of the field as the query condition, and the logic for generating the query condition is consistent.

[0101] Therefore, this embodiment abandons the manual conversion method and extends the deserializer to automatically capture field types based on the database type and automatically convert different field types. Simultaneously with field type conversion, it selects different SQL builders based on the database type to construct SQL statements that can be executed correctly in each database. This improves the reusability of the writer and further increases the construction speed of data synchronization tasks.

[0102] See Figure 9 As shown, this embodiment also discloses a dynamic SQL statement construction device based on FlinkCDC, applied in a deserializer, including:

[0103] The data parsing module 901 is used to receive the collected data sent by the collector and parse the collected data into general data;

[0104] Module 902 is used to obtain the corresponding type resolver and SQL builder based on the type of the database being written.

[0105] The type resolver processing module 903 is used to perform type conversion on the field values ​​before and / or after the change in general data based on the field type, using a type resolver to obtain the converted field values ​​before and / or after the change.

[0106] The SQL builder processing module 904 is used to input the statement operation type in the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database.

[0107] The specific implementation of each module of the FlinkCDC-based SQL statement dynamic construction device is the same as the FlinkCDC-based SQL statement dynamic construction method, and will not be described again in this embodiment.

[0108] See Figure 10 As shown, this embodiment also discloses a computer device, including at least one processor 1002; and a memory 1001 communicatively connected to the at least one processor 1002; wherein the memory 1001 stores a computer program that can be executed by the at least one processor 1002, and the computer program is executed by the at least one processor 1002 to enable the at least one processor 1002 to execute the SQL statement dynamic construction method.

[0109] The computer device can be a personal computer (PC), laptop computer, or smartphone, and can also be an all-in-one computer, PDA, tablet computer, smart TV playback terminal, in-vehicle terminal, or other portable device. For PC-based computer devices, such as all-in-one computers, the operating system can include, but is not limited to, Linux, Unix, Windows series systems (e.g., Windows XP, Windows 7), and Mac OS X (Apple's operating system). For mobile computer devices, such as smartphones, the operating system can include, but is not limited to, Android, iOS (Apple's mobile phone operating system), and Windows.

[0110] This application proposes a method for dynamically constructing SQL statements based on FlinkCDC, which can be executed by a FlinkCDC-based SQL statement dynamic construction device. This device can be implemented in software and / or hardware, and is generally integrated into a computer device.

[0111] This embodiment also discloses a computer-readable storage medium storing a computer program that causes a computer to execute in order to implement the described method for dynamically constructing SQL statements.

[0112] Furthermore, embodiments of this application also provide a computer program product, which includes a non-transitory computer-readable storage medium storing a computer program operable to cause a computer to execute some or all of the steps of any of the methods described in the method embodiments. The computer program product may be a software installation package, and the computer includes a computer device.

[0113] The above description is merely a preferred embodiment of the present invention; however, the scope of protection of the present invention is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in the present invention, based on the technical solution and its improved concepts, should be covered within the scope of protection of the present invention.

Claims

1. A method for dynamically constructing SQL statements based on FlinkCDC, characterized in that, Applications in deserializers include: Receive the collected data sent by the collector and parse the collected data into general data; Retrieve the corresponding type resolver and SQL builder based on the type of the library being written; Based on the field type, a type resolver is used to perform type conversion on the field values ​​before and / or after the change in the general data to obtain the converted field values ​​before and / or after the change. Input the statement operation type in the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database. Based on field type, a type resolver is used to perform type conversion on the pre-change and / or post-change field values ​​in general data, specifically including: Establish a connection with the write library based on the write library type; The system retrieves the tables that need to be synchronized, reads the field types and field names from the tables in the database, and stores them using structure information. The structure information includes the database, table, field names, and field types in the database. Retrieve the database, table, and field names from the general data; traverse the structure information to obtain the matching field names written to the database; based on the matching field names, obtain the corresponding field types from the structure information. Input the obtained field types into the type resolver corresponding to the database type; Based on the input field type, perform type conversion on the pre-change and / or post-change field values ​​in the general data.

2. The method for dynamically constructing SQL statements based on FlinkCDC according to claim 1, characterized in that, The type resolver is configured with multiple type conversion functions that match the field type; The type conversion of pre-change and / or post-change field values ​​in general data based on the input field type specifically includes: Based on the type of the input field, the corresponding type conversion function is matched in the type resolver; Type conversion functions are used to convert the values ​​of fields before and / or after the change in general data to obtain the converted values ​​of fields before and / or after the change.

3. The method for dynamically constructing SQL statements based on FlinkCDC according to claim 1, characterized in that, The database type to be written and the tables to be synchronized are configured through a JSON file in the synchronization task; the username, password, and connection method for the database to be written are also configured through a JSON file in the synchronization task.

4. The method for dynamically constructing SQL statements based on FlinkCDC according to claim 1, characterized in that, The step of inputting the statement operation type from the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database, specifically includes: Retrieve the statement operation type from general data; Retrieve the field values ​​before and / or after the change, after conversion by the type resolver; Input the statement operation type and the converted field values ​​before and / or after the change into the SQL builder to construct different SQL statements based on different statement operation types.

5. The method for dynamically constructing SQL statements based on FlinkCDC according to claim 4, characterized in that, The construction of different SQL statements based on different statement operation types specifically includes: If the statement operation type is an add operation, construct the add statement based on the transformed and modified field values; If the statement operation type is a modification operation, construct the update statement based on the converted field values ​​before and / or the field values ​​after the change; If the statement operation type is delete, the delete statement is constructed based on the converted values ​​of the fields before the change.

6. The method for dynamically constructing SQL statements based on FlinkCDC according to claim 1, characterized in that, After constructing the SQL statement that can be executed normally in the database, the process also includes: The SQL statement is sent to the writer and executed in the write database.

7. A dynamic SQL statement construction device based on FlinkCDC, characterized in that, Applications in deserializers include: The data parsing module is used to receive the collected data sent by the collector and parse the collected data into general data. The retrieval module is used to obtain the corresponding type resolver and SQL builder based on the type of the written library; The type resolver processing module is used to perform type conversion on the pre-change field values ​​and / or post-change field values ​​in general data based on the field type, and obtain the converted pre-change field values ​​and / or post-change field values; The SQL builder processing module is used to input the statement operation type in the general data, as well as the field values ​​before and / or after type conversion, into the SQL builder to construct an SQL statement that can be executed normally in the database. Based on field type, a type resolver is used to perform type conversion on the pre-change and / or post-change field values ​​in general data, specifically including: Establish a connection with the write library based on the write library type; The system retrieves the tables that need to be synchronized, reads the field types and field names from the tables in the database, and stores them using structure information. The structure information includes the database, table, field names, and field types in the database. Retrieve the database, table, and field names from the general data; traverse the structure information to obtain the matching field names written to the database; based on the matching field names, obtain the corresponding field types from the structure information. Input the obtained field types into the type resolver corresponding to the database type; Based on the input field type, perform type conversion on the pre-change and / or post-change field values ​​in the general data.

8. A computer device, characterized in that, The system includes at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to execute the SQL statement dynamic construction method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer programs. The computer program causes a computer to execute the method for dynamically constructing SQL statements according to any one of claims 1 to 6.

Images