Database-based Ultra-Wide Table Processing Method, Device, and Storage Medium

Abstract

A method, device, and storage medium for processing ultra-wide tables based on a database according to the present invention include the following steps: Select the maximum number of fields in the data analysis table according to the database single-table maximum number of fields standard; count the number of fields in the data analysis table; count the number of basic fields in the data analysis table; calculate the number of fields in each group of the data analysis table; calculate the number of groups of the data analysis table; redesign the table structure of the data analysis table based on the field grouping strategy; design the table structure of the field analysis definition table based on the field grouping strategy; perform a joint correlation analysis on the grouped data analysis table and the newly designed field analysis definition table to restore all the original data dimension fields of the data analysis table. Through the present invention, the database's processing ability for ultra-wide tables and its versatility in the field of data analysis are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data analysis, and particularly relates to a method, device and storage medium for processing ultra-wide tables based on a database. Background Art

[0002] With the development of Internet and big data technologies, data is growing explosively in all industries. People are paying more and more attention to data assets, and the demand for data analysis is becoming stronger. In the field of data analysis, it has evolved from analyzing small data at the beginning to analyzing big data. The analysis of big data is not only reflected longitudinally in the magnitude of data records, but also horizontally in the number of data features. Longitudinally, through larger data samples, more regular data trends can be analyzed. At the same time, horizontally, by introducing and expanding more data dimensions and adding more feature parameters, more accurate and comprehensive data analysis can be made from more perspectives.

[0003] For this reason, people have begun to introduce big data technologies such as Kudu, Greenplum, StarRocks, etc., a real-time OLAP database based on the MPP architecture, which can process tables with a large number of records and help quickly obtain analysis results. However, when dealing with tables with ultra-large data dimensions, such as tables with thousands of fields, these databases cannot handle them. For example, in Kudu, the maximum number of columns in a single table is limited to 300 columns.

[0004] In view of the above background, there is an urgent need for a method, system, device and medium for processing ultra-wide tables based on a database, which can improve the accuracy and comprehensiveness of user data analysis by processing data with ultra-large data dimensions, so as to better explore the logic and laws behind the data and provide a basis for user decision-making behaviors. Summary of the Invention

[0005] A method, device and storage medium for processing ultra-wide tables based on a database proposed by the present invention can at least solve one of the technical problems in the background art.

[0006] To achieve the above object, the present invention adopts the following technical solutions:

[0007] A method for processing an ultra-wide table based on a database includes:

[0008] S1. Determine the maximum number of fields: According to the maximum number of fields standard for a single table in the database, select the maximum number of fields of the physical table of the data analysis table in the database;

[0009] S2. Determine the number of fields in the data table: Count the number of all fields in the original data analysis table;

[0010] S3. Determine the number of basic fields in the data table: Count the number of fields of the basic data in the original data analysis table;

[0011] S4. Calculate the field grouping length: Based on the maximum number of fields determined in S1 and the number of basic fields determined in S3, calculate the number of fields in each group of the data analysis table;

[0012] S5. Calculate the number of field groups: Based on the number of fields in the data table determined in S2, the number of basic fields determined in S3, and the field grouping length determined in S4, calculate the number of groups of the data analysis table and perform grouping;

[0013] S6. Design the structure of the data analysis table: For the field groups determined in S5, based on the field grouping strategy, redesign the table structure of the data analysis table;

[0014] S7. Design the field grouping definition table: For the field groups determined in S5, based on the field grouping strategy, design the table structure of the field analysis definition table;

[0015] S8. Joint data analysis: Perform joint correlation analysis on the grouped data analysis table in S6 and the newly designed field analysis definition table in S7 to restore all the original data dimension fields of the data analysis table.

[0016] Furthermore, the S3. Determine the number of basic fields in the data table specifically includes:

[0017] Based on all the basic data dimension fields of the data analysis table, partition by the global primary key, select the corresponding partition fields, rather than partitioning by the grouping-related fields. For this reason, count the number of all basic data dimension fields.

[0018] Furthermore, S6. Design the structure of the data analysis table specifically includes:

[0019] Based on the field grouping strategy, redesign the table structure of the data analysis table. In the new table structure, the basic fields are the same as those in the original table, and the grouping fields are named according to the grouping sequence.

[0020] Furthermore, S7. Design the field grouping definition table specifically includes:

[0021] Based on the field grouping strategy, the grouping fields of the new data analysis table have no clear field meanings. Their specific field meanings and naming mappings are recorded through an additional field grouping definition table. Therefore, based on this strategy, design the table structure of the field grouping definition table, and at the same time store the sequence number, meaning, and field name information of the grouping fields in the table.

[0022] Furthermore, the S8. Joint data analysis specifically includes:

[0023] Perform a joint query on the grouped data analysis table and the newly designed field analysis definition table to restore all the original data dimension fields of the data analysis table;

[0024] During data analysis, perform analysis based on all the restored data dimension fields and grouped fields of the data analysis table.

[0025] Further, in step S4, calculate the field grouping length d = the maximum number of fields a - (the number of basic fields c + 1).

[0026] Further, in S5, calculate the number of field groups as: (the number of fields in the data table b - the number of basic fields c) / the field grouping length d.

[0027] On the other hand, the present invention also discloses a computer-readable storage medium storing a computer program, which when executed by a processor causes the processor to execute the steps of the above method.

[0028] On yet another hand, the present invention also discloses a computer device including a memory and a processor, where the memory stores a computer program, and when the computer program is executed by the processor, it causes the processor to execute the steps of the above method.

[0029] As can be seen from the above technical solutions, the method for processing ultra-wide tables based on a database according to the present invention improves the accuracy and comprehensiveness of user data analysis by processing data with ultra-large data dimensions, thereby better mining the logic and rules behind the data and providing a basis for user decision-making behaviors.

[0030] Specifically, by grouping all the fields of a data analysis table that exceeds the maximum number of fields supported by a single table in the database, it is possible to store the data of the ultra-wide table well and perform big data analysis on this basis, helping to more accurately and comprehensively mine the business value in big data.

[0031] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0032] Previously, the database could not store the data of a data analysis table that exceeded the maximum number of fields in a single table defined by the database well, and could only discard some fields or store them in sub-tables. If some fields are discarded, the accuracy of the data analysis results will decay exponentially (specifically different based on the number of discarded fields). If stored in sub-tables, it is difficult to restore all the original data dimensions of the data analysis table during data analysis, and at the same time, it will also consume a multiple of human resources (specifically, the increase in human resources is based on the number of sub-tables). BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 It is a flowchart of an embodiment of the present invention;

[0034] Figure 2 Schematic diagram of joint data analysis according to an embodiment of the present invention. Detailed implementation manners

[0035] To make the objectives, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are some but not all of the embodiments of the present invention.

[0036] As Figure 1 shown, the method for processing an ultra-wide table based on a database according to this embodiment includes the following steps:

[0037] S1. Determine the maximum number of fields: According to the standard of the maximum number of fields of 300 in a single Kudu table, select the maximum number of fields of the physical table of the data analysis table in the database;

[0038] S2. Determine the number of fields in the data table: Count the number of all fields in the original data analysis table;

[0039] S3. Determine the basic number of fields in the data table: Count the number of fields of basic data in the original data analysis table;

[0040] S4. Calculate the field grouping length: Based on the maximum number of fields determined in S1 and the basic number of fields determined in S3, calculate the number of fields in each group of the data analysis table;

[0041] S5. Calculate the number of field groups: Based on the number of fields in the data table determined in S2, the basic number of fields determined in S3, and the field grouping length determined in S4, calculate the number of groups of the data analysis table and perform grouping;

[0042] S6. Design the data analysis table structure: For the field groups determined in S5, based on the field grouping strategy, redesign the table structure of the data analysis table;

[0043] S7. Design the field grouping definition table: For the field groups determined in S5, based on the field grouping strategy, design the table structure of the field analysis definition table;

[0044] S8. Joint data analysis: Perform joint correlation analysis on the grouped data analysis table in S6 and the newly designed field analysis definition table in S7 to restore all the original data dimension fields of the data analysis table.

[0045] Specifically:

[0046] The determination of the maximum number of fields specifically includes:

[0047] When designing the database table structure, it is necessary to appropriately select the maximum number of fields for the physical table of the data analysis table in the database according to the standard of the maximum number of fields supported by a single table in the database.

[0048] The determination of the number of fields in the data table specifically includes:

[0049] For the original data analysis table, list all fields and count the number of all fields.

[0050] The determination of the basic number of fields in the data table specifically includes:

[0051] It is necessary to partition based on all the basic data dimension fields of the data analysis table according to the global primary key, select the corresponding partition fields, rather than partitioning according to the grouping-related fields. For this reason, count the number of all basic data dimension fields.

[0052] The calculation of the field grouping length specifically includes:

[0053] Since the number of fields in the data table is much larger than the maximum number of fields, it is necessary to group the fields in the data table. Excluding the basic fields in the data table, group the remaining other fields. Based on the aforementioned maximum number of fields, the number of fields in the data table, and the number of basic fields in the data table, calculate the field grouping length.

[0054] The calculation of the number of field groups specifically includes:

[0055] Based on the aforementioned maximum number of fields, the number of fields in the data table, the number of basic fields in the data table, and the field grouping length, calculate the number of field groups.

[0056] The design of the data analysis table structure specifically includes:

[0057] Based on the field grouping strategy, redesign the table structure of the data analysis table. In the new table structure, the basic fields are the same as those in the original table, and the grouped fields are named according to the grouping sequence.

[0058] The design of the field grouping definition table specifically includes:

[0059] Based on the field grouping strategy, the grouped fields in the new data analysis table do not have clear field meanings. Their specific field meanings and naming mappings need to be recorded through an additional field grouping definition table. Therefore, it is necessary to design the table structure of the field grouping definition table based on this strategy, and at the same time store information such as the serial number, meaning, and field name of the grouped fields in the table.

[0060] The combined data analysis specifically includes:

[0061] Since the data in the data table ultimately needs to be used for data analysis, in order to perform lossless analysis on the data in the original way, it is necessary to perform a joint query on the data analysis table after grouping and the newly designed field analysis definition table to restore all the original data dimension fields of the data analysis table.

[0062] When performing data analysis, it should be based on all the restored data dimension fields and grouping fields of the data analysis table.

[0063] The following combines Figure 2 Specifically described as follows:

[0064] S1. Determine the maximum number of fields a: According to the standard of the maximum number of fields per Kudu single table being 300, considering the extensibility of the basic fields of the table, for example, select 250 fields.

[0065] S2. Determine the number of fields b in the data table: Statistically summarize the number of fields in the data table for data analysis to obtain the number of fields in the data table, such as 2800.

[0066] S3. Determine the number of basic fields c in the data table: Statistically summarize the number of basic fields in the data table for data analysis to obtain the number of basic fields in the data table, such as 24. Adding the field grouping serial number or grouping identifier, it becomes 24 + 1 = 25.

[0067] S4. Calculate the field grouping length d: The maximum number of fields a - (the number of basic fields c + 1); that is, 250 - 25 = 225.

[0068] S5. Calculate the number of field groups: (the number of fields b in the data table - the number of basic fields c) / the field grouping length d; that is, (2800 - 25) / 225 = 12.3, rounded up to 13.

[0069] S6. Design the data analysis table structure TA: The basic field names are such as C1, C2, C3,..., C24, WD_GROUP, and the grouped field names are such as WD_C1, WD_C2, WD_C3,..., WD_C225, where WD_GROUP records the field grouping serial number or grouping identifier;

[0070] S7. Design the field grouping definition table TG: The fields are such as WD_GROUP, COL_INDEX, COL_NAME, where COL_INDEX is the serial number of the grouped field and COL_NAME is the corresponding field name or data dimension name.

[0071] S8. Joint data analysis: Such as Figure 2As shown in the figure, ① the WD_GROUP of the TA table is associated with the WD_GROUP of the TG table; ② reading the COL_NAME of the TG table in ascending order of COL_INDEX and replacing it into WD_CX of the TA table ("X" represents the field number of the grouping field); ③ performing a column-row conversion operation on all the grouping fields of the TA table to restore all the original data dimension fields.

[0072] In summary, in the embodiment of the present invention, by grouping all the fields of the data analysis table that exceeds the maximum number of fields supported by a single table in the database, it is possible to store the data of the ultra-wide table well and perform big data analysis on this basis, helping to more accurately and comprehensively explore the business value in big data.

[0073] On the other hand, the present invention also discloses a computer-readable storage medium storing a computer program, which when executed by a processor causes the processor to execute the steps of the above method.

[0074] On yet another hand, the present invention also discloses a computer device including a memory and a processor, the memory storing a computer program, which when executed by the processor causes the processor to execute the steps of the above method.

[0075] In another embodiment provided by the present application, there is also provided a computer program product containing instructions, which when running on a computer causes the computer to execute any of the above-described database-based ultra-wide table processing methods in the embodiments.

[0076] It can be understood that the system, device, and storage medium provided in the embodiments of the present invention correspond to the method provided in the embodiments of the present invention. The explanations, examples, and beneficial effects of the relevant content can refer to the corresponding parts in the above method.

[0077] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the processes or functions described in the embodiments of the present application are generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center by wire (such as coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (such as infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more integrated available media. The available medium can be a magnetic medium (such as a floppy disk, hard disk, magnetic tape), an optical medium (such as a DVD), or a semiconductor medium (such as a solid state disk (SSD)).

[0078] It should be noted that, in this document, relational terms such as first and second are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the term "comprising", "including" or any other variant thereof is intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "comprising a..." does not exclude the existence of additional identical elements in the process, method, article or device comprising the element.

[0079] Each embodiment in this specification is described in a related manner. The same or similar parts among the embodiments can be referred to each other, and the differences between each embodiment and other embodiments are emphasized. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the description of the method embodiment.

[0080] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements on some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for processing an ultra-wide table based on a database, characterized in that: The following steps are involved: S1. Determine the maximum number of fields: According to the maximum number of fields in a single database table, select the maximum number of fields in the physical table of the data analysis table in the database; S2. Determine the number of data table fields: count the number of all fields in the original data analysis table; S3. Determine the number of basic fields in the data table: count the number of fields of basic data in the original data analysis table; S4, calculate the length of field grouping: based on the maximum number of fields determined in S1 and the number of basic fields determined in S3, calculate the number of fields in each group of the data analysis table; S5. Calculate the number of field groups: based on the number of data table fields determined in S2, the number of basic fields determined in S3, and the field group length determined in S4, calculate the number of groupings of the data analysis table and perform grouping; S6. Design the data analysis table structure: group the fields determined in S5 and redesign the table structure of the data analysis table based on the field grouping strategy; S7, designing a field grouping definition table: grouping the fields determined in S5, and based on the field grouping strategy, designing the table structure of the field analysis definition table; S8, joint data analysis: perform joint correlation analysis on the grouped data analysis table in S6 and the newly designed field analysis definition table in S7 to restore all the original data dimension fields of the data analysis table; S3, determining the number of basic fields of the data table, specifically includes: Based on all the basic data dimension fields of the data analysis table, partition by global primary key and select corresponding partition fields instead of partitioning by grouping related fields. To this end, count the number of all basic data dimension fields. S6. Design the data analysis table structure, including: Based on the field grouping strategy, the table structure of the data analysis table is redesigned. In the new table structure, the basic fields are consistent with the original table fields, and the grouping fields are named according to the grouping sequence. S7. Design the field group definition table, including: Based on the field grouping strategy, the grouping fields of the new data analysis table do not have clear field meanings. Their specific field meanings and naming mappings are recorded through an additional field grouping definition table. Therefore, the table structure of the field grouping definition table is designed based on this strategy, and the serial number, meaning, and field name information of the grouping field are stored in the table.

2. The method for processing an ultra-wide table based on a database according to claim 1, characterized in that: The S8, joint data analysis, specifically includes: Perform a joint query on the grouped data analysis table and the newly designed field analysis definition table to restore all the original data dimension fields of the data analysis table; When analyzing data, analysis is performed based on all data dimension fields and grouping fields after the data analysis table is restored.

3. The method for processing an ultra-wide table based on a database according to claim 1, characterized in that: In step S4, the field group length d is calculated as follows: maximum field number a-(basic field number c+1).

4. The method for processing an ultra-wide table based on a database according to claim 1, characterized in that: The number of field groups calculated in S5 is: (number of data table fields b-number of basic fields c) / field group length d.

5. A computer-readable storage medium storing a computer program, wherein when the computer program is executed by a processor, the processor is caused to perform the steps of the method according to any one of claims 1 to 4.

6. A computer device comprising a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the processor executes the steps of the method according to any one of claims 1 to 4.

Images