Optimization method and device for improving big data export

Abstract

The invention relates to the technical field of related government affair products, and particularly provides an optimization method and device for improving big data export, in each data processing stage, data in a target database is divided into a plurality of processing batches according to a set single-batch processing condition number, and batch information of each processing batch is generated; caching the batch information of each processing batch to a distributed cache service, and sequentially executing the processing process of each processing batch on the target database according to the batch information of each processing batch; and every time the processing process of one processing batch is executed, determining the processing batch as a completed processing batch, and caching the batch code of the completed processing batch to the distributed cache service. Compared with the prior art, the performance of the server can be guaranteed, the problem of server downtime cannot occur in the big data exporting process, and high availability of services is guaranteed.

Description

Technical Field

[0001] The present invention relates to the technical field of related government product technologies, and specifically provides a method and device for improving big data export optimization. Background Art

[0002] The current existing data export process generally reads the data in each database that meets the screening conditions into the memory according to the data screening conditions configured by the user, and then exports the data in the memory to a specified file or to a database according to the user's selection and confirmation operation.

[0003] In some government data application scenarios, such as statistics on the province's case handling data, the data to be queried ranges from millions to hundreds of millions, depending on different screening conditions. At this time, the total amount of data to be screened is too large, resulting in a long time required for existing data query and export methods, which occupy database query, I / O and other resources for a long time, affecting the normal use of the database.

[0004] After a large amount of data is read into the memory, it occupies a large amount of memory resources, affecting the normal use of the entire server and making it impossible to execute higher priority tasks normally. At the same time, the export process of massive data takes a long time, so it is easy to have exceptions; once a processing exception occurs, it can only be re-queried and exported from the beginning, which prolongs the time it takes to occupy database and server resources and makes data export efficiency extremely low. Summary of the invention

[0005] The present invention aims at the above-mentioned deficiencies of the prior art and provides a highly practical method for improving the optimization of big data export.

[0006] A further technical task of the present invention is to provide a device for improving big data export optimization that is reasonably designed, safe and applicable.

[0007] The technical solution adopted by the present invention to solve its technical problem is:

[0008] A method for improving the optimization of big data export, in each data processing stage, according to the number of set single batch processing conditions, the data in the target database is divided into multiple processing batches, and batch information of each processing batch is generated;

[0009] The batch information of each processing batch is cached to the distributed cache service, and the processing process of each processing batch is executed on the target database in sequence according to the batch information of each processing batch; whenever the processing process of a processing batch is completed, the processing batch is determined as a completed processing batch, and the batch code of the completed processing batch is cached to the distributed cache service.

[0010] Further, for the abnormal export processing, when it is determined that the processing process of any processing batch fails, the processing batch is determined as a processing failure batch;

[0011] The batch codes of the failed batches are cached in the distributed cache service; when the processing process of all the processing batches is completed, the processing process of each failed batch is re-executed according to the batch codes of the failed batches cached in the distributed cache service and the batch information of each processing batch.

[0012] Furthermore, when the execution of any processing batch processing process is interrupted, the execution statement of each unfinished processing batch is read from the execution statement of each processing batch cached in the distributed cache service according to the batch code of the completed processing batch; the execution statement of each unfinished processing batch is executed on the target database in turn, and the processing result of each unfinished processing batch is obtained from the target database in turn.

[0013] Furthermore, the batch information includes:

[0014] The batch code of each processing batch, the data code corresponding to each processing batch and the execution statement; in each data processing stage, according to the number of single-batch processing conditions set, the data in the target database is divided into multiple processing batches, and the batch information of each processing batch is generated; according to the number of single-batch processing conditions set and the data with corresponding processing conditions in the target database, the data with corresponding processing conditions is divided into multiple processing batches, and the batch code of each processing batch and the data code corresponding to each processing batch are obtained; according to the data code corresponding to each processing batch and the set screening conditions, the execution statement of each processing batch is spliced.

[0015] Furthermore, according to the data encoding corresponding to each processing batch and the set screening conditions, the execution statements of each processing batch are spliced ​​together.

[0016] It includes: executing the table information and field information in the statement, splicing the first condition WHERE clause in the execution statement of each processing batch according to the data encoding corresponding to each processing batch; dynamically setting the number of single batch processing conditions in each data processing stage according to at least one reference factor among server performance, task priority, data volume in the target database, processing condition complexity, target database type, target data export location and server load.

[0017] Furthermore, the distributed cache service deploys a Redis cluster, which is a master-slave architecture, with one master and multiple slaves plus a sentinel mode. The master cache node is responsible for storing cache data and copying the cache data to other slave cache nodes, while the slave cache node is responsible for reading the cache data. When the master cache node service goes down, the slave cache node will generate a new master cache node through election;

[0018] Master-slave mode: when the master cache node goes down, the slave cache node acts as the master cache node and continues to provide services;

[0019] Sentinel is responsible for monitoring whether the Redis master node and slave node processes are working properly. When a node goes down, Sentinel is responsible for sending a message notification to the administrator and automatically transferring to the slave node;

[0020] If a failover occurs and the address of the primary node changes, the application service will not be aware of it and there is no need to change the access address.

[0021] Furthermore, the master-slave mode is that the Master is the master cache node and the Slave is the slave cache node. Data replication is one-way and can only be from the master node to the slave node. Once the master cache node goes down, the slave cache node will replace it as the new master cache node, and the cached data is consistent, which will not affect the client's access.

[0022] Furthermore, the sentinel cluster is defined as:

[0023] The sentinel node consists of a sentinel node and a data node. The sentinel node is a special redis node that does not store data. The master node and the slave node in the data node are both data nodes. Access to the data of the redis cluster is through the sentinel cluster. The sentinel monitors the entire redis cluster.

[0024] Furthermore, when the sentinel nodes interact with the master and slave cache nodes, each sentinel node sends a PING command to all its master cache nodes, slave cache nodes, and other sentinel instances once per second;

[0025] If an instance instance has been offline for more than a certain period of time since its last effective reply to the PING command, the instance will be marked as subjectively offline by the Sentinel;

[0026] If a primary cache node is marked as subjectively offline, all sentinel nodes that are monitoring this primary cache node must confirm once per second whether the primary cache node has indeed entered the subjectively offline state;

[0027] If a master server is marked as subjectively offline, and a sufficient number of sentinels agree with this judgment within a specified time frame, then the master cache node is marked as objectively offline;

[0028] The sentinel and other sentinel nodes negotiate the status of the master cache node. If the master cache node is down, they vote to automatically select a new master cache node and point the remaining slave cache nodes to the new master cache node for cache data replication. When there are not enough sentinel nodes to agree that the master cache node is offline, the objective offline status of the master cache node will be removed;

[0029] When the primary cache node returns a valid reply to the sentinel node's PING command again, the subjective offline status of the primary cache node will be removed.

[0030] A device for improving big data export optimization includes: at least one memory and at least one processor;

[0031] The at least one memory is used to store a machine-readable program;

[0032] The at least one processor is used to call the machine-readable program to execute a method for improving the optimization of large data export.

[0033] Compared with the prior art, the method and device for improving big data export optimization of the present invention have the following outstanding beneficial effects:

[0034] The present invention flexibly formulates export batches and batch execution data volumes according to server performance, task priority, data volume in the target database, complexity of processing conditions, target database type, target data export location and server load, thereby ensuring server performance, preventing server downtime during the big data export process and ensuring high availability of the service.

[0035] Sentinel nodes communicate with each other to automatically sense the addition or removal of cache nodes. When the primary cache node is removed, a new primary cache node can be quickly elected and the migrated content blocks can be recalculated and allocated, so that the cache nodes in the cache cluster always maintain a balanced load and maintain high availability. They automatically sense cache nodes and replicate data, allowing the cache cluster to remain easy to scale horizontally and highly available. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0037] Attached Figure 1 The present invention is a schematic diagram of the interaction between sentinel nodes and master-slave cache nodes in a method for improving the optimization of big data export. DETAILED DESCRIPTION

[0038] In order to enable those skilled in the art to better understand the solution of the present invention, the present invention is further described in detail below in conjunction with specific implementation methods. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0039] A best embodiment is given below:

[0040] In this embodiment, a method for improving the optimization of big data export is provided. In each data processing stage, the data in the target database is divided into multiple processing batches according to the number of set single batch processing conditions, and batch information of each processing batch is generated.

[0041] The batch information of each processing batch is cached to the distributed cache service; according to the batch information of each processing batch, the processing process of each processing batch is executed on the target database in sequence; whenever the processing process of a processing batch is completed, the processing batch is determined as a completed processing batch, and the batch code of the completed processing batch is cached to the distributed cache service.

[0042] For the exception export processing, when it is determined that the processing process of any processing batch fails, the processing batch is determined as a processing failure batch; the batch code of the processing failure batch is cached to the distributed cache service.

[0043] When the processing of all processing batches is completed, the processing of each processing failed batch is re-executed according to the batch code of the processing failed batch cached in the distributed cache service and the batch information of each processing batch.

[0044] When the execution of any processing batch is interrupted, the execution statement of each unfinished processing batch is read from the execution statement of each processing batch cached in the distributed cache service according to the batch code of the completed processing batch. The execution statement of each unfinished processing batch is executed on the target database in sequence, and the processing result of each unfinished processing batch is obtained from the target database in sequence.

[0045] The batch information includes:

[0046] The batch code of each processing batch, the data code and execution statement corresponding to each processing batch;

[0047] In each data processing stage, the data in the target database is divided into multiple processing batches according to the set number of single-batch processing conditions, and batch information of each processing batch is generated; according to the set number of single-batch processing conditions and the data with corresponding processing conditions in the target database, the data with corresponding processing conditions is divided into multiple processing batches, and the batch code of each processing batch and the data code corresponding to each processing batch are obtained; according to the data code corresponding to each processing batch and the set screening conditions, the execution statement of each processing batch is spliced.

[0048] According to the data encoding corresponding to each processing batch and the set screening conditions, the execution statement of each processing batch is spliced, including: table information and field information in the execution statement. According to the data encoding corresponding to each processing batch, the first condition WHERE clause in the execution statement of each processing batch is spliced;

[0049] The number of single batch processing conditions for each data processing stage is dynamically set based on at least one reference factor including server performance, task priority, data volume in the target database, complexity of processing conditions, target database type, target data export location and server load.

[0050] The distributed cache service deploys a Redis cluster. To ensure data sharing among cache services, the architecture is made into a master-slave architecture, with one master and multiple slaves plus a sentinel mode.

[0051] The master cache node is responsible for storing cache data and copying the cache data to other slave cache nodes, while the slave cache node is responsible for reading the cache data. When the master cache node service goes down, the slave cache node will elect a new master cache node to ensure high availability of the service. Only a read-write separation architecture can easily achieve horizontal expansion and support high-concurrency requests.

[0052] In the master-slave mode, when the master cache node goes down, the slave cache node can take over as the master cache node and continue to provide services. However, there is a problem. The IP address of the master cache node has changed. At this time, the application service still uses the address of the original master cache node to access, which will result in the inability to obtain cache data. At this time, the sentinel mode plays a key role. The sentinel realizes automatic fault recovery.

[0053] Sentinel is responsible for monitoring whether the Redis master node and slave node processes are working properly. When a node goes down, Sentinel is responsible for sending a message to the administrator and automatically transferring to the slave node. If a failover occurs, the address of the master node changes. At this time, the application service is unaware and there is no need to change the access address, because Sentinel is the one that interacts with the application service. The Sentinel mode solves the failover problem very well and has reached a new level in terms of high availability.

[0054] Among them, Figure 1 As shown in the figure, in the master-slave mode, the master is the main cache node and the slave is the slave cache node. Data replication is one-way, only from the master node to the slave node. Once the master cache node goes down, a slave cache node will replace it as the new master cache node, and the cached data is consistent, which will not affect the client's access at all.

[0055] The sentinel cluster is defined as:

[0056] Based on replication, Sentinel implements automated failure recovery.

[0057] The sentinel node consists of two parts, the sentinel node and the data node;

[0058] Sentinel node: The Sentinel system consists of one or more Sentinel nodes. Sentinel nodes are special Redis nodes that do not store data.

[0059] Data nodes: Both master nodes and slave nodes are data nodes.

[0060] Access to the data of the redis cluster is through the sentinel cluster, which monitors the entire redis cluster.

[0061] like Figure 1 As shown in the figure, when the sentinel node interacts with the master and slave cache nodes, each sentinel node sends a PING command to all its master cache nodes, slave cache nodes and other sentinel instances once per second. If an instance has been away from the last valid reply to the PING command for more than a certain period of time, the instance will be marked as subjectively offline by the sentinel.

[0062] If a master cache node is marked as subjective offline, all sentinel nodes that are monitoring this master cache node must confirm once per second whether the master cache node has indeed entered the subjective offline state. If a master server is marked as subjective offline and a sufficient number of sentinels agree with this judgment within the specified time range, then the master cache node is marked as objectively offline.

[0063] The sentinel and other sentinel nodes negotiate the status of the master cache node. If the master cache node is down, a new master cache node will be automatically selected by voting. The remaining slave cache nodes point to the new master cache node for cache data replication. When there are not enough sentinel nodes to agree that the master cache node is offline, the objective offline status of the master cache node will be removed. When the master cache node returns a valid reply to the sentinel node's PING command, the subjective offline status of the master cache node will be removed.

[0064] Based on the above method, a device for improving big data export optimization in this embodiment includes: at least one memory and at least one processor;

[0065] The at least one memory is used to store a machine-readable program;

[0066] The at least one processor is used to call the machine-readable program to execute a method for improving the optimization of large data export.

[0067] The above-mentioned specific implementations are only specific cases of the present invention. The patent protection scope of the present invention includes but is not limited to the above-mentioned specific implementations. Any technical solutions that comply with the claims of the present invention and any appropriate changes or substitutions made by ordinary technicians in the relevant technical field shall fall within the patent protection scope of the present invention.

[0068] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A method for improving the optimization of big data export, It is characterized in that At each data processing stage, the data in the target database is divided into multiple processing batches according to the number of single batch processing conditions set, and batch information of each processing batch is generated; The batch information of each processing batch is cached to the distributed cache service, and the processing process of each processing batch is executed on the target database in sequence according to the batch information of each processing batch; whenever the processing process of a processing batch is completed, the processing batch is determined as a completed processing batch, and the batch code of the completed processing batch is cached to the distributed cache service.

2. According to claim 1, a method for improving the optimization of big data export, It is characterized in that For abnormal export processing, when it is determined that the processing process of any processing batch fails, the processing batch is determined as a processing failure batch; Cache the batch codes of failed batches to the distributed cache service; When the processing of all processing batches is completed, the processing of each processing failed batch is re-executed according to the batch code of the processing failed batch cached in the distributed cache service and the batch information of each processing batch.

3. According to claim 2, a method for improving the optimization of big data export, It is characterized in that When the execution of any processing batch processing process is interrupted, the execution statement of each unfinished processing batch is read from the execution statement of each processing batch cached in the distributed cache service according to the batch code of the completed processing batch; the execution statement of each unfinished processing batch is executed on the target database in turn, and the processing result of each unfinished processing batch is obtained from the target database in turn.

4. A method for improving the export optimization of big data according to claim 3, It is characterized in that The batch information includes: The batch code of each processing batch, the data code corresponding to each processing batch and the execution statement; in each data processing stage, according to the number of single-batch processing conditions set, the data in the target database is divided into multiple processing batches, and the batch information of each processing batch is generated; according to the number of single-batch processing conditions set and the data with corresponding processing conditions in the target database, the data with corresponding processing conditions is divided into multiple processing batches, and the batch code of each processing batch and the data code corresponding to each processing batch are obtained; according to the data code corresponding to each processing batch and the set screening conditions, the execution statement of each processing batch is spliced.

5. A method for improving the export optimization of big data according to claim 4, It is characterized in that According to the data encoding and set filtering conditions corresponding to each processing batch, the execution statement of each processing batch is spliced. It includes: executing the table information and field information in the statement, splicing the first condition WHERE clause in the execution statement of each processing batch according to the data encoding corresponding to each processing batch; dynamically setting the number of single batch processing conditions in each data processing stage according to at least one reference factor among server performance, task priority, data volume in the target database, processing condition complexity, target database type, target data export location and server load.

6. A method for improving the export optimization of big data according to claim 5, It is characterized in that The distributed cache service deploys a Redis cluster, which is a master-slave architecture, with one master and multiple slaves plus a sentinel mode. The master cache node is responsible for storing cache data and copying cache data to other slave cache nodes, while the slave cache node is responsible for reading cache data. When the master cache node service goes down, the slave cache node will elect a new master cache node; Master-slave mode: when the master cache node goes down, the slave cache node acts as the master cache node and continues to provide services; Sentinel is responsible for monitoring whether the Redis master node and slave node processes are working properly. When a node goes down, Sentinel is responsible for sending a message notification to the administrator and automatically transferring to the slave node; If a failover occurs and the address of the primary node changes, the application service will not be aware of it and there is no need to change the access address.

7. A method for improving the export optimization of big data according to claim 6, It is characterized in that The master-slave mode is that the Master is the master cache node and the Slave is the slave cache node. Data replication is one-way and can only be from the master node to the slave node. Once the master cache node goes down, the slave cache node will replace it as the new master cache node, and the cached data is consistent, which will not affect the client's access.

8. A method for improving the optimization of big data export according to claim 7, It is characterized in that The sentinel cluster is defined as: The sentinel node consists of a sentinel node and a data node. The sentinel node is a special redis node that does not store data. The master node and the slave node in the data node are both data nodes. Access to the data of the redis cluster is through the sentinel cluster. The sentinel monitors the entire redis cluster.

9. A method for improving the optimization of big data export according to claim 8, It is characterized in that When the sentinel nodes interact with the master and slave cache nodes, each sentinel node sends a PING command to all its master cache nodes, slave cache nodes, and other sentinel instances once per second; If an instance instance has been offline for more than a certain period of time since its last effective reply to the PING command, the instance will be marked as subjectively offline by the Sentinel; If a primary cache node is marked as subjectively offline, all sentinel nodes that are monitoring this primary cache node must confirm once per second whether the primary cache node has indeed entered the subjectively offline state; If a master server is marked as subjectively offline, and a sufficient number of sentinels agree with this judgment within a specified time frame, then the master cache node is marked as objectively offline; The sentinel and other sentinel nodes negotiate the status of the master cache node. If the master cache node is down, they vote to automatically select a new master cache node and point the remaining slave cache nodes to the new master cache node for cache data replication. When there are not enough sentinel nodes to agree that the master cache node is offline, the objective offline status of the master cache node will be removed; When the primary cache node returns a valid reply to the sentinel node's PING command again, the subjective offline status of the primary cache node will be removed.

10. A device for improving the optimization of big data export, It is characterized in that include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to call the machine-readable program to execute the method according to any one of claims 1 to 9.

Images