A method for displaying data in partitioned tables of big data

Abstract

The present invention discloses a method for displaying data in a table of big data. When realizing fast display, the method does not cache data, but only caches the data's point and segmentation information. The data location is queried through the point and segmentation information. While the data can be correctly queried and displayed, the method does not cause cached data redundancy or a large cache volume, thereby achieving system optimization.

Description

Technical Field

[0001] The present invention relates to the field of big data technology, and in particular to a method for displaying data in separate tables of big data. Background Art

[0002] For quality platforms that require large amounts of data analysis, large or even extremely large data volumes are common. However, when a table's data volume reaches tens or even hundreds of millions, two problems arise. First, slow query speeds lead to very slow page display, even with database indexes. Second, and more serious, the sheer volume of data can overwhelm a single table, causing the system to crash. The former can be addressed with caching, but table overload cannot be solved with standard methods. Currently, there are two common approaches to addressing table issues. One is to directly purchase a more expensive big data processing system to circumvent these challenges. However, this approach is both prohibitively expensive for many companies and requires a high learning curve for big data processing. The second approach involves splitting the database into multiple identical tables, creating multiple tables with suffixes named according to creation time or company. Data is then separated by creation time or company, stored in separate tables. This solves the data storage problem and utilizes caching to address slow data processing. However, after the tables are divided, the data connection issue becomes very important. For example, when a page needs to combine the last three data items of the first table, the second to fifth data items of the second table, and the third to fifth data items of the fourth table to display 10 data items to the user according to the query conditions, it becomes difficult to calculate the location of the required data in real time, calculate the amount of data and the location of the data when the remaining data is insufficient, and switch and query tables in real time. There is currently no corresponding solution in the technical field. Summary of the Invention

[0003] The purpose of the present invention is to provide a method for displaying data in a sharded table of big data in order to solve the above problems. The present invention adopts the principle of data point marking and a caching mechanism to solve the problem of correctly displaying data after large amounts of data are stored in sharded tables, thereby solving the problem of how to correctly query the data required by each table according to the query conditions and combine and display them when multiple data sources appear after sharding.

[0004] The present invention achieves the above-mentioned purpose through the following technical solutions:

[0005] A method for displaying data in a table of big data includes the following steps:

[0006] First, to address the issue of large data volumes overwhelming a single table, data can be stored in separate tables. To do this, the tables must be categorized by type. For example, if Company A, Company B, and Company C have the largest data volumes, and the combined data volumes of the other companies equal the data volume of one company, categorize by company name and create four tables, storing the data for Company A, Company B, Company C, and the other companies, respectively. If the data volumes for each subsidiary are small, but the total monthly data volume for all companies is large, categorize by month, create separate monthly data tables, and import the data sequentially.

[0007] Secondly, we will implement point marking of data. That is, after the data enters the table, we will mark the segmentation conditions (such as creation time, data type, and company to which it belongs) and location of the data. We will operate on each table in turn, segment the data in the table according to the segmentation conditions, and name these data segments divided into multiple segments. Then, we will traverse each segment of each table, get the points of the first and last data of the segment in turn, and combine them together according to the table name, segment data name, and the first and last data points to form multiple cached data. All cached data will be stored in the cache in turn.

[0008] Finally, when encountering a query condition combination (company set, creation start time, creation end time, data type set), the first step is to perform a full point search of the data in the cache according to the query condition combination. For example, the company set plus the creation start time plus the data type is used as the starting condition, and the company set plus the creation end time plus the data type is used as the ending condition. The cache is then searched for the segments from the starting point to the ending point. The number of segments may be 0, 1, or multiple, depending on the query condition combination. If the number is 0, an empty data string is directly returned to the front-end display. If the number of segments is greater than 0, all points of the required data are obtained according to the paging requirements. That is, the cached data in all the queried segments are reassembled according to the page size (pageSize). The total number of point data in the segment is divided by the value of pageSize and then rounded up to get the total number of pages. A page object with the total number of pages is created, and pageSize positions are set in each page object. The point data is sorted into each page object in order, and then all point data of the corresponding page is obtained according to the page number (pageNumber). The point distribution of the actual data required this time in each table is calculated. Finally, data is obtained from each table in the database table according to the point, and the centralized data is merged and displayed.

[0009] The beneficial effects of the present invention are:

[0010] The present invention provides a method for displaying large amounts of data in a table. This method, while not caching data for rapid display, only caches the data's location and segmentation information. The location and segmentation information are then used to query the data. This method allows for accurate data query and display without causing redundant cached data or excessive cache size, thus optimizing the system. This method allows for accurate display of large amounts of data without caching the data itself, achieving low-cost storage of large amounts of data while ensuring accurate data display with a small cache size. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the practical drawings required in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0012] Figure 1 It is a flow chart of the present invention. DETAILED DESCRIPTION

[0013] To make the objectives, technical solutions, and advantages of the present invention more apparent, the technical solutions of the present invention will be described in detail below. Obviously, the embodiments described are only some of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, all other implementations obtained by those of ordinary skill in the art without inventive effort are within the scope of protection of the present invention.

[0014] In any embodiment, Figure 1 As shown, a method for displaying data in a table of big data of the present invention includes:

[0015] 1. Store data in separate tables based on the time period or company to which the data belongs. (For example, if the total data volume is 40 million, with the largest data volume from companies A, B, and C, and the total data volume from other companies equaling the data volume of one company, then divide the data into four tables, one for A, one for B, one for C, and one for other companies, and name them data_company_a, data_company_b, data_company_c, and data_company_other, respectively.)

[0016] Each table is classified according to another data classification method (for example, if the table is divided by company, then the data in the table is divided by time, and vice versa). The starting point of each segment (time period in this example) data is recorded (such as a-20210101-1-13243, a-20210102-13244-27643, b-20210101-1-13, b-20210102-14-22, other-20210101-1-124, other-20210102-125-227, etc., a represents the table of company A, the middle time is the time of the segment, the first subsequent number is the starting ID position of the segment data in the current table, and the last number is the ending ID position of the segment data in the current table), and the data point is stored in the cache;

[0017] When querying data:

[0018] First, preliminarily locate the tables where the data resides based on the query conditions. (For example, if the query conditions include querying the data of Company B and Company D, traverse the table names and obtain the data in data_company_b. Since there is no table ending with Company D, the data of Company D is in data_company_other).

[0019] Locate the data in which segments according to the query conditions: (for example, if the query condition is data from 20210101 to 20210102, based on the result of the previous step, obtain the point data of 20210101 and 20210102 starting with b and other from the cache and arrange them in order according to the query conditions);

[0020] According to the pageNum and pageSize of the query conditions, calculate the points of the data to be displayed (for example, pageNum is 3, pageSize is 10, that is, take the 10 data of the third page, and you need to pass (3-1)*10=20 data in front. First, the point data starting with b is b-20210101-1-13, b-20210102-14-22. From the point analysis, it is calculated from b-20210101-1-13 that the amount of data here is not enough to reach the 20 that need to be filtered, and then the next point is calculated. According to the analysis of b-20210102-14-22, the analysis result is that the data exceeds the 20 data that need to be filtered (22-20>0), but is less than the required 10 data (22-20<10). That is, starting from the 21st position and ending at the 22nd position, a total of 2 data can be obtained, and 8 data need to be supplemented. Based on the obtained point data starting with "other", more than 8 of the 24 points of "other-20210101-1-1" meet the conditions (124-0>8), that is, it is necessary to start from the 1st position and end at the 8th position);

[0021] Based on the points calculated in the previous step, get data from each table by location at the same time; (get data with ids 21 and 22 from the data_company_b table, and get data with ids 1 to 8 from the data_company_other table);

[0022] Merge data into a unified format and display it to users in the format they need. Without caching the data itself, it can correctly display large amounts of data, thus achieving low-cost storage of big data and correct display of data with a small cache.

[0023] The above is only a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of changes or replacements within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims. It should also be noted that the various specific technical features described in the above specific embodiments can be combined in any suitable manner unless there is any contradiction. In order to avoid unnecessary repetition, the present invention will no longer describe various possible combinations separately. In addition, the various different embodiments of the present invention can also be arbitrarily combined. As long as they do not violate the ideas of the present invention, they should also be regarded as the contents disclosed by the present invention.

Claims

1. A method for displaying data in a table of big data, characterized in that: The following steps are involved: Step 1: Store the data in separate tables and classify them according to the table type. Create data tables for each table and import the data in sequence. Step 2: Mark the data points based on the segmentation conditions and positions. Perform operations on each table in turn, segment the data in the table according to the segmentation conditions, and name the data segments. Then, traverse each segment of each table and get the first and last data points of the segment in turn. Then, the table name, segment data name, and first and last data points are combined to form multiple cache data, and all cache data are stored in the cache in sequence; Step 3: When a certain query condition combination is encountered, perform a full-point search of the data in the cache according to the query condition combination requirements, using the company set plus the creation time, start time, and data type as the starting condition, and the company set plus the creation time, end time, and data type as the ending condition; When querying data, the data is first initially located in the table based on the query conditions, and then located in the segment based on the query conditions. The segments from the start point to the end point are obtained in the cache. The number of segments may be 0, 1, or more. If it is 0, directly return an empty data string to the front end for display; If the number of segments is greater than 0, the data points to be displayed are calculated based on the number of pages and page size of the query conditions; Based on the points calculated in the previous step, data is obtained from each table according to the location, and then the data is merged and displayed.

2. A method for displaying data in a table of large data according to claim 1, characterized in that: In step 1, separate tables are used to store data classified by company name or by month, and data tables are created separately to import data in sequence.

3. A method for displaying data in a table of large data according to claim 1, characterized in that: In step 2, the query condition combination includes a company set, a creation start time, a creation end time, and a data type set.

Images