Data evaluation method and device, electronic equipment and storage medium

Abstract

The invention discloses a data evaluation method and device, electronic equipment and a storage medium, and relates to the technical field of distributed databases, and the method comprises the steps: obtaining data files of a to-be-evaluated table on a plurality of segment instances in response to a data skew query task of the to-be-evaluated table, meanwhile, physical file information of the segmented instances is obtained through an external webpage table, association analysis is conducted on data files corresponding to the segmented instances in combination with storage information of a system table, distribution of actual effective data can be reflected more truly, the accuracy of skew index calculation is improved, and the calculation efficiency is improved. And then the data inclination degree of the to-be-evaluated table is determined according to the accurate inclination index, so that the problem of insufficient accuracy of data inclination degree evaluation in related technologies is solved.

Description

Technical Field

[0001] The present application relates to the field of distributed database technology, and in particular to a data evaluation method, device, electronic device, and storage medium. Background Art

[0002] Greenplum is a distributed parallel database that uses a shared-nothing Massively Parallel Processing (MPP) architecture. It enables efficient parallel processing of massive amounts of data by distributing data across multiple segment instances using field hashing. Its storage formats include heap tables and append-optimized (AO) tables, relying on the even distribution of data across segment instances to ensure query performance. However, incorrect distribution key selection or data manipulation can lead to data skew, meaning that some segment instances have significantly more data than others, which can cause query response delays, overflow file errors, insufficient memory, and other issues. Therefore, after data is stored in the database, it is necessary to verify the uniformity of the distribution to ensure that the number of rows and storage consumption on each node are roughly balanced.

[0003] Related technologies assess data skew based on the coefficient of variation of row counts or by measuring the percentage of system idle time during table scans. However, Greenplum suffers from the following issues: Skew calculation for heap tables relies on the count total command, which is inefficient for large heap tables, consuming resources and impacting other tasks; Skew calculation for append-optimized tables is performed by reading the number of rows in the system table, failing to account for table expansion, resulting in inaccurate skew rates. Summary of the Invention

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

[0005] The present application provides a data evaluation method, comprising: in response to a data skew query task for a table to be evaluated, obtaining data files of the table to be evaluated on multiple segmented instances; wherein the table to be evaluated is a heap table or an append-optimized table, and each segmented instance stores part 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 web page table; wherein the system table is used to record storage information of the table to be evaluated, and the external web page table is used to record physical file information of multiple segmented instances; performing correlation analysis on the data files corresponding to the multiple segmented instances through the information table, and calculating a skew index; wherein the skew index is used to characterize the data distribution of the table to be evaluated on the multiple segmented instances; and determining the degree of data skew of the table to be evaluated based on the skew index.

[0006] The present application also provides a data evaluation device, comprising: A data file acquisition module is 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 obtain the information table corresponding to the table to be evaluated, and the information table includes a system table and an external web page table; wherein the system table is used to record the storage information of the table to be evaluated, and the external web page table is used to record the physical file information of multiple segment instances; The association analysis module is used to perform association analysis on the data files corresponding to multiple segment instances through the information table and calculate the tilt index; wherein the tilt index is used to characterize the data distribution of the table to be evaluated on multiple segment instances; The processing module is used to determine the data skew degree of the table to be evaluated based on the skew index.

[0007] The present application also provides an electronic device, comprising: a memory for storing a computer program; and a processor for implementing the steps of the above-mentioned data evaluation method when executing the computer program.

[0008] The present application also provides a computer-readable storage medium, in which a computer program is stored. When the computer program is executed by a processor, the steps of the above-mentioned data evaluation method are implemented.

[0009] The present application also provides a computer program product, comprising a computer program, which implements the steps of the above-mentioned data evaluation method when executed by a processor.

[0010] This application responds to the data skew query task of the table to be evaluated, obtains the data files of the table to be evaluated on multiple segmented instances, and at the same time obtains the physical file information of the segmented instances through an external web page table, and performs correlation analysis on the data files corresponding to the multiple segmented instances in combination with the storage information of the system table. It can more realistically reflect the distribution of actual valid data, improve the accuracy of the calculation of the skew index, and then determine the data skew degree of the table to be evaluated based on the accurate skew index, thereby solving the problem of insufficient accuracy in data skew degree evaluation in related technologies. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In order to more clearly illustrate the embodiments of the present application, the following is a brief introduction to the drawings required for use in the embodiments. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0012] Figure 1A schematic diagram of a specific hardware architecture on which the execution of a data evaluation method provided in an embodiment of the present application relies; Figure 2 A schematic diagram of a data evaluation method provided in an embodiment of the present application Figure 1 ; Figure 3 A schematic diagram of the structure of a system table provided in an embodiment of the present application; Figure 4 A schematic diagram of a data evaluation method provided in an embodiment of the present application Figure 2 ; Figure 5 A schematic diagram of the structure of a data evaluation device provided in an embodiment of the present application; Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0013] The following will be combined with the accompanying drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0014] It should be noted that, in the description of this application, the terms "comprises," "includes," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. The terms "first," "second," etc., in this application are used to distinguish similar objects, and are not used to describe a particular order or sequence.

[0015] In order to more clearly illustrate the embodiments of the present application, the following briefly introduces the technical terms used in the embodiments: Greenplum is a distributed parallel database that uses a shared-nothing MPP architecture. It is developed based on the open source relational database (Postgre SQL). It is a relational distributed parallel database that uses a shared-nothing MPP architecture. It can also be called an MPP database. It has built-in parallel storage, parallel communication, parallel computing, and parallel optimization functions, and has powerful and efficient data storage and processing capabilities.

[0016] A cluster consists of a management node (Master Server) and multiple data nodes (Segment Servers). The Master is the database entry point, primarily responsible for receiving client connection requests, generating query plans for SQL statements, distributing the plans to all segment nodes for execution, and aggregating the results returned by the segment nodes. The Master node does not store any user data, only database metadata, such as system tables. Each data node can run multiple database instances, also called Segment instances. (When high availability is enabled, there is one active and one standby instance: the active master instance provides services while the standby instance synchronizes data with the active master.) Each instance can be considered an independent PostgreSQL database, storing a portion of user data and participating in query execution. The more Segment instances a node has, the more resources it uses when executing queries on that segment node, resulting in faster query execution. By distributing data across different data nodes after hashing a specific field, the amount of data processed by a single node is significantly reduced while queries can be parallelized, leveraging the cluster's strengths and significantly improving query performance.

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

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

[0019] Greenplum Database uses two storage formats: heap tables (HEAP tables) and append-optimized (AO) tables. HEAP tables support efficient update and delete operations. AO tables write data in an append-only manner, offering extremely high write performance. They are typically used to store factual data within the database and also support data update and delete operations. Because Greenplum Database relies on evenly distributed data across segments, for optimal query performance, user data should be evenly distributed across each segment instance, regardless of the storage format, meaning each instance stores 1 / n of the data.

[0020] Distributed storage: For example, in a 100-node cluster, each node stores only a portion of the total data. The distributed nature of the MPP database allows its storage capacity and computing power to exceed the limits of a single server, becoming the sum of multiple segments.

[0021] Data skew occurs when data is processed in parallel and unevenly distributed, resulting in a large amount of data being concentrated on a single compute segment instance. This causes the processing speed of that segment to be significantly lower than the average, becoming a bottleneck for the entire cluster and affecting overall computing performance. Data skew affects scan (read) performance and all other query execution operations, such as joins and groupings.

[0022] gp_dist_random is a Greenplum Database-specific system function that provides direct access to data on each segment in a distributed table. It allows queries to directly access data on all segments, returning results containing rows from every segment, and executing the same query independently on each segment.

[0023] In Greenplum, table-level data skew is a key cause of poor query performance and insufficient memory. Therefore, after initial and incremental data ingestion, it's crucial to verify the even distribution of data across all segment instances. This ensures that each segment has roughly the same number of rows and balanced storage consumption. Greenplum provides the gp_skew_coefficients and gp_skew_idle_fractions views for diagnosing uneven table data distribution, but these two solutions have significant technical limitations in practice, making them difficult to efficiently and accurately detect skew.

[0024] However, the above solution has obvious flaws: 1. Inefficiency: HEAP tables require a full table scan to calculate the number of rows using the COUNT(*) command. This is time-consuming and resource-intensive for large data tables, impacting other tasks. 2. Inaccuracy: AO tables rely on the number of rows recorded in system tables, failing to account for table expansion caused by frequent updates / deletions. Dead tuples are incorrectly counted, leading to distorted skewness calculations. 3. Single dimension: This only counts the distribution of row counts, ignoring differences in storage size, and cannot identify scenarios where the number of rows is balanced but the data volume is skewed. 4. Inefficient queries: Single-table skewed queries require filtering after traversing the entire table. Ineffective calculations amplify resource consumption and can easily lead to system overload under high concurrency.

[0025] In order to solve some or all of the above-mentioned technical problems, an embodiment of the present application provides a data evaluation method, which obtains data file information through information tables (system tables and external web page tables) without the need for full table scanning, and directly performs correlation analysis based on storage information and physical file information, thereby improving computing efficiency, reducing the occupation of system resources and the impact on other tasks; obtaining the physical file information of the segmented instance through the external web page table, and performing correlation analysis in combination with the storage information of the system table can more realistically reflect the distribution of actual valid data and improve the accuracy of the calculation of the tilt indicator; directly performing correlation analysis on the data files of multiple segmented instances based on the information table, without the need to traverse the entire table, reducing invalid calculations, reducing resource consumption, and avoiding system overload problems under high concurrency.

[0026] In some embodiments provided in the present application, the physical size of the data file is analyzed through an information table, and the calculated skew index not only includes dimensions related to the number of rows, but also reflects the distribution differences of the storage size. It can identify scenarios where the number of rows is balanced but the amount of data is skewed, enriching the evaluation dimensions.

[0027] In order to enable those skilled in the art to better understand the present application, the present application is further described in detail below with reference to the accompanying drawings and specific implementation methods.

[0028] In conjunction 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.

[0029] like Figure 1 Schematic diagram of the specific hardware architecture on which the execution of the data evaluation method relies.

[0030] The hardware architecture includes: building a system table module, implementing an external web page table module, an association analysis module, and a data tilt assessment module.

[0031] Among them, the system table module is used to build a built-in system table. The system table is created on all segment instances when the database is initialized. It contains fields such as segment instance identifier (segment_id), table object identifier (reloid), data file name (relfilenode), hidden tuple count (hidden_tupcount), total tuple count (total_tupcount), and inflated size (inflated_size).

[0032] 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.

[0033] 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.

[0034] 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.

[0035] 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.

[0036] 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: 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.

[0037] 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.

[0038] 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>, such 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.

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

[0040] In some embodiments, in response to a data skew query task for a table to be evaluated, the number of tables to be evaluated is first determined to determine whether it is a single table, multiple tables, or a full table. Depending on the number of tables to be evaluated, the specific operations in subsequent step S203 may differ.

[0041] S202: Obtain the information table corresponding to the table to be evaluated.

[0042] The information table includes a system table and an external web page 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 web page table corresponding to the table to be evaluated is used to record the physical file information of multiple segment instances.

[0043] In some embodiments, during database initialization, a system table, perhaps named pg_file_details_t, is created on the segment instance. After successful database initialization, the newly added system table is also created. Whenever data files on the segment instance are added, deleted, or modified, the system table is updated synchronously.

[0044] System tables (such as pg_file_details_t) are structured tables maintained within the database, specifically used to manage database table storage information. Their fields include the segment instance identifier (segment_id), table object identifier (reloid), data file name (relfilenode), hidden tuple count (hidden_tupcount), total tuple count (total_tupcount), and inflated size (inflated_size).

[0045] like Figure 3 As shown, these are designed around the storage properties of database tables. The data is automatically generated and maintained by the database system and reflects the storage status of the database table. The segment_id identifies the specific segment instance ID, indicating which segment instance the data file belongs to. The reloid indicates the database table (such as the table to be evaluated) and is used to establish a correspondence between system tables and database tables. The relfilenode is the data file name on the segment instance. It physically identifies the data storage file for the database table and is used to track the physical storage medium for the table data. The hidden_tupcount records the number of hidden tuples (rows) in the data file caused by delete or update operations. These hidden tuples, while physically present, are invalid and reflect the residual history of table data modifications. The total_tupcount is the total number of tuples (rows) in the data file, including both valid and hidden tuples. It provides a complete count of the number of rows in the data file at the physical level. The inflated_size indicates the inflated data size, reflecting the inactive space in the data file caused by updates, deletes, and other operations. It can be used to analyze table storage efficiency.

[0046] In the above embodiment, the system table pg_file_details_t records storage information such as the total number of tuples (total_tupcount). This eliminates the need for a time-consuming full table scan; row count data can be quickly retrieved by simply querying the system table. This significantly improves efficiency, reduces resource usage, and reduces the impact on other tasks. pg_file_details_t includes the number of hidden tuples (hidden_tupcount) and the inflated size (inflated_size), which clearly distinguishes valid tuples from invalid hidden tuples generated by updates and deletions. This prevents invalid hidden tuples from interfering with statistical results and improves the accuracy of skew rate calculations. The inclusion of storage size-related fields such as inflated_size in the system table allows for analysis not only based on the number of rows but also incorporating storage size dimensions when assessing data skew. This effectively identifies scenarios where the number of rows is balanced but the data volume is skewed, enriching the evaluation dimensionality. In addition, since the system table directly stores key information such as the segment instance identifier (segment_id) and table object identifier (reloid), there is no need to traverse the entire table when querying single-table skew. Instead, the corresponding data file information can be quickly associated and located through these identifiers. This reduces invalid calculations, lowers resource consumption, and avoids system overload under high concurrency.

[0047] 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.

[0048] 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.

[0049] 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.

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

[0051] 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.

[0052] 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.

[0053] 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.

[0054] Specifically, for multiple system tables corresponding to multiple segmented instances, the total number of tuples and the number of hidden tuples in each system table 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 number of valid tuples corresponding to that system table. This valid tuple number corresponds to a segmented instance, that is, the number of valid tuples in the table to be evaluated. Furthermore, the valid tuple numbers of all system tables corresponding to the table to be evaluated are summed to obtain the total number of valid tuples in the table to be evaluated.

[0055] Furthermore, a skew index is calculated based on the total number of valid tuples in the table to be evaluated. Optionally, the average number of tuples in the segmented instances is calculated based on the total number of valid tuples in the table to be evaluated and the number of instances in the multiple segmented instances. The standard deviation is then calculated based on the average number of tuples. The ratio of the average number of tuples to the standard deviation is then calculated to obtain the skew index.

[0056] For example, the tilt indicator is the coefficient of variation (CV). The coefficient of variation can be calculated according to the following formula (1): (1) In formula (1), n ​​is the number of instances of the segment instance, xi is the number of valid tuples corresponding to the i-th segment instance, It is the number of valid tuples corresponding to n segment instances, and also the number of valid tuples of n system tables. It is actually the total number of valid tuples in the table to be evaluated. is the average number of tuples corresponding to segment instances.

[0057] The above embodiment uses the total number of tuples and the number of hidden tuples recorded in the system table, and obtains the number of valid tuples through difference operation without the need for full table scanning, which greatly reduces data processing time and resource usage and improves computing efficiency; the number of valid tuples is obtained by subtracting the number of hidden tuples from the total number of tuples, accurately eliminating invalid hidden tuples generated by deletion or update, avoiding the interference of dead tuples on the statistical results, making the calculation of the tilt index more in line with the actual distribution of valid data, and improving accuracy; not only is the tilt index calculated based on the number of valid tuples, but it is also combined with the expanded size of the system table and the physical size of the external web page table for statistics, taking into account the data storage size dimension and enriching the evaluation dimension; calculation is performed based on the existing data of the system table and the external web page table, without the need to traverse the entire table and then filter, which reduces invalid calculations, reduces resource consumption, and alleviates the risk of system overload under high concurrency.

[0058] In some embodiments, skew statistics are performed on data files corresponding to multiple segmented instances based on the expanded size of the system table and the physical size of the external web table, including: when the segmented instance identifiers recorded in the system table and the external web table are the same, and the data file names recorded in the system table and the external web table are the same, obtaining the expanded size of the system table record and the physical size of the external web table record; performing a difference operation on the physical size and the expanded size to obtain the actual data volume of the table to be evaluated; and calculating the skew index based on the actual data volume of the table to be evaluated.

[0059] Specifically, first obtain the segment instance identifier and data file name of the system table record, as well as the segment instance identifier and data file name of the external web page table record. Then, compare whether the segment instance identifiers and data file names of the two table records are the same. If so, it means that the current system table and the current external web page table correspond to the same data file of the same segment instance, and then further obtain the expanded size of the system table record and the physical size of the external web page table record. Calculate the difference between the physical size and the expanded size as the actual data volume of the data file, and perform the above-mentioned association operation and actual data volume calculation operation on the system table and external web page table of each segment instance to obtain the actual data volume of all data files distributed in multiple segment instances of the table to be evaluated. Then calculate the skew index based on the actual data volume of the table to be evaluated.

[0060] In the process of calculating the skew index based on the actual data volume of the table to be evaluated, the average data volume is first calculated based on the actual data volume and the number of data files of the table to be evaluated, and then a difference operation is performed on the total data volume and the average data volume of the table to be evaluated to obtain the data volume difference, and then a ratio operation is performed on the data volume difference and the average data volume to obtain the skew index.

[0061] Optionally, the tilt indicator is a dispersion coefficient. The dispersion coefficient is calculated according to the following formula (2): (2) In formula (2), is the maximum data size of the table to be evaluated, and is the data size of the largest data file of the table to be evaluated distributed on the i-th segment instance. is the actual data volume of the table to be evaluated, and n is the number of data files.

[0062] The above embodiment associates the records of the same segment instance identifier and data file name in the system table and the external web page table, and uses the existing expanded size and physical size data of the two to perform difference calculations to obtain the actual data volume, without the need for a full table scan, reducing resource consumption and calculation time, and improving efficiency; the actual data volume is obtained by subtracting the expanded size from the physical size, accurately eliminating the invalid expansion space generated by update and deletion operations, avoiding the interference of invalid data on the statistical results, making the tilt indicator more in line with the actual storage distribution of valid data, and improving accuracy; focusing on the actual data volume of data storage for tilt statistics, supplementing the evaluation of the storage size dimension, and breaking through the limitations of single row statistics; performing association calculations based on the existing records of the system table and the external web page table, there is no need to traverse the entire table and then filter, reducing invalid calculations and reducing resource consumption.

[0063] In some embodiments, such as Figure 4 As shown, when executing step S203, the following steps S301 to S309 are included: S301. Obtain the total number of tuples and the number of hidden tuples recorded in the system table.

[0064] Get the total tuple count total_tupcount and hidden tuple count hidden_tupcount recorded in the system table pg_file_details_t.

[0065] S302: Perform a difference operation on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples.

[0066] Valid tuple count = total tuple count total_tupcount - hidden tuple count hidden_tupcount. Calculate the valid tuple count for the table to be evaluated across multiple segment instances. Each segment instance corresponds to one system table, and each system table corresponds to one valid tuple count.

[0067] S303 : performing a sum operation on the numbers of valid tuples in multiple system tables to obtain the total number of valid tuples in the table to be evaluated.

[0068] All data of the table to be evaluated is distributed across multiple segment instances. Each segment instance corresponds to a system table. After calculating the number of valid tuples of all system tables, the total number of valid tuples of the table to be evaluated is obtained by cumulatively adding them up.

[0069] S304 : When the total number of valid tuples is greater than or equal to the tuple number threshold, calculate a first tilt index according to the total number of valid tuples in the table to be evaluated.

[0070] The tuple number threshold is a preset number of valid tuples in the database table. The tuple number threshold can be the number of segment instances in the current database or other values, which are not specifically limited in this application.

[0071] Compare whether the total number of valid tuples in the table to be evaluated is greater than or equal to the tuple number threshold. If the total number of valid tuples is greater than or equal to the tuple number threshold, calculate the first tilt indicator, such as the coefficient of variation, based on the total number of valid tuples, and then continue to execute subsequent steps.

[0072] If the total number of valid tuples is less than the tuple number threshold, it means that some of the multiple segment instances may have no data files. In this case, it is meaningless to evaluate whether the table is skewed, and the process ends.

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

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

[0075] S306. 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, obtain the expanded size of the system table record and the physical size of the external web page table record.

[0076] Compare the segment instance identifier and data file name to see if they are the same. If so, get 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.

[0077] S307: Perform a difference operation on the physical size and the expanded size to obtain the actual data volume of the table to be evaluated.

[0078] The actual data volume of the table to be evaluated = physical size - expanded size.

[0079] S308: Calculate a second tilt index based on the actual data volume of the table to be evaluated.

[0080] The second tilt indicator may be a coefficient of dispersion.

[0081] S309: Calculate a tilt index according to the first tilt index and the second tilt index.

[0082] The first tilt index and the second tilt index may be weightedly summed to obtain a final tilt index.

[0083] The above embodiment calls the total number of tuples and the number of hidden tuples recorded in the system table pg_file_details_t, and obtains the number of valid tuples through a simple difference operation without the need for a full table scan, which greatly reduces the resource consumption and time cost of data processing and improves computing efficiency. The valid tuple number is obtained by subtracting the number of hidden tuples from the total number of tuples, accurately eliminating invalid hidden tuples generated by deletion or update. At the same time, the actual data volume is calculated by subtracting the expansion size from the physical size, avoiding the interference of invalid expansion space on the statistical results, making the skew index more consistent with the actual valid data distribution and improving accuracy. The first skew index is calculated based on the number of valid tuples, and the second skew index is calculated in combination with the actual data volume. Finally, a comprehensive skew index is obtained through weighted summation, which takes into account both the logical row number and physical storage dimensions. The existing data in the system table and the external web page table are used for step-by-step calculation, without the need for filtering after traversing the entire table, and the tuple number threshold is used to avoid meaningless calculations, reduce invalid operations, and reduce resource consumption.

[0084] In some embodiments, when the table to be evaluated is a single or multiple database tables, indicating that a data skew query task needs to be performed on the table to be evaluated, the table to be evaluated is filtered based on the table name to perform data skew analysis and calculate the skew index. For example, filtering is performed using a WHERE clause or a JOIN join condition.

[0085] When the table to be evaluated is a full table, step S203 or any optional implementation method thereof is directly executed to obtain the tilt index.

[0086] The above embodiment directly filters out the tables to be evaluated based on the table names, whether it is a single table or multiple tables, without traversing the entire data before filtering, thus reducing the processing of irrelevant data, reducing resource consumption caused by invalid calculations, and improving query efficiency.

[0087] S204: Determine the data skewness of the table to be evaluated based on the skew index.

[0088] In some embodiments, the degree of data skew of the table to be evaluated is determined based on a skew index, including: if the skew index is greater than a first skew threshold and less than or equal to a second skew threshold, it is determined to be 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, it is determined to be 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; if the skew index is greater than the third skew threshold, it is determined to be a third data skew degree; wherein the third data skew degree is greater than the second data skew degree.

[0089] For example, assuming that the tilt indicator is the coefficient of variation, the corresponding tilt degree is shown in Table 1. Optionally, for moderate tilt, the data distribution is slightly adjusted; for extremely high tilt, urgent treatment is required.

[0090] Table 1

[0091] The above embodiment compares the tilt index with the first, second, and third tilt thresholds, and clearly divides the first, second, and third data tilt degrees, such as medium, high, and extremely high, so that the tilt assessment is upgraded from a simple numerical statistics to a degree judgment with a clear gradient. Through quantitative degree grading, optimization strategies can be formulated in a targeted manner, avoiding optimization decision lags or resource mismatches caused by single indicators and ambiguous interpretations.

[0092] In summary, the embodiment of the present application provides a data evaluation method, which directly utilizes the existing information of the system table and the external web page table instead of the full table scan to calculate the number of rows, thereby avoiding the processing of a large amount of invalid data, greatly reducing resource consumption and time costs, and being able to efficiently complete the skew diagnosis even in the face of large data tables, reducing the impact on other tasks. The number of valid tuples is obtained by the difference between the total number of tuples and the number of hidden tuples, and the actual data volume is obtained by combining the difference between the physical size and the expanded size. Interference factors such as dead tuples and invalid expansion space are accurately eliminated, so that the calculation of the skew index is more in line with the actual distribution of valid data, and the problem of statistical distortion caused by table expansion is solved. Taking into account both the number of valid tuples (row number dimension) and the actual data volume (storage size dimension), it is possible to identify the scenario of "balanced number of rows but skewed data volume", enriching the dimensions of skew evaluation and making the diagnostic results more comprehensive.

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

[0094] like Figure 5 As shown, an embodiment of the present application further provides a data evaluation device, the device comprising: A data file acquisition module 501 is 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 502 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 the storage information of the table to be evaluated, and the external webpage table is used to record the physical file information of multiple segment instances; The association analysis module 503 is used to perform association analysis on the data files corresponding to the multiple segment instances through the information table to calculate the tilt index; wherein the tilt index is used to characterize the data distribution of the table to be evaluated on the multiple segment instances; The processing module 504 is configured to determine the degree of data skew of the table to be evaluated according to the skew index.

[0095] As an optional implementation provided in an embodiment of the present application, the association analysis module 503 is specifically used to: perform skew statistics on the data files corresponding to multiple segmented 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 multiple segmented 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.

[0096] As an optional implementation provided in an embodiment of the present application, there are multiple system tables; The association analysis module 503 is specifically used to: obtain the total number of tuples and the number of hidden tuples recorded in the system table; perform a difference operation on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples; sum the number of valid tuples in multiple system tables to obtain the total number of valid tuples in the table to be evaluated; and calculate the skew index based on the total number of valid tuples in the table to be evaluated.

[0097] As an optional implementation provided in an embodiment of the present application, the association analysis module 503 is specifically used to: calculate the average number of tuples of multiple segmented instances based on the total number of valid tuples in the table to be evaluated and the number of instances of multiple segmented instances; calculate the standard deviation based on the average number of tuples; perform a ratio operation on the average number of tuples and the standard deviation to obtain a tilt index.

[0098] As an optional implementation provided in an embodiment of the present application, the association analysis module 503 is specifically used to: obtain the expanded size of the system table record and the physical size of the external web table record when the segmented instance identifiers of the system table record and the external web table record are the same, and the data file names of the system table and the external web table record are the same; perform a difference operation on the physical size and the expanded size to obtain the actual data volume of the table to be evaluated; and calculate the skew index based on the actual data volume of the table to be evaluated.

[0099] As an optional implementation provided in an embodiment of the present application, the association analysis module 503 is specifically used to: calculate the average data volume based on the actual data volume and the number of data files of the table to be evaluated; perform a difference operation on the maximum data volume and the average data volume of the table to be evaluated to obtain a data volume difference; perform a ratio operation on the data volume difference and the average data volume to obtain a tilt index.

[0100] As an optional implementation provided by an embodiment of the present application, the association analysis module 503 is specifically used to: obtain the total number of tuples and the number of hidden tuples recorded in the system table; perform a difference operation on the total number of tuples and the number of hidden tuples to obtain the number of valid tuples; perform a sum operation on the valid tuple numbers of multiple 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, calculate the first tilt index based on the total number of valid tuples of the table to be evaluated; obtain the segmented instance identifier and data file name of the system table and the external web page table record; when the segmented instance identifier of the system table and the external web page table record are the same, and the data file name of the system table and the external web page table record are the same, obtain the expanded size of the system table record and the physical size of the external web page table record; perform a difference operation on the physical size and the expanded size to obtain the actual data volume of the table to be evaluated; calculate the second tilt index based on the actual data volume of the table to be evaluated; calculate the tilt index based on the first tilt index and the second tilt index.

[0101] As an optional implementation provided in an embodiment of the present application, the processing module 504 is used to: if the tilt index is greater than the first tilt threshold and less than or equal to the second tilt threshold, determine it as 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, determine it as the 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, determine it as the third data tilt degree; wherein the third data tilt degree is greater than the second data skew degree.

[0102] As an optional implementation provided in an embodiment of the present application, the device also includes a system table creation module, which is used to create a system table on the segment instance when the database is initialized; the system table includes at least one of a segment instance identifier, a table object identifier, a data file name, a hidden tuple number, a total tuple number, and an expanded size.

[0103] As an optional implementation provided in the embodiment of the present application, the information table acquisition module 502 is specifically configured to: Obtain system tables of multiple segment instances through distributed query; An external web page table is created through 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.

[0104] As an optional implementation provided in an embodiment of the present application, the device further includes a filtering module for filtering the tables to be evaluated according to their names when the tables to be evaluated are single or multiple database tables.

[0105] For the description of the features in the embodiment corresponding to the data evaluation device, reference can be made to the relevant description of the embodiment corresponding to the data evaluation method, and no further details will be given here.

[0106] like Figure 6 As shown, an embodiment of the present application further provides an electronic device, including a memory 601 and a processor 602, wherein 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 data evaluation method embodiments.

[0107] An embodiment of the present application further provides a computer-readable storage medium, in which a computer program is stored, wherein the computer program is configured to execute the steps of any of the above-mentioned data evaluation method embodiments when run.

[0108] In an exemplary embodiment, the computer-readable storage medium may include, but is not limited to, various media that can store computer programs, such as a USB flash drive, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk, or an optical disk.

[0109] An embodiment of the present application further provides a computer program product, which includes a computer program. When the computer program is executed by a processor, the steps in any of the above data evaluation method embodiments are implemented.

[0110] An embodiment of the present application further provides another computer program product, including a non-volatile computer-readable storage medium, wherein the non-volatile computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps in any of the above-mentioned data evaluation method embodiments are implemented.

[0111] Professionals may further appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the above description has generally described the components and steps of each example according to their functions. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0112] The above is a detailed introduction to a data evaluation method, device, electronic device and storage medium provided by the present application. Specific examples are used herein to illustrate the principles and implementation methods of the present application. The description of the above embodiments is only used to help understand the method and core ideas of the present application. It should be pointed out that for ordinary technicians in this technical field, without departing from the principles of the present application, several improvements and modifications can be made to the present application, and these improvements and modifications also fall within the scope of protection 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; The degree of data skew of the table to be evaluated is determined according to the skew index.

2. 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: Performing skew statistics on the data files corresponding to the multiple segment instances according to the number of valid tuples recorded in the system table, and calculating the skew index; and / or, According to the expanded size of the system table record and the physical size of the external web page table record, skew statistics are performed on the data files corresponding to the multiple segment instances to calculate the skew index.

3. The method according to claim 2, 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; Performing a sum operation on 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.

4. The method according to claim 3, 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.

5. The method according to claim 2, characterized in that 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.

6. The method according to claim 5, 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.

7. The method according to claim 2, 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; Performing a sum operation on 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.

8. The method according to any one of claims 1 to 7, 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.

9. 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.

10. 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.

11. The method according to claim 1, wherein 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.

12. 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; The processing module is configured to determine the data skew degree of the table to be evaluated according to the skew index.

13. 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 11 when executing the computer program.

14. 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 11 when executed by a processor.

15. 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 11 are implemented.

Images