Data migration method and electronic device

Abstract

The present invention discloses a data migration method and electronic device. The method includes: reading data to be migrated from a table of data to be migrated in a source database; generating binary data based on the data to be migrated; and writing the binary data to a target database based on a target command. The source database and the target database are of different database types. In the present invention, using binary data for data migration improves data migration efficiency.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a data migration method and electronic equipment. Background Art

[0002] The PostgreSQL database, also known as the PG database, supports SQL (structured query language) and JSON (javascript object notation) queries, provides a rich set of external interfaces, and offers high reliability, availability, and fault recovery capabilities. In recent years, the PG database has become increasingly popular, and many databases have been developed based on it, including OpenGauss, FusionDB, Postgres, and GaussDB. The PG database and databases developed based on it are collectively referred to as PG-based databases. Other databases that do not belong to the PG database family, such as MySQL and Oracle databases, are collectively referred to as non-PG-based databases.

[0003] Typically, data stored in non-PG databases is migrated to PG databases to better manage and utilize data, and to improve data availability and security. Improving the efficiency of migration from non-PG databases to PG databases is a major concern in the industry. Summary of the Invention

[0004] The embodiments of the present application disclose a data migration method and an electronic device, which can improve the efficiency of data migration.

[0005] The first aspect discloses a data migration method, which can be applied to electronic devices, modules in electronic devices (for example, chips, central processing units, etc.), and logic modules or software (such as the migration tool described below) that can implement all or part of the functions of electronic devices. The following description is based on the application to electronic devices as an example. The data migration method may include: reading data to be migrated from a data table to be migrated in a source database; generating binary data based on the data to be migrated; writing the binary data to a target database based on a target command; wherein the database type of the source database is different from the database type of the target database.

[0006] In an embodiment of the present application, when the database type of the source database is different from the database type of the target database, after the electronic device reads the data to be migrated from the source database, it can generate binary data required by the target command based on the data to be migrated, and then write the binary data to the target database based on the target command. Among them, when the data in binary format is imported into the target database, no additional processing is required and it can be directly imported into the target database, thereby improving the efficiency of data migration. In addition, the use of a binary method can make the storage space occupied by the data less and the amount of data transmitted over the network less, thereby further improving the efficiency of data migration.

[0007] As a possible implementation method, generating binary data based on the data to be migrated includes: converting the data type in the data to be migrated into a corresponding target data type; the target data type is a data type corresponding to the data to be migrated in the target database; and generating binary data based on the data to be migrated of the target data type.

[0008] In the embodiment of the present application, since the data types supported by the source database and the target database may not be exactly the same, to ensure the accuracy of data import, the electronic device can first convert the data type in the data to be migrated into the corresponding target data type, and then generate the binary data required by the target command based on the data to be migrated of the target data type. In this way, the accuracy of the generated binary data can be guaranteed, and thus the accuracy of the imported data can be guaranteed.

[0009] As a possible implementation method, writing the binary data into the target database based on the target command includes: sending a target command to the target database, the target command carrying a target parameter, the target parameter being used to indicate the binary data, and the target command being used to instruct the target database to write the binary data into the target database.

[0010] In an embodiment of the present application, a target command may be sent to the target database to instruct the target database to write the corresponding binary data into the target database, thereby completing data migration.

[0011] As a possible implementation method, after generating binary data from the data to be migrated, the method also includes: writing the binary data into a buffer; when the buffer is full, placing the buffer into a target queue; writing the binary data to the target database based on the target command includes: obtaining the buffer from the target queue; and writing the binary data stored in the buffer into the target database based on the target command.

[0012] In an embodiment of the present application, binary data can be sent to the target database in units of buffers, so that the target database can write data in units of buffers. In this way, the number of IO (network IO) can be reduced, thereby further improving the efficiency of data migration.

[0013] Exemplarily, the buffer zone here corresponds to the Buffer in the following specific implementation manner.

[0014] As a possible implementation method, reading the data to be migrated from the data table to be migrated in the source database includes: reading the data to be migrated from the data table to be migrated in the source database through multiple first threads; converting the data type in the data to be migrated into the corresponding target data type includes: converting the data type in the data to be migrated into the corresponding target data type through the multiple first threads; generating binary data based on the data to be migrated of the target data type includes: generating the binary data required by the target command based on the data to be migrated of the target data type through the multiple first threads.

[0015] In an embodiment of the present application, multiple first threads can be used to read the data to be migrated from the source database, and the data type in the read data to be migrated can be converted into the corresponding target data type, and the binary data required by the target command can be generated based on the data to be migrated of the target data type. This method of parallel processing through multiple threads can further improve the efficiency of data migration.

[0016] As a possible implementation method, obtaining the buffer from the target queue includes: obtaining multiple buffers from the target queue; writing the binary data stored in the buffer to the target database based on the target command includes: writing the binary data stored in the multiple buffers to the target database respectively based on the target command through multiple second threads.

[0017] In an embodiment of the present application, binary data in different buffers can be written into the target database in parallel through multiple second threads, which can further improve the efficiency of data migration.

[0018] As a possible implementation, the number of the first threads and the number of the second threads are determined according to at least one of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, and the total amount of data in the data tables to be migrated.

[0019] In an embodiment of the present application, the number of first threads can be determined based on one or more of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, or the total amount of data in the data tables to be migrated. Alternatively, the number of second threads can be determined based on one or more of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, or the total amount of data in the data tables to be migrated. This determination method comprehensively considers the amount of data to be migrated, thereby ensuring that the number of first and second threads is appropriate, thereby ensuring the efficiency of data migration.

[0020] As a possible implementation, the number of the first threads is set in a ratio to the number of the second threads, and the ratio of the number of the first threads to the number of the second threads is equal to T1 is the time required for a first thread to read the data to be migrated from the source database, fill a buffer and put it into the target queue. T2 is the time required for a second thread to obtain a buffer from the target queue and write the binary data stored in the buffer into the target database.

[0021] In the embodiment of the present application, the ratio of the number of the first threads to the number of the second threads may be equal to In this way, the number of first threads and the number of second threads are more appropriate, and while ensuring the efficiency of data migration, it can avoid occupying additional CPU resources and memory resources, thereby saving CPU resources and memory resources.

[0022] As a possible implementation manner, the method also includes: obtaining metadata information of the data table to be migrated, the metadata information of the data table to be migrated includes the data type corresponding to each attribute in the data table to be migrated; obtaining metadata information of the target data table in the target database, the metadata information of the target data table includes the data type corresponding to each attribute in the target data table; converting the data type in the data to be migrated to the corresponding target data type includes: based on the metadata information of the data table to be migrated and the metadata information of the target data table, converting the data type of each attribute value in the data to be migrated to the corresponding target data type.

[0023] In an embodiment of the present application, the data type corresponding to each attribute in the data table to be migrated can be accurately determined based on the metadata information of the data table to be migrated, and the data type corresponding to each attribute in the target data table can be accurately determined based on the metadata information of the target data table, thereby ensuring the accuracy of data type conversion.

[0024] As a possible implementation, the binary data includes one or more tuple parts, a tuple part may include a 16-bit attribute quantity indication field and data corresponding to each attribute value in the tuple part, the attribute quantity indication field is used to indicate the number of attributes included in the tuple part, the data corresponding to an attribute value in the tuple part includes a 32-bit attribute value length indication field, the attribute value length indication field is used to indicate the number of bytes occupied by the corresponding attribute value, and the data corresponding to an attribute value in the tuple part also includes an attribute value of the number of bytes indicated by the corresponding attribute value length indication field.

[0025] In the embodiment of the present application, the accuracy of data migration can be guaranteed by the binary data in the above-mentioned specific format, and the target database can efficiently complete the data import based on the binary data in this specific format.

[0026] A second aspect discloses an electronic device, which includes a processor and a memory. The processor calls a computer program stored in the memory to implement the data migration method provided in the first aspect and any possible implementation of the first aspect.

[0027] The third aspect discloses a computer-readable storage medium, on which a computer program or computer instructions are stored. When the computer program or computer instructions are executed, the data migration method disclosed in the above aspects is implemented.

[0028] A fourth aspect discloses a chip, comprising a processor, which is used to execute a computer program or computer instructions stored in a memory. When the computer program or computer instructions are executed, the chip executes the data migration method disclosed in the above aspects.

[0029] As a possible implementation, the memory is located outside the chip.

[0030] A fifth aspect discloses a computer program product, which includes a computer program code. When the computer program code is run, the data migration method disclosed in the above aspects is executed.

[0031] It should be understood that the implementation and beneficial effects of the above-mentioned multiple aspects or any possible implementation methods of the present application can be referenced to each other. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0033] Figure 1 This is a schematic diagram of a data migration scenario disclosed in related art;

[0034] Figure 2 This is a schematic diagram of the software structure of an electronic device disclosed in an embodiment of the present application;

[0035] Figure 3 This is a schematic diagram of a data migration scenario disclosed in an embodiment of the present application;

[0036] Figure 4 This is a schematic diagram of another data migration scenario disclosed in an embodiment of the present application;

[0037] Figure 5 This is a flow chart of a data migration method disclosed in an embodiment of the present application;

[0038] Figure 6 This is a flowchart of another data migration method disclosed in an embodiment of the present application;

[0039] Figure 7 This is a flowchart of another data migration method disclosed in an embodiment of the present application;

[0040] Figure 8 This is a structural diagram of an electronic device disclosed in an embodiment of the present application. DETAILED DESCRIPTION

[0041] The embodiments of the present application disclose a data migration method and an electronic device for improving the efficiency of data migration. The technical solutions in the embodiments of the present application will be described clearly and completely below in conjunction with the accompanying drawings in the embodiments of the present application.

[0042] In order to better understand the embodiments of the present application, the relevant terms and related technologies of the embodiments of the present application are described below.

[0043] The CSV (Comma-Separated Values) format stores data (numbers, text, etc.) in plain text. A CSV file typically contains any number of records, optionally separated by line breaks. Each record can contain one or more field values ​​(attribute values), optionally separated by a delimiter (such as a comma).

[0044] A database cluster is generally a database system consisting of multiple machines (such as servers). One or more databases can be created on a database cluster. A server in a database cluster, or the smallest computing unit within a server, is called a node.

[0045] Database clusters can include centralized database clusters, distributed database clusters, etc. Among them, a centralized database cluster can include a host (master node) and multiple backup machines (slave nodes), and the data stored in the master and backup machines can be the same. A distributed database cluster can also include a master node and multiple slave nodes, each slave node can store a certain shard of data, and the data shards stored by multiple nodes can be spliced ​​together into a complete data. The master node can also be called a coordination node or a management node, and the slave node can also be called a data node. The master node in the database cluster can manage and monitor each slave node. For example, the master node can issue data operation instructions to each slave node, such as data query instructions, instructing the slave node to perform data query.

[0046] For example, a database may store tables, each of which may include multiple columns and rows. Each row in a table (each record in the database) is called a tuple, and each column is an attribute. In other words, a tuple is the smallest storage unit for row data, and a tuple can include the values ​​of each attribute (field) in a row of data.

[0047] PostgreSQL database, referred to as PG database, can support SQL (structured query language), JSON (javascript object notation) queries, etc., provide a rich interface to the outside world, and have high reliability, availability, and fault recovery capabilities. Among them, there are also many databases developed based on the PG database, including OpenGauss, FusionDB, Postgres, GaussDB, etc. For the convenience of description, in the embodiments of the present application, the PG database and the database developed based on the PG database can be collectively referred to as PG system database. Other databases that do not belong to the PG system database are collectively referred to as non-PG system databases, such as MySQL, Oracle database, etc.

[0048] Data migration generally refers to the process of transferring data from one storage system to another. For example, migrating data from one database to another. The database containing the data to be migrated is generally referred to as the source database, and the database receiving the data from the source database is generally referred to as the target database. The primary purpose of data migration is to better manage and utilize data, and to improve data availability and security.

[0049] Data migration generally includes full data migration and incremental data migration. Full data migration generally refers to migrating all data of one or more tables that need to be migrated (such as one or more tables selected by the user) in the source database at a certain point in time to the target database. Full data migration usually involves a large amount of data, such as TB (terabyte) level, and takes a long time. Incremental data migration generally only involves incremental modified data after a specified point in time. It can migrate the incremental modified data of one or more tables that need to be migrated in the source database after a certain point in time to the target database. Generally, full data migration and incremental data migration can be used in conjunction to ensure the integrity of data migration.

[0050] In the related art, since the data types included in the PG database are different or not completely the same as those included in the non-PG database, when migrating data from the non-PG database to the PG database, the data is usually inserted into the target database in CSV format based on the CopyManager interface of the PG database. Figure 1 , Figure 1 This is a schematic diagram of a data migration scenario disclosed in the related art. Figure 1 As shown, during data migration, you can use multithreading to execute query statements (such as select statements) in parallel to retrieve the raw data to be migrated from the source database. You can then convert the raw data into CSV text format. You can then call the copyIn method to insert the CSV text data into the target database. The copyIn method is a data writing method in the CopyManger interface class provided by the PG database driver (such as the JDBC driver). It can insert CSV text data into a table in the target database.

[0051] It should be understood that although data from non-PG databases can be migrated to PG databases through the CSV text format, this approach involves serialization and deserialization problems, space expansion problems, etc. For example, for non-text data types, such as Bytea, Double, Float, Numeric, etc., there are redundant serialization and deserialization problems. For example, assuming that the data found in the source database is of type Float, when migrating the data, it needs to be serialized into a string and written into a CSV text, and then the CSV text can be sent to the target database. After that, the CSV text can be deserialized into the corresponding Float type data. This redundant serialization and deserialization operation will reduce the efficiency of data migration. It should be understood that serialization and deserialization here are relative concepts. Serialization can be understood as converting data of the original type into data of another type. The main function of serialization is to transmit data, while deserialization can be understood as converting the converted data back into data of the original type for data processing, etc. As described above, when sending data, the Float type data can be serialized into a string type first. After receiving the string type data, the target database can deserialize the data into the corresponding Float type data.

[0052] Furthermore, serializing certain data types from the source database into strings can lead to space expansion. For example, if the source database includes blob data to be migrated, serializing it into a string will increase the data size. For example, if the blob contains a 1MB image, serializing it into a string might result in a 2MB file size, which would take up more storage space and increase the transfer time.

[0053] In addition, serializing raw data into strings also involves escaping and unescaping issues. For example, since columns in the CSV text format are separated by column delimiters (such as commas), if a piece of raw data includes column delimiters, then the raw data can be wrapped in double quotes for escape. Furthermore, if a piece of raw data is wrapped in double quotes and the raw data content also includes double quotes, it is necessary to add another double quote before the double quote in the raw data content for escape. Accordingly, when inserting data into the target database based on the CSV text, unescaping is required to restore the original data. It can be seen that this escaping and unescaping will also reduce the efficiency of data migration.

[0054] In order to solve the above problems, in an embodiment of the present application, when migrating data from a non-PG database to a PG database, the data types of the non-PG database and the PG database can be matched, and the data type of the original data in the non-PG database can be converted to the corresponding target data type in the PG database. Then, based on the converted target data type, the binary format data required by the copy from std binary command is generated. After that, the corresponding binary format data can be written to the target database through the copy from std binary command. In this way, data can be imported based on binary format data, and there will be no serialization and deserialization problems, space expansion problems, etc. in related technologies. In addition, the efficiency of data import based on binary format data is higher, so the efficiency of data migration can be improved.

[0055] In order to better understand the embodiments of the present application, the system architecture of the embodiments of the present application is described below.

[0056] See also Figure 2 , Figure 2 2 is a schematic diagram of the software structure of an electronic device disclosed in an embodiment of the present application. The electronic device 200 may be a terminal device such as a laptop computer, a desktop computer, or a server, and the present embodiment is not limited thereto. The electronic device 200 may include a migration tool 201.

[0057] The migration tool 201 may communicate (data exchange) with the source database 300 and the target database 400 , for example, by communicating with the source database 300 and the target database 400 through a network.

[0058] In an embodiment of the present application, the migration tool 201 can migrate the data in the source database 300 to the target database 400, which can be a full data migration or an incremental data migration. During the data migration process, the migration tool 201 can read the data to be migrated from the source database 300, and then match the data type, convert the data type of the data to be migrated into the corresponding target data type in the PG database, and then generate the binary format data required by the copy from std binary command based on the converted target data type. After that, the corresponding binary format data can be written to the target database 400 through the copyfrom std binary command. It should be understood that the data to be migrated is also the data in the source database 300 that needs to be migrated to the target database 400.

[0059] It should be noted that the copy from std binary command is a data writing command unique to the PG database, which can efficiently write binary format data into the table of the PG database.

[0060] It should be noted that in this embodiment of the present application, source database 300 can be a PG database or a non-PG database, and target database 400 can be a PG database. When source database 300 is a PG database, migration tool 201 does not need to match data types and can directly generate binary data required by the copy from std binary command based on the data to be migrated. The corresponding binary data can then be written to the target database using the copy from std binary command.

[0061] It should also be noted that the source database 300 and the target database 400 can be a stand-alone database, a centralized database, or a distributed database, etc., and the present embodiment does not limit this. Figure 2 The source database 300 and the target database 400 are located outside the electronic device 200, but the present embodiment does not limit the specific devices where the source database 300 and the target database 400 are located. For example, the source database 300 can be located in the electronic device 200, and the target database 400 can also be located in the electronic device 200.

[0062] For a more detailed description of the migration tool 201 , please refer to the relevant description in the following method embodiment, which will not be repeated here.

[0063] It should be noted that, although the embodiments of the present application are mainly described by taking the data migration from a non-PG database to a PG database as an example, the database types of the source database and the target database are not specifically limited in the embodiments of the present application. The data migration method disclosed in the embodiments of the present application is mainly used to improve the data migration efficiency from the source database to the target database when the database types of the source database and the target database are different. For example, the data migration efficiency from a MySQL database to an OpenGauss database and the data migration efficiency from a MySQL database to an Oracle database can be improved.

[0064] It should also be noted that Figure 2 The software structure shown is only an example and does not constitute a limitation. In other embodiments of the present application, Figure 2 The software structure shown may include more or fewer software modules than shown in the figure, or different module settings, etc., which is not limited here.

[0065] It should be noted that the data migration method provided in the embodiments of this application can mainly include two parts. The first part is to read the data to be migrated from the source database and generate the binary data required by the copy from std binary command based on the data to be migrated. The second part is to write the binary data to the target database based on the copy from std binary command, that is, to write the data to be migrated to the target database.

[0066] The following combination Figure 3 The data migration process of this application is illustrated below.

[0067] See also Figure 3 , Figure 3 This is a schematic diagram of a data migration scenario disclosed in the embodiment of this application. Figure 3 As shown, the migration tool in the electronic device can create multiple data reading threads. These multiple data reading threads can concurrently execute select statements to read the data to be migrated from the source database. These multiple data reading threads can then match the data types of the source and target databases, converting the data types of the data to be migrated to the target data type corresponding to the target database. These multiple data reading threads can then generate the binary data required by the copy fromstd binary command based on the target data type to be migrated, and write the generated binary data to a memory queue.

[0068] The migration tool in the electronic device can also create multiple data writing threads. When there is binary data in the memory queue, these multiple data writing threads can read the binary data from the memory queue and then write the corresponding binary data to the target database based on the copy from std binary command.

[0069] For example, the following example description is given with the source database being MySQL and the target database being OpenGauss. Figure 4 , Figure 4 This is another data migration scenario diagram disclosed in the embodiment of this application. Figure 4As shown, an electronic device can use multiple threads to concurrently read the data to be migrated from table 1 (TABLE_1) in a MySQL database. For example, thread 1 reads rows 1-1000 of table 1, thread 2 reads rows 1001-2000 of table 1, and thread 3 reads rows 2001-3000 of table 1. Furthermore, threads 1, 2, and 3 can each generate the binary data required for the copy from std binary command based on the read data to be migrated, and then write the data into a buffer. Subsequently, the electronic device can use multiple threads to concurrently execute the copy from std binary command, writing the binary data in different buffers to an OpenGauss database. This effectively writes the data to be migrated into the OpenGauss database. For example, Binary Data1, Binary Data2, and Binary Data3 in different buffers can be written into the OpenGauss database. It should be understood that a buffer can be a storage variable created by the electronic device, such as a byte array, and can correspond to a buffer in memory. In other words, writing data into a buffer actually means writing data into the memory space corresponding to the buffer.

[0070] In the embodiments of the present application, compared to the CSV text format, the use of a binary format can reduce the storage space (e.g., memory space) occupied by the data and reduce the amount of data transmitted over the network, thereby improving data migration efficiency. Furthermore, the use of the copy from std binary command for writing data is more efficient, further improving data migration efficiency.

[0071] In an embodiment of the present application, the electronic device may create one or more first threads, which may be used to read the data to be migrated from the source database, and may generate binary data required by the copy from stdbinary command based on the data to be migrated, and may place the generated binary data into the target queue. The electronic device may also create one or more second threads, which may be used to read binary data from the target queue, and may write the binary data into the target database based on the copy from std binary command, that is, write the data to be migrated into the target database. Figure 5 The processing flow of the first thread is illustrated by way of example. Figure 6 The processing flow of the second thread is exemplified.

[0072] Based on the above system architecture, please refer to Figure 5 , Figure 5This is a flow chart of a data migration method disclosed in an embodiment of the present application. Figure 5 As shown, the data migration method may include but is not limited to the following steps:

[0073] 501. A first thread reads data to be migrated from a source database.

[0074] In an embodiment of the present application, when performing data migration from a source database to a target database, the electronic device may create one or more first threads. It should be understood that in order to read the data to be migrated in the source database, the one or more first threads may establish a connection with the source database based on the connection information of the source database. The connection information of the source database may include information such as the IP (internet protocol) address, port, user name, and user password corresponding to the source database. In one possible implementation, before performing data migration, the electronic device may receive the connection information of the source database input by the user.

[0075] The data to be migrated may be data in the source database that needs to be migrated to the target database, and may be all or part of the tables in the source database. In one possible implementation, before performing data migration, the electronic device may receive instruction information input by the user, where the instruction information is used to indicate the tables in the source database that need to be migrated to the target database (hereinafter referred to as the data tables to be migrated).

[0076] The first thread can execute a query statement (such as a select statement) to read the data to be migrated from the data table to be migrated. For example, assuming that the data table to be migrated includes data table 1, the first thread can execute a select statement to read one or more tuples from data table 1 each time.

[0077] In an embodiment of the present application, when multiple first threads are used, the multiple first threads can read the data to be migrated from the source database in an inter-table parallel manner, or can read the data to be migrated from the source database in an intra-table parallel manner, or can read the data to be migrated from the source database in an inter-table parallel + intra-table parallel manner. Among them, the inter-table parallel manner refers to multiple first threads reading data from different data tables to be migrated in parallel. The intra-table parallel manner refers to multiple first threads reading different data in the same data table to be migrated in parallel, such as multiple first threads reading data from different shards in the same data table to be migrated in parallel. The inter-table parallel + intra-table parallel manner means that the multiple first threads can be divided into multiple groups, and the multiple first threads included in some groups can read data from different data tables to be migrated in parallel, and the multiple first threads included in other groups can read different data in the same data table to be migrated in parallel.

[0078] In an embodiment of the present application, when adopting intra-table parallelism, or inter-table parallelism + intra-table parallelism, a sharding threshold can be pre-set, and the sharding threshold is used to indicate the size of the shard. For example, the sharding threshold can be set to 1000. In this case, every 1000 tuples in the data table to be migrated can be used as a shard. If the total number of tuples in the data table to be migrated is less than or equal to 1000, all tuples in the data table to be migrated can be used as a shard. If the total number of tuples in the data table to be migrated is greater than 1000, it can be sharded in units of 1000 tuples in sequence. However, for the last shard, the number of tuples may be less than 1000. In this case, the shard can be merged with the previous shard, or the shard can be used as an independent shard. For example, assuming that the total number of tuples in data table 1 is 2500, tuples 1-1000 can be used as the first shard, tuples 1001-2000 as the second shard, and tuples 2001-2500 as the third shard. Alternatively, the second and third shards can be merged, and tuples 1001-2500 can be used as one shard.

[0079] In one possible implementation, the number of first threads can be determined based on at least one of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, and the total data volume of the data tables to be migrated. For example, when using inter-table parallelism, the number of first threads can be the smaller of a first threshold and the number of data tables to be migrated. The first threshold is a preset maximum number of first threads. The first threshold can be determined based on parameters such as the number of CPUs in the electronic device and the number of cores in each CPU, such as the number of CPUs multiplied by the number of CPU cores. Therefore, if the number of data tables to be migrated is less than or equal to the first threshold, the number of first threads can be equal to the number of data tables to be migrated. In this case, each first thread can process one data table to be migrated, thereby improving data migration efficiency. If the number of data tables to be migrated is greater than the first threshold, the number of first threads can be set to the first threshold. This ensures that CPU resources are fully utilized, the number of threads is appropriate, and memory resources are not excessively occupied. For another example, when using intra-table parallelism, the number of first threads can be determined based on the data volume of the data tables to be migrated. For example, a corresponding number of first threads can be preset for each data volume interval. For example, if the amount of data in the data table to be migrated is less than 100GB, the number of first threads can be 2; if the amount of data in the data table to be migrated is greater than or equal to 100GB and less than 500GB, the number of first threads can be 4; if the amount of data in the data table to be migrated is greater than or equal to 500GB and less than 1024GB, the number of first threads can be 6; if the amount of data in the data table to be migrated is greater than 1024GB, the number of first threads can be 8. It should be understood that the above-mentioned method of determining the number of first threads is merely an example and does not constitute a limitation thereto. For another example, when adopting the intra-table parallel method, the electronic device can first determine the number of shards of the data table to be migrated, and then determine the number of first threads based on the number of shards of the data table to be migrated, such as the number of first threads can be equal to the number of shards of the data table to be migrated.

[0080] 502. The first thread converts the data type of the to-be-migrated data into the target data type.

[0081] It should be understood that when performing data migration, the data in the data table to be migrated in the source database is actually copied to the target data table corresponding to the data table to be migrated in the target database. Since the source database and the target database may belong to different databases, the definitions of the target data table and the corresponding data table to be migrated may be different. The target data table may be a data table corresponding to the data table to be migrated created based on the characteristics of the target database. The data table to be migrated and the corresponding target data table may include the same attributes (fields), and each attribute or each column in the data table to be migrated may correspond to a data type, and each attribute or each column in the target data table may also correspond to a data type.

[0082] In an embodiment of the present application, since the source database and the target database may support or include different data types, for the same attribute, the data type corresponding to the attribute in the data table to be migrated (referred to as the source data type) and the data type corresponding to the target data table (referred to as the target data type) may be different. The data types of the attribute values ​​are different, the storage space occupied by the attribute values ​​may be different, and the binary data corresponding to the attribute values ​​may also be different. Therefore, in order to facilitate the subsequent call of the copy from std binary command to write the data to be migrated into the target database, the first thread can convert the data type of the data to be migrated into the target data type, that is, the data type of each attribute value in the data table to be migrated can be converted into the data type of the corresponding attribute in the target data table. Of course, if the data type of an attribute value in the data table to be migrated is the same as the data type of the corresponding attribute in the target data table, there is no need to convert the data type, and the binary data required by the copy from std binary command can be generated directly based on the attribute value of the source data type.

[0083] In an embodiment of the present application, the first thread can obtain the data type corresponding to each attribute in the data table to be migrated, and can also obtain the attribute type corresponding to each attribute in the target data table. For example, the data returned by the select statement for the data table to be migrated may include the data type corresponding to each attribute in the data table to be migrated. Therefore, the first thread can obtain the data type corresponding to each attribute in the data table to be migrated from the data returned by the select statement. For another example, the first thread can also directly obtain the metadata information of the data table to be migrated, and the metadata information may include the name of each attribute in the data table to be migrated, and the data type corresponding to each attribute. Similarly, the first thread can also directly obtain the metadata information of the target data table, and the metadata information may include the name of each attribute in the target data table, and the data type corresponding to each attribute.

[0084] For example, assume that the data table to be migrated and the corresponding target data table both include attribute 1, attribute 2, and attribute 3. In the data table to be migrated, the data type corresponding to attribute 1 is data type 1, the data type corresponding to attribute 2 is data type 2, and the data type corresponding to attribute 3 is data type 3. In the corresponding target data table, the data type corresponding to attribute 1 is data type 4, the data type corresponding to attribute 2 is data type 5, and the data type corresponding to attribute 3 is data type 6. Data types 1-6 are all different. Therefore, for a tuple in the data table to be migrated, the first thread may convert the data type of the attribute value corresponding to attribute 1 in the tuple from data type 1 to data type 4, the data type of the attribute value corresponding to attribute 2 in the tuple from data type 2 to data type 5, and the data type of the attribute value corresponding to attribute 3 in the tuple from data type 3 to data type 6. For example, assuming that the source database is Oracle and the target database is OpenGauss, data type 1 may be Oracle's binary_float type, and data type 4 may be OpenGauss's float type. Alternatively, data type 1 may be Oracle's binary_double type, and data type 4 may be OpenGauss's double type. Alternatively, data type 1 may be a bit(1) type, and data type 4 may be a boolean type. A bit(1) type occupies 1 bit of storage space and can have a value of 0 or 1, while a boolean type occupies 1 byte of storage space and can have a value of true or false. In this case, a bit(1) type 1 can be converted to a boolean type true, and a bit(1) type 0 can be converted to a boolean type false, based on a preset relationship.

[0085] 503. The first thread generates corresponding binary data based on the to-be-migrated data of the target data type.

[0086] After the first thread converts the data type of the to-be-migrated data into the target data type, it may generate corresponding binary data based on the to-be-migrated data of the target data type. The binary data may be imported using the copy from std binary command.

[0087] In order to better understand the embodiments of the present application, the format of the binary data required by the copy from std binary command is exemplified below. The binary data required by the copy from std binary command may include three parts in sequence: a file header, a tuple (row data), and a file tail. Among them, the file header may include a fixed area of ​​15 bytes and a variable-length extension area. The fixed area of ​​15 bytes may include a string marker bit and a flag bit. The string marker bit may be an 11-byte sequence "PGCOPY\nFF\r\n\0", and the flag bit may be a 32-bit Integer (integer) type. In the embodiments of the present application, its value may be 0. In the embodiments of the present application, the variable-length extension area may be a 32-bit Integer type, and its value may be 0. The tuple part corresponds to the data to be migrated of the target data type. Each tuple may start with a 16-bit short (short integer) type data, whose value represents the number of columns, that is, the number of attributes in the tuple. After the short type data, the data corresponding to each attribute value in the tuple may be included in sequence. The data corresponding to each attribute value may include two parts: one part is a 32-bit integer, which is used to indicate the number of bytes occupied by the following attribute value, and the other part is the attribute value corresponding to the number of bytes. The end of the file may include a 16-bit integer, whose value may be -1. It should be noted that the 32-bit integer used to indicate the number of bytes occupied by the following attribute value may be -1, in which case it indicates that the following attribute value is null and does not need to be followed by the attribute value of the corresponding number of bytes.

[0088] For example, assume the table to be migrated contains three attributes, and the target data type to be migrated includes tuple 1 (attribute value 1, attribute value 2, attribute value 3), tuple 2 (attribute value 4, attribute value 5, attribute value 6), and tuple 3 (attribute value 7, attribute value 8, attribute value 9). The data types of attribute values ​​1-9 are converted to the data types of the corresponding attributes in the target table. In this case, the binary data required by the copy from std binary command generated based on tuples 1-3 can be as follows:

[0089] PGCOPY\nFF\r\n\0 (11-byte string marker) + 0 (32-bit Integer type) + 0 (32-bit Integer type) + 3 (16-bit short type) + the number of bytes corresponding to attribute value 1 + attribute value 1 + the number of bytes corresponding to attribute value 2 + attribute value 2 + the number of bytes corresponding to attribute value 3 + attribute value 3 + 3 (16-bit short type) + the number of bytes corresponding to attribute value 4 + attribute value 4 + the number of bytes corresponding to attribute value 5 + attribute value 5 + the number of bytes corresponding to attribute value 6 + attribute value 6 + 3 (16-bit short type) + the number of bytes corresponding to attribute value 7 + attribute value 7 + the number of bytes corresponding to attribute value 8 + attribute value 8 + the number of bytes corresponding to attribute value 9 + attribute value 9 + -1 (16-bit integer).

[0090] It should be understood that the binary data required by the "copy from std binary" command is illustrated above only as an example of the overall structure of the corresponding binary data, to facilitate understanding. However, in practice, all of the above data can be stored or represented in a corresponding binary format. For example, both the byte count and the attribute value corresponding to an attribute value can be represented in binary format.

[0091] 504. The first thread writes the corresponding binary data into the Buffer.

[0092] After the first thread generates corresponding binary data based on the data to be migrated of the target data type, the corresponding binary data may be written into a buffer, where the buffer may be a byte array.

[0093] 505. When the Buffer is full, the first thread puts the Buffer into the target queue.

[0094] After the first thread writes the corresponding binary data into the buffer, it can determine whether the buffer is full. If the buffer is full, the first thread can place the buffer into the target queue so that the second thread can process it. If the buffer is not full, the first thread can continue to read the data to be migrated from the data table to be migrated, then generate the corresponding binary data based on the data to be migrated, and then write the generated binary data into the buffer until the buffer is full or all the data in the data table to be migrated has been read. In one possible implementation, the target queue can be a first-in-first-out (FIFO) queue.

[0095] It should be understood that, when the Buffer is a byte array, the first thread puts the Buffer into the target queue, that is, the first thread puts the byte array into the target queue.

[0096] Based on the above system architecture, please refer to Figure 6 , Figure 6 This is a flow chart of another data migration method disclosed in the embodiment of this application. Figure 6 As shown, the data migration method may include but is not limited to the following steps:

[0097] 601. The second thread obtains the Buffer from the target queue.

[0098] In an embodiment of the present application, when performing data migration from a source database to a target database, the electronic device may create one or more second threads. It should be understood that in order to write the data to be migrated into the target database, the one or more second threads may establish a connection with the target database based on the connection information of the target database. The connection information of the target database may include information such as the IP (internet protocol) address, port, user name, and user password corresponding to the target database. In one possible implementation, before performing data migration, the electronic device may receive the connection information of the target database input by the user.

[0099] If a buffer exists in the target queue, the second thread can obtain the buffer from the target queue. In this embodiment of the present application, a data element in the target queue can be a buffer. It should be understood that if a buffer does not exist in the target queue, the second thread can wait, and then, when a buffer is placed in the target queue, it can obtain the buffer from the target queue for processing.

[0100] In a possible implementation, the target queue may be a first-in-first-out queue.

[0101] In one possible implementation, the number of second threads can be determined based on at least one of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, and the total amount of data in the data tables to be migrated. For example, the number of second threads can be the smaller value between the second threshold and the number of data tables to be migrated. The second threshold is the preset maximum number of second threads, and the second threshold can be determined based on parameters such as the number of CPUs of the electronic device and the number of cores of each CPU, such as being equal to the number of CPUs * the number of CPU cores. For another example, the number of second threads can be determined based on the amount of data in the data tables to be migrated, such as for every two data volume intervals, the corresponding number of second threads can be preset.

[0102] In another possible implementation, the number of second threads can be determined based on the number of first threads. Specifically, the electronic device can monitor the time required for the first thread to read the data to be migrated from the source database and fill a buffer and put it into the target queue (hereinafter referred to as T1), and can also monitor the time required for the second thread to obtain a buffer from the target queue and write the data to be migrated in the buffer into the target database (hereinafter referred to as T2). The number of first threads / the number of second threads can be equal to Indicates rounding up. In this way, the number of buffers placed by the first thread in the target queue can be greater than the number of buffers taken out of the target queue by the second thread in the same period of time, or the number of buffers placed by the first thread in the target queue can be equal to the number of buffers taken out of the target queue by the second thread, thereby ensuring that the second thread always has full buffers to process, thereby improving the efficiency of data migration. Moreover, in this way, the number of first threads and the number of second threads are more appropriate, and while ensuring the efficiency of data migration, it is possible to avoid occupying additional CPU resources and memory resources. It should be understood that the above-mentioned method of determining the number of second threads is only an exemplary description and does not constitute a limitation thereto.

[0103] 602. The second thread writes the data to be migrated in the buffer to the target database based on the copy from std binary command.

[0104] Specifically, for each Buffer, the second thread can call the corresponding copy from std binary command and can send the corresponding Buffer to the target database so as to write the data to be migrated in the Buffer to the target database. For example, assuming that the data to be migrated in the Buffer needs to be written to the corresponding data table 1 in the target database, the corresponding copy from std binary command can be the name of copy data table 1 from std binary, and the second thread can call the command. In addition, the second thread can send the corresponding Buffer to the target database. Accordingly, the target database can receive the Buffer, and then the target database can write the data to be migrated in the Buffer to data table 1 based on the name of copy data table 1 from std binary command. It should be understood that the std in the copy from std binary command represents standard input.

[0105] In one possible implementation, the copy from std binary command can include a target parameter, which indicates the buffer to be written to the target database. This target parameter can be the storage address of the buffer. Thus, when the second thread calls the corresponding copy from std binary command, it can use the target parameter carried in the command to send the corresponding buffer to the target database, thereby writing the data to be migrated in the buffer to the target database.

[0106] In the above process, the electronic device can read the data to be migrated from the source database, and can convert the data type of the data to be migrated into the target data type corresponding to the target data table. Then, the electronic device can generate the binary data required by the copy from std binary command based on the target type of the data to be migrated. Afterwards, the electronic device can write the data to be migrated in the binary data to the target database based on the copy from std binary command. It can be seen that when performing data migration, the electronic device imports data in binary mode based on the copy from std binary command. This method has a high import efficiency, which can improve the overall efficiency of data migration.

[0107] Based on the above system architecture, please refer to Figure 7 , Figure 7 This is a flow chart of another data migration method disclosed in an embodiment of the present application. This data migration method can be applied to electronic devices or migration tools in electronic devices. This data migration method may include but is not limited to the following steps:

[0108] 701. Read the data to be migrated from the data table to be migrated in the source database.

[0109] 702. Generate binary data based on the data to be migrated.

[0110] Since the database type of the source database is different from the database type of the target database, in order to generate accurate binary data, the electronic device can first convert the data type in the data to be migrated into the corresponding target data type, and then generate corresponding binary data based on the data to be migrated of the target data type. Among them, the target data type is the data type corresponding to the data to be migrated in the target database. It should be understood that for the data table to be migrated in the source database, the target database includes a target data table corresponding to the data table to be migrated. When performing data migration, that is, migrating the data in the data table to be migrated to the corresponding target data table. Therefore, when performing data type conversion, the data type of each attribute value in the data table to be migrated can be converted into the data type corresponding to each attribute in the corresponding target data table.

[0111] In one possible implementation, to improve data migration efficiency, multiple first threads can be created. The multiple first threads can then read the data to be migrated from the to-be-migrated data table in the source database, convert the data type of the read data to be migrated to the corresponding target data type, and generate binary data required by the target command based on the data to be migrated of the target data type.

[0112] To accurately convert data types, one possible implementation involves obtaining metadata information for the table to be migrated and the target table. The metadata for the table to be migrated may include the data type corresponding to each attribute in the table to be migrated, while the metadata for the target table includes the data type corresponding to each attribute in the target table. Subsequently, based on the metadata for the table to be migrated and the target table, the data type of each attribute value in the data to be migrated may be converted to the corresponding target data type.

[0113] 703. Writing binary data to a target database based on a target command, the database type of the source database is different from the database type of the target database.

[0114] In a possible implementation, writing binary data into a target database based on a target command specifically includes: sending a target command to the target database, where the target command carries a target parameter, where the target parameter is used to indicate the binary data, and where the target command is used to instruct the target database to write the binary data into the target database.

[0115] In one possible implementation, after generating binary data from the data to be migrated, the data migration method may further include: writing the binary data into a buffer; when the buffer is full, placing the buffer into a target queue; writing the binary data into a target database based on a target command includes: obtaining the buffer from the target queue; and writing the binary data stored in the buffer into the target database based on a target command.

[0116] In one possible implementation, to improve data migration efficiency, multiple second threads may be created, and multiple buffers may be obtained from the target queue. The binary data stored in the multiple buffers may then be written to the target database based on the target command using the multiple second threads.

[0117] In a possible implementation, the number of the first threads and the number of the second threads may be determined according to at least one of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, and the total amount of data in the data tables to be migrated.

[0118] In a possible implementation, the number of the first threads is set to be proportional to the number of the second threads, and the ratio of the number of the first threads to the number of the second threads is equal to T1 is the time required for a first thread to read the data to be migrated from the source database, fill a buffer and put it into the target queue. T2 is the time required for a second thread to obtain a buffer from the target queue and write the binary data stored in the buffer into the target database.

[0119] It should be noted that the relevant information (ie, the same information or similar information) and related descriptions in the above different embodiments can refer to each other.

[0120] It should be understood that the above Figure 5 、 Figure 6 or Figure 7 The above processing flow is illustrated by taking an electronic device or a module of an electronic device (such as a thread in an electronic device) as an example of the execution subject of the interactive diagram, but this application does not limit the execution subject of the interactive diagram. For example, Figure 5 、 Figure 6 or Figure 7 The electronic device may also be a chip, a chip system, a processor (such as a CPU), etc. that supports the electronic device to implement the method, or a logic module or software that can implement all or part of the functions of the electronic device.

[0121] Based on the above system architecture, please refer to Figure 8 , Figure 8800 is a schematic diagram of the structure of an electronic device disclosed in an embodiment of the present application. The electronic device 800 may include: a processor 801, a communication interface 802, and a memory 803. The processor 801, the communication interface 802, and the memory 803 may be interconnected or connected to each other via a bus 804.

[0122] Exemplarily, the memory 803 is used to store computer programs and data of the electronic device 800. The memory 803 may include, but is not limited to, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM), or portable read-only memory (CD-ROM). The communication interface 802 is used to support the electronic device 800 to communicate, such as receiving or sending data.

[0123] Exemplarily, the processor 801 may be a central processing unit (CPU), a complex programmable logic device (CPLD), a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), or other programmable logic device (PLD), a transistor logic device (TLD), a hardware component, or any combination thereof. The processor may also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, and the like.

[0124] In one embodiment, the electronic device 800 may be the above electronic device, and the processor 801 may be used to read the program stored in the memory 803 and execute the above Figure 5 、 Figure 6 or Figure 7 For the operations performed by the electronic device or components in the electronic device in the illustrated method embodiment, reference may be made to the above related descriptions and will not be repeated here in detail.

[0125] It should be noted that Figure 8 The electronic device 800 shown is only one implementation of the embodiment of the present application. In actual applications, the electronic device 800 may also include more or fewer components, which is not limited here.

[0126] An embodiment of the present application further discloses a computer-readable storage medium having instructions stored thereon, which, when executed, execute the method in the above method embodiment.

[0127] The embodiments of the present application further disclose a computer program product comprising instructions, which, when executed, perform the method in the above method embodiments.

[0128] Obviously, the embodiments described above are only some of the embodiments of this application, and not all of them. Reference to "embodiments" herein means that the specific features, structures, or characteristics described in connection with the embodiments may be included in at least one embodiment of the present application. The appearance of this phrase in various places in the specification does not necessarily refer to the same embodiment, nor does it refer to independent or alternative embodiments that are mutually exclusive with other embodiments. It is understood, both explicitly and implicitly, that the embodiments described herein can be combined with other embodiments. All other embodiments derived by persons of ordinary skill in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application. In the specification, claims, and accompanying drawings of this application, the terms "first," "second," "third," and so on are used to distinguish between different objects, not to describe a specific order. Furthermore, the terms "including," "having," and any variations thereof are intended to cover non-exclusive inclusions. For example, a list of steps or elements may be included, or alternatively, steps or elements not listed may be included, or alternatively, other steps or elements inherent to the process, method, product, or device may be included. It can be understood that the equal sign of the above conditional judgment can be greater than one end or less than one end. For example, the above conditional judgment of a threshold being greater than, less than or equal to can also be changed to a conditional judgment of the threshold being greater than or equal to or less than the threshold, which is not limited here.

[0129] It will be appreciated that only the parts relevant to the present application, not all, are shown in the accompanying drawings. It will be appreciated that some exemplary embodiments are described as processes or methods depicted as flow charts. Although the flow charts describe the various operations (or steps) as sequential processes, many of the operations therein can be implemented in parallel, concurrently, or simultaneously. In addition, the order of the various operations can be rearranged. When its operation is completed, the process can be terminated, but can also have additional steps not included in the accompanying drawings. The process can correspond to a method, function, procedure, subroutine, subprogram, etc.

[0130] As used in this specification, the terms "component," "module," "system," "unit," and the like are used to refer to computer-related entities, hardware, firmware, a combination of hardware and software, software, or software in execution. For example, a unit can be, but is not limited to, a process running on a processor, a processor, an object, an executable file, an execution thread, a program, and / or distributed between two or more computers. In addition, these units can be executed from various computer-readable media having various data structures stored thereon. For example, a unit can communicate through local and / or remote processes based on signals having one or more data packets (e.g., data from a second unit interacting with another unit in a local system, a distributed system, and / or a network. For example, the Internet interacts with other systems via signals).

[0131] The specific implementation methods described above further illustrate the purpose, technical solutions and beneficial effects of this application. It should be understood that the above description is only the specific implementation methods of this application and is not intended to limit the scope of protection of this application. Any modifications, equivalent replacements, improvements, etc. made on the basis of the technical solutions of this application should be included in the scope of protection of this application.

Claims

1. A data migration method, characterized in that: include: Reading the data to be migrated from the data table to be migrated in the source database through multiple first threads; Converting the data type in the to-be-migrated data into the corresponding target data type through the multiple first threads; The target data type is the data type corresponding to the data to be migrated in the target database; generating binary data required by a target command based on the to-be-migrated data of the target data type through the multiple first threads; Writing the binary data into a buffer; When the buffer is full, the buffer is placed into the target queue; Get a plurality of buffers from the target queue; writing the binary data stored in the plurality of buffers into a target database based on the target command through a plurality of second threads; wherein the database type of the source database is different from the database type of the target database; The target database is a PG database, which includes a PostgreSQL database and a database developed based on the PostgreSQL database; the source database is a non-PG database; the target command is a data write command for the PG database, which is used to write binary format data into a table in the PG database, and the number of the first threads is set in a ratio to the number of the second threads, and the ratio of the number of the first threads to the number of the second threads is equal to T1 is the time required for a first thread to read the data to be migrated from the source database, fill a buffer, and put it into the target queue; T2 is the time required for a second thread to obtain a buffer from the target queue and write the binary data stored in the buffer into the target database; When the multiple first threads read the data to be migrated from the source database in an inter-table parallel manner, the number of first threads is the smaller value between a first threshold and the number of the data tables to be migrated, wherein the first threshold is a preset maximum number of first threads.

2. The method according to claim 1, characterized in that Writing the binary data into the target database based on the target command includes: A target command is sent to the target database, where the target command carries a target parameter, where the target parameter is used to indicate the binary data, and the target command is used to instruct the target database to write the binary data into the target database.

3. The method according to claim 1, characterized in that The number of the first threads and the number of the second threads are determined according to at least one of the following: the number of data tables to be migrated, the number of tuples included in each data table to be migrated, and the total amount of data in the data tables to be migrated.

4. The method according to any one of claims 1 to 3, characterized in that The method further comprises: Obtaining metadata information of the data table to be migrated, where the metadata information of the data table to be migrated includes a data type corresponding to each attribute in the data table to be migrated; Acquire metadata information of a target data table in the target database, where the metadata information of the target data table includes a data type corresponding to each attribute in the target data table; The converting the data type in the to-be-migrated data into the corresponding target data type includes: Based on the metadata information of the data table to be migrated and the metadata information of the target data table, the data type of each attribute value in the data to be migrated is converted into a corresponding target data type.

5. An electronic device, characterized in that: The electronic device includes a processor and a memory, and the processor calls a computer program or computer instruction stored in the memory to implement the method according to any one of claims 1 to 4.

Images