CN117251470A

CN117251470A - SQL statement dynamic construction method and device based on FlinkCDC

Info

Publication number: CN117251470A
Application number: CN202311231544.4A
Authority: CN
Inventors: 郑建辉; 邱启钰; 曾锦熹; 赖滢; 李程扬
Original assignee: XIAMEN ZHIYE SOFTWARE ENGINEERING CO LTD
Current assignee: XIAMEN ZHIYE SOFTWARE ENGINEERING CO LTD
Priority date: 2023-09-22
Filing date: 2023-09-22
Publication date: 2023-12-19

Abstract

The invention provides a SQL statement dynamic construction method and device based on FlinkCDC, which are applied to an anti-serializer and comprise the following steps: receiving acquisition data sent by an acquisition device, and analyzing the acquisition data into general data; acquiring a corresponding type analyzer and an SQL constructor based on the write-in type; based on the field type, performing type conversion on the field value before the change and/or the field value after the change in the general data by using a type analyzer to obtain the field value before the change and/or the field value after the change after the conversion; and inputting the statement operation type in the general data and the field value before and/or the field value after the change after the type conversion into an SQL constructor to construct an SQL statement which can be normally executed in writing and warehousing. The invention can automatically perform type conversion and restore executable SQL according to different write-in types, solves the problem of data conversion failure or precision loss, and has better expansibility and reusability.

Description

SQL statement dynamic construction method and device based on FlinkCDC

Technical Field

The application relates to the technical field of data synchronization, in particular to a method and a device for dynamically constructing SQL sentences based on FlinkCDC.

Background

Along with the increasing requirement of informatization development, the data value is also becoming higher and higher in the ecology of public service systems such as medical systems, and in order to break the island effect of data and ensure the timeliness of data synchronization, the FlinkCDC is adopted to realize the real-time synchronization of databases, and a data warehouse is constructed to further provide data support for other systems. The main three parts of the FlinkCDC are collector (Source), deserializer (Deserizer) and writer (Sink). The existing flankcdc function is quite pure, only the action of analyzing Oracle data through a log is performed, the operation of writing into a database is not performed, the data types of different databases are various, and how to automatically restore SQL according to the database types is a target for data synchronization pursuit. The existing solution is to perform data conversion and SQL construction in a writer, and this method needs to take a lot of time to construct tasks when performing a lot of data synchronization operations, and the constructed tasks have little reusability. Especially in the scene of hospital data synchronization, the solution further prolongs the construction time of tasks for application scenes with more libraries, more tables and more fields.

Disclosure of Invention

In order to solve the technical problems, the invention provides the SQL statement dynamic construction method and the device based on the FlinkCDC, which can automatically perform type conversion and restore executable SQL according to different write-in types (write-in database types), solve the problems of data conversion failure or precision loss, and have better expansibility and reusability.

The invention adopts the following technical scheme:

a SQL statement dynamic construction method based on FlinkCDC is applied to an anti-serializer and comprises the following steps:

receiving acquisition data sent by an acquisition device, and analyzing the acquisition data into general data;

acquiring a corresponding type analyzer and an SQL constructor based on the write-in type;

based on the field type, performing type conversion on the field value before the change and/or the field value after the change in the general data by using a type analyzer to obtain the field value before the change and/or the field value after the change after the conversion;

and inputting the statement operation type in the general data and the field value before and/or the field value after the change after the type conversion into an SQL constructor to construct an SQL statement which can be normally executed in writing and warehousing.

Preferably, based on the field type, using a type parser to perform type conversion on the field value before change and/or the field value after change in the general data, specifically including:

based on the write-in type, establishing connection with write-in;

acquiring a table to be synchronized, reading field types and field names in the table in a write-in library, and storing the field types and the field names through structural information; the structure information comprises a database, a data table, a field name and a field type written in a warehouse;

acquiring a database, a data table and field names in the general data, traversing the structure information, and acquiring the matched field names written in the warehouse; acquiring a corresponding field type in the structure information based on the matched field name;

inputting the acquired field type into a type analyzer corresponding to the database type;

and performing type conversion on the field value before change and/or the field value after change in the general data based on the input field type.

Preferably, the type parser is configured with a plurality of type conversion functions matched with the field types;

the type conversion of the field value before change and/or the field value after change in the general data based on the input field type specifically comprises:

based on the input field type, matching to a corresponding type conversion function in a type parser;

and performing type conversion on the field value before and/or the field value after the change in the general data through a type conversion function to obtain the field value before and/or the field value after the change after the conversion.

Preferably, the write-in type and the table to be synchronized are configured through JSON files in a synchronization task; the user name, password and connection mode of writing into the library are also configured through the JSON file in the synchronous task.

Preferably, the method includes inputting the statement operation type in the general data and the field value before and/or the field value after the change after the type conversion into an SQL constructor to construct an SQL statement that can be normally executed in writing and warehousing, and specifically includes:

acquiring statement operation types in the general data;

acquiring a field value before change and/or a field value after change after the conversion of the type analyzer;

inputting the sentence operation type and the converted field value before and/or field value after the change into an SQL constructor, and constructing different SQL sentences based on different sentence operation types.

Preferably, the constructing different SQL statements based on different statement operation types specifically includes:

if the sentence operation type is a new operation, constructing a new sentence based on the converted changed field value;

if the statement operation type is a modification operation, constructing an update statement based on the converted field value before and/or field value after modification;

if the statement operation type is delete, constructing a delete statement based on the converted pre-change field value.

Preferably, after the constructing the SQL statement that can be normally executed in the write-in library, the method further includes:

the SQL statement is sent to the writer, and the SQL statement is executed in the write library.

In a second aspect, an SQL statement dynamic construction device based on FlinkCDC, applied in a deserializer, includes:

the data analysis module is used for receiving the collected data sent by the collector and analyzing the collected data into general data;

the acquisition module is used for acquiring the corresponding type analyzer and SQL constructor based on the write-in type;

the type analyzer processing module is used for performing type conversion on the field value before the change and/or the field value after the change in the general data by using the type analyzer based on the field type to obtain the field value before the change and/or the field value after the change after the conversion;

and the SQL constructor processing module is used for inputting the statement operation type in the general data and the field value before and/or the field value after the change after the type conversion into the SQL constructor to construct the SQL statement which can be normally executed in writing and warehousing.

In a third aspect, a computer device includes at least one processor; and a memory communicatively coupled to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executable by the at least one processor to enable the at least one processor to perform the SQL statement dynamic construction method.

In a fourth aspect, a computer-readable storage medium stores a computer program that causes a computer to execute to implement the SQL statement dynamic construction method.

The invention has the following beneficial effects:

the method and the device can automatically perform type conversion and restore the executable SQL according to different write-in types (write-in database types) defined in the synchronous task, solve the problem of data conversion failure or precision loss, have good expansibility and reusability, and greatly improve the speed of constructing the synchronous task by developers.

In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that are needed in the description of the embodiments will be briefly described below, and it is obvious that the drawings described below are only some embodiments of the present invention, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

Drawings

FIG. 1 is a flow chart of a method for dynamically constructing SQL statements based on the FlinkCDC in an embodiment of the invention;

FIG. 2 is a block diagram of an embodiment of an anti-serializer;

FIG. 3 is a flow chart diagram of a FlinkCDC of an embodiment of the present invention;

FIG. 4 is a type conversion flow chart of an embodiment of the present invention;

FIG. 5 is a schematic diagram of the structure information of a write library according to an embodiment of the present invention;

FIG. 6 is a schematic diagram of a type conversion function according to an embodiment of the present invention;

FIG. 7 is a schematic diagram of a JSON file configuration according to an embodiment of the present invention;

FIG. 8 is a flow chart of SQL construction according to an embodiment of the invention;

FIG. 9 is a block diagram of a dynamic SQL statement construction device based on the FlinkCDC according to an embodiment of the invention;

fig. 10 is a schematic structural diagram of a computer device according to an embodiment of the present invention.

Detailed Description

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention; it is apparent that the described embodiments are only some embodiments of the present invention, not all embodiments, and that all other embodiments obtained by persons of ordinary skill in the art without making creative efforts based on the embodiments in the present invention are within the protection scope of the present invention.

In the description of the present invention, it should be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.

In the description of the present invention, unless explicitly stated and defined otherwise, step identifiers S101, S102, S103, etc. are used for convenience of description only, and do not represent an execution sequence, and the corresponding execution sequence may be adjusted as needed.

Reference herein to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment may be included in at least one embodiment of the present application. The appearances of such phrases in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. Those of skill in the art will explicitly and implicitly appreciate that the embodiments described herein may be combined with other embodiments.

Referring to fig. 1 and fig. 2, the method for dynamically constructing an SQL statement based on the FlinkCDC in this embodiment is applied to an deserializer, and includes:

s101, receiving collected data sent by a collector, and analyzing the collected data into general data;

s102, acquiring a corresponding type analyzer and an SQL constructor based on the write-in type;

s103, based on the field type, performing type conversion on the field value before and/or the field value after the change in the general data by using a type analyzer to obtain the field value before and/or the field value after the change after the conversion;

s104, inputting the statement operation type in the general data and the field value before change and/or the field value after change after type conversion into an SQL constructor to construct an SQL statement which can be normally executed in writing and warehousing.

Referring to FIG. 3, the main three components involved in the FlinkCDC framework are collector (Source), deserializer (Deserizer), writer (Sink). After the collector collects the data, the original data is transmitted to the deserializer for analysis, the data is analyzed into a general format, type conversion is carried out according to the writing warehouse-in type, a corresponding SQL sentence is generated, the general format data is delivered to the writer, and the writer obtains the SQL sentence and executes the SQL sentence.

The operations of type parsing and dynamic SQL construction in this embodiment are mainly performed in the inverse sequencer, and in the default inverse sequencer, general data may be obtained, where the general data includes a database type, top-to-bottom hierarchical information of collection fields in the database (in the example of Oracle, the hierarchical relationship is Schema, table, field from top to bottom), values of fields before and/or after modification, and statement operation types.

Here, it should be noted that, if the statement operation type is increased, the general data includes only the value of the field after the change, and the value before the change may be set to be null; if the statement operation type is delete, the general data only comprises the value of the field before the change, and the value after the change can be set as null; if the statement operation type is modified, the value of the field before and after the change is included in the general data.

In the general data, the data structure of the field name is as follows.

Referring to fig. 4, based on the field type, the type parser is used to perform type conversion on the field value before change and/or the field value after change in the general data, which specifically includes:

based on the write-in type, establishing connection with write-in;

Specifically, referring to fig. 5, the structure information may be "database: data table: field name: the structure of the field type "is saved.

Because the data types in the source library may be different from the write library, the generic data needs to be matched with the field information read from the write library and converted to the format required for the write library. For example, converting a time stamp to a date type, converting a character string to a numeric type, etc.

In this embodiment, each database type has a specific type resolver, and different resolvers are obtained according to different database types in the flow.

In this embodiment, the type parser is configured with a plurality of type conversion functions matched with the field types;

Specifically, referring to fig. 6, taking a Data converter as an example, when a field type matches with the converter, a match () operation is performed on the field type shown in fig. 5, and if the matching is successful, the field value is put into toSqlValue () to be called, so as to obtain the converted Data value. In fig. 5, the data type of the last-last field update_time is DATE, and after the field of the DATE type is obtained, the data type is matched with the type conversion function in the defined type parser according to the field name according to the keyword "DATE", and the DATE enumerates an attribute called "typeName", and the DATE type is matched with the typeName in the match method.

In this embodiment, the write-in type and the table to be synchronized are configured through JSON files in the synchronization task; the user name, password and connection mode of writing into the library are also configured through the JSON file in the synchronous task.

Specifically, referring to FIG. 7, this is a JSON file of the creation task. A sink type exists in the sink in the JSON of the creation task, and when the synchronous task is started, the database type is determined according to the sink type. The schemaList and tableList in source indicate the databases and tables to be collected. In addition, the user name, password and connection mode of writing into the library can be defined in the same JSON file, such as username, password and url in sink.

Referring to fig. 8, the method inputs the statement operation type in the general data, and the field value before change and/or the field value after change after type conversion into an SQL constructor to construct an SQL statement that can be normally executed in writing and warehousing, specifically includes:

acquiring statement operation types in the general data;

There will be a difference in the various database SQL grammars, and this embodiment creates an SQL builder for each database type to distinguish grammars. Different SQL constructs will be selected in the flow according to different database types.

And constructing an SQL constructor aiming at the database type, wherein the SQL constructor constructs an SQL sentence according to the grammar of different databases and assisted with the values before and after the change of the field. To ensure that SQL is performed correctly, the front and back values must be converted by a type parser.

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

Since the universal data structure collected by the flankcdc has no database index information and no primary key information, the universal data structure can only be constructed by using old data as a condition during updating and deleting, and the following description will be given of the addition, modification and deletion statement of the structure by taking the following issued universal data as an example.

The newly added SQL statement constructed is as follows:

insert intotable names (field a, field B) values (post-change a, post-change B);

the structured modified SQL statement is as follows:

update table name set field a=post-change a, field b=post-change bwhere field a=pre-change a and field

B=b before change;

the constructed delete statement is as follows:

delete from table name where field a=pre-change a and field b=pre-change B.

Here, it can be seen that both deletion and modification use the old data of the field as a query condition, and the generation of the query condition is logically consistent.

In this embodiment, the manual conversion method is abandoned, and the method is expanded on the basis of the inverse sequencer, so that the types of the fields can be automatically grasped through the database types, and the automatic conversion of different field types can be performed. And when the field types are converted, different SQL constructors are selected according to the database types, and SQL sentences which can be normally executed in each database are constructed. The reusability of the writer is improved, and the construction speed of the data synchronization task is further improved.

Referring to fig. 9, this embodiment also discloses an SQL statement dynamic construction device based on the FlinkCDC, which is applied in an deserializer, and includes:

the data analysis module 901 is configured to receive the collected data sent by the collector, and analyze the collected data into general data;

an obtaining module 902, configured to obtain a corresponding type resolver and SQL builder based on the write-in type;

the type parser processing module 903 is configured to perform type conversion on the field value before and/or the field value after the change in the general data by using the type parser based on the field type, so as to obtain a converted field value before and/or field value after the change;

the SQL builder processing module 904 is configured to input the statement operation type in the general data, and the field value before and/or the field value after the change after the type conversion into the SQL builder, and construct an SQL statement that can be normally executed in the write-in library.

The implementation of each module of the device for dynamically constructing the SQL sentence based on the FlinkCDC is the same as that of the SQL sentence based on the FlinkCDC, and the description of the embodiment is not repeated.

Referring to FIG. 10, the present embodiment also discloses a computer device comprising at least one processor 1002; and a memory 1001 communicatively coupled to the at least one processor 1002; wherein the memory 1001 stores a computer program executable by the at least one processor 1002, the computer program being executable by the at least one processor 1002 to enable the at least one processor 1002 to perform the SQL statement dynamic construction method.

The computer equipment can be a personal computer (personal computer, PC), a notebook computer or a smart phone, and can also be an integrated machine, a palm computer, a tablet personal computer (pad), a smart television playing terminal, a vehicle-mounted terminal or portable equipment and the like. The operating system of the computer device at the PC side, such as an all-in-one machine, may include, but is not limited to, linux system, unix system, windows series system (such as Windows xp, windows 7, etc.), mac OS X system (operating system of apple computer), etc. The operating system of the computer device of the mobile terminal, such as a smart phone, may include, but is not limited to, an android system, an IOS (operating system of an apple phone), a Window system, and other operating systems.

The embodiment of the application provides an SQL statement dynamic construction method based on the FlinkCDC, which can be executed by an SQL statement dynamic construction device based on the FlinkCDC. The apparatus may be implemented in software and/or hardware and may typically be integrated in a computer device.

The embodiment also discloses a computer readable storage medium storing a computer program, which causes a computer to execute to implement the SQL statement dynamic construction method.

Furthermore, embodiments of the present application provide a computer program product comprising a non-transitory computer-readable storage medium storing a computer program operable to cause a computer to execute to perform some or all of the steps of any one of the methods recited in the method embodiments. The computer program product may be a software installation package, the computer comprising a computer device.

The above description is only of the preferred embodiments of the present invention; the scope of the invention is not limited in this respect. Any person skilled in the art, within the technical scope of the present disclosure, may apply to the present invention, and the technical solution and the improvement thereof are all covered by the protection scope of the present invention.

Claims

1. The SQL statement dynamic construction method based on the FlinkCDC is characterized by being applied to an anti-serializer and comprising the following steps:

2. The method for dynamically constructing an SQL statement based on the FlinkCDC according to claim 1, wherein the type conversion of the pre-change field value and/or the post-change field value in the general data is performed by using a type parser based on the field type, specifically comprising:

based on the write-in type, establishing connection with write-in;

3. The method for dynamically constructing an SQL statement based on the FlinkCDC according to claim 2, wherein a plurality of type conversion functions matched with field types are configured in the type parser;

4. The method for dynamically constructing SQL statements based on the FlinkCDC according to claim 2, wherein the write-in type and the tables to be synchronized are configured through JSON files in a synchronization task; the user name, password and connection mode of writing into the library are also configured through the JSON file in the synchronous task.

5. The method for dynamically constructing an SQL statement based on the FlinkCDC according to claim 1, wherein the method for inputting the statement operation type in the general data and the field value before and/or after the change after the type conversion into the SQL constructor to construct the SQL statement which can be normally executed in writing and warehousing specifically comprises the following steps:

acquiring statement operation types in the general data;

6. The method for dynamically constructing the SQL statement based on the FlinkCDC according to claim 4, wherein the construction of the different SQL statements based on different statement operation types comprises the following steps:

7. The method for dynamically constructing an SQL statement based on the FlinkCDC according to claim 1, wherein after constructing the SQL statement that can be normally executed in writing into a library, further comprising:

8. An SQL statement dynamic construction device based on FlinkCDC is characterized by being applied to a deserializer and comprising:

9. A computer device comprising at least one processor; and a memory communicatively coupled to the at least one processor; wherein the memory stores a computer program executable by the at least one processor to enable the at least one processor to perform the SQL statement dynamic construction method of any one of claims 1-7.

10. A computer-readable storage medium storing a computer program that causes a computer to execute to implement the SQL statement dynamic construction method of any one of claims 1 to 7.