A method and system for exporting large data volumes and generating custom format Excel files

Abstract

The application discloses a method and system for exporting large data and generating an Excel file in a custom format, and belongs to the technical field of computer data processing. The method comprises the following steps: receiving an export request and analyzing query conditions and Excel format configurations; constructing a resource image containing estimated data volume, query cost index, connection pool availability, thread pool availability, disk input / output idle degree and data distribution skewness; calculating export parallelism according to the resource image and determining a sharding strategy; generating a snapshot list, each sharding thread reads data using key set paging and writes into an intermediate file in a delay-oriented format, and releases a database connection after each batch is written; after all intermediate files are generated, an asynchronous merging service reads the intermediate files in a streaming manner and translates them into worksheet row fragments to assemble and generate a final Excel file; and uploading the final Excel file to an object storage after verification. The application can reduce deep paging overhead, shorten database connection occupation time, improve uneven sharding load and guarantee data consistency.

Description

Technical Field

[0001] This invention relates to the fields of computer data processing, database query optimization, asynchronous task scheduling, file generation, and spreadsheet document processing, specifically to a method and system for exporting large amounts of data and generating custom-formatted Excel files. This invention can be applied to scenarios requiring the export of large amounts of business data into Excel files, such as cross-border e-commerce systems, enterprise resource planning systems, order management systems, supply chain management systems, advertising analysis systems, financial auditing systems, data platforms, and reporting platforms. It is particularly suitable for exporting millions to tens of millions of data points, generating complex Excel formats, and handling concurrent export scenarios across multiple business processes. Background Technology

[0002] In cross-border e-commerce, ERP systems, and supply chain management systems, business personnel often need to export order data, advertising report data, supply chain details data, inventory flow data, or financial audit data into Excel files for offline analysis, business auditing, reconciliation, archiving, or reporting to external systems.

[0003] As business scales up, the amount of data exported in a single session gradually increases from tens of thousands to millions or even tens of millions of records. Traditional Excel export solutions typically follow this process: the front end initiates an export request, the back end reads data from the database page by page based on query conditions, converts the query results into a collection of objects, then writes them into an Excel file using an Excel processing component, and finally returns the file to the user or uploads it to object storage. This approach works fine for small data volumes, but it has the following problems when exporting large amounts of data: First, to avoid server memory overflow, disk I / O overload, and excessive pressure on database CPU and memory, existing systems typically limit the amount of data exported at one time to less than 100,000 records. When the amount of data exported at one time exceeds one million records, service response timeouts, memory overflows, or server crashes are likely to occur.

[0004] Second, existing solutions mostly use the LIMITOFFSET method for pagination queries. When the data volume exceeds millions and the offset is large, the database needs to scan and discard a large number of previous records, which causes the query time to increase significantly with the page number, and the database's CPU, memory and disk input / output resources are continuously occupied.

[0005] Third, in existing solutions, the number of column headers, column header names, column data types, multi-row headers, multi-sheet splitting, and complex styles are usually hard-coded and bound to the specific export logic. When new export requirements are added, the export code often needs to be developed repeatedly, resulting in poor reusability.

[0006] Fourth, export requests from different business lines are processed using a unified queue, which lacks identification of task size, query complexity, and system resource status. Large data export tasks may occupy queues and system resources for a long time, causing small data export tasks to be blocked, affecting user experience.

[0007] Fifth, traditional batch export methods typically execute database reading, data conversion, Excel style rendering, sheet splitting, and file writing sequentially in the same thread. Since Excel style rendering and file writing are time-consuming, database connections are held for extended periods after database reading is complete, resulting in inefficient use of database connection resources. When multiple large-volume export tasks are executed concurrently, the database connection pool may be exhausted, impacting other online business requests.

[0008] Sixth, some solutions increase the export speed by increasing the number of threads. However, the number of threads is usually determined by manual configuration or fixed parameters without considering the database connection pool capacity, query complexity, export thread pool status, disk input / output capacity, and data distribution. Blindly increasing the number of threads may lead to database connection pool contention, an increase in slow queries, disk write congestion, and a decrease in overall system throughput.

[0009] Seventh, existing solutions typically divide data evenly according to date range or primary key range. However, during e-commerce promotions, concentrated advertising campaigns, or peak business scenarios, the data volume in certain date or primary key ranges is significantly higher than in other ranges. This causes some threads to complete quickly while others run for a long time, resulting in long-tailed data sharding. At the same time, large-scale data export tasks are usually asynchronous. During the export process, source data may be added, deleted, or updated. If there is a lack of snapshot control and verification mechanisms, the final Excel file may have issues such as duplication, omissions, or inconsistent results.

[0010] Eighth, while existing solutions may use message queues, streaming write components, or multi-threaded thread pools, these techniques are usually used independently and lack an overall collaborative design around database connection pool protection, query complexity awareness, data distribution awareness, snapshot consistency, delayed formatting of intermediate files, and final streaming assembly of Excel files. Therefore, existing technologies struggle to balance stability, performance, resource utilization, and format flexibility in export scenarios with large data volumes, high concurrency, and complex formats.

[0011] Therefore, there is an urgent need for a method and system that can not only fit the existing enterprise-level asynchronous export architecture, but also achieve database connection protection, deep pagination optimization, adaptive concurrency control, dynamic sharding, data consistency guarantee, and custom Excel format delayed rendering in big data scenarios. Summary of the Invention

[0012] This invention primarily aims to address at least the following technical problems existing in the prior art: 1) When exporting large amounts of data, traditional offset pagination causes the number of rows scanned in the database to increase significantly with the page number; 2) The coupling between database reading and Excel format rendering leads to the database connection being occupied for a long time; 3) The parallelism of multi-threaded export cannot be adaptively adjusted according to the database connection pool, query complexity, thread pool, and disk input / output status; 4) Simple even distribution by date or primary key is prone to generating long-tailed threads in data skew scenarios; 5) The asynchronous sharded export process lacks data consistency guarantees; 6) It is difficult to balance custom Excel format generation with large-scale streaming processing; 7) Large-scale tasks block small-scale tasks, resulting in excessively long user waiting times.

[0013] To achieve the above objectives, the present invention adopts the technical solution as described in claims 1-10: A method for exporting large amounts of data and generating custom-formatted Excel files includes: receiving an export request and parsing the query conditions and Excel format configuration in the export request; constructing a resource profile of the export task based on the query conditions; calculating the export parallelism based on the resource profile and determining a sharding strategy based on the data distribution skewness to generate multiple data sharding intervals; generating a snapshot list for the export request and adding filter conditions based on snapshot timestamps to the query conditions corresponding to each sharding interval; each export thread reading data and writing it to intermediate files using a key-set pagination method, releasing the database connection after each batch of data is written; after all intermediate files are generated, an asynchronous merging service generates style information and Sheet routing rules based on the Excel format configuration, streams the intermediate files, translates the data into worksheet row fragments, and assembles them to generate the final Excel file; after verification, it is uploaded to object storage and the file access address and export status are returned; wherein, the release of the database connection occurs before the generation of style information, the generation of Sheet routing rules, the translation of worksheet row fragments, and the assembly of the final Excel file.

[0014] Furthermore, the resource profile includes at least the estimated amount of data to be exported, the query cost index, the availability of the database connection pool, the availability of the export thread pool, the disk I / O idle time, and the data distribution skewness. Thus, the system can determine the appropriate execution method for the export task from multiple dimensions such as data volume, query complexity, connection pool resources, thread resources, disk I / O resources, and data distribution status before starting the export task.

[0015] Furthermore, the parallelism of the export is constrained by the estimated amount of data to be exported, the remaining capacity of the database connection pool, the remaining capacity of the export thread pool, the query cost index, and the disk I / O idle time. This method ensures that the export task will not blindly increase the parallelism simply because the amount of data is large, nor will it continue to add export threads when the database connection pool or disk I / O resources are insufficient, thereby avoiding the export task from impacting online business.

[0016] Furthermore, the sharding strategy includes date range sharding, primary key range sharding, equal sharding, and virtual bucket sharding; the system determines whether there is skew in the data to be exported by using data distribution entropy; when there is skew, equal sharding or virtual bucket sharding is used to improve the load balance of each export thread.

[0017] Furthermore, the intermediate file is a file oriented towards delayed formatting, which can be a columnar intermediate file, a row-based intermediate file, an unstyled streaming Excel fragment file, or a weakly styled streaming Excel fragment file; the intermediate file is generated during the database reading phase, but the final complex style rendering of the Excel file, sheet routing calculation, worksheet XML fragment generation, and file assembly are all completed by the asynchronous merge service after the database connection is released.

[0018] This invention also provides a system for exporting large amounts of data and generating custom-formatted Excel files, including a request parsing module, a task distribution module, a resource profile construction module, a parallelism and sharding module, a snapshot list module, a sharded reading and intermediate file module, an asynchronous merging and assembly module, a verification and uploading module, and a monitoring and feedback module.

[0019] The present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the above-described method.

[0020] The beneficial effects and significant advancements of this invention are as follows: This invention decouples the export process into two independent stages: data reading and intermediate file writing, and delayed format rendering and assembly. The database connection is held only during data reading and is released immediately after reading is completed. It no longer participates in the subsequent Excel style rendering, header information generation, sheet routing calculation, and final Excel file assembly process, thus significantly reducing the connection time compared to traditional solutions.

[0021] In this invention, each shard thread uses key-set pagination instead of traditional offset pagination. Each query directly locates the target position using the primary key or unique index key. The number of rows scanned does not increase linearly with the amount of data read, thus avoiding the performance degradation caused by deep pagination.

[0022] This invention quantifies the degree of data skew by calculating the data distribution entropy. When a severe skew is detected, it automatically switches to an equal sharding or virtual bucket dynamic retrieval strategy to ensure a more balanced load on each shard thread and reduce the impact of long-tail threads on the overall export time.

[0023] This invention achieves data consistency assurance in asynchronous shard export scenarios by recording a snapshot timestamp at the start of the export process, adding snapshot filtering conditions to all shard queries to define the range of data versions read by each shard, and performing record count verification and hash verification during the merging phase.

[0024] In this invention, the data reading stage adopts a batch query method, and the batch data objects can be released after being written to the intermediate file; the merging and assembly stage adopts a streaming block-level reading and line-by-line translation method, which does not require loading the full amount of data into memory.

[0025] This invention records column type, style mapping, formatter and other information in the column metadata of the intermediate file, and renders them uniformly in the asynchronous assembly stage. When a new export requirement is added, only the format configuration item needs to be added, without modifying the core export logic.

[0026] This invention utilizes parallel computation with multiple resource constraints, a dynamic degradation mechanism, and a resource retention mechanism for small data volume tasks. As a result, the system can protect database connection pools, thread pools, and disk input / output resources in high-concurrency export scenarios, thereby reducing the impact of export tasks on core business operations. Attached Figure Description

[0027] Figure 1 This is a schematic diagram of the system architecture provided in an embodiment of the present invention; Figure 2 A flowchart of the method provided in an embodiment of the present invention; Figure 3 This is a timing diagram for read / write separation provided in an embodiment of the present invention; Figure 4 This is a schematic diagram of the intermediate file structure provided in an embodiment of the present invention; Figure 5 This is a schematic diagram of the parallelism decision-making process provided in an embodiment of the present invention; Figure 6 This is a schematic diagram of the sharding strategy decision-making process provided in an embodiment of the present invention; Figure 7 This is a schematic diagram illustrating the process of asynchronously merging services to assemble the final Excel file, as provided in an embodiment of the present invention. Detailed Implementation

[0028] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be described in detail below with reference to the accompanying drawings and specific embodiments. The described embodiments are merely some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0029] In the description of this invention, the database may be a relational database, a distributed database, a data warehouse, or a data storage system that supports structured queries; the Excel file may be a .xlsx format file or a compatible spreadsheet workbook file; the object storage may be a cloud object storage service, a distributed file system, or a local file system; the message queue may be RabbitMQ, Kafka, or other asynchronous message middleware; and the streaming spreadsheet writing component may be an Excel processing library that supports streaming writing.

[0030] Example 1 like Figure 1 As shown, Figure 1 As shown, the system provided in this embodiment adopts a layered architecture design, including a gateway layer, a service layer, a message layer, a processing layer, and a storage layer.

[0031] The gateway layer deploys a load balancing component. As a specific implementation, the gateway layer can deploy Nginx, which is responsible for receiving user export requests and distributing the export requests to multiple backend application service nodes, such as DAS nodes, to achieve balanced request distribution and high service availability.

[0032] The service layer deploys multiple application service nodes. Each node runs a request receiving and routing module. After receiving an export request, it parses the query conditions and Excel format configuration, and initiates a lightweight estimation query to the database, statistical table, or partition metadata to obtain the estimated amount of data to be exported. If the estimated amount of data to be exported is less than or equal to a preset threshold, such as 20,000 records, the request is routed to a small data volume direct processing channel, where an in-memory table processing component or a lightweight streaming table processing component quickly generates an Excel file and returns it. If the estimated amount of data to be exported is greater than the threshold, the export request is encapsulated into a message object, sent to a message queue for asynchronous processing, and then a task received response is returned to the caller.

[0033] Deploying asynchronous message middleware in the message layer is one specific implementation method. RabbitMQ can be used to handle large-scale data export tasks. The message middleware is used to decouple the service layer and the processing layer, and error message handling, task retry, dead letter queue, priority queue and task status notification mechanism can be configured.

[0034] The processing layer deploys message consumption and export processing services. The message consumption service can be MessageHandler, used to consume export request messages from the message queue and perform parameter validation and task parsing. The export processing service can include ExportMainService, FileMergeService, and FileUploadService. ExportMainService is used to build resource profiles, calculate export parallelism, determine sharding strategies, generate snapshot lists, and start sharded export threads. Each sharded export thread reads database data using key-set pagination and writes the read results to a local temporary intermediate file. FileMergeService is used to perform asynchronous merging and delayed Excel formatting after all intermediate files are generated, generating the final Excel file. FileUploadService is used to upload the final Excel file to the object storage service and generate the file access address.

[0035] The storage layer includes a business database, a local disk temporary directory, and an object storage service. The business database is used to store order data, advertising report data, supply chain detail data, etc. The local disk temporary directory is used to store intermediate files generated by each shard thread. The object storage service is used to store the final generated Excel file and provide the file access address to the caller.

[0036] The system may also include a scheduled task for cleaning up temporary files. This task periodically scans the local disk's temporary directory and deletes intermediate files that have exceeded the preset retention time, files from failed tasks, and expired result files to avoid wasting disk space.

[0037] Example 2 Users can initiate export requests through the front-end interface, business system interface, or scheduled tasks. Export requests can include data query conditions, Excel format configuration, task priority, result notification method, and caller identifier.

[0038] The data query conditions may include time range, store identifier, account identifier, platform type, business type, order status, product category, warehouse identifier, supplier identifier, etc. The Excel format configuration may include export column set, column order, column display name, column data type, multi-row header configuration, column width configuration, style identifier, numeric format, date format, null value handling strategy, maximum number of rows in the sheet, and file naming rules.

[0039] After receiving the export request, the system first generates an export task identifier (taskId) and verifies the query conditions and Excel format configuration. If the verification passes, the system estimates the amount of data to be exported. The estimated amount of data to be exported can be obtained through one or more of the following methods: performing a lightweight COUNT query on the query conditions; querying pre-aggregated data in the business statistics table; querying database partition metadata; estimating based on the number of records in historical similar query tasks; or using index sampling or partition sampling for approximate estimation.

[0040] When the estimated amount of data to be exported is less than or equal to the preset small data volume threshold, such as less than or equal to 20,000 records, the system sends the export request to the small data volume processing channel. The small data volume processing channel can use Python DataFrame, Java memory object collection, lightweight streaming table component or other memory-based data processing components to achieve fast export. This method is suitable for small data volume tasks and can reduce message queue waiting and fragmentation processing overhead.

[0041] When the estimated amount of data to be exported exceeds the preset small data volume threshold, the system will encapsulate the export request into a large data volume export task message and deliver it to the message queue for subsequent consumption and processing by the MessageHandler.

[0042] To prevent large-scale tasks from blocking small-scale tasks, the system can abstract database connections, export threads, and disk I / O quotas into resource tokens. The system reserves database connection resources, export thread resources, and disk I / O resources for small-scale channels. Large-scale channels do not occupy the reserved resources of small-scale channels during execution, thereby ensuring the response time of small-scale tasks.

[0043] Example 3 When a large-scale data export task enters the processing layer, ExportMainService first constructs a resource profile of the export task. The resource profile is used to characterize the current export task in terms of data scale, query complexity, system resources, and data distribution.

[0044] A resource profile can be represented as: in, This indicates the estimated amount of data to be exported; Indicates the query cost index; Indicates the availability of the database connection pool; This indicates the availability of the exported thread pool; Indicates disk I / O idle time; Indicates the skewness of the data distribution.

[0045] Query cost index Based on the database execution plan, query template, and historical execution metrics, the system can parse query statements or query templates and extract query feature information such as the number of associated tables, aggregation calculation complexity, sorting complexity, filter condition selectivity, historical average single batch query time, and the number of rows scanned by the database execution plan.

[0046] In one embodiment, the query cost index Calculate using the following formula: in, Indicates the number of related tables. Indicates the computational complexity of aggregation. Indicate the sorting complexity. This indicates the selective cost of filtering conditions. This indicates the historical average time taken for a single batch of queries. This indicates the estimated number of rows scanned in the database execution plan. to These are preset weighting coefficients.

[0047] Database connection pool availability can be calculated based on the maximum number of connections in the database connection pool, the number of currently active connections, the number of waiting connection threads, and the number of connections reserved for core business. Export thread pool availability can be calculated based on the maximum number of export thread pool threads and the number of currently active export threads. Disk I / O idle time can be calculated based on local disk write latency, disk queue length, remaining disk space, and I / O waiting ratio. Data distribution skew can be calculated using data distribution entropy, as detailed in subsequent embodiments.

[0048] By profiling resources, the system can identify the resource requirements of the task for database connections, threads, disk input / output, and query engines before the actual export is executed, thus providing a quantitative basis for parallelism decisions and sharding strategy selection.

[0049] Example 4 like Figure 5 As shown, after constructing the resource profile, ExportMainService calculates the export parallelism N based on the resource profile. The export parallelism N is used to determine the number of data shard export threads that are executed simultaneously.

[0050] In one embodiment, the degree of parallelism N is calculated according to the following formula: in: To estimate the amount of data to be exported, To preset the data volume threshold for a single data segment; This represents the maximum number of connections in the database connection pool. This represents the current number of active connections in the database connection pool. Reserve a number of connections for core business operations. The number of database connections required for each export thread; Tmax is the maximum number of threads in the exported thread pool, and Tactive is the current number of active threads in the exported thread pool; To pre-determine the complexity conversion factor, To query the cost index; This represents the current disk I / O idle time. Disk input / output baseline values ​​required for each export thread.

[0051] This formula ensures that the export parallelism is simultaneously constrained by the amount of data, the remaining capacity of the database connection pool, the remaining capacity of the export thread pool, the query complexity, and the disk I / O capacity. When the query complexity is high, the remaining capacity of the database connection pool is low, the remaining capacity of the export thread pool is insufficient, or the disk I / O is busy, the export parallelism will automatically decrease. When the query is relatively simple and the system resources are sufficient, the export parallelism can be appropriately increased.

[0052] For example, in a medium-complexity order export task, the estimated amount of data to be exported. Given 8 million records, with a preset single-shard data volume threshold of 500,000 Dunitans, then Nd=16; the maximum number of connections in the database connection pool. The current number of active connections is 80. The number of connections reserved for core business is 20. The number of connections used per thread is 20. If the value is 1, then Nc = 40. The maximum number of threads in the export thread pool, Tmax, is 20. The number of currently active export threads... If the value is 4, then Nt = 16; if we query the cost index... If the value is 1.8 and K is 16, then Nq = 8; if the disk I / O idle time is... It is 0.8. If it is 0.05, then =16. Final: The system uses 8 split export threads to execute this large data export task.

[0053] In another complex join query scenario, if Higher levels lead to If N = 1, then N may eventually be limited to 1. This mechanism reflects the system's protection of database resources. That is, a large amount of data does not necessarily mean that the more threads the better. Complex queries should be automatically downgraded for execution under resource-constrained conditions.

[0054] Furthermore, during the execution of the export task, the system samples at least one of the following metrics at a preset period: database CPU utilization, single batch query time, number of waiting threads in the database connection pool, and disk I / O wait time; when any sampled metric exceeds the corresponding preset safety threshold, a parallelism degradation is triggered. in, This is the downgrade factor, and 0 < <1.

[0055] For example, the initial export parallelism of a certain export task =8. During execution, the database CPU utilization rate is detected to exceed 80%, and the system triggers a degradation, reducing the current export parallelism to 4. After the degradation is triggered, the sharding tasks that are already being executed can continue to be completed, but the number of new virtual buckets or new sharding tasks can be limited. When all sampling indicators fall back to below the corresponding safety threshold within multiple consecutive sampling periods, the system gradually restores the export parallelism, but does not exceed the initial export parallelism N.

[0056] Example 5 like Figure 6 As shown in the figure, this embodiment explains the calculation of data distribution entropy and the selection of sharding strategy.

[0057] After determining the export parallelism, the system needs to generate data sharding intervals based on the data distribution status. By default, the system can generate shards based on date range or primary key range. For example, if the export time range is 30 days and the export parallelism N is 10, the system can initially divide the data into 10 shards based on the date range, with each shard processing approximately 3 days of data.

[0058] However, in real-world business scenarios, data may be significantly skewed. For example, during promotional events, end-of-month settlements, or peak advertising periods, the amount of data on certain days may be much higher than on other days. If the data is still distributed equally by date, it may result in some threads processing an excessive amount of data, creating a long tail.

[0059] To address this, the present invention introduces data distribution entropy. The system divides the data to be exported into m sampling buckets according to time windows, primary key ranges, or partition fields, and obtains the set of record counts in each sampling bucket: Calculate the percentage of data in the i-th sampling bucket: Calculate the data distribution entropy: Calculate the normalized data distribution entropy: When the normalized data distribution entropy Hn is greater than or equal to the preset skew threshold, it indicates that the data distribution is relatively uniform, and the system can use date range sharding or primary key range sharding.

[0060] When the normalized data distribution entropy Hn is less than the preset skew threshold, it indicates that there is a significant data skew, and the system switches to an equal-sharing strategy or a virtual bucket sharding strategy.

[0061] In the equal-size partitioning strategy, the system calculates the target partition data size based on the export parallelism N and the estimated export data size D: The system determines the partition boundaries based on database statistics, index sampling results, or segmentation counting results, ensuring that the number of data records in each partition interval is close to the specified value. .

[0062] In the virtual bucket sharding strategy, the system divides the data to be exported into M virtual buckets, where M is greater than N. Each export thread is not fixed to a large shard, but dynamically acquires virtual buckets. After a thread completes a virtual bucket, it continues to acquire the next unprocessed virtual bucket until all virtual buckets are processed. This method can reduce long-tail waiting caused by data skew.

[0063] For example, suppose the export conditions cover order data for a certain year. The system presamples the data volume for each month. The data volume for November and December is significantly higher than that for other months due to promotional activities. If the normalized data distribution entropy Hn calculated by the system is lower than the preset skew threshold, it is determined that the data distribution is severely skewed. In this case, the system does not use a simple equal distribution by month, but adopts a virtual bucket sharding strategy to divide the target data into multiple virtual buckets according to the primary key range, and the export thread dynamically retrieves and processes them.

[0064] Example 6 Exporting large amounts of data is usually an asynchronous task, which may take several minutes or even longer from task creation to the final generation of the Excel file. During this period, the source data may be added, deleted or updated. If consistency control is not performed, the data read by different shards may not belong to the same data view, and the final Excel file may contain duplicates, omissions or inconsistent quantities.

[0065] To address this issue, this invention generates a snapshot manifest for export requests. The snapshot manifest can be represented as: in, For export task identifier; This is the snapshot expiration time; For query template summary; Summary of filter criteria; This is a list of segments.

[0066] Each fragment list item can be represented as: in, For segmentation identification; Use the starting cursor for the slice; End cursor for partitioning; The expected number of records; This is the expected verification value for the fragmentation; This is a fragmented processing state.

[0067] In lightweight snapshot mode, the system records the export start time (snapshotTime) and adds the following to each shard query: update_time <= snapshotTime Alternatively, use snapshot constraints equivalent to the business data version field. For example, for an order table that includes an update time field, each shard query can include a condition that the order update time is less than or equal to the snapshot expiration time, thus limiting all shards to reading data prior to the same point in time.

[0068] In primary key list mode, the system first generates a primary key list that meets the query criteria, and then divides the data into shards based on the primary key list. Each shard export thread only exports data from the primary key list that belongs to the corresponding data shard range. This approach is suitable for business scenarios with high data consistency requirements or frequent updates to the source data.

[0069] After each fragment is read, the system generates fragment verification information: After the final Excel file is generated, the system summarizes the data from each segment. and in the snapshot list The comparison is performed; at the same time, the hash values ​​of each shard are aggregated and the final hash verification is performed; if the verification fails, the abnormal shard retry, task failure marking, or manual verification process can be triggered.

[0070] Example 7 Each export thread reads database data using key-set pagination within its corresponding data sharding range.

[0071] For a data table with a unique, monotonically increasing primary key, the specific data query logic for each export thread is as follows: query the specified field list `field_list` from the target data table `table_name`; wherein, the filtering conditions for the query operation include at least: the primary key id is greater than the primary key value `last_id` of the last record in the previous batch and less than or equal to the end cursor `end_id` of the current data shard interval, the update time `update_time` is less than or equal to the snapshot expiration time `snapshotTime`, and other preset business filtering conditions `other_conditions`; in addition, the query operation is strictly sorted in ascending order according to the primary key id, and the maximum number of records returned in each query is limited by the set batch read quantity `batchSize`.

[0072] After each query returns a batch of data, the export thread writes this batch of data to the data block segment of the intermediate file and updates the offset index segment. Once the data writing is complete and the disk write conditions are met, the export thread releases the database connection corresponding to this batch of data. Database connection release can be achieved through a connection pool return mechanism or by closing the database session.

[0073] For composite sort keys or non-contiguous primary keys, each of the export threads can also use composite key set pagination. For example, when the creation time and primary key are used as composite cursors, the filtering conditions for the data query can be specifically configured as follows: the creation time of the target record (create_time) is greater than the creation time of the last record in the previous batch (last_time); or, if the creation time of the target record is equal to the creation time of the last record in the previous batch, the primary key id of the target record is greater than the primary key (last_id) of the last record in the previous batch.

[0074] By using key-set pagination, the database does not need to scan and discard a large number of previous records in deep pagination scenarios, which can reduce database scanning overhead.

[0075] The key point of this invention is that the database connection is released before Excel style rendering, header information generation, sheet routing rule calculation, worksheet row fragment translation, final Excel file assembly, and file upload; that is, the database connection only participates in database data reading and rapid intermediate file writing, and does not participate in the subsequent time-consuming Excel formatting process.

[0076] This timing design limits the database connection lifecycle to the data reading phase and the intermediate file fast disk write phase, avoiding the problem of database connections being continuously occupied during Excel style rendering, complex header processing, sheet splitting, and file upload, as in traditional solutions.

[0077] Example 8 like Figure 4 As shown, the intermediate file in this invention is an intermediate data file for delayed Excel formatting. The intermediate file does not necessarily have to be the final Excel file, but is used to quickly write to disk during the database reading stage and perform formatting and final file assembly in the subsequent asynchronous merging stage.

[0078] In one implementation, the intermediate file is a columnar intermediate file or a rowar intermediate file, which includes a file header segment, column data segment, data block segment, offset index segment, and check segment.

[0079] In one implementation, the intermediate file is a columnar intermediate file or a rowar intermediate file, which includes a file header segment, column data segment, data block segment, offset index segment, and check segment.

[0080] Specifically, the file header segment HeaderSegment may include the following fields: magic (identifying the file type), version (identifying the intermediate file format version), taskId, shardId, rowCount (number of records), columnCount, encodingType, compressionType, and createTime.

[0081] The column metadata segment is used to determine the mapping relationship between the source fields and the target Excel columns during the asynchronous merge phase, and to determine the style, formatter, and null value handling policy. Specifically, the column metadata segment ColumnMetaSegment may include the following fields: columnName, fieldName, columnType, Excel columnIndex, styleId, formatterId, nullValuePolicy, and width.

[0082] The data block segment comprises multiple data blocks, where each data block (DataBlock) may include: a block identifier (blockId), a start row (startRow), a row count (rowCount), and compressed data content (compressedData). The data blocks can be stored as row blocks or as column blocks; storing them as row blocks facilitates sequential translation into worksheet row fragments, while storing them as column blocks facilitates uniform formatting of specific columns.

[0083] The offset index segment OffsetIndex can include: blockId, startRow, offset, length, and blockHash. Based on this offset index segment, the asynchronous merge service can quickly locate the target data block without sequentially scanning the entire intermediate file from the file header.

[0084] The CheckSegment may include: row-level hash value rowHash, column-level hash value columnHash, block hash value list blockHashList, and file-level hash value fileHash.

[0085] The checksum section is used to determine whether the intermediate file is complete and whether it has been corrupted during the writing or reading process.

[0086] In another implementation, the intermediate file can also be an unstyled streaming Excel fragment file or a weakly styled streaming Excel fragment file. This fragment file can be generated by a table processing component that supports streaming writes. This fragment file retains data row and column metadata and the basic sheet structure, but does not perform complex style rendering, multi-row header merging, and global sheet routing of the final Excel file. The final style rendering, multi-row header merging, sheet splitting, and file merging are all completed by an asynchronous merge service.

[0087] Through the above methods, the present invention can achieve high processing efficiency and verification capability by using columnar or row-based intermediate files, and it is also compatible with the existing unstyled or weakly styled Excel temporary fragment file implementation in enterprise systems, thus matching the actual R&D architecture of enterprises.

[0088] Example 9 like Figure 7 As shown, after all intermediate files are generated, the asynchronous merge service starts the asynchronous merge task. During this stage, it no longer accesses the business database or occupies a database connection.

[0089] The asynchronous merging service generates the following based on the Excel format configuration: 1) Workbook Part; 2) Styles Part; 3) SharedStrings Part; 4) Sheet Part; 5) Relation Part; 6) ContentType Part; 7) HeaderRows Part; 8) Sheet Route.

[0090] Sheet routing rules can be determined as follows: Here, GlobalRowIndex is the global row number in the final Excel file, and SheetLimit is the maximum number of data rows in a single sheet. In a preferred embodiment, SheetLimit is set to 600,000 rows to automatically split into multiple sheets when exporting large amounts of data.

[0091] The asynchronous merge service reads data in blocks based on the offset index segment of the intermediate file, and translates the data into worksheet row fragments conforming to the OfficeOpenXML specification based on the column type, target Excel column number, style identifier, and formatter identifier in the column metadata segment.

[0092] For example, the worksheet row fragment generated by translating the data of row 100 can be specifically represented as: a row node row containing a row number attribute r with a value of 100; the row node further contains multiple subordinate cell nodes c, such as the first cell node located at position A100, which contains a data type attribute t configured as string type s, a style index attribute s with a value of 1, and a value node v storing the specific content 123; and the second cell node located at position B100, which contains a data type attribute t configured as numeric type n, a style index attribute s with a value of 2, and a value node v storing the specific content 456.78.

[0093] During the translation process, the system applies column headers, styles, number formats, date formats, and null value policies according to the Excel format configuration. When a sheet reaches the SheetLimit, the system automatically creates a new sheet and continues to write subsequent data.

[0094] Ultimately, the system assembles workbook fragments, style fragments, shared string fragments, worksheet fragments, relationship fragments, and content type fragments into an xlsx file. The entire process uses streaming read and streaming write methods, eliminating the need to load all intermediate files or all Excel row data into memory.

[0095] Example 10 After the final Excel file is generated, the system performs record count verification and hash verification based on the snapshot list and intermediate file verification segments.

[0096] If the verification passes, the file upload service will upload the final Excel file to an object storage service, such as OSS, a distributed file system, or another file storage service. After successful upload, the system will generate a file access address and encapsulate the task identifier, file access address, export status, number of records, file size, and verification value into a result message.

[0097] The result message can be written to the task result table or pushed to the caller through the message queue. The caller can obtain the exported result by listening to the message queue or querying the task status interface.

[0098] If the verification fails, the system can locate the abnormal fragment based on the snapshot list and re-execute the data reading and intermediate file generation of the abnormal fragment; if the number of retries exceeds the preset threshold, the task status will be marked as failed and an error message will be returned.

[0099] Example 11 To prevent large data tasks from blocking small data tasks, the system can set up a resource token mechanism.

[0100] The system abstracts database connection, export thread, and disk I / O quotas into resource tokens: Before executing large-scale data tasks, a corresponding number of resource tokens need to be requested. The system sets up a reserved resource pool for small-scale data tasks. When there are many large data tasks, large data tasks cannot occupy the reserved resource tokens of small data tasks. In this way, even if multiple large data tasks are executing, small data tasks can still obtain the necessary database connection and export thread, thereby avoiding long waiting times for small tasks.

[0101] In one implementation, if the estimated amount of data to be exported is less than or equal to 20,000 records, the task enters the small data volume processing channel; if the estimated amount of data to be exported is greater than 20,000 records, the task enters the large data volume asynchronous processing channel. The small data volume processing channel can use an in-memory table generation component to read and generate an Excel file at once; the large data volume asynchronous processing channel performs resource profile construction, export parallelism calculation, dynamic sharding, snapshot list, intermediate file generation, and asynchronous merging processes.

[0102] Example 12 Taking the export of cross-border e-commerce order details as an example, if a user chooses to export order data from the last 30 days, after the system receives the export request, it parses the query conditions and Excel format configuration. Based on the order table partition metadata and historical statistics, the system estimates that the amount of data to be exported will be 8 million records.

[0103] The system retrieved the database execution plan and found that the query involved the order master table, order details table, logistics table, and payment table, with multiple table joins. Based on the historical single batch query time and the estimated number of rows scanned, the system calculated the query cost index Qcost to be 1.8. The system also found that the maximum number of connections in the database connection pool was 80, the current number of active connections was 20, the number of connections reserved for core business was 20, the maximum number of threads in the export thread pool was 20, the current number of active export threads was 4, and the disk I / O status was normal.

[0104] Based on the parallelism calculation formula, the system determines that the derived parallelism N is 8.

[0105] The system sampled 30 days of data and found that the amount of data during promotional days was significantly higher than that on ordinary days. The normalized data distribution entropy was lower than the preset skew threshold. Therefore, the system did not use simple date equalization, but instead used equal boundary partitioning to divide the 8 million data points into 8 data partitioning intervals with similar data amounts.

[0106] The system generates a snapshot list for each shard and records the snapshot expiration time. Subsequently, eight shard export threads read data using key set pagination, reading 20,000 data entries per batch. After writing the data to an intermediate file, the database connection is released. Once all shard intermediate files are generated, FileMergeService asynchronously reads the intermediate files and generates multiple rows and columns, order amount format, date format, and multiple sheets according to the Excel format configuration. When each sheet reaches 600,000 rows of data, it automatically switches to the next sheet.

[0107] Finally, the system performs record count and hash verification based on the snapshot list. After the verification is successful, the final Excel file is uploaded to OSS and the file access address and export success status are returned.

[0108] Example 13 During the export of large amounts of data, issues such as database query failures, intermediate file write failures, insufficient disk space, asynchronous merging anomalies, and object storage upload failures may occur. To improve the reliability of the export task, this invention can set up a task status table, a sharding status table, and an exception retry mechanism.

[0109] Task status can include: created, enqueued, reading, intermediate file generated, merging, verifying, uploading, success, failure. Shard status can include: not started, processing, intermediate file generated, verification passed, verification failed, retrying, failure terminated.

[0110] When a shard thread exits abnormally, the database connection fails, the intermediate file is corrupted, or the shard verification fails, the system re-executes the shard based on the shard start cursor and end cursor in the snapshot list. Since each shard has an independent intermediate file and verification information, the system does not need to re-execute the entire export task, but only needs to retry the abnormal shard.

[0111] When asynchronous merging fails, the system can retain the generated intermediate files and restart the asynchronous merging service. When final verification fails, the system can locate the abnormal shards based on the failed shard verification information and only reread or regenerate the intermediate files for the abnormal shards.

[0112] Example 14 In one implementation test environment, an application server with a 16-core CPU and 64GB of memory was used. The maximum number of connections in the database connection pool was 80, the maximum number of threads in the export thread pool was 20, and the data to be exported consisted of 8 million order records with 32 columns of export fields, including multiple rows of headers and various custom styles.

[0113] After adopting the solution of this invention: the database connection is only occupied during the data reading and intermediate file writing stages, and Excel style rendering, sheet splitting and final file upload are all performed after the database connection is released; each shard uses key set pagination reading to avoid the overhead of deep pagination scanning; when data skew is detected, the system automatically adopts equal boundary sharding or virtual bucket dynamic retrieval strategy; the final Excel file is generated by assembling streaming worksheet row fragments, and the peak memory usage is stable and controllable; small data volume tasks obtain fast response through small task processing channels or resource token reservation mechanisms.

[0114] In some embodiments, the time taken to export 1 million data entries can be controlled within approximately 5 to 10 minutes, and the database connection time can be reduced by approximately 80% to 90% compared to the traditional read-and-write final Excel solution. It should be noted that the specific performance effect will be affected by server configuration, database type, query complexity, number of fields, network environment, disk performance and Excel format complexity. The above data are only examples under specific implementation environments.

[0115] Example 15 Without departing from the concept of this invention, intermediate files may employ different encoding methods, different compression algorithms, or different data organization methods. For example, intermediate files may employ columnar compression format, row-based compression format, unstyled Excel fragment format, or weakly styled Excel fragment format.

[0116] Without departing from the concept of this invention, the asynchronous merging service can directly generate OfficeOpenXML worksheet row fragments, or it can call a table processing component that supports streaming writing to complete the final Excel file writing.

[0117] Without departing from the concept of this invention, message queues can use RabbitMQ, Kafka, or other message middleware; object storage services can use OSS, distributed file systems, local shared storage, or other file storage systems.

[0118] Without departing from the concept of this invention, the weight coefficients in the query cost index can be preset values ​​or dynamically adjusted based on feedback from historical export tasks; the sampling buckets of the data distribution entropy can be divided by time, primary key, partition, or hash bucket; the snapshot list can be stored in the form of database tables, files, cache, or message bodies.

[0119] This invention can be applied to scenarios that require exporting large amounts of data in Excel files, such as cross-border e-commerce systems, ERP systems, advertising systems, supply chain management systems, financial auditing systems, order management systems, data middleware, and reporting platforms. This invention can be deployed on servers, cloud computing platforms, or distributed computing environments via software, and has good industrial applicability.

[0120] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, various modifications and improvements can be made without departing from the concept of the present invention, such as using different intermediate file encoding methods, different compression algorithms, different database types, different message middleware, different object storage methods, or different Excel fragment generation methods. All such modifications and improvements should fall within the protection scope of the present invention.

Claims

1. A method for exporting large amounts of data and generating custom-formatted Excel files, characterized in that, Includes the following steps: S1. Receive an export request and parse the query conditions and Excel format configuration in the export request. The Excel format configuration includes at least the export column configuration, column type configuration, column header configuration, cell style configuration, and sheet split configuration. S2. Construct the resource profile of the export task based on the query conditions. The resource profile includes at least the estimated amount of exported data, query cost index, database connection pool availability, export thread pool availability, disk input / output idle time, and data distribution skewness. S3. Calculate and derive the parallelism based on the resource profile. Based on the data distribution skewness, a sharding strategy is determined, generating N data sharding intervals, where The value is an integer greater than or equal to 1, and the degree of parallelism N of the export is jointly constrained by the estimated amount of exported data, the query cost index, the availability of the database connection pool, the availability of the export thread pool, and the disk input / output idle degree. S4. Generate a snapshot list for the export request. The snapshot list at least records the export task identifier, export snapshot timestamp, query condition summary, start cursor, end cursor, expected number of records and shard verification information for each data shard interval, and adds snapshot filtering conditions based on the export snapshot timestamp to the query conditions corresponding to each data shard interval. S5. Allocate an export thread for each of the data sharding intervals. Each export thread reads database data in its corresponding data sharding interval using a key set pagination method and writes the read data into an intermediate file. The intermediate file is an intermediate file for Excel delayed formatting. After each batch of data is written into the intermediate file, the corresponding export thread releases the database connection it occupies. S6. After all the intermediate files corresponding to the data sharding intervals are generated, the asynchronous merging service generates style information, header information and Sheet routing rules according to the Excel format configuration, streams each intermediate file, translates the data in the intermediate files into worksheet row fragments, and assembles them according to the Sheet routing rules to generate the final Excel file. S7. Based on the snapshot list and the verification information in the intermediate file, perform record count verification and hash verification on the final Excel file. After the verification is passed, upload the final Excel file to the object storage service and return a result message containing task identifier, file access address and export status. Specifically, the release of the database connection in step S5 occurs before the generation of style information, header information, sheet routing rules, worksheet row fragment translation, and final Excel file assembly in step S6, and before the final Excel file upload in step S7.

2. The method for exporting large amounts of data and generating a custom-formatted Excel file according to claim 1, characterized in that, The query cost index described in step S2 Determined in the following ways: Obtain the query feature information corresponding to the query conditions, including the number of associated tables. Aggregate computational complexity Sorting complexity Filtering conditions and selective cost Historical average time for a single batch query And the estimated number of rows scanned in the database execution plan. ; The query cost index is calculated using the following formula: Among them, a1 to a6 are preset weight coefficients; The query is divided into multiple complexity levels based on the value of the query cost index Qcost. When Qcost exceeds a preset high-risk threshold, the export parallelism N is forcibly limited to 1, or the export request is placed in a delayed scheduling queue.

3. The method for exporting large amounts of data and generating a custom-formatted Excel file according to claim 2, characterized in that, The derivation of parallelism in step S3 Determined by the following formula: in, The estimated amount of exported data, To preset the data volume threshold for a single data segment; This represents the maximum number of connections in the database connection pool. This represents the current number of active connections in the database connection pool. Reserve a number of connections for core business operations. The number of database connections required for each export thread; To derive the maximum number of threads in the thread pool, To export the current number of active threads in the thread pool; To pre-determine the complexity conversion factor, The query cost index; This represents the current disk I / O idle time. Disk input / output baseline values ​​required for each export thread.

4. The method for exporting large amounts of data and generating a custom-formatted Excel file according to claim 1, characterized in that, The data distribution skewness mentioned in step S3 is characterized by data distribution entropy, which is calculated as follows: Presample the target exported data range to obtain the data volume of each of the m sampling buckets. Calculate the percentage of data volume in each bucket: Calculate the data distribution entropy: And calculate the normalized data distribution entropy: When the normalized data distribution entropy Hn is lower than the preset skew threshold, it is determined that the data distribution is skewed, and the N data sharding intervals are generated by using an equal sharding strategy or a virtual bucket sharding strategy. The equal-volume sharding strategy is as follows: based on the target shard data volume D / N, the sharding boundary is determined based on database statistics, index sampling results, or segmentation counting results, so that the data volume of each data sharding interval approaches D / N. The virtual bucket sharding strategy is as follows: the target exported data is divided into M virtual buckets, where M is an integer greater than N. Each export thread dynamically takes over the next unprocessed virtual bucket after completing the data processing of the current virtual bucket, until all virtual buckets are processed.

5. The method for exporting large amounts of data and generating a custom-formatted Excel file according to claim 1, characterized in that, The snapshot filtering condition in step S4 is: to add a constraint that the update time is less than or equal to the exported snapshot timestamp to the query conditions corresponding to each of the data sharding intervals; Alternatively, based on database consistency read mechanism, data version number mechanism or primary key list mechanism, the data sharding intervals can be limited to read data under the same snapshot view; In step S5, the key set paging method used by each export thread is as follows: using the primary key, unique index key, or composite cursor key as the cursor, data paging is performed using the query mode of WHERE cursor key greater than the maximum cursor value of the previous batch and sorted according to the cursor key. After each batch of queries is completed, the maximum cursor value of the current batch is used as the starting cursor for the next batch of queries.

6. The method for exporting large amounts of data and generating a custom-formatted Excel file according to claim 1, characterized in that, The intermediate file mentioned in step S5 is one of the following: columnar intermediate file, row-based intermediate file, unstyled flowing Excel fragment file, or weakly styled flowing Excel fragment file. When the intermediate file is a columnar intermediate file or a row-based intermediate file, the intermediate file includes at least: The file header is used to record the task identifier, fragment identifier, format version number, total number of records, total number of columns, encoding method, and compression method. The column data section is used to record the field name, source data type, target Excel column number, style identifier, formatter identifier, and null value handling strategy for each column. Data block segment, used to store data records in units of data blocks; The offset index segment is used to record the offset position and data length of each data block in the intermediate file; The checksum section is used to record the checksum of the number of records in the fragment, the row-level hash value, the column-level hash value, the block-level hash value, and the file-level hash value. When the intermediate file is an unstyled streaming Excel fragment file or a weakly styled streaming Excel fragment file, the intermediate file retains the data row and column metadata and the basic sheet structure, but does not perform complex style rendering, multi-row header merging, and global sheet routing in the final Excel file. The complex style rendering, multi-row header merging, and global sheet routing are uniformly completed by the asynchronous merging service.

7. The method for exporting large amounts of data and generating a custom-formatted Excel file according to claim 1, characterized in that, In step S6, when the asynchronous merging service reads each intermediate file in a streaming manner, it pre-generates style definition fragments, header fragments, and workbook structure fragments according to the Excel format configuration. Open each intermediate file in global row order, and use the offset index segment of the intermediate file to perform block-level skip reading; translate the read data line by line into worksheet row fragments that conform to the OfficeOpenXML specification; The target sheet for each row is determined according to the sheet routing rules, which are as follows: in, This is the global line number. For preset single Maximum number of rows threshold.

8. A method for exporting large amounts of data and generating a custom-formatted Excel file according to any one of claims 1 to 7, characterized in that, The method further includes: Before step S3, based on the comparison result between the estimated amount of exported data and the preset task threshold, the export request is diverted to a small data volume channel or a large data volume channel. When the estimated amount of exported data is less than or equal to the preset task threshold, an Excel file is generated through the small data volume channel in a single-threaded memory mode or a lightweight streaming mode. When the estimated amount of exported data is greater than the preset task threshold, steps S2 to S7 are executed through the large data volume channel. During the execution of steps S2 to S7, the large data volume channel does not occupy the database connection resources, export thread resources, and disk input / output resources reserved for the small data volume channel; Furthermore, during the execution of steps S5 to S6, at least one of the following indicators is sampled at a preset period: database CPU utilization, single batch query time, number of waiting threads in the database connection pool, and disk I / O wait time. When any sampling metric exceeds the corresponding preset safety threshold, the current export parallelism is reduced according to the following formula: in, The demotion coefficient is 0 < <1; When all sampling indicators fall back below the corresponding safety threshold in multiple consecutive sampling periods, the degree of parallelism is gradually restored, but does not exceed the initial degree of parallelism N calculated in step S3.

9. A system for exporting large amounts of data and generating custom-formatted Excel files, characterized in that, include: The request parsing module is used to receive export requests and parse query conditions and Excel format configurations. The task routing module is used to route export requests to small data volume channels or large data volume channels based on the estimated amount of data to be exported. The resource profile building module is used to build a resource profile of the export task based on the query conditions. The resource profile includes at least the estimated amount of exported data, query cost index, database connection pool availability, export thread pool availability, disk input / output idle time, and data distribution skewness. The parallelism and sharding module is used to calculate and derive the parallelism N based on the resource profile, and determine the sharding strategy based on the data distribution skewness to generate N data sharding intervals. The snapshot list module is used to generate a snapshot list containing the exported snapshot timestamp, the start and end cursors of each data shard interval, the expected number of records and verification information, and to attach snapshot filtering conditions based on the exported snapshot timestamp to the query conditions corresponding to each data shard interval. The sharded reading and intermediate file module is used to allocate export threads for each data shard interval, so that each export thread reads database data using key set pagination and writes the data to the intermediate file, and releases the database connection after each batch of data is written. The asynchronous merging and assembly module is used to generate style information, header information and sheet routing rules according to the Excel format configuration after all intermediate files are generated. It streams the intermediate files, translates the data into worksheet row fragments, and then assembles them to generate the final Excel file. The verification and upload module is used to perform record count verification and hash verification on the final Excel file. After the verification is successful, it is uploaded to the object storage service and the file access address and export status are returned. The monitoring and feedback module is used to collect operating metrics during the execution of the export task, and trigger the export parallelism degradation when the operating metrics exceed a preset safety threshold, and trigger the export parallelism recovery when the operating metrics fall back below the preset safety threshold.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements a method for exporting large amounts of data and generating a custom-formatted Excel file as described in any one of claims 1 to 8.

Images