A data insertion method for batch inserting asset data into a big data analysis platform
By adopting the batch insertion method of asset data in the big data analysis platform, using concurrent number parameters and data preprocessing mechanism, the performance bottlenecks and data integrity problems when importing large amounts of asset data are solved, and efficient and stable data import and resources are achieved. Optimization ensures data accuracy and system stability.
Patent Information
- Application Number
- CN202410673898.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-05-28
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2044-05-28
AI Technical Summary
When importing large amounts of asset data, there are problems such as system response and data loss. Especially when the data volume exceeds 500,000 records, the page loading time is prolonged, the interaction is hysteresis, and even temporary unresponsive states occur. , and data omissions may occur when importing APIs, affecting the accuracy and reliability of the data.
Using the batch insertion method of asset data, by establishing a tool with adjustable concurrent number parameters, parsing, verifying and preprocessing data, uploading it to the target database in batches, optimizing the concurrent data transmission and processing mechanism, ensuring the unified and complete data format, and using transactions Manage and batch logs to prevent data omissions.
It realizes efficient, stable and accurate processing of large amounts of asset data import, significantly improves the import speed, reduces network round trips and database operations, ensures data integrity and reasonable allocation of system resources, and avoids system shocks and data loss.
Smart Images

Figure CN118550927B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of big data analysis, and in particular to a data insertion method for inserting asset data in batches into a big data analysis platform. Background Art
[0002] When performing big data analysis on asset data, the current big data analysis platform uses the following asset data import methods: asset management page import method, and asset management page API import method.
[0003] When the asset management page import method is performing asset data import operations, when the imported data set is small, the system can handle it smoothly, and the import process is fast and unimpeded. However, when the amount of imported data exceeds 500,000 records, the system response slows down significantly, which is manifested in longer page loading time, delayed interaction, and even temporary unresponsiveness, causing obvious waiting and inconvenience in operation.
[0004] When importing asset data by calling the asset management page API import method, data integrity issues will be encountered. Specifically, although calling the asset management page API to import data bypasses the front-end restrictions, when trying to import a large amount of asset information, the system occasionally misses data, that is, some asset records are not successfully entered into the system. This data loss not only affects the accuracy and reliability of asset management, but may also cause a chain reaction in subsequent asset analysis, report generation and other links, reducing overall work efficiency and data credibility.
[0005] In summary, the existing technology lacks an effective method for batch importing asset data that can ensure smooth system processing without data loss, so as to efficiently, stably and accurately handle the import of large amounts of asset data. Summary of the invention
[0006] In view of the above analysis, an embodiment of the present invention aims to provide a data insertion method for batch inserting asset data into a big data analysis platform, so as to solve the problem of slow system response or data loss when batch importing asset data in the prior art.
[0007] On the one hand, an embodiment of the present invention provides a data insertion method for inserting asset data in batches into a big data analysis platform, comprising the following steps:
[0008] S1. Establish a batch asset data insertion tool with adjustable concurrent number parameters;
[0009] S2. After receiving the data import start instruction, set the concurrency parameter of the asset data batch insertion tool according to the amount of data to be imported;
[0010] S3. Select the asset data to be imported in the asset data batch insertion tool, and control the asset data batch insertion tool to parse and verify the selected asset data to be imported in turn to obtain the asset data to be imported in a standard data format;
[0011] S4. Control the asset data batch insertion tool to perform data preprocessing on the asset data to be imported in the standard data format to achieve data format unification and standardization;
[0012] S5. Establish a data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform, and upload the asset data to be imported after data preprocessing to the target database in batches according to the preset data mapping rules and the above-mentioned concurrent number parameter settings.
[0013] The beneficial effects of the above technical solution are as follows: it solves the performance bottleneck and data integrity problems of the big data analysis platform using traditional page import and API calls when processing and importing batch asset data. It can efficiently, stably and accurately process the import of large amounts of asset data. The concurrent number parameters of the asset data batch insertion tool can be customized, with simple configuration and easy delivery. The tool automatically parses the original data file and verifies the data accuracy. In addition, according to the data preprocessing logic, the data is cleaned to ensure the uniformity and standardization of the data format. Through the optimized concurrent data transmission and processing mechanism, a large amount of data can be efficiently transmitted to the database in batches, greatly reducing the number of network round trips and database operations, thereby significantly improving the speed of data import and shortening the overall time consumption. Through concurrent batch processing strategies and transaction management, the asset data batch insertion tool can allocate and use system resources more reasonably and reduce the impact on the target database. It is reusable, and other companies that need to import asset data in batches when processing projects on the security big data analysis platform can directly reuse it.
[0014] Based on the further improvement of the above method, step S2 includes the following sub-steps:
[0015] S21. After receiving the data import start instruction, the asset data to be imported is scanned to determine the amount of data to be imported;
[0016] S22. Determine the optimal number of concurrent data insertions on the server based on the number of CPU cores and memory size of the server and the amount of asset data to be imported;
[0017] S23. Set the concurrency parameter in the asset data bulk insertion tool to the above-mentioned optimal concurrency.
[0018] Further, the parsing in step S3 includes the following sub-steps:
[0019] S31. Set column mapping rules in the configuration file of the asset data bulk insertion tool, wherein the column mapping rules include mapping rules between column names of the CSV file and field names and field types of the target database;
[0020] S32. Control the asset data batch insertion tool to automatically locate all CSV files corresponding to the asset data to be imported under the specified path, and open each CSV file;
[0021] S33. The asset data batch insertion tool is controlled to map each CSV file according to the above column mapping rules, so that the column names of the CSV file correspond to the field names and field types of the target database one by one;
[0022] S34. Perform data type conversion on each CSV file after mapping according to the data type requirements of the target database;
[0023] S35. Perform structured processing on each CSV file after data type conversion to obtain a data structure list that is easy to process corresponding to each CSV file, and each element in the list represents an independent asset data to be imported.
[0024] Further, the verification in step S3 includes the following sub-steps:
[0025] S36. Perform a mandatory item check on each element in the data structure list and correct the mandatory item check failure results so that each element contains a non-null value;
[0026] S37. Perform data format verification on each element in the data structure list, including using a regular expression or built-in function to verify whether the data format of each numerical data conforms to the expected data format, and correcting the elements that do not conform to the expected data format;
[0027] S38. Perform data range verification on each element in the data structure list, so that the asset data to be imported corresponding to each element is within a reasonable set range and the string length meets the maximum field length limit of the target database;
[0028] S39. Perform a uniqueness check on each element in the data structure list, and exclude duplicate records of asset data to be imported to ensure that each element in the data structure list has data uniqueness;
[0029] S40. List the data structures processed in steps S36-S39 as the asset data to be imported in a standard data format.
[0030] Further, step S4 includes the following sub-steps:
[0031] S41. Control the asset data batch insertion tool to convert the data type of the asset data to be imported in the standard data format so that the type of each column of data is correct;
[0032] S42. Control the asset data batch insertion tool to clean the text data of the asset data to be imported in the standard data format after the data type conversion, including removing irrelevant characters and unifying upper and lower case characters;
[0033] S43. Control the asset data batch insertion tool to perform time data processing on the asset data to be imported in the standard data format after the text data is cleaned, so that the format of all time data in the asset data to be imported is consistent and in the same time zone;
[0034] S44. Control the asset data batch insertion tool to replace null values in the asset data to be imported in the standard data format after time data processing, and replace all null values with specified values.
[0035] Further, step S5 includes the following sub-steps:
[0036] S51. Establish a data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform;
[0037] S52. Verify whether the data transmission connection is valid, and correct the database configuration information in the data transmission connection for invalid data transmission connections until the data transmission connection is valid;
[0038] S53. The asset data to be imported after data preprocessing is uploaded to the target database in batches according to the preset data mapping rules and the above-mentioned concurrent number parameter settings until all asset data to be imported are inserted;
[0039] S54. Close the data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform to release the resources it occupies on the server.
[0040] Further, the data mapping rules in step S53 include column mapping rules.
[0041] Further, step S53 includes the following sub-steps:
[0042] S531. Control asset data batch insertion tool to prepare SQL insert statements according to data mapping rules;
[0043] S532. Control the asset data batch insertion tool according to the concurrency parameter setting in its parameters, start multi-threaded batch execution of SQL insert statements, and insert the asset data to be imported after data cleaning into the target database.
[0044] Further, step S53 also includes the following sub-steps:
[0045] S533. During the insertion process of step S532, the asset data batch insertion tool is controlled to record the insertion status of each asset data to be imported, and the number of successful insertions is counted. For the problem of failed insertion, the asset data to be imported is modified and reinserted.
[0046] Further, step S53 also includes the following sub-steps:
[0047] S534. During the insertion process of step S532, if the data transmission connection is disconnected or the data format is incorrect, the asset data batch insertion tool is controlled to record the error information and roll back all operations of the current batch. After the data is corrected, all operations of the current batch are re-executed to prevent data omissions.
[0048] This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the invention, nor is it intended to limit the scope of the invention. BRIEF DESCRIPTION OF THE DRAWINGS
[0049] The above and other objects, features and advantages of the present invention will become more apparent through a more detailed description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, wherein like reference numerals generally represent like components throughout the exemplary embodiments of the present invention.
[0050] Figure 1 A schematic diagram showing the steps of the data insertion method in Example 1 is shown;
[0051] Figure 2 A schematic flow chart of the data insertion method of Embodiment 1 is shown. DETAILED DESCRIPTION
[0052] Embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to make the present invention more thorough and complete, and to fully convey the scope of the present invention to those skilled in the art.
[0053] As used herein, the term "including" and its variations mean open inclusion, i.e., "including but not limited to". Unless otherwise stated, the term "or" means "and / or". The term "based on" means "based at least in part on". The terms "an example embodiment" and "an embodiment" mean "at least one example embodiment". The term "another embodiment" means "at least one further embodiment". The terms "first", "second", etc. may refer to different or the same objects. Other explicit and implicit definitions may also be included below.
[0054] Example 1
[0055] An embodiment of the present invention discloses a method for inserting asset data in batches into a big data analysis platform, aiming to effectively improve the problems of slow system response or data loss when importing asset data in batches in the prior art through an improved solution.
[0056] like Figure 1-2 As shown, the data insertion method includes the following steps:
[0057] S1. Establish a batch asset data insertion tool with adjustable concurrency parameters.
[0058] Specifically, the asset data batch insertion tool can set a concurrency parameter, which refers to the number of connections that the data batch insertion tool simultaneously accesses to the server. The asset data batch insertion tool is provided with a start function (corresponding to step S2), a data preparation function (corresponding to step S3), a data preprocessing function (corresponding to step S4), a database connection function, and a data mapping function and a data insertion function (corresponding to step S5).
[0059] S2. After receiving the data import start instruction, set the concurrency parameter of the asset data batch insertion tool according to the amount of asset data to be imported.
[0060] Specifically, the user starts the start function of the asset data batch insertion tool and sets the concurrency parameter in the parameters. After the concurrency parameter is set, the asset data batch insertion tool can execute multiple thread processes at the same time.
[0061] S3. Select the asset data to be imported in the asset data batch insertion tool, and control the asset data batch insertion tool to parse and verify the selected asset data to be imported in sequence to obtain the asset data to be imported in a standard data format.
[0062] Specifically, the user starts the data preparation function of the asset data batch insertion tool, selects multiple files of asset data to be imported, and the asset data batch insertion tool automatically parses and verifies the data files to ensure that the data format is correct.
[0063] S4. Control the asset data batch insertion tool to perform data preprocessing on the asset data to be imported in the standard data format to achieve data format unification and standardization.
[0064] S5. Establish a data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform, and upload the asset data to be imported after data preprocessing to the target database in batches according to the preset data mapping rules and the above-mentioned concurrent number parameter settings.
[0065] Compared with the prior art, the method of this embodiment solves the performance bottleneck and data integrity problems of the big data analysis platform using traditional page import and API calls when processing and importing batch asset data. It can efficiently, stably and accurately process the import of large amounts of asset data. The concurrency parameter of the asset data batch insertion tool can be customized, with simple configuration and easy delivery. The tool automatically parses the original data file and verifies the data accuracy. In addition, according to the data preprocessing logic, the data is cleaned to ensure the uniformity and standardization of the data format. Through the optimized concurrent data transmission and processing mechanism, a large amount of data can be efficiently transmitted to the database in batches, greatly reducing the number of network round trips and database operations, thereby significantly improving the speed of data import and shortening the overall time consumption. Through concurrent batch processing strategies and transaction management, the asset data batch insertion tool can allocate and use system resources more reasonably and reduce the impact on the target database. It is reusable, and other companies that need to import asset data in batches when processing projects on the security big data analysis platform can directly reuse it.
[0066] Example 2
[0067] Based on the improvement of Example 1, step S2 (start function of asset data batch insertion tool) includes the following sub-steps:
[0068] S21. After receiving the data import start instruction, the asset data to be imported is scanned to determine the data volume of the asset data to be imported.
[0069] S22. Determine the optimal concurrency number for data insertion on the server based on the number of CPU cores and memory size of the server and the amount of asset data to be imported.
[0070] S23. Set the concurrency parameter in the asset data bulk insertion tool to the above-mentioned optimal concurrency.
[0071] Preferably, the parsing in step S3 (data preparation function of asset data batch insertion tool) includes the following sub-steps:
[0072] S31. Mapping rule setting: Set column mapping rules in the configuration file of the asset data batch insertion tool, and the column mapping rules include mapping rules between the column names of the CSV file and the field names and field types of the target database. The column mapping rules are used to match the field names and field types of the target database in the CSV file one by one.
[0073] S32. File reading: Control the asset data batch insertion tool to automatically locate all CSV files corresponding to the asset data to be imported under the specified path, and use an efficient data reading module (CSV library in Python or Apache CommonsCSV library) to open each CSV file to ensure that the reading process is efficient and lossless.
[0074] S33. Column mapping: Control asset data batch insertion tool to map each CSV file according to the above column mapping rules, so that the column name of the CSV file corresponds to the field name and field type of the target database. This step ensures that even if the column order of the CSV file is different from the table structure of the target database, it can be correctly matched.
[0075] S34. Data type conversion: Perform data type conversion on each CSV file after mapping according to the data type requirements of the target database. Specifically, when reading each row of data, the asset data batch insertion tool will perform type checks on each column of data and convert it according to the data type requirements of the field in the target database, for example, converting string dates to date and time types, and converting numeric values to corresponding integer or floating point types.
[0076] S35. Structuring the results: Each CSV file after data type conversion is structured to obtain an easy-to-process data structure list corresponding to each CSV file, and each element in the list represents an independent asset data (a record) to be imported. The parsed data is organized into an easy-to-process data structure and converted into a list to prepare for subsequent verification and insertion into the database.
[0077] The parsing result obtained through steps S32-S35 is a structured data set. After the parsing is completed, the data of the original CSV file will be converted into a series of structured data objects, that is, a Python dictionary list, each object contains the database table field name as a key and the corresponding CSV data value as a value, which is convenient for subsequent verification and processing.
[0078] Preferably, during the execution of steps S32-S35, the asset data batch insertion tool should record all problems found during the parsing process, such as file non-existence, format error, etc., and output them through log files, so that users can understand and correct them in time. The default log only outputs errors or problem data. If you want to see the structured data set, you need to turn on the debug mode.
[0079] Preferably, the verification in step S3 (data preparation function of asset data batch insertion tool) includes the following sub-steps:
[0080] S36. Mandatory item check: perform mandatory item check on each element in the data structure list and correct the unqualified mandatory item check results so that each element contains a non-null value. That is, for the fields marked as mandatory in the table definition of the target database, verify whether each record contains a non-null value.
[0081] S37. Data format verification: Perform data format verification on each element in the data structure list, including using regular expressions or built-in functions to verify whether the data format of each numerical data conforms to the expected data format, and correcting the elements that do not conform to the expected data format. Specifically, for data of types such as dates and numbers, use regular expressions or built-in functions to verify whether their formats conform to expectations, for example, whether the date format is YYYY-MM-DD.
[0082] S38. Data range verification: Perform data range verification on each element in the data structure list, so that the asset data to be imported corresponding to each element is within a reasonable set range (for example, age is not less than 0 and not greater than 150), and its string length complies with the maximum field length limit of the target database.
[0083] S39. Perform a uniqueness check on each element in the data structure list, and exclude duplicate records of the asset data to be imported to ensure that each element in the data structure list has data uniqueness. Specifically, for fields that require uniqueness in the (target database) table, such as asset numbers, verify whether there are duplicate records to ensure data uniqueness.
[0084] S40. List the data structures processed in steps S36-S39 as the asset data to be imported in a standard data format.
[0085] Preferably, during the execution of steps S36-S40, the asset data batch insertion tool should record all problems found during the verification process. After the verification is completed, the asset data batch insertion tool will print a log, listing all the data records that have passed the verification and the specific reasons for failing the verification (such as which field of which record does not meet the requirements), so that users can quickly locate and correct the problem, and clearly display the overall pass status of the data verification (such as "a total of XX records, YY passed the verification, and ZZ failed"), so that users can see it at a glance.
[0086] Preferably, step S4 (data preprocessing function of the asset data batch insertion tool, mainly cleaning the parsed data) includes the following sub-steps:
[0087] S41. Data type conversion: Control the asset data batch insertion tool to convert the data type of the asset data to be imported in the standard data format so that the data type of each column is correct. For example, converting text-type numbers to numeric types facilitates subsequent mathematical operations and analysis.
[0088] S42. Text data cleaning: Control the asset data batch insertion tool to perform text data cleaning on the asset data to be imported in the standard data format after data type conversion, including removing irrelevant characters (including deleting special symbols, blank characters, etc.) and unifying upper and lower case (converting to all lower case or all upper case for easy comparison).
[0089] S43. Time data processing: Control the asset data batch insertion tool to perform time data processing (including format unification and time zone adjustment) on the asset data to be imported in the standard data format after the text data is cleaned, so that the format of all time data in the asset data to be imported is consistent (i.e., the format is unified, such as converted into a unified timestamp) and is in the same time zone (i.e., time zone adjustment, adjusting the time data to the same time zone);
[0090] S44. Control replacement: Control the asset data batch insertion tool to replace null values in the asset data to be imported in the standard data format after time data processing, and replace all null values with specified values. Specifically, for the target database field type required to be non-null and the value in the asset data to be imported that is null, replace the null value with the specified value according to the corresponding field null value replacement rule configured in the configuration file.
[0091] Preferably, step S5 (database connection function, data mapping function and data insertion function of the asset data batch insertion tool) includes the following sub-steps:
[0092] S51. Database connection (i.e. database connection function): establish a data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform. Specifically, the pymysql module of the python language and the target database can be used to try to establish a connection.
[0093] S52. Connection verification: verify whether the above data transmission connection is valid, and for invalid data transmission connection, modify the database configuration information in the data transmission connection until the data transmission connection is valid.
[0094] Specifically, the sign of a successful connection is that the code is executed without exception and returns the expected results. If the above code block that attempts to connect and query does not throw any exceptions, the connection is successful. The SELECT ERSION() query can successfully return the database version information, proving that the connection is not only established, but also can interact normally.
[0095] Connection failure is indicated by exception capture or error information. If any exception is captured during the connection attempt or query execution, the connection failed. Exception objects usually carry error information, such as "Access denied for user" (permission problem), "Connection refused" (connection refused), "Unknown database" (database does not exist), etc., which directly point out the cause of the failure. If the connection fails, the asset data bulk insertion tool will prompt the user to check the database configuration information.
[0096] S53. Data insertion (ie, data insertion function): the asset data to be imported after data preprocessing is uploaded to the target database in batches according to the preset data mapping rules and the above-mentioned concurrent number parameter settings until all asset data to be imported are inserted.
[0097] S54. Clean up and close after insertion is completed: Close the data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform to release the resources it occupies on the server.
[0098] Preferably, step S53 includes the following sub-steps:
[0099] S530. Set data mapping rules; specifically, the data mapping rules in step S53 include column mapping rules, and other rules can be set according to requirements.
[0100] S531. Control the asset data batch insertion tool to prepare SQL insert statements according to the data mapping rules; SQL insert statements are a common language for operating databases, and those skilled in the art can understand them. (Steps S530-S531 together constitute the data mapping function)
[0101] S532. Control the asset data batch insertion tool according to the concurrency parameter setting in its parameters, start multi-threaded batch execution of SQL insert statements, and insert the asset data to be imported after data cleaning into the target database.
[0102] Preferably, step S53 further includes the following sub-steps:
[0103] S533. Problem data processing: During the insertion process of step S532, the asset data batch insertion tool is controlled to record the insertion status (success / failure) of each asset data to be imported, and the number of successful insertions is counted. The asset data to be imported that failed to be inserted will be modified and reinserted.
[0104] Specifically, after the asset data batch insertion tool is executed, you can view the number of successful insertions and detailed information on failed records in the detailed log of the insertion operation. If any problematic data is found, you can modify it and reinsert it.
[0105] Preferably, step S53 further includes the following sub-steps:
[0106] S534. Solution to prevent data omission: During the insertion process of step S532, if the data transmission connection is disconnected or the data format is incorrect, the asset data batch insertion tool is controlled to record the error information and roll back all operations of the current batch. After the data is corrected, all operations of the current batch are re-executed to prevent data omission.
[0107] The solution to prevent data omission includes transaction processing and batch log.
[0108] Transaction processing: Use transactions in database operations to ensure data consistency and integrity. This means that the transaction will be committed only when all data records are successfully inserted; if any record fails to be inserted, the entire batch of operations will be rolled back, thus preventing some data from being inserted while other data is omitted.
[0109] Batch log: Records detailed logs for each batch insert operation, including start time, end time, number of successfully inserted records, and detailed information of failed records (such as row number, error cause, etc.) to ensure that no data is missed.
[0110] As a preferred implementation, data preprocessing uses Python's Pandas library for data frame operations, NumPy for numerical calculations, and Scikit-learn for data conversion and outlier detection. Regular expressions are used to clean and format text data.
[0111] Compared with Example 1, the data insertion method for inserting asset data in batches into a big data analysis platform provided in this embodiment has the following advantages:
[0112] 1. The asset data batch insertion tool is designed specifically for processing large-scale data. Its introduction provides an efficient and reliable way to solve the difficulties of existing technologies.
[0113] 2. High performance and low time consumption: The asset data batch insertion tool can efficiently transfer large amounts of data to the database in batches through an optimized concurrent data transmission and processing mechanism, greatly reducing the number of network round trips and database operations, thereby significantly improving the speed of data import and shortening the overall time consumption. Even when facing data sets of millions, it can maintain good response performance.
[0114] 3. Data integrity and accuracy assurance: The built-in data validation logic of the asset data batch insertion tool can perform strict verification before data is imported, including data format, logical consistency, etc., to ensure that only accurate data can be written into the system, effectively avoiding data loss and insertion of erroneous data, and maintaining the high quality of data assets.
[0115] 4. Resource optimization and stability: Through intelligent batch processing strategies and transaction management, the batch insertion tool can allocate and use system resources more reasonably, reduce the impact on the database, avoid service instability or interruption caused by resource competition, and improve the stability and reliability of the entire import process.
[0116] 5. In the big data analysis platform applied to the customer site, when importing a large amount of asset data, it was proved that the overall time consumption was low, the data import was complete, and the import efficiency and stability were high.
[0117] Embodiments of the present invention have been described above, and the above description is exemplary, not exhaustive, and is not limited to the disclosed embodiments. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The terms used herein are selected to best explain the principles of the embodiments, practical applications, or improvements over the prior art, or to enable other persons of ordinary skill in the art to understand the embodiments disclosed herein.
Claims
1. A method for inserting asset data in batches into a big data analysis platform, characterized in that: The steps include: S1. Establish a batch asset data insertion tool with adjustable concurrent number parameters; S2. After receiving the data import start instruction, set the concurrency parameters of the asset data batch insertion tool according to the amount of asset data to be imported, including: S21. After receiving the data import start instruction, the asset data to be imported is scanned to determine the amount of data to be imported; S22. Determine the optimal number of concurrent data insertions on the server based on the number of CPU cores and memory size of the server and the amount of data to be imported; S23. Set the concurrency parameter in the asset data batch insertion tool to the above-mentioned optimal concurrency; S3. Select the asset data to be imported in the asset data batch insertion tool, and control the asset data batch insertion tool to parse and verify the selected asset data to be imported in turn to obtain the asset data to be imported in a standard data format; The analysis in step S3 further includes the following sub-steps: S31. Set column mapping rules in the configuration file of the asset data bulk insertion tool, wherein the column mapping rules include mapping rules between column names of the CSV file and field names and field types of the target database; S32. Control the asset data batch insertion tool to automatically locate all CSV files corresponding to the asset data to be imported under the specified path, and open each CSV file; S33. The asset data batch insertion tool is controlled to map each CSV file according to the above column mapping rules, so that the column names of the CSV file correspond to the field names and field types of the target database one by one; S34. Perform data type conversion on each CSV file after mapping according to the data type requirements of the target database; S35. Perform structured processing on each CSV file after data type conversion to obtain a data structure list that is easy to process corresponding to each CSV file, and each element in the list represents an independent asset data to be imported; The verification in step S3 further includes the following sub-steps: S36. Perform a mandatory item check on each element in the data structure list and correct the mandatory item check failure results so that each element contains a non-null value; S37. Perform data format verification on each element in the data structure list, including using a regular expression or built-in function to verify whether the data format of each numerical data conforms to the expected data format, and correcting the elements that do not conform to the expected data format; S38. Perform data range verification on each element in the data structure list, so that the asset data to be imported corresponding to each element is within a reasonable set range and the string length meets the maximum field length limit of the target database; S39. Perform a uniqueness check on each element in the data structure list, and exclude duplicate records of asset data to be imported to ensure that each element in the data structure list has data uniqueness; S40. The data structure list processed by steps S36-S39 is used as the asset data to be imported in a standard data format; S4. Control the asset data batch insertion tool to perform data preprocessing on the asset data to be imported in the standard data format to achieve data format unification and standardization, including: S41. Control the asset data batch insertion tool to convert the data type of the asset data to be imported in the standard data format so that the type of each column of data is correct; S42. Control the asset data batch insertion tool to clean the text data of the asset data to be imported in the standard data format after the data type conversion, including removing irrelevant characters and unifying upper and lower case characters; S43. Control the asset data batch insertion tool to perform time data processing on the asset data to be imported in the standard data format after the text data is cleaned, so that the format of all time data in the asset data to be imported is consistent and in the same time zone; S44. Control the asset data batch insertion tool to replace null values in the asset data to be imported in the standard data format after time data processing, and replace all null values with specified values; S5. Establish a data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform, and upload the asset data to be imported after data preprocessing to the target database in batches according to the preset data mapping rules and the above-mentioned concurrent number parameter settings, including: S51. Establish a data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform; S52. Verify whether the data transmission connection is valid, and correct the database configuration information in the data transmission connection for invalid data transmission connections until the data transmission connection is valid; S53. The asset data to be imported after data preprocessing is uploaded to the target database in batches according to the preset data mapping rules and the above-mentioned concurrent number parameter settings until all asset data to be imported are inserted, including: S531. Control asset data batch insertion tool to prepare SQL insert statements according to data mapping rules; S532. Control the asset data batch insertion tool according to the concurrency parameter setting in its parameters, start multi-threaded batch execution of SQL insert statements, and insert the asset data to be imported after data cleaning into the target database; Data mapping rules include column mapping rules; S54. Close the data transmission connection between the asset data batch insertion tool and the target database in the big data analysis platform to release the resources it occupies on the server.
2. The method for inserting asset data in batches into a big data analysis platform according to claim 1, characterized in that: Step S53 also includes the following sub-steps: S533. During the insertion process of step S532, the asset data batch insertion tool is controlled to record the insertion status of each asset data to be imported, and the number of successful insertions is counted. For the problem of failed insertion, the asset data to be imported is modified and reinserted.
3. The method for inserting asset data in batches into a big data analysis platform according to claim 2, characterized in that: Step S53 also includes the following sub-steps: S534. During the insertion process of step S532, if the data transmission connection is disconnected or the data format is incorrect, the asset data batch insertion tool is controlled to record the error information and roll back all operations of the current batch. After the data is corrected, all operations of the current batch are re-executed to prevent data omissions.
Citation Information
Patent Citations
Quick and bulk importing method for data, electronic device and computer readable storage medium
CN108491475A
Data import method, device and equipment and computer program product
CN113342885A
Enhanced real-time calculation method for dynamic real-time synchronization of multi-source large table data
CN113407600A
High-concurrency data storage method and device, electronic equipment and storage medium
CN115408383A
File import method and device and electronic equipment
CN115934728A
