Automatic table building method and device for data synchronization

Abstract

The invention discloses an automatic table building method and device for data synchronization, and the method comprises the steps: building a mapping configuration table between a data transmitting end database and a data receiving end database, and enabling the types of the data transmitting end database and the data receiving end database to be different, the mapping configuration table comprises a mapping relation between a data sending end database field type and a data receiving end database field type; metadata information of a to-be-transmitted data table in the data sending end database is obtained, and a table building statement of the data receiving end database is automatically built based on the metadata information and the mapping configuration table; and completing heterogeneous data source data transmission based on the table establishment statement. According to the method, the database table building statement of the data receiving end can be automatically built, automatic table building is achieved, and efficiency and safety are remarkably improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data transmission, and in particular to a method and device for automatically building a table for data synchronization. Background Art

[0002] Currently, during the data transmission process (such as data migration), it is necessary to manually create tables in the database of the data receiving end. This process is very cumbersome, especially for different heterogeneous data sources. When facing hundreds or thousands of tables that need to be created, the processing of the field name, keywords, case, and field type of each table field is also a very cumbersome and time-consuming operation. Moreover, manual database operation also brings some data security risks. Summary of the invention

[0003] The embodiment of the present invention provides a method and device for automatically building a table for data synchronization, so as to solve the problem of low data transmission efficiency in the prior art.

[0004] The automatic table creation method for data synchronization according to an embodiment of the present invention includes:

[0005] Constructing a mapping configuration table between a data sending end database and a data receiving end database, wherein the type of the data sending end database is different from the type of the data receiving end database, and the mapping configuration table includes a mapping relationship between a field type of the data sending end database and a field type of the data receiving end database;

[0006] Obtaining metadata information of the data table to be transmitted in the data sending end database, and automatically constructing a table creation statement of the data receiving end database based on the metadata information and the mapping configuration table;

[0007] The heterogeneous data source data transmission is completed based on the table creation statement.

[0008] According to some embodiments of the present invention, the step of automatically constructing a table creation statement of a data receiving end database based on the metadata information and the mapping configuration table includes:

[0009] Based on the field type of the data table to be transmitted recorded in the metadata information, and in comparison with the mapping configuration table, the field type of the table to be constructed in the data receiving end database is automatically determined;

[0010] Automatically determine the field names of the table to be constructed based on the field names of the data table to be transferred recorded in the metadata information;

[0011] Based on the field type of the table to be constructed and the field name of the table to be constructed, a table construction statement of the data receiving end database is automatically constructed.

[0012] According to some embodiments of the present invention, the mapping configuration table further includes: data receiving end database field length information and precision information.

[0013] According to some embodiments of the present invention, completing the heterogeneous data source data transmission based on the table creation statement includes:

[0014] The length information and / or precision information of the fields in the table creation statement are adjusted, and the heterogeneous data source data transmission is completed based on the adjusted table creation statement.

[0015] According to some embodiments of the present invention, completing the heterogeneous data source data transmission based on the table creation statement includes:

[0016] The data sending end database and the data receiving end database are connected through the jdbc connection mode, and the table creation statement is executed.

[0017] The automatic table creation device for data synchronization according to an embodiment of the present invention includes:

[0018] A setting unit, used to construct a mapping configuration table between a data sending end database and a data receiving end database, wherein the type of the data sending end database is different from the type of the data receiving end database, and the mapping configuration table includes a mapping relationship between a field type of the data sending end database and a field type of the data receiving end database;

[0019] A table creation statement construction unit, used to obtain metadata information of the data table to be transmitted in the data sending end database, and automatically construct a table creation statement of the data receiving end database based on the metadata information and the mapping configuration table;

[0020] The transmission unit is used to complete the heterogeneous data source data transmission based on the table creation statement.

[0021] According to some embodiments of the present invention, the table creation statement construction unit is used to:

[0022] Based on the field type of the data table to be transmitted recorded in the metadata information, and in comparison with the mapping configuration table, the field type of the table to be constructed in the data receiving end database is automatically determined;

[0023] Automatically determine the field names of the table to be constructed based on the field names of the data table to be transferred recorded in the metadata information;

[0024] Based on the field type of the table to be constructed and the field name of the table to be constructed, a table construction statement of the data receiving end database is automatically constructed.

[0025] According to some embodiments of the present invention, the mapping configuration table further includes: data receiving end database field length information and precision information.

[0026] According to some embodiments of the present invention, the transmission unit is used to:

[0027] The length information and / or precision information of the fields in the table creation statement are adjusted, and the heterogeneous data source data transmission is completed based on the adjusted table creation statement.

[0028] According to some embodiments of the present invention, the transmission unit is used to:

[0029] The data sending end database and the data receiving end database are connected through the jdbc connection mode, and the table creation statement is executed.

[0030] By adopting the embodiments of the present invention, it is possible to realize automatic construction of table creation statements in the database of the data receiving end, realize automatic table creation, and greatly improve the data transmission efficiency. In particular, when it comes to large-scale data migration and synchronization, batch table creation can be automatically realized. Compared with manual table creation in the prior art, the efficiency and security are significantly improved.

[0031] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Various other advantages and benefits will become apparent to those skilled in the art by reading the detailed description of the embodiments below. The accompanying drawings are only used to illustrate the preferred embodiments and are not to be considered as limiting the present invention. In the accompanying drawings:

[0033] Figure 1 is a flow chart of an automatic table creation method for data synchronization in an embodiment of the present invention;

[0034] Figure 2 It is a schematic diagram of field type mapping configuration in an embodiment of the present invention. DETAILED DESCRIPTION

[0035] Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided in order to enable a more thorough understanding of the present invention and to enable the scope of the present invention to be fully communicated to those skilled in the art. In addition, in some examples, known methods, structures and techniques are not shown in detail so as not to obscure the understanding of this specification.

[0036] Reference Figure 1As shown, the automatic table creation method for data synchronization of an embodiment of the present invention includes:

[0037] S1, construct a mapping configuration table between the data sending end database and the data receiving end database.

[0038] The type of the data sending end database is different from the type of the data receiving end database. For example, the type of the data sending end database is MYSQL, and the type of the data receiving end database is ORACLE; or vice versa, the type of the data sending end database is ORACLE, and the type of the data receiving end database is MYSQL; or other situations, which are not listed here one by one.

[0039] The mapping configuration table includes the mapping relationship between the field type of the data sending end database and the field type of the data receiving end database. It can be understood that the mapping configuration table lists the mapping relationship between each field type in the data sending end database and each field type in the data receiving end database. The mapping relationship can be a one-to-one relationship, a one-to-many relationship, or a many-to-one relationship. There is no specific limitation here and it can be set according to actual needs.

[0040] S2, obtaining metadata information of the data table to be transmitted in the data sending end database, and automatically constructing a table creation statement of the data receiving end database based on the metadata information and the mapping configuration table;

[0041] The metadata information at least includes the field type. Therefore, according to the field type of the data table to be transferred, the corresponding field type of the table to be constructed is searched in the mapping configuration table, and then the table construction statement is automatically generated based on the field type of the table to be constructed.

[0042] S3, completing the heterogeneous data source data transmission based on the table creation statement.

[0043] By executing the table creation statement, you can complete the table creation operation and realize data transmission.

[0044] It should be noted that the “automatic” feature involved in the embodiments of the present invention emphasizes that the action does not require manual operation, for example, it can be implemented through a computer program.

[0045] The embodiments of the present invention can implement a data transmission method between heterogeneous data sources, such as a data synchronization process and a data migration process.

[0046] By adopting the embodiments of the present invention, it is possible to realize automatic construction of table creation statements in the database of the data receiving end, realize automatic table creation, and greatly improve the data transmission efficiency. In particular, when it comes to large-scale data migration and synchronization, batch table creation can be automatically realized. Compared with manual table creation in the prior art, the efficiency and security are significantly improved.

[0047] On the basis of the above-mentioned embodiments, various variant embodiments are further proposed. It should be noted that, in order to make the description concise, only the differences from the above-mentioned embodiments are described in each variant embodiment.

[0048] According to some embodiments of the present invention, the mapping configuration table may be constructed by a user or automatically by a computer program. For example, a mapping configuration table template may be constructed first, and then relevant information of a data sending end database and a data receiving end database may be obtained, and the obtained relevant information may be automatically filled into the mapping configuration table template to form a mapping configuration table.

[0049] According to some embodiments of the present invention, the trigger mechanism for obtaining the metadata information of the data table to be transmitted in the data sending end database may be the construction of a mapping configuration table. In other words, after the mapping configuration table is constructed, the metadata information of the data table to be transmitted in the data sending end database is automatically triggered.

[0050] According to some embodiments of the present invention, the step of automatically constructing a table creation statement of a data receiving end database based on the metadata information and the mapping configuration table includes:

[0051] Based on the field type of the data table to be transmitted recorded in the metadata information, and in comparison with the mapping configuration table, the field type of the table to be constructed in the data receiving end database is automatically determined;

[0052] Automatically determine the field names of the table to be constructed based on the field names of the data table to be transferred recorded in the metadata information;

[0053] Based on the field type of the table to be constructed and the field name of the table to be constructed, a table construction statement of the data receiving end database is automatically constructed.

[0054] Furthermore, the automatically determining the field names of the table to be constructed based on the field names of the data table to be transmitted recorded in the metadata information includes:

[0055] Based on the field name of the data table to be transmitted recorded in the metadata information, a table with the same name that already exists in the database of the data receiving end is quickly matched.

[0056] The field names of the table to be built are the same as the field names of the data table to be transferred.

[0057] Furthermore, the automatically determining the field names of the table to be constructed based on the field names of the data table to be transmitted recorded in the metadata information includes:

[0058] Custom table name rules: ${tableName} can be concatenated with prefixes and suffixes, and the target table name after variable replacement is used to verify the table name in the data receiving database;

[0059] If the table name exists, the existing table name will be used directly. If it does not exist, you can automatically create a data table with this name.

[0060] According to some embodiments of the present invention, the mapping configuration table further includes: data receiving end database field length information and precision information.

[0061] It can be understood that the mapping configuration table can also set the data receiving end database field length information and precision information. For example, referring to Table 1, the mapping configuration table can also set whether the data receiving end database field length is set; if the data receiving end database field length is set, whether the data receiving end database field uses the field length and field precision of the corresponding data sending end database field type; if the data receiving end database field does not use the field length and field precision of the corresponding data sending end database field type, what is the specific setting value of the field length, what is the specific setting value of the field precision, and other information.

[0062] Table 1

[0063]

[0064] It should be noted that the source end involved in the table is equivalent to the data sending end, and the target end is equivalent to the data receiving end.

[0065] According to some embodiments of the present invention, completing the heterogeneous data source data transmission based on the table creation statement includes:

[0066] The length information and / or precision information of the fields in the table creation statement are adjusted, and the heterogeneous data source data transmission is completed based on the adjusted table creation statement.

[0067] It can be understood that after the table creation statement is constructed based on the mapping configuration table, it can be displayed to the user, and the user can further adjust the length information and / or precision information therein.

[0068] According to some embodiments of the present invention, completing the heterogeneous data source data transmission based on the table creation statement includes:

[0069] The data sending end database and the data receiving end database are connected by jdbc connection mode, and the table building statement is executed. It can be understood that when building a table based on the table building statement, the jdbc connection mode can be used to connect to different databases, and then the table building statement is executed.

[0070] The timing for creating a table in the embodiment of the present invention is when configuring a data synchronization task to complete the table creation operation.

[0071] Completing the table creation before the actual execution of the synchronization task is more reasonable and more in line with the needs of actual application scenarios, so that the data synchronization link only needs to focus on the data synchronization process without paying attention to other operations.

[0072] For single table creation, submit the target field list generated after automatic mapping through the front-end interface, and execute the table creation statement through the JDBC connection method.

[0073] Furthermore, for batch table creation, since it takes a long time to create a table, and it is necessary to perceive the progress of the table creation in real time, the front-end loop submits the field list of the target end generated after automatic mapping, and executes the table creation statement through the jdbc connection method. The failure of table creation does not affect the next submission, and the requirements of batch table creation can be easily achieved through this method.

[0074] Refer to the following Figure 2 The heterogeneous data source data transmission method according to an embodiment of the present invention is described in detail with a specific application example. It is worth noting that the following description is only an exemplary description and is not a specific limitation of the present invention. All similar structures and similar variations of the present invention should be included in the protection scope of the present invention.

[0075] The heterogeneous data source data transmission method of the embodiment of the present invention can be applied to the data synchronization of heterogeneous data sources, solving the problem of how to complete the table creation requirements when synchronizing heterogeneous data sources in a more convenient, efficient, secure and batch manner in a WEB system, thereby reducing the workload and security risks caused by manual intervention.

[0076] The embodiment of the present invention configures the field type mapping so that the user can quickly and automatically map the table fields on the source side to generate the table creation DDL statement on the target side. The user can also manually adjust the target side field type and field length on the configuration interface, and provide a one-click batch table creation function, which can provide the creation of a single table and the needs of batch table creation. In addition, for the scenario of whole database migration, it also supports the table creation support for thousands of tables in the whole database.

[0077] In detail, first, complete the field type mapping configuration.

[0078] Before automatically creating a table, prepare the field type mapping configuration of the source data source and the target data source, as shown in Table 1, including the source database type, source field type, target database type, target field type, whether to set the length, whether to use the source length and precision, set the length, set the precision, etc.

[0079] Then, according to the metadata information of the data table on the source side, the table creation statement on the target side is automatically mapped and generated through the field type mapping configuration.

[0080] Build the table creation statement on the target side, that is, the DDL statement. Specifically include:

[0081] 1. Obtain source metadata information: First, you need to obtain the metadata information of the source data table, including field name, field type, field length and precision, whether it is a partition field, and other information.

[0082] 2. Automatically map and generate target table DDL information: Based on the metadata information of the source data table, according to the field type mapping configuration, map each field of the target table, including field name, field type, field length and precision, etc. For example, if the source database is MySQL, the source field name is name, and the field type is VARCHAR, after mapping to the target Oracle, the field name remains unchanged, the field type is VARCHAR2, and the field length of the target will follow the length and precision of the source field according to the configuration, see Figure 2 shown.

[0083] 3. Setting of partition fields: If the target end is a Hive table and you need to create a partition table, you can also set the partition fields and partition values ​​through the configuration interface. After setting, the set partition information will also be reflected in the table creation DDL statement.

[0084] Supports automatic table creation for data sources that can create partitioned tables, such as hive, DAMO, and greenplum.

[0085] For data tables that need to create partition fields, you can add partition fields before creating the table. The system provides default partition field values. Users can also customize partition field values ​​according to business needs to achieve data synchronization and write to the specified partition.

[0086] Partition fields are added before building a table, partition fields are created when building a table, and partition directories are created based on partition field values ​​when actual data is synchronized.

[0087] 4. Manually adjust field information: If the target table field name and field type, as well as field length and precision generated by automatic mapping do not meet the requirements, the user can also manually adjust them through the interface.

[0088] Finally, create the table.

[0089] The table is created using the jdbc connection method, that is, using jdbc to connect to different data sources, and then executing the table creation DDL statement we constructed.

[0090] When we need to create a table, we submit the target field list generated after automatic mapping through the front-end interface, and execute the table creation statement through the jdbc connection method.

[0091] When we need to create tables in batches, we take into account that the table creation operation takes a long time and we need to perceive the progress of the table creation in real time. Therefore, for batch table creation operations, we adopt the method of submitting the target end field list generated after automatic mapping in the front end loop, and execute the table creation statement through the jdbc connection method. The failure of table creation does not affect the next submission. This method can conveniently meet the requirements of batch table creation.

[0092] By adopting the embodiment of the present invention, through field type configuration, the field type conversion rules between different heterogeneous data sources can be infinitely expanded, and the field information of the target end table can be quickly obtained based on the source end data table metadata information mapping. Moreover, based on the field type mapping configuration, the field type mapping conversion of dozens, hundreds, or thousands of tables can be efficiently realized, and manual comparison and field type conversion are completely eliminated. In addition, the method of connecting to the target database through the JDBC method and executing the DDL statement to create a data table avoids the data security and risk hazards caused by manual operation of the database, and is more in line with data security requirements.

[0093] It should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

[0094] Based on the above heterogeneous data source data transmission method, the embodiment of the present invention also correspondingly proposes an automatic table creation device for data synchronization, including:

[0095] A setting unit, used to construct a mapping configuration table between a data sending end database and a data receiving end database, wherein the type of the data sending end database is different from the type of the data receiving end database, and the mapping configuration table includes a mapping relationship between a field type of the data sending end database and a field type of the data receiving end database;

[0096] A table creation statement construction unit, used to obtain metadata information of the data table to be transmitted in the data sending end database, and automatically construct a table creation statement of the data receiving end database based on the metadata information and the mapping configuration table;

[0097] The transmission unit is used to complete the heterogeneous data source data transmission based on the table creation statement.

[0098] By adopting the embodiments of the present invention, it is possible to realize automatic construction of table creation statements in the database of the data receiving end, realize automatic table creation, and greatly improve the data transmission efficiency. In particular, when it comes to large-scale data migration and synchronization, batch table creation can be automatically realized. Compared with manual table creation in the prior art, the efficiency and security are significantly improved.

[0099] On the basis of the above-mentioned embodiments, various variant embodiments are further proposed. It should be noted that, in order to make the description concise, only the differences from the above-mentioned embodiments are described in each variant embodiment.

[0100] According to some embodiments of the present invention, the table creation statement construction unit is used to:

[0101] Based on the field type of the data table to be transmitted recorded in the metadata information, and in comparison with the mapping configuration table, the field type of the table to be constructed in the data receiving end database is automatically determined;

[0102] Automatically determine the field names of the table to be constructed based on the field names of the data table to be transferred recorded in the metadata information;

[0103] Based on the field type of the table to be constructed and the field name of the table to be constructed, a table construction statement of the data receiving end database is automatically constructed.

[0104] According to some embodiments of the present invention, the mapping configuration table further includes: data receiving end database field length information and precision information.

[0105] According to some embodiments of the present invention, the transmission unit is used to:

[0106] The length information and / or precision information of the fields in the table creation statement are adjusted, and the heterogeneous data source data transmission is completed based on the adjusted table creation statement.

[0107] According to some embodiments of the present invention, the transmission unit is used to:

[0108] The data sending end database and the data receiving end database are connected through the jdbc connection mode, and the table creation statement is executed.

[0109] It should be noted that, in the description of this specification, the serial numbers of the above-mentioned embodiments of the present invention are only for description and do not represent the advantages and disadvantages of the embodiments.

[0110] The suffixes such as "module", "component" or "unit" used to represent elements are only used to facilitate the description of the present invention, and have no specific meanings by themselves. Therefore, "module", "component" or "unit" may be used mixedly.

[0111] Descriptions with reference to the terms "one embodiment", "some embodiments", "illustrative embodiments", "examples", "specific examples", or "some examples" are intended to mean that the specific features, structures, materials, or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiment or example. Although some of the embodiments described herein include certain features included in other embodiments but not other features, the combination of features of different embodiments is meant to be within the scope of the present invention and to form different embodiments. The specific features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples. For example, in the claims, any one of the claimed embodiments may be used in any combination.

[0112] The terms "comprises", "includes" or any other variation thereof are intended to cover non-exclusive inclusion, such that a process, method, article or apparatus that includes a list of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of additional identical elements in the process, method, article or apparatus that includes the element.

[0113] "And / or" describes the relationship between the associated objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the associated objects are in an "or" relationship.

Claims

1. A method for automatically creating a table for data synchronization, characterized in that: include: Constructing a mapping configuration table between a data sending end database and a data receiving end database, wherein the type of the data sending end database is different from the type of the data receiving end database, and the mapping configuration table includes a mapping relationship between a field type of the data sending end database and a field type of the data receiving end database; Obtaining metadata information of the data table to be transmitted in the data sending end database, and automatically constructing a table creation statement of the data receiving end database based on the metadata information and the mapping configuration table; The heterogeneous data source data transmission is completed based on the table creation statement.

2. The method according to claim 1, characterized in that The method of automatically constructing a table construction statement of a data receiving end database based on the metadata information and the mapping configuration table includes: Based on the field type of the data table to be transmitted recorded in the metadata information, and in comparison with the mapping configuration table, the field type of the table to be constructed in the data receiving end database is automatically determined; Automatically determine the field names of the table to be constructed based on the field names of the data table to be transferred recorded in the metadata information; Based on the field type of the table to be constructed and the field name of the table to be constructed, a table construction statement of the data receiving end database is automatically constructed.

3. The method according to claim 1, characterized in that The mapping configuration table also includes: data receiving end database field length information and precision information.

4. The method according to claim 3, characterized in that The completing the heterogeneous data source data transmission based on the table creation statement includes: The length information and / or precision information of the fields in the table creation statement are adjusted, and the heterogeneous data source data transmission is completed based on the adjusted table creation statement.

5. The method according to claim 1, characterized in that The completing the heterogeneous data source data transmission based on the table creation statement includes: The data sending end database and the data receiving end database are connected through the jdbc connection mode, and the table creation statement is executed.

6. A data synchronization automatic table creation device, characterized in that: include: A setting unit, used to construct a mapping configuration table between a data sending end database and a data receiving end database, wherein the type of the data sending end database is different from the type of the data receiving end database, and the mapping configuration table includes a mapping relationship between a field type of the data sending end database and a field type of the data receiving end database; A table creation statement construction unit, used to obtain metadata information of the data table to be transmitted in the data sending end database, and automatically construct a table creation statement of the data receiving end database based on the metadata information and the mapping configuration table; The transmission unit is used to complete the heterogeneous data source data transmission based on the table creation statement.

7. The device according to claim 6, characterized in that The table creation statement construction unit is used to: Based on the field type of the data table to be transmitted recorded in the metadata information, and in comparison with the mapping configuration table, the field type of the table to be constructed in the data receiving end database is automatically determined; Automatically determine the field names of the table to be constructed based on the field names of the data table to be transferred recorded in the metadata information; Based on the field type of the table to be constructed and the field name of the table to be constructed, a table construction statement of the data receiving end database is automatically constructed.

8. The device according to claim 6, characterized in that The mapping configuration table also includes: data receiving end database field length information and precision information.

9. The device according to claim 8, characterized in that The transmission unit is used for: The length information and / or precision information of the fields in the table creation statement are adjusted, and the heterogeneous data source data transmission is completed based on the adjusted table creation statement.

10. The device according to claim 6, characterized in that The transmission unit is used for: The data sending end database and the data receiving end database are connected through the jdbc connection mode, and the table creation statement is executed.

Images