Data lake warehouse clustering method and system based on query and metadata anomaly detection

Abstract

The present application relates to a data lake warehouse clustering method and system based on query and metadata anomaly detection, belonging to the technical field of big data computing. It comprises: data acquisition and preprocessing; EC-GKDE algorithm anomaly determination: training the data set after preprocessing operation to obtain the anomaly score of all query tasks for initial determination; metadata information anomaly determination: obtaining the associated table of the abnormal query task, obtaining the HDFS actual path information in the Hive metadata service, and calculating the proportion of small files under the HDFS actual path to determine whether the associated table of the abnormal query task is an abnormal table; clustering optimization: submitting a clustering task to combine small files into large files. The present application reduces the storage resource overhead and greatly reduces the time required for query tasks. The present application can control the number of clustering operation executions, and alleviate the problems of concurrent update not supported, incremental query not friendly and cluster resource waste caused by the clustering mechanism.

Description

Technical Field

[0001] This invention relates to the field of big data computing technology, and in particular to a data lake warehouse clustering method and system based on query and metadata anomaly detection. Background Technology

[0002] In today's increasingly diverse and heterogeneous data environment, data lake warehouses have become a highly efficient and flexible data management solution. Among existing data lake warehouse technologies, the Hudi data lake architecture and Presto query engine are among the mainstream solutions. Hudi is an open-source data lake warehouse management framework that provides various services for optimizing the storage and management of large-scale datasets, while supporting incremental data synchronization and time-travel queries. Presto is a distributed SQL query engine used to handle large-scale data analysis queries. It can provide various performance metrics for historical query tasks, helping us to analyze and optimize query performance. The combination of Hudi and Presto technologies enables the lake warehouse architecture to ingest data in real time or in batches to meet different data storage needs and provide users with fast query services for massive amounts of heterogeneous data from multiple sources.

[0003] While combining the two can provide a more powerful and comprehensive service, Hudi itself also has some potential problems. COW (Copy-on-Write) tables are one of the two write modes supported by Hudi. This write mode causes data files to grow continuously, leading to reduced data write efficiency. Furthermore, in lakehouse scenarios, data ingestion often tends to write to small files to improve parallelism, especially in streaming scenarios where continuous data ingestion causes COW tables to continuously generate a large number of small files, which can cause a sharp drop in query performance. To address these issues, Hudi proposes a clustering mechanism to manage the small file problem. Its core idea is to allow parallel writing of multiple small files during data ingestion to improve write performance, while a single process periodically merges small files into large files to manage the data in the data lake. Through this mechanism, Hudi can effectively balance the issues between data ingestion and data querying, ensuring data storage efficiency while effectively reducing data query latency. However, existing clustering mechanisms have the following problems:

[0004] (1) The clustering mechanism does not support concurrent updates. To avoid data inconsistency caused by changes to files that are being rewritten or moved, Hudi implements snapshot isolation for File Groups. Modifications are not allowed during clustering; if an update operation is committed at this time, it will be rejected and an exception will be raised.

[0005] (2) The clustering mechanism is not friendly to incremental queries. Data processed by clustering will appear as "new" data in the incremental query results, but in reality, no data content has been modified. This process only involves rewriting data from small files to large files, so it may be incorrectly identified as incremental data.

[0006] (3) Excessive execution of synchronous clustering. Since the default trigger condition for generating a schedule is the number of data commits, even small files that don't significantly impact queries will trigger continuous synchronous clustering operations, resulting in significant waste of cluster resources. While asynchronous clustering allows manual task submission to control the number of clustering operations, its trigger condition is also the number of commits. It blocks the generation of new schedules until the committed schedules are completed. Furthermore, asynchronous clustering relies on specific configurations, requiring professionals to monitor and analyze cluster performance to adjust various parameters for optimal results.

[0007] Therefore, how to reasonably utilize Hudi's clustering mechanism to optimize data layout, reduce unnecessary clustering operations to avoid the above problems, and improve the efficiency of data querying is an important issue faced in data lake warehouse scenarios. Summary of the Invention

[0008] To address the shortcomings of existing technologies, this invention proposes a data lake warehouse clustering method based on query and metadata anomaly detection.

[0009] In this invention, firstly, the performance metrics of historical query tasks from the Presto engine are used to save them as a training dataset. Then, the dataset is cleaned and fed into an ECOD anomaly detection algorithm based on the empirical cumulative distribution function for training, resulting in an anomaly score for each query task. Tasks exceeding a predetermined threshold are identified as potentially anomaly-prone. Table information from the anomaly tasks is obtained, and the path information of the anomaly table in the underlying storage is obtained through metadata stored in Hive. The proportion of small files under this path is calculated, and a secondary determination is made to determine whether the anomaly is caused by small files. Finally, the anomaly table is designated as the target of a clustering operation, and scheduling and execution tasks are submitted synchronously to merge small files into larger files of similar or identical size.

[0010] Using this invention, the number of times Hudi clustering operations are executed can be controlled as much as possible, which to some extent avoids the problems of unsupported concurrent updates, unfriendly incremental queries, and waste of cluster resources caused by the clustering mechanism; in addition, compared with before clustering, this invention can significantly improve the efficiency of Presto accessing Hudi COW tables.

[0011] This invention also proposes a data lake warehouse clustering system based on query and metadata anomaly detection, which is used to implement the above-mentioned data lake warehouse clustering method based on query and metadata anomaly detection.

[0012] Terminology Explanation:

[0013] Hudi: Hadoop Upserts Deletes and Incrementals, is an open-source data lake management framework for managing and processing batch and streaming data in big data environments.

[0014] Presto: A distributed SQL query engine for querying large-scale data. Its goal is to perform fast, interactive query analysis in large-scale data warehouses, supporting multiple data sources and data formats.

[0015] COW Tables: COW (Copy On Write) tables are a data storage mode that uses append-only writing. Each time new data is written, the old file and the new data are merged to generate a completely new data file, while retaining historical versions of the data file.

[0016] Hudi Clustering: A core service of the Hudi framework used for data layout optimization in data lakes. It has two operating modes: synchronous (Inline) and asynchronous (Async). The execution mechanism is divided into two phases: scheduling and execution.

[0017] File Group: A file structure in Hudi tables used to control file versions; each File Group has a unique File ID.

[0018] Snapshot Isolation: A transaction isolation level that allows transactions to execute on a consistent snapshot of the data without being affected by changes made by other concurrent transactions;

[0019] HDFS: HDFS (Hadoop Distributed File System) is a distributed file system for storing and managing large-scale data. An HDFS cluster typically consists of a NameNode and multiple DataNodes, which work together to store and manage data.

[0020] The empirical cumulative distribution function (ECF) is a statistical tool used to visualize the distribution of data. It represents the cumulative percentage of observations in a given dataset that are less than or equal to a given value. It is a non-parametric descriptive statistical method that makes no assumptions about the data distribution but directly utilizes observed data points.

[0021] Gaussian kernel density estimation (KDE) is a nonparametric statistical method that uses a Gaussian kernel function to smoothly estimate the distribution of data.

[0022] Presto Web Service: The Presto service provides a web UI interface for checking and monitoring the current running status of the Presto cluster, as well as displaying a list of running query tasks. Each query task contains all query performance metrics, and JSON data containing performance metrics can be obtained by accessing the corresponding API.

[0023] The technical solution of this invention is as follows:

[0024] Data lake warehouse clustering methods based on query and metadata anomaly detection include:

[0025] Data acquisition and preprocessing: Collect various performance metrics of historical query tasks, select some relevant performance metrics as the initial dataset, and perform preprocessing operations on the initial dataset;

[0026] EC-GKDE algorithm for anomaly detection: The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks and to make an initial judgment on the anomaly query tasks.

[0027] Metadata information anomaly determination: Obtain the associated table of the abnormal query task, obtain the actual HDFS path information in the Hive metadata service based on the table information of the associated table of the abnormal query task, calculate the proportion of small files under the actual HDFS path, and make a second determination as to whether the associated table of the abnormal query task is an abnormal table.

[0028] Cluster optimization: Use the information from the exception table as the basis for synchronous clustering operations, submit clustering tasks, and merge small files into large files.

[0029] According to a preferred embodiment of the present invention, various performance indicators of historical query tasks are collected, a portion of the relevant performance indicators are selected as the initial dataset, and preprocessing operations are performed on the initial dataset; including:

[0030] The JSON data for each query task is obtained through the Presto Web service. The JSON data for each query task is parsed one by one to obtain all performance metrics for each query task and then stored persistently.

[0031] Filter some performance metrics, and find a correlation between the filtered performance metrics and query anomalies caused by small files;

[0032] Data cleaning includes: handling missing values, duplicate values, and outliers in the filtered dataset, i.e., the filtered performance metrics; deleting records with missing data, complete duplicates, or outliers; and standardizing the storage units and time units of the dataset.

[0033] According to a preferred embodiment of the present invention, the preprocessed dataset is trained using the EC-GKDE algorithm based on the empirical cumulative distribution function and the Gaussian kernel density estimation method to obtain anomaly scores for all query tasks, and an initial determination of anomalous query tasks is made; including:

[0034] Tail probability estimation: For each data point in the dataset, the empirical cumulative distribution function of a univariate model is used to estimate the left tail ECDF probability and right tail ECDF probability of that data point in all dimensions.

[0035] Tail weighting coefficients: Normalize the data in the same dimension to the [0,1] interval, use the Gaussian kernel function to calculate the kernel density of the data in each dimension, and determine the weights of the left tail ECDF probability and the right tail ECDF probability based on the data point with the highest density in each dimension; if the data point with the highest density in each dimension is located on the right side of the data, it means that the outliers are concentrated on the left side, so increase the weight of the left tail ECDF probability; otherwise, increase the weight of the right tail ECDF probability.

[0036] Tail probability aggregation: The tail probabilities are aggregated by considering only the left tail, only the right tail, and considering both the left and right tail ECDF probabilities, respectively, to obtain the anomaly score for each sample.

[0037] Anomaly score selection: The probability is represented using the negative logarithmic space. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the aggregated probabilities of the left tail, right tail, and tail considering weights.

[0038] Abnormal Task Determination: After obtaining the abnormal score, determine which query tasks are considered abnormal tasks based on the threshold set in the actual scenario.

[0039] Further preferred, the left tail ECDF probability and right tail ECDF probability The calculation formulas are shown in equations (I) and (II):

[0040]

[0041] In equations (I) and (II), is the j-th feature value of the i-th sample in the input dataset, l{·} is the indicator function, n is the number of samples, and z is the data point currently being calculated.

[0042] A further preferred Gaussian kernel function K(x) is shown in equation (III):

[0043]

[0044] A further preferred approach is to use a Gaussian kernel function to summarize the contributions of all data points to the current target point within the same dimension, thereby obtaining the probability density estimate of the current target point, as shown in Equation (IV):

[0045]

[0046] In equation (IV), The bandwidth parameter in the j-th dimension is determined using Silverman's rule, where n is the number of samples. It is the sample standard deviation.

[0047] A further preferred approach is to calculate the density of all data points in each dimension j, and select the value of the point with the highest density in the interval [0,1] as the tail weight coefficient W. j .

[0048] Further optimization, for each sample X i Aggregate tail probabilities to obtain anomaly score O(X). i );

[0049] Aggregate the ECDF probabilities of the left tail to obtain the anomaly score O. left-only (X i The formula is shown in equation (V):

[0050]

[0051] Aggregate the right tail ECDF probabilities to obtain the anomaly score O. right-only (X i The formula is shown in equation (VI):

[0052]

[0053] The tail probability O is aggregated based on the tail weighting coefficient. w (X i The formula is shown in equation (VII):

[0054]

[0055] A further preferred method for selecting anomaly scores is as shown in equation (VIII):

[0056] O i =max{O left-only (X i ),O right-only (X i ),O w (X i )}(VIII)

[0057] In equation (VIII), O i Let O represent the anomaly score of the i-th sample. left-only (X i ), O right-only (X i ), O w (X i The results are obtained from equations (V), (VI), and (VII) above, respectively.

[0058] According to a preferred embodiment of the present invention, the process includes obtaining the association table of the abnormal query task, obtaining the actual HDFS path information in the Hive metadata service based on the table information of the association table of the abnormal query task, calculating the proportion of small files under the actual HDFS path, and making a secondary determination as to whether the association table of the abnormal query task is an abnormal table; including:

[0059] First, each query task has a query field, which includes the SQL statement submitted by the user. The sqlparse library is used to parse the SQL statement and extract the table information of the related tables involved.

[0060] Secondly, the table information is used to obtain the actual HDFS path information of the abnormal table stored in the Hive metadata service by Hudi. Based on the path information, all file names and sizes are recursively listed and output to a CSV file.

[0061] Finally, the system filters and retains the stored data and the latest version of the basic files. Files smaller than the Hudi small file threshold are identified as small files. The number of small files and the total number of files are counted, and the proportion of small files to the total number of files is calculated. If the proportion exceeds the predetermined threshold, the associated table is judged as an abnormal table for the second time.

[0062] According to a preferred embodiment of the present invention, the information in the exception table is used as the basis for the synchronous clustering operation to submit the clustering task and merge small files into large files; including:

[0063] Configure parameters for the synchronous scheduling task, disable automatic triggering of synchronous cluster scheduling and execution, specify the cluster task submission mode, set the upper limit for small and large files, submit the synchronous execution task, perform synchronous clustering operations, and merge a large number of small files into a very small number of large files.

[0064] A computer device includes a memory and a processor, the memory storing a computer program, the processor executing the computer program to implement the steps of a data lake warehouse clustering method based on query and metadata anomaly detection.

[0065] A computer-readable storage medium having a computer program stored thereon, the computer program, when executed by a processor, implementing the steps of a data lake warehouse clustering method based on query and metadata anomaly detection.

[0066] A data lake warehouse clustering system based on query and metadata anomaly detection includes:

[0067] Data acquisition and preprocessing module: Collects various performance indicators of historical query tasks, selects some relevant performance indicators as the initial dataset, and performs preprocessing operations on the initial dataset;

[0068] EC-GKDE algorithm for anomaly detection: The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks and to make an initial judgment on the anomaly query tasks.

[0069] Metadata information anomaly determination module: obtain the associated table of the abnormal query task, obtain the actual HDFS path information in Hive metadata service based on the table information of the associated table of the abnormal query task, calculate the proportion of small files under the actual HDFS path, and make a second determination as to whether the associated table of the abnormal query task is an abnormal table.

[0070] Cluster optimization module: Uses information from the exception table as the basis for synchronous clustering operations, submits clustering tasks, and merges small files into large files.

[0071] The beneficial effects of this invention are as follows:

[0072] This invention proposes a method to analyze various performance metrics of historical query tasks provided by the Presto engine using an anomaly detection algorithm. Query tasks that may be abnormal are marked, and a secondary determination is made to determine whether the anomaly is caused by small files. Then, a clustering operation is performed on the abnormal table, merging small files into larger files of similar or identical size, reducing storage resource overhead and significantly decreasing the time required for query tasks. Furthermore, using this method for clustering operations allows control over the number of clustering operations, mitigating problems such as lack of support for concurrent updates, unfriendly incremental queries, and wasted cluster resources associated with clustering mechanisms. Attached Figure Description

[0073] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0074] Figure 1 This is a flowchart illustrating a data lake warehouse clustering method based on query and metadata anomaly detection.

[0075] Figure 2 This is a flowchart illustrating the EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method.

[0076] Figure 3 This is a flowchart illustrating the process of determining abnormal metadata information. Detailed Implementation

[0077] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings and embodiments, but is not limited thereto.

[0078] Example 1

[0079] Data lake warehouse clustering methods based on query and metadata anomaly detection, such as Figure 1 As shown, it includes:

[0080] Data acquisition and preprocessing: Collect various performance metrics of historical query tasks, select some relevant performance metrics as the initial dataset, and perform preprocessing operations on the initial dataset;

[0081] EC-GKDE algorithm for anomaly detection: The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks and to make an initial judgment on the anomaly query tasks.

[0082] Metadata information anomaly determination: Obtain the associated table of the abnormal query task, obtain the actual HDFS path information in the Hive metadata service based on the table information of the associated table of the abnormal query task, calculate the proportion of small files under the actual HDFS path, and make a second determination as to whether the associated table of the abnormal query task is an abnormal table.

[0083] Cluster optimization: Use the information from the exception table as the basis for synchronous clustering operations, submit clustering tasks, and merge small files into large files.

[0084] Example 2

[0085] The data lake warehouse clustering method based on query and metadata anomaly detection described in Example 1 differs in that:

[0086] Collect various performance metrics from historical query tasks, select some relevant performance metrics as the initial dataset, and perform preprocessing operations on the initial dataset, including:

[0087] The system retrieves a set of historical query task IDs from the Presto system table "system.runtime.queries". Using this ID set, it retrieves the JSON data for each query task from the Presto web service. The JSON data for each query task is then parsed to obtain all performance metrics, which are persistently stored in a CSV file. These metrics include SQL statement, task submission time, actual runtime, CPU time, number of Drivers generated, input data volume, and output data volume.

[0088] Selected performance metrics were filtered, and the filtered performance metrics were correlated with query anomalies caused by small files; these included: elapsed_time, cpu_time, driver_count, input_data, output_data, and stage_count. These features represent process runtime, process processor time, number of Drivers generated, amount of data input to the process, amount of data output by the process, and number of Stages generated by task parsing, respectively.

[0089] Data cleaning includes: handling missing values, duplicate values, and outliers in the filtered dataset, i.e., the filtered performance metrics; deleting records with missing data, complete duplicates, or outliers; and standardizing the storage and time units of the dataset, converting the units to bytes and milliseconds.

[0090] The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks, enabling initial identification of anomalous query tasks; including:

[0091] like Figure 2 As shown, the core idea of ​​the EC-GKDE algorithm is to observe rare events that appear at the tail of the data distribution to identify outliers in the dataset. The main steps include: tail probability estimation, tail weight coefficient, tail probability aggregation, outlier score selection, and outlier task determination.

[0092] Tail probability estimation: For each data point in the dataset, the empirical cumulative distribution function (ECDF) of a univariate model is used to estimate the left tail ECDF probability and right tail ECDF probability of that data point across all dimensions (features); this is done through the empirical distribution of each dimension in the dataset, without relying on complex parameter tuning;

[0093] Tail weighting coefficients: Data in the same dimension are normalized to the [0,1] interval. The kernel density of the data in each dimension is calculated using a Gaussian kernel function. The weights of the left tail ECDF probability and the right tail ECDF probability are determined based on the data point with the highest density in each dimension. If the data point with the highest density in each dimension is located on the right side of the data, it indicates that outliers are concentrated on the left side, so the weight of the left tail ECDF probability is increased; otherwise, the weight of the right tail ECDF probability is increased. For example, if the value of the data point with the highest density in this dimension in the [0,1] interval is X, then the weight of the left tail ECDF is X, and the weight of the right tail is (1-X).

[0094] Tail probability aggregation: The tail probabilities are aggregated by considering only the left tail, only the right tail, and considering both the left and right tail ECDF probabilities, respectively, to obtain the anomaly score for each sample.

[0095] Anomaly score selection: The probability is represented using the negative logarithmic space. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the aggregated probabilities of the left tail, right tail, and tail considering weights.

[0096] Abnormal Task Determination: After obtaining the abnormal score, determine which query tasks are considered abnormal tasks based on the threshold set in the actual scenario; for example, after sorting the abnormal scores from high to low, take the smallest value of the top 10% as the threshold, and tasks with scores greater than this threshold are identified as abnormal tasks.

[0097] In summary, the EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, enhances its robustness under different data distributions by determining the left and right tail probability weight coefficients through kernel density estimation. Furthermore, it does not rely on complex parameter tuning and exhibits higher efficiency, accuracy, and interpretability. The preprocessed dataset is input into the EC-GKDE algorithm, which outputs anomaly scores for each query task. These anomaly scores are sorted from largest to smallest, and query tasks with anomaly scores exceeding a predetermined threshold are initially classified as anomalous.

[0098] This is accomplished through the empirical distribution of each dimension in the dataset, without relying on complex parameter tuning; left-tailed ECDF probability. and right tail ECDF probability The calculation formulas are shown in equations (I) and (II):

[0099]

[0100] In equations (I) and (II), is the j-th feature value of the i-th sample in the input dataset, l{·} is the indicator function, n is the number of samples, and z is the data point currently being calculated.

[0101] The Gaussian kernel function K(x) is shown in equation (III):

[0102]

[0103] By using a Gaussian kernel function within the same dimension, the contribution of all data points to the current target point (z) is summarized to obtain the probability density estimate of the current target point, as shown in Equation (IV):

[0104]

[0105] In equation (IV), The bandwidth parameter (Bandwidth) in the j-th dimension is determined using Silverman's rule, where n is the number of samples. It is the sample standard deviation.

[0106] Calculate the density of all data points in each dimension j, and select the value of the point with the maximum density in the interval [0,1] as the tail weight coefficient W. j .

[0107] For each sample X i Aggregate tail probabilities to obtain anomaly score O(X). i );

[0108] Aggregate the ECDF probabilities of the left tail to obtain the anomaly score O. left-only (X i The formula is shown in equation (V):

[0109]

[0110] Aggregate the right tail ECDF probabilities to obtain the anomaly score O. right-only (X i The formula is shown in equation (VI):

[0111]

[0112] The tail probability O is aggregated based on the tail weighting coefficient. w (X i The formula is shown in equation (VII):

[0113]

[0114] The selection of anomaly scores is shown in equation (VIII):

[0115] O i =max{O left-only (X i ),O right-only (X i ),O w (X i )}(VIII)

[0116] In equation (VIII), O i Let O represent the anomaly score of the i-th sample. left-only (X i ), O right-only (X i ), O w (X i The results are obtained from equations (V), (VI), and (VII) above, respectively.

[0117] like Figure 3 As shown, the associated table of the abnormal query task is obtained. Based on the table information of the associated table of the abnormal query task, the actual HDFS path information in the Hive metadata service is obtained, and the proportion of small files under the actual HDFS path is calculated. A second determination is made as to whether the associated table of the abnormal query task is an abnormal table; including:

[0118] First, each query task has a query field, which includes the SQL statement submitted by the user. The sqlparse library is used to parse the SQL statement and extract the table information of the related tables involved.

[0119] Secondly, using the table information, the actual HDFS path information of the abnormal table stored by Hudi in the Hive metadata service is obtained. Based on the path information, all file names and sizes are recursively listed and output to a CSV file; including:

[0120] The integration of Hudi and Hive stores the metadata of Hudi tables in the Hive Metastore. The HDFS path of Hudi tables can be found through the Hive Metastore API. The table information obtained in the previous step is submitted to Hive as the value of the "DESCRIBEFORMATTED table_name;" command, which returns the storage information about the table. The value of the path parameter is the location of the table in HDFS, i.e., the path information of the table.

[0121] Finally, since the Hudi COW table contains metadata files and multiple version files, it is necessary to filter and retain the basic files with the latest storage data and version. Files smaller than the Hudi small file threshold are identified as small files. The number of small files and the total number of files are counted, and the proportion of small files to the total number of files is calculated. If it exceeds the predetermined threshold (20%), the associated table is judged as an abnormal table for the second time.

[0122] The information from the exception table is used as the basis for the synchronous clustering operation. The clustering task is submitted to merge small files into large files; including:

[0123] Use the path information obtained in the previous step as the value of the command "hdfs dfs-ls-hR path_information" to recursively list all file names and sizes and output them to a CSV file; filter the CSV file for basic files with the suffix "parquet", then group the files with the same FileID in their filenames, compare the file timestamps, and keep the file with the latest timestamp in each group;

[0124] The Hudi clustering mechanism executes a threshold based on the value of the small.file.limit parameter. Files smaller than this threshold are considered "small files". The number of small files is counted against the total number of files. The proportion of small files to the total number of files is calculated. If the proportion exceeds a predetermined threshold (20%), the table is considered an abnormal table, and the abnormal table path is recorded as table_path.

[0125] The exception table information is used as the basis for the synchronous clustering task. The clustering task is submitted to merge small files into a large file. The synchronous clustering task requires calling the Hudi-provided interface "hudi-utilities-bundle.jar" jar package and the "org.apache.hudi.utilities.HoodieClusteringJob" class, including:

[0126] Submit a synchronized schedule task, including:

[0127] Set the mode configuration parameter to schedule to specify a scheduled task;

[0128] Set the base-path configuration parameter to table_path, and specify the path of the exception table as the target path of the cluster task;

[0129] Set the table-name configuration parameter to the name of the exception table to specify the target table for the cluster task;

[0130] Setting the `hoodie.clustering.async.enabled` configuration parameter to `false` disables automatic triggering of synchronous clustering operations; setting the `hoodie.clustering.schedule.inline` configuration parameter to `false` disables automatic scheduling of the clustering service; setting the `hoodie.clustering.plan.strategy.small.file.limit` configuration parameter to `209715200` (200MB) limits small files participating in the cluster to below 200MB.

[0131] Set the hoodie.clustering.plan.strategy.target.file.max.bytes configuration parameter to 524,288,000 (500MB) to set the maximum target file size to 500MB;

[0132] Submitting a synchronous execution task includes:

[0133] Set the mode configuration parameter to execute to specify that the task will be executed.

[0134] Set the base-path configuration parameter to table_path, and specify the path of the exception table as the target path of the cluster task;

[0135] Set the table-name configuration parameter to the name of the exception table to specify the target table for the cluster task;

[0136] After submitting the synchronization task, Hudi will execute the clustering task, merging a large number of small files into a small number of large files to improve the query speed of the Presto engine, control the number of clustering operations, and reduce the negative impact of the clustering mechanism.

[0137] Example 3

[0138] A computer device includes a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the steps of the data lake warehouse clustering method based on query and metadata anomaly detection as described in Embodiment 1 or 2.

[0139] Example 4

[0140] A computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of the data lake warehouse clustering method based on query and metadata anomaly detection as described in Embodiment 1 or 2.

[0141] Example 5

[0142] A data lake warehouse clustering system based on query and metadata anomaly detection includes:

[0143] Data acquisition and preprocessing module: Collects various performance indicators of historical query tasks, selects some relevant performance indicators as the initial dataset, and performs preprocessing operations on the initial dataset;

[0144] EC-GKDE algorithm for anomaly detection: The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks and to make an initial judgment on the anomaly query tasks.

[0145] Metadata information anomaly determination module: obtain the associated table of the abnormal query task, obtain the actual HDFS path information in Hive metadata service based on the table information of the associated table of the abnormal query task, calculate the proportion of small files under the actual HDFS path, and make a second determination as to whether the associated table of the abnormal query task is an abnormal table.

[0146] Cluster optimization module: Uses information from the exception table as the basis for synchronous clustering operations, submits clustering tasks, and merges small files into large files.

[0147] Obviously, the examples listed in the specific embodiments are only a part of the examples of this invention, and not all of them. All other examples obtained by those skilled in the art based on the examples of this invention without inventive effort should fall within the protection scope of this invention.

Claims

1. A data lake warehouse clustering method based on query and metadata anomaly detection, characterized in that, include: Data acquisition and preprocessing: Collect various performance metrics of historical query tasks, select some relevant performance metrics as the initial dataset, and perform preprocessing operations on the initial dataset; EC-GKDE algorithm for anomaly detection: The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks and to make an initial judgment on the anomaly query tasks. Metadata information anomaly determination: Obtain the associated table of the abnormal query task, obtain the actual HDFS path information in the Hive metadata service based on the table information of the associated table of the abnormal query task, calculate the proportion of small files under the actual HDFS path, and make a second determination as to whether the associated table of the abnormal query task is an abnormal table. Cluster optimization: Use the information from the exception table as the basis for the synchronous cluster operation, submit the cluster task, and merge small files into large files; The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain the anomaly scores of all query tasks and to make an initial judgment on the anomaly query tasks. include: Tail probability estimation: For each data point in the dataset, the empirical cumulative distribution function of a univariate model is used to estimate the left tail ECDF probability and right tail ECDF probability of that data point in all dimensions. Tail weighting coefficients: Normalize the data in the same dimension to the [0,1] interval, use the Gaussian kernel function to calculate the kernel density of the data in each dimension, and determine the weights of the left tail ECDF probability and the right tail ECDF probability based on the data point with the highest density in each dimension; if the data point with the highest density in each dimension is located on the right side of the data, it means that the outliers are concentrated on the left side, so increase the weight of the left tail ECDF probability; otherwise, increase the weight of the right tail ECDF probability. Tail probability aggregation: The tail probabilities are aggregated by considering only the left tail, only the right tail, and considering both the left and right tail ECDF probabilities, respectively, to obtain the anomaly score for each sample. Anomaly score selection: The probability is represented using the negative logarithmic space. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the aggregated probabilities of the left tail, right tail, and tail considering weights. Abnormal task determination: After obtaining the abnormal score, determine which query tasks are considered abnormal tasks based on the threshold set in the actual scenario; Retrieve the associated table of the abnormal query task; based on the table information of the associated table of the abnormal query task, obtain the actual HDFS path information in the Hive metadata service; calculate the proportion of small files under the actual HDFS path; and make a secondary determination as to whether the associated table of the abnormal query task is an abnormal table; including: First, each query task has a query field, which includes the SQL statement submitted by the user. The sqlparse library is used to parse the SQL statement and extract the table information of the related tables involved. Secondly, the table information is used to obtain the actual HDFS path information of the abnormal table stored in the Hive metadata service by Hudi. Based on the path information, all file names and sizes are recursively listed and output to a CSV file. Finally, the system filters and retains the stored data and the latest version of the basic files. Files smaller than the Hudi small file threshold are identified as small files. The number of small files and the total number of files are counted, and the proportion of small files to the total number of files is calculated. If the proportion exceeds the predetermined threshold, the associated table is judged as an abnormal table for the second time.

2. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 1, characterized in that, Collect various performance metrics from historical query tasks, select some relevant performance metrics as the initial dataset, and perform preprocessing operations on the initial dataset, including: The JSON data for each query task is obtained through the Presto Web service. The JSON data for each query task is parsed one by one to obtain all the performance metrics for each query task and stored persistently. Filter some performance metrics, and find a correlation between the filtered performance metrics and query anomalies caused by small files; Data cleaning includes: handling missing values, duplicate values, and outliers in the filtered dataset, i.e., the filtered performance metrics; deleting records with missing data, complete duplicates, or outliers; and standardizing the storage units and time units of the dataset.

3. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 1, characterized in that, Left tail ECDF probability and right tail ECDF probability The calculation formulas are shown in equations (I) and (II): （I）； (II); In equations (I) and (II), is the j-th feature value of the i-th sample in the input dataset, l{·} is the indicator function, n is the number of samples, and z is the data point currently being calculated.

4. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 1, characterized in that, Gaussian kernel function As shown in equation (III): 。 5. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 3, characterized in that, By using a Gaussian kernel function within the same dimension, the contribution of all data points to the current target point is summarized, resulting in a probability density estimate of the current target point, as shown in Equation (IV): ； In equation (IV), The bandwidth parameter in the j-th dimension is determined using Silverman's rule, where n is the number of samples. It is the sample standard deviation; Calculate the density of all data points in each dimension j, and select the value of the point with the maximum density in the interval [0,1] as the tail weight coefficient. .

6. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 5, characterized in that, For each sample Aggregate tail probabilities to obtain anomaly scores ; Aggregate the left tail ECDF probabilities to obtain anomaly scores. The formula is shown in equation (V): ； Aggregate the right tail ECDF probabilities to obtain anomaly scores. The formula is shown in equation (VI): ； Aggregate tail probabilities based on tail weighting coefficients. The formula is shown in equation (VII): ； The selection of anomaly scores is shown in equation (VIII): ； In equation (VIII), Let the outlier score be the score of the i-th sample. , , These are obtained from equations (V), (VI), and (VII) above, respectively.

7. The data lake warehouse clustering method based on query and metadata anomaly detection according to any one of claims 1-6, characterized in that, The information from the exception table is used as the basis for the synchronous clustering operation. The clustering task is submitted to merge small files into large files; including: Configure parameters for the synchronous scheduling task, disable automatic triggering of synchronous cluster scheduling and execution, specify the cluster task submission mode, set the upper limit for small and large files, submit the synchronous execution task, perform synchronous clustering operations, and merge a large number of small files into a very small number of large files.

8. A data lake warehouse clustering system based on query and metadata anomaly detection, characterized in that, include: Data acquisition and preprocessing module: Collects various performance indicators of historical query tasks, selects some relevant performance indicators as the initial dataset, and performs preprocessing operations on the initial dataset; EC-GKDE algorithm for anomaly detection: The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain anomaly scores for all query tasks and to make an initial judgment on the anomaly query tasks. Metadata information anomaly determination module: obtain the associated table of the abnormal query task, obtain the actual HDFS path information in Hive metadata service based on the table information of the associated table of the abnormal query task, calculate the proportion of small files under the actual HDFS path, and make a second determination as to whether the associated table of the abnormal query task is an abnormal table. Cluster optimization module: Uses information from the exception table as the basis for synchronous clustering operations, submits clustering tasks, and merges small files into large files; The EC-GKDE algorithm, based on the empirical cumulative distribution function and Gaussian kernel density estimation method, is used to train the preprocessed dataset to obtain the anomaly scores of all query tasks and to make an initial judgment on the anomaly query tasks. include: Tail probability estimation: For each data point in the dataset, the empirical cumulative distribution function of a univariate model is used to estimate the left tail ECDF probability and right tail ECDF probability of that data point in all dimensions. Tail weighting coefficients: Normalize the data in the same dimension to the [0,1] interval, use the Gaussian kernel function to calculate the kernel density of the data in each dimension, and determine the weights of the left tail ECDF probability and the right tail ECDF probability based on the data point with the highest density in each dimension; if the data point with the highest density in each dimension is located on the right side of the data, it means that the outliers are concentrated on the left side, so increase the weight of the left tail ECDF probability; otherwise, increase the weight of the right tail ECDF probability. Tail probability aggregation: The tail probabilities are aggregated by considering only the left tail, only the right tail, and considering both the left and right tail ECDF probabilities, respectively, to obtain the anomaly score for each sample. Anomaly score selection: The probability is represented using the negative logarithmic space. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the aggregated probabilities of the left tail, right tail, and tail considering weights. Abnormal task determination: After obtaining the abnormal score, determine which query tasks are considered abnormal tasks based on the threshold set in the actual scenario; Retrieve the associated table of the abnormal query task; based on the table information of the associated table of the abnormal query task, obtain the actual HDFS path information in the Hive metadata service; calculate the proportion of small files under the actual HDFS path; and make a secondary determination as to whether the associated table of the abnormal query task is an abnormal table; including: First, each query task has a query field, which includes the SQL statement submitted by the user. The sqlparse library is used to parse the SQL statement and extract the table information of the related tables involved. Secondly, the table information is used to obtain the actual HDFS path information of the abnormal table stored in the Hive metadata service by Hudi. Based on the path information, all file names and sizes are recursively listed and output to a CSV file. Finally, the system filters and retains the stored data and the latest version of the basic files. Files smaller than the Hudi small file threshold are identified as small files. The number of small files and the total number of files are counted, and the proportion of small files to the total number of files is calculated. If the proportion exceeds the predetermined threshold, the associated table is judged as an abnormal table for the second time.

Images