A data evaluation method and device, electronic equipment and storage medium

Abstract

The application discloses a data evaluation method and device, electronic equipment and storage medium, and relates to the technical field of distributed databases. The application responds to a data skew query task of a table to be evaluated, obtains data files of the table to be evaluated on multiple segmented instances, simultaneously obtains physical file information of the segmented instances through an external webpage table, and performs correlation analysis on the data files corresponding to the multiple segmented instances in combination with storage information of a system table, so that the distribution of actual effective data can be more truly reflected, the accuracy of skew index calculation is improved, and then the skew degree of the table to be evaluated is determined according to the accurate skew index, thereby solving the problem of insufficient accuracy of data skew degree evaluation in the related art.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of distributed database, and particularly relates to a data evaluation method and device, electronic equipment and storage medium. BACKGROUND

[0002] Greenplum is a distributed parallel database using a shared-nothing massively parallel processing (MPP) architecture, which realizes efficient parallel processing of massive data by dispersing data according to fields to multiple segment instances. Its storage formats include heap tables and append optimized (AO) tables, which rely on the uniform distribution of data among segment instances to ensure query performance. However, incorrect distribution key selection or data operations can cause data skew, i.e., some segment instances have significantly more data than others, which can cause query response delays, overflow file errors, and insufficient memory. Therefore, the uniformity of the data distribution needs to be verified after the data is stored to ensure that the number of rows and storage consumption of each node are roughly balanced.

[0003] In related technologies, Greenplum evaluates the degree of data skew based on the coefficient of variation of the number of rows, or measures the degree of data skew through the system idle percentage during table scanning. However, Greenplum has the following problems: the skew calculation for heap tables relies on the total number of statistics commands, which is inefficient for large amounts of heap tables, occupies resources, and affects other tasks; the calculation for append optimized tables is implemented by reading the number of rows in the system table, which does not consider table expansion, resulting in inaccurate skew rate. SUMMARY

[0004] The present application provides a data evaluation method and device, electronic equipment and storage medium to at least solve the problem of insufficient accuracy in related technologies.

[0005] The present application provides a data evaluation method, which includes: in response to a data skew query task for a to-be-evaluated table, obtaining data files of the to-be-evaluated table on multiple segment instances; wherein the to-be-evaluated table is a heap table or an append optimized table, and each segment instance stores part of the data files of the to-be-evaluated table; obtaining an information table corresponding to the to-be-evaluated table, the information table including a system table and an external web table; wherein the system table is used to record storage information of the to-be-evaluated table, and the external web table is used to record physical file information of the multiple segment instances; performing correlation analysis on the data files corresponding to the multiple segment instances through the information table to calculate a skew index; wherein the skew index is used to represent the data distribution of the to-be-evaluated table on the multiple segment instances; and determining the degree of data skew of the to-be-evaluated table according to the skew index.

[0006] The application further provides a data evaluation device, comprising:

[0007] A data file acquisition module is configured to acquire data files of a to-be-evaluated table on a plurality of segmented instances in response to a data skew query task of the to-be-evaluated table, wherein the to-be-evaluated table is a heap table or an append-optimized table, and each segmented instance stores part of data files of the to-be-evaluated table.

[0008] An information table acquisition module is configured to acquire an information table corresponding to the to-be-evaluated table, wherein the information table comprises a system table and an external webpage table, the system table is configured to record storage information of the to-be-evaluated table, and the external webpage table is configured to record physical file information of the plurality of segmented instances.

[0009] An association analysis module is configured to perform association analysis on the data files corresponding to the plurality of segmented instances through the information table, and calculate a skew index, wherein the skew index is used to represent data distribution of the to-be-evaluated table on the plurality of segmented instances.

[0010] A processing module is configured to determine a data skew degree of the to-be-evaluated table according to the skew index.

[0011] The application further provides an electronic device, comprising a memory configured to store a computer program and a processor configured to execute the computer program to implement the steps of the data evaluation method.

[0012] The application further provides a computer readable storage medium, wherein the computer readable storage medium stores a computer program, and the computer program is executed by a processor to implement the steps of the data evaluation method.

[0013] The application further provides a computer program product, comprising a computer program, and the computer program is executed by a processor to implement the steps of the data evaluation method.

[0014] The application acquires data files of a to-be-evaluated table on a plurality of segmented instances in response to a data skew query task of the to-be-evaluated table, simultaneously acquires physical file information of the segmented instances through an external webpage table, performs association analysis on the data files corresponding to the plurality of segmented instances in combination with storage information of a system table, can more truly reflect distribution of actual effective data, improves accuracy of skew index calculation, and then determines a data skew degree of the to-be-evaluated table according to the accurate skew index, thereby solving the problem of insufficient evaluation accuracy of the data skew degree in related technologies. BRIEF DESCRIPTION OF DRAWINGS

[0015] In order to more clearly illustrate the embodiments of the present application, the following will briefly introduce the drawings needed to be used in the embodiments. Obviously, the drawings described below are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative effort on the basis of these drawings.

[0016] Figure 1 The schematic diagram of a specific hardware architecture relied on by the execution of a data evaluation method provided by an embodiment of the present application;

[0017] Figure 2 The flowchart of a data evaluation method provided by an embodiment of the present application Figure 1 ;

[0018] Figure 3 The structural schematic diagram of a system table provided by an embodiment of the present application;

[0019] Figure 4 The flowchart of a data evaluation method provided by an embodiment of the present application Figure 2 ;

[0020] Figure 5 The structural schematic diagram of a data evaluation device provided by an embodiment of the present application;

[0021] Figure 6 The structural schematic diagram of an electronic device provided by an embodiment of the present application. DETAILED DESCRIPTION

[0022] The technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative effort fall within the protection scope of the present application.

[0023] It should be noted that, in the description of the present application, the terms “include”, “contain” or any other variants thereof are intended to cover non-exclusive inclusion, so that the process, method, article or device including a series of elements not only includes those elements, but also includes other elements not explicitly listed or inherent to such process, method, article or device. The terms “first”, “second” and the like in the present application are used to distinguish similar objects, and are not used to describe a specific order or sequence.

[0024] In order to more clearly illustrate the embodiments of the present application, the following will briefly introduce the technical terms needed to be used in the embodiments:

[0025] Greenplum is a distributed parallel database with shared-nothing MPP architecture, developed based on open source relational database (PostgreSQL). It is a shared-nothing, MPP architecture relational distributed parallel database, also known as MPP database, with built-in parallel storage, parallel communication, parallel computing and parallel optimization functions, and has powerful and efficient data storage and processing capabilities.

[0026] A cluster consists of a management node (Master Server) and multiple data nodes (Segment Server). The Master is the entry of the database, mainly responsible for receiving client connection requests, generating query plans for SQL statements, and distributing query plans to all Segment nodes for execution. The Master node does not store any user data, but only stores the metadata of the database, such as system tables. Multiple database instances can run on each data node, called Segment instances (after high availability is enabled, there is a master and a backup, the normal master instance provides services, and the backup instance synchronizes the master instance data). Each instance can be considered as an independent PostgreSQL database, which stores a part of user data and participates in query execution. The more Segment instances on a node, the more resources are used for query execution on this Segment node, and the faster the query execution speed. By distributing data to different data nodes according to the hash calculation of a certain field, on the one hand, the data volume processed by a single node is greatly reduced, and on the other hand, the query can be parallelized to fully utilize the advantages of the cluster and greatly improve the data query performance.

[0027] Structured Query Language (SQL) is used to access data and query, update and manage relational database systems, such as SELECT and INSERT commands.

[0028] Tuple is the data stored in the database, and a tuple is equivalent to a row.

[0029] Greenplum has two storage formats: HEAP table and Append Optimized (AO) table. HEAP table supports efficient update and delete operations. AO table writes data in an append manner, has extremely high write performance, and is usually used to store fact data in the database, and also supports data update and delete operations. Since Greenplum database relies on uniform distribution of data between Segments, in order to obtain better query performance, user data should be evenly distributed on each Segment instance, i.e. each instance stores 1 / n data, regardless of the storage format.

[0030] Distributed storage: For example, on a cluster of 100 nodes, each node only saves a part of the total data. The distributed nature of MPP databases makes their storage capacity and computing power exceed the upper limit of a single machine, and becomes the sum of multiple Segments.

[0031] Data skew refers to the uneven distribution of data among parallel processing instances, where a single segment instance has significantly more data than other segment instances. This leads to a large amount of data being concentrated on individual computing segment instances, resulting in processing speeds that are much lower than the average computing speed. This becomes a bottleneck for the entire cluster processing, affecting overall computing performance. Data skew can affect scan (read) performance and all other query execution operations, such as JOIN and GROUP operations.

[0032] gp_dist_random is a system function specific to Greenplum databases, which provides a way to directly access data on each Segment in a distributed table. It allows queries to directly access data on all Segments, returning results containing data rows from each Segment, with the same query executed independently on each Segment.

[0033] In Greenplum, table-level data skew is the core reason for low query performance and memory shortage. Therefore, it is crucial to verify the uniform distribution of data on each Segment instance after initial data import and incremental data import, ensuring that the number of rows in each Segment is roughly equal and storage consumption is balanced. Greenplum provides two views, gp_skew_coefficients and gp_skew_idle_fractions, for diagnosing uneven table data distribution. However, these two solutions have significant technical limitations in practical applications, making it difficult to meet the efficient and accurate skew detection needs.

[0034] However, the above solutions have obvious defects: 1. Low efficiency: For HEAP tables, the row count needs to be calculated through the total number command COUNT(*) full table scan, which takes a long time and consumes a lot of resources, affecting other tasks; 2. Inaccurate: For AO tables, the row count relies on system table records, without considering table expansion caused by frequent updates / deletions, dead tuples are incorrectly counted, skew rate calculation is distorted; 3. Single dimension: Only the row count distribution is counted, ignoring the storage size difference, unable to identify scenarios where "row count is balanced but data volume is skewed"; 4. Inefficient query: Single table skew query needs to traverse the entire table and filter, invalid calculation amplifies resource consumption, high concurrency easily leads to system overload.

[0035] To solve the above-mentioned part or all technical problems, the embodiment of the present application provides a data evaluation method, which obtains data file information through information tables (system table and external webpage table), does not need full table scanning, directly performs correlation analysis based on storage information and physical file information, improves the calculation efficiency, reduces the occupation of system resources and the influence on other tasks, obtains the physical file information of segmented instances through the external webpage table, performs correlation analysis in combination with the storage information of the system table, can more truly reflect the distribution of actual effective data, and improves the accuracy of the calculation of the skew index, directly performs correlation analysis on the data files of multiple segmented instances based on the information table, does not need to traverse the full table, reduces invalid calculation, reduces resource consumption, and avoids the system overload problem under high concurrency.

[0036] In some embodiments provided by the present application, the physical size of the data file is analyzed through the information table, the calculated skew index not only contains the row number related dimension, but also can reflect the distribution difference of the storage size, can identify the scene of balanced row number but skewed data volume, and enriches the evaluation dimension.

[0037] In order to enable the person skilled in the art to better understand the present application, the present application will be further described in detail below in combination with the drawings and specific embodiments.

[0038] In combination with the specific application environment architecture or specific hardware architecture on which the execution of the data evaluation method depends, the specific application environment architecture or specific hardware architecture is described here.

[0039] As shown in Figure 1 The schematic diagram of the specific hardware architecture on which the execution of the data evaluation method depends.

[0040] The hardware architecture includes a system table construction module, an external webpage table implementation module, a correlation analysis module and a data skew evaluation module.

[0041] The system table construction module internally stores a system table, the system table is created on all segmented instances when the database is initialized, and contains fields such as segment instance identifier (segment_id), table object identifier (reloid), data file name (relfilenode), hidden tuple number (hidden_tupcount), total tuple number (total_tupcount), inflated size (inflated_size) and the like.

[0042] The module for implementing external web tables defines an external web table based on preset commands. For example, an external web table is created using the SQL syntax CREATE EXTERNAL WEB TABLE. The command is specified in the EXECUTE clause, and all segment instances corresponding to the table to be evaluated are defined to run the command in parallel to obtain information about all data files stored in multiple segment instances.

[0043] The association analysis module is used to estimate table distribution. It involves two types of skew statistics: tuple count (table row count) skew statistics and table data size skew statistics.

[0044] The data skew assessment module includes built-in reference values ​​for different skew indicators, such as standard deviation and coefficient of dispersion. It outputs a result indicating whether the data in the table is evenly distributed.

[0045] The embodiments of the present application provide a data evaluation method, and the method is described in detail in conjunction with the execution process of the data evaluation method.

[0046] like Figure 2 As shown, Figure 2 A schematic diagram of a data evaluation method provided in an embodiment of the present application Figure 1 The method includes the following steps S201 to S204:

[0047] S201 : In response to a data skew query task on a table to be evaluated, data files of the table to be evaluated on multiple segment instances are obtained.

[0048] The table to be evaluated is either a heap table or an append-optimized table. HEAP tables support dynamic updates, and data files may contain hidden tuples after deletion or update. AO tables are written in an append-only manner, resulting in a more compact file structure, but frequent updates may also lead to bloat. The metadata of the table to be evaluated is stored in the management node. The data evaluation method provided in this application is the same for both HEAP and AO tables, simplifying the code logic and facilitating maintenance.

[0049] Each segment instance stores part of the data files of the table to be evaluated. All data files of the table to be evaluated are distributed to multiple segment instances. The data file stores the physical data of the table to be evaluated. Whether it is a HEAP table or an AO table, its data is split into multiple Segment instances, and each instance only processes part of the data stored in it. The data file name format is <fileno>As 12345, when there are multiple data files for the same table to be evaluated, the data file name format is <fileno> .1, <fileno>.2 etc.

[0050] In the present application, the user first connects the management node (MPP Master) and establishes a session with the database. The database waits for the user to send a data skew query request for the table to be evaluated. When the user initiates the data skew query request, the database receives the data skew query request and submits a data skew query task.

[0051] In some embodiments, in response to the data skew query task of the table to be evaluated, it is first determined whether the table to be evaluated is a single table, multiple tables, or a full table, according to the number of tables to be evaluated. The number of tables to be evaluated is different, and the specific operation is different when executing the subsequent step S203.

[0052] S202, obtaining an information table corresponding to the table to be evaluated.

[0053] The information table includes a system table and an external webpage table. The system table corresponding to the table to be evaluated is used to record the storage information of the table to be evaluated. The external webpage table corresponding to the table to be evaluated is used to record the physical file information of the plurality of segment instances.

[0054] In some embodiments, when the database is initialized, a system table can be created on the segment instance (Segment), which can be named pg_file_details_t. After the database is successfully initialized, the newly added system table has also been successfully created. When the data file of the table on the segment instance exists the operation of adding, deleting or modifying, the system table is updated synchronously.

[0055] The system table (such as pg_file_details_t) is a structured table maintained internally by the database, which is specially used to manage the storage information of the database table. Its fields include segment instance identifier (segment_id), table object identifier (reloid), data file name (relfilenode), hidden tuple number (hidden_tupcount), total tuple number (total_tupcount), and inflated size (inflated_size).

[0056] For example, the pg_file_details_t table is as follows: Figure 3 As shown, these are all designed around the storage attributes of the database table, and the data is automatically generated and maintained by the database system, reflecting the storage state of the database table. Among them, segment_id identifies the specific segment instance ID, indicating which segment instance the data file belongs to. reloid indicates the database table (such as the table to be evaluated), which is used to establish the correspondence between the system table and the database table. relfilenode is the data file name on the segment instance, which identifies the data storage file of the database table at the physical level, and is used to track the physical storage carrier of the table data. hidden_tupcount is used to record the number of hidden tuples (rows) in the data file due to deletion or update operations. These hidden tuples exist physically but are invalid, reflecting the historical modification residues of the table data. total_tupcount is the total number of tuples (rows) of the data file, including the number of valid tuples and the number of hidden tuples, which is a complete statistics of the number of rows of the physical level data file. inflated_size represents the inflated data size, which embodies the invalid space existing in the data file due to update, deletion and other operations, and can be used to analyze the storage efficiency of the table.

[0057] In the above embodiment, the system table pg_file_details_t records storage information such as total_tupcount, without the need to perform time-consuming full table scanning, and only needs to query the system table to quickly obtain row number related data, greatly improving the efficiency and reducing resource occupation and the impact on other tasks. pg_file_details_t contains hidden_tupcount and inflated_size, which can clearly distinguish between valid tuples and invalid hidden tuples caused by updates and deletions, avoiding the interference of invalid hidden tuples on the statistical results and improving the accuracy of the skew rate calculation. The system table contains storage size related fields such as inflated_size, so that when evaluating data skew, not only can it be based on row number, but also can be analyzed in combination with storage size dimension, which can effectively identify scenarios where row numbers are balanced but data volumes are skewed, enriching the evaluation dimension. In addition, since the system table directly stores key information such as segment_id and reloid, when querying single table skew, it is not necessary to traverse the entire table, but only needs to quickly associate and locate the corresponding data file information through these identifiers, reducing invalid calculations, reducing resource consumption, and avoiding system overload under high concurrency.

[0058] In some embodiments, distributed queries are used to retrieve system tables from multiple segment instances. For example, gp_dist_random('pg_file_details_t') can be used to query system tables from multiple segment instances. Information such as row counts can be retrieved directly from the system tables of each segment instance without requiring a full table scan, significantly reducing data processing time and usage of system resources.

[0059] In some embodiments, an external web table is created using a pre-set command. For example, an external web table is created using the SQL syntax CREATEEXTERNAL WEB TABLE. The command is specified in the EXECUTE clause and executed in parallel across all segment instances corresponding to the table to be evaluated, thereby obtaining information about all data files stored in the multiple segment instances. This is achieved, for example, through the combined use of shell commands such as ls -l, grep, and awk. This avoids the inefficient operation of scanning data line by line, further improving efficiency.

[0060] In the above embodiment, the external web page table can reflect the actual storage of valid data by directly reading the physical information of the data file on the segmented instance, which is convenient for combining with information such as the number of hidden tuples recorded in the system table. At the same time, the data distribution is evaluated from two dimensions: the number of logical tuples and the physical storage attributes, which can eliminate the interference of dead tuples, make up for the defect of the existing solution that relies on system table statistics and is easily affected by table expansion, and improve the accuracy of tilt calculation.

[0061] S203: Perform correlation analysis on data files corresponding to multiple segment instances through the information table, and calculate a tilt index.

[0062] The skew index is used to characterize the data distribution of the table to be evaluated across multiple segment instances. Association analysis includes statistics on the skew of the number of table tuples and / or the skew of the amount of table data.

[0063] In some embodiments, step S203 includes: performing skew statistics on data files corresponding to multiple segment instances based on the number of valid tuples in the system table, and calculating a skew index; and / or performing skew statistics on data files corresponding to multiple segments based on the expanded size of the system table and the physical size of the external web page table, and calculating a skew index.

[0064] In the process of performing skew statistics on the data files corresponding to multiple segmented instances based on the number of valid tuples in the system table, the total number of tuples and the number of hidden tuples recorded in the system table are first obtained; then, a difference operation is performed on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples; then, the valid tuple numbers of multiple system tables are added together to obtain the total number of valid tuples in the table to be evaluated; and then, the skew index is calculated based on the total number of valid tuples in the table to be evaluated.

[0065] Specifically, for the corresponding multiple system tables of the multiple segment instances, the total number of tuples and the number of hidden tuples of each system table record are obtained. Then for each system table, the difference between the total number of tuples and the number of hidden tuples is calculated as the valid number of tuples corresponding to this system table, which corresponds to a segment instance, that is, the partial valid number of tuples of the table to be evaluated. Further, the valid numbers of tuples of all system tables corresponding to the table to be evaluated are summed to obtain the total valid number of tuples of the table to be evaluated.

[0066] Further, according to the total valid number of tuples of the table to be evaluated, the skew index is calculated. Optionally, the average number of tuples of the segment instances is first calculated according to the total valid number of tuples of the table to be evaluated and the number of instances of the multiple segment instances, and then the standard deviation is calculated according to the average number of tuples, and then the average number of tuples and the standard deviation are ratioed to obtain the skew index.

[0067] Illustratively, the skew index is the coefficient of variation (CV). The coefficient of variation can be calculated according to the following formula (1):

[0068] (1)

[0069] In formula (1), n is the number of instances of the segment instances, xi is the valid number of tuples corresponding to the i-th segment instance, is the valid number of tuples corresponding to the n segment instances, which is also the valid number of tuples of the n system tables, and is actually the total valid number of tuples of the table to be evaluated. is the average number of tuples of the segment instances.

[0070] The above embodiment uses the total number of tuples and the number of hidden tuples of the system table record to obtain the valid number of tuples through difference operation, without full table scanning, greatly reducing the data processing time and resource occupation, and improving the calculation efficiency; the valid number of tuples is obtained by subtracting the number of hidden tuples from the total number of tuples, which accurately excludes the invalid hidden tuples generated by deletion or update, avoids the interference of dead tuples on the statistical result, makes the skew index calculation more in line with the actual effective data distribution, and improves the accuracy; not only the skew index is calculated based on the valid number of tuples, but also the statistical data of the system table and the physical size of the external web page table are combined, considering the data storage size dimension, enriching the evaluation dimension; based on the existing data of the system table and the external web page table, the full table is not traversed and filtered, reducing invalid calculation, reducing resource consumption, and alleviating the risk of system overload under high concurrency.

[0071] In some embodiments, the skew statistics of the data files corresponding to the plurality of segment instances are calculated according to the inflated size of the system table and the physical size of the external webpage table, including: in the case that the segment instance identifier recorded by the system table is the same as the segment instance identifier recorded by the external webpage table, and the data file name recorded by the system table is the same as the data file name recorded by the external webpage table, obtaining the inflated size recorded by the system table and the physical size recorded by the external webpage table; performing difference operation on the physical size and the inflated size to obtain the actual data amount of the table to be evaluated; and calculating the skew index according to the actual data amount of the table to be evaluated.

[0072] Specifically, first, the segment instance identifier and the data file name recorded by the system table, and the segment instance identifier and the data file name recorded by the external webpage table are obtained. Then, it is compared whether the segment instance identifier and the data file name recorded by the two tables are the same. If yes, it indicates that the current system table and the current external webpage table correspond to the same data file of the same segment instance, and then the inflated size recorded by the system table and the physical size recorded by the external webpage table are further obtained. The difference between the physical size and the inflated size is calculated as the actual data amount of the data file. The above-mentioned association operation and the calculation operation of the actual data amount are performed on the system table and the external webpage table of each segment instance, and the actual data amount of all data files distributed in the plurality of segment instances of the table to be evaluated is obtained. Then, the skew index is calculated according to the actual data amount of the table to be evaluated.

[0073] In the process of calculating the skew index according to the actual data amount of the table to be evaluated, first, the average data amount is calculated according to the actual data amount of the table to be evaluated and the number of data files, and then the difference between the total data amount of the table to be evaluated and the average data amount is calculated to obtain the data amount difference, and then the ratio of the data amount difference and the average data amount is calculated to obtain the skew index.

[0074] Optionally, the skew index is the dispersion coefficient. The dispersion coefficient is calculated according to the following formula (2):

[0075] (2)

[0076] In formula (2), is the maximum data amount of the table to be evaluated, and is the data amount of the maximum data file distributed on the i-th segment instance of the table to be evaluated. is the actual data amount of the table to be evaluated, and n is the number of data files.

[0077] The embodiments described above associate the same segment instance identifier and data file name records in the system table and the external webpage table, use the existing inflated size and physical size data to perform difference operation to obtain the actual data amount, do not need to perform full table scanning, reduce resource consumption and calculation time consumption, and improve efficiency; the actual data amount is obtained by subtracting the inflated size from the physical size, accurately excludes invalid inflated space generated by update and deletion operations, avoids interference of invalid data on statistical results, makes the skew index more consistent with storage distribution of actual effective data, and improves accuracy; the skew statistics are focused on the actual data amount of data storage, supplement storage size dimension evaluation, and break through the limitation of single row number statistics; based on the existing records of the system table and the external webpage table, the correlation calculation is performed without traversing and filtering the full table, reduces invalid calculation, and reduces resource consumption.

[0078] In some embodiments, as shown in FIG. 3, when step S203 is performed, the following steps S301-S309 are included. Figure 4

[0079] S301, obtain the total tuple number and the hidden tuple number of the system table record.

[0080] Obtain the total tuple number total_tupcount and the hidden tuple number hidden_tupcount of the system table pg_file_details_t record.

[0081] S302, perform difference operation on the total tuple number and the hidden tuple number to obtain the effective tuple number.

[0082] Effective tuple number = total tuple number total_tupcount-hidden tuple number hidden_tupcount. The effective tuple number of the to-be-evaluated table on multiple segment instances is calculated. One segment instance corresponds to one system table, and one system table corresponds to one effective tuple number.

[0083] S303, perform sum operation on the effective tuple numbers of multiple system tables to obtain the total effective tuple number of the to-be-evaluated table.

[0084] All data of the to-be-evaluated table is distributed on multiple segment instances, each segment instance corresponds to one system table, after the effective tuple numbers of all system tables are calculated, the sum is accumulated to obtain the total effective tuple number of the to-be-evaluated table.

[0085] S304, in the case where the total effective tuple number is greater than or equal to the tuple number threshold, according to the total effective tuple number of the to-be-evaluated table, the first skew index is calculated.

[0086] ​Wherein, the tuple number threshold value threshold is a pre-set valid tuple number of the database table. The tuple number threshold value can be the segment instance number of the current database, or other values, which are not specifically limited in the present application.

[0087] The total number of valid tuples of the table to be evaluated is compared with the tuple number threshold value. If the total number of valid tuples is greater than or equal to the tuple number threshold value, a first skew index, such as a coefficient of variation, is calculated according to the total number of valid tuples, and the subsequent steps are continued.

[0088] If the total number of valid tuples is less than the tuple number threshold value, it means that there can be some segment instances without data files in the plurality of segment instances, and in this case, it is meaningless to evaluate whether the table is skewed, and the process is ended.

[0089] S305, Obtain the segment instance identifier and data file name recorded in the system table and the external web page table.

[0090] Obtain segment_id1 and data file name 1 recorded in the system table pg_file_details_t, and segment instance identifier segment_id2 and data file name 2 recorded in the external web page table.

[0091] S306, In the case that the segment instance identifier recorded in the system table is the same as that recorded in the external web page table, and the data file name recorded in the system table is the same as that recorded in the external web page table, obtain the inflated size recorded in the system table, and the physical size recorded in the external web page table.

[0092] Compare whether the segment instance identifier and the data file name are the same. If yes, obtain the inflated size inflated_size recorded in the system table pg_file_details_t, and the physical size recorded in the external web page table.

[0093] S307, Perform difference operation on the physical size and the inflated size to obtain the actual data amount of the table to be evaluated.

[0094] The actual data amount of the table to be evaluated = physical size - inflated size.

[0095] S308, Calculate a second skew index according to the actual data amount of the table to be evaluated.

[0096] The second skew index can be a discrete coefficient.

[0097] S309, Calculate a skew index according to the first skew index and the second skew index.

[0098] The first skew index and the second skew index can be weighted and summed to obtain the final skew index.

[0099] The total number of tuples and the number of hidden tuples recorded in the system table pg_file_details_t are called in the above embodiment, and the number of effective tuples can be obtained by simple difference operation, without full table scanning, greatly reducing the resource consumption and time cost of data processing, and improving the calculation efficiency; the number of effective tuples is obtained by subtracting the number of hidden tuples from the total number of tuples, which accurately excludes the invalid hidden tuples generated by deletion or update, and at the same time, the actual data amount is calculated by subtracting the expansion size from the physical size, avoiding the interference of invalid expansion space on the statistical result, making the tilt index more in line with the actual effective data distribution, and improving the accuracy; the first tilt index is calculated based on the number of effective tuples, and the second tilt index is calculated in combination with the actual data amount, and the comprehensive tilt index is finally obtained by weighted summation, taking into account the logical row number and the physical storage dimension; based on the existing data of the system table and the external web page table, step-by-step calculation is performed without traversing the full table and filtering, and meaningless calculation is avoided by tuple number threshold judgment, reducing invalid operations and resource consumption.

[0100] In some embodiments, in the case of a single or multiple database tables to be evaluated, indicating that a data tilt query task needs to be performed is that the table to be evaluated is a single table or multiple tables, and the table to be evaluated is filtered according to the table name of the table to be evaluated for data tilt analysis to calculate the tilt index. For example, filtering is performed by using a WHERE clause or a JOIN association condition.

[0101] In the case of a full-amount table to be evaluated, step S203 or any optional embodiment thereof is directly performed to obtain the tilt index.

[0102] The above embodiment directly filters out the table to be evaluated according to the table name, whether it is a single table or multiple tables, without traversing the full-amount data and then screening, reducing the processing process of irrelevant data, reducing the resource consumption caused by invalid calculation, and improving the query efficiency.

[0103] S204, determining the data tilt degree of the table to be evaluated according to the tilt index.

[0104] In some embodiments, determining the data tilt degree of the table to be evaluated according to the tilt index includes: if the tilt index is greater than a first tilt threshold and less than or equal to a second tilt threshold, determining a first data tilt degree; wherein the first tilt threshold is less than the second tilt threshold; if the tilt index is greater than the second tilt threshold and less than or equal to a third tilt threshold, determining a second data tilt degree; wherein the second tilt threshold is less than the third tilt threshold; the second data tilt degree is greater than the first data tilt degree; if the tilt index is greater than the third tilt threshold, determining a third data tilt degree; wherein the third data tilt degree is greater than the second data tilt degree.

[0105] Exemplary, exemplary, assuming that the skew indicator is the coefficient of variation, the corresponding skew degree is shown in Table 1. Optionally, for moderate skew, slightly adjust the data distribution; for very high skew, emergency treatment is required.

[0106] Table 1

[0107]

[0108] The above embodiment compares the skew indicator with the first, second and third skew thresholds, and clearly divides the first, second and third data skew degrees, such as moderate, higher and very high, so that the skew evaluation is upgraded from simple numerical statistics to degree judgment with clear gradient. Through quantitative degree grading, optimization strategies can be targeted, and optimization decision lag or resource mismatch caused by single index and ambiguous interpretation can be avoided.

[0109] In summary, the embodiment of the present application provides a data evaluation method, which directly uses the existing information of the system table and the external web page table to replace the full table scan to calculate the number of rows, avoiding the processing of a large amount of invalid data, greatly reducing resource consumption and time cost, and even facing large data tables, the skew diagnosis can be efficiently completed, and the influence on other tasks is reduced. The difference between the total tuple number and the hidden tuple number is used to obtain the effective tuple number, and the difference between the physical size and the inflated size is used to obtain the actual data amount, which accurately excludes interference factors such as dead tuples and invalid inflated space, so that the skew indicator calculation is more in line with the actual effective data distribution, and the statistical distortion problem caused by table inflation is solved. Taking into account the effective tuple number (row number dimension) and the actual data amount (storage size dimension), the scene of "balanced row number but skewed data amount" can be identified, the dimension of skew evaluation is enriched, and the diagnosis result is more comprehensive.

[0110] Through the description of the above embodiments, those skilled in the art can clearly understand that the method according to the above embodiments can be realized by means of software and the necessary general hardware platform, of course, it can also be realized by hardware, but in many cases the former is a better embodiment.

[0111] As Figure 5 shown, the embodiment of the present application also provides a data evaluation device, which comprises:

[0112] The data file acquisition module 501 is configured to acquire data files of the to-be-evaluated table on a plurality of segmented instances in response to a data skew query task of the to-be-evaluated table; wherein the to-be-evaluated table is a heap table or an append-optimized table, and each segmented instance stores part of the data files of the to-be-evaluated table.

[0113] The information table obtaining module 502 is configured to obtain an information table corresponding to the to-be-evaluated table, the information table comprising a system table and an external webpage table; wherein the system table is configured to record storage information of the to-be-evaluated table, and the external webpage table is configured to record physical file information of the plurality of segmented instances;

[0114] The correlation analysis module 503 is configured to perform correlation analysis on the data files corresponding to the plurality of segmented instances through the information table, and calculate a skew index; wherein the skew index is configured to represent data distribution of the to-be-evaluated table on the plurality of segmented instances.

[0115] The processing module 504 is configured to determine a data skew degree of the to-be-evaluated table according to the skew index.

[0116] As an optional implementation provided by the embodiments of the present application, the correlation analysis module 503 is specifically configured to: perform skew statistics on the data files corresponding to the plurality of segmented instances according to the number of valid tuples recorded by the system table, and calculate the skew index; and / or perform skew statistics on the data files corresponding to the plurality of segmented instances according to the inflated size recorded by the system table and the physical size recorded by the external webpage table, and calculate the skew index.

[0117] As an optional implementation provided by the embodiments of the present application, the number of system tables is a plurality.

[0118] The correlation analysis module 503 is specifically configured to: obtain the total number of tuples and the number of hidden tuples recorded by the system table; perform difference operation on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples; perform summation operation on the number of valid tuples of the plurality of system tables to obtain the total number of valid tuples of the to-be-evaluated table; and calculate the skew index according to the total number of valid tuples of the to-be-evaluated table.

[0119] As an optional implementation provided by the embodiments of the present application, the correlation analysis module 503 is specifically configured to: calculate the average number of tuples of the plurality of segmented instances according to the total number of valid tuples of the to-be-evaluated table and the number of instances of the plurality of segmented instances; calculate the standard deviation according to the average number of tuples; and perform ratio operation on the average number of tuples and the standard deviation to obtain the skew index.

[0120] As an optional implementation provided by the embodiments of the present application, the correlation analysis module 503 is specifically configured to: in the case that the segmented instance identifiers recorded by the system table and the external webpage table are the same, and the data file names recorded by the system table and the external webpage table are the same, obtain the inflated size recorded by the system table and the physical size recorded by the external webpage table; perform difference operation on the physical size and the inflated size to obtain the actual data amount of the to-be-evaluated table; and calculate the skew index according to the actual data amount of the to-be-evaluated table.

[0121] As an optional implementation provided by the embodiment of the present application, the correlation analysis module 503 is specifically configured to: calculate an average data amount according to an actual data amount of the table to be evaluated and a number of data files; perform difference operation on the maximum data amount of the table to be evaluated and the average data amount to obtain a data amount difference; and perform ratio operation on the data amount difference and the average data amount to obtain the skew index.

[0122] As an optional implementation provided by the embodiment of the present application, the correlation analysis module 503 is specifically configured to: obtain a total tuple number and a hidden tuple number of the system table record; perform difference operation on the total tuple number and the hidden tuple number to obtain an effective tuple number; perform summation operation on the effective tuple numbers of the plurality of system tables to obtain a total effective tuple number of the table to be evaluated; in a case where the total effective tuple number is greater than or equal to the tuple number threshold, calculate a first skew index according to the total effective tuple number of the table to be evaluated; obtain a segmented instance identifier and a data file name of the system table and the external web page table record; in a case where the segmented instance identifiers of the system table and the external web page table record are same and the data file names of the system table and the external web page table record are same, obtain an inflated size of the system table record and a physical size of the external web page table record; perform difference operation on the physical size and the inflated size to obtain an actual data amount of the table to be evaluated; calculate a second skew index according to the actual data amount of the table to be evaluated; and calculate the skew index according to the first skew index and the second skew index.

[0123] As an optional implementation provided by the embodiment of the present application, the processing module 504 is configured to: if the skew index is greater than a first skew threshold and less than or equal to a second skew threshold, determine a first data skew degree; wherein the first skew threshold is less than the second skew threshold; if the skew index is greater than the second skew threshold and less than or equal to a third skew threshold, determine a second data skew degree; wherein the second skew threshold is less than the third skew threshold; the second data skew degree is greater than the first data skew degree; and if the skew index is greater than the third skew threshold, determine a third data skew degree; wherein the third data skew degree is greater than the second data skew degree.

[0124] As an optional implementation provided by the embodiment of the present application, the apparatus further comprises a system table creating module configured to create a system table on a segmented instance when a database is initialized; the system table comprises at least one of a segmented instance identifier, a table object identifier, a data file name, a hidden tuple number, a total tuple number and an inflated size.

[0125] As an optional implementation provided by the embodiment of the present application, the information table obtaining module 502 is specifically configured to:

[0126] obtain the system tables of the plurality of segmented instances through a distributed query manner;

[0127] The external webpage table is created by a preset command, and the physical file information recorded in the external webpage table includes at least one of a segmented instance identifier, a data file name, and a physical size.

[0128] As an optional implementation of the embodiment of the present application, the device further comprises a filtering module configured to filter the table to be evaluated according to a table name of the table to be evaluated in a case where the table to be evaluated is a single or multiple database tables.

[0129] The features of the embodiment of the data evaluation device can be referred to the related description of the embodiment of the data evaluation method, which will not be repeated here.

[0130] As shown in Figure 6 The embodiment of the present application further provides an electronic device, which comprises a memory 601 and a processor 602, the memory 601 stores a computer program, and the processor 602 is configured to run the computer program to execute the steps in any of the above-mentioned data evaluation method embodiments.

[0131] The embodiment of the present application further provides a computer readable storage medium, which stores a computer program, and the computer program is configured to execute the steps in any of the above-mentioned data evaluation method embodiments when running.

[0132] In an example embodiment, the above-mentioned computer readable storage medium can include but is not limited to a U disk, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk or an optical disk, and various media that can store computer programs.

[0133] The embodiment of the present application further provides a computer program product, which comprises a computer program, and the computer program is executed by a processor to implement the steps in any of the above-mentioned data evaluation method embodiments.

[0134] The embodiment of the present application further provides another computer program product, which comprises a non-volatile computer readable storage medium, and the non-volatile computer readable storage medium stores a computer program, and the computer program is executed by a processor to implement the steps in any of the above-mentioned data evaluation method embodiments.

[0135] Those skilled in the art will further realize that the mere concepts, teachings, and embodiments described herein are merely meant to provide an enabling description of the applications and are not intended to limit the scope of the applications. Therefore, embodiments or examples described herein are not meant to be limiting, but merely to aid in the understanding of the overall more complete range of variations that can be implemented in accordance with the concepts, teachings, and embodiments described herein.

[0136] The data evaluation method and device, the electronic device, and the storage medium provided by the present application are described in detail above. The principles and implementation manners of the present application are described by applying specific examples in the present article. The above description of the embodiments is only used to help understand the method of the present application and its core idea. It should be pointed out that, for those skilled in the art, without departing from the principles of the present application, some improvements and modifications can be made to the present application. These improvements and modifications also fall within the protection scope of the claims of the present application.< / fileno> < / fileno> < / fileno>

Claims

1. A data evaluation method, characterized in that: include: In response to a data skew query task on a table to be evaluated, data files of the table to be evaluated on multiple segment instances are obtained; wherein the table to be evaluated is a heap table or an append-optimized table, and each segment instance stores a portion of the data files of the table to be evaluated; Obtaining an information table corresponding to the table to be evaluated, the information table including a system table and an external webpage table; wherein the system table is used to record storage information of the table to be evaluated, and the external webpage table is used to record physical file information of the multiple segment instances; Performing correlation analysis on the data files corresponding to the multiple segment instances through the information table to calculate a tilt index; wherein the tilt index is used to characterize the data distribution of the table to be evaluated on the multiple segment instances; Determining the degree of data skewness of the table to be evaluated based on the skew index; The data files corresponding to the multiple segmentation instances are subjected to correlation analysis through the information table to calculate the skew index, including: performing skew statistics on the data files corresponding to the multiple segmentation instances according to the number of valid tuples recorded in the system table to calculate the skew index; and / or performing skew statistics on the data files corresponding to the multiple segmentation instances according to the expanded size recorded in the system table and the physical size recorded in the external web page table to calculate the skew index.

2. The method according to claim 1, characterized in that There are multiple system tables; The step of performing tilt statistics on the data files corresponding to the plurality of segment instances according to the number of valid tuples in the system table and calculating the tilt index includes: Get the total number of tuples and the number of hidden tuples recorded in the system table; Performing a difference operation on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples; Adding up the valid tuple numbers of the plurality of system tables to obtain the total number of valid tuples of the table to be evaluated; The skew index is calculated according to the total number of valid tuples in the table to be evaluated.

3. The method according to claim 2, characterized in that Calculating the tilt index according to the total number of valid tuples in the table to be evaluated includes: Calculate the average number of tuples of the multiple segment instances according to the total number of valid tuples in the table to be evaluated and the number of instances of the multiple segment instances; Calculating a standard deviation based on the average number of tuples; A ratio operation is performed on the average number of tuples and the standard deviation to obtain the tilt index.

4. The method according to claim 1, wherein Performing skew statistics on the data files corresponding to the multiple segment instances according to the expanded size of the system table record and the physical size of the external web page table record to calculate the skew index includes: When the segment instance identifiers of the system table and the external web page table records are the same, and the data file names of the system table and the external web page table records are the same, obtaining the expanded size of the system table record and the physical size of the external web page table record; Performing a difference operation on the physical size and the expanded size to obtain an actual data volume of the table to be evaluated; The tilt index is calculated according to the actual data volume of the table to be evaluated.

5. The method according to claim 4, characterized in that Calculating the tilt index according to the actual data volume of the table to be evaluated includes: Calculate the average data volume based on the actual data volume and the number of data files in the table to be evaluated; Performing a difference operation based on the maximum data volume of the table to be evaluated and the average data volume to obtain a data volume difference; A ratio operation is performed on the data volume difference and the average data volume to obtain the tilt index.

6. The method according to claim 1, characterized in that The performing correlation analysis on the data files corresponding to the plurality of segment instances through the information table to calculate the tilt index includes: Get the total number of tuples and the number of hidden tuples recorded in the system table; Performing a difference operation on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples; Adding up the valid tuple numbers of the plurality of system tables to obtain the total number of valid tuples of the table to be evaluated; When the total number of valid tuples is greater than or equal to the tuple number threshold, calculating a first tilt index according to the total number of valid tuples in the table to be evaluated; Obtaining the segment instance identifier and data file name recorded in the system table and the external webpage table; When the segment instance identifiers of the system table and the external web page table records are the same, and the data file names of the system table and the external web page table records are the same, obtaining the expanded size of the system table record and the physical size of the external web page table record; Performing a difference operation on the physical size and the expanded size to obtain an actual data volume of the table to be evaluated; Calculating a second tilt index according to the actual data volume of the table to be evaluated; The tilt index is calculated according to the first tilt index and the second tilt index.

7. The method according to any one of claims 1 to 6, characterized in that Determining the data skew degree of the table to be evaluated based on the skew indicator includes: If the tilt index is greater than a first tilt threshold and less than or equal to a second tilt threshold, determining the first data tilt degree; wherein the first tilt threshold is less than the second tilt threshold; If the tilt index is greater than the second tilt threshold and less than or equal to the third tilt threshold, it is determined to be a second data tilt degree; wherein the second tilt threshold is less than the third tilt threshold; and the second data tilt degree is greater than the first data tilt degree; If the tilt index is greater than the third tilt threshold, it is determined to be a third data tilt degree; wherein the third data tilt degree is greater than the second data tilt degree.

8. The method according to claim 1, characterized in that The method further comprises: When the database is initialized, system tables are created on the segment instance; The system table includes at least one of a segment instance identifier, a table object identifier, a data file name, a number of hidden tuples, a total number of tuples, and an inflated size.

9. The method according to claim 1, characterized in that The obtaining of the information table corresponding to the table to be evaluated includes: Obtaining the system tables of the multiple segment instances by using a distributed query method; The external web page table is created by a preset command; wherein the physical file information recorded in the external web page table includes at least one of a segment instance identifier, a data file name, and a physical size.

10. The method according to claim 1, characterized in that The method further comprises: In the case that the table to be evaluated is a single or multiple database tables, the tables to be evaluated are filtered according to their table names.

11. A data evaluation device, characterized in that: include: a data file acquisition module, configured to, in response to a data skew query task on a table to be evaluated, acquire data files of the table to be evaluated on multiple segment instances; wherein the table to be evaluated is a heap table or an append-optimized table, and each segment instance stores a portion of the data files of the table to be evaluated; An information table acquisition module is used to acquire an information table corresponding to the table to be evaluated, wherein the information table includes a system table and an external webpage table; wherein the system table is used to record storage information of the table to be evaluated, and the external webpage table is used to record physical file information of the multiple segment instances; an association analysis module, configured to perform association analysis on the data files corresponding to the plurality of segment instances through the information table, and calculate a tilt index; wherein the tilt index is used to characterize the data distribution of the table to be evaluated on the plurality of segment instances; A processing module, configured to determine the degree of data skew of the table to be evaluated based on the skew indicator; The association analysis module is specifically used to perform skew statistics on the data files corresponding to the multiple segmentation instances based on the number of valid tuples recorded in the system table, and calculate the skew index; and / or, perform skew statistics on the data files corresponding to the multiple segmentation instances based on the expanded size of the system table records and the physical size of the external web page table records, and calculate the skew index.

12. An electronic device, characterized in that: include: memory for storing computer programs; A processor, configured to implement the steps of the data evaluation method according to any one of claims 1 to 10 when executing the computer program.

13. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, wherein the computer program implements the steps of the data evaluation method according to any one of claims 1 to 10 when executed by a processor.

14. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the data evaluation method according to any one of claims 1 to 10 are implemented.

Images