A fragmented migration method for data of tens of billions

Abstract

This invention discloses a sharding migration method for database migration of billions of records, belonging to the field of database migration technology. The method specifically involves: dividing the source data table to be migrated into several data shards; migrating data from the source data table to the target data table within each shard; and, if the migration of a particular record or shard fails, customizable shard breakpoints are used for continued migration. This invention ensures a high degree of consistency between the source and target databases; moreover, it is sustainable, guaranteeing the effectiveness of data migration before an anomaly occurs and allowing subsequent data migration to continue after an anomaly is handled.

Description

Technical Field

[0001] This invention relates to the field of database migration technology, and in particular to a sharding migration method for billions of data points. Background Technology

[0002] For a long time, Oracle databases have been widely used in the State Grid information system due to their comprehensive functions, stable performance, rich teaching materials, and comprehensive related tools, forming an information and digital system architecture with Oracle as an important component. After three years of construction, the Energy Internet Power Marketing System (Marketing 2.0), based on the State Grid cloud platform and adopting a microservice architecture, has been basically completed and is gradually being promoted and operated throughout the province. The current version includes 1610 business products, 178 query functions, 143 data microservices, 122 reports, 7 management dashboards, 245 business microservices, and 7649 related microservice interfaces. When selecting a database for Marketing 2.0 in the early stages of construction, factors such as database stability, massive data access capabilities, team application experience, and project time were weighed, and Oracle database, which the project team has long-term application experience with, was chosen as the main database for Marketing 2.0.

[0003] With the successful launch in Jiangsu, Zhejiang, Anhui, Shandong, Fujian, and other regions, a wealth of practical experience and firsthand knowledge regarding the digitalization of customer service has been accumulated during the actual operation of Marketing 2.0. Simultaneously, as the digitalization of marketing operations progresses, various new demands continue to emerge. After comprehensive evaluation, it was found that Oracle databases have significant limitations in three areas: business support, system development, and operation and maintenance, severely hindering the development of Marketing 2.0.

[0004] Promoting the localization of Oracle databases is essential to supporting the development of marketing operations. As customer-facing systems and internal management systems, they differ significantly in operational characteristics. Marketing 2.0 is the first system built around a customer-centric approach. Customer-facing systems handle massive, high-frequency, parallel, and discrete business processes, while management-facing systems focus more on overall, global monitoring, evaluation, and statistics, with a much lower user access frequency. Actual operational monitoring data from Jiangsu Company's Marketing 2.0 fully illustrates this difference. The Marketing 2.0 middleware service is called an average of 6 million times daily, and the top 20 most accessed services are all related to "getting electricity." For example, the three most frequently accessed services are the direct electricity bill payment platform interface, the customer 360-degree view, and the contract information interface. This phenomenon stems from the inherently distributed and parallel nature of customer needs. For instance, Nanjing customers do not need to negotiate and queue with Suzhou customers to pay their electricity bills; they choose their payment times based on their individual work and life needs. This demonstrates that customer service operations require robust parallel processing capabilities across all levels of the system. However, Oracle employs a centralized architecture, causing a large number of concurrent requests from the front end to become sequential requests when they reach the database, significantly impacting overall system response efficiency. To address this, the middleware service had to construct a large-scale cache to reduce database access and improve response speed. However, this inevitably increased the complexity of system development and maintenance, and also led to greater resource consumption, further hindering the ability to respond quickly to business changes.

[0005] For Marketing 2.0, the localization of Oracle databases involves three aspects: first, replacing the Oracle database with a domestic relational database; second, improving the design methodology, system architecture, software model, and data model of Marketing 2.0 to fully leverage the technological advantages of domestic databases deployed on cloud platforms and more efficiently support customer service operations; and third, developing cloud technology components similar to Oracle's peripheral tools, such as cloud technology components similar to database replication software OGG, to meet the requirements of system operation and maintenance. When replacing Oracle databases with domestic relational databases, the primary task is migrating historical data from the original system. The Marketing 2.0 system faces 450 million electricity customers nationwide and billions of yuan worth of marketing business data across the province. Achieving a reliable, full, and efficient migration of billions of data points is undoubtedly a challenge. Therefore, a sharding migration method for billions of data points is needed to solve the database migration problem. Summary of the Invention

[0006] The purpose of this invention is to propose a fragmentation migration method for data of tens of billions, comprising the following steps:

[0007] The source data table to be migrated is divided into several data slices;

[0008] Migrate data from a data slice from the source table to the target table; and

[0009] When the migration of a certain data item or a data shard fails, a customizable shard breakpoint resume migration can be performed.

[0010] The specific steps for splitting the source data table to be migrated into several data slices are as follows:

[0011] Guided by the database syntax difference index system, the table structure matching between the migration data table in the source database and the target table in the target database is completed;

[0012] After the matching is confirmed, read the source data table to include basic information such as data volume, keywords or primary indexes, table structure, and size of each record.

[0013] Calculate the fragment size and total number of fragments based on the pre-configured fragmentation parameters, and record them in the fragmentation parameter configuration. Fragment size S slc The calculation method is as follows:

[0014]

[0015] Among them, R avl This refers to the available memory space of the data migration server, S. rcd N is the size of each record. slc Number of shards.

[0016] The specific steps to migrate data from a data slice from the source table to the target table are as follows:

[0017] First, calculate the data range in the source table based on the shard size and the corresponding sequence number of the shard, and read the data corresponding to the shard from the source database; then, complete the type conversion according to the matching table structure information; finally, write the data into the target database table one row at a time or in batches.

[0018] The specific steps for customizable fragment breakpoint resume migration are as follows:

[0019] Step A1: Select a database based on the database unified access interface information;

[0020] Step A2: Based on table migration information or database syntax difference index system, select the database migration table and match it with the target database;

[0021] Step A3: Set the sharding parameters to obtain the sharding parameter configuration;

[0022] Step A4: Calculate data shards based on sharding parameter configuration, table migration information, and Oracle database;

[0023] Step A5: Record table data migration, generate data migration logs and table migration information; at the same time, determine whether the table migration is complete. If yes, end this breakpoint resume migration; otherwise, proceed to step A6.

[0024] Step A6: Read sharded data from the Oracle database;

[0025] Step A7: Write data to the target database shards, handle exceptions, and generate data migration logs;

[0026] Step A8: Determine whether the data migration was successful. If so, record the table data migration, generate a data migration log and table migration information. If not, handle the exception and generate a data migration log.

[0027] A sharding migration device for billions of data points includes:

[0028] The sharding module is used to divide the source data table to be migrated into several data shards;

[0029] The migration module is used to migrate data in a data slice from the source data table to the target data table;

[0030] The migration continuation module is used to perform customizable fragment breakpoint continuation migration when a certain data entry or fragment fails to migrate.

[0031] The specific steps of the sharding module in dividing the source data table to be migrated into several data slices are as follows:

[0032] Guided by the database syntax difference index system, the table structure matching between the migration data table in the source database and the target table in the target database is completed;

[0033] After the matching is confirmed, read the source data table to include basic information such as data volume, keywords or primary indexes, table structure, and size of each record.

[0034] Calculate the fragment size and total number of fragments based on the pre-configured fragmentation parameters, and record them in the fragmentation parameter configuration. Fragment size S slc The calculation method is as follows:

[0035]

[0036] Among them, R avl This refers to the available memory space of the data migration server, S. rcd N is the size of each record. slc Number of shards.

[0037] The migration module performs the following steps to migrate data from a data slice from the source table to the target table:

[0038] First, calculate the data range in the source table based on the shard size and the corresponding sequence number of the shard, and read the data corresponding to the shard from the source database; then, complete the type conversion according to the matching table structure information; finally, write the data into the target database table one row at a time or in batches.

[0039] The specific steps for customizable fragment breakpoint resume migration in the resume migration module are as follows:

[0040] Step A1: Select a database based on the database unified access interface information;

[0041] Step A2: Based on table migration information or database syntax difference index system, select the database migration table and match it with the target database;

[0042] Step A3: Set the sharding parameters to obtain the sharding parameter configuration;

[0043] Step A4: Calculate data shards based on sharding parameter configuration, table migration information, and Oracle database;

[0044] Step A5: Record table data migration, generate data migration logs and table migration information; at the same time, determine whether the table migration is complete. If yes, end this breakpoint resume migration; otherwise, proceed to step A6.

[0045] Step A6: Read sharded data from the Oracle database;

[0046] Step A7: Write data to the target database shards, handle exceptions, and generate data migration logs;

[0047] Step A8: Determine whether the data migration was successful. If so, record the table data migration, generate a data migration log and table migration information. If not, handle the exception and generate a data migration log.

[0048] An electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the various steps of a fragmented migration method for billions of data points.

[0049] A storage medium storing a computer program that, when executed by a processor, implements the steps of a fragmented migration method for billions of data points.

[0050] The beneficial effects of this invention are as follows:

[0051] 1. This invention can ensure a high degree of consistency between the source database data and the target database data;

[0052] 2. This invention can guarantee support for common domestic databases and is expandable, supporting new databases through configuration; at the same time, it can widely support conversion between Oracle and different databases in terms of data type, length, precision, scale, etc., without discrimination, thus improving its versatility.

[0053] 3. The data migration of the present invention is highly efficient, ensuring not only the speed of migration but also its reliability;

[0054] 4. This invention is sustainable, ensuring the effectiveness of data migration before an anomaly occurs, and continuing to migrate subsequent data after the anomaly is handled. Attached Figure Description

[0055] Figure 1 This is a diagram illustrating data migration based on data access.

[0056] Figure 2 This is a schematic diagram of the data migration process based on data sharding.

[0057] Figure 3 This is a schematic diagram of the slicer's working process;

[0058] Figure 4 This is a schematic diagram of the breakpoint resume process based on fragmentation;

[0059] Figure 5 This is a schematic diagram of a customizable segmented breakpoint resuming process. Detailed Implementation

[0060] This invention proposes a fragmented migration method for data of tens of billions. The invention will be further described below with reference to the accompanying drawings and specific embodiments.

[0061] Figure 1 This is a schematic diagram of data migration based on data access. The method is as follows: with the help of all databases' support for programming languages, the programming language is used as the data migration medium to convert the data in the source database into the programming type, and then into the corresponding type of data in the target database, that is, DB -> Program -> DB.

[0062] Figure 2 This diagram illustrates the data migration process based on sharding. In the Marketing 2.0 system, some data tables are unique, with exceptionally large amounts of data, reaching billions or even tens of billions of rows. Such a massive amount of data cannot be loaded into memory all at once for data migration; it requires phased and batch migration, necessitating the development of unique migration strategies to improve the synchronization efficiency of individual tables.

[0063] To meet the above requirements, a "data fragmentation migration method" is constructed, modeled after the packet switching rules used in computer network data transmission. The implementation is as follows: First, a fragmenter is built to split the massive amount of single-table data into several fragments based on the primary key, according to the computing power of the migrating computer. The data volume of each fragment should not be too large, just enough to meet the requirements of a single migration. Data migration is performed on a fragment-by-fragment basis, achieving a "break-down" migration of large single-table data. Simultaneously, a multi-threaded synchronous migration strategy can be implemented to achieve parallel migration and improve efficiency. Finally, upon arrival at the target database, the data order is restored in the target database based on the primary key of the original table, ensuring consistency between the original and target tables.

[0064] Figure 3 This is a schematic diagram of the sharding process. The sharding is responsible for dividing the source data table to be migrated into several data slices, preparing the data for subsequent sharding-based data migration methods. First, guided by the database syntax difference index system, the table structure between the migration data table in the source database and the target table in the target database is matched. After manual modification and confirmation, the basic information of the source data table is read: data volume, key or primary index, table structure, size of each record, etc. Then, based on the pre-configured "sharding parameter configuration" (including available memory space, etc.), the shard size (the number of records allowed in each shard) and the total number of shards are calculated and recorded in the "sharding parameter configuration".

[0065] Data shard size refers to the number of records contained in each shard, and it is a key factor in determining data migration. Where R avl This refers to the available memory space of the data migration server, S. rcd N is the size of each record. slc Number of shards.

[0066] With a relatively fixed amount of available memory on the data migration server, the number of shards is the only customizable factor. More shards allow for more parallel data migration processes, requiring more resources for process control and allocation, but each shard contains fewer data records, resulting in fewer records affected by migration anomalies. Conversely, fewer shards result in fewer data migration processes, less resource consumption, but larger shards, leading to a greater number of records affected by migration anomalies.

[0067] Figure 4This diagram illustrates a shard-based breakpoint resume migration process. Using shards as the unit, combined with migration logs recorded at the shard level and rollback technology, the shard where the anomaly occurred can be precisely located. When a migration breakpoint occurs, the migration prior to the breakpoint is recorded in the migration log. When the breakpoint conditions are resolved, the migration process is reactivated. By querying the migration log, the shard at the time of the breakpoint will be rolled back, and then the migration will restart from that shard, thus achieving the breakpoint resume migration function.

[0068] Figure 5 This is a schematic diagram of a customizable sharded breakpoint resume migration process. First, relying on the "Unified Database Access Interface Information," the source database and target database are selected from the databases supported by the "Unified Database Access Interface." Then, relying on the support of the "Unified Database Access Interface," the system information of the source database is accessed to obtain all database tables, and the data tables to be migrated this time are selected (all are selected by default).

[0069] Matching the source and target tables during data migration can be divided into two scenarios: First, the target table does not exist and needs to be automatically created during the migration. In this case, if there are "table migration information" records from successful migration cases of similar target databases in other provinces, the corresponding target table structure information can be obtained from the "table migration information" to generate the table creation SQL, which can be executed before the data migration. The table name and field names will be the same as the source table. If there are no "table migration information" records, the table structure of the target table corresponding to the source table, such as field types, lengths, and longitudes, can be generated under the guidance of the "database syntax difference index system." After confirmation, the table creation SQL can be generated to complete the creation of the target table. Second, the target table is created before the data migration. If a table with the same name already exists in the target database, the data field names can be matched between each source table and the target table. Of course, if the target database has "table migration information," the existing matching relationship can be directly referenced.

[0070] After matching the source table with the target table, set the sharding parameters according to the characteristics of the migration server; calculate the shard size and data range of each data table based on the sharding parameters and the structure information of each data table; perform data migration for each data table in sequence (the data migration process is the same as the breakpoint resume migration process); after the table data is successfully migrated, the table migration information corresponding to the target database must be recorded for reference by other network provinces' data migration; then proceed to the next data table migration.

[0071] In this way, the strategy for successful data migration between Oracle and domestic databases is stored as knowledge in the form of "table migration information." If other provinces or regions subsequently need to migrate data using the same database, they only need to reference it without further configuration. To better suit the characteristics of data migration between databases in the Marketing 2.0 system, detailed configuration information for each successful migration of more than 5,000 data tables in the Marketing 2.0 system will be recorded so that it can be directly applied when migrating the same database in the future.

[0072] This embodiment also includes an electronic device and a storage medium. The electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the various steps of the fragmentation migration method for billions of data points. The storage medium stores a computer program thereon. When the computer program is executed by the processor, it implements the various steps of the fragmentation migration method for billions of data points.

[0073] This invention can ensure a high degree of consistency between the source database data and the target database data, and has scalability and universality; it not only guarantees the speed of migration, but also the reliability of migration; it can also guarantee the effectiveness of data migration before an anomaly occurs, and continue to migrate subsequent data after the anomaly is handled.

[0074] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code. The solutions in the embodiments of this application can be implemented in various computer languages, such as the object-oriented programming language Java and the interpreted scripting language JavaScript.

[0075] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0076] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0077] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0078] Although preferred embodiments of this application have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments as well as all changes and modifications falling within the scope of this application.

[0079] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

Claims

1. A method for sharding migration of data at a scale of 10 billion, characterized in that, The method comprises the following steps: cutting the source data table to be migrated into a plurality of data slices; The specific steps of cutting the source data table to be migrated into a plurality of data slices are as follows: under the guidance of the database syntax difference index system, matching the table structure between the migration data table in the source database and the target table in the target database is completed; after the matching is completed and confirmed, the basic information of the source data table including the data volume, the keyword or the primary index, the table structure and the size of each record is read; According to the pre-configured slice parameter, the slice size and the total number of slices are calculated, recorded in the slice parameter configuration, and the slice size S slc The calculation method is as follows: wherein R avl is the size of the available memory space of the data migration server, S rcd is the size of each record, N slc is the number of shards; migrating the data in the data slice from the source data table to the target data table; and when the migration of a piece or a piece of the data slice fails, performing customizable slice breakpoint resuming migration on the data; The specific steps of the customizable slice breakpoint resuming migration are as follows: Step A1: selecting a database according to the database unified access interface information; Step A2: selecting a database migration table and matching a target database according to the table migration information or the database syntax difference index system; Step A3: setting slice parameters to obtain slice parameter configurations; Step A4: calculating data slices according to the slice parameter configurations, the table migration information and the Oracle database; Step A5: performing table data migration recording to generate data migration logs and table migration information; meanwhile, judging whether the table migration is completed, if yes, ending the breakpoint resuming migration, if not, performing Step A6; Step A6: reading slice data from the Oracle database; Step A7: writing data into the target database slice and performing exception handling to generate data migration logs; Step A8: judging whether the data migration is successful, if yes, performing table data migration recording to generate data migration logs and table migration information, if not, performing exception handling to generate data migration logs.

2. The method of claim 1, wherein, The specific steps of migrating the data in the data slice from the source data table to the target data table are as follows: first, calculating the data range in the source table according to the slice size and the serial number corresponding to the slice, reading out the data corresponding to the slice from the source database; then, performing type conversion according to the matched table structure information; finally, writing the data into the target database table piece by piece or in batches.

3. A device for sharding migration of data facing the level of 10 billion, characterized by, It comprises: a slicing module, configured to cut the source data table to be migrated into a plurality of data slices; The specific steps of cutting the source data table to be migrated into a plurality of data slices by the slicing module are as follows: under the guidance of the database syntax difference index system, matching the table structure between the migration data table in the source database and the target table in the target database is completed; after the matching is completed and confirmed, the basic information of the source data table including the data volume, the keyword or the primary index, the table structure and the size of each record is read; According to the pre-configured slice parameter, the slice size and the total number of slices are calculated, recorded in the slice parameter configuration, and the slice size S slc The calculation method is as follows: wherein R avl is the size of the available memory space of the data migration server, S rcd is the size of each record, N slc is the number of shards; a migration module, configured to migrate the data in the data slice from the source data table to the target data table; a resuming migration module, configured to, when the migration of a piece or a piece of the data slice fails, perform customizable slice breakpoint resuming migration on the data; The specific steps of the customizable slice breakpoint resuming migration in the resuming migration module are as follows: Step A1: selecting a database according to the database unified access interface information; Step A2: selecting a database migration table and matching a target database according to the table migration information or the database syntax difference index system; Step A3: setting slice parameters to obtain slice parameter configurations; Step A4: calculating data slices according to the slice parameter configurations, the table migration information and the Oracle database; Step A4: calculating data sharding according to the sharding parameter configuration, table migration information and Oracle database; Step A5: performing table data migration record, generating data migration log and table migration information; meanwhile, judging whether the table migration is ended, if yes, ending the breakpoint continuation migration, if no, performing step A6; Step A6: reading sharding data from the Oracle database; Step A7: writing data to the target database sharding and performing exception handling, generating data migration log; Step A8: judging whether the data migration is successful, if yes, performing table data migration record, generating data migration log and table migration information, if no, performing exception handling, generating data migration log.

4. The device for migrating the shard of the hundred billion level data according to claim 3, characterized in that, The specific steps of the migration module for migrating data in the data slice from the source data table to the target data table are as follows: First, calculating the data range in the source table according to the sharding size and the serial number corresponding to the sharding, reading out the data corresponding to the sharding from the source database; then, completing type conversion according to the matched table structure information; finally, writing into the target database table piece by piece or in batches.

5. An electronic device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, The processor executes the computer program to realize each step in the sharding migration method for 10 billion level data according to any one of claims 1-2.

6. A storage medium having stored thereon a computer program, characterized in that The computer program is executed by the processor to realize each step in the sharding migration method for 10 billion level data according to any one of claims 1-2.

Images