A method, system, apparatus, and storage medium for data information document processing.

Abstract

This invention relates to a method, system, apparatus, and storage medium for data file processing. By using shell commands in the Linux system and encapsulating general functions of Linux commands and MySQL database types, it can periodically perform file processing of specified data. File processing can be flexibly configured according to the data information to meet the file processing needs of data with different scales, realizing the file processing of specified data of any scale from the database to the server. Compared with the prior art, this invention has the advantages of being applicable to data of any scale, fast processing speed, high reliability, and good portability.

Description

Technical Field

[0001] This invention relates to the field of data processing, and in particular to a method, system, apparatus, and storage medium for data information file processing. Background Technology

[0002] In existing technologies, the following solutions are generally used to handle the file generation function for large amounts of data:

[0003] The first type involves splitting documents according to fixed administrative regions, building upon a general document generation function. The general document generation function is implemented using a single thread, and the document generation time increases linearly with the corresponding data size. While splitting documents by fixed administrative regions mitigates the risk to some extent, the data distribution within each region is uneven, with some regions having significantly more data than others, leading to slow response times.

[0004] The second approach, building upon the first, involves splitting the large data set into administrative regions. While this approach significantly mitigates the risk, it doesn't fundamentally solve the problem. Because the administrative region divisions of a single large file may differ from those of other files, other systems using that file may configure inconsistent administrative regions, potentially causing confusion.

[0005] The third category: Based on general file generation functionality, data is split according to randomly assigned regions. This type of solution has high upfront costs, generally requiring data sharding of the data source beforehand. Sharding methods include, but are not limited to, hash modulo and consistent hashing algorithms for assigning numbers to sharding fields. Because this type of solution requires significant upfront investment in algorithm selection, and the command writing for some algorithms is quite complex, it is only suitable for large-scale data information and is often used in data analysis products.

[0006] In summary, existing technologies suffer from problems such as data partitioning methods being incompatible with different data sizes, rigidity in data partitioning methods, and time-consuming processes. Summary of the Invention

[0007] The purpose of this invention is to overcome the defects of the prior art by providing a data information file processing method, system, device and storage medium.

[0008] The objective of this invention can be achieved through the following technical solutions:

[0009] A method for processing data information into files includes the following steps:

[0010] Use the Shell command set to receive data that needs to be filened and its related parameters;

[0011] The data information that needs to be documented is broken down into documented configuration information according to the preset information statistics dimensions and stored in the information dimension configuration table; the information dimension configuration table is designed based on the preset information statistics dimensions.

[0012] Based on the documented configuration information, the data size of the data information is estimated and compared with a preset data size threshold;

[0013] If the estimated data size does not exceed the data size threshold, the Shell parent process is directly invoked to generate the final file;

[0014] If the estimated data size exceeds the data size threshold, the data information is broken down into multiple dataset fragments. Then, through the Shell parent process and with the help of pipe files, multiple Shell child processes are synchronously launched. Each Shell child process writes each dataset fragment to multiple temporary files, and finally, the multiple temporary files are merged into the final file.

[0015] Furthermore, the data information decomposition scheme includes decomposition according to the range of the number of records and decomposition according to the range of a single field index;

[0016] If the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution is configured, then the data is split according to the index field.

[0017] If the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution cannot be obtained, the data will be split according to the range of the number of records.

[0018] Furthermore, the Shell command set includes a parent Shell command set and a child Shell command set;

[0019] The parent shell command set includes various execution commands and temporary parameters for data information files; the child shell command set includes execution commands for writing dataset sharding DQL statements to temporary files.

[0020] Furthermore, the parent shell command set includes five types of DQL statements;

[0021] The first type of DQL statement is a preset query statement, which is a command that must be executed in the parent shell command set. It is used to query the file-based configuration information of data information, and the query results will be stored in the temporary parameters of the parent shell command set.

[0022] The second type of DQL statement is a query statement obtained by concatenating the query results of the first type of DQL statement. It is a command that must be executed in the parent shell command set. It is used to query the amount of data corresponding to the data information file, thereby estimating the data size. The query results will be stored in the temporary parameters of the parent shell command set.

[0023] The third type of DQL statement is obtained by concatenating the query results of the first and second types of DQL statements from the parent shell command set. If the estimated data size does not exceed the data size threshold, this statement will be executed and the query results will be written directly to the final file.

[0024] The fourth type of DQL statement is obtained by concatenating data limiting conditions on the basis of the third type of DQL statement by the parent shell command set. When the estimated data size exceeds the data size threshold, the dataset is split according to the single field index in the first type of DQL statement, either by the range of the number of records or by the range of the single field index. The fourth type of DQL statement is written to the pipeline file and executed by the child shell command set, and the query results are written to the temporary parameters.

[0025] The fifth type of DQL statement is a supplement to the fourth type of DQL statement, which is based on the first type of DQL statement to confirm the existence of a single-field index. It adds a condition to the third type of DQL statement to specify the number of rows and query the value of the indexed field.

[0026] Furthermore, both the parent and child shell command sets include temporary parameters. After the query results of the first type of DQL statement and the second type of DQL statement are stored in the temporary parameters, logical analysis is required. For temporary parameters that do not meet the preset conditions, detailed exception log information must be recorded and an exception exit mechanism must be executed.

[0027] Furthermore, the relevant parameters include file ID, data date, and region range, where file ID is a fixed identifier set for data information that needs to be fileified, serving as a general identifier for fileification configuration.

[0028] Furthermore, the information dimension configuration table is located in the MySQL category database, and uses file id as one of the primary keys or composite primary keys.

[0029] A data information file processing system, based on the data information file processing method described above, includes a database access module, a logical decision module, a file writing module, a sub-shell calling module, a sub-process scanning module, a file merging module, and a log and exception handling module;

[0030] The database access module calls the first and second types of DQL statements, writes the query results to temporary parameters, and then concatenates the third, fourth, and fifth types of DQL statements based on the temporary parameters for file writing in different scenarios; any query failure or abnormal result will trigger the logging and exception handling module functions.

[0031] The logical decision module selects a solution by determining whether a single-field index exists in the first type of DQL statement and receiving the estimation results of the data size from the second type of DQL statement.

[0032] The file writing module writes the query results of DQL statements to a temporary file or the final file by calling the Shell parent process. Any writing error will trigger the logging and exception handling module functions.

[0033] The sub-shell calling module is triggered by the shell parent process after generating multiple pipe files in the file writing module. This module reads the pipe file name parameters and the contents of the pipe file, triggering the shell child process's file writing module. At the same time, this module is monitored throughout by the child process scanning module, which monitors whether it is running and its running status. Any function error will trigger the parent process's logging and exception handling module functions.

[0034] The child process scanning module queries the execution status of the sub-layer Shell call module and the file writing module of the Shell child process based on the pipe file name; it checks whether each child process terminates normally based on the return results of the child process log and the exception handling module; if all terminate normally, the file merging module of the Shell parent process is triggered; if there are still running child processes, the process enters a short sleep and then checks again; if any child process exception occurs, the log and exception handling modules of the parent process are triggered.

[0035] The file merging module, after receiving a normal termination signal from the child process scanning module, merges multiple temporary files generated by the Shell child process's file writing module into a final file, and deletes the temporary files and pipe files; if an exception occurs during the merging process, it triggers the parent process's "logging and exception handling module";

[0036] The logging and exception handling module records a normal log after each step is completed if all steps are executed normally. If an error occurs during the execution of a certain module step, it records both normal and exception logs, exits abnormally, and throws an exception for the monitoring program to capture the error information.

[0037] A data information file processing apparatus includes a memory and a processor. The memory stores a computer program, and the processor can execute the data information file processing method described above by calling program instructions.

[0038] A computer-readable storage medium includes a computer program that can be executed by a processor using the data information file processing method described above.

[0039] Compared with the prior art, the present invention has the following beneficial effects:

[0040] 1. Applicable to data of any size

[0041] Before performing the data information filing step, this invention provides three file generation processing schemes by estimating the data size and comparing it with a preset data size threshold: (1) a single-threaded scheme suitable for small to medium data volumes, such as when the estimated data size does not exceed the data size threshold; (2) a multi-threaded scheme suitable for large data volumes after data governance, such as when the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution is configured; and (3) a secondary multi-threaded scheme suitable for large data volumes without data governance, such as when the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution cannot be obtained. These three schemes cover most scenarios and can form a general file generation method. For products with low quantity pressure, scheme (1) can solve most file generation requirements.

[0042] 2. Fast processing speed

[0043] This invention directly uses the Shell parent process to write the dataset to the final file for data information that does not exceed the data size threshold. When processing data information that exceeds the data size threshold, it splits the data into multiple dataset fragments according to the data size threshold, the range of the number of records, or the range of a single field index. It can also automatically calculate the number of fragments, which is calculated by dividing the data size by the data size threshold and rounding up, thus achieving a faster processing speed.

[0044] 3. High reliability

[0045] This invention targets large-scale data information by automatically breaking it down into multiple dataset fragments. Through a shell parent process and with the help of pipe files, multiple shell child processes are synchronously launched. Each shell child process writes each dataset fragment to multiple temporary files. Finally, the multiple temporary files are automatically merged into a final file, which is highly reliable and less prone to causing confusion.

[0046] 4. Facilitates transplantation

[0047] This invention uses Linux's built-in shell commands to encapsulate common functions of Linux commands and MySQL databases without using other non-open-source tools, thereby achieving related functions. Therefore, using MySQL database products can achieve convenient portability and reduce development costs.

[0048] 5. Flexible configuration

[0049] The invention references common data documentation examples in the industry, outlines general information statistics dimensions, and designs corresponding MySQL database configuration tables based on these dimensions. The invention provides comprehensive coverage of all information statistics dimensions, incorporating them all into the database configuration tables for flexible allocation. Attached Figure Description

[0050] Figure 1 This is a flowchart illustrating the implementation of the present invention. Detailed Implementation

[0051] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. These embodiments are based on the technical solution of the present invention and provide detailed implementation methods and specific operating procedures. However, the scope of protection of the present invention is not limited to the following embodiments.

[0052] This invention provides a data information file processing method, system, device, and storage medium, which can be applied to the financial field or other fields to realize the periodic execution of file processing of specified data information, and flexibly configure the file processing according to the data information, and can meet the data information file processing needs of different data scales.

[0053] This invention utilizes the built-in shell commands of the Linux system, encapsulating Linux commands and general functions of the MySQL database without using other non-open-source tools. This enables the file-based processing of specified data of any size from the database to the server. Simultaneously, it employs a flexible, automatically adjustable multi-threaded file generation method to smooth out the overall processing time. It also fully considers different data partitioning mechanisms, choosing a relatively simple and universal approach: for large-scale data, it utilizes the Limit clause of the MySQL database to implement two temporary data sharding methods, then uses a multi-threaded parallel processing method to complete the file generation, and finally merges the files to obtain the final file.

[0054] Terminology Explanation

[0055] 1) Shell

[0056] Commonly known as a shell, it is a command interpreter that provides an operating interface for users of Linux and Unix systems. Shell is both a command language and a programming language, used to receive user commands and then call the corresponding applications.

[0057] 2) MySQL database style

[0058] It mainly includes basic MySQL relational database, TDSQL distributed database, and GBase relational database.

[0059] 3) DQL statements

[0060] The query statement in relational and distributed databases is used for querying, and the representative command is select.

[0061] 4) Parent and child processes

[0062] In a Linux system environment, a single shell command can encapsulate multiple Linux commands, forming a command set. Shell commands, as Linux commands themselves, can also be nested within a command set; the outer shell command is called the parent shell, and the inner shell command is called the child shell. When the parent shell is invoked by a Linux server, a corresponding Linux process is immediately created, called the shell parent process. When the shell parent process executes a command from a child shell, a corresponding Linux process is immediately created, called the shell child process. The parent process ID of the shell child process inherits from the shell parent process's process ID; the parent process ID of the shell parent process is generally the login process ID of the caller, thus forming a parent-child process relationship.

[0063] 5) Limit Clause

[0064] This is a feature specific to MySQL databases and is typically added to the result set of a database query. Adding a clause format like `Limit m, n` can narrow the range of the result set query, displaying records from the (m+1)th to the (m+n)thth record according to a certain sorting rule.

[0065] 6) Data size threshold

[0066] Data size refers to the estimated size of the generated file before the data is documented. The documenting process typically uses a single thread. However, when the statistical dimensions and amount of data exceed a certain order of magnitude, the single thread's processing performance becomes insufficient, leading to a prolonged documenting time. Setting a data size threshold helps the program make logical decisions about whether to adopt a multi-threaded parallel approach.

[0067] 7) Execute synchronously in the background

[0068] This generally refers to multiple shell commands being simultaneously launched in the background of a Linux server. This scenario typically occurs when using the crontab command or other external scheduling tools to launch shell commands at fixed intervals, or when a parent process launches multiple child processes simultaneously. This invention specifically refers to the latter, where the parent shell command contains a for loop, and the for loop contains a nohup statement to launch a child shell command in the background. After the child shell is launched and spawns a child process, it does not prevent the parent process from launching the child shell again to create new child processes.

[0069] like Figure 1 As shown, a data information file processing method includes the following steps:

[0070] S1. Use the Shell command set to periodically receive externally input data that needs to be fileted, along with its related parameters. These parameters include the file ID, data date, and region range.

[0071] The file ID is a fixed identifier set for the data information that needs to be filed. It is a fixed value and serves as a general identifier for the file configuration. It is also the most important external input parameter of the parent shell command. The data date and region range are the second input parameter of the parent shell command. They are optional, non-essential, range reductions on the data source for the data information that needs to be filed, in order to reduce the performance pressure of filening data information in a single operation.

[0072] S2. The data information that needs to be documented is broken down into documented configuration information according to the preset information statistics dimensions and stored in the information dimension configuration table. Usually, the file ID is used as one of the primary keys or composite primary keys of the information dimension configuration table.

[0073] S3. After obtaining the file configuration information corresponding to the data information of the file ID, based on the file configuration information, estimate the data size of the data information according to the data volume of the dataset under the optional constraints of the file ID, and compare it with the data size threshold preset by the technical personnel.

[0074] S4. If the estimated data size does not exceed the data size threshold, the Shell parent process is directly called to generate the final file.

[0075] S5. If the estimated data size exceeds the data size threshold, the data information is split into multiple dataset fragments according to the data size threshold, the range of the number of records, or the range of a single field index.

[0076] If the estimated data size exceeds the data size threshold, and a single-field index with uniform data distribution is configured, then the dataset will be split according to the index field. That is, the dataset will be divided into multiple shards of the same size based on the uniform distribution of the index field. The number of shards is automatically calculated based on the data size threshold and can be automatically adjusted.

[0077] If the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution cannot be obtained, the data is split according to the range of record counts. Through the parent shell process, multiple shell child processes are synchronously launched in the background using pipe files. Each shell child process writes each dataset fragment to multiple temporary files, and finally, these temporary files are merged into the final file.

[0078] In this embodiment, technical personnel, referring to common data information filing examples in the industry, sorted out common information statistical dimensions and designed corresponding MySQL category database information dimension configuration tables based on these common information statistical dimensions. Data information is broken down according to these common information statistical dimensions and stored in multiple information dimension configuration tables of the MySQL category database. When used, the filed configuration information in these information dimension configuration tables is concatenated together according to a preset logic.

[0079] File ID and region range serve as primary keys or composite primary keys for configuration tables across multiple statistical dimensions. When multiple levels of configuration information exist within the same statistical dimension, the hierarchical relationships, order, and association methods of these multiple levels must be stored together in the corresponding database information dimension configuration table.

[0080] Generally, information statistics dimensions can be broadly categorized into two types: dataset-level information and file-based configuration information. Dataset-level information includes, but is not limited to, data items, data formats, delimiters between data items, data sources, the ways and limitations are associated between data sources, data size thresholds, and data subset partitioning methods. File-based configuration information includes, but is not limited to, file-based server-side storage directories, naming conventions for final files, naming conventions for temporary files, and built-in auxiliary information within files.

[0081] In this embodiment, the commands that encapsulate the entire process of data information fileification are encapsulated into a Shell command set using the Shell commands that come with the Linux system. The DQL statements for querying fileified configuration information and the rules for concatenating multiple configurations of the same statistical dimension are all encapsulated into the Shell command set.

[0082] The shell command set consists of a parent shell command set and a child shell command set, both of which can uniformly receive external parameters. The parent shell command set includes various executable commands that file data and various temporary parameters. The child shell command set encapsulates Linux shell commands and pipe file methods, including executable commands that write dataset-sharded DQL statements to temporary files and commands that file dataset query results.

[0083] The parent shell command set receives external parameters including file ID, data date, and region range, which serve as constraints for various executed commands within the parent shell command set. The child shell command set receives external parameters consisting of pipe file identifiers generated during the execution of the parent shell command set. These pipe file identifiers are named by concatenating key parameter information such as the file ID during command execution within the parent shell command set. Each pipe file identifier corresponds to a unique pipe file at a specified path on the server side, storing key information such as query statements for querying dataset slices. Child shell commands are optional, not mandatory, within the normal parent shell command logic.

[0084] This embodiment requires the MySQL Pivot Database client to be installed on the server where the parent and child shell command sets are located. The database access command file in the client is used to remotely access the MySQL Pivot Database server and execute DQL query statements.

[0085] Database query DQL statements can be broadly categorized into three types:

[0086] The first type of DQL statement is a preset query statement in the command set, which adds data date and file ID as limiting conditions to obtain query results of various information statistical dimensions;

[0087] The second type of DQL statement is a query statement obtained by concatenating the query results of the first type of DQL statement. It is specifically used to query the data information corresponding to the file ID and the amount of data within a region, and is used to estimate the data scale.

[0088] The third type of DQL statement is a long string query command that concatenates data items and delimiters between data items according to a preset specification based on a subset of data, so that the query results can be written to a specified file.

[0089] More specifically, the parent shell command set includes five types of DQL statements, namely five types of select query statements:

[0090] The first type of DQL statement is a command that must be executed in the parent shell command set. It is used to query the file-based configuration information of data information, and the query results will be stored in the temporary parameters of the parent shell command set.

[0091] The second type of DQL statement is a query statement obtained by concatenating the query results of the first type of DQL statement. It is a command that must be executed in the parent shell command set. It is used to query the amount of data corresponding to the data information file, thereby estimating the data size. The query results will be stored in the temporary parameters of the parent shell command set.

[0092] The third type of DQL statement is concatenated from the query results of the first and second types of DQL statements by the parent shell command set. However, it may not be executed. If the estimated data size does not exceed the data size threshold, the statement will be executed and the query results will be written directly to the final file.

[0093] The fourth type of DQL statement is derived from the third type of DQL statement by the parent shell command set, with the addition of data constraint conditions. When the estimated data size exceeds the data size threshold, the dataset is split according to the single-field index situation in the first type of DQL statement, either by the range of records sorted in ascending order or by the range of single-field index. The fourth type of DQL statement is written to a pipe file, handed over to the child shell command set for execution, and the query results are written to temporary parameters.

[0094] The fifth type of DQL statement is a supplement to the fourth type of DQL statement, which is based on the first type of DQL statement to confirm the existence of a single-field index. It adds a condition to the third type of DQL statement to specify the number of rows and query the value of the indexed field.

[0095] In this embodiment, technicians set strictly controlled temporary parameters in the parent and child shell command sets. These temporary parameters, adhering to naming conventions that are accurate, concise, non-redundant, unambiguous, and non-reusable, are used to store the query results of the first type of DQL statements.

[0096] After temporary parameters are stored in the query results of the first type of DQL statement and the second type of DQL statement, technical personnel need to perform logical analysis on the temporary parameters according to preset rules. In some unreasonable scenarios where some information statistics dimensions do not meet the preset rules, such as no records are found or records are found but the information is empty, abnormal log information should be recorded in a timely manner and an abnormal exit mechanism should be implemented to facilitate the timely capture of problems by product-level and higher-level batch monitoring tools.

[0097] This embodiment requires technicians to add a step of remotely accessing the second type of DQL statement in the parent shell command set to obtain the data volume of the data subset to be filed, thereby calculating the approximate data size and comparing it with the data size threshold obtained by the first type of DQL statement. If the data size threshold is exceeded or not exceeded, the parent shell command set will be guided to adopt different data information filing schemes.

[0098] In this embodiment, multiple scenario processing solutions are provided for data information of different data scales.

[0099] (1) Suitable for single-threaded solutions with small to medium data volume. If the estimated data size does not exceed the data size threshold, the basic solution is adopted, that is, the first, second and third types of DQL statements are executed, and the query results of the third type of DQL statement are directly written to the final file.

[0100] This approach does not require consideration of single-field indexes or data partitioning, and it does not involve the use of pipe files or the invocation of shell subprocesses. It is generally suitable for data information with a small data scale.

[0101] (2) Applicable to multi-threaded solutions with large data volumes that have undergone data governance, such as when the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution is configured. In this case, the optimal big data process solution is adopted, which involves executing the first, second, fourth, and fifth types of DQL statements. The fourth type of DQL statement is written to a pipe file, and the pipe file name is used as the only parameter passed to the sub-shell command. The temporary file is written in the shell subprocess, and then the files are merged. Among them, the fourth type of DQL statement is based on the third type of DQL statement, and the query results of the fifth type of DQL statement are concatenated. The data information is decomposed into multiple dataset fragments according to the index fields.

[0102] (3) This is a secondary multi-threaded solution applicable to large-scale data projects that have not undergone data governance. For example, if the estimated data size exceeds the data size threshold and a uniformly distributed single-field index cannot be obtained, a large-scale fallback solution is adopted. This involves executing the first, second, and fourth types of DQL statements, writing the fourth type of DQL statement to a pipe file, with the pipe filename serving as the sole parameter for the sub-shell command. The temporary file is written within the shell subprocess, and then the files are merged. The fourth type of DQL statement, based on the third type of DQL statement, concatenates the ascending order of the record count range to decompose the data information into multiple dataset fragments.

[0103] Only data that has undergone data governance and has a single-field index with evenly distributed data can choose the optimal process, which involves obtaining the index field values ​​of each shard node based on the number of shards and dividing the data into subsets according to the region of the index field. The optimal process has the highest efficiency in querying shards.

[0104] Compared to the optimal process, this embodiment also provides a solution for partitioning the dataset into shards based on the number of rows sorted in ascending order when data governance is poor and it is impossible to create a single-field index that distributes data evenly. Although it is not as efficient as the optimal process in terms of time consumption and resource usage, it is still a significant improvement over the basic solution of direct writing and is a fallback solution for the optimal process.

[0105] The concept for a multi-scenario handling solution is derived from common large file transfer solutions in the industry. That is, before sending a large file, the local server splits it into smaller parts according to certain rules, sends the split files and a list of files to the remote server, and the remote server's program merges them back into the original large file based on the file list. If the local server gains higher privileges on the remote server, it can then remotely merge the files.

[0106] In this embodiment, if it is determined that the final file requires built-in auxiliary information, a new line is added at the end of the final file to include non-critical information for subsequent users to understand, including but not limited to the business meaning of the final file, the number of data records, the data date, and the statistical time. This type of information is preceded by a special character to ensure that databases of any type can quickly recognize it and skip loading this line during file loading.

[0107] In a preferred embodiment, the multi-scenario handling solution is executed as follows:

[0108] Scenario 1: Confirm that the estimated data size does not exceed the data size threshold.

[0109] This embodiment implements a basic solution for this type of scenario: the query results of the third type of DQL statement are directly written to the final file. The final filename includes the absolute path of the final file on the server.

[0110] Scenario 2: Confirm that the estimated data size exceeds the data size threshold, and that a single-field index with uniform data distribution is configured.

[0111] This embodiment implements the optimal big data workflow solution for this type of scenario:

[0112] Preset the following information: ① The estimated data scale is x times the data scale threshold, and x is rounded up; ② The number of data entries corresponding to the data scale threshold is n; ③ The field name in the single-field index is set to idx; ④ In the original sorting of the data subset, the idx field values of the (n + 1)-th, (2n + 1)-th,..., (xn - x + 1)-th records are A1, A2,..., A , ,

[0119] , ,

[0120] , ,

[0117] , n-1 , ,

[0118] , ,

[0116] ,

[0121] .

[0113] 1) Obtain the fourth type of DQL statement

[0114] In this embodiment, after confirming that a single-field index with uniform data distribution can be obtained, the data subset is quickly divided using the index. Based on the third type of DQL statement, a qualification condition regarding the idx field is added to form x fourth type of DQL statements; at the same time, the unique MySQL dialect database-specific Limit clause (Limit m, 1) is added to obtain a single data entry, forming x fifth type of DQL statements.

[0115] The fifth type of DQL statement actually queries the idx field values of the (n + 1)-th, (2n + 1)-th,..., (xn - x + 1)-th records in the original sorting of the data subset; [[ID=​​​​​​​​​​​​​​​​​4) Implement file merging

[0122] After all shell child processes invoked by the parent shell process have finished normally, the parent shell command set writes the files sequentially to the final file in ascending order of temporary filenames, and deletes the temporary files and pipe files generated by the parent shell process.

[0123] Scenario 3: Confirm that the estimated data size exceeds the data size threshold and that a single-field index with uniform data distribution cannot be obtained.

[0124] This embodiment implements a big data fallback solution for this type of scenario:

[0125] The following information is preset: ① The estimated data size is x times the data size threshold, with x rounded up; ② The number of data records corresponding to the data size threshold is set to n.

[0126] 1) Obtain the fourth type of DQL statement

[0127] After confirming that it is impossible to obtain a single-field index with uniform data distribution and to use the index to quickly partition the data subset, the Limit clause (Limit m,n) unique to MySQL's own database style is added to the third type of DQL statement to obtain the data range, forming x fourth type DQL statements.

[0128] The fourth type of DQL statement actually queries x data shards of a data subset. Each data shard corresponds to the data from the 1st to the nth data, the data from the (n+1)th to the 2nth data, ..., the data from the (xn-x+1)th to the last data in the data subset.

[0129] 2) Generate pipe files

[0130] The parent shell process corresponding to the parent shell command set generates a multiple of x pipe files in a loop. The pipe file name contains information such as file ID, region range, data date, and the sequence number within the multiple x. Each pipe file contains a corresponding DQL statement (Type 4) and a corresponding temporary filename to be written. The temporary filename contains the absolute path to the temporary file on the server.

[0131] 3) Initiate the sub-shell process

[0132] The parent shell process corresponding to the parent shell command set simultaneously launches x child shell processes corresponding to child shell command sets in the background of the server where the parent shell command set resides via the nohup command. Each child shell process receives the name of the pipe file as its unique input parameter. Each child shell process implements the corresponding fourth type of DQL statement and writes it to a temporary file.

[0133] 4) Implement file merging

[0134] After all shell child processes invoked by the parent shell process have finished normally, the parent shell command set writes the files sequentially to the final file in ascending order of temporary filenames, and deletes the temporary files and pipe files generated by the parent shell process.

[0135] This embodiment also provides a data information file processing system, including a database access module, a logical decision module, a file writing module, a sub-layer Shell call module, a sub-process scanning module, a file merging module, and a log and exception handling module;

[0136] The database access module calls the first and second types of DQL statements, writes the query results to temporary parameters, and then concatenates the third, fourth, and fifth types of DQL statements based on the temporary parameters for file writing in different scenarios. Any query failure or abnormal result will trigger the logging and exception handling module functions.

[0137] The logical decision module determines whether a single-field index exists in the first type of DQL statement and receives the data size estimation result from the second type of DQL statement to select a solution. If the estimated data size does not exceed the data size threshold, the directly generated basic solution is adopted; if it exceeds the threshold and a specific index exists, the optimal big data process solution is adopted; otherwise, the big data fallback solution is adopted.

[0138] The file writing module works as follows: When the Shell parent process processes small amounts of data for file conversion, it uses a basic approach, calling the database access module to directly write the results of the third type of DQL statements to the final file. When processing large amounts of data for file conversion, the Shell parent process uses an optimal big data workflow and a fallback big data approach, writing the common fourth type of DQL statements to multiple pipe files. The Shell child process retrieves information from the pipe files and calls the database access module to directly write the results of the fourth type of DQL statements to a temporary file. Any write errors will trigger the logging and exception handling modules.

[0139] The sub-shell calling module is triggered by the shell parent process after generating multiple pipe files in the file writing module. This module reads the pipe file name parameters and the contents of the pipe file, triggering the shell child process's file writing module. At the same time, this module is monitored throughout by the child process scanning module, which monitors whether it is running and its running status. Any function error will trigger the parent process's logging and exception handling module functions.

[0140] The child process scanning module queries the execution status of the sub-shell call module and the file writing module of the shell child process based on the pipe file name; it checks whether each child process terminates normally based on the return results of the child process log and exception handling module; if all terminate normally, the file merging module of the shell parent process is triggered; if there are still running child processes, the process enters a short sleep and then re-checks and judges; if any child process exception occurs, the log and exception handling modules of the parent process are triggered.

[0141] The file merging module, after receiving a normal termination signal from the child process scanning module, merges multiple temporary files generated by the Shell child process's file writing module into a final file, and deletes the temporary files and pipe files; if an exception occurs during the merging process, it triggers the parent process's "logging and exception handling module".

[0142] The logging and exception handling module records a normal log after each step is completed if all steps execute normally. If an error occurs during the execution of a module step, both normal and exception logs are recorded simultaneously, and the module exits with an exception, throwing an exception for the monitoring program to capture the error information. All steps in this module are auxiliary steps designed to assist the batch monitoring program in capturing exceptions.

[0143] The data information document processing method, when implemented in the aforementioned data information document processing system, includes the following steps:

[0144] 1. The database access module receives the external parameter file ID, data date, and region range, executes the first type of DQL statement query, obtains the configuration information of various statistical dimensions of the data information file, and the second and third types of DQL statements, and then proceeds to step 2.

[0145] 2. The database access module receives and executes the second type of DQL statement to obtain the estimated data size, which is then transmitted to the logic decision module for comparison with the data size threshold, and proceeds to step 3.

[0146] 3. The logical decision module receives the estimated data size, performs a data size threshold comparison, obtains the comparison result, and selects either step 4 or step 5.

[0147] 4. In scenarios where the threshold is not exceeded, the file writing module accepts the third type of DQL statement, writes the execution result into the final file in file form, provides it to the user, and ends all steps;

[0148] 5. In scenarios where the threshold is exceeded, obtain the number of shards after splitting the data subset, and proceed to step 6;

[0149] 6. The logical decision module receives the identifier information indicating whether a specific index is required, obtains the data subset splitting scheme, and selects to execute step 7 or step 8.

[0150] 7. In scenarios where there is no specific requirement for an index, the database access module receives the row number sharding scheme parameters, assembles the third type of DQL statement with the clause of Limit row number interval in ascending order into the fourth type of DQL statement, writes it into the pipeline file corresponding to the number of shards, and executes step 10.

[0151] 8. In scenarios where specific index requirements exist, the database access module receives the index sharding scheme parameters, combines the third type of DQL statement with the Limit clause specifying the number of rows in ascending order, assembles the fifth type of DQL statement, obtains the values ​​of each index field for the specified number of rows, and executes step 9.

[0152] 9. In scenarios where specific index requirements exist, the database access module receives the index sharding scheme parameters, assembles the third type of DQL statement with the clauses indicating that the value of the index field is within a certain range, writes the new fourth type of DQL statement into the pipeline file corresponding to the number of shards, and executes step 10.

[0153] 10. The sub-shell call module receives the filename of the fragmented pipe, reads the fourth type of DQL statement and the temporary filename in the corresponding pipe file, transmits them to the database access module, writes the query result of the fourth type of DQL statement into the temporary file, and executes step 11.

[0154] 11. The subprocess scanning module receives the fragmented pipe file name, confirms that the Shell subprocess has terminated normally, transmits the Shell subprocess termination return value to the file merging module, performs file merging, temporary file cleanup, and pipe file cleanup, obtains the final file in file form, provides it to the user, and ends all steps.

[0155] Among them, the database access module in the parent Shell command set also has a sub-step of checking file configuration information. When the file configuration information item is found, it will check whether the final file storage path, temporary file storage path, and pipe file storage path exist, and will also check the user's file creation and write permissions. All of these are preparation steps.

[0156] After the files are merged, the temporary file cleanup and pipe file cleanup steps are sub-steps of the file writing module in the parent Shell command set. After the files are merged, all temporary files and pipe files generated in the entire process will be deleted, which are cleanup steps.

[0157] In this embodiment, the method for data subset slicing in the optimal big data workflow is derived from extensive data practice; the experience in setting the data scale threshold is based on repeatedly statistically analyzing the time taken to generate files of a certain data scale and the time taken to merge files of the same scale, and calculating a relatively suitable threshold calculation logic; using pipeline files to divide the dataset into shards and process the files in parallel by different Shell subprocesses, and finally merging them, is an optimization of various file generation solutions in the industry; the query and judgment order of the general file-based configuration information parameterization in the parent Shell command set is the optimal order after multiple rounds of tuning.

[0158] The preferred embodiments of the present invention have been described in detail above. It should be understood that those skilled in the art can make numerous modifications and variations based on the concept of the present invention without creative effort. Therefore, all technical solutions that can be obtained by those skilled in the art based on the concept of the present invention through logical analysis, reasoning, or limited experimentation on the basis of existing technology should be within the scope of protection defined by the claims.

Claims

1. A method for processing data information into files, characterized in that, Includes the following steps: The Shell command set is used to receive data information that needs to be filed and its related parameters. The Shell command set includes a parent Shell command set and a child Shell command set. The data information that needs to be documented is broken down into documented configuration information according to the preset information statistics dimensions and stored in the information dimension configuration table; the information dimension configuration table is designed based on the preset information statistics dimensions. Based on the documented configuration information, the data size of the data information is estimated and compared with a preset data size threshold; If the estimated data size does not exceed the data size threshold, the Shell parent process is directly invoked to generate the final file; If the estimated data size exceeds the data size threshold, the data information is broken down into multiple dataset fragments, and multiple Shell child processes are synchronously launched through the Shell parent process using pipe files. Each Shell child process writes each dataset fragment to multiple temporary files, and finally merges the multiple temporary files into the final file. The parent shell command set includes five types of DQL statements; The first type of DQL statement is a preset query statement, which is a command that must be executed in the parent shell command set. It is used to query the file-based configuration information of data information, and the query results will be stored in the temporary parameters of the parent shell command set. The second type of DQL statement is a query statement obtained by concatenating the query results of the first type of DQL statement. It is a command that must be executed in the parent shell command set. It is used to query the amount of data corresponding to the data information file, thereby estimating the data size. The query results will be stored in the temporary parameters of the parent shell command set. The third type of DQL statement is obtained by concatenating the query results of the first and second types of DQL statements from the parent shell command set. If the estimated data size does not exceed the data size threshold, this statement will be executed and the query results will be written directly to the final file. The fourth type of DQL statement is obtained by concatenating data limiting conditions on the basis of the third type of DQL statement from the parent shell command set. When the estimated data size exceeds the data size threshold, the dataset is split according to the single field index in the first type of DQL statement, either by the number of records or by the single field index. The fourth type of DQL statement is written to a pipe file and executed by the child shell command set, and the query results are written to temporary parameters. The fifth type of DQL statement is a supplement to the fourth type of DQL statement, which is based on the first type of DQL statement to confirm the existence of a single-field index. It adds a condition to the third type of DQL statement to specify the number of rows and query the value of the indexed field.

2. The data information file processing method according to claim 1, characterized in that, The data information decomposition scheme includes decomposition according to the range of the number of records and decomposition according to the range of a single field index; If the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution is configured, then the data is split according to the index field. If the estimated data size exceeds the data size threshold and a single-field index with uniform data distribution cannot be obtained, the data will be split according to the range of the number of records.

3. The data information file processing method according to claim 1, characterized in that, The Shell command set includes a parent Shell command set and a child Shell command set; The parent shell command set includes various execution commands and temporary parameters for data information files; the child shell command set includes execution commands for writing dataset sharding DQL statements to temporary files.

4. The data information file processing method according to claim 1, characterized in that, Both the parent and child shell command sets include temporary parameters. After the query results of the first type of DQL statement and the second type of DQL statement are stored in the temporary parameters, logical analysis is required. For temporary parameters that do not meet the preset conditions, detailed exception log information should be recorded and an exception exit mechanism should be executed.

5. The data information file processing method according to claim 1, characterized in that, The relevant parameters include file ID, data date, and region range. The file ID is a fixed identifier set for the data information that needs to be fileified, serving as a general identifier for the fileification configuration.

6. The data information file processing method according to claim 5, characterized in that, The information dimension configuration table is located in the MySQL Streaming database, and uses file id as one of the primary keys or composite primary keys.

7. A data information document processing system, characterized in that, The data information file processing method described in claim 1 includes a database access module, a logical decision module, a file writing module, a sub-shell calling module, a sub-process scanning module, a file merging module, and a log and exception handling module. The database access module calls the first and second types of DQL statements, writes the query results to temporary parameters, and then concatenates the third, fourth, and fifth types of DQL statements based on the temporary parameters for file writing in different scenarios; any query failure or abnormal result will trigger the logging and exception handling module functions. The logical decision module selects a solution by determining whether a single-field index exists in the first type of DQL statement and receiving the estimation results of the data size from the second type of DQL statement. The file writing module writes the query results of DQL statements to a temporary file or the final file by calling the Shell parent process. Any writing error will trigger the logging and exception handling module functions. The sub-shell calling module is triggered by the shell parent process after generating multiple pipe files in the file writing module. This module reads the pipe file name parameters and the contents of the pipe file, triggering the shell child process's file writing module. At the same time, this module is monitored throughout by the child process scanning module, which monitors whether it is running and its running status. Any function error will trigger the parent process's logging and exception handling module functions. The subprocess scanning module queries the execution status of the sub-layer Shell call module and the file writing module of the Shell subprocess based on the pipe file name; it determines whether each subprocess terminates normally based on the subprocess log and the return result of the exception handling module; if all terminate normally, it triggers the file merging module of the Shell parent process. If there are still running child processes, the process will enter a short sleep and then re-examine and determine the status; if any child process is abnormal, the parent process's logging and exception handling modules will be triggered. The file merging module, after receiving a normal termination signal from the subprocess scanning module, merges multiple temporary files generated by the Shell subprocess's file writing module into a final file, and deletes the temporary files and pipe files. If an exception occurs during the merging process, the parent process's logging and exception handling modules will be triggered. The logging and exception handling module records a normal log after each step is completed if all steps are executed normally. If an error occurs during the execution of a certain module step, both normal and abnormal logs will be recorded simultaneously, and an abnormal exit will be executed, throwing an exception so that the monitoring program can capture the error information.

8. A data information document processing device, characterized in that, It includes a memory and a processor, wherein the memory stores a computer program, and the processor invokes program instructions to execute the data information file processing method as described in any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, It includes a computer program that can be executed by a processor to implement the data information file processing method as described in any one of claims 1-6.

Images