Data skew pre-checking method for distributed database

Abstract

The invention discloses a method for pre-checking data skew of a distributed database. The method comprises the following steps: S1, grouping data according to different data columns; s2, taking a data column with the maximum number of repeated data after grouping as a sorting field; s3, acquiring the number of data nodes in the current distributed cluster; s4, adding two new data columns in the data table; and S5, distributing the data to each data node. The method is simpler and clearer to operate, small in workload, free of a series of follow-up influences and capable of fundamentally solving the problem of data skew.

Description

Technical Field

[0001] The invention belongs to the technical field of data processing, and in particular is a distributed database data tilt pre-check method. Background Art

[0002] Data skew is a common problem in distributed computing and big data processing, and can occur in distributed cluster environments. It means that during data processing, some data items or data groups are abnormally concentrated in certain data nodes, while other data nodes process relatively less data. The current way to handle data distribution is to use distribution columns to distribute a type of data together. This requires using discrete data as distribution columns as much as possible. If the data in the distribution column is not discrete, data skew will occur.

[0003] At present, the solutions to data skew are implemented only after the data skew problem occurs. This brings many adverse effects: low disk space utilization; some nodes may exhaust resources due to processing too much data, while other nodes are relatively idle; low CPU and memory utilization, slow task execution; due to the excessive amount of data processed by some nodes, the overall task execution time is prolonged, resulting in system instability; long-term data skew may cause system crashes or memory overflows and other problems.

[0004] The existing solution requires re-finding distribution columns, building tables, importing data, and determining whether data is skewed over and over again. This not only takes a lot of time and manpower, but also causes system instability and affects normal business operations. And if all fields in the original table are used as distribution columns, the data is still skewed, and the problem of data skew cannot be solved. Summary of the invention

[0005] In view of the above problems, the present invention is proposed to provide a distributed database data skew pre-check method that overcomes the above problems or at least partially solves the above problems.

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

[0007] A distributed database data skew pre-check method, the method comprising the following steps:

[0008] S1. Group the data according to different data columns;

[0009] S2, taking the data column with the largest number of repeated data after grouping as the sorting field;

[0010] S3. Get the number of data nodes in the current distributed cluster;

[0011] S4. Add two new data columns to the data table;

[0012] S5. Allocate data to each data node.

[0013] Optionally, in step S1, the GROUP BY of SQL is used to perform grouping, and statistical calculations are performed on each group of data to find the value with the most duplicates.

[0014] Optionally, step S2 includes:

[0015] The field with the most duplicate values ​​is the first sorting field.

[0016] In addition to the first sorting field, find the field with the largest amount of repeated values ​​among the remaining fields and use it as the second sorting field.

[0017] Optionally, if the fields with the largest amount of repeated values ​​in the remaining fields are the same, the field with the second largest amount of repeated values ​​is selected for comparison; if the fields with the second largest amount of repeated values ​​in the remaining fields are also the same, the judgment is made based on the space occupied by the fields as a whole, and the fields occupying a larger space are sorted higher.

[0018] Optionally, step S4 includes:

[0019] Add a new column to the data table, the value of which is a positive integer without duplication;

[0020] Add a new column to the data table, the value of which is the number of data nodes with numeric unique keys.

[0021] Optionally, the number of data nodes is a uniformly distributed value between 0 and 9.

[0022] In summary, due to the adoption of the above technical solution, the beneficial effects of the present invention are:

[0023] The present invention adds a numeric unique key (ID) column and a modulo (DN) column of the numeric unique key divided by the number of data nodes at the beginning of data table design before the data skew problem occurs, so that the data in the data table can be evenly distributed, thereby fundamentally eliminating the occurrence of the data skew problem. In this way, the operation only requires adding two columns to the data table, which is not only simpler and clearer to operate, but also has a small workload and will not have a series of subsequent impacts, and can fundamentally solve the data skew problem. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Figure 1 A schematic flow chart of a distributed database data skew pre-check method provided in an embodiment of the present application.

[0025] Figure 2 This is a schematic diagram of the complete data distribution. DETAILED DESCRIPTION

[0026] In order to make the purpose, 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 in conjunction with the drawings in the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0027] See also Figure 1 This embodiment provides a distributed database data skew pre-check method, including the following steps:

[0028] S1. Group the data according to different data columns.

[0029] You can use SQL's GROUP BY to group data and perform statistical calculations on each group of data to find the value with the most duplicates.

[0030] S2. Use the data column with the largest number of repeated data after grouping as the sorting field, and sort it using the ORDER BY clause.

[0031] Step S2 includes:

[0032] The field with the most duplicate values ​​is the first sorting field.

[0033] In addition to the first sorting field, find the field with the largest amount of repeated values ​​among the remaining fields and use it as the second sorting field.

[0034] If the fields with the largest amount of repeated values ​​in the remaining fields are the same, the field with the second largest amount of repeated values ​​will be selected for comparison; if the fields with the second largest amount of repeated values ​​in the remaining fields are also the same, the comparison will be made based on the space occupied by the fields as a whole, and the fields that occupy a larger space will be ranked higher.

[0035] S3. Get the number of data nodes in the current distributed cluster.

[0036] S4. Add two new data columns to the data table.

[0037] Step S4 includes:

[0038] Add a new column to the data table, whose values ​​are non-repeating positive integers; this way, the data table will have a column of evenly distributed values.

[0039] A new column is added to the data table, the value of which is the number of data nodes of the numeric unique key, and the number of data nodes is a uniformly distributed value between 0 and 9.

[0040] S5. Allocate data to each data node. Allocating data nodes means allocating data to each data node according to the value of the column modulo the number of data nodes.

[0041] In this embodiment, before the data skew problem occurs, at the beginning of the data table design, by adding a numeric unique key (ID) column and a numeric unique key divided by the number of data nodes modulo (DN) column, the data in the data table is evenly distributed, thereby fundamentally eliminating the data skew problem. In this way, the operation only requires adding two columns to the data table, which is not only simpler and clearer to operate, but also has a small workload and will not cause a series of subsequent impacts, and can fundamentally solve the data skew problem.

[0042] The above contents are further detailed descriptions of the present invention in combination with specific preferred embodiments, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For ordinary technicians in the technical field to which the present invention belongs, several simple deductions or substitutions can be made without departing from the concept of the present invention, which should be regarded as falling within the protection scope of the present invention.

Claims

1. A distributed database data skew pre-check method, characterized in that: The method comprises the following steps: S1. Group the data according to different data columns; S2, taking the data column with the largest number of repeated data after grouping as the sorting field; S3. Get the number of data nodes in the current distributed cluster; S4. Add two new data columns to the data table; S5. Allocate data to each data node.

2. A distributed database data skew pre-check method as claimed in claim 1, characterized in that: In step S1, the SQL GROUP BY is used to perform grouping, and statistical calculations are performed on each group of data to find the value with the most duplicates.

3. A distributed database data skew pre-check method as claimed in claim 1, characterized in that: Step S2 includes: The field with the most duplicate values ​​is the first sorting field. In addition to the first sorting field, find the field with the largest amount of repeated values ​​among the remaining fields and use it as the second sorting field.

4. A distributed database data skew pre-check method as claimed in claim 3, characterized in that: If the fields with the largest amount of repeated values ​​in the remaining fields are the same, the field with the second largest amount of repeated values ​​will be selected for comparison; if the fields with the second largest amount of repeated values ​​in the remaining fields are also the same, the comparison will be made based on the space occupied by the fields as a whole, and the fields that occupy a larger space will be ranked higher.

5. A distributed database data skew pre-check method as claimed in claim 1, characterized in that: Step S4 includes: Add a new column to the data table, the value of which is a positive integer without duplication; Add a new column to the data table, the value of which is the number of data nodes with numeric unique keys.

6. A distributed database data skew pre-check method as claimed in claim 5, characterized in that: The number of data nodes is a uniformly distributed value between 0 and 9.

Images