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

By using an anomaly detection algorithm based on Presto and metadata, small files in Hudi COW tables are marked and merged, solving the problem of frequent generation of small files in Hudi's clustering mechanism and improving query efficiency and resource utilization.

CN118939736AActive Publication Date: 2024-11-12SHANDONG COMP SCI CENTNAT SUPERCOMP CENT IN JINAN +1
View PDF 8 Cites 0 Cited by

Patent Information

Application Number
CN202410944068.9
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-15
Publication Date
2024-11-12
Estimated Expiration
2044-07-15

AI Technical Summary

Technical Problem

The existing Hudi clustering mechanism has problems in data lake warehouse scenarios, such as not supporting concurrent updates, being unfriendly to incremental queries, and wasting cluster resources, especially the frequent generation of small files in COW tables, which leads to a decrease in query performance.

Method used

By using historical query task performance metrics based on the Presto engine, and leveraging the ECOD anomaly detection algorithm and metadata information, query tasks that may be abnormal are marked. A secondary determination is then made to determine whether the anomaly is caused by small files, and these are merged into larger files to control the number of clustering operations.

Benefits of technology

It significantly improves the query efficiency of Presto accessing Hudi COW tables, reduces storage resource overhead, lowers query task time, and alleviates the problems of concurrent updates and cluster resource waste.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118939736A_ABST
    Figure CN118939736A_ABST
Patent Text Reader

Abstract

The invention relates to a data lake and warehouse clustering method and system based on query and metadata anomaly detection, and belongs to the technical field of big data computing. Comprising the steps of data acquisition and preprocessing; performing EC-GKDE algorithm anomaly judgment: training the data set after the preprocessing operation to obtain anomaly scores of all query tasks, and performing initial judgment; metadata information abnormity judgment: obtaining an association table of an abnormal query task, obtaining HDFS actual path information in the Hive metadata service, calculating the proportion of small files under the HDFS actual path, and secondarily judging whether the association table of the abnormal query task is an abnormal table or not; and cluster optimization: submitting a cluster task, and merging the small files into a large file. According to the method, the expenditure of storage resources is reduced, and the time consumed by task query is greatly reduced; according to the method, the number of times of clustering operation execution can be controlled, and the problems that concurrent updating is not supported, incremental query is not friendly and cluster resources are wasted due to a clustering mechanism are solved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present 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 Art

[0002] In today's growing multi-source heterogeneous and multi-modal data environment, data lakes have become an efficient and flexible data management solution. Among the existing data lake technologies, using the Hudi data lake architecture and the Presto query engine is one of the mainstream solutions. Hudi is an open source data lake management framework that provides a variety of services for optimizing the storage and management of large-scale data sets, while supporting incremental data synchronization and time travel queries. Presto is a distributed SQL query engine for processing large-scale data analysis queries. It can provide various performance indicators for historical query tasks to help us analyze and optimize query performance. The combination of Hudi and Presto technologies enables the lake architecture to ingest data in real time or batches to meet different data storage requirements, and provide users with fast query services for massive multi-source heterogeneous data.

[0003] Although combining the two can provide more powerful and complete services, Hudi itself also has some potential problems. COW table is one of the two writing modes supported by Hudi. This writing mode will cause the data file to continue to grow, resulting in reduced data writing efficiency. In addition, in the lake warehouse scenario, data ingestion usually tends to write small files to improve parallelism, especially in the streaming scenario where data is continuously ingested, causing the COW table to continuously generate a large number of small files, which will cause a rapid decline in query performance. In response to the above problems, Hudi proposed a clustering mechanism to manage the small file problem. Its core idea is: when ingesting data, it allows multiple small files to be written in parallel to improve writing performance, and at the same time, a process is used to periodically merge small files into large files to manage the data in the data lake. Through this mechanism, Hudi can effectively balance the problems between data ingestion and data query, and can effectively reduce the latency of data query while ensuring data storage efficiency. However, the existing clustering mechanism has the following problems:

[0004] (1) The clustering mechanism does not support concurrent updates. In order to avoid data inconsistency caused by changes to files that are being rewritten or moved, Hudi implements Snapshot Isolation-level isolation for File Groups. When clustering is executed, modifications are not allowed. If an update operation is submitted at this time, it will reject the update and cause an exception.

[0005] (2) The clustering mechanism is not friendly to incremental queries. The clustered data will be treated as "new" data in the incremental query results, but in fact, no data content has been modified. This process only involves rewriting the data in the small file into the large file, so it may be mistakenly identified as incremental data.

[0006] (3) Synchronous clustering is executed too many times. Since the default trigger condition for generating a schedule is the number of commits of the data, even if the small files generated do not have a significant impact on the query, the synchronous clustering operation will be executed continuously, which will cause a large waste of cluster resources. Although asynchronous clustering can control the number of clustering by manually submitting execution tasks, its trigger condition is also the number of commits. Before the submitted schedule is executed, the generation of a new schedule will be blocked. In addition, asynchronous clustering relies on specific configurations and requires professionals to monitor and analyze the operation of the cluster to adjust various parameters to achieve the desired effect.

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

[0008] In view of the shortcomings of the existing technology, the present invention proposes a data lake clustering method based on query and metadata anomaly detection.

[0009] In the present invention, firstly, various performance indicators of historical query tasks of the Presto engine are used and saved as a training data set; then, the data set is cleaned and input into the ECOD anomaly detection algorithm based on the empirical cumulative distribution function for training to obtain the anomaly score of each query task, and the tasks above the predetermined threshold are judged as possibly having anomalies; the table information in the abnormal task is obtained, and the path information of the abnormal table in the underlying storage is obtained through the metadata information stored in Hive, the proportion of small files under the path is calculated, and a secondary judgment is made as to whether the anomaly is caused by a small file problem; finally, the abnormal table is designated as the target of the clustering operation, and the scheduling task and the execution task are submitted synchronously, so that the small files are merged into large files of the same or similar size.

[0010] By using the present invention, the number of executions of Hudi clustering operations can be controlled as much as possible, and the problems of concurrent update support, incremental query unfriendliness and cluster resource waste caused by the clustering mechanism can be avoided to a certain extent; in addition, compared with before clustering, the present invention can significantly improve the efficiency of Presto accessing Hudi COW tables.

[0011] The present 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 amounts of data. Its goal is to perform fast, interactive query analysis in large-scale data warehouses, supporting a variety of data sources and data formats.

[0015] COW table: A COW table (Copy On Write) is a data storage mode that uses append-write. Every time new data is written, the old file and the new data are merged to generate a new data file, and the historical version of the data file is retained;

[0016] Hudi Clustering: A core service of the Hudi framework, used to optimize the data layout of the data lake. It has two operating modes: synchronous (Inline) and asynchronous (Async). The execution mechanism is divided into two stages: scheduling (Schedule) and execution (Execute);

[0017] Flie Group: A file structure in the Hudi table used to control file versions. The same File Group has a unique File ID.

[0018] Snapshot Isolation level 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 usually consists of a name node (NameNode) and multiple data nodes (Data Node), which work together to store and manage data;

[0020] The Empirical Cumulative Distribution Function is a statistical tool used to visualize data distribution. It represents the cumulative percentage of observations in a given data set that are less than or equal to a given value. It is a non-parametric descriptive statistical method that does not make assumptions about the data distribution, but directly uses the 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: A Web UI interface provided by the Presto service for checking and monitoring the current running status of the Presto cluster and displaying a list of query tasks that have been run. Each query task contains all query performance indicators. You can obtain JSON data containing performance indicators by accessing the corresponding API.

[0023] The technical solution of the present invention is:

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

[0025] Data collection and preprocessing: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and perform preprocessing operations on the initial data set;

[0026] EC-GKDE algorithm anomaly determination: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make initial determinations on abnormal query tasks;

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

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

[0029] Preferably, according to the present invention, various performance indicators of historical query tasks are collected, some relevant performance indicators are selected as initial data sets, and preprocessing operations are performed on the initial data sets; including:

[0030] The JSON data of each query task is obtained through the Presto Web service, and the JSON data of each query task is parsed one by one to obtain all the performance indicators of each query task and store them persistently.

[0031] Filter some performance indicators. There is a correlation between the filtered performance indicators and the query anomalies caused by small files.

[0032] Data cleaning includes: processing missing values, duplicate values ​​and outliers for the filtered data set, i.e. the filtered performance indicators, deleting records with missing data, complete duplicates or outliers; and standardizing the storage units and time units of the data set.

[0033] Preferably, according to the present invention, the EC-GKDE algorithm based on the empirical cumulative distribution function and the Gaussian kernel density estimation method is used to train the data set after the preprocessing operation, obtain the abnormal scores of all query tasks, and make an initial judgment on the abnormal query tasks; including:

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

[0035] Tail weight coefficient: Normalize the data of the same dimension to the interval [0,1], 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 according to the data point with the largest density in each dimension; if the data point with the largest density in each dimension is located on the right side of the data, it means that the abnormal points are concentrated on the left side, then increase the weight of the left tail ECDF probability; otherwise, increase the weight of the right tail ECDF probability;

[0036] Tail probability aggregation: Aggregate the tail probabilities from the weights of considering only the left tail, only the right tail, and considering the left tail ECDF probability and the right tail ECDF probability to obtain the abnormal score of each sample;

[0037] Anomaly score selection: Negative logarithmic space is used to represent the probability. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the left tail, right tail, and weighted tail probability aggregation results.

[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 the right-tail ECDF probability The calculation formula is shown in formula (I) (II):

[0040]

[0041] In formula (I) and (II), is the jth eigenvalue of the ith sample in the input data set, l{·} is the indicator function, n is the number of samples, and z is the data point currently being calculated.

[0042] Further preferably, the Gaussian kernel function K(x) is as shown in formula (III):

[0043]

[0044] Further preferably, in the same dimension, the contribution of all data points to the current target point is summarized by a Gaussian kernel function to obtain a probability density estimate of the current target point, as shown in formula (IV):

[0045]

[0046] In formula (IV), is the bandwidth parameter of the jth dimension determined by Silverman's rule, n is the number of samples, is the sample standard deviation.

[0047] Preferably, the density of all data points in each dimension j is calculated, and the value of the point with the maximum density in the interval [0,1] is selected as the tail weight coefficient W j .

[0048] Further preferably, for each sample X i , aggregate the tail probabilities to obtain the anomaly score O(X i );

[0049] Aggregate the left tail ECDF probability to obtain the anomaly score O left-only (X i ), the formula is shown in formula (V):

[0050]

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

[0052]

[0053] Aggregate the tail probability O according to the tail weight coefficient w (X i ), the formula is shown in formula (VII):

[0054]

[0055] Further preferably, the anomaly score is selected as shown in formula (VIII):

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

[0057] In formula (VIII), O i represents the anomaly score of the i-th sample, the anomaly score O left-only (X i ), O right-only (X i ), O w (X i ) are obtained from the above formulas (V), (VI) and (VII) respectively.

[0058] Preferably, according to the present invention, the association table of the abnormal query task is obtained, the actual path information of the HDFS in the Hive metadata service is obtained according to the table information of the association table of the abnormal query task, and the proportion of small files under the actual path of the HDFS is calculated, and it is secondary determined 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 SQL statement is parsed using the sqlparse library to extract the table information of the associated tables involved.

[0060] Secondly, use the table information to obtain the actual HDFS path information of the abnormal table stored by Hudi in the Hive metadata service, recursively list all file names and sizes based on the path information, and output them to a CSV file;

[0061] Finally, the basic files with the latest storage data and versions are selected and retained. 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 ratio of the number of small files to the total number of files is calculated. If it is greater than the established threshold, the association table is determined to be an abnormal table for the second time.

[0062] Preferably, according to the present invention, the information in the exception table is used as the basis for the synchronous clustering operation, the clustering task is submitted, and the small files are merged into a large file; including:

[0063] Configure the parameters of the synchronization scheduling task, turn off the automatic triggering of synchronization cluster scheduling and execution, specify the cluster task submission mode, set the upper limit of small files and large files, submit the synchronization execution task, perform the synchronization cluster operation, and merge a large number of small files into a few large files.

[0064] A computer device includes a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the steps of a data lake warehouse clustering method based on query and metadata anomaly detection are implemented.

[0065] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of a data lake clustering method based on query and metadata anomaly detection.

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

[0067] Data collection and preprocessing module: collects various performance indicators of historical query tasks, selects some relevant performance indicators as the initial data set, and performs preprocessing operations on the initial data set;

[0068] EC-GKDE algorithm anomaly determination: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make initial determinations on abnormal query tasks;

[0069] Metadata information anomaly determination module: obtains the association table of the abnormal query task, obtains the actual path information of HDFS in the Hive metadata service based on the table information of the association table of the abnormal query task, calculates the proportion of small files under the actual path of HDFS, and determines whether the association table of the abnormal query task is an abnormal table;

[0070] Cluster optimization module: uses the information in 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 the present invention are:

[0072] The present invention proposes a method of using an anomaly detection algorithm to analyze various performance indicators of historical query tasks provided by the Presto engine, marking query tasks that may be abnormal, and performing a secondary determination as to whether the anomaly is caused by small files, and then performing a clustering operation on the abnormal table to merge small files into large files of similar or identical size, thereby reducing the overhead of storage resources and greatly reducing the time required for query tasks. In addition, using this method to perform clustering operations can control the number of clustering operations performed, thereby alleviating the problems of concurrent update support, incremental query unfriendliness, and cluster resource waste caused by the clustering mechanism. BRIEF DESCRIPTION OF THE DRAWINGS

[0073] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. 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 creative work.

[0074] Figure 1 The flowchart of the data lake warehouse clustering method based on query and metadata anomaly detection is shown in Figure 1.

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

[0076] Figure 3 The figure is a flowchart for determining abnormal metadata information. DETAILED DESCRIPTION

[0077] The technical solution of the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments of the specification, 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, including:

[0080] Data collection and preprocessing: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and perform preprocessing operations on the initial data set;

[0081] EC-GKDE algorithm anomaly determination: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make initial determinations on abnormal query tasks;

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

[0083] Cluster optimization: Use the information in the exception table as the basis for synchronous cluster operations, submit cluster 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 is different in that:

[0086] Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and perform preprocessing operations on the initial data set; including:

[0087] Obtain the ID set of historical query tasks through the Presto system table "system.runtime.queries", use the ID set to obtain the JSON data of each query task from the Presto Web service, parse the JSON data of each query task one by one to obtain all performance indicators of each query task and store them persistently in a CSV file. All performance indicators, such as SQL statements, task submission time, actual running time, CPU time, number of Driver generation, input data volume, and output data volume, etc.

[0088] Filter some performance indicators. The filtered performance indicators are correlated with query anomalies caused by small files, including elapsed_time, cpu_time, driver_count, input_data, output_data, and stage_count. These features represent the process running time, the processor time occupied by the process, the number of Drivers generated, the amount of data input to the process, the amount of data output by the process, and the number of stages generated by task parsing.

[0089] Data cleaning includes: processing missing values, duplicate values ​​and outliers for the filtered data set, i.e. the filtered performance indicators, and deleting records with missing data, complete duplicates or outliers; standardizing the storage units and time units of the data set, and converting the units into bytes and milliseconds.

[0090] Through the EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method, the preprocessed data set is trained to obtain the anomaly scores of all query tasks and make initial judgments on abnormal query tasks; including:

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

[0092] Tail probability estimation: For each data point in the dataset, the univariate empirical cumulative distribution function (ECDF) is used to estimate the left-tail ECDF probability and right-tail ECDF probability of the data point in all dimensions (features); this is done through the empirical distribution of each dimension in the dataset and does not rely on complex parameter adjustments;

[0093] Tail weight coefficient: Normalize the data of the same dimension to the interval [0,1], 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 according to 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 abnormal points are concentrated on the left side, then increase the weight of the left-tail ECDF probability; otherwise, increase the weight of the right-tail ECDF probability; for example, if the value of the data point with the highest density in this dimension in the interval [0,1] 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: Aggregate the tail probabilities from the weights of considering only the left tail, only the right tail, and considering the left tail ECDF probability and the right tail ECDF probability to obtain the abnormal score of each sample;

[0095] Anomaly score selection: Negative logarithmic space is used to represent the probability. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the left tail, right tail, and weighted tail probability aggregation results.

[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 greater than the threshold are considered abnormal tasks;

[0097] In general, the EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method determines the left and right tail probability weight coefficients through kernel density estimation, which enhances the robustness of the algorithm under different data distributions, does not rely on complex parameter adjustments, and has higher efficiency, accuracy and interpretability. The preprocessed data set is input into the EC-GKDE algorithm, and the anomaly score of each query task is output. The anomaly scores are sorted from large to small, and the query tasks with anomaly scores greater than the established threshold are preliminarily determined as abnormal query tasks.

[0098] This is done through the empirical distribution of each dimension in the data set, without relying on complex parameter adjustments; left-tail ECDF probability and the right-tail ECDF probability The calculation formula is shown in formula (I) (II):

[0099]

[0100] In formula (I) and (II), is the jth eigenvalue of the ith sample in the input data set, 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 formula (III):

[0102]

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

[0104]

[0105] In formula (IV), is the bandwidth parameter of the jth dimension determined by the Silverman rule, n is the number of samples, 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 largest density in the interval [0,1] as the tail weight coefficient W j .

[0107] For each sample X i , aggregate the tail probabilities to obtain the anomaly score O(X i );

[0108] Aggregate the left tail ECDF probability to obtain the anomaly score O left-only (X i ), the formula is shown in formula (V):

[0109]

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

[0111]

[0112] Aggregate the tail probability O according to the tail weight coefficient w (X i ), the formula is shown in formula (VII):

[0113]

[0114] The anomaly score is selected as shown in formula (VIII):

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

[0116] In formula (VIII), O i represents the anomaly score of the i-th sample, the anomaly score O left-only (X i ), O right-only (X i ), O w (X i ) are obtained from the above formulas (V), (VI) and (VII) respectively.

[0117] like Figure 3 As shown, the association table of the abnormal query task is obtained, the actual path information of the HDFS in the Hive metadata service is obtained according to the table information of the association table of the abnormal query task, and the proportion of small files under the actual path of the HDFS is calculated, and a secondary determination is made as to whether the association 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 SQL statement is parsed using the sqlparse library to extract the table information of the associated tables involved.

[0119] Secondly, use the table information to obtain the actual HDFS path information of the abnormal table stored by Hudi in the Hive metadata service, recursively list all file names and sizes based on the path information, and output them to a CSV file; including:

[0120] The integration of Hudi and Hive will store the metadata of the Hudi table in the Hive Metastore. You can use the HiveMetastore API to find the HDFS path of the Hudi table. Submit the table information obtained in the previous step as the value of the "DESCRIBEFORMATTED table_name;" command to Hive. In the returned Storage Information about the table, the value of the path parameter is the location of the table in HDFS, that is, the path information of the table.

[0121] Finally, since there are metadata files and multi-version files in the Hudi COW table, it is necessary to filter and retain the basic files with the latest storage data and versions. 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 the number of small files to the total number of files is calculated. If it is greater than the established threshold (20%), the association table is judged as an abnormal table for the second time.

[0122] Use the information in the exception table as the basis for synchronous clustering operations, submit clustering tasks, and merge small files into large files; including:

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

[0124] The execution threshold of the Hudi clustering mechanism is the value of the small.file.limit parameter. Files with a size smaller than this threshold are identified as "small files". The number of small files and the total number of files are counted, and the proportion of the number of small files to the total number of files is calculated. If it is greater than the established threshold (20%), the table is secondarily determined to be an abnormal table, and the abnormal table path is recorded as table_path.

[0125] Use the exception table information as the basis for the synchronous clustering task, submit the clustering task, and merge small files into large files; the synchronous clustering task needs to call the interface "hudi-utilities-bundle.jar" jar package and "org.apache.hudi.utilities.HoodieClusteringJob" class provided by Hudi, including:

[0126] Submit synchronization schedule tasks, including:

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

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

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

[0130] Set the hoodie.clustering.async.enabled configuration parameter to false to disable automatic triggering of synchronous clustering operations; set the hoodie.clustering.schedule.inline configuration parameter to false to disable automatic scheduling of clustering services; set the hoodie.clustering.plan.strategy.small.file.limit configuration parameter to 209715200 (200MB) to limit the size of small files participating in clustering to less than 200MB;

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

[0132] Submitting synchronous execution tasks includes:

[0133] Set the mode configuration parameter to execute, specifying the task to be executed;

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

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

[0136] After submitting the synchronous execution task, Hudi will execute the clustering task and merge 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 executions of the clustering operation, and reduce the negative impact of the clustering mechanism.

[0137] Example 3

[0138] A computer device includes a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the steps of the data lake warehouse clustering method based on query and metadata anomaly detection described in Example 1 or 2 are implemented.

[0139] Example 4

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

[0141] Example 5

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

[0143] Data collection and preprocessing module: collects various performance indicators of historical query tasks, selects some relevant performance indicators as the initial data set, and performs preprocessing operations on the initial data set;

[0144] EC-GKDE algorithm anomaly determination: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make initial determinations on abnormal query tasks;

[0145] Metadata information anomaly determination module: obtains the association table of the abnormal query task, obtains the actual path information of HDFS in the Hive metadata service based on the table information of the association table of the abnormal query task, calculates the proportion of small files under the actual path of HDFS, and determines whether the association table of the abnormal query task is an abnormal table;

[0146] Cluster optimization module: uses the information in 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 implementation are only some examples of the present invention, not all examples. Based on the examples of the present invention, all other examples obtained by technicians without creative work should fall within the protection scope of the present invention.

Claims

1. A data lake clustering method based on query and metadata anomaly detection, characterized in that: include: Data collection and preprocessing: Collect various performance indicators of historical query tasks, select some relevant performance indicators as the initial data set, and perform preprocessing operations on the initial data set; EC-GKDE algorithm anomaly determination: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make initial determinations on abnormal query tasks; Metadata information anomaly determination: obtain the association table of the abnormal query task, obtain the HDFS actual path information in the Hive metadata service based on the table information of the association table of the abnormal query task, calculate the proportion of small files under the HDFS actual path, and make a secondary determination whether the association table of the abnormal query task is an abnormal table; Cluster optimization: Use the information in the exception table as the basis for synchronous cluster operations, submit cluster tasks, and merge small files into large files.

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

3. The data lake clustering method based on query and metadata anomaly detection according to claim 1 is characterized in that: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make an initial judgment on the abnormal query tasks. include: Tail probability estimation: For each data point in the data set, the univariate empirical cumulative distribution function is used to estimate the left tail ECDF probability and right tail ECDF probability of the data point in all dimensions; Tail weight coefficient: Normalize the data of the same dimension to the interval [0,1], 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 according to the data point with the largest density in each dimension; if the data point with the largest density in each dimension is located on the right side of the data, it means that the abnormal points are concentrated on the left side, then increase the weight of the left tail ECDF probability; otherwise, increase the weight of the right tail ECDF probability; Tail probability aggregation: Aggregate the tail probabilities from the weights of considering only the left tail, only the right tail, and considering the left tail ECDF probability and the right tail ECDF probability to obtain the abnormal score of each sample; Anomaly score selection: Negative logarithmic space is used to represent the probability. A lower probability corresponds to a higher anomaly score. The maximum value is selected as the final anomaly score from the left tail, right tail, and weighted tail probability aggregation results. 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.

4. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 3 is characterized in that: Left Tail ECDF Probability and the right-tail ECDF probability The calculation formula is shown in formula (I) (II): In formula (I) and (II), is the jth eigenvalue of the ith sample in the input data set, l{·} is the indicator function, n is the number of samples, and z is the data point currently being calculated.

5. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 3 is characterized in that: The Gaussian kernel function K(x) is shown in formula (III):

6. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 3 is characterized in that: In the same dimension, the contribution of all data points to the current target point is summarized through the Gaussian kernel function to obtain the probability density estimate of the current target point, as shown in formula (IV): In formula (IV), is the bandwidth parameter of the jth dimension determined by Silverman's rule, n is the number of samples, 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 largest density in the interval [0,1] as the tail weight coefficient W j .

7. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 3 is characterized in that: For each sample X i , aggregate the tail probabilities to obtain the anomaly score O(X i ); Aggregate the left tail ECDF probabilities to obtain the anomaly score O left-only (X i ), the formula is shown in formula (V): Aggregate the right tail ECDF probability to obtain the anomaly score O right-only (X i ), the formula is shown in formula (VI): Aggregate the tail probability O according to the tail weight coefficient w (X i ), the formula is shown in formula (VII): The anomaly score is selected as shown in formula (VIII): A i =max{O left-only (X i ),A right-only (X i ),A w (X i )}(VIII) In formula (VIII), O i represents the anomaly score of the i-th sample, the anomaly score O left-only (X i ), O right-only (X i ), O w (X i ) are obtained from the above formulas (V), (VI) and (VII) respectively.

8. The data lake warehouse clustering method based on query and metadata anomaly detection according to claim 1 is characterized in that: Obtain the associated table of the abnormal query task, obtain the actual path information of the HDFS 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 path of the HDFS, and secondarily determine 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 SQL statement is parsed using the sqlparse library to extract the table information of the associated tables involved. Secondly, use the table information to obtain the actual HDFS path information of the abnormal table stored by Hudi in the Hive metadata service, recursively list all file names and sizes based on the path information, and output them to a CSV file; Finally, the basic files with the latest storage data and versions are selected and retained. 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 ratio of the number of small files to the total number of files is calculated. If it is greater than the established threshold, the association table is determined to be an abnormal table for the second time.

9. The data lake warehouse clustering method based on query and metadata anomaly detection according to any one of claims 1 to 8, characterized in that: Use the information in the exception table as the basis for synchronous clustering operations, submit clustering tasks, and merge small files into large files; including: Configure the parameters of the synchronization scheduling task, turn off the automatic triggering of synchronization cluster scheduling and execution, specify the cluster task submission mode, set the upper limit of small files and large files, submit the synchronization execution task, perform the synchronization cluster operation, and merge a large number of small files into a few large files.

10. Data lake clustering system based on query and metadata anomaly detection, characterized by: include: Data collection and preprocessing module: collects various performance indicators of historical query tasks, selects some relevant performance indicators as the initial data set, and performs preprocessing operations on the initial data set; EC-GKDE algorithm anomaly determination: The EC-GKDE algorithm based on the empirical cumulative distribution function and Gaussian kernel density estimation method is used to train the preprocessed data set to obtain the anomaly scores of all query tasks and make initial determinations on abnormal query tasks; Metadata information anomaly determination module: obtains the association table of the abnormal query task, obtains the actual path information of HDFS in the Hive metadata service based on the table information of the association table of the abnormal query task, calculates the proportion of small files under the actual path of HDFS, and determines whether the association table of the abnormal query task is an abnormal table; Cluster optimization module: uses the information in the exception table as the basis for synchronous clustering operations, submits clustering tasks, and merges small files into large files.

Citation Information

Patent Citations

  • Load balancing optimizing method and system for distributed file system

    CN103973583A

  • A small file processing method and a distributed system

    CN109684294A

  • File aggregation compatible method and device, computer equipment and storage medium

    CN113704027A

  • Small file storage method and system based on HBase multi-terminal fusion

    CN115544149A

  • Hudi asynchronous data clustering method and system based on hotspot prediction

    CN117633232A