Data writing method and device of table file, electronic equipment and storage medium

Abstract

The invention provides a data writing method and device for a table file, electronic equipment and a storage medium, and the method comprises the steps: distributing all fragmented tasks of which the execution states are to be executed to threads in a thread pool according to the task execution rate and the current state of each thread; reading data of an ith page in a first line number range of the fragmentation task in the original table file; storing data in the range of the jth second row number in the ith page into a cache; sequentially writing each row of data in the jth second row number range in the cache into a locally stored initial table file according to the target field mapping relationship; j = j + 1; i = i + 1 until all data of the ith page are written into the initial table file; and copying the data in all the initial form files to a locally stored target form file. According to the method, the writing efficiency can be improved through multiple threads, the memory occupied space in the writing process is reduced, and the safety of the form file is improved.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, 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 of generating table files is that users manually write data into table files.

[0003] However, the traditional writing method has low generation efficiency when the data volume is large, and it is difficult to meet the needs of high-concurrency scenarios. In addition, the file generation takes up a lot of memory, which can easily cause program crashes. There is a lack of a complete security protection mechanism, and files may be tampered with or leaked during transmission and storage. Summary of the invention

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

[0005] In a first aspect, an embodiment of the present application provides a method for writing data to a table file, the method for writing data to a table file comprising:

[0006] At a preset time interval, according to the task execution rate and current state of each thread, all shard tasks in the execution state of pending execution are assigned to 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 sharding task, it reads the data of the i-th page within the first row number range of the sharding task in the original table file; stores the data within the j-th second row number range of the i-th page in the cache; writes each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence 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; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0;

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

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

[0010] After all data within the j-th second line number range in the cache are written into the initial table file, all data within the j-th second line number range in the cache are cleared.

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

[0012] After all data of the i-th page are written into the initial table file, the i-th page is determined as the latest execution page number of the thread for the slice task;

[0013] After the interruption of the slice task is resumed, data is written to the initial table file starting from the next page of the latest execution page number of the slice task by the thread.

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

[0015] After all 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;

[0016] The cell width of each column is adjusted according to the maximum content length of each column, so that the ratio of the content display length of each cell in each column to the total content length is greater than a preset ratio.

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

[0018] If the target table file is not encrypted or the encryption time length of the current encryption key is greater than the preset time length, an encryption key is generated; and the target table file is encrypted by the encryption key;

[0019] The identifier of the encryption key and the storage path of the target table file are stored in a key database in correspondence; or the identifier of the encryption key corresponding to the storage path of the target table file in the key database is determined as the identifier of the newly generated encryption key.

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

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

[0022] Digitally sign the summary of the target table file using an RSA private key;

[0023] The digital signature and the name of the target table file are stored in a digital signature database correspondingly.

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

[0025] In response to a download request initiated by a file download user, authenticating the file download user;

[0026] If the verification is successful, obtaining the key identifier of the table file corresponding to the download request from the key database;

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

[0028] The decrypted table file is sent to the file downloading user for downloading.

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

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

[0031] The writing module is used for each thread to read the data of the i-th page within the first row number range of the sharding task in the original table file when executing each sharding task; store the data within the j-th second row number range of the i-th page in the cache; write each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence 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; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0;

[0032] The copy module is used to copy the data in all the initial table files to the target table files stored locally when the execution status of all the sharding tasks is completed.

[0033] In the third aspect, an embodiment of the present application also provides an electronic device, comprising: a processor, a storage medium and a bus, wherein the storage medium stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to perform the steps of the method for writing data to a table file as described in any one of the first aspects.

[0034] In a fourth aspect, 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 executed by a processor, the steps of the method for writing data to a table file as described in any one of the first aspects are executed.

[0035] The embodiment of the present application provides a data writing method, device, electronic device and storage medium for a table file, the method comprising: at a preset time interval, according to the task execution rate and current state of each thread, all sharding tasks with an execution state to be executed are assigned to threads in a thread pool; the sharding task is obtained by sharding the data in the original table file; when each thread executes each sharding task, it reads the data of the i-th page within the first row number range of the sharding task in the original table file; the data within the j-th second row number range in the i-th page is stored in a cache; each row of data within the j-th second row number range in the cache is sequentially written into the locally stored initial table file 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 sharding task is written, stop reading; the initial values ​​of i and j are 0; when the execution state of all sharding tasks is completed, the data in all initial table files are copied to the locally stored target table file. The present application can improve writing efficiency through multithreading, reduce memory usage during writing, and improve table file security. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0037] Figure 1 A flow chart showing a method for writing data into a table file provided in an embodiment of the present application is shown;

[0038] Figure 2 A schematic diagram of a process for determining a target field mapping relationship provided in an embodiment of the present application is shown;

[0039] Figure 3 A schematic diagram showing the structure of a device for writing data into a table file provided in an embodiment of the present application is shown;

[0040] Figure 4 A schematic structural diagram of an electronic device provided in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0041] To make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the drawings in the present application only serve the purpose of explanation and description and are not used to limit the scope of protection of the present application. In addition, it should be understood that the schematic drawings are not drawn in real proportion. The flowchart used in this application shows the operations implemented according to some embodiments of the present application. It should be understood that the operations of the flowchart can be implemented out of sequence, and the steps without logical context can be reversed in order or implemented simultaneously. In addition, those skilled in the art can add one or more other operations to the flowchart under the guidance of the content of the present application, or remove one or more operations from the flowchart.

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

[0043] In order to enable those skilled in the art to use the content of this application, the following implementation is provided in conjunction with the specific application scenario "data processing technology field". For those skilled in the art, the general principles defined herein can be applied to other embodiments and application scenarios without departing from the spirit and scope of this application. 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 "comprising" will be used in the embodiments of the present application to indicate the existence of the features declared thereafter, but does not exclude the addition of other features.

[0045] The following is a detailed description of a method for writing data to a table file provided in an embodiment of the present application.

[0046] Reference Figure 1 As shown, it is a flow chart of a method for writing data to a table file provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:

[0047] S101. Allocate all sharding tasks in the pending execution state to threads in a thread pool at a preset time interval and according to the task execution rate and current state of each thread; the sharding tasks are obtained by sharding the data in the original table 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 number of slice rows and the total number of rows in the original table file to obtain at least one slice task and a first range of rows corresponding to each slice task.

[0049] In the implementation manner of the present application, the first preset number of shard rows is achieved through repeated performance testing and adjustment, and can start from a relatively standard value (such as 5000 rows or 10000 rows) and gradually adjust according to performance. If the shard is too large, memory consumption will increase, processing speed may decrease, and parallel processing efficiency will be low; if the shard is too small, although the parallel processing efficiency can be improved, it will lead to increased management and scheduling overhead, and the overall system performance will decrease. The sharding method of the present application not only ensures parallel processing efficiency, but also ensures overall system performance.

[0050] Taking the total number of rows in the original table file as 10,000 as an example, sharding is performed according to the first preset sharding number of 5,000 rows. The first row number corresponding to the first sharding task ranges from 1 to 5,000 rows; the first row number corresponding to the second sharding task ranges from 5,001 to 10,000 rows.

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

[0052] Furthermore, the method further includes storing the shard 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 shard task database. The execution status of the shard task includes three states: completed, pending, and executing.

[0053] Step 2: At a preset time interval, according to the task execution rate and current state of each thread, assign all shard tasks with an execution state of pending to be executed to threads in the thread pool.

[0054] In the implementation manner 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 the shard task database for all shard tasks with an execution status to be executed: SELECT * FROM data_shard WHERE status = 'PENDING'; according to the task execution rate, current status, and preset load balancing strategy of each thread, all shard tasks with an execution status to be executed are encapsulated as Runnable and assigned to threads in the thread pool. In Java, Runnable is an interface that belongs to the java.lang package. The Runnable interface allows objects of classes that implement it to be executed by threads. In other words, any instance of a class that implements the Runnable interface can be used as a thread body and thus run in a separate thread.

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

[0056] In addition, when the query execution status is a shard task to be executed, a priority is set for each shard task according to the data volume, data complexity, and estimated processing time of each shard task; the shard tasks to be executed are placed in the global queue according to the priority, so that the shard tasks to be executed with high priority are executed first.

[0057] For example, a larger shard can be set with a lower priority, while a smaller shard can be set with a higher priority, so that small tasks can be processed first as much as possible to avoid some threads in the thread pool being idle waiting for the large shard to complete.

[0058] S102. When executing each sharding task, each thread reads the data of the i-th page within the first row number range of the sharding task in the original table file; stores the data within the j-th second row number range of the i-th page in the cache; writes each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence 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; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0.

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

[0060] Here, the data of the i-th page within the first row number range of the shard task in the original table file is read by paging, which ensures the data writing efficiency. The memory usage is reduced by caching the data of each page multiple times before writing.

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

[0062] Furthermore, the method further comprises: 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] Furthermore, the method also includes: after all data of the i-th page are written to the initial table file, the i-th page is determined as the latest execution page number of the thread for the shard task; after the shard task is interrupted and restored, data is written to the initial table file starting from the next page of the latest execution page number of the thread for the shard task.

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

[0065] Furthermore, the method also includes: after all 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 content display length of each cell in each column accounts for a ratio of the total content length that is greater than a preset ratio.

[0066] In the implementation mode of the present application, the column width is dynamically adjusted according to the column content to improve 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 content display length of each cell in each column accounts for a greater proportion of the total content length than a preset proportion.

[0067] In addition, when adjusting the column width, in addition to considering the text length of the cell, you also need to consider the data format. For example, for date and number columns, you may need to consider their display length after formatting to ensure that the column width can adapt to the formatted data.

[0068] Furthermore, the method also includes: after the thread completes the execution of the slice task, determining the execution status of the slice task as completed; when the thread starts to execute the slice task, determining the execution status of the slice task as being executed.

[0069] Furthermore, after the thread writes a preset number of rows of data into the initial table file, the execution progress of the sharding task is updated (represented by the number of rows). The task execution rate of the thread can be calculated based on the execution progress.

[0070] Reference Figure 2 As shown, it is a schematic diagram of a process for determining a target field mapping relationship provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:

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

[0072] In the implementation mode 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 purpose 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 first mapping relationship database contains a field mapping relationship, the field mapping relationship in the first mapping relationship database is determined as a historical field mapping relationship; if the first mapping relationship database does not contain a field mapping relationship, the field mapping relationship in the second mapping relationship database is determined as a historical field mapping relationship.

[0073] The file types 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, user_id (generated user ID) as the key, template field and mapped_value original field as the value. 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, the attribute information corresponding to the template field in the table file template uploaded by the user is obtained, including:

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

[0078] In the implementation of the present application, Apache POI is used to parse the table file template and 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 a Java API to process files in Microsoft Office formats, including but not limited to Excel, Word, PowerPoint, etc.

[0079] Example: The column headers in the table file template are "|Date|Amount|Name|" and the sample data of 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 are ["2023-11-19","100.50","John Doe"].

[0080] Step 2: Match the preset regular expression corresponding to each preset field type with the sample data corresponding to the template field to obtain the field type corresponding to the template field; the preset field type is divided according to the purpose of the data in the original table file.

[0081] In the implementation manner of the present application, the preset field type may include various types such as date, amount, text, etc., which are divided according to the purpose 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 domain to obtain the field meaning corresponding to the template field.

[0083] In the implementation manner of the present application, the field meaning analysis model is a pre-trained SpaCy model. The SpaCy model is optimized for specific fields (such as finance, law, medical care, etc.), and the field-specific vocabulary and entity recognition model are loaded to obtain a field meaning analysis model corresponding to the specific field. The field meaning analysis model can combine the contextual information of the template field to infer the field meaning and solve the problem of multiple meanings of fields with the same name; use a multilingual model or customize a multilingual training set to process fields in different languages ​​in a global environment to ensure the compatibility of cross-border business. For example, the "Date" field may mean "creation date" in an order and "payment date" in a payment. Therefore, the names and field types corresponding to all template fields are input together into the field meaning analysis model corresponding to the field to obtain the field meaning corresponding to each template field.

[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, with the goal of enabling computers to understand, interpret, and generate human language. 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, determining from a pre-established rule base the target rule that the attribute information corresponding to the template field complies with; determining 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 base includes an attribute information condition and a field mapping relationship conclusion.

[0090] In the implementation of the present application, the rule whose attribute information corresponding to the template field meets the attribute information condition is determined as the target rule. In the rule base, there is only one rule corresponding to each template field of each generated user, and the rules in the rule base are used to determine the original field with a mapping relationship with the template field.

[0091] Here, the rule base 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 so that business analysts can also manage and maintain these rules. The rule base includes the rule file (drl format) corresponding to each rule.

[0092] Example of rule:

[0093]

[0094]

[0095] Among them, "Field(name=="Date"||semanticType=="Time")" is the attribute information condition, and "FieldMapping.targetField="Transaction Date"" is the field mapping relationship conclusion. 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 triggering a rule is to pass the parsed fields as fact objects (Fact) to the rule engine Drools, and Drools generates field mapping according to the rule file.

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

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

[0099] In the implementation mode 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 each 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 field in B and the original field in C, the second similarity between the original field in B and the original field in D, and the second similarity between the original field in C and the original field in D, the target field mapping relationship corresponding to the template field is determined.

[0101] Determine the target field mapping relationship corresponding to the template field, including:

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

[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 relationship.

[0104] Step 2: Determine a second recommended field mapping relationship from two first recommended field mapping relationships whose second similarity is less than a preset similarity.

[0105] In the implementation manner of the present application, the first recommended field mapping relationship with the closest 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 the relationship configuration time of C. If the second similarity between the original field in B and the original field in C is less than the preset similarity, C is determined as the second recommended field mapping relationship.

[0107] Step three: determine the initial field mapping relationship and the two first recommended field mapping relationships whose second similarity is greater than or equal to a preset similarity as the second recommended field mapping relationship.

[0108] Example 3, in the scenario of Example 2, if the second similarity between the original field in B and the original field in C is greater than the preset similarity, it means 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 a user recommendation field mapping relationship, the present application not only considers the attribute information of the template field, but also considers the field meaning of the historical field mapping relationship of the template field, thereby 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 enter the target field mapping relationship corresponding to the template field.

[0111] In the implementation manner of the present application, all 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 higher the position in the mapping list; 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 relationship as the target field mapping relationship, or can execute the target field mapping relationship corresponding to the configuration template field.

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

[0113] In the implementation of the present application, if the target field mapping relationship is not in the second recommended field mapping relationship, it means that the target field mapping relationship is manually configured by the user when it is generated and is not recommended. Therefore, the target field mapping relationship needs to be written into the first mapping relationship database and the second mapping relationship database to improve the accuracy of the next recommendation.

[0114] Furthermore, the method further comprises: acquiring the latest field mapping relationship within a most recent preset period from the second mapping relationship database according to a preset period; and updating the rules in the rule base according to the latest field mapping relationship.

[0115] In an implementation manner of the present application, if the rule base stores rules to be updated corresponding to the template fields in the generated user and the latest field mapping relationship, the original fields in the field mapping relationship conclusion in the rules to be updated are updated to the original fields in the latest field mapping relationship; if the rule base does not store rules to be updated corresponding to the template fields in the generated user and the latest field mapping relationship, the latest rules are generated based on the attribute information of the template fields in the latest field mapping relationship and the latest field mapping relationship; and the latest rules are stored in the rule base.

[0116] Here, the rules to be updated refer to the rules corresponding to the generated user and template fields in the rule base. If the rule base does not store the rules to be updated corresponding to the generated user and the template fields in the latest field mapping relationship, the attribute information of the template fields 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 rules.

[0117] S103: When the execution status of all sharding tasks is completed, copy the data in all initial table files to the target table files 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, an encryption key is generated; the target table file is encrypted by the encryption key; the identifier of the encryption key and the storage path of the target table file are stored in the key database accordingly; or the identifier of the encryption key corresponding to the storage path of the target table file in the key database is determined as the identifier of the newly generated encryption key.

[0119] Specifically, if the target table file is not encrypted, an AES key is dynamically generated through the Vault API and encrypted using AES-256 (i.e., 256-bit key length). This ensures high-strength encryption protection. API example 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, which 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'). Use the JavaCryptography API to set the AES encryption tool, select the appropriate encryption mode, and initialize the key. Code example: Cipher cipher = Cipher.getInstance("AES"); cipher.init(Cipher.ENCRYPT_MODE, secretKey). File encryption block by block: read the file input stream and encrypt it block by block to prevent memory overload, suitable for processing large files. Use CipherOutputStream for encrypted output: FileInputStream fis = new FileInputStream (originalFile); CipherOutputStream cos = new CipherOutputStream (newFileOutputStream (encryptedFile), cipher); byte[] buffer = new byte

[1024] ; intbytesRead; while((bytesRead = fis.read(buffer))! = -1) {cos.write(buffer, 0, bytesRead);} cos.close(); fis.close(). The code to store the encryption key identifier and the storage path of the target table file 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 table file is longer than the preset duration, it is necessary to rotate the key regularly to ensure the security of the key. Use the Quartz scheduled task framework to regularly check the validity period of the key, and trigger the scheduled task when the key expires or reaches the rotation period. The scheduled task is started through Quartz and checks whether the key is expired at regular intervals (for example, monthly or quarterly). If it has expired, generate a new key: The code to generate a new AES key is: vaultClient.createSecret("encryption_keys / aes-256-new"), and returns the new key identifier. Update the key identifier in the key database. When the new key is generated, the key identifier associated with the file will need 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, ensuring that the file uses the latest key.

[0121] Here, when the target table file is re-encrypted by using the encryption key, the target table file may be first decrypted by using the old encryption key and then encrypted by using the new encryption key.

[0122] In addition, old keys should not be deleted immediately, but should be kept for a certain period of time to roll back or decrypt historical files. The key version management provided by Vault can help solve this problem. Ensure the security of the key rotation process to prevent key leakage. All sensitive operations (such as key updates) should be performed in a protected environment and access control should be used to limit unnecessary permissions.

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

[0124] Step 1: Determine the hash value of the target table file as the summary of the target table file.

[0125] In the implementation manner of the present application, the Java Cryptography API is used to calculate the SHA-256 hash value of the file as the file digest.

[0126] Step 2: Digitally sign the summary of the target table file using the RSA private key.

[0127] In the implementation manner of the present application, an 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 accordingly.

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

[0130] In the implementation manner of the present application, the RSA private key is dynamically loaded from Vault for signing operations; example: use VaultAPI to obtain the private key: `vaultClient.getSecret("keys / rsa-private")`.

[0131] Furthermore, the method further comprises:

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

[0133] In the implementation of the present application, OAuth2 or JWT is used to verify the identity of the file downloading user. The user permission record is queried to verify whether the user has the decryption permission for the target file. Example query: SELECT FROM user_permissionsWHERE user_id=? AND file_name=?.

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

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

[0136] In the implementation manner of the present application, the corresponding encryption key is obtained from the Vault according to the key identifier, the encrypted file is decrypted using the key, and provided to the user for download.

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

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

[0139] Step 1: Store the target table file to the preset storage address in the preset main storage bucket of Alibaba Cloud OSSSDK.

[0140] In the implementation mode of the present application, the target table file is uploaded to the designated primary storage bucket using Alibaba Cloud OSS SDK, and a storage address is designated 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 generated code of the preset storage address is as follows: bucket-name / 2024 / 11 / 26 / generated-file.xlsx.

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

[0143] Step 2: Generate a download link with a validity period for the target table file.

[0144] In the implementation mode of the present application, a signed URL is generated for the target table file, and the file downloading user can download the file through the URL, and the link will expire within a specified time.

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

[0146] In the implementation mode of the present application, in order to implement anti-hotlinking, the download link information needs to be stored in Redis, including the target table file identifier, the identifier of the generating user, the IP address of the generating user and the validity period, etc. This will help to verify during downloading.

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

[0148] The method also includes: when the writing of the target table file is completed, the identification of the generating user, the identification of the target table file, the generation time, and the generation status (success or failure) are stored in the log accordingly; after the target table file is uploaded to the main storage bucket, the identification, file size, upload time, and storage path of the target table file are stored in the log accordingly; after the file downloading user completes downloading the corresponding table file, the identification of the file downloading user, the IP address of the file downloading user, the download time, and the download link verification result are stored in the log accordingly.

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

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

[0151] In an implementation manner of the present application, if the number of file downloads and / or file generation times is greater than a preset number, the behavior is considered abnormal. These abnormal behaviors may be manifestations of malicious operations, abuse of services, or system failures. Once abnormal behavior is detected, the system will trigger an alarm. The alarm can be notified through instant messaging tools (such as WeChat for Enterprise, DingTalk, etc.) to ensure that relevant personnel can handle abnormal situations in a timely manner. Alarm notification: The alarm message will include information such as the user identifier that triggered the exception, the specific circumstances of the abnormal behavior (such as more than 50 downloads within 10 minutes, and the time when the abnormal behavior occurred. The system can push this information to a preset alarm platform (such as WeChat for Enterprise or DingTalk) through HTTP requests so that staff can conduct subsequent inspections and processing.

[0152] The embodiment of the present application provides a method for writing data to a table file, the method comprising: at a preset time interval, according to the task execution rate and current state of each thread, all sharding tasks with an execution state to be executed are assigned to threads in a thread pool; the sharding tasks are obtained by sharding the data in the original table file; when each thread executes each sharding task, it reads the data of the i-th page within the first row number range of the sharding task in the original table file; the data within the j-th second row number range in the i-th page is stored in a cache; each row of data within the j-th second row number range in the cache is sequentially written to the initial table file stored locally according to the target field mapping relationship; j=j+1; until all data of the i-th page is written to the initial table file, i=i+1; until all data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0; when the execution state of all sharding tasks is completed, the data in all initial table files are copied to the target table file stored locally. The present application can improve writing efficiency through multithreading, reduce memory usage during writing, and improve table file security.

[0153] The embodiment of the present application provides a method for writing data to a table file, the method comprising: assigning all sharding tasks in the execution state to be executed to threads in a thread pool according to the task execution rate and current state of each thread; reading the data of the i-th page within the first row number range of the sharding task in the original table file; storing the data within the j-th second row number range in the i-th page into a cache; writing each row of data within the j-th second row number range in the cache into a locally stored initial table file in sequence 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 initial table files into a locally stored target table file. The present application can improve writing efficiency through multithreading, reduce memory usage during writing, and improve table file security.

[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 the problem by the device in the embodiment of the present application is similar to the data writing method for the 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 repeated.

[0155] Reference Figure 3 FIG. 1 is a schematic diagram of a data writing device for a table file provided in an embodiment of the present application, wherein the data writing device for a table file comprises:

[0156] The allocation module 301 is used to allocate all the slice tasks in the execution state of being to be executed to the threads in the thread pool according to the task execution rate and current state of each thread at a preset time interval; the slice tasks are obtained by slicing the data in the original table file;

[0157] The writing module 302 is used for each thread to read the data of the i-th page within the first row number range of the sharding task in the original table file when executing each sharding task; store the data within the j-th second row number range of the i-th page in the cache; write each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence 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; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0;

[0158] The copy module 303 is used to copy the data in all the initial table files to the target table files stored locally when the execution status of all the sharding tasks is completed.

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

[0160] like Figure 4 As shown, an electronic device 400 provided in an embodiment of the present application includes: a processor 401, a memory 402 and a bus, wherein the memory 402 stores machine-readable instructions executable by the processor 401. When the electronic device is running, the processor 401 communicates with the memory 402 through the bus, and the processor 401 executes the machine-readable instructions to perform the steps of the data writing method of the above-mentioned table file.

[0161] Specifically, the memory 402 and the processor 401 can be general-purpose memories and processors, which are not specifically limited here. When the processor 401 runs the computer program stored in the memory 402, the data writing method of the table file can be executed.

[0162] Corresponding to the above-mentioned method for writing data to a table file, 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 executed by a processor, the steps of the above-mentioned method for writing data to a table file are executed.

[0163] Those skilled in the art can clearly understand that, for the convenience and simplicity of description, the specific working process of the system and device described above can refer to the corresponding process in the method embodiment, and will not be repeated in this application. In the several embodiments provided in this application, it should be understood that the disclosed system, device and method can be implemented in other ways. The device embodiments described above are merely schematic. For example, the division of the modules is only a logical function division. There may be other division methods in actual implementation. 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 mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, indirect coupling or communication connection of devices or modules, which can be electrical, mechanical or other forms.

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

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

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

[0167] The above are only specific implementations of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

Claims

1. A method for writing data into a table file, characterized in that: The method comprises: At a preset time interval, according to the task execution rate and current state of each thread, all shard tasks in the execution state of pending execution are assigned to threads in the thread pool; the shard tasks are obtained by sharding the data in the original table file; When each thread executes each sharding task, it reads the data of the i-th page within the first row number range of the sharding task in the original table file; stores the data within the j-th second row number range of the i-th page in the cache; writes each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence 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; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0; When the execution status of all sharding tasks is completed, the data in all initial table files are copied to the target table files stored locally.

2. The method for writing data into a table file according to claim 1, characterized in that: The method further comprises: After all data within the j-th second line number range in the cache are written into the initial table file, all data within the j-th second line number range in the cache are cleared.

3. The method for writing data into a table file according to claim 1, characterized in that: The method further comprises: After all data of the i-th page are written into the initial table file, the i-th page is determined as the latest execution page number of the thread for the slice task; After the interruption of the slice task is resumed, data is written to the initial table file starting from the next page of the latest execution page number of the slice task by the thread.

4. The method for writing data into a table file according to claim 1, characterized in that: The method further comprises: After all 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; The cell width of each column is adjusted according to the maximum content length of each column, so that the ratio of the content display length of each cell in each column to the total content length is greater than a preset ratio.

5. The method for writing data into a table file according to any one of claims 1 to 4, characterized in that: After copying the data in all the initial table files to the locally stored target table files, the method further includes: If the target table file is not encrypted or the encryption time length of the current encryption key is greater than the preset time length, an encryption key is generated; and the target table file is encrypted by the encryption key; The identifier of the encryption key and the storage path of the target table file are stored in a key database in correspondence; or the identifier of the encryption key corresponding to the storage path of the target table file in the key database is determined as the identifier of the newly generated encryption key.

6. The method for writing data into a table file according to claim 5, characterized in that: After copying the data in all the initial table files to the locally stored target table files, the method further includes: Determine the hash value of the target table file as a digest of the target table file; Digitally sign the summary of the target table file using an RSA private key; The digital signature and the name of the target table file are stored in a digital signature database correspondingly.

7. The method for writing data into a table file according to claim 5, characterized in that: The method further comprises: In response to a download request initiated by a file download user, authenticating the file download user; If the verification is successful, obtaining the key identifier of the table file corresponding to the download request from the key database; Decrypting the table file according to the key corresponding to the key identifier; The decrypted table file is sent to the file downloading user for downloading.

8. A data writing device for a table file, characterized in that: The device comprises: An allocation module is used to allocate all shard tasks in the execution state of being to be executed to threads in the thread pool according to the task execution rate and current state of each thread at a preset time interval; the shard tasks are obtained by sharding the data in the original table file; The writing module is used for each thread to read the data of the i-th page within the first row number range of the sharding task in the original table file when executing each sharding task; store the data within the j-th second row number range of the i-th page in the cache; write each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence 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; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0; The copy module is used to copy the data in all the initial table files to the target table files stored locally when the execution status of all the sharding tasks is completed.

9. An electronic device, characterized in that: include: A processor, a storage medium and a bus, wherein the storage medium stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor and the storage medium communicate via the bus, and the processor executes the machine-readable instructions to perform the steps of the method for writing data to a table file as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for writing data into a table file according to any one of claims 1 to 7 are executed.

Images