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

Abstract

The present invention discloses an automated processing method and system for multi-field data table management and warehousing, which belongs to the field of big data and database management technology. Through data preprocessing, graph model construction, data table segmentation, field mapping relationship establishment, creation of associated ID for subtable, index setting and import of subtable, and writing of automated management process tool, innovative strategy optimization algorithm, efficient management and rapid warehousing of complex data tables are realized. The present invention can realize more efficient multi-field data table management and warehousing process, greatly reduce manual operation, improve import efficiency, ensure data consistency and integrity, and provide convenience for data management; flexible warehousing mechanism can be customized according to the structure and field characteristics of the data table.

Description

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

Technical Field

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 warehousing multi-field data tables.

Background Art

With the advent of the big data era, the scale of data has increased exponentially, making the structure and content of data and data tables increasingly complex. Therefore, the management and storage of multi-field data tables have become particularly critical. Traditional data table management and storage methods rely on manual operations or simple automated scripts when processing large-scale, multi-field data tables. They are inefficient and difficult to meet the needs of real-time data analysis and timely response to business. Therefore, how to efficiently manage and store such data tables and ensure data security has become an important issue that needs to be solved urgently.

Summary of the invention

The technical task of the present invention is to address the above deficiencies and provide an automated processing method and system for multi-field data table management and warehousing, which realizes efficient management and rapid warehousing of complex data tables through innovative strategies and optimization algorithms.

The technical solution adopted by the present invention to solve the technical problem is:

An automated processing method for multi-field data table management and warehousing, which implements data preprocessing, graph model construction, data table segmentation, field mapping relationship establishment, creation of associated IDs for sub-tables, index setting and import of sub-tables, and writing of automated management process tools, innovative strategy optimization algorithms, to achieve efficient management of complex data tables and rapid warehousing.

Furthermore, 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 preprocessed to remove duplicate, invalid or erroneous data to ensure data quality and consistency;

(2) Constructing a graph model:

Based on business requirements and the correlation between fields, 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 divided 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 subtable and set an index:

The association ID is not only used to identify the sub-table, but also to build the association relationship between sub-tables to realize cross-table data association query;

(6) Import the data into the sub-table and match the unique ID field of other sub-tables to the sub-table:

Import the data of the master table into each sub-table through SQL statements;

(7) Development of automated warehousing tools:

A set of multi-field data table automatic splitting tools is 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.

Furthermore, the data preprocessing can realize automatic cleaning, segmentation, conversion and merging of data tables based on segmentation strategies and field mapping relationships, greatly shortening the data processing cycle and improving the efficiency of data query, processing and storage.

Furthermore, the graph model is constructed according to the dependency and access frequency between fields, and the fields with high correlation or frequent access are aggregated into a sub-table; the efficiency of subsequent data query is optimized and cross-table operations are reduced.

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

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;

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, so as to facilitate cross-table query and data integration.

Furthermore, a unique ID is established for each sub-table to ensure the association between the sub-tables;

Create a unique ID for each piece of data in the subtable through a distributed unique ID generator or create UUID in MySQL; and add index strategies for appropriate fields based on the characteristics of the data and query requirements to improve query efficiency.

The present invention also claims protection for an automated processing system for multi-field data table management and warehousing, including a data preprocessing module, a graph model building module, a data table segmentation module, a field mapping relationship establishment module, a sub-table creation association ID module, a data import sub-table module, and an automated warehousing tool generation module;

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

The present invention also claims protection for an automated processing device for managing and warehousing a multi-field data table, comprising at least one memory and at least one processor;

The at least one memory is used to store a machine-readable program;

The at least one processor is used to call the machine-readable program to implement the above method.

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

Compared with the prior art, the method and system for automatically processing and storing multi-field data tables of the present invention have the following beneficial effects:

The multi-field data table management and warehousing process method provided by the present invention realizes efficient management and rapid warehousing of complex data tables through innovative strategies and optimization algorithms, greatly improves the efficiency and accuracy of data processing and data management, and reduces operating costs. At the same time, the method of the present invention can be applied to various data tables with multiple fields, realize more efficient table management and warehousing processes, greatly reduce manual operations, improve import efficiency, ensure data consistency and integrity, and provide convenience for data management, and has broad application prospects.

BRIEF DESCRIPTION OF THE DRAWINGS

1 is a flowchart of an automated processing method for managing and warehousing a multi-field data table provided by an embodiment of the present invention;

FIG. 2 is an example diagram of a graph model constructed according to an embodiment of the present invention.

DETAILED DESCRIPTION

The present invention will be further described below in conjunction with specific embodiments.

The embodiment of the present invention provides an automated processing method for multi-field data table management and warehousing, which implements efficient management and rapid warehousing of complex data tables through data preprocessing, graph model construction, data table segmentation, establishment of field mapping relationships, creation of associated IDs for subtables, setting of indexes to import subtables, and writing of automated management process tools and innovative strategy optimization algorithms.

The data preprocessing, through the automated multi-field data management and warehousing tool, can realize automatic cleaning, segmentation, conversion, merging and other operations on the data table based on segmentation strategy and field mapping relationship, greatly shortening the data processing cycle and improving the efficiency of data query, processing and warehousing.

The graph model is constructed based on the dependency and access frequency between fields, and the fields with high correlation or frequent access are aggregated into a sub-table to optimize the efficiency of later data query and reduce cross-table operations.

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.

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.

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

Appropriate indexing is done to address bottleneck issues during the warehousing process, improving data warehousing efficiency and subsequent data query efficiency.

The specific 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 preprocessed to remove duplicate, invalid or erroneous data to ensure the quality and consistency of the data.

2. Build a graph model:

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

For example, in community housing data, there are often frequent related queries for names, personnel labels, residential addresses, telephone numbers, etc. According to the graph model algorithm, these related fields can be placed in a subtable to facilitate fast query and data integration.

The specific method is: each node in the graph model represents a field, and the edge represents the relationship between the 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, etc. See Figure 2.

3. Data table segmentation:

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

The graph model is used to analyze the association and association frequency between fields, and 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: population basic information table, population additional information table, community table, and housing table.

The population basic table contains: name, community name, age, person label, residential address, telephone number, ID number, place of origin and other fields that are frequently associated with the name field;

The population supplementary table contains fields with low correlation frequency, such as marital status, education, income, occupation, and political status;

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

The community table contains the community and other community related fields such as the corresponding community number.

4. Establish field mapping relationship:

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 query and data integration.

5. Create an associated ID for the subtable and set an index:

When importing data from the master table into sub-tables separately, a unique ID must be created to associate each sub-table with each other. The association ID is not only used to identify the sub-table, but also to build the association relationship between sub-tables to implement cross-table data association query. You can create a unique ID for each piece of data in the sub-table through the distributed unique ID generator or create UUID in MySQL. And according to the characteristics of the data and query requirements, add index strategies for appropriate fields to improve query efficiency.

In the above example, a distributed unique ID generator is required to create unique IDs for the three sub-tables, namely: userid of the personnel basic table, houseid of the house table, and communityId of the community table; then corresponding fields for storing houseid and communityId are created in the personnel basic table, and when the population additional table is imported, the userid of the personnel basic table is matched according to the ID card number to associate it with the personnel basic table. At the same time, according to the characteristics of the data and query requirements, index strategies are added for related fields such as the ID card number to improve query efficiency.

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 subtable through SQL statements:

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

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

Select distinct province, city, district, street, building number, room number from community room list;

6.2. Similarly, export the community table data.

Insert into community table (community, community number...)

Select distinct community, community number...from community room total table;

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

6.4. Data of the basic table of personnel,

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

SELECT UUID(), name, community name, age, person tag, residential address, phone number, ID number, native place FROM community person and house total data table;

6.5. Create relevant indexes and use the update statement to match the houseid and communityid fields for the personnel base table.

6.6. Create index and import personnel additional table data.

Insert into additional table of personnel (marital status, education, income, occupation, political status, userid)

SELECT marital status, education, income, occupation, political status, (

SELECT

userid

FROM

Personnel basic table b

WHERE

a. ID number = b. ID number

)AS userid, place of origin FROM community housing data table a.

7. Development of automated warehousing tools:

A set of multi-field data table automatic splitting tools is 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.

In the process of managing and storing multi-field data tables, the entire data table is usually imported into the database through SQL. In this process, the import efficiency, data consistency and the complexity of subsequent data management are difficult to guarantee. This method can achieve a more efficient multi-field data table management and storage process, greatly reduce manual operations, improve import efficiency, ensure data consistency and integrity, and facilitate data management.

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 to achieve automatic management and storage of multi-field data tables, thereby improving the efficiency and accuracy of data processing and data management and reducing operating costs.

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 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; the system realizes automated processing of multi-field data table management and warehousing through the methods described in the above embodiments.

1. Data preprocessing module,

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.

2. Build the graph model module.

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.

For example, in community housing data, there are usually frequent related queries for names, personnel labels, residential addresses, telephone numbers, etc. According to the graph model algorithm, these related fields can be placed in a subtable to facilitate fast query and integration of data.

The specific method is: each node in the graph model represents a field, and the edge represents the relationship between the fields. We 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.

3. Data table segmentation module,

Aggregate highly correlated or frequently accessed fields into a subtable. This can optimize data query efficiency and reduce cross-table operations. Use the graph model to analyze the association and association frequency between fields and determine which fields should be divided into the same subtable.

4. Establish a field mapping relationship module.

Establish field mapping relationships for each sub-table to ensure the 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 query and data integration.

5. Create an associated ID module for the subtable.

Create a unique ID for the subtable to ensure the association between each subtable. When importing the data of the main table into the subtables separately, a unique ID must be created to associate each subtable with each other. The association ID is not only used to identify the subtable, but also to build the association relationship between subtables to achieve cross-table data association query. You can create a unique ID for each piece of data in the subtable through the distributed unique ID generator or create UUID in MySQL. And according to the characteristics of the data and query requirements, add index strategies for appropriate fields to improve query efficiency.

In the above example, a distributed unique ID generator is required to create unique IDs for the three sub-tables, namely: userid of the personnel basic table, houseid of the house table, and communityId of the community table; then corresponding fields for storing houseid and communityId are created in the personnel basic table, and when the population additional table is imported, the userid of the personnel basic table is matched according to the ID card number to associate it with the personnel basic table. At the same time, according to the characteristics of the data and query requirements, index strategies are added for related fields such as the ID card number to improve query efficiency.

6. Import data into sub-table module,

Import data into the sub-table, and match the sub-table with the unique ID field of other sub-tables. Use SQL statements to import the master table data into each sub-table.

7. Automatic warehousing tool generation module,

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.

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

The at least one memory is used to store a machine-readable program;

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.

The embodiment of the present invention further provides a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the processor executes the automatic processing method for managing and storing multi-field data tables described in the above embodiment. Specifically, a system or device equipped with a storage medium can be provided, on which software program codes for implementing the functions of any of the above embodiments are stored, and a computer (or CPU or MPU) of the system or device can read and execute the program code stored in the storage medium.

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 a part of the present invention.

The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer by a communication network.

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

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

The present invention is 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 protection scope of the present invention.

Claims (10)

1. An automatic processing method for managing and warehousing multi-field data table is characterized in that a graph model is constructed through data preprocessing, the data table is divided, a field mapping relation is established, an associated ID is created for a sub-table, a cable is arranged and guided into the sub-table, and the sub-table is written into an automatic management flow tool, so that a strategy optimization algorithm is innovated, and efficient management and quick warehousing of complex data tables are realized.

2. The automated processing method for managing and warehousing multi-field data table according to claim 1, wherein the implementation process of the method is as follows:

1) Data preprocessing:

Firstly, receiving a data table to be treated and put in storage, and cleaning and preprocessing the data to remove repeated, invalid or wrong data;

2) Constructing a graph model:

based on the service requirement and the relevance among the fields, analyzing the dependency relationship and the access frequency among the fields, and constructing a graph model to prepare for sub-tables;

3) Data table segmentation:

aggregating fields with high correlation or frequent access into a sub-table;

analyzing the association relation and association frequency among the fields through the graph model, and determining which fields should be divided into the same sub-table;

4) Establishing a field mapping relation:

establishing a field mapping relation for each sub-table to ensure correct mapping of data among different tables;

5) Creating an association ID for a sub-table, setting an index:

the association ID is not only used for identifying the sub-tables, but also used for constructing association relations among the sub-tables, so as to realize cross-table data association inquiry;

6) Importing data into the sub-table, and matching the sub-table with unique ID fields of other sub-tables:

importing the total table data into each sub-table through the sql statement;

7) Automatic warehouse tool development:

Developing an automatic splitting tool for the multi-field data table, realizing automatic splitting of the data table, automatic establishment of field mapping relation, automatic generation of association ID and automatic importing of sub-table tasks.

3. The automated processing method for managing and warehousing multi-field data tables according to claim 1 or 2, wherein the data preprocessing realizes the operations of automatically cleaning, dividing, converting and merging the data tables based on a dividing strategy and a field mapping relation.

4. The automated processing method for managing and warehousing a multi-field data table according to claim 1 or 2, wherein the graph model is constructed according to the dependency relationship between fields and the access frequency, and the fields with high correlation or frequent access are gathered into a sub-table.

5. The automated multi-field data table management and warehousing method according to claim 4, wherein the graph model is constructed, each node represents a field, the edges represent the association between the fields, and the connection between the two nodes is based on the possible association between the fields.

6. The automated processing method for managing and warehousing a multi-field data table according to claim 1 or 2, wherein the data table is divided, and a division strategy is designed according to characteristics and service requirements of the data table, so that the data table is divided into a plurality of sub-tables;

And establishing a field mapping relation for each sub-table, and defining a data type, a format and a value range for each field through metadata management and data dictionary technology.

7. An automated processing method for managing and warehousing multi-field data tables according to claim 1 or 2, wherein a unique id is established for the sub-tables to ensure the relevance between the sub-tables;

creating a unique ID for each piece of data in the sub-table by a distributed unique ID generator or creating a UUID in mysql; and adding an index strategy for the proper field according to the characteristics of the data and the query requirement.

8. An automatic processing system for managing and warehousing a multi-field data table is characterized by comprising a data preprocessing module, a graph model building module, a data table dividing module, a field mapping relation building module, a sub-table creation association ID module, a data importing sub-table module and an automatic warehousing tool generating module;

The system realizes the automation treatment of multi-field data table management and warehousing by the method of any one of claims 1 to 7.

9. An automatic processing device for managing and warehousing multi-field data tables is characterized by comprising at least one memory and at least one processor;

The at least one memory for storing a machine readable program;

the at least one processor being configured to invoke the machine readable program to implement the method of any of claims 1 to 7.

10. A computer readable medium having stored thereon computer instructions which, when executed by a processor, cause the processor to implement the method of any of claims 1 to 7.