DDL statement conversion method for incremental data migration, and related device

Abstract

Provided in the present description are a DDL statement conversion method for incremental data migration, and a related device. The method comprises: in response to triggering an incremental data migration task between a source end database and a target end database, acquiring a source end DDL statement which has been executed in the source end database and conforms to a syntactic rule of the source end database; analyzing the source end DDL statement to obtain operating information contained in the source end DDL statement in respect of the source end database and, on the basis of a predefined data format, converting the operating information into a corresponding structural body; and converting the structural body into a target end DDL statement conforming to a syntactic rule of the target end database, and executing the target end DDL statement in the target end database.

Description

A DDL statement conversion method and related equipment in incremental data migration Technical Field

[0001] One or more embodiments of this specification relate to the field of data migration technology, and in particular, to a DDL statement conversion method and related equipment in incremental data migration. Background Art

[0002] Data Definition Language (DDL) can perform a series of operations on database objects, such as creating, modifying, and deleting tables. Therefore, during incremental data migration between a source and target database, DDL statements executed in the source database must be accurately synchronized with the target database to ensure consistency between the source and target databases.

[0003] However, if the source and target databases are heterogeneous databases of different database types, there will be syntax compatibility issues with DDL statements during the synchronization process. That is, DDL statements that can be correctly executed on the source side will not be correctly executed on the target database because they do not conform to the syntax rules of the target database, which will seriously affect the consistency of the target and source databases.

[0004] Summary of the Invention

[0005] In view of this, one or more embodiments of this specification provide a DDL statement conversion method and related equipment in incremental data migration.

[0006] In a first aspect, the present specification provides a method for converting DDL statements in incremental data migration, the method comprising: in response to triggering an incremental data migration task between a source database and a target database, obtaining a source DDL statement that has been executed in the source database and conforms to the grammatical rules of the source database; parsing the source DDL statement to obtain operation information for the source database contained in the source DDL statement, and converting the operation information into a corresponding structure based on a predefined data format; converting the structure into a target DDL statement that conforms to the grammatical rules of the target database, and executing the target DDL statement in the target database.

[0007] In a second aspect, the present specification provides a DDL statement conversion device in data migration, comprising: an acquisition unit for acquiring, in response to triggering an incremental data migration task between a source database and a target database, a source DDL statement that has been executed in the source database and conforms to the syntax rules of the source database; a parsing unit for parsing the source DDL statement, obtaining operation information for the source database contained in the source DDL statement, and converting the operation information into a corresponding structure based on a predefined data format; a conversion unit for converting the structure into a target DDL statement that conforms to the syntax rules of the target database, and executing the target DDL statement in the target database.

[0008] Accordingly, this specification also provides a computer device, comprising: a memory and a processor; the memory stores a computer program that can be run by the processor; when the processor runs the computer program, it executes the DDL statement conversion method in incremental data migration described in the first aspect above.

[0009] Accordingly, this specification also provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the method for converting DDL statements in incremental data migration as described in the first aspect above is executed.

[0010] In summary, when heterogeneous source-side databases and target-side databases perform incremental data migration, the present application can first convert the source-side DDL statements executed in the source-side database that comply with the grammatical rules of the source-side database into a universal structure. The structure can describe the operation information contained in the source-side DDL statement based on a predefined data format. Then, based on the operation information described in the structure, a target-side DDL statement that complies with the grammatical rules of the target-side database is further generated, and the target-side DDL statement is executed in the target-side database. In this way, the present application uses the intermediate structure as a grammatical conversion bridge between the source-side DDL statement and the DDL statement, connecting the previous and the next, and no longer needing to consider the differences in grammatical rules between each source-side database and the target-side database, which greatly improves the conversion efficiency of DDL statements between heterogeneous databases and reduces the conversion cost of DDL statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] FIG1 is a schematic diagram of a system architecture for incremental data migration provided by an exemplary embodiment;

[0012] FIG2 is a flow chart of a DDL statement conversion method in incremental data migration provided by an exemplary embodiment;

[0013] FIG3 is a schematic structural diagram of a DDL statement conversion device in incremental data migration provided by an exemplary embodiment;

[0014] FIG4 is a schematic structural diagram of a computer device provided by an exemplary embodiment. DETAILED DESCRIPTION

[0015] Exemplary embodiments will be described in detail herein, with examples illustrated in the accompanying drawings. In the following description, when referring to the drawings, identical numerals in different figures represent identical or similar elements, unless otherwise indicated. The implementations described in the following exemplary embodiments are not intended to represent all implementations consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with certain aspects of one or more embodiments of this specification, as detailed in the appended claims.

[0016] It should be noted that in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or fewer steps than those described in this specification. In addition, a single step described in this specification may be broken down into multiple steps for description in other embodiments, and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0017] It should be noted that the “plurality” mentioned in this application refers to two or more.

[0018] In addition, the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0019] (1) Data Definition Language (DDL) is a statement used to create, delete, modify, and other operations on objects in the database (such as data tables, views, and indexes).

[0020] During incremental data migration between the source and target databases, the target database must synchronize not only the specific data content that has changed in the source database, but also the table structure (e.g., table data types, indexes, constraints, and other information). Therefore, during incremental data migration, DDL statements executed in the source database must be synchronized with the target database, ensuring that the target database executes the same DDL statements as the source database, thereby ensuring consistency in the target database's table structure with the source database.

[0021] If it is a homogeneous migration, that is, the database types of the source and target databases are the same, there are no syntax compatibility issues when synchronizing the source DDL statements to the target database. Usually, you only need to map the database name and data table name contained in the DDL statement before you can directly synchronize the DDL statement to the target database for execution.

[0022] If it is a heterogeneous migration, that is, the database types of the source and target databases are inconsistent, the DDL statements on the source side will have syntax compatibility issues during the synchronization process. That is, DDL statements that can be correctly executed on the source side will not be correctly executed on the target database because they do not conform to the syntax rules of the target database, which seriously affects the consistency of the target and source databases.

[0023] Based on this, in order to ensure that the DDL statements in the heterogeneous migration scenario can be synchronized smoothly, it is often necessary to perform syntax conversion on the source-side DDL statements to obtain target-side DDL statements that correspond to the source-side DDL statements and comply with the syntax rules of the target database so that they can be executed in the target-side database. However, in conventional DDL statement conversion solutions, it is usually necessary to customize the syntax mapping relationship between the source-side database and the target-side database on a one-to-one basis, and then convert the source-side DDL statements in the source-side database into target-side DDL statements that comply with the syntax rules of the target-side database based on the pre-customized syntax mapping relationship. This is inefficient and costly. Every time a new database type is added, the corresponding DDL conversion solution must be customized again. As the number of database types in incremental data migration continues to increase, the one-to-one customized DDL conversion solution will bring huge costs, fail to meet actual needs, and pose great resistance to on-site deployment.

[0024] Based on this, this specification provides a technical solution. When performing incremental data migration between heterogeneous source and target databases, the DDL statements executed in the source database can be first converted into a common structure, and then target DDL statements that comply with the target database's grammatical rules are generated based on the structure. This greatly improves the efficiency of DDL statement conversion between heterogeneous databases and reduces costs.

[0025] During implementation, in response to triggering the incremental data migration task between the source database and the target database, the present application can obtain the source DDL statement that has been executed in the source database and conforms to the grammatical rules of the source database. Then, the source DDL statement is parsed to obtain the operation information for the source database contained in the source DDL statement, and based on the predefined data format, the operation information is converted into a corresponding structure. Then, the source database can send the structure to the target database connected to it, and accordingly, the target database receives the structure and converts the structure into a target DDL statement that conforms to the grammatical rules of the target database, and finally the target DDL statement can be executed in the target database.

[0026] In the above technical solution, when the heterogeneous source-side database and target-side database perform incremental data migration, the present application can first convert the source-side DDL statement executed in the source-side database that complies with the grammatical rules of the source-side database into a universal structure. The structure can describe the operation information contained in the source-side DDL statement based on a predefined data format. Then, based on the operation information described in the structure, a target-side DDL statement that complies with the grammatical rules of the target-side database is further generated, and the target-side DDL statement is executed in the target-side database. In this way, the present application uses the intermediate structure as a grammatical conversion bridge between the source-side DDL statement and the DDL statement, connecting the previous and the next, and no longer needing to consider the differences in grammatical rules between each source-side database and the target-side database, which greatly improves the conversion efficiency of DDL statements between heterogeneous databases, reduces the conversion cost of DDL statements, and meets the actual needs of users.

[0027] In addition, compared with the conventional technical solution that requires pre-customization of a one-to-one syntax mapping relationship for each source database and target database to complete the DDL statement conversion, the present application can be easily applied to various types of databases, and ultimately realize star-shaped DDL statement conversion (multiple types of source-side DDL statements → intermediate structures → multiple types of target-side DDL statements), which has strong scalability.

[0028] Please refer to Figure 1, which is a schematic diagram of a system architecture for incremental data migration provided by an exemplary embodiment. One or more embodiments provided in this specification may be implemented in the system architecture shown in Figure 1 or a similar system architecture. As shown in Figure 1, the system may include a source database and a target database. In one illustrated embodiment, a communication connection may be established between the source database and the target database via a wired or wireless method, which is not specifically limited in this specification.

[0029] In one embodiment, the source database and the target database may be heterogeneous databases of different database types. Furthermore, the source database may perform data migration to the target database, which may include full data migration and incremental data migration.

[0030] In one illustrated embodiment, during incremental data migration from a source database to a target database, source DDL statements executed in the source database need to be synchronized to the target database for execution to ensure consistency between the source and target databases.

[0031] As shown in Figure 1, during the incremental data migration from the source database to the target database, the executed source DDL statements that comply with the grammatical rules of the source database can be obtained from the source database.

[0032] The grammatical rules of the source database may be related to the database type and version number of the source database. For example, the grammatical rules may specify information such as data types and data table partitions supported by the source database, which is not specifically limited in this specification.

[0033] The source-side DDL statement may include operation information for the source-side database. In one embodiment, the operation information may include operation object information contained in the source-side DDL statement and the operation type related to the operation object, etc., which is not specifically limited in this specification.

[0034] For example, the operation object may be a data table, view, index, etc. in the source database, which is not specifically limited in this specification. For example, the operation type may be a create operation, a delete operation, a modify operation, etc., which is not specifically limited in this specification.

[0035] Furthermore, as shown in FIG1 , the source database may convert the executed source DDL statement into a corresponding structure, wherein the structure may be used to describe the operation information included in the source DDL statement based on a predefined data format.

[0036] In an illustrated embodiment, when the source database converts the source DDL statement into the corresponding structure, the process may include: parsing the source DDL statement to obtain operation information for the source database contained in the source DDL statement, and converting the parsed operation information into the corresponding structure based on a predefined data format, etc. The details are not elaborated here, and reference may be made to the description in the following embodiment for details.

[0037] Furthermore, as shown in FIG1 , the source-side database may send the converted structure to the target-side database connected thereto, and correspondingly, the target-side database receives the structure.

[0038] Furthermore, as shown in FIG1 , after receiving the structure, the target-side database can convert the structure into a target-side DDL statement that complies with the target-side grammatical rules and execute the target-side DDL statement, thereby ultimately achieving DDL statement synchronization in incremental data migration.

[0039] In one illustrated embodiment, when the target database converts the structure into a target-side DDL statement that complies with the target-side grammatical rules, the process may include: parsing the structure to obtain operation information contained in the structure. If at least some of the operation information does not comply with the grammatical rules of the target database, syntax conversion may be performed on the at least some of the operation information, and a target-side DDL statement that complies with the grammatical rules of the target database may be generated based on the converted operation information. This process is not further described here; for details, reference may be made to the description in the following embodiments.

[0040] It should be noted that this specification does not specifically limit the specific types of the source database and the target database.

[0041] In one illustrated embodiment, the source database and the target database may be relational databases, such as Oracle database, mySQL database, PostgreSQL database, OceanBase database, DB2 database, etc., and this specification does not impose any specific limitations on this.

[0042] In one illustrated embodiment, the system architecture depicted in FIG1 may further include a data transmission service program that interfaces with the source database and the target database. The data transmission service program may run on a computer device (not shown) independent of the source database and the target database. The data transmission service program may be used to perform incremental data migration between the source database and the target database. Furthermore, the data transmission service program may be used to perform the aforementioned DDL statement conversion, including obtaining source DDL statements from the source database, converting the source DDL statements into structures, further converting the structures into target DDL statements, and finally sending the target DDL statements to the target database, etc. This specification does not impose specific limitations on this.

[0043] In this way, the present application uses the intermediate structure as a syntax conversion bridge between the source DDL statement and the DDL statement, connecting the previous and the next, and no longer needing to consider the differences in syntax rules between each source database and the target database, greatly improving the conversion efficiency of DDL statements between heterogeneous databases and reducing the conversion cost of DDL statements.

[0044] Please refer to Figure 2, which is a schematic flow diagram of a DDL statement conversion method for incremental data migration, provided by an exemplary embodiment. This method can be applied to the system architecture shown in Figure 1, specifically to the source and target databases in the system architecture shown in Figure 1, and can also be applied to data transmission service programs independent of the source and target databases. As shown in Figure 2, this method can specifically include the following steps S101-S103.

[0045] Step S101 : in response to triggering an incremental data migration task between a source database and a target database, obtaining source DDL statements that have been executed in the source database and comply with the syntax rules of the source database.

[0046] In one illustrated embodiment, the source database can perform incremental data migration to the target database. During the incremental data migration process, the source DDL statements executed in the source database need to be synchronized to the target database for execution to ensure consistency between the source database and the target database.

[0047] Based on this, in response to triggering an incremental data migration task between a source database and a target database, the present application can obtain the source DDL statements that have been executed in the source database.

[0048] It should be understood that source-side DDL statements executed in a source database generally conform to the source database's syntax rules. The source database's syntax rules may be related to the source database's database type and version number, among other things. For example, the syntax rules may specify the data types supported by the source database, the data length corresponding to the data type, and data table partition information, among other things. This specification does not specifically limit this.

[0049] It should be noted that this specification does not specifically limit the method of obtaining source-side DDL statements.

[0050] In one illustrated embodiment, the present application can obtain the executed source DDL statements in real time during the process of the source database executing the source DDL statements. For example, each time a source DDL statement is executed in the source database, the executed source DDL statement is obtained in a timely manner.

[0051] In an illustrated embodiment, the present application may also periodically obtain one or more source-side DDL statements executed within a period of time according to a preset time interval, and so on. This specification does not make specific limitations on this.

[0052] In one illustrated embodiment, the present application may obtain the executed source DDL statements by the source database itself, or may obtain the executed source DDL statements in the source database by a data transmission service program used to perform incremental data migration, etc. This specification does not make any specific limitations on this.

[0053] Step S102 : parsing the source-side DDL statement to obtain operation information for the source-side database contained in the source-side DDL statement, and converting the operation information into a corresponding structure based on a predefined data format.

[0054] In an illustrated embodiment, after obtaining the source DDL statement executed in the source database, the source database may parse the source DDL statement to obtain operation information for the source database contained in the source DDL statement.

[0055] The above operation information may include any possible information related to the operation to be executed by the source-side DDL statement.

[0056] In an illustrated embodiment, the operation information may include operation object information included in the source-side DDL statement and an operation type related to the operation object, etc., which is not specifically limited in this specification.

[0057] Exemplarily, the operation type may be a creation operation, a deletion operation, a modification operation, etc., which is not specifically limited in this specification.

[0058] Exemplarily, the operation object information may include the type, object identifier, and attribute information of the operation object, etc., which is not specifically limited in this specification. The type of the operation object may be a data table, view, index, etc. in the source database, which is not specifically limited in this specification. The object identifier of the operation object may be the name of the operation object, such as the name of the data table, the name of the view, and the name of the index, etc. The attribute information of the operation object may be, for example, the data type, constraint type, and partition type in the data table, etc., which is not specifically limited in this specification.

[0059] In an illustrated embodiment, taking the operation object as a data table as an example, the operation information may specifically include the name of the source database, the name of the data table in the source database targeted by this operation, information corresponding to the fields (TableColumn) in the data table, information corresponding to the constraints (TableConstraint) in the data table, information corresponding to the indexes (TableIndex) in the data table, information corresponding to the partitions (TablePartition) in the data table, and comments (comment) information of the data table, etc. This specification does not make specific limitations on this.

[0060] For example, the information corresponding to the fields in the data table may include: the field names, field types, additional attributes of the fields (such as whether the field is non-null, default value, character set, visibility, whether it is a generated column, etc.), etc., of the multiple fields contained in the data table, and this specification does not specifically limit this. Among them, the field type may include the data type of the field, such as integer, floating point, and string type, etc., and this specification does not specifically limit this.

[0061] For example, the information corresponding to the constraint in the data table may include: the name of the constraint, the constraint type, and the set of fields in the data table affected by the constraint, etc., which are not specifically limited in this specification. Among them, the constraint type may include a primary key constraint, a not null constraint, a unique constraint, and a foreign key constraint, etc., which are not specifically limited in this specification.

[0062] For example, the information corresponding to the index in the data table may include: the name of the index, the index type, the set of fields in the data table affected by the index, and whether the index is a prefix index. If so, the information may also include the length of the prefix index, etc. This specification does not make specific limitations on this.

[0063] Exemplarily, the information corresponding to the partitions in the data table may include: the partition type of the first-level and second-level partitions corresponding to the data table, the partition expressions of the first-level and second-level partitions, the number of first-level and second-level partitions, and the shard information corresponding to each partition (such as the name of the shard, the shard type, the shard expression, etc.), etc. This specification does not make specific limitations on this.

[0064] In one illustrated embodiment, taking the operation object as a data table as an example, this application defines a corresponding dynamic structure (Event) to represent the above dynamic operation behaviors based on dynamic DDL operations such as drop table / alter table / truncate table / create table.

[0065] Furthermore, after parsing the operation information contained in the source DDL statement, the present application can convert the operation information into a corresponding structure based on a predefined data format. It should be understood that the structure can describe the operation information based on a predefined data format.

[0066] Exemplarily, taking the operation object contained in the source-side DDL statement as a data table and the operation type as a modification operation as an example, the structure converted based on the source-side DDL statement may include: the name of the data table to be modified, the modification type and modification content of the data table, etc. Among them, the modification type may include adding or subtracting columns, adding indexes, deleting indexes, modifying field types, and modifying field default values, etc. Further, when the modification type includes adding or subtracting columns, the above-mentioned modification type may include column information of the added or subtracted columns; when the modification type includes adding an index, the above-mentioned modification type may include the index name of the index to be added, the index type, the set of fields affected by the index, etc.; when the modification type includes deleting an index, the above-mentioned modification type may include the index name of the index to be deleted; when the modification type includes modifying a field type, the above-mentioned modification type may include the field name to be modified, the new field type, etc., and this specification does not make specific limitations on this.

[0067] For example, taking the operation object contained in the source-side DDL statement as a data table and the operation type as a delete operation as an example, the structure converted based on the source-side DDL statement may include: the name of the data table to be deleted, whether the data table to be deleted is a temporary table, and the type of delete operation (for example, whether to delete only the table, or delete the table and the constraints on the table, etc.), etc. This specification does not make specific restrictions on this.

[0068] Below, several examples will be used to illustrate possible source-side DDL statements in this application and structures converted based on the source-side DDL statements.

[0069] Example 1: Create a data table (create table)

[0070] For example, taking the source database as a MySQL database and the source DDL statement used to create a data table t in the source database as an example, the source DDL statement may be as follows:

[0071] The above c1 to c6 can be the names of columns in the data table t. Accordingly, the structure converted based on the above source-side DDL statement can be as follows:

[0072] Example 2: Modify the data table (Alter table)

[0073] For example, taking the source database as a MySQL database and the source DDL statement used to modify the data table t in the source database as an example, the source DDL statement may be as follows:

[0074] alter table t modify c decimal(4,0)default null comment'c comment'

[0075] Correspondingly, the structure converted based on the above source-side DDL statement can be as follows:

[0076] Example 3: Drop table

[0077] For example, taking the source database as a MySQL database and the source DDL statement used to delete data tables t1 and t2 in the source database as an example, the source DDL statement may be as follows:

[0078] drop table if exists t1,t2

[0079] Correspondingly, the structure converted based on the above source-side DDL statement can be as follows:

[0080] DropTableEvent

[0081] --tableNames='t1','t2'

[0082] --drop option=if exists

[0083] It should be noted that the above examples 1, 2 and 3 are merely illustrative. In some other possible implementations, the specific format of the structure may be different from the above examples, and this specification does not make any specific limitations on this.

[0084] In addition, it should be noted that this specification does not particularly limit the specific implementation method of parsing the source-side DDL statement and obtaining the corresponding structure based on the operation information obtained by the parsing.

[0085] In one embodiment, the source database may include a pre-built DDL parser that matches the syntax rules of the source database and the above-mentioned predefined data format. Accordingly, the source database can convert the executed source DDL statements into corresponding structures through the DDL parser.

[0086] For example, if the source database is a MySQL database, the DDL parser in the MySQL database can match the grammatical rules of the MySQL database and the predefined data format. For example, if the source database is a DB2 database, the DDL parser in the DB2 database can match the grammatical rules of the DB2 database and the predefined data format, and so on. Examples are not given here.

[0087] In one illustrated embodiment, the present application can pre-build the above-mentioned DDL parser in the source database before the incremental data migration begins. In this way, in response to the triggering of the incremental data migration task, the pre-built DDL parser can obtain the executed source DDL statement and convert the source DDL statement into a corresponding structure.

[0088] In one illustrated embodiment, the present application may also respond to the triggering of the incremental data migration task and then construct the above-mentioned DDL parser in the source database to obtain the executed source DDL statement through the DDL parser and convert the source DDL statement into a corresponding structure, etc. This specification does not make specific limitations on this.

[0089] In addition, in an illustrated embodiment, the above-mentioned DDL parser can also be constructed in a data transmission service program that is connected to the above-mentioned source database and target database and is used to perform incremental data migration, so as to obtain the source DDL statements that have been executed in the source database through the DDL parser in the data transmission service program, and convert the source DDL statements into corresponding structures, etc. This specification does not make specific limitations on this.

[0090] In an illustrated embodiment, if the source database connected to the data transmission service program includes multiple database types, a DDL parser corresponding to the grammatical rules of each source database type can be constructed in the data transmission service program.

[0091] For example, if the source database connected to the data transmission service program includes an Oracle database, a mySQL database, an OceanBase database, a DB2 database, etc., a DDL parser corresponding to the grammatical rules of the above-mentioned Oracle database, mySQL database, OceanBase database, and DB2 database can be constructed in advance in the data transmission service program.

[0092] Furthermore, when any one or more of the above-mentioned source databases starts to perform incremental data migration, the data transmission service program can obtain the source DDL statements in the corresponding source databases and convert them into structures through the DDL parser pre-built therein corresponding to any one or more source databases, etc. This specification does not make specific limitations on this.

[0093] Step S103 : converting the structure into a target-side DDL statement that complies with the grammatical rules of the target-side database, and executing the target-side DDL statement in the target-side database.

[0094] In one illustrated embodiment, after the source database converts the executed source DDL statement into a corresponding structure, it can send the converted structure to the target database. Accordingly, the target database receives the structure. Alternatively, as described above, if the source DDL statement is converted into a structure via a data transmission service, the converted structure can also be sent to the target database via the data transmission service, and so on. This specification does not specifically limit this.

[0095] Furthermore, after receiving the structure corresponding to the source-side DDL statement, the target-side database can further convert the structure into a target-side DDL statement that complies with the target-side database syntax rules, and execute the target-side DDL statement in the target-side database to achieve synchronization of the DDL statements, thereby ensuring the consistency of the target-side database and the source-side database during the incremental data migration process.

[0096] For example, if the source database is a mySQL database and the target database is a DB2 database, after converting the source DDL statement that complies with the mySQL database syntax rules into a structure, the present application can further convert the structure into a target DDL statement that complies with the DB2 database syntax rules.

[0097] In an illustrated embodiment, when the target-side database converts a structure into a target-side DDL statement that complies with the grammatical rules of the target-side database, it may specifically include: parsing the received structure, obtaining the operation information described in the structure, and converting the operation information into a target-side DDL statement that complies with the grammatical rules of the target-side database.

[0098] In an illustrated embodiment, if at least part of the operation information does not conform to the grammatical rules of the target database, the target database can perform grammatical conversion on at least part of the operation information and generate a target DDL statement that conforms to the grammatical rules of the target database based on the grammatically converted operation information.

[0099] For example, different types of databases may support different data types, data lengths, etc. Accordingly, the above-mentioned syntax conversion may include data type conversion, data length conversion, etc., which is not specifically limited in this specification.

[0100] In one illustrated embodiment, if at least part of the operation information does not conform to the grammatical rules of the target database, the target database may directly remove at least part of the operation information and generate a target DDL statement that conforms to the grammatical rules of the target database based on the remaining operation information, etc. This specification does not make specific limitations on this.

[0101] Exemplarily, taking the target database as a DB2 database, and the source DDL statement and the corresponding structure shown in the above example 1 as an example, the process of converting the structure into the target DDL statement can be as follows.

[0102] 1. For create table TableObject->objectName="t" in the structure, you can directly output create table "T" to create data table T.

[0103] 2. In the structure, TableColumn->objectName = c1, type = int isNull = false. The information contained therein is compatible with the syntax of the DB2 database, so c1 INTEGER not null is output normally.

[0104] 3. For TableColumn->objectName=c2 type=binary(length=10)isNull=true in the structure, since the DB2 database does not support the binary data type contained therein, the binary data type can be converted to CHAR(10)FOR BIT DATA supported by the DB2 database.

[0105] 4. For TableColumn->objectName=c3 type=varbinary(length=20)isNull=true in the structure, since the DB2 database does not support the data type varbinary contained therein, the data type varbinary can be converted to VARCHAR(10)FOR BIT DATA supported by the DB2 database.

[0106] 5. For TableColumn->objectName=c4 type=bit isNull=true in the structure, since the DB2 database does not support the data type bit contained therein, the data type bit can be converted to CHAR(1)FOR BIT DATA supported by the DB2 database.

[0107] 6. For TableColumn->objectName=c5 type=datetime(precision=2)isNull=false in the structure, since the DB2 database does not support the data type datetime contained therein, the data type datetime can be converted to TIMESTAMP(2) supported by the DB2 database.

[0108] 7. For TableColumn->objectName=c6 type=char(length=100)isNull=false,default='aaa' in the structure, since the maximum length of the data type char exceeds the maximum length supported by the DB2 database, the data type char(length=100) can be converted to VARCHAR(100CODEUNITS32) supported by the DB2 database.

[0109] 8. For TableConstraint->objectName=null, indexType=primary, columnNames=(c1) in the structure, the information contained therein is compatible with the syntax of the DB2 database, so the primary key (c1) is output normally.

[0110] 9. For TableConstraint->objectName=null, indexType=unique, columnNames=(c6) in the structure, the information contained therein is compatible with the syntax of the DB2 database, so CONSTRAINT "GEN_1" UNIQUE(c6) is output normally.

[0111] 10. For the TablePartition structure, since DB2 database tables only support primary partitioning and not secondary partitioning, the incompatible secondary partitions can be ignored, retaining only the primary partitions. Furthermore, the information contained in each partition slice is compatible with DB2 database syntax, allowing for normal output according to DB2 database syntax rules.

[0112] For example, based on the structure shown in Example 1 above, the target-side DDL statement obtained by the final conversion can be as follows:

[0113] Exemplarily, taking the target database as a DB2 database, and the source DDL statement and the corresponding structure shown in the above example 2 as an example, the process of converting the structure into the target DDL statement can be as follows.

[0114] 1. For alterTableEvent->objectName='t' in the structure, you can directly output alter table "t", which means modifying data table t.

[0115] 2. The TableColumnObject structure contains three attributes to be modified. Since DB2 only supports modifying one attribute per alter table DDL statement, the structure must be converted into three alter table DDL statements, each translating a single alter type.

[0116] For example, based on the structure shown in Example 2 above, the target-side DDL statement obtained by the final conversion can be as follows:

[0117] For example, taking the target database as a DB2 database, and the source DDL statement and the corresponding structure shown in the above example three as an example, the process of converting the structure into the target DDL statement can be as follows.

[0118] 1. For the DropTableEvent in the structure, you can directly output the drop table.

[0119] 2. For tableNames = 't1', 't2' in the structure, since it contains two data tables, t1 and t2, but each drop table DDL statement in the DB2 database can only delete one table, the above operation information needs to be converted into two drop table DDL statements.

[0120] 3. For the drop option = if exists in the structure, since the DB2 database does not support this drop option, this part of the information can be ignored.

[0121] For example, based on the structure shown in Example 3 above, the target-side DDL statement obtained by the final conversion can be as follows:

[0122] drop table t1

[0123] drop table t2

[0124] In one illustrated embodiment, similar to the aforementioned source database, the target database may also include a pre-built DDL converter (builder) that matches the target database's grammatical rules and the aforementioned predefined data format. Accordingly, the target database can use the DDL converter to convert the structure into a target DDL statement that complies with the target database's grammatical rules.

[0125] For example, if the source database is a DB2 database, the DDL converter in the DB2 database can match the DB2 database's syntax rules and the predefined data format. For example, if the source database is an OceanBase database, the DDL converter in the OceanBase database can match the OceanBase database's syntax rules and the predefined data format, and so on. Examples are not given here.

[0126] In one illustrated embodiment, the present application can pre-build the above-mentioned DDL converter in the target database before the incremental data migration begins. In this way, in response to the triggering of the incremental data migration task, the pre-built DDL converter can convert the received structure into a target DDL statement that conforms to the grammatical rules of the target database.

[0127] In one illustrated embodiment, the present application may also construct the above-mentioned DDL converter in the target database in response to the triggering of the incremental data migration task, so as to convert the received structure into a target DDL statement that complies with the syntax rules of the target database through the DDL converter, etc. This specification does not make specific limitations on this.

[0128] In addition, in an illustrated embodiment, the above-mentioned DDL converter can also be constructed in a data transmission service program that is connected to the above-mentioned source database and target database and is used to perform incremental data migration, so as to convert the structure into a target-side DDL statement that complies with the grammatical rules of the target-side database through the DDL converter in the data transmission service program, etc. This specification does not make specific limitations on this.

[0129] In an illustrated embodiment, if the target database connected to the data transmission service program includes multiple database types, a DDL converter corresponding to the grammatical rules of each target database type can be constructed in the data transmission service program.

[0130] For example, if the target database connected to the data transmission service program includes Oracle database, mySQL database, OceanBase database and DB2 database, etc., then DDL converters corresponding to the syntax rules of the above-mentioned Oracle database, mySQL database, OceanBase database and DB2 database can be constructed in advance in the data transmission service program.

[0131] Furthermore, when any one or more of the above-mentioned target-end databases starts to perform incremental data migration, the data transmission service program can convert the received structure into a target-end DDL statement that complies with the grammatical rules of the target-end database through the DDL converter pre-built therein corresponding to any one or more target-end databases, etc. This specification does not make specific limitations on this.

[0132] As described above, the present application decouples the conversion process of the source-side DDL statement to the structure, and the conversion process of the structure to the target-side DDL statement. In this way, for each new type of source-side database, it is only necessary to develop a DDL parser corresponding to the new type of source-side database to parse the source-side DDL statement in the new type of source-side database into an intermediate general structure; similarly, for each new type of target-side database, it is only necessary to develop a DDL converter corresponding to the new type of target database to convert the intermediate structure into a target-side DDL statement that conforms to the grammatical rules of the target database. In this way, the DDL statement conversion method provided by the present application can ultimately achieve star-shaped DDL statement conversion (multiple types of source-side DDL statements → intermediate structures → multiple types of target-side DDL statements), which greatly improves the conversion efficiency of DDL statements between various types of databases and has strong scalability.

[0133] In summary, when the heterogeneous source database and target database perform incremental data migration, the present application can first convert the source DDL statement executed in the source database that complies with the grammatical rules of the source database into a universal structure. Among them, the structure can describe the operation information contained in the source DDL statement based on a predefined data format. Then, based on the operation information described in the structure, a target DDL statement that complies with the grammatical rules of the target database is further generated, and the target DDL statement is executed in the target database. In this way, the present application uses the intermediate structure as a grammatical conversion bridge between the source DDL statement and the DDL statement, connecting the previous and the next, and no longer needing to consider the differences in grammatical rules between each source database and the target database, greatly improving the conversion efficiency of DDL statements between heterogeneous databases, reducing the conversion cost of DDL statements, and meeting the actual needs of users. In addition, compared with the conventional technical solution that requires pre-customization of a one-to-one grammatical mapping relationship for each source database and target database in order to complete the DDL statement conversion, the present application can be easily applied to various types of databases and has strong scalability.

[0134] In addition, in an embodiment shown, the source-side database and the target-side database can also be isomorphic databases of the same database type. In conventional solutions, it is often necessary to monitor and map the database name, data table name, and view name contained in the source-side DDL statement. If the monitoring mechanism (for example, using regular expressions to match the data table name and view name in the source-side DDL statement) is not accurate, it is very easy to cause mapping errors, which will cause errors in the DDL statements synchronized to the target-side database, affecting the consistency between the source-side database and the target-side database. Compared with the above-mentioned conventional solution, the present application can obtain accurate data table names, column names, etc. in the structure through the intermediate conversion structure, and then obtain accurate target-side DDL statements, thereby ensuring the accuracy of DDL statement synchronization.

[0135] Corresponding to the implementation of the above-mentioned method flow, an embodiment of this specification also provides a DDL statement conversion device in incremental data migration. Please refer to Figure 3, which is a structural diagram of a DDL statement conversion device in incremental data migration provided by an exemplary embodiment. The device 30 can be applied to the source database and target database for incremental data migration in the system architecture shown in Figure 1. As shown in Figure 3, the device 30 includes: an acquisition unit 301, which is used to respond to the triggering of the incremental data migration task between the source database and the target database, and obtain the source DDL statement that has been executed in the source database and conforms to the grammatical rules of the source database; a parsing unit 302, which is used to parse the source DDL statement, obtain the operation information for the source database contained in the source DDL statement, and convert the operation information into a corresponding structure based on a predefined data format; a conversion unit 303, which is used to convert the structure into a target DDL statement that conforms to the grammatical rules of the target database, and execute the target DDL statement in the target database.

[0136] In an illustrated embodiment, the conversion unit 303 is specifically used to: parse the structure to obtain the operation information contained in the structure; if at least part of the operation information does not conform to the grammatical rules of the target-side database, perform grammatical conversion on at least part of the operation information, and generate the target-side DDL statement that conforms to the grammatical rules of the target-side database based on the grammatically converted operation information.

[0137] In an illustrated embodiment, the operation information includes operation object information contained in the source-side DDL statement and an operation type related to the operation object.

[0138] In an illustrated embodiment, the operation object information includes one or more combinations of the following: the type of the operation object, the object identifier, and the attribute information; the operation type is any one of a delete operation, a modify operation, and a create operation.

[0139] In one illustrated embodiment, the attribute information of the operation object includes a data type; and the syntax conversion includes a data type conversion.

[0140] In an illustrated embodiment, the source database includes a pre-built DDL parser that matches the grammatical rules of the source database and the predefined data format; the parsing unit 302 is specifically used to: parse the source DDL statement through the DDL parser in the source database, obtain the operation information for the source database contained in the source DDL statement, and convert the operation information into a corresponding structure based on the predefined data format.

[0141] In one illustrated embodiment, the target-side database includes a pre-built DDL converter that matches the grammatical rules of the target-side database and the predefined data format; the conversion unit 303 is specifically used to: convert the structure into a target-side DDL statement that complies with the grammatical rules of the target-side database through the DDL converter in the target-side database.

[0142] In an illustrated embodiment, the source-end database and the target-end database are heterogeneous databases of different database types.

[0143] The implementation process of the functions and effects of each unit in the above-mentioned device 30 is specifically described in the description of the embodiments corresponding to Figures 1 and 2 above, and will not be repeated here. It should be understood that the above-mentioned device 30 can be implemented by software, or by hardware or a combination of software and hardware. Taking software implementation as an example, as a device in the logical sense, it is formed by the processor (CPU) of the device where it is located reading the corresponding computer program instructions into the memory and running them. From the hardware level, in addition to the CPU and memory, the device where the above-mentioned device is located usually also includes other hardware such as chips for wireless signal transmission and reception, and / or other hardware such as boards and cards for implementing network communication functions.

[0144] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical modules, that is, they may be located in one place or distributed across multiple network modules. Some or all of the units or modules may be selected according to actual needs to achieve the purpose of the scheme of this specification. Those of ordinary skill in the art can understand and implement the present invention without inventive effort.

[0145] The devices, units, and modules described in the above embodiments may be implemented by computer chips or entities, or by products having certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email transceiver, game console, tablet computer, wearable device, vehicle-mounted computer, or any combination of these devices.

[0146] Corresponding to the above-mentioned method embodiment, the embodiment of this specification also provides a computer device. Please refer to Figure 4, which is a structural diagram of a computer device provided by an exemplary embodiment. The computer device shown in Figure 4 can be the source database and the target database in the system architecture shown in Figure 1 above, or the computer device can also be a device docked with the source database and the target database, in which a data transmission service program for performing incremental data migration, etc. can be run. As shown in Figure 4, the computer device includes a processor 1001 and a memory 1002, and can further include an input device 1004 (such as a keyboard, etc.) and an output device 1005 (such as a display, etc.). The processor 1001, the memory 1002, the input device 1004 and the output device 1005 can be connected by a bus or other means. As shown in Figure 4, the memory 1002 includes a computer-readable storage medium 1003, which stores a computer program that can be run by the processor 1001. The processor 1001 can be a CPU, a microprocessor, or an integrated circuit for controlling the execution of the above method embodiment. When the processor 1001 runs the stored computer program, it can execute the various steps of the DDL statement conversion method in the incremental data migration in the embodiment of this specification, including: in response to triggering the incremental data migration task between the source database and the target database, obtaining the source DDL statement that has been executed in the source database and conforms to the grammatical rules of the source database; parsing the source DDL statement to obtain the operation information for the source database contained in the source DDL statement, and based on a predefined data format, converting the operation information into a corresponding structure; converting the structure into a target DDL statement that conforms to the grammatical rules of the target database, and executing the target DDL statement in the target database, and so on.

[0147] For a detailed description of each step in the DDL statement conversion method for incremental data migration, please refer to the previous content and will not be repeated here.

[0148] Corresponding to the above method embodiments, embodiments of this specification also provide a computer-readable storage medium storing a computer program. When executed by a processor, these computer programs perform the steps of the DDL statement conversion method for incremental data migration described in the embodiments of this specification. For details, please refer to the description of the embodiments corresponding to Figures 1 and 2 above, and will not be repeated here.

[0149] The above description is only a preferred embodiment of this specification and is not intended to limit this specification. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of this specification should be included in the scope of protection of this specification.

[0150] In a typical configuration, a terminal device includes one or more CPUs, input / output interfaces, network interfaces, and memory.

[0151] Memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0152] Computer-readable media include permanent and non-permanent, removable and non-removable media that can store information using any method or technology. The information can be computer-readable instructions, data structures, program modules or other data.

[0153] Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory media such as modulated data signals and carrier waves.

[0154] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0155] Those skilled in the art will appreciate that the embodiments of this specification may be provided as methods, systems, or computer program products. Thus, the embodiments of this specification may take the form of entirely hardware embodiments, entirely software embodiments, or embodiments combining software and hardware. Furthermore, the embodiments of this specification may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

Claims

1. A DDL statement conversion method in incremental data migration, characterized in that: The method comprises: In response to triggering an incremental data migration task between a source database and a target database, obtaining a source DDL statement that has been executed in the source database and complies with a grammatical rule of the source database; Parsing the source-side DDL statement to obtain operation information for the source-side database contained in the source-side DDL statement, and converting the operation information into a corresponding structure based on a predefined data format; The structure is converted into a target-side DDL statement that complies with the grammatical rules of the target-side database, and the target-side DDL statement is executed in the target-side database.

2. The method according to claim 1, characterized in that The step of converting the structure into a target-side DDL statement that complies with the grammatical rules of the target-side database includes: Parsing the structure to obtain operation information contained in the structure; If at least part of the operation information does not conform to the grammatical rules of the target database, syntax conversion is performed on at least part of the operation information, and the target DDL statement that conforms to the grammatical rules of the target database is generated based on the operation information after syntax conversion.

3. The method according to claim 2, characterized in that The operation information includes operation object information contained in the source-side DDL statement and an operation type related to the operation object.

4. The method according to claim 3, characterized in that The operation object information includes one or more combinations of the following: the type of the operation object, the object identifier and the attribute information; the operation type is any one of a deletion operation, a modification operation and a creation operation.

5. The method according to claim 4, characterized in that The attribute information of the operation object includes a data type; and the syntax conversion includes a data type conversion.

6. The method according to claim 1, characterized in that The source database includes a pre-built DDL parser that matches the grammatical rules of the source database and the predefined data format; The parsing of the source-side DDL statement to obtain operation information for the source-side database contained in the source-side DDL statement, and converting the operation information into a corresponding structure based on a predefined data format, includes: The source-side DDL statement is parsed by a DDL parser in the source-side database to obtain operation information for the source-side database contained in the source-side DDL statement, and the operation information is converted into a corresponding structure based on a predefined data format.

7. The method according to claim 1, characterized in that The target database includes a pre-built DDL converter that matches the grammatical rules of the target database and the predefined data format; The step of converting the structure into a target-side DDL statement that complies with the grammatical rules of the target-side database includes: The structure is converted into a target-side DDL statement that complies with the grammatical rules of the target-side database through a DDL converter in the target-side database.

8. The method according to any one of claims 1 to 7, characterized in that: The source database and the target database are heterogeneous databases of different database types.

9. A DDL statement conversion device in data migration, characterized in that: include: an acquisition unit, configured to acquire, in response to triggering an incremental data migration task between a source database and a target database, a source DDL statement that has been executed in the source database and complies with a grammatical rule of the source database; A parsing unit, configured to parse the source-side DDL statement, obtain operation information for the source-side database contained in the source-side DDL statement, and convert the operation information into a corresponding structure based on a predefined data format; The conversion unit is used to convert the structure into a target-side DDL statement that complies with the grammatical rules of the target-side database, and execute the target-side DDL statement in the target-side database.

10. A computer device, characterized in that: include: Memory and processor; The memory stores a computer program executable by the processor; When the processor runs the computer program, the processor performs the method according to any one of claims 1 to 8.

11. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and when the computer program is executed by a processor, the method according to any one of claims 1 to 8 is implemented.

Images