A data warehousing method and system based on file arrangement
By using a file-based data import method, the data import process is divided into data acquisition and arrangement stages. This generates files that are consistent with the database backup file format, and performs quality checks during the data acquisition stage. This solves the complexity and performance problems of traditional import methods, and achieves efficient and reliable data import and backup.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- HENAN XINAN COMM TECH CO LTD
- Filing Date
- 2023-12-23
- Publication Date
- 2026-06-09
Smart Images
Figure CN118069603B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing technology, and in particular to a data entry method and system based on file arrangement. Background Technology
[0002] In the era of big data, various types of data sources are constantly emerging, including structured data (such as tabular data in traditional databases), semi-structured data (such as logs and XML files), unstructured data (such as text, images, and audio), and real-time streaming data. Storing multiple types of data from different sources into databases has become commonplace and essential. At the same time, the need for real-time data analysis and decision-making has placed higher demands on the efficiency of data import.
[0003] Currently, there are two main traditional methods for data import: using SQL or ETL tools to insert data into a database. However, when dealing with multiple data sources and large amounts of data, using the traditional SQL method has the following problems:
[0004] (1) Complexity: Different database systems may have different syntax for certain operations, and writing optimized SQL for data entry is time-consuming and requires a high level of database expertise. Customized conversions are required for different data sources, and the conversion process requires code implementation, resulting in a large workload for development and adjustment.
[0005] (2) Performance issues: Under high load, SQL insertion will consume database connection and IO resources. Due to the database storage mechanism, it will also consume a lot of memory and write a lot of logs, which will cause data query response delay. Complex data queries will also cause the performance of data insertion to decrease, thus affecting the availability and stability of the database.
[0006] (3) Difficulty of copying and backup: In the case of massive data, data copying and backup usually require additional resources, which will further degrade the performance of the database.
[0007] Using open-source ETL tools presents the following problems:
[0008] (1) Complexity: The ETL process usually requires a lot of configuration in order to transform data from different sources into database ingestion operations. Due to the fixed steps of ETL tools, optimization is difficult.
[0009] (2) Performance issues: Since each step of an ETL tool can only perform one operation, the data processing steps may be numerous and complex, which will directly lead to a decrease in processing performance. In addition, storing intermediate data at each step will consume a lot of memory and computing resources. The more steps there are, the greater the possibility of performance bottlenecks.
[0010] (3) Process crash: Due to uncontrollable factors of data quality, some steps in the process may fail, which will cause the entire processing to stop. If data verification is performed on each processing step, although the stability of the program can be guaranteed, the data conversion efficiency will be seriously affected. Summary of the Invention
[0011] Given the diversity of data types and the massive volume of data, traditional methods of data import using SQL or ETL tools suffer from complex processes and low efficiency. This invention provides a data import method and system based on file arrangement.
[0012] On one hand, the present invention provides a data import method based on file arrangement, including:
[0013] Step 1: Extract valid data from the specified data source;
[0014] Step 2: Execute the database backup command to obtain the data arrangement format of the data backup file corresponding to the current database type;
[0015] Step 3: Arrange the extracted valid data according to the data arrangement format of the data backup file to form a structured dataset;
[0016] Step 4: Store the structured dataset in a pre-built "Writing" folder to generate a file with the same format as the data backup file. Once the writing is complete, cut and paste the file in the "Writing" folder into the pre-built "Import" folder.
[0017] Step 5: Execute the incremental data loading command to load the files in the folder to be imported into the database. After loading is complete, cut the files in the folder to be imported into the pre-built folder that has been loaded into the database.
[0018] Furthermore, the data arrangement format is used to indicate the order of the data fields and the storage format of each field.
[0019] Furthermore, in step 4, generating a file with the same format as the data backup file specifically includes naming and storing the file to be generated according to the naming and storage formats of the data backup file.
[0020] Furthermore, in step 4, during the writing process, a structured dataset corresponds to one or more files to be generated, and the file size of a file to be generated is determined based on the current database loading performance.
[0021] Furthermore, in step 1, during the extraction of valid data, data quality checks and error handling are performed on the data source, and a log of the entire extraction process is generated.
[0022] On the other hand, the present invention provides a data entry system based on document arrangement, comprising:
[0023] The data acquisition module is used to extract valid data from a specified data source;
[0024] The data arrangement format acquisition module is used to execute database backup commands to obtain the data arrangement format of the data backup file corresponding to the current database type;
[0025] The data arrangement module is used to arrange the extracted valid data according to the data arrangement format of the data backup file to form a structured dataset;
[0026] The file processing module is used to store the structured dataset into a pre-built writing folder to generate a file with the same format as the data backup file. After the writing is completed, the file in the writing folder is cut and pasted into a pre-built import folder. After all the files in the import folder are loaded into the database, the file in the import folder is cut and pasted into a pre-built database-ready folder.
[0027] The reverse backup module is used to execute incremental data loading commands to load files in the folder to be imported into the database.
[0028] The beneficial effects of this invention are:
[0029] (1) By dividing the entire data entry process into two stages, namely the data acquisition and arrangement stage and the file loading stage, effective optimization of the stages can be carried out without affecting the database performance, and multiple data source types can be processed efficiently and without redundancy.
[0030] (2) By arranging the extracted valid data according to the data arrangement format of the data backup file supported by the database to generate a file that is consistent with the format of the data backup file, the data is loaded into the database in reverse in the form of a file, which reduces the complexity and time cost of data import; and since the format of the data file written is also the format of the data backup file, the file itself is also an effective carrier for backup and migration, thus achieving the reuse effect of writing once and using it multiple times.
[0031] (3) By performing data quality checks, error handling and log recording during the data acquisition stage, we can ensure the quality of the data entering the database and reduce the error rate during the data loading process. On the other hand, while ensuring efficiency and timeliness, we can monitor data processing in real time, which helps to trace and troubleshoot problems and provides more possibilities for analysis dimensions.
[0032] (4) This invention supports both traditional databases and distributed databases. Attached Figure Description
[0033] Figure 1 A flowchart illustrating a data import method based on file arrangement provided in an embodiment of the present invention;
[0034] Figure 2 A schematic diagram illustrating the data arrangement format for acquiring data backup files according to an embodiment of the present invention;
[0035] Figure 3 This is a flowchart illustrating a data entry system based on file arrangement, provided as an embodiment of the present invention. Detailed Implementation
[0036] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of the embodiments of this invention will be clearly described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.
[0037] Example 1
[0038] like Figure 1 As shown, this embodiment of the invention provides a data import method based on file arrangement, including the following steps:
[0039] S101: Extract valid data from the specified data source;
[0040] S102: Execute the database backup command to obtain the data arrangement format of the data backup file corresponding to the current database type;
[0041] Specifically, database backup can be viewed as the process of storing structured data into files. Therefore, in this embodiment, the data arrangement format of the data backup file can be obtained by executing the database backup command.
[0042] like Figure 2 As shown in (a), taking a PostgreSQL database as an example, by executing the database backup command "pg_dump -d" on a certain data file, the following results can be obtained: Figure 2 (b) shows the data arrangement format. This data arrangement format specifies the field information of the data, the order of the fields, and the storage format of each field. For example, according to the order of the fields, the field information includes id, appid, groupid, userid, user, etc.
[0043] S103: Arrange the extracted valid data according to the data arrangement format of the data backup file to form a structured dataset;
[0044] S104: Store the structured dataset in a pre-built writing folder to generate a file with the same format as the data backup file. When the writing is complete, cut the file in the writing folder into the pre-built import folder.
[0045] Specifically, during the writing process, a structured dataset corresponds to one or more files to be generated, and the file size of a file to be generated is determined based on the current database loading performance, thereby achieving a balance between data loading performance and data timeliness.
[0046] In this embodiment of the invention, the format of the data backup file mainly includes the file naming format and the storage format. Therefore, when generating a file that is consistent with the format of the data backup file, the process mainly includes: naming and storing the file to be generated according to the naming format and storage format of the data backup file, respectively.
[0047] S105: Execute the incremental data loading command to load the files in the folder to be imported into the database. After loading is complete, cut the files in the folder to be imported into the pre-built folder that has been loaded into the database.
[0048] Specifically, files already in the database folder can be migrated, compressed, or backed up as needed, or deleted periodically to reduce disk usage.
[0049] In this embodiment, during the data orchestration stage, the extracted valid data is orchestrated according to the data orchestration format of the data backup file supported by the database. Therefore, the format of the generated file is naturally consistent with the format of the data backup file, so that the extracted valid data can be reverse-loaded into the database in the form of a file. This invention refers to this reverse loading process as reverse backup.
[0050] Meanwhile, this invention pre-constructs three types of folders: a "writing in progress" folder, a "pending import" folder, and a "already in the database" folder. This allows files at different stages to be stored in different folder types, enabling incremental loading of data files without affecting the database's CRUD (Create, Read, Update, Delete) services. Furthermore, since the valid data file is generated according to the data backup file format, this file itself is a structured backup of the valid data for the database. Thus, the production environment database can back up only the process and log data generated by business operations, without needing to execute a separate database backup command to back up the valid data, ultimately reducing database resource consumption and improving database performance and stability. Moreover, the generated valid data file can be directly used for data recovery and migration, thereby improving data security and reusability.
[0051] In this embodiment of the invention, by strictly dividing the data extraction and orchestration stage from the file loading into the database stage, the data acquisition and orchestration stage does not consume database resources when solving the problem of inconsistent data formats from different data sources, and therefore does not affect the current performance of the database. Thus, both of the above stages can be deployed on the database server for execution.
[0052] Example 2
[0053] Based on Embodiment 1 above, the difference lies in that, when dealing with large amounts of data and complex data transformation processes, this embodiment deploys the data extraction and orchestration stages on a separate server, while only the file loading into the database stage is deployed on the database server, thereby maximizing performance. It should be noted that, with separate deployment, the written files need to be uploaded to the database server. Traditional integrated data acquisition and recording methods and ETL tools cannot achieve stage division and independent deployment.
[0054] Furthermore, during the extraction of valid data, data quality checks, error handling, and logging can be selectively performed on the data source to ensure data integrity and traceability, thereby guaranteeing high data quality and availability.
[0055] The other steps are the same as in Example 1, and will not be repeated here.
[0056] Example 3
[0057] like Figure 3 As shown, this embodiment of the invention provides a data entry system based on file arrangement, including a data acquisition module 301, a data arrangement format acquisition module 302, a data arrangement module 303, a file processing module 304, and a reverse backup module 305.
[0058] The data acquisition module 301 is used to extract valid data from a specified data source; the data arrangement format acquisition module 302 is used to execute a database backup command to obtain the data arrangement format of the data backup file corresponding to the current database type; the data arrangement module 303 is used to arrange the extracted valid data according to the data arrangement format of the data backup file to form a structured dataset; the file processing module 304 is used to store the structured dataset in a pre-built writing folder to generate a file consistent with the format of the data backup file, and when the writing is completed, cut the files in the writing folder to a pre-built import folder; and when all the files in the import folder are loaded into the database, cut the files in the import folder to a pre-built already loaded folder; the reverse backup module 305 is used to execute an incremental data loading command to load the files in the import folder into the database.
[0059] It should be noted that the embodiments of the present invention are for implementing the above method embodiments, and their specific functions can be referred to the above method embodiments, which will not be repeated here.
[0060] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.
Claims
1. A data import method based on file arrangement, characterized in that, include: Step 1: Extract valid data from the specified data source; During the extraction of valid data, data quality checks and error handling are performed on the data source, and a log of the entire extraction process is generated. Step 2: Execute the database backup command to obtain the data arrangement format of the data backup file corresponding to the current database type; The data arrangement format is used to indicate the order of the data fields and the storage format of each field; Step 3: Arrange the extracted valid data according to the data arrangement format of the data backup file to form a structured dataset; Step 4: Store the structured dataset in a pre-built "Writing in Progress" folder to generate files consistent with the format of the data backup files. Once the writing is complete, cut and paste the files in the "Writing in Progress" folder into a pre-built "Import to Publish" folder. Specifically, generating files consistent with the format of the data backup files includes naming and storing the files to be generated according to the naming and storage formats of the data backup files. Furthermore, during the writing process, one structured dataset corresponds to one or more files to be generated, and the file size of a file to be generated is determined based on the current database loading performance. Step 5: Execute the incremental data loading command to load the files in the folder to be imported into the database. After loading is complete, cut the files in the folder to be imported into the pre-built folder that has been loaded into the database.
2. A data entry system based on file arrangement, applied to the data entry method based on file arrangement as described in claim 1, characterized in that, include: The data acquisition module is used to extract valid data from a specified data source; The data arrangement format acquisition module is used to execute database backup commands to obtain the data arrangement format of the data backup file corresponding to the current database type; The data arrangement module is used to arrange the extracted valid data according to the data arrangement format of the data backup file to form a structured dataset; The file processing module is used to store the structured dataset into a pre-built writing folder to generate a file with the same format as the data backup file. After the writing is completed, the file in the writing folder is cut and pasted into the pre-built import folder. And after all the files in the folder to be imported are loaded into the database, cut the files in the folder to be imported into the pre-built folder that has been loaded into the database; The reverse backup module is used to execute incremental data loading commands to load files in the folder to be imported into the database.