A method and system for batch importing data
By preprocessing two-dimensional table data and batch processing of databases, the problems of slow processing speed and high cluster pressure when importing hierarchical data sets are solved, and efficient data import and reduce cluster pressure are achieved.
Patent Information
- Application Number
- CN202411703437.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-11-26
- Publication Date
- 2025-04-01
AI Technical Summary
When importing data sets with hierarchical relationships, the processing speed is slow, resulting in high pressure on the data set cluster and poor user experience and business scalability.
By preprocessing the two-dimensional table data to be imported, the data is sorted into a multi-dimensional array, and using the idea of pruning to verify and process it in the database at a hierarchical level, obtaining the data set to be added, and finally using the database batch processing method to be used to process each data set to be added.
The data processing logic is simplified, the import efficiency is improved, and the pressure on the data set cluster is reduced. The import time is shortened from the original 20-30s+ to 4-10s.
Smart Images
Figure CN119739713A_ABST
Abstract
Description
Technical Field
[0001] The present application belongs to the field of data processing technology, and specifically relates to a method and system for batch importing data. Background Art
[0002] In the data of various industries, there are widely data sets with hierarchical relationships. Figure 1 As shown in the figure, this kind of hierarchical data set is often called tree-structured data. In addition to business data, each data item in the tree structure also has an identifier of the parent level to which it belongs. The top-level data item is identified by a special symbol to identify it as top-level data. In a hierarchical data set, the number of levels and the number of data items contained in each level are theoretically unlimited, but generally a limit is artificially determined based on business needs or the hardware resources of the information system.
[0003] People usually represent hierarchical data sets in the form of two-dimensional tables. In a two-dimensional table, each row generally represents a data item. The columns of the two-dimensional table list the identifiers of the data items of the upper levels to which the data item belongs in order from left to right from high to low. For example, a data item of the third level is expressed as:
[0004] First-level ID, second-level ID, data item ID
[0005] In the database of the information system, when saving hierarchical data sets, in order to reduce data storage space, usually only the identifier of the data item and the identifier of the data item of the previous layer to which it belongs need to be stored. For example, a data item of the third layer is stored as follows:
[0006] Second level ID, data item ID
[0007] When manually maintained data needs to be imported into an information system, it is usually necessary to determine for each data item whether the level to which it belongs already exists. If it does not exist, the ID of the data item at that level needs to be added first, and then the data item is written. The process is as follows: Figure 2 shown.
[0008] The existing data import method has a long data processing time, frequent database read and write operations, poor user experience and business scalability (increase the number limit), and easily puts pressure on the underlying data cluster. Take a three-layer data set with an upper limit of 50 data per layer as an example. The maximum data volume of this data set is: 50*50*50=125,000. When importing data, the number of database read and write operations is: 125,000 rows*4 times=50w times, and the required import processing time is about 20-30s+. Summary of the invention
[0009] The purpose of this application is to overcome the shortcomings of the prior art in that the data processing speed is slow and the pressure on the data set cluster is high.
[0010] In order to achieve the above object, the present application proposes a method for batch importing data, which is used to batch import hierarchical data represented by a two-dimensional table into a database, and the method comprises:
[0011] Step 1: Preprocess the two-dimensional table data to be imported and organize the data into a multi-dimensional array form;
[0012] Step 2: Using the pruning idea, the obtained multidimensional array is verified and processed hierarchically in the database to obtain multiple data sets to be added;
[0013] Step 3: Use the database batch processing method to process each new data set.
[0014] As an improvement to the above method, the multidimensional array is in the form of:
[0015] The first dimension data is the identifier of all data items in the first level; each data item in the first dimension contains an array, and each data in the array is the identifier of the data item in the next level contained in the first dimension data item, and so on. The multidimensional array represents a hierarchical data set in a tree structure.
[0016] As an improvement to the above method, each of the to-be-added data sets includes data items in a level that do not exist in the database.
[0017] As an improvement to the above method, when the to-be-added data sets are processed in batches using a database, one to-be-added data set is processed each time.
[0018] The present application also provides a batch data import system, which is implemented based on the above method, and the system includes:
[0019] A preprocessing module is used to preprocess the two-dimensional table data to be imported and organize the data into a multi-dimensional array form;
[0020] The data verification module is used to verify the obtained multidimensional array by level in the database using the pruning idea to obtain multiple data sets to be added;
[0021] The batch import module is used to process each data set to be added using the database batch processing method.
[0022] Compared with the prior art, the advantages of this application are:
[0023] 1. Simplified the complexity of data processing logic and improved import efficiency;
[0024] 2. Reduced the pressure on the data set cluster. BRIEF DESCRIPTION OF THE DRAWINGS
[0025] Figure 1 Shown is a schematic diagram of a hierarchical relationship data set;
[0026] Figure 2 The figure is a flow chart of a method for processing a hierarchical relationship data set in the prior art;
[0027] Figure 3 The figure shows a flow chart of the method for batch importing data provided by the present application;
[0028] Figure 4 Shown is a schematic diagram of the results of the verification process. DETAILED DESCRIPTION
[0029] The technical solution of the present application is described in detail below with reference to the accompanying drawings.
[0030] The method and system for batch importing data provided by the present application pre-process the two-dimensional table to be imported, first sort out the data item levels that are not in the database, and use the batch writing characteristics of the database to write all the missing data item information in one level at one time. It only needs to perform the same number of write operations as the number of levels to ensure that the parent level information of all data items exists in the database, and then write each data item, which can greatly reduce the query and ingestion operations of the database, improve data processing efficiency, and reduce the pressure on the underlying data cluster.
[0031] Example 1
[0032] like Figure 3 As shown, the method for importing data provided by this application includes:
[0033] Step 1: Preprocess the two-dimensional table data to be imported and organize the data into a multi-dimensional array.
[0034] The dimension of a multidimensional array is equal to the level of the data. The first dimension of the multidimensional array is the identifier of all the data items in the first level. Each data item in the first dimension contains an array. Each data in the array is the identifier of the data item in the next level contained in the first dimension data item. And so on. The multidimensional array represents a hierarchical data set in a tree structure.
[0035] Step 2: Using the pruning idea, verify the multidimensional array obtained in the previous step hierarchically in the database.
[0036] The results of the verification process are sorted by level, and each level of data items that do not exist in the database are sorted into a set as the data set to be added, and at most the number of sets is the same as the number of levels.
[0037] Step 3: Use the database batch processing method to process each new data set.
[0038] By using the batch processing method of the database, one database operation can complete the processing of a data set to be added, that is, one operation can complete the addition of missing data items in a level in the database.
[0039] Taking the student ability indicator data in a teaching system as an example, the above method is explained:
[0040] The capability indicator data has a maximum of three levels, and the upper limit of each level of data items is 50. Part of the data in the two-dimensional table to be imported is shown in Table 1:
[0041] Table 1 Partial data of the two-dimensional table of capability indicators to be imported
[0042]
[0043] First, preprocess the two-dimensional table data and organize the data into a multidimensional array. The results are as follows:
[0044]
[0045] Then, using the pruning idea, the multidimensional array obtained in the previous step is verified hierarchically in the database.
[0046] like Figure 4 As shown, the results of the verification process have the following three situations:
[0047] 1) The data in the hierarchy in the database does not exist and needs to be completely added;
[0048] 2) Some data already exists in the hierarchy of the database and needs to be partially added;
[0049] 3) All data in the hierarchy in the database exists and is completely filtered.
[0050] After the previous step, we can clearly know which data needs to be written and which data should be discarded. Therefore, for the data range that needs to be written, we use the database's batch operation method to integrate the data and write it in batches.
[0051] Insert the second-level data. The sample SQL is as follows:
[0052] INSERT INTO dimensions(name,parent_id,is_deleted)
[0053] VALUES
[0054] ('Secondary Dimension',1,0),
[0055] ('Secondary dimension 1',1,0);
[0056] Insert the third-level data. The sample SQL is as follows:
[0057] INSERT INTO indexes(name,dimension_id,is_deleted)
[0058] VALUES
[0059] ('Capability Index',20,0),
[0060] ('Capability Index 1',20,0),
[0061] ('Capability index 2', 20, 0);
[0062] Compared with the prior art, the method for importing data provided by this application has the following advantages:
[0063] 1. Using the above method to import batch data simplifies the complexity of data processing logic and improves import efficiency. The execution efficiency can be reduced from the original 20-30s+ to 4-10s.
[0064] The existing technical solution requires verification of 125,000 rows one by one. However, the method provided by this application only needs to be processed by level classification, and the same data item is only verified once, and a processing mark is added to the data to facilitate subsequent batch write operations.
[0065] 2. Data is processed in batches, reducing the pressure on the data set cluster.
[0066] In the existing technical solution, the number of database read and write operations is 125,000 rows * 4 times = 500,000 times. The method provided by this application has the best case scenario: all data items in the database do not exist and need to be completely added, which only requires 50 read verifications + 50*(1+50) = 2,600 batch writes. The worst case scenario is: some data exists in the database and needs to be partially added, which requires 50*(1+50) + 50*50*50 = 12.7w read verifications.
[0067] Example 2
[0068] The present application also provides a batch data import system, which is implemented based on the above method, and the system includes:
[0069] A preprocessing module is used to preprocess the two-dimensional table data to be imported and organize the data into a multi-dimensional array form;
[0070] The data verification module is used to verify the obtained multidimensional array by level in the database using the pruning idea to obtain multiple data sets to be added;
[0071] The batch import module is used to process each data set to be added using the database batch processing method.
[0072] The present application may also provide a computer device, comprising: at least one processor, a memory, at least one network interface and a user interface. The various components in the device are coupled together through a bus system. It is understood that the bus system is used to achieve connection and communication between these components. In addition to the data bus, the bus system also includes a power bus, a control bus and a status signal bus.
[0073] The user interface may include a display, a keyboard or a pointing device, such as a mouse, a trackball, a touch pad or a touch screen.
[0074] It is understood that the memory in the embodiments disclosed in the present application may be a volatile memory or a non-volatile memory, or may include both volatile and non-volatile memories. Among them, the non-volatile memory may be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), or a flash memory. The volatile memory may be a random access memory (RAM), which is used as an external cache. By way of example and not limitation, many forms of RAM are available, such as static random access memory (SRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDRSDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synchronous link dynamic random access memory (SLDRAM), and direct memory bus random access memory (DRRAM). The memories described herein are intended to include, but are not limited to, these and any other suitable types of memories.
[0075] In some embodiments, the memory stores the following elements, executable modules or data structures, or a subset thereof, or an extended set thereof: an operating system and applications.
[0076] The operating system includes various system programs, such as a framework layer, a core library layer, a driver layer, etc., which are used to implement various basic services and process hardware-based tasks. The application includes various application programs, such as a media player (Media Player), a browser (Browser), etc., which are used to implement various application services. The program for implementing the method of the embodiment of the present disclosure can be included in the application.
[0077] In the above embodiment, the processor may also call a program or instruction stored in the memory, specifically, a program or instruction stored in an application program, and is used to:
[0078] Execute the steps of the above method.
[0079] The above method can be applied to a processor or implemented by a processor. The processor may be an integrated circuit chip with signal processing capabilities. In the implementation process, each step of the above method can be completed by an integrated logic circuit of hardware in the processor or an instruction in the form of software. The above processor may be a general processor, a digital signal processor (Digital Signal Processor, DSP), an application specific integrated circuit (Application Specific Integrated Circuit, ASIC), a field programmable gate array (Field Programmable Gate Array, FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components. The above-disclosed methods, steps and logic block diagrams can be implemented or executed. The general processor may be a microprocessor or the processor may also be any conventional processor, etc. The steps of the above-disclosed method can be directly embodied as a hardware decoding processor to execute, or the hardware and software modules in the decoding processor are combined to execute. The software module can be located in a mature storage medium in the field such as a random access memory, a flash memory, a read-only memory, a programmable read-only memory or an electrically erasable programmable memory, a register, etc. The storage medium is located in the memory, and the processor reads the information in the memory and completes the steps of the above method in combination with its hardware.
[0080] It is understood that the embodiments described in the present application can be implemented by hardware, software, firmware, middleware, microcode or a combination thereof. For hardware implementation, the processing unit can be implemented in one or more application specific integrated circuits (ASIC), digital signal processors (DSP), digital signal processing devices (DSPD), programmable logic devices (PLD), field programmable gate arrays (FPGA), general-purpose processors, controllers, microcontrollers, microprocessors, other electronic units for performing the functions described in the present application or a combination thereof.
[0081] For software implementation, the technology of the present application can be implemented by executing the functional modules (such as procedures, functions, etc.) of the present application. The software code can be stored in a memory and executed by a processor. The memory can be implemented in the processor or outside the processor.
[0082] The present application may also provide a non-volatile storage medium for storing a computer program. When the computer program is executed by a processor, each step in the above method embodiment can be implemented.
[0083] Finally, it should be noted that the above embodiments are only used to illustrate the technical solution of the present application and are not intended to limit it. Although the present application is described in detail with reference to the embodiments, a person skilled in the art should understand that any modification or equivalent replacement of the technical solution of the present application does not depart from the spirit and scope of the technical solution of the present application and should be included in the scope of the claims of the present application.
Claims
1. A method for batch importing data, for batch importing hierarchical data represented by a two-dimensional table into a database, the method comprising: Step 1: Preprocess the two-dimensional table data to be imported and organize the data into a multi-dimensional array form; Step 2: Using the pruning idea, the obtained multidimensional array is verified and processed hierarchically in the database to obtain multiple data sets to be added; Step 3: Use the database batch processing method to process each new data set.
2. The method for batch importing data according to claim 1, characterized in that: The form of the multidimensional array is: The first dimension data is the identifier of all data items in the first level; each data item in the first dimension contains an array, and each data in the array is the identifier of the data item in the next level contained in the first dimension data item, and so on. The multidimensional array represents a hierarchical data set in a tree structure.
3. The method for batch importing data according to claim 1, characterized in that: Each of the to-be-added data sets includes a data item in a level that does not exist in the database.
4. The method for batch importing data according to claim 1, characterized in that: When using the database to batch process the data sets to be added, one data set to be added is processed each time.
5. A batch data import system, implemented based on the method according to any one of claims 1 to 4, characterized in that: The system comprises: A preprocessing module is used to preprocess the two-dimensional table data to be imported and organize the data into a multi-dimensional array form; The verification data module is used to verify the obtained multi-dimensional array by level in the database by using the pruning idea to obtain multiple data sets to be added; and The batch import module is used to process each data set to be added using the database batch processing method.