Data writing method, device, electronic device and storage medium for table files

Abstract

The present application provides a method, an apparatus, an electronic device and a storage medium for writing data into a table file. The method includes: allocating all shard tasks with an execution status of to-be-executed to threads in a thread pool according to the task execution rate and the current status of each thread; reading the data of the i-th page within the range of the first number of rows of the shard task in the original table file; storing the data within the range of the j-th second number of rows in the i-th page into a cache; sequentially writing each row of data within the range of the j-th second number of rows in the cache into the initial table file stored locally according to the target field mapping relationship; j = j + 1; until all the data of the i-th page is written into the initial table file, i = i + 1; copying the data in all the initial table files to the target table file stored locally. The present application can improve the writing efficiency through multi-threading, reduce the memory occupied space during the writing process, and improve the security of the table file.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular, to a method, apparatus, electronic device, and storage medium for writing data into a table file. Background Art

[0002] With the development of technology, data needs to be presented in tables in various scenarios such as data analysis and reporting, budgeting and financial planning. The traditional method for generating a table file is for a user to manually write data into the table file.

[0003] However, the traditional writing method has low generation efficiency when dealing with a large amount of data, is difficult to meet the requirements of high-concurrency scenarios, has a high memory occupancy during file generation, is prone to causing program crashes, lacks a complete security protection mechanism, and the file may be tampered with or leaked during transmission and storage. Summary of the Invention

[0004] In view of this, the purpose of this application is to provide a method, apparatus, electronic device, and storage medium for writing data into a table file, which can improve the writing efficiency through multi-threading, reduce the memory occupancy during the writing process, improve the security of the table file, and reduce the data error rate.

[0005] In a first aspect, an embodiment of this application provides a method for writing data into a table file. The method for writing data into the table file includes:

[0006] At preset time intervals, according to the task execution rate and current status of each thread, all shard tasks with an execution status of to-be-executed are assigned to the threads in the thread pool; the shard tasks are obtained by sharding the data in the original table file;

[0007] When each thread executes each shard task, it reads the data on the i-th page within the first row number range of the shard task in the original table file; stores the data within the second row number range of the j-th in the i-th page into the cache; writes each row of data within the second row number range of the j-th in the cache into the initial table file stored locally according to the target field mapping relationship; j = j + 1; until all the data on the i-th page is written into the initial table file, i = i + 1; until all the data within the first row number range of the shard task is written, stop reading; the initial values of i and j are 0;

[0008] When the execution status of all shard tasks is completed, the data in all the initial table files is copied to the target table file stored locally.

[0009] In a possible implementation manner, the method further includes:

[0010] After all data within the j-th second row number range in the cache are written to the initial table file, clear all data within the j-th second row number range in the cache.

[0011] In a possible implementation, the method further includes:

[0012] After all data on the i-th page are written to the initial table file, determine the i-th page as the latest executed page of the thread for the sharding task;

[0013] After the sharding task resumes from interruption, start writing data to the initial table file from the next page of the latest executed page of the thread for the sharding task.

[0014] In a possible implementation, the method further includes:

[0015] After all data on the i-th page are written to the initial table file, determine the maximum content length of each column in the initial table file;

[0016] Adjust the cell width of each column according to the maximum content length of each column, so that the display length of the content of each cell in each column accounts for a proportion greater than the preset proportion of the total content length.

[0017] In a possible implementation, after copying all data in the initial table files to the target table file stored locally, the method further includes:

[0018] If the target table file is not encrypted or the encryption duration of the current encryption key is greater than the preset duration, generate an encryption key; encrypt the target table file with the encryption key;

[0019] Store the identifier of the encryption key and the storage path of the target table file in the key database correspondingly; or determine the identifier of the encryption key in the key database corresponding to the storage path of the target table file as the identifier of the newly generated encryption key.

[0020] In a possible implementation, after copying all data in the initial table files to the target table file stored locally, the method further includes:

[0021] Determine the hash value of the target table file as the digest of the target table file;

[0022] Digitally sign the digest of the target table file with the RSA private key;

[0023] Store the digital signature and the name of the target table file in the digital signature database correspondingly.

[0024] In a possible implementation, the method further includes:

[0025] Authenticate the file download user in response to a download request initiated by the file download user;

[0026] If the authentication is passed, obtain the key identifier of the table file corresponding to the download request from the key database;

[0027] Decrypt the table file according to the key corresponding to the key identifier;

[0028] Send the decrypted table file to the file download user for download.

[0029] In a second aspect, an embodiment of the present application further provides a data writing device for a table file, and the device includes:

[0030] An allocation module, configured to allocate all shard tasks with an execution status of to-be-executed to threads in a thread pool according to the task execution rate and the current status of each thread at a preset time interval; the shard tasks are obtained by sharding the data in the original table file;

[0031] A writing module, configured to, when each thread executes each shard task, read the data on the i-th page within the range of the first number of rows of the shard task in the original table file; store the data within the range of the j-th second number of rows in the i-th page into a cache; sequentially write each row of data within the range of the j-th second number of rows in the cache into the initial table file stored locally according to the target field mapping relationship; j = j + 1; until all the data on the i-th page is written into the initial table file, i = i + 1; until all the data within the range of the first number of rows of the shard task is written, stop reading; the initial values of i and j are 0;

[0032] A copying module, configured to copy the data in all the initial table files to the target table file stored locally when the execution status of all shard tasks is completed.

[0033] In a third aspect, an embodiment of the present application further provides an electronic device, including: a processor, a storage medium, and a bus, where the storage medium stores machine-readable instructions executable by the processor. When the electronic device runs, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to perform the steps of the data writing method for a table file as described in any item of the first aspect.

[0034] Fourthly, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is run by a processor, it executes the steps of the data writing method for the table file as described in any item of the first aspect.

[0035] An embodiment of the present application provides a data writing method, device, electronic device and storage medium for a table file. The method includes: at preset time intervals, allocate all shard tasks with an execution status of to-be-executed to the threads in the thread pool according to the task execution rate and the current status of each thread; the shard tasks are obtained by sharding the data in the original table file; when each thread executes each shard task, read the data on the i-th page within the first row number range of the shard task from the original table file; store the data within the second row number range of the j-th in the i-th page into the cache; write each row of data within the second row number range of the j-th in the cache to the initial table file stored locally according to the target field mapping relationship; j = j + 1; until all the data on the i-th page is written to the initial table file, i = i + 1; until all the data within the first row number range of the shard task is written, stop reading; the initial values of i and j are 0; when the execution status of all shard tasks is completed, copy the data in all the initial table files to the target table file stored locally. The present application can improve the writing efficiency through multi-threading, reduce the memory occupation space during the writing process, and improve the security of the table file. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following will briefly introduce the drawings required to be used in the embodiments. It should be understood that the following drawings only show some embodiments of the present application, and thus should not be regarded as limiting the scope. For those of ordinary skill in the art, other related drawings can be obtained based on these drawings without creative efforts.

[0037] Figure 1 Shows a flowchart of a data writing method for a table file provided by an embodiment of the present application;

[0038] Figure 2 Shows a schematic flowchart of determining a target field mapping relationship provided by an embodiment of the present application;

[0039] Figure 3 Shows a schematic structural diagram of a data writing device for a table file provided by an embodiment of the present application;

[0040] Figure 4 Shows a schematic structural diagram of an electronic device provided by an embodiment of the present application. DETAILED DESCRIPTION

[0041] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the following will clearly and completely describe the technical solutions in the embodiments of this application with reference to the accompanying drawings in the embodiments of this application. It should be understood that the accompanying drawings in this application are only for the purposes of illustration and description, and are not used to limit the protection scope of this application. Additionally, it should be understood that the schematic drawings are not drawn to the scale of the actual objects. The flowcharts used in this application illustrate the operations implemented according to some embodiments of this application. It should be understood that the operations in the flowchart may not be implemented in sequence, and steps without a logical context relationship may be reversed in order or implemented simultaneously. In addition, those skilled in the art can add one or more other operations to the flowchart or remove one or more operations from the flowchart under the guidance of the content of this application.

[0042] In addition, the described embodiments are only a part of the embodiments of this application, rather than all of the embodiments. The components of the embodiments of this application usually described and illustrated in the accompanying drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of this application provided in the accompanying drawings is not intended to limit the scope of the claimed application, but merely represents the selected embodiments of this application. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative efforts fall within the protection scope of this application.

[0043] To enable those skilled in the art to use the content of this application, the following implementation manners are given in combination with a specific application scenario, the "data processing technology field". For those skilled in the art, without departing from the spirit and scope of this application, the general principles defined here can be applied to other embodiments and application scenarios. Although this application is mainly described around the "data processing technology field", it should be understood that this is only an exemplary embodiment.

[0044] It should be noted that the term "including" will be used in the embodiments of this application to indicate the existence of the features stated thereafter, but does not exclude adding other features.

[0045] The following provides a detailed description of a method for writing data into a tabular file provided by the embodiments of this application.

[0046] Refer to Figure 1 As shown, it is a flowchart of a method for writing data into a tabular file provided by the embodiments of this application. The following describes the exemplary steps of the embodiments of this application:

[0047] S101. At preset time intervals, allocate all shard tasks with an execution status of to-be-executed to the threads in the thread pool according to the task execution rate and the current status of each thread; the shard tasks are obtained by sharding the data in the original tabular file.

[0048] Step 1: Count the total number of rows in the original table file; slice the data in the original table file according to the first preset slice row number and the total number of rows in the original table file to obtain at least one slice task and the corresponding first row number range for each slice task.

[0049] In the embodiment of the present application, the first preset slice row number is achieved through repeated performance testing and adjustment. It can start from a relatively standard value (such as 5000 rows or 10000 rows) and be gradually adjusted according to the performance. If the slice is too large, the memory consumption will increase, the processing speed may decrease, and the parallel processing efficiency is low; if the slice is too small, although the parallel processing efficiency can be improved, it will lead to an increase in the overhead of management and scheduling, and the overall performance of the system will instead decline. The slicing method of the present application ensures both the parallel processing efficiency and the overall performance of the system.

[0050] Taking the total number of rows in the original table file as 10000 rows as an example, when slicing according to the first preset slice row number of 5000 rows, the first row number range corresponding to the first slice task is from 1 to 5000 rows; the first row number range corresponding to the second slice task is from 5001 to 10000 rows.

[0051] Here, the code for counting the total number of rows in the original table file is as follows: 1. Open the original table file: Workbook workbook = new XSSFWorkbook(inputFile); 2. Obtain the first Sheet: Sheet sheet = workbook.getSheetAt(0); 3. Traverse each row of the Sheet to count the total number of rows.

[0052] Further, the method further includes storing the slice task identifier, the minimum value in the first row number range, the maximum value in the first row number range, and the execution status in the slice task database correspondingly. The execution status of the slice task includes three statuses: completed, to be executed, and in execution.

[0053] Step 2: According to the preset time interval, and based on the task execution rate and the current status of each thread, allocate all slice tasks with the execution status of to be executed to the threads in the thread pool.

[0054] In the embodiment of the present application, the initial value of the task execution rate of each thread is set to 0. Initialize the thread pool: ExecutorService executor = Executors.newFixedThreadPool(10); Query all shard tasks with the execution status to be executed in the shard task database: SELECT * FROM data_shard WHERE status = 'PENDING'; According to the task execution rate, current status, and preset load balancing strategy of each thread, encapsulate all shard tasks with the execution status to be executed into Runnable and assign them to the threads in the thread pool. In Java, Runnable is an interface that belongs to the java.lang package. The Runnable interface allows objects of the class that implements it to be executed by a thread. In other words, any instance of a class that implements the Runnable interface can be used as the thread body and thus run in a separate thread.

[0055] Here, the load balancing strategies include: 1. Round-robin scheduling: Allocate shards to each thread pool or thread in turn in a round-robin manner, which is suitable for scenarios where the shard task volume is relatively balanced. 2. Weighted round-robin: According to the computational amount of the task, shard size, etc., assign different "weights" to each thread; the thread pool with a larger weight will be assigned more shards, while the thread pool with a smaller weight will process fewer shards. 3. Least connections first: If the workload differences of each thread pool are large, the "least connections first" strategy can be used to allocate new shards to the thread pool with the least current processing volume.

[0056] In addition, when querying shard tasks with the execution status to be executed, set priorities for each shard task according to the data volume, data complexity, and estimated processing time of each shard task; put the shard tasks to be executed into the global queue according to the priorities to preferentially execute the shard tasks with higher priorities.

[0057] For example, larger shards can be set with lower priorities, while smaller shards can be set with higher priorities to try to process small tasks first and avoid some threads in the thread pool waiting idle for large shards to complete.

[0058] S102. When each thread executes each shard task, read the data on the i-th page within the range of the first number of rows of the shard task in the original table file; store the data within the range of the j-th second number of rows on the i-th page into the cache; write each row of data within the range of the j-th second number of rows in the cache to the initial table file stored locally in sequence according to the target field mapping relationship; j = j + 1; until all the data on the i-th page is written to the initial table file, i = i + 1; until all the data within the range of the first number of rows of the shard task is written, stop reading; the initial values of i and j are 0.

[0059] In the embodiment of the present application, the data within the range of the first number of rows of the sharding task in the original table file is paged according to the second preset number of sharding rows (such as 500 rows); the data of each page is divided according to the third preset number of sharding rows (such as 100 rows) to obtain at least one range of the second number of rows. The third preset number of sharding rows is less than the second preset number of sharding rows.

[0060] Here, by paging and reading the data of the i-th page within the range of the first number of rows of the sharding task in the original table file, the data writing efficiency is ensured. By caching the data of each page multiple times and then writing it, the memory occupancy space is reduced.

[0061] Further, the method further includes: after all the data within the j-th range of the second number of rows in the cache are written into the initial table file, clearing all the data within the j-th range of the second number of rows in the cache.

[0062] Further, the method further includes: after all the data of the i-th page are written into the initial table file, clearing the data in the table database. The table database is used to store the data read from the original table file.

[0063] Further, the method further includes: after all the data of the i-th page are written into the initial table file, determining the maximum content length of each column in the initial table file; adjusting the cell width of each column according to the maximum content length of each column so that the proportion of the display length of the content of each cell in each column to the total content length is greater than the preset proportion.

[0064] Here, by recording the latest executed page number of the thread for this sharding task, the problem of data rewriting caused after the task interruption can be avoided.

[0065] Further, the method further includes: after all the data of the i-th page are written into the initial table file, determining the maximum content length of each column in the initial table file; adjusting the cell width of each column according to the maximum content length of each column so that the proportion of the display length of the content of each cell in each column to the total content length is greater than the preset proportion.

[0066] In the embodiment of the present application, the column width is dynamically adjusted according to the column content, improving the readability of the generated table. The cell width of each column is adjusted according to the maximum content length of each non-empty column so that the proportion of the display length of the content of each cell in each column to the total content length is greater than the preset proportion.

[0067] In addition, when adjusting the column width, in addition to considering the text length of the cell, the data format also needs to be considered. For example: for date and number columns, it may be necessary to consider their formatted display lengths to ensure that the column width can adapt to the formatted data.

[0068] Further, the method further includes: after the thread completes the execution of the shard task, determining the execution status of the shard task as completed; when the thread starts to execute the shard task, determining the execution status of the shard task as in execution.

[0069] Further, after the thread writes data of a preset number of rows into the initial table file each time, the execution progress of the shard task (represented by the number of rows) is updated. Based on the execution progress, the task execution rate of the thread can be calculated.

[0070] Referring to Figure 2 As shown, it is a schematic flowchart of the process for determining the target field mapping relationship provided by the embodiment of the present application. The following describes each step of the embodiment of the present application exemplarily:

[0071] S201. Obtain the attribute information corresponding to the template field in the table file template uploaded by the user, and the historical field mapping relationship corresponding to the template field.

[0072] In the embodiment of the present application, the table file template includes at least one template field and corresponding sample data. The attribute information corresponding to the template field is determined based on the usage and field of the data stored in the template field; the historical field mapping relationship refers to the historical mapping relationship between the template field and the original field in the original table file. If the field mapping relationship is included in the first mapping relationship database, the field mapping relationship in the first mapping relationship database is determined as the historical field mapping relationship; if the field mapping relationship is not included in the first mapping relationship database, the field mapping relationship in the second mapping relationship database is determined as the historical field mapping relationship.

[0073] Among them, the file type of the table file template can be Excel (.xls,.xlsx) and CSV.

[0074] The first mapping relationship database is a cache database; the second mapping relationship database is a long-term storage database.

[0075] Here, the first mapping relationship database uses hash storage, with the user_id (generated user ID) as the key and the template field and the original mapped_value field as the values. Example: HSET user:1 "Amount" "Invoice Total". The structure of the second mapping relationship database is designed as: user_id (generated user ID), field_name (template field), mapped_value (original field), timestamp (adjustment time); Example data storage: INSERT INTO field_mapping_adjustments (user_id, field_name, mapped_value, timestamp) VALUES (1, 'Amount', 'Invoice Total', '2024-12-04 12:34:56').

[0076] Specifically, obtain the attribute information corresponding to the template fields in the table file template uploaded by the generated user, including:

[0077] Step 1: Extract the names and sample data corresponding to the template fields in the table file template.

[0078] In the implementation mode of this application, Apache POI is used to parse the table file template to extract the column headers (i.e., template fields) and sample data. The full name of Apache POI is "Apache Portable Document Format (PDF) and Office Open XML (OOXML) Project", which is an open-source project that provides Java APIs to process files in Microsoft Office formats, including but not limited to Excel, Word, PowerPoint, etc.

[0079] Example: If the column headers in the table file template are "|Date|Amount|Name|" and the sample data for each column is "|2023-11-19|100.50|John Doe|", then the names corresponding to the extracted template fields are ["Date", "Amount", "Name"]. The sample data corresponding to the template fields is ["2023-11-19", "100.50", "John Doe"].

[0080] Step 2: Match the preset regular expressions corresponding to each preset field type with the sample data corresponding to the template fields to obtain the field types corresponding to the template fields; the preset field types are obtained by dividing according to the uses of the data in the original table file.

[0081] In the embodiments of the present application, the preset field types may include various types such as date, amount, text, etc., which are obtained by dividing according to the uses of the data in the original table file. The preset regular expression corresponding to the date may be `\d{4}-\d{2}-\d{2}`. The preset regular expression corresponding to the amount may be `^\d+(\.\d{1,2})?$`. The preset field type corresponding to the successfully matched regular expression is determined as the field type corresponding to the template field.

[0082] Step 3: Input the name and field type corresponding to the template field into the field meaning analysis model corresponding to the field, and obtain the field meaning corresponding to the template field.

[0083] In the embodiments of the present application, the field meaning analysis model is a pre-trained SpaCy model. The SpaCy model is optimized for a specific field (such as finance, law, medicine, etc.), and a domain-specific vocabulary and entity recognition model are loaded to obtain the field meaning analysis model corresponding to the specific field. The field meaning analysis model can infer the field meaning by combining the context information of the template field, and solve the problem of multiple meanings of homonymous fields; use a multilingual model or a customized multilingual training set to process fields in different languages in a globalized environment to ensure the compatibility of cross-border operations. For example, the "Date" field may represent the "creation date" in an order and the "payment date" in a payment. Therefore, the names and field types corresponding to all template fields are input into the field meaning analysis model corresponding to the field together, and the field meaning corresponding to each template field is obtained.

[0084] Example key code:

[0085]

[0086]

[0087] Among them, NLP (Natural Language Processing) is a broad field that involves multiple disciplines such as computer science, artificial intelligence, and linguistics, aiming to enable computers to understand, interpret, and generate human language. And SpaCy is a specific tool and library for implementing NLP tasks.

[0088] Step 4: Determine the name, field type, and field meaning corresponding to the template field as the attribute information corresponding to the template field.

[0089] S202: Determine the target rule that the attribute information corresponding to the template field conforms to from the pre-established rule library; determine the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule library includes an attribute information condition and a field mapping relationship conclusion.

[0090] In the embodiments of the present application, a rule in which the attribute information corresponding to the template field satisfies the attribute information condition is determined as the target rule. In the rule library, there is only one rule corresponding to each template field of each generated user. The rules in the rule library are used to determine the original fields having a mapping relationship with the template field.

[0091] Here, the rule library is a library in the business rule management system Drools. The business rule management system Drools allows developers to separate business rules from application code, enabling business analysts to also manage and maintain these rules. The rule library includes a rule file (in drl format) corresponding to each rule.

[0092] Rule example:

[0093]

[0094]

[0095] Among them, "Field(name == \"Date\" || semanticType == \"Time\")" is the attribute information condition, and "FieldMapping.targetField = \"Transaction Date\"" is the conclusion of the field mapping relationship. The above example is used to indicate that when the name of the template field is Date and the field type semanticType is Time, the template field targetField is mapped to the original field Transaction Date.

[0096] Specifically, the process of rule triggering is to pass the parsed fields as fact objects (Fact) to the rule engine Drools, and Drools generates field mappings according to the rule files.

[0097] Example: - Input template fields: `["Date", "Amount", "Name"]`, output mapping relationship: Date -> Transaction Date, Amount -> Transaction Amount, Name -> CustomerName.

[0098] S203. Determine the target field mapping relationship corresponding to the template field according to the first similarity between the initial field mapping relationship and each historical field mapping relationship, and the second similarity between the preset field meanings of the original fields in each two historical field mapping relationships.

[0099] In the embodiments of the present application, the cosine similarity between the initial field mapping relationship and each historical field mapping relationship is calculated to obtain the first similarity between the initial field mapping relationship and each historical field mapping relationship. For every two historical field mapping relationships, the cosine similarity between the preset field meanings of the original fields in the two historical field mapping relationships is calculated to obtain the second similarity between the preset field meanings of the original fields in the two historical field mapping relationships.

[0100] Example 1, the initial field mapping relationship is A, and the historical field mapping relationships include B, C, and D; then, according to the first similarity between A and B, the first similarity between A and C, the first similarity between A and D, the second similarity between the original fields in B and the original fields in C, the second similarity between the original fields in B and the original fields in D, and the second similarity between the original fields in C and the original fields in D, the target field mapping relationship corresponding to the template field is determined.

[0101] Determining the target field mapping relationship corresponding to the template field includes:

[0102] Step 1: Determine the first recommended field mapping relationships as the historical field mapping relationships with the highest first similarity among the preset number.

[0103] Example 2, in the scenario of Example 1, the first similarity between A and B is greater than the first similarity between A and C, and the first similarity between A and C is greater than the first similarity between A and D; the preset number is 2, then B and C are determined as the first recommended field mapping relationships.

[0104] Step 2: Determine the second recommended field mapping relationships from the two first recommended field mapping relationships with the second similarity less than the preset similarity.

[0105] In the embodiments of the present application, the first recommended field mapping relationship with the most recent relationship configuration time among the two first recommended field mapping relationships is determined as the second recommended field mapping relationship.

[0106] Example 3, in the scenario of Example 2, the relationship configuration time of B is earlier than that of C. If the second similarity between the original fields in B and the original fields in C is less than the preset similarity, then C is determined as the second recommended field mapping relationship.

[0107] Step 3: Determine the initial field mapping relationship and the two first recommended field mapping relationships with the second similarity greater than or equal to the preset similarity as the second recommended field mapping relationships.

[0108] Example 3. In the scenario of Example 2, if the second similarity between the original fields in B and the original fields in C is greater than the preset similarity, it indicates that the field semantics between B and C are not similar. Therefore, A, B, and C are determined as the second recommended field mapping relationship.

[0109] Here, when generating the field mapping relationship recommended for the user, in addition to considering the attribute information of the template fields, the present application also considers the field meanings of the historical field mapping relationships of the template fields, improving the recommendation accuracy and user experience.

[0110] Step 4. Send the second recommended field mapping relationship to the generating user, so that the generating user can determine the target field mapping relationship from the second recommended field mapping relationship or manually input the target field mapping relationship corresponding to the template field.

[0111] In the embodiment of the present application, all the second recommended field mapping relationships are sorted according to the first similarity to obtain a mapping list; the greater the first similarity corresponding to the second recommended field mapping relationship, the more forward the position in the mapping list; and the mapping list relationship is sent to the generating user. The generating user can select a field mapping relationship from the second recommended field mapping relationships as the target field mapping relationship, or can also execute the configuration of the target field mapping relationship corresponding to the template field.

[0112] Further, if the target field mapping relationship is not in the second recommended field mapping relationship, then write the target field mapping relationship into the first mapping relationship database and the second mapping relationship database.

[0113] In the embodiment of the present application, if the target field mapping relationship is not in the second recommended field mapping relationship, it indicates that the target field mapping relationship is manually configured by the generating user and is not recommended. Therefore, it is necessary to write the target field mapping relationship into the first mapping relationship database and the second mapping relationship database to improve the recommendation accuracy for the next time.

[0114] Further, the method further includes: obtaining the latest field mapping relationship within a preset period from the second mapping relationship database according to a preset period; and updating the rules in the rule library according to the latest field mapping relationship.

[0115] In the embodiment of the present application, if there is a to-be-updated rule corresponding to the generating user and the template field in the latest field mapping relationship stored in the rule library, then update the original field in the field mapping relationship conclusion of the to-be-updated rule to the original field in the latest field mapping relationship; if there is no to-be-updated rule corresponding to the generating user and the template field in the latest field mapping relationship stored in the rule library, then generate a latest rule according to the attribute information of the template field in the latest field mapping relationship and the latest field mapping relationship; and store the latest rule into the rule library.

[0116] Here, the rule to be updated refers to the rule in the rule library corresponding to the generated user and template fields. If there is no rule to be updated in the rule library corresponding to the template field in the mapping relationship between the generated user and the latest fields, the attribute information of the template field in the latest field mapping relationship is used as the attribute information condition, and the latest field mapping relationship is determined as the field mapping relationship conclusion to obtain the latest rule.

[0117] S103. When the execution status of all shard tasks is completed, copy the data in all initial table files to the target table file stored locally.

[0118] Furthermore, if the target table file is not encrypted or the encryption duration of the current encryption key is greater than the preset duration, generate an encryption key; encrypt the target table file with the encryption key; store the identifier of the encryption key and the storage path of the target table file in the key database correspondingly; or determine the identifier of the encryption key in the key database corresponding to the storage path of the target table file as the identifier of the newly generated encryption key.

[0119] Specifically, if the target spreadsheet file is not encrypted, an AES key is dynamically generated through the Vault API and encrypted using AES-256 (i.e., a 256-bit key length). This ensures high-strength encryption protection. Example API for obtaining the key: vaultClient.getSecret("encryption_keys / aes-256"); The key is dynamically generated and can be used to encrypt multiple files. Key identifier storage: Each generated key will have a unique key identifier, and this identifier is associated with the file path and stored in the MySQL database to ensure the association between the file and the key. Example key database storage: INSERT INTO file_metadata(file_name, key_identifier) VALUES('file.xlsx', 'key123'). Set up the AES encryption tool using the Java Cryptography API, select the appropriate encryption mode, and initialize the key. Code example: Cipher cipher = Cipher.getInstance("AES"); cipher.init(Cipher.ENCRYPT_MODE, secretKey). Encrypt the file block by block: Read the file input stream and encrypt it block by block to prevent memory overload, which is suitable for processing large files. Use CipherOutputStream for encrypted output: FileInputStream fis = new FileInputStream(originalFile); CipherOutputStream cos = new CipherOutputStream(new FileOutputStream(encryptedFile), cipher); byte[] buffer = new byte

[1024] ; int bytesRead; while ((bytesRead = fis.read(buffer)) != -1) { cos.write(buffer, 0, bytesRead);} cos.close(); fis.close(). The code for storing the identifier of the encryption key and the storage path of the target spreadsheet file corresponding to each other in the key database is: INSERT INTO file_metadata(file_name, key_identifier) VALUES('file.xlsx', 'key123').

[0120] Specifically, if the encryption duration of the current encryption key of the target spreadsheet file is greater than the preset duration, in order to ensure the security of the key, it is necessary to rotate the key regularly. Use the Quartz scheduling task framework to check the validity period of the key regularly, and trigger a scheduling task when the key expires or reaches the rotation period. The scheduling task is started through Quartz and checks whether the key has expired at regular intervals (for example, monthly or quarterly). If it has expired, a new key is generated: the code for generating a new AES key is: vaultClient.createSecret("encryption_keys / aes-256-new"), and the new key identifier is returned. Update the key identifier in the key database. When the new key is generated, the key identifier related to the file needs to be updated. Update these metadata to ensure that the file is associated with the new key. Each file corresponds to a key identifier, and we need to replace the old key identifier of the file with the new key identifier. For example, the update statement is as follows: UPDATE file_metadata SET key_identifier = 'new_key_identifier' WHERE file_name = 'file.xlsx'; The above SQL will update the key identifier of the file file.xlsx to the new key identifier to ensure that the file uses the latest key.

[0121] Here, when re-encrypting the target spreadsheet file with the encryption key, the target spreadsheet file can be first decrypted with the old encryption key and then encrypted with the new encryption key.

[0122] In addition, the old key should not be deleted immediately and should be retained for at least a certain period of time for rollback or decrypting historical files. The key version management provided by Vault can help solve this problem. Ensure the security during the key rotation process and prevent key leakage. All sensitive operations (such as key updates) should be carried out in a protected environment and unnecessary permissions should be restricted through access control.

[0123] Furthermore, after copying the data in all initial spreadsheet files to the target spreadsheet file stored locally, the method further includes:

[0124] Step 1: Determine the hash value of the target spreadsheet file as the digest of the target spreadsheet file.

[0125] In the embodiment of the present application, the SHA-256 hash value of the file is calculated using the Java Cryptography API as the file digest.

[0126] Step 2: Digitally sign the digest of the target spreadsheet file with the RSA private key.

[0127] In the embodiments of the present application, the RSA private key is used to sign the file hash value to generate a digital signature.

[0128] Step 3: Store the digital signature and the name of the target table file in the digital signature database in a corresponding manner.

[0129] Example: `INSERT INTO file_metadata(file_name,digital_signature)VALUES('file.xlsx',base64EncodedSignature);`.

[0130] In the embodiments of the present application, the RSA private key is dynamically loaded from Vault for the signature operation; Example: Use the Vault API to obtain the private key: `vaultClient.getSecret("keys / rsa-private")`.

[0131] Further, the method further includes:

[0132] Step 1: In response to a download request initiated by a file download user, authenticate the file download user.

[0133] In the embodiments of the present application, OAuth2 or JWT is used to authenticate the identity of the file download user. Query the user permission record to verify whether the user has the decryption permission for the target file. Example query: SELECT FROM user_permissions WHERE user_identifier =? AND file_name =?.

[0134] Step 2: If the verification is passed, obtain the key identifier of the table file corresponding to the download request from the key database.

[0135] Step 3: Decrypt the table file according to the key corresponding to the key identifier.

[0136] In the embodiments of the present application, according to the key identifier, obtain the corresponding encryption key from Vault. Use the key to decrypt the encrypted file and provide it to the user for download.

[0137] Step 4: Send the decrypted table file to the file download user for download.

[0138] Further, after copying the data in all the initial table files to the target table file stored locally, the method further includes:

[0139] Step 1: Store the target table file at a preset storage address in a preset main storage bucket of the Alibaba Cloud OSS SDK.

[0140] In the embodiments of the present application, the target table file is uploaded to the specified main storage bucket using the Alibaba Cloud OSS SDK, and a storage address is specified for the target table file. The preset storage address of the target table file can be generated according to the current date or other rules.

[0141] The generation code for the preset storage address is like: bucket-name / 2024 / 11 / 26 / generated-file.xlsx.

[0142] In addition, the target table file is copied from the main storage bucket to the backup storage bucket to complete the backup. The backup storage bucket and the main storage bucket are set in different geographical regions to improve file security.

[0143] Step Two: Generate a download link with an expiration date for the target table file.

[0144] In the embodiments of the present application, a signed URL is generated for the target table file. File download users can download the file through this URL, and the link will expire within the specified time.

[0145] Step Three: Store the information of the download link in the link database.

[0146] In the embodiments of the present application, in order to implement anti-leeching, the download link information needs to be stored in Redis, including the identifier of the target table file, the identifier of the generating user, the IP address of the generating user, and the expiration date, etc. This will help with verification during download.

[0147] Step Two: If the verification passes, obtain the table file corresponding to the download request from the main storage bucket of the Alibaba Cloud OSS SDK, and obtain the key identifier of the table file corresponding to the download request from the key database.

[0148] The method further includes: after the target table file is written, storing the identifier of the generating user, the identifier of the target table file, the generation time, and the generation status (success or failure) in the log correspondingly; after the target table file is uploaded to the main storage bucket, storing the identifier of the target table file, the file size, the upload time consumption, and the storage path in the log correspondingly; after the file download user downloads the corresponding table file, storing the identifier of the file download user, the IP address of the file download user, the download time, and the download link verification result in the log correspondingly.

[0149] Here, configure a visualization panel to support querying the log by time range, task status, and user dimension.

[0150] The method further includes: extracting all log data in the log according to a preset log viewing time interval; sorting the log data of each user by time; performing a sliding time window (10 minutes) on the sorted log data, and counting the number of downloaded files and / or the number of generated files of the user in each time window; if the number of downloaded files and / or the number of generated files is greater than a preset number, an alarm is triggered.

[0151] In the embodiment of the present application, if the number of downloaded files and / or the number of generated files is greater than a preset number, this behavior is considered abnormal. These abnormal behaviors may be manifestations of malicious operations, abuse of services, or system failures. Once an abnormal behavior is detected, the system will trigger an alarm. The alarm can be notified through instant messaging tools (such as Enterprise WeChat, DingTalk, etc.) to ensure that relevant personnel can handle the abnormal situation in a timely manner. Alarm notification: The alarm message will include the user identifier triggering the abnormality, the specific situation of the abnormal behavior (such as downloading more than 50 times within 10 minutes, and information such as the time when the abnormal behavior occurred). The system can push this information to a preset alarm platform (such as Enterprise WeChat or DingTalk) through an HTTP request for subsequent inspection and processing by the staff.

[0152] The embodiment of the present application provides a method for writing data into a tabular file. The method includes: allocating all shard tasks with an execution status of to-be-executed to threads in a thread pool according to a preset time interval, based on the task execution rate and the current status of each thread; the shard tasks are obtained by sharding the data in the original tabular file; when each thread executes each shard task, reading the data on the i-th page within the range of the first number of rows of the shard task from the original tabular file; storing the data within the range of the second number of rows of the j-th in the i-th page into a cache; writing each row of data within the range of the second number of rows of the j-th in the cache into the initial tabular file stored locally according to the target field mapping relationship; j = j + 1; until all the data on the i-th page is written into the initial tabular file, i = i + 1; until all the data within the range of the first number of rows of the shard task is written, stop reading; the initial values of i and j are 0; when the execution status of all shard tasks is completed, copying the data in all the initial tabular files to the target tabular file stored locally. The present application can improve the writing efficiency through multi-threading, reduce the memory occupation space during the writing process, and improve the security of the tabular file.

[0153] An embodiment of the present application provides a method for writing data into a table file. The method includes: allocating all shard tasks with an execution status of to-be-executed to threads in a thread pool according to the task execution rate and current status of each thread; reading the data of the i-th page within the first row number range of the shard task in the original table file; storing the data within the second row number range of the j-th in the i-th page into a cache; writing each row of data within the second row number range of the j-th in the cache into the initial table file stored locally in sequence according to the target field mapping relationship; j = j + 1; until all data of the i-th page is written into the initial table file, i = i + 1; copying the data in all initial table files to the target table file stored locally. The present application can improve the writing efficiency through multi-threading, reduce the memory occupation space during the writing process, and improve the security of the table file.

[0154] Based on the same inventive concept, an embodiment of the present application also provides a data writing device for a table file corresponding to the data writing method for a table file. Since the principle of solving problems by the device in the embodiment of the present application is similar to that of the above-mentioned data writing method for a table file in the embodiment of the present application, the implementation of the device can refer to the implementation of the method, and the repeated parts will not be elaborated.

[0155] Refer to Figure 3 As shown in

[0156] An allocation module 301, configured to allocate all shard tasks with an execution status of to-be-executed to threads in a thread pool at a preset time interval according to the task execution rate and current status of each thread; the shard tasks are obtained by sharding the data in the original table file;

[0157] A writing module 302, configured to, when each thread executes each shard task, read the data of the i-th page within the first row number range of the shard task in the original table file; store the data within the second row number range of the j-th in the i-th page into a cache; write each row of data within the second row number range of the j-th in the cache into the initial table file stored locally in sequence according to the target field mapping relationship; j = j + 1; until all data of the i-th page is written into the initial table file, i = i + 1; until all data within the first row number range of the shard task is written, stop reading; the initial values of i and j are 0;

[0158] A copying module 303, configured to copy the data in all initial table files to the target table file stored locally when the execution status of all shard tasks is completed.

[0159] An embodiment of the present application provides a data writing device for a table file, which can improve the writing efficiency through multi-threading, reduce the memory occupied during the writing process, and improve the security of the table file.

[0160] As Figure 4 shown, an electronic device 400 provided by an embodiment of the present application includes: a processor 401, a memory 402, and a bus. The memory 402 stores machine-readable instructions executable by the processor 401. When the electronic device runs, communication between the processor 401 and the memory 402 is carried out through the bus, and the processor 401 executes the machine-readable instructions to perform the steps of the data writing method for the table file as described above.

[0161] Specifically, the above-mentioned memory 402 and processor 401 can be general-purpose memory and processor, which are not specifically limited here. When the processor 401 runs the computer program stored in the memory 402, it can execute the data writing method for the table file as described above.

[0162] Corresponding to the data writing method for the table file as described above, an embodiment of the present application also provides a computer-readable storage medium. A computer program is stored on the computer-readable storage medium, and when the computer program is run by a processor, it executes the steps of the data writing method for the table file as described above.

[0163] Those skilled in the art can clearly understand that for the convenience and conciseness of description, the specific working processes of the systems and devices described above can refer to the corresponding processes in the method embodiments, which will not be elaborated in the present application. In the several embodiments provided by the present application, it should be understood that the disclosed systems, devices, and methods can be implemented in other ways. The device embodiments described above are only illustrative. For example, the division of the modules is only a logical function division, and there can be other division methods in actual implementation. Also, for example, multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the couplings or direct couplings or communication connections shown or discussed with each other can be through some communication interfaces. The indirect couplings or communication connections of the devices or modules can be in electrical, mechanical, or other forms.

[0164] The modules described as separate components may or may not be physically separated. The components shown as modules may or may not be physical units, that is, they can be located in one place, or they can be distributed to multiple network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0165] In addition, in each embodiment of the present application, each functional unit may be integrated into one processing unit, may exist separately as individual physical units, or two or more units may be integrated into one unit.

[0166] If the above-mentioned function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a non-volatile computer-readable storage medium executable by a processor. Based on such an understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the information processing method described in each embodiment of the present application. The foregoing storage medium includes: various media such as USB flash drives, mobile hard disks, ROM, RAM, magnetic disks, or optical discs that can store program codes.

[0167] The above is only the specific implementation manner of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art within the technical scope disclosed by the present application can easily think of changes or substitutions, which should all be covered by the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the protection scope of the claims.

Claims

1. A method for writing data into a tabular file, characterized in that, The method includes: At preset time intervals, all shard tasks with an execution status of to-be-executed are assigned to threads in the thread pool according to the task execution rate and current status of each thread; the shard tasks are obtained by sharding the data in the original table file; When each thread executes each shard task, it reads the data on the i-th page within the range of the first number of rows of the shard task in the original table file; stores the data within the range of the j-th second number of rows in the i-th page into the cache; writes each row of data within the range of the j-th second number of rows in the cache to the initial table file stored locally in sequence according to the target field mapping relationship; j = j + 1; until all data on the i-th page is written to the initial table file, i = i + 1; until all data within the range of the first number of rows of the shard task is written, stop reading; the initial values of i and j are 0; When the execution status of all shard tasks is completed, copy the data in all initial table files to the target table file stored locally; If the target table file is not encrypted or the encryption duration of the current encryption key is greater than the preset duration, generate an encryption key; encrypt the target table file with the encryption key; store the identifier of the encryption key and the storage path of the target table file correspondingly in the key database; or determine the identifier of the encryption key corresponding to the storage path of the target table file in the key database as the identifier of the newly generated encryption key; Determine the hash value of the target table file as the digest of the target table file; perform a digital signature on the digest of the target table file with the RSA private key; store the digital signature and the name of the target table file correspondingly in the digital signature database; The method further includes: after all data on the i-th page is written to the initial table file, determine the i-th page as the latest execution page of the thread for the shard task; after the shard task is interrupted and resumed, start writing data to the initial table file from the next page of the latest execution page of the thread for the shard task.

2. The data writing method for the table file according to claim 1, wherein The method further includes: After all data within the range of the j-th second number of rows in the cache is written to the initial table file, clear all data within the range of the j-th second number of rows in the cache.

3. The data writing method for the table file according to claim 1, wherein The method further includes: After all data on the i-th page is written to the initial table file, determine the maximum content length of each column in the initial table file; Adjust the cell width of each column according to the maximum content length of each column, so that the display length of the content of each cell in each column accounts for a proportion greater than the preset proportion of the total content length.

4. The data writing method for a table file according to claim 1, characterized in that The method further includes: In response to a download request initiated by a file download user, authenticate the file download user; If the verification is passed, obtain the key identifier of the table file corresponding to the download request from the key database; Decrypt the table file with the key corresponding to the key identifier; Send the decrypted table file to the file download user for download.

5. A data writing device for a table file, characterized in that, The device includes: An allocation module, configured to allocate all shard tasks with an execution status of to-be-executed to threads in a thread pool according to a preset time interval, based on the task execution rate and the current status of each thread; the shard tasks are obtained by sharding data in an original table file; A writing module, configured to, when each thread executes each shard task, read the data on the i-th page within the range of the first number of rows of the shard task from the original table file; store the data within the range of the j-th second number of rows in the i-th page into a cache; write each row of data within the range of the j-th second number of rows in the cache into an initial table file stored locally in sequence according to a target field mapping relationship; j = j + 1; until all data on the i-th page is written into the initial table file, i = i + 1; until all data within the range of the first number of rows of the shard task is written, stop reading; the initial values of i and j are 0; A copying module, configured to, when the execution status of all shard tasks is completed, copy the data in all initial table files to a target table file stored locally; The copying module is further configured to, if the target table file is not encrypted or the encryption duration of the current encryption key is greater than a preset duration, generate an encryption key; encrypt the target table file with the encryption key; store the identifier of the encryption key and the storage path of the target table file correspondingly into a key database; or determine the identifier of the encryption key corresponding to the storage path of the target table file in the key database as the identifier of the newly generated encryption key; The copying module is further configured to determine the hash value of the target table file as the digest of the target table file; perform a digital signature on the digest of the target table file with an RSA private key; store the digital signature and the name of the target table file correspondingly into a digital signature database; The writing module is further configured to, after all data on the i-th page is written into the initial table file, determine the i-th page as the latest executed page of the thread for the shard task; after the shard task is interrupted and resumed, start writing data into the initial table file from the next page of the latest executed page of the thread for the shard task.

6. An electronic device, characterized in that, Comprising: A processor, a storage medium, and a bus, where the storage medium stores machine-readable instructions executable by the processor. When the electronic device runs, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to perform the steps of the data writing method for a table file according to any one of claims 1 to 4.

7. A computer-readable storage medium, characterized in that, A computer program is stored on the computer-readable storage medium, and when the computer program is run by a processor, it performs the steps of the data writing method for a table file according to any one of claims 1 to 4.

Images