An automated processing method and system for multi-field data table management and warehousing

Abstract

The present invention discloses an automated processing method and system for managing and warehousing multi-field data tables, belonging to the field of big data and database management technology. The system preprocesses data, constructs a graph model, segments data tables, establishes field mapping relationships, creates associated IDs for subtables, sets indexes to import subtables, and develops automated management process tools, innovating strategy optimization algorithms, to achieve efficient management and rapid warehousing of complex data tables. The present invention can achieve a more efficient multi-field data table management and warehousing process, significantly reducing manual operations, improving import efficiency, ensuring data consistency and integrity, and facilitating data management. The flexible warehousing mechanism allows for customized processing based on the structure and field characteristics of the data table.

Description

Technical Field

[0001] The present invention relates to the technical field of big data and database management, and in particular to an automated processing method and system for managing and storing multi-field data tables. Background Art

[0002] With the advent of the big data era, data volumes are increasing exponentially, leading to increasingly complex data and table structures and content. Therefore, managing and storing multi-field tables has become crucial. Traditional methods for managing and storing large, multi-field tables rely heavily on manual operations or simple automated scripts, resulting in low efficiency and difficulty meeting the demands of real-time data analysis and timely business response. Therefore, how to efficiently manage and store these tables while ensuring data security has become a critical issue that needs to be addressed. Summary of the Invention

[0003] The technical task of the present invention is to address the above shortcomings and provide an automated processing method and system for multi-field data table management and warehousing. Through innovative strategies and optimization algorithms, efficient management and rapid warehousing of complex data tables are achieved.

[0004] The technical solution adopted by the present invention to solve its technical problem is:

[0005] An automated processing method for managing and warehousing multi-field data tables. Through data preprocessing, building a graph model, splitting data tables, establishing field mapping relationships, creating associated IDs for sub-tables, setting indexes to import sub-tables, and writing automated management process tools, innovative strategy optimization algorithms, it achieves efficient management of complex data tables and rapid warehousing.

[0006] Furthermore, the implementation process of this method is as follows:

[0007] (1) Data preprocessing:

[0008] First, the data tables to be managed and stored are received, and the data is cleaned and pre-processed to remove duplicate, invalid or erroneous data to ensure data quality and consistency;

[0009] (2) Constructing a graph model:

[0010] Based on business needs and the correlation between fields, we analyze the dependencies and access frequencies between fields and build a graph model to prepare for table sharding.

[0011] (3) Data table segmentation:

[0012] Aggregate highly relevant or frequently accessed fields into a subtable;

[0013] Use the graph model to analyze the association and association frequency between fields and determine which fields should be grouped into the same sub-table.

[0014] (4) Establish field mapping relationship:

[0015] Establish field mapping relationships for each sub-table to ensure correct mapping of data between different tables;

[0016] (5) Create an associated ID for the child table and set an index:

[0017] The association ID is not only used to identify the child table, but also to build the association relationship between the child tables and implement cross-table data association query;

[0018] (6) Import the data into the sub-table and match the unique ID field of other sub-tables for the sub-table:

[0019] Import the total table data into each sub-table through SQL statements;

[0020] (7) Development of automated warehousing tools:

[0021] A set of multi-field data table automatic splitting tools was developed through Python packaging to realize automatic segmentation of data tables, automatic establishment of field mapping relationships, automatic generation of associated IDs, and automatic import of sub-table tasks.

[0022] Furthermore, the data preprocessing, based on segmentation strategies and field mapping relationships, enables automatic cleaning, segmentation, conversion, and merging of data tables, significantly shortening the data processing cycle and improving the efficiency of data query, processing, and storage.

[0023] Furthermore, the graph model is constructed based on the dependencies and access frequencies between fields, and fields with high correlation or frequent access are aggregated into a sub-table; the efficiency of subsequent data queries is optimized and cross-table operations are reduced.

[0024] Preferably, in the graph model, each node represents a field, and an edge represents an association between fields, and a line is drawn between two nodes according to possible connections between the fields.

[0025] Furthermore, the data table segmentation is to design a reasonable segmentation strategy according to the characteristics of the data table and business requirements, and split the data table into multiple sub-tables;

[0026] The field mapping relationship is established for each sub-table, and the data type, format, value range, etc. are defined for each field through metadata management and data dictionary technology, which facilitates cross-table query and data integration.

[0027] Furthermore, a unique ID is created for each sub-table to ensure the association between the sub-tables;

[0028] Create a unique ID for each piece of data in the child table using a distributed unique ID generator or creating a UUID in MySQL. Add indexing strategies to appropriate fields based on the data characteristics and query requirements to improve query efficiency.

[0029] The present invention also claims protection for an automated processing system for managing and warehousing multi-field data tables, comprising a data pre-processing module, a graph model building module, a data table segmentation module, a field mapping relationship establishment module, a sub-table association ID creation module, a data import sub-table module, and an automated warehousing tool generation module;

[0030] The system realizes the automatic processing of multi-field data table management and warehousing through the above method.

[0031] The present invention also claims protection for an automated processing device for managing and storing multi-field data tables, comprising at least one memory and at least one processor;

[0032] The at least one memory is configured to store a machine-readable program;

[0033] The at least one processor is configured to call the machine-readable program to implement the above method.

[0034] The present invention also claims protection for a computer-readable medium having computer instructions stored thereon, which, when executed by a processor, causes the processor to implement the above method.

[0035] Compared with the prior art, the automated processing method and system for managing and storing multi-field data tables of the present invention has the following beneficial effects:

[0036] The multi-field data table management and warehousing process method provided by the present invention achieves efficient management and rapid warehousing of complex data tables through innovative strategies and optimization algorithms, significantly improving the efficiency and accuracy of data processing and management, and reducing operating costs. Furthermore, the method of the present invention is applicable to various data tables with multiple fields, achieving more efficient table management and warehousing processes, significantly reducing manual operations, improving import efficiency, ensuring data consistency and integrity, and facilitating data management, thus having broad application prospects. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 This is a flowchart of an automated processing method for managing and storing multi-field data tables provided by an embodiment of the present invention;

[0038] Figure 2 This is an example diagram of a graph model construction provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0039] The present invention will be further described below with reference to specific embodiments.

[0040] An embodiment of the present invention provides an automated processing method for multi-field data table management and warehousing. Through data preprocessing, graph model construction, data table segmentation, field mapping relationship establishment, creation of associated IDs for sub-tables, index setting and importing sub-tables, and writing an automated management process tool, innovative strategy optimization algorithm, efficient management of complex data tables and rapid warehousing are achieved.

[0041] The data preprocessing mentioned above uses an automated multi-field data management and warehousing tool to automatically clean, segment, convert, and merge data tables based on segmentation strategies and field mapping relationships. This significantly shortens the data processing cycle and improves the efficiency of data query, processing, and warehousing.

[0042] The graph model is constructed based on the dependencies and access frequencies between fields, and highly correlated or frequently accessed fields are clustered into a sub-table. This optimizes the efficiency of subsequent data queries and reduces cross-table operations.

[0043] Data table segmentation: design a reasonable segmentation strategy based on the characteristics of the data table and business needs, and split the data table into multiple sub-tables.

[0044] This method is applicable to various data tables with multiple fields, achieving more efficient table management and warehousing processes, greatly reducing manual operations, improving import efficiency, and ensuring data consistency and integrity.

[0045] Create a unique ID for the sub-table to ensure the association between the sub-tables.

[0046] Appropriate indexing is established to address bottlenecks in the warehousing process, improving data warehousing efficiency and subsequent data query efficiency.

[0047] The specific implementation process of this method is as follows:

[0048] 1. Data preprocessing:

[0049] First, the data tables to be managed and stored are received, and the data is cleaned and preprocessed to remove duplicate, invalid or malformed data to ensure data quality and consistency.

[0050] 2. Build a graph model:

[0051] Based on business needs and the correlation between fields, we analyze the dependencies and access frequencies between fields and build a graph model to prepare for table sharding.

[0052] For example, in community housing data, there are often frequent related queries for names, person tags, residential addresses, and phone numbers. Using graph model algorithms, these related fields can be placed in a subtable to facilitate fast query and data integration.

[0053] The specific method is: each node in the graph model represents a field, and the edge represents the relationship between fields. You can connect two nodes based on the possible relationship between the fields. For example, there is an inevitable relationship between age and income, and there is a corresponding relationship between phone numbers and users. Figure 2 shown.

[0054] 3. Data table segmentation:

[0055] Aggregate highly correlated or frequently accessed fields into a subtable. This can optimize data query efficiency and reduce cross-table operations.

[0056] By analyzing the association and association frequency between fields through the graph model, we can determine which fields should be divided into the same sub-table. For example, according to the graph model shown in the figure above, the multi-field data table can be divided into four sub-tables: basic population information table, additional population information table, community table, and housing table.

[0057] The population basic table contains fields that are frequently associated with the name field, such as name, community name, age, person label, residential address, telephone number, ID number, and place of origin.

[0058] The population appendix table contains fields with low correlation frequency, such as marital status, education level, income, occupation, and political status;

[0059] The house table contains fields such as province, city, district, street, building number, room number, etc. where the specific house is located;

[0060] The community table contains the community and other community-related fields such as the corresponding community number.

[0061] 4. Establish field mapping relationship:

[0062] Establish field mapping relationships for each sub-table to ensure correct mapping of data between different tables; for example, through metadata management and data dictionary technology, define the data type, format, value range, etc. for each field to facilitate cross-table queries and data integration.

[0063] 5. Create an associated ID for the child table and set an index:

[0064] When importing data from a master table into its sub-tables, unique IDs must be established to link each sub-table. Linking IDs not only identify sub-tables but also establish relationships between them, enabling cross-table data join queries. You can use a distributed unique ID generator or create a UUID in MySQL to create a unique ID for each record in a sub-table. Based on the data characteristics and query requirements, add indexing strategies for appropriate fields to improve query efficiency.

[0065] In the above example, a distributed unique ID generator is used to create unique IDs for the three sub-tables: userid for the personnel table, houseid for the housing table, and communityid for the community table. Corresponding fields are then created in the personnel table to store houseid and communityid. When importing the population supplementary table, the userid in the personnel table is matched against the ID number to associate it with the personnel table. Furthermore, based on the data characteristics and query requirements, indexing strategies are added for relevant fields such as the ID number to improve query efficiency.

[0066] 6. Import the data into the sub-table and match the unique ID fields of other sub-tables for the sub-table:

[0067] Import the total table data into each subtable through SQL statements:

[0068] 6.1. First, obtain the house table data (distinct ensures the uniqueness of the data) and generate houseid.

[0069] Insert into housing table (province, city, district, street, building number, room number)

[0070] Select distinct province, city, district, street, building number, room number from community room total table;

[0071] 6.2, Similarly, import the community table data,

[0072] Insert into community table (community, community number...)

[0073] Select distinct community, community number... from community person and house total table;

[0074] 6.3. Generate houseid and communityid data in the corresponding subtables respectively.

[0075] 6.4, guide personnel basic table data,

[0076] Insert into personnel basic table (userid, name, community name, age, personnel label, residential address, phone number, ID number, place of origin)

[0077] SELECT UUID(), name, community name, age, person tag, residential address, phone number, ID number, and place of origin FROM the community housing data table;

[0078] 6.5. Create relevant indexes and use the update statement to match the houseid and communityid fields for the personnel basic table.

[0079] 6.6. Create index and import personnel additional table data.

[0080]

[0081] 7. Development of automated warehousing tools:

[0082] A set of multi-field data table automatic splitting tools was developed through Python packaging to realize automatic segmentation of data tables, automatic establishment of field mapping relationships, automatic generation of associated IDs, and automatic import of sub-table tasks.

[0083] When managing and storing multi-field data tables, the entire table is usually imported into the database using SQL. This process is difficult to guarantee, resulting in import efficiency, data consistency, and the complexity of subsequent data management. This method can achieve a more efficient multi-field data table management and storage process, significantly reducing manual operations, improving import efficiency, ensuring data consistency and integrity, and facilitating data management.

[0084] In addition, it provides a flexible storage mechanism that can be customized according to the structure and field characteristics of the data table. In general, it is designed to achieve automated management and storage of multi-field data tables, thereby improving the efficiency and accuracy of data processing and data management, and reducing operating costs.

[0085] An embodiment of the present invention also provides an automated processing system for multi-field data table management and warehousing, including a data preprocessing module, a graph model construction module, a data table segmentation module, a field mapping relationship establishment module, a sub-table association ID creation module, a data import sub-table module, and an automated warehousing tool generation module; the system realizes automated processing of multi-field data table management and warehousing through the methods described in the above embodiments.

[0086] 1. Data preprocessing module,

[0087] First, the data tables to be managed and stored are received, and the data is cleaned and preprocessed to remove duplicate, invalid or erroneous data to ensure data quality and consistency.

[0088] 2. Build a graph model module,

[0089] Based on business needs and the correlation between fields, analyze the dependencies and access frequencies between fields, and build a graph model to prepare for table sharding.

[0090] For example, in community housing data, names, person tags, residential addresses, and phone numbers are often frequently associated with queries. Using graph model algorithms, these associated fields can be placed in a subtable for faster querying and data integration.

[0091] The specific method is: each node in the graph model represents a field, and the edge represents the relationship between fields. We can connect two nodes based on the possible relationship between the fields. For example, there is an inevitable connection between age and income, and there is a corresponding relationship between phone numbers and users.

[0092] 3. Data table segmentation module,

[0093] Cluster highly correlated or frequently accessed fields into a subtable. This optimizes data query efficiency and reduces cross-table operations. Use graph models to analyze the relationships and frequency of associations between fields and determine which fields should be grouped into the same subtable.

[0094] 4. Establish a field mapping relationship module,

[0095] Establish field mappings for each sub-table to ensure correct data mapping between different tables. For example, through metadata management and data dictionary technology, define the data type, format, value range, etc. for each field to facilitate cross-table queries and data integration.

[0096] 5. Create an associated ID module for the subtable.

[0097] Create unique IDs for subtables to ensure inter-table correlation. When importing data from a master table into subtables, unique IDs are necessary to ensure inter-table correlation. Correlation IDs not only identify subtables but also establish relationships between them, enabling cross-table data correlation queries. You can use a distributed unique ID generator or create a UUID in MySQL to create a unique ID for each record in a subtable. Based on the data characteristics and query requirements, add indexing strategies for appropriate fields to improve query efficiency.

[0098] In the above example, a distributed unique ID generator is used to create unique IDs for the three sub-tables: userid for the personnel table, houseid for the housing table, and communityid for the community table. Corresponding fields are then created in the personnel table to store houseid and communityid. When importing the population supplementary table, the userid in the personnel table is matched against the ID number to associate it with the personnel table. Furthermore, based on the data characteristics and query requirements, indexing strategies are added for relevant fields such as the ID number to improve query efficiency.

[0099] 6. Data import into sub-table module,

[0100] Import data into the sub-tables, matching the sub-tables' unique ID fields with those of other sub-tables. Use SQL statements to import the master table data into each sub-table.

[0101] 7. Automatic warehousing tool generation module,

[0102] Combining the above modules, a set of multi-field data table automatic splitting tools was developed through Python packaging to realize tasks such as automatic segmentation of data tables, automatic establishment of field mapping relationships, automatic generation of associated IDs, and automatic import of sub-tables.

[0103] The embodiment of the present invention further provides an automated processing device for managing and storing multi-field data tables, comprising at least one memory and at least one processor;

[0104] The at least one memory is configured to store a machine-readable program;

[0105] The at least one processor is used to call the machine-readable program to implement the automated processing method for managing and warehousing the multi-field data table described in the above embodiment.

[0106] Embodiments of the present invention further provide a computer-readable medium having computer instructions stored thereon. When executed by a processor, the computer instructions cause the processor to execute the automated processing method for managing and storing multi-field data tables described in the above embodiments. Specifically, a system or device equipped with a storage medium can be provided. The storage medium stores software program code that implements the functions of any of the above embodiments, and causes a computer (or CPU or MPU) of the system or device to read and execute the program code stored in the storage medium.

[0107] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute part of the present invention.

[0108] Examples of storage media for providing program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, the program code can be downloaded from a server computer via a communication network.

[0109] In addition, it should be clear that the functions of any of the above embodiments can be achieved not only by executing the program code read by the computer, but also by enabling the operating system operating on the computer to complete part or all of the actual operations based on the instructions of the program code.

[0110] In addition, it can be understood that the program code read from the storage medium is written into the memory provided in the expansion board inserted into the computer or into the memory provided in the expansion unit connected to the computer, and then based on the instructions of the program code, the CPU installed on the expansion board or expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above embodiments.

[0111] The present invention has been shown and described in detail above through the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above multiple embodiments, those skilled in the art can know that the code review methods in the above different embodiments can be combined to obtain more embodiments of the present invention, and these embodiments are also within the scope of protection of the present invention.

Claims

1. An automated processing method for managing and storing multi-field data tables, characterized in that: Through data preprocessing, graph model construction, data table segmentation, field mapping relationship establishment, creation of association IDs for sub-tables, indexing and importing sub-tables, and the development of automated management process tools and innovative strategy optimization algorithms, we can achieve efficient management and rapid storage of complex data tables. The implementation process of this method is as follows: 1) Data preprocessing: First, the data tables to be managed and stored are received, and the data is cleaned and pre-processed to remove duplicate, invalid or malformed data; 2) Build a graph model: Based on business needs and the correlation between fields, we analyze the dependencies and access frequencies between fields and build a graph model to prepare for table sharding. 3) Data table segmentation: Aggregate highly relevant or frequently accessed fields into a subtable; Use the graph model to analyze the association and association frequency between fields and determine which fields should be grouped into the same sub-table. 4) Establish field mapping relationship: Establish field mapping relationships for each sub-table to ensure correct mapping of data between different tables; 5) Create an associated ID for the child table and set an index: The association ID is not only used to identify the child table, but also to build the association relationship between the child tables and implement cross-table data association query; 6) Import the data into the sub-table and match the unique ID fields of other sub-tables for the sub-table: Import the total table data into each sub-table through SQL statements; 7) Development of automated warehousing tools: Developed a tool for automatically splitting multi-field data tables to achieve automatic data table segmentation, automatic establishment of field mapping relationships, automatic generation of associated IDs, and automatic import of sub-table tasks; The data preprocessing is based on the segmentation strategy and field mapping relationship to achieve automatic cleaning, segmentation, conversion and merging operations on the data table; The graph model is constructed, in which each node represents a field, and an edge represents an association between fields. A line is formed between two nodes based on possible connections between the fields. The data table segmentation is to design a segmentation strategy according to the characteristics of the data table and business requirements, and split the data table into multiple sub-tables; the field mapping relationship is established for each sub-table, and the data type, format, and value range are defined for each field through metadata management and data dictionary technology.

2. The automated processing method for managing and storing multi-field data tables according to claim 1, characterized in that: The graph model is constructed based on the dependency relationship and access frequency between fields, and fields with high correlation or frequent access are aggregated into a subtable.

3. The automated processing method for managing and storing multi-field data tables according to claim 1, characterized in that: Create a unique ID for each sub-table to ensure the association between sub-tables; Use a distributed unique ID generator or create a UUID in MySQL to create a unique ID for each piece of data in the child table. Add index strategies for appropriate fields based on the data characteristics and query requirements.

4. An automated processing system for managing and storing multi-field data tables, characterized in that: It includes data preprocessing module, graph model building module, data table segmentation module, field mapping relationship establishment module, sub-table association ID creation module, data import sub-table module, and automatic warehousing tool generation module; The system realizes the automatic processing of multi-field data table management and storage through the method described in any one of claims 1 to 3.

5. An automated processing device for managing and storing multi-field data tables, characterized in that: comprising at least one memory and at least one processor; The at least one memory is configured to store a machine-readable program; The at least one processor is configured to call the machine-readable program to implement the method according to any one of claims 1 to 3.

6. A computer-readable medium, characterized in that The computer-readable medium stores computer instructions, which, when executed by a processor, enable the processor to implement the method according to any one of claims 1 to 3.

Images