Method, device, electronic device and medium for generating table file

Abstract

The present application provides a method, device, electronic device and medium for generating a table file, the method comprising: obtaining attribute information corresponding to a template field in a table file template uploaded by a user, and a historical field mapping relationship corresponding to the template field; determining a target rule that the attribute information corresponding to the template field complies with from a pre-established rule library; determining the field mapping relationship conclusion in the target rule as an initial field mapping relationship corresponding to the template field; determining a target field mapping relationship corresponding to the template field based on a first similarity between the initial field mapping relationship and each historical field mapping relationship, and a second similarity between the preset field meanings of the original fields in each two historical field mapping relationships; generating a table file corresponding to the table file template based on the target field mapping relationship and the original table file. The present application can reduce the complexity of configuring field mapping relationships, improve generation efficiency, and reduce data error rate.

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 medium for generating 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 for users to manually enter data into the table files.

[0003] However, traditional methods are time-consuming and labor-intensive to adjust field mappings during writing, resulting in low generation efficiency and a high data error rate in table files. Summary of the Invention

[0004] In view of this, the purpose of this application is to provide a method, device, electronic device and medium for generating a table file, which can reduce the complexity of the configuration field mapping relationship, improve the generation efficiency and reduce the data error rate.

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

[0006] Obtaining attribute information corresponding to the template fields in the table file template uploaded by the user, and a historical field mapping relationship corresponding to the template fields; the attribute information is determined based on the purpose and field of the data stored in the template fields; the historical field mapping relationship refers to the mapping relationship between the template fields and the original fields in the original table file;

[0007] Determine a target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; 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 base includes an attribute information condition and a field mapping relationship conclusion;

[0008] Determining a target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships;

[0009] A table file corresponding to the table file template is generated according to the target field mapping relationship and the original table file.

[0010] In a possible implementation, the acquiring and generating of attribute information corresponding to template fields in the table file template uploaded by the user includes:

[0011] Extracting the names and sample data corresponding to the template fields in the table file template;

[0012] Matching 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 obtained by dividing it according to the purpose of the data in the original table file;

[0013] Inputting the name and field type corresponding to the template field into the field meaning analysis model corresponding to the field to obtain the field meaning corresponding to the template field;

[0014] The name, the field type, and the field meaning corresponding to the template field are determined as attribute information corresponding to the template field.

[0015] In a possible implementation, determining the target field mapping relationship corresponding to the template field based on a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between preset field meanings of the original fields in each two of the historical field mapping relationships includes:

[0016] Determine a preset number of historical field mapping relationships with the highest first similarity as first recommended field mapping relationships;

[0017] Determine a second recommended field mapping relationship from two first recommended field mapping relationships whose second similarity is less than a preset similarity;

[0018] Determining the initial field mapping relationship and two first recommended field mapping relationships whose second similarity is greater than or equal to the preset similarity as a second recommended field mapping relationship;

[0019] The second recommended field mapping relationship is sent to the generating user, so that the generating user determines the target field mapping relationship from the second recommended field mapping relationship or manually enters the target field mapping relationship corresponding to the template field.

[0020] In a possible implementation, sending the second recommended field mapping relationship to the generating user includes:

[0021] sorting all second recommended field mapping relationships according to the first similarity to obtain a mapping list; wherein the greater the first similarity corresponding to the second recommended field mapping relationship, the higher the position in the mapping list;

[0022] The mapping list is sent to the generating user.

[0023] In one possible implementation, the method further includes:

[0024] 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; the first mapping relationship database is a cache database; the second mapping relationship database is a long-term storage database.

[0025] In one possible implementation, the method further includes:

[0026] Obtaining the latest field mapping relationship within a most recent preset period from the second mapping relationship database according to a preset period;

[0027] The rules in the rule base are updated according to the latest field mapping relationship.

[0028] In a possible implementation, updating the rules in the rule base according to the latest field mapping relationship includes:

[0029] If the rule base stores a to-be-updated rule corresponding to the generating user and the template field in the latest field mapping relationship, then the original field in the field mapping relationship conclusion in the to-be-updated rule is updated to the original field in the latest field mapping relationship;

[0030] If the rule base does not store the rules to be updated corresponding to the generated user and the template field in the latest field mapping relationship, then the latest rules are generated based on the attribute information of the template field in the latest field mapping relationship and the latest field mapping relationship; and the latest rules are stored in the rule base.

[0031] In a second aspect, an embodiment of the present application further provides a device for generating a table file, the device comprising:

[0032] An acquisition module is used to acquire attribute information corresponding to the template fields in the table file template uploaded by the user, and a historical field mapping relationship corresponding to the template fields; the attribute information is determined based on the purpose and field of the data stored in the template fields; the historical field mapping relationship refers to the mapping relationship between the template fields and the original fields in the original table file;

[0033] A determination module is configured to determine, from a pre-established rule base, a target rule that the attribute information corresponding to the template field complies with; and 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 base includes an attribute information condition and a field mapping relationship conclusion;

[0034] The determining module is further configured to determine a target field mapping relationship corresponding to the template field based on a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships;

[0035] A generation module is used to generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

[0036] In a third aspect, an embodiment of the present application further 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. 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 generating a table file as described in any one of the first aspects.

[0037] 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 generating a table file as described in any one of the first aspects are executed.

[0038] The embodiment of the present application provides a method, device, electronic device and medium for generating a table file, the method comprising: obtaining attribute information corresponding to the template field in the table file template uploaded by the user, and a historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file; determining the target rule that the attribute information corresponding to the template field complies with from a pre-established rule library; 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 library includes an attribute information condition and a field mapping relationship conclusion; determining the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; generating a table file corresponding to the table file template according to the target field mapping relationship and the original table file. The present application can reduce the complexity of configuring field mapping relationships, improve generation efficiency and reduce data error rate. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following is a brief introduction to the drawings required for use in the embodiments. 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 relevant drawings can be obtained based on these drawings without creative work.

[0040] Figure 1 A flowchart of a method for generating a table file provided in an embodiment of the present application is shown;

[0041] Figure 2 A schematic diagram of a process for generating a table file corresponding to a table file template provided in an embodiment of the present application is shown;

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

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

[0044] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions 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 illustration 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 to scale. The flowcharts used in this application illustrate the operations implemented according to some embodiments of the present application. It should be understood that the operations of the flowcharts can be implemented out of sequence, and steps without logical context can be reversed or implemented simultaneously. In addition, those skilled in the art, under the guidance of the contents of this application, can add one or more other operations to the flowchart, or remove one or more operations from the flowchart.

[0045] In addition, the described embodiments are only a part of the embodiments of the present application, rather than all of the embodiments. The components of the embodiments of the present application generally 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 claimed application, but merely represents 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 are within the scope of protection of the present application.

[0046] To enable those skilled in the art to utilize the contents of this application, the following embodiments are provided in conjunction with a specific application scenario, the "field of data processing technology." Those skilled in the art will appreciate that 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 primarily described in the "field of data processing technology," it should be understood that this is merely an exemplary embodiment.

[0047] 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.

[0048] The following is a detailed description of a method for generating a table file provided in an embodiment of the present application.

[0049] Reference Figure 1 FIG. 1 is a flow chart of a method for generating a table file provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:

[0050] S101. 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.

[0051] In an 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 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 the 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 the historical field mapping relationship.

[0052] The file types of the table file template can be Excel (.xls, .xlsx) and CSV.

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

[0054] Here, the first mapping database uses hash storage, with user_id (generated user ID) as the key and the template field and mapped_value original field as the value. Example: HSET user:1 "Amount" "Invoice Total" . The second mapping database is structured as follows: 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').

[0055] Specifically, the attribute information corresponding to the template fields in the table file template uploaded by the user is obtained, including:

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

[0057] In this application implementation, Apache POI is used to parse the table file template and extract column headers (i.e., template fields) and sample data. Apache POI stands for "Apache Portable Document Format (PDF) and Office Open XML (OOXML) Project," an open source project that provides a Java API for processing files in Microsoft Office formats, including but not limited to Excel, Word, and PowerPoint.

[0058] For 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|", the names of the extracted template fields are ["Date","Amount","Name"]. The sample data corresponding to the template fields is ["2023-11-19","100.50","John Doe"].

[0059] 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.

[0060] In this embodiment, the preset field types can include date, amount, text, and other types, categorized according to the purpose of the data in the original spreadsheet file. The preset regular expression for date can be ˋ\d{4}-\d{2}-\d{2}ˋ. The preset regular expression for amount can be ˋ^\d+(\.\d{1,2})?$ˋ. The preset field type corresponding to the regular expression that successfully matches is determined as the field type corresponding to the template field.

[0061] 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.

[0062] 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 businesses. 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.

[0063] Example key code:

[0064] -NLP semantic analysis: SpaCy model extracts the meaning of fields.

[0065] Model input: Field name: ["Date","Amount","Name"], Field type: ["Date","Amount","Name"].

[0066] Through SpaCy semantic analysis and context inference: "Date" → time dimension (for example, transaction date, creation date); "Amount" → financial amount (such as payment amount, total amount); "Name" → user name (for example, customer name, employee name).

[0067] Model output: ["Date"->transaction date, "Amount"->total financial amount, "Name"->customer name].

[0068] Natural Language Processing (NLP) is a broad field that encompasses multiple disciplines, including 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.

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

[0070] S102. Determine the target rule that the attribute information corresponding to the template field complies with from a 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 attribute information conditions and field mapping relationship conclusions.

[0071] In the embodiment 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 that has a mapping relationship with the template field.

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

[0073] Rule example:

[0074] rule "Match Date Field"

[0075] when

[0076] Field(name=="Date"||semanticType=="Time")

[0077] then

[0078] FieldMapping.targetField="Transaction Date";

[0079] end

[0080] Here, "Field(name == "Date" || semanticType == "Time")" is the attribute information condition, and "FieldMapping.targetField = "Transaction Date"" is the field mapping conclusion. This example indicates that when the template field name is Date and the field type semanticType is Time, the template field targetField is mapped to the original field Transaction Date.

[0081] 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 mapping according to the rule file.

[0082] Example: - Input template fields: ["Date", "Amount", "Name"], output mapping relationships: Date->Transaction Date, Amount->Transaction Amount, Name->Customer Name.

[0083] S103: Determine a target field mapping relationship corresponding to the template field based on 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 each two historical field mapping relationships.

[0084] In the embodiment of the present application, the cosine similarity between the initial field mapping relationship and each historical field mapping relationship is calculated to obtain a 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 a second similarity between the preset field meanings of the original fields in the two historical field mapping relationships.

[0085] Example 1: The initial field mapping relationship is A, and the historical field mapping relationships include B, C, and D. Then, based on 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.

[0086] Specifically, determining the mapping relationship between the template fields and the target fields includes:

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

[0088] 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.

[0089] 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.

[0090] 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.

[0091] 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.

[0092] Step 3: 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.

[0093] 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.

[0094] Here, when generating user recommendation field mapping relationships, this 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.

[0095] 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.

[0096] In the embodiments of the present application, all second recommended field mappings are sorted by the first similarity to generate a mapping list; the greater the first similarity corresponding to the second recommended field mapping, the higher its position in the mapping list; and the mapping list is sent to the generating user. The generating user can select a field mapping from the second recommended field mappings as the target field mapping, or execute the target field mapping corresponding to the configuration template field.

[0097] 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.

[0098] In this embodiment, if the target field mapping is not in the second recommended field mapping, it means that the target field mapping was manually configured by the user and is not recommended. Therefore, the target field mapping needs to be written to the first mapping database and the second mapping database to improve the accuracy of the next recommendation.

[0099] 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.

[0100] 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.

[0101] Here, the "to-be-updated rule" refers to the rule in the rule base that corresponds to the generated user and template fields. If the rule base does not store a to-be-updated rule 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 determination is used as the field mapping relationship conclusion to obtain the latest rule.

[0102] S104: Generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

[0103] Reference Figure 2 As shown, it is a flow chart of generating a table file corresponding to a table file template provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:

[0104] S201. Allocate all shard tasks in the pending execution state to threads in the thread pool according to the preset time interval and the task execution rate and current state of each thread; the shard tasks are obtained by sharding the data in the original table file.

[0105] 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 row number range corresponding to each slice task.

[0106] In the implementation manner of the present application, the first preset number of shard rows is achieved through repeated performance testing and adjustment. It can start from a relatively standard value (such as 5,000 rows or 10,000 rows) and gradually adjust according to performance. If the shard is too large, memory consumption will increase, the processing speed may decrease, and the 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 the parallel processing efficiency, but also ensures the overall system performance.

[0107] For example, if the total number of rows in the original table file is 10,000, and the file is sharded according to the first preset number of shard rows of 5,000, the first row number corresponding to the first shard task ranges from 1 to 5,000 rows; the first row number corresponding to the second shard task ranges from 5,001 to 10,000 rows.

[0108] Here, the pseudo 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.

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

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

[0111] 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 all shard tasks in the shard task database that are in the execution state to be executed: SELECT * FROM data_shard WHERE status = 'PENDING'; according to the task execution rate, current state, and preset load balancing strategy of each thread, all shard tasks in the execution state to be executed are encapsulated as Runnable and assigned 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 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.

[0112] Load balancing strategies include: 1. Round-robin: shards are assigned to each thread pool or thread in a round-robin fashion, suitable for scenarios where the workload of shards is relatively balanced. 2. Weighted round-robin: Thread pools with larger weights are assigned more shards, while thread pools with smaller weights process fewer shards. 3. Least connections first: If the workloads of thread pools vary significantly, the "least connections first" strategy can be used to assign new shards to the thread pool with the least workload.

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

[0114] For example, larger shards can be set with lower priorities, while smaller shards can be set with higher priorities, so that smaller tasks can be processed first as much as possible, avoiding some threads in the thread pool being idle waiting for the large shards to complete.

[0115] S202. 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 to 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 to 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.

[0116] In an embodiment of the present application, data within a first range of rows of a sharding task in an original table file is paginated according to a second preset number of sharding rows (e.g., 500 rows); and data on each page is divided according to a third preset number of sharding rows (e.g., 100 rows) to obtain at least one second range of rows. The third preset number of sharding rows is smaller than the second preset number of sharding rows.

[0117] Here, data on page i within the first row of the shard task in the original table file is read in pages, ensuring data writing efficiency. By caching each page of data multiple times before writing, memory usage is reduced.

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

[0119] 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.

[0120] Furthermore, the method also includes: after all data of page i is written to the initial table file, determining page i as the latest execution page number of the thread for the shard task; after the shard task is interrupted and restored, writing data to the initial table file starting from the next page of the latest execution page number of the thread for the shard task.

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

[0122] Furthermore, the method also includes: after all data of page i 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.

[0123] In the embodiment 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 ratio of the content display length of each cell in each column to the total content length is greater than a preset ratio.

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

[0125] Furthermore, 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 being executed.

[0126] Furthermore, after each thread writes a preset number of rows of data into the initial table file, it updates the execution progress of the sharding task (represented by the number of rows). Based on the execution progress, the task execution rate of the thread can be calculated.

[0127] S203: 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.

[0128] Furthermore, if the target table file is not encrypted or the encryption duration of the current encryption key is greater than a preset duration, an encryption key is generated; the target table file is encrypted using 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.

[0129] 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., a 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 for storing 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').

[0130] Specifically, if the encryption duration of the current encryption key of the target table file is longer than the preset duration, in order to ensure the security of the key, it is necessary to rotate the key regularly. 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 return 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.

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

[0132] Furthermore, old keys should not be deleted immediately; they should be retained for at least a certain period of time to allow for rollbacks or decryption of historical files. Vault's key versioning can help address this issue. Ensure the security of key rotation to prevent key leaks. All sensitive operations (such as key updates) should be performed in a protected environment, with access controls to restrict unnecessary permissions.

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

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

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

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

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

[0138] Step 3: Store the digital signature and the name of the target table file in the digital signature database.

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

[0140] In the implementation of this application, the RSA private key is dynamically loaded from Vault for signing operations; for example, the private key is obtained using the Vault API: `vaultClient.getSecret("keys / rsa-private")`.

[0141] Key code: MessageDigest digest =MessageDigest.getInstance("SHA-256"); FileInputStream fis=new FileInputStream(encryptedFile); byte[]buffer=new byte

[1024] ; int bytesRead; while((bytesRead=fis.read(buffer))!=-1){digest.update(buffer,0,bytesRead);}byte[]fileHash=digest.digest().

[0142] Furthermore, the method further comprises:

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

[0144] In this implementation, OAuth2 or JWT is used to verify the identity of the file downloader. The user's permission record is queried to verify whether the user has decryption permission for the target file. Example query: SELECT FROM user_permissions WHERE user_id = ? AND file_name = ?

[0145] 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.

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

[0147] In the embodiment 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 is provided to the user for download.

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

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

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

[0151] In the implementation of this application, the target table file is uploaded to the designated primary storage bucket using 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 based on the current date or other rules.

[0152] The generated code for the preset storage address is as follows: bucket-name / 2024 / 11 / 26 / generated-file.xlsx.

[0153] In addition, the target table files are copied from the primary bucket to the backup bucket to complete the backup. The backup bucket is set to a different geographical region from the primary bucket to improve file security.

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

[0155] In the implementation manner 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.

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

[0157] In the embodiment 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 generating user's IP address and validity period, etc. This will help to verify during downloading.

[0158] Specifically, obtaining the key identifier of the table file corresponding to the download request from the key database includes:

[0159] Step 1: Verify the download link of the file downloading user based on the download link information of the table file corresponding to the download request in the link database. Verify whether the file download request is legitimate, including but not limited to verifying the existence of file information, file validity period, link, user ID and IP, etc.

[0160] 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.

[0161] The method also includes: after the target table file is written, 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.

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

[0163] The method also includes: extracting all log data in 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 through a time window (10 minutes) to count the number of times the user downloaded files and / or generated files in each time window; and triggering an alarm if the number of times the user downloaded files and / or generated files is greater than the preset number.

[0164] In the implementation manner of the present application, if the number of times a file is downloaded and / or the number of times a file is generated 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 Business, DingTalk, etc.) to ensure that relevant personnel can deal with abnormal situations in a timely manner. Alarm notification: The alarm message will include information such as the user ID that triggered the abnormality, 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 Business or DingTalk) through HTTP requests so that staff can conduct subsequent inspections and processing.

[0165] The embodiment of the present application provides a method for generating a table file, the method comprising: obtaining attribute information corresponding to the template field in the table file template uploaded by the user, and a historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file; determining the target rule that the attribute information corresponding to the template field complies with from a pre-established rule library; 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 library includes an attribute information condition and a field mapping relationship conclusion; determining the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; generating a table file corresponding to the table file template according to the target field mapping relationship and the original table file. The present application can reduce the complexity of configuring field mapping relationships, improve generation efficiency, and reduce data error rate.

[0166] Based on the same inventive concept, an embodiment of the present application also provides a device for generating a table file corresponding to the method for generating a table file. Since the principle of solving the problem by the device in the embodiment of the present application is similar to the method for generating 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 repeated.

[0167] Reference Figure 3 FIG. 1 is a schematic diagram of a device for generating a table file provided in an embodiment of the present application, wherein the device for generating a table file includes:

[0168] Acquisition module 301 is used to acquire attribute information corresponding to the template fields in the table file template uploaded by the user, and the historical field mapping relationship corresponding to the template fields; the attribute information is determined based on the purpose and field of the data stored in the template fields; the historical field mapping relationship refers to the mapping relationship between the template fields and the original fields in the original table file;

[0169] Determination module 302 is used to determine the target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; 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 base includes an attribute information condition and a field mapping relationship conclusion;

[0170] The determining module 302 is further configured to determine a target field mapping relationship corresponding to the template field based on a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships;

[0171] The generating module 303 is configured to generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

[0172] The table file generation device provided in this application can reduce the complexity of the configuration field mapping relationship, improve the generation efficiency, and reduce the data error rate.

[0173] 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 method for generating a table file as described above.

[0174] 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 method for generating the table file can be executed.

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

[0176] Those skilled in the art can clearly understand that, for the convenience and brevity 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.

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

[0178] 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.

[0179] 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. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or 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 aforementioned storage medium includes various media that can store program codes, such as a USB flash drive, a mobile hard disk, a ROM, a RAM, a magnetic disk, or an optical disk.

[0180] The above are only specific embodiments of the present application, but the scope of protection of this application is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in this application should be included in the scope of protection of this application. Therefore, the scope of protection of this application should be based on the scope of protection of the claims.

Claims

1. A method for generating a table file, characterized in that: The method comprises: Obtaining 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; the attribute information is determined based on the purpose and field of the data stored in the template fields; the historical field mapping relationship refers to the mapping relationship between the template fields and the original fields in the original table file; Determine a target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; 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 base includes an attribute information condition and a field mapping relationship conclusion; Determining a target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; Generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file; Among them, the target field mapping relationship corresponding to the template field is determined based on the first similarity between the initial field mapping relationship and each of the historical field mapping relationships and the second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships, including: determining a preset number of historical field mapping relationships with the highest first similarity as the first recommended field mapping relationship; if the second similarity of the two first recommended field mapping relationships is less than the preset similarity, determining the second recommended field mapping relationship according to the relationship configuration time of the historical field mapping relationship; if the second similarity of the two first recommended field mapping relationships is greater than or equal to the preset similarity, determining the initial field mapping relationship and the two first recommended field mapping relationships as the second recommended field mapping relationship; sending the second recommended field mapping relationship to the generating user, so that the generating user determines the target field mapping relationship from the second recommended field mapping relationship or manually enters the target field mapping relationship corresponding to the template field.

2. The method for generating a table file according to claim 1, wherein: The step of obtaining and generating attribute information corresponding to template fields in a table file template uploaded by a user includes: Extracting the names and sample data corresponding to the template fields in the table file template; Matching 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 obtained by dividing it according to the purpose of the data in the original table file; Inputting the name and field type corresponding to the template field into the field meaning analysis model corresponding to the field to obtain the field meaning corresponding to the template field; The name, the field type, and the field meaning corresponding to the template field are determined as attribute information corresponding to the template field.

3. The method for generating a table file according to claim 1, wherein: The sending the second recommended field mapping relationship to the generating user includes: sorting all second recommended field mapping relationships according to the first similarity to obtain a mapping list; wherein the greater the first similarity corresponding to the second recommended field mapping relationship, the higher the position in the mapping list; The mapping list is sent to the generating user.

4. The method for generating a table file according to claim 1, wherein: The method further comprises: 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; the first mapping relationship database is a cache database; the second mapping relationship database is a long-term storage database.

5. The method for generating a table file according to claim 4, wherein: The method further comprises: Obtaining the latest field mapping relationship within a most recent preset period from the second mapping relationship database according to a preset period; The rules in the rule base are updated according to the latest field mapping relationship.

6. The method for generating a table file according to claim 5, wherein: The updating of the rules in the rule base according to the latest field mapping relationship includes: If the rule base stores a to-be-updated rule corresponding to the generating user and the template field in the latest field mapping relationship, then the original field in the field mapping relationship conclusion in the to-be-updated rule is updated to the original field in the latest field mapping relationship; If the rule base does not store the rules to be updated corresponding to the generated user and the template field in the latest field mapping relationship, then the latest rules are generated based on the attribute information of the template field in the latest field mapping relationship and the latest field mapping relationship; and the latest rules are stored in the rule base.

7. A device for generating a table file, characterized in that: The device comprises: An acquisition module is used to acquire 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; the attribute information is determined based on the purpose and field of the data stored in the template fields; the historical field mapping relationship refers to the mapping relationship between the template fields and the original fields in the original table file; A determination module is configured to determine, from a pre-established rule base, a target rule that the attribute information corresponding to the template field complies with; and 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 base includes an attribute information condition and a field mapping relationship conclusion; The determining module is further configured to determine a target field mapping relationship corresponding to the template field based on a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; A generating module, configured to generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file; Among them, the determination module is specifically used to determine a preset number of historical field mapping relationships with the highest first similarity as the first recommended field mapping relationship; if the second similarity of the two first recommended field mapping relationships is less than the preset similarity, the second recommended field mapping relationship is determined according to the relationship configuration time of the historical field mapping relationship; if the second similarity of the two first recommended field mapping relationships is greater than or equal to the preset similarity, the initial field mapping relationship and the two first recommended field mapping relationships are determined as the second recommended field mapping relationship; the second recommended field mapping relationship is sent to the generating user, so that the generating user determines the target field mapping relationship from the second recommended field mapping relationship or manually enters the target field mapping relationship corresponding to the template field.

8. 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. 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 generating a table file as described in any one of claims 1 to 6.

9. 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 generating a table file according to any one of claims 1 to 6 are executed.

Images