Online analysis and processing methods for query statements, computer equipment and storage media
By preprocessing the query statements and selecting a performance prediction model, the problem of OLAP engine selection is solved, and efficient processing in dynamic data environments is achieved.
Patent Information
- Application Number
- CN202410577800.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-05-10
- Publication Date
- 2026-11-17
- Estimated Expiration
- 2044-05-10
AI Technical Summary
In existing technologies, it is difficult to select the best OLAP engine in business scenarios with dynamically changing data volumes and multiple data tables, resulting in low business processing efficiency.
By preprocessing the received query statements to determine the timeliness of the query, at least two online analytical processing engines are selected, and the most suitable engine is selected for processing based on the performance prediction model. This includes building the performance prediction model, training sample data, optimizing the loss function, and selecting the engine with the best performance prediction result.
It improves the processing efficiency of query statements and ensures that the most suitable OLAP engine is selected for processing in environments with different data volumes and table variations.
Smart Images

Figure CN118427211B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the fields of Internet and big data application technology, and in particular to a method for online analysis and processing of query statements, computer equipment, and storage medium. Background Technology
[0002] Online Analytical Processing (OLAP) is a technology for organizing large business databases and supporting complex analysis. OLAP enables analysts to quickly, consistently, and interactively observe information from various perspectives to achieve a deep understanding of the data. Currently, the selection of an OLAP engine is mainly based on the specific business scenario, the size of the data, and the complexity of the query statements. When making technology selections, software developers will choose a specific execution engine based on current or foreseeable future conditions to complete the data queries.
[0003] However, in practical applications, most data sources consist of more than one table, and the data volume varies significantly between these tables. Furthermore, the data volume of each table is dynamically changing, especially in the internet finance sector, where the data volume grows exponentially with the increase in user numbers. Therefore, current practices often fail to select the optimal OLAP engine for each table at different stages. Summary of the Invention
[0004] This application provides a query statement online analysis and processing method, computer equipment, and storage medium to solve the technical problem of low business processing efficiency in the prior art.
[0005] Firstly, this application provides an online parsing and processing method for query statements, including:
[0006] The received query statements are preprocessed to determine the timeliness of the query.
[0007] Based on timeliness, at least two online analytical processing engines are required to correspond to the query statement;
[0008] Based on the performance prediction model of each online analytical processing engine, determine the performance prediction results for each online analytical processing engine.
[0009] The performance prediction results of all online analytical processing engines are compared, and the target online analytical processing engine for executing the query is determined based on the comparison results.
[0010] In one embodiment, the received query statement is preprocessed to determine the timeliness of the query, including:
[0011] Parse the query statement to obtain the parsed results;
[0012] Based on the parsing results, determine whether the query statement is a real-time query.
[0013] In one embodiment, based on timeliness, at least two online analytical processing engines are determined corresponding to the query statement, including:
[0014] When the query is a real-time query, at least two online analytical processing engines corresponding to the query are identified as Type I online analytical processing engines, which include ClickHouse, Doris and ElasticSearch.
[0015] If the query is not a real-time query, at least two online analytical processing engines corresponding to the query are identified as second-type online analytical processing engines. Second-type online analytical processing engines include Kylin, Clickhouse, Doris, and ElasticSearch.
[0016] In one embodiment, the performance prediction model of each online analytical processing engine includes a query processing sub-model and a metric prediction sub-model. The query processing sub-model is built on the online analytical processing engine and is used to process query statements. The metric prediction sub-model is used to predict the performance metrics of the query processing sub-model based on the processing results of the query statements by the query processing sub-model.
[0017] In one embodiment, the training process for the performance prediction model of each online analytics engine includes:
[0018] Obtain the sample dataset, where each sample has a corresponding artificial label;
[0019] The query processing sub-model processes each sample, and the index prediction sub-model determines the prediction performance index when the query processing sub-model processes each sample.
[0020] Calculate the loss function based on the manual label and prediction performance metrics corresponding to each sample;
[0021] Adjust the model parameters of the indicator prediction sub-model, return the steps of processing each sample according to the query statement processing sub-model, and determine the prediction performance index of the indicator prediction sub-model when the query statement processing sub-model processes each sample.
[0022] The model parameters of the indicator prediction sub-model are determined until the loss function is minimized.
[0023] In one embodiment, the indicator prediction sub-model includes multiple encoding layers; adjusting the model parameters of the indicator prediction sub-model includes:
[0024] The model parameters of each coding layer are adjusted by the gradient magnitude of each coding layer.
[0025] In one embodiment, the performance prediction result for each online analytical processing (OLAP) engine is determined based on the performance prediction model for each OLAP engine, including:
[0026] Based on the performance prediction model of each online analytical processing engine, determine the performance metrics of each online analytical processing engine when executing query statements;
[0027] The performance metrics are normalized to obtain the normalized performance metrics results.
[0028] Based on the normalization results, the performance prediction results for each online analytical processing engine are determined.
[0029] In one embodiment, determining the target online analytical processing engine for executing the query statement based on the comparison results includes:
[0030] The OLAP engine with the best performance prediction result is used as the target OLAP engine for the query statement, and the query statement is executed based on the target OLAP engine to return the query results.
[0031] Secondly, this application provides an online query statement analysis and processing apparatus, comprising:
[0032] The preprocessing module is used to preprocess the received query statements and determine the timeliness of the query statements.
[0033] The first determination module is used to determine at least two online analytical processing engines corresponding to the query statement based on the timeliness.
[0034] The second determining module is used to determine the performance prediction result of each online analytical processing engine based on the performance prediction model of each online analytical processing engine.
[0035] The third determination module is used to compare the performance prediction results of all online analytical processing engines and determine the target online analytical processing engine to execute the query statement based on the comparison results.
[0036] Thirdly, this application also provides a computer device, the computer comprising: a processor, and a memory communicatively connected to the processor;
[0037] The memory stores the instructions that the computer executes;
[0038] The processor implements the above method when executing computer execution instructions stored in memory.
[0039] Fourthly, this application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the above-described method.
[0040] Fifthly, this application also provides a computer program product, including computer execution instructions, which, when executed by a processor, implement the above-described method.
[0041] This application provides a query statement online analytical processing (OLAP) method, computer device, and storage medium. The method includes: preprocessing a received query statement to determine its timeliness; determining at least two OLAP engines corresponding to the query statement based on its timeliness; determining the performance prediction result of each OLAP engine based on its performance prediction model; comparing the performance prediction results of all OLAP engines, and determining the target OLAP engine for executing the query statement based on the comparison result. Compared to existing technologies that can only process queries using a single OLAP engine, this application constructs a performance prediction model for each OLAP engine. When executing each query statement, it selects the most suitable OLAP engine based on the performance prediction model corresponding to each OLAP engine, effectively improving the processing efficiency of the query statement. Attached Figure Description
[0042] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.
[0043] Figure 1 This is a schematic diagram illustrating an application scenario of the online analysis and processing method for query statements provided in one embodiment.
[0044] Figure 2 The flowchart of an online parsing and processing method for query statements provided in one embodiment Figure 1 ;
[0045] Figure 3 The flowchart of an online parsing and processing method for query statements provided in one embodiment Figure 2 ;
[0046] Figure 4 The flowchart of an online parsing and processing method for query statements provided in one embodiment Figure 3 ;
[0047] Figure 5This is a schematic diagram of the online query analysis and processing device provided in one embodiment;
[0048] Figure 6 This is an internal structural diagram of a computer device provided in one embodiment.
[0049] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation
[0050] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.
[0051] In the description of this application, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of this application, "multiple" means two or more, unless otherwise explicitly specified.
[0052] First, let me explain the terms used in this application:
[0053] OLAP: Online Analytical Processing, generally refers to high-performance online analytical processing of big data without transactions. Commonly used OLAP frameworks include Kylin, Apache Druid, ClickHouse, Doris, Presto, and ElasticSearch. General concepts and components of an OLAP framework include: multidimensional data models, fast aggregation, multidimensional query languages, data slicing and partitioning, caching optimization, and metadata management. OLAP is widely used in internet finance, such as for calculating user retention, user behavior analysis, and user funnel analysis.
[0054] OLAP frameworks can generally be divided into three main categories: MOLAP, ROLAP, and HOLAP. MOLAP: Multidimensional OLAP, utilizes multidimensional cubes and pre-computation techniques for complex multidimensional data analysis; Kylin is a typical example of MOLAP. ROLAP (Relational On-line Analysis Processing): A relational database-based OLAP system that performs dynamic multidimensional analysis on data stored in relational databases (rather than multidimensional databases). It allows direct access to the underlying data storage using SQL query language, providing flexible data analysis capabilities; Presto is an example. HOLAP: Hybrid OLAP, combining MOLAP and ROLAP. It offers a balanced approach, incorporating the advantages of different approaches. Examples include Apache Druid, ClickHouse, and Doris.
[0055] The method provided in the embodiments of this application can be applied to, for example, Figure 1 In the application environment shown, terminal 102 is used to obtain query requests triggered by the user. Terminal 102 communicates with server 104 via the network, sending the obtained query request to server 104. Server 104 generates a query statement based on the query request, processes the query statement, and returns the query result. The data storage system can store the data that server 104 needs to process. The data storage system can be integrated on server 104 or placed on the cloud or other network servers.
[0056] The terminal can be, but is not limited to, various personal computers, laptops, smartphones, tablets, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, and smart in-vehicle devices. Portable wearable devices can include smartwatches, smart bracelets, and head-mounted devices. Server 104 can be implemented using a standalone server or a server cluster consisting of multiple servers.
[0057] In one embodiment, such as Figure 2 As shown, an online parsing method for query statements has been provided, which is applied to... Figure 1 Taking server 104 as an example, the following steps are included:
[0058] Step 202: Preprocess the received query statement to determine the query timeliness;
[0059] Step 204: Based on timeliness, determine at least two online analytical processing engines corresponding to the query statement;
[0060] Step 206: Determine the performance prediction result for each online analytical processing engine based on the performance prediction model for each online analytical processing engine.
[0061] Step 208: Compare the performance prediction results of all online analytical processing engines, and determine the target online analytical processing engine for executing the query statement based on the comparison results.
[0062] A query statement refers to a query triggered by a user's query request. When querying a database, query statements include SQL queries and non-SQL queries. Query statements are diverse and can even be nested. The query timeliness indicates whether the query statement needs to return results in real-time. It should be noted that different types of query statements have different built-in syntax functions; therefore, when preprocessing query statements, a unified preprocessing method applicable to all types of query statements is required.
[0063] The timeliness of a query statement includes real-time and non-real-time queries. Different query timeliness levels correspond to different OLAP engine types; for example, Kylin does not support real-time queries. The OLAP engine corresponding to a query statement is determined based on its timeliness, and different timeliness levels may correspond to different OLAP engines. Furthermore, the method provided in this application embodiment is applicable when the query timeliness corresponds to at least two OLAP engines. This necessitates obtaining performance prediction results through the performance prediction model of each OLAP engine, thereby selecting the OLAP engine with the optimal performance prediction result to process the query statement. When the query timeliness only determines one OLAP engine, no selection is required and there is no other choice.
[0064] Online Analytical Processing (OLAP) engine performance prediction models are used to obtain the performance of each OLAP engine when processing queries. Each OLAP engine has its own performance prediction model, trained with sample data. When processing a query, the query is input into the OLAP engine's performance prediction model to obtain the performance prediction result. This performance prediction result includes predictions for various performance metrics, such as processing time, CPU utilization, and memory utilization. It's important to note that the performance metrics predicted by each OLAP engine's performance prediction model are determined during model construction. Typically, when building performance prediction models for multiple OLAP engines, the same performance metrics are considered. Furthermore, each OLAP engine's performance prediction model can predict performance metrics individually or simultaneously for multiple metrics. The final performance prediction result for each OLAP engine's query is composed of the predictions for all performance metrics.
[0065] When building a performance prediction model, you can use Hugging Face's Transformers library to load a pre-trained BERT model and its corresponding tokenizer. Choose a suitable pre-trained BERT model, such as "bert-base-uncased". Define a Transformer Encoder as a fully connected layer model architecture. This model accepts the output of the BERT model as input and predicts query performance metrics, such as latency, CPU usage, and memory usage, through one or more Transformer Encoder layers. You can also add Layer-wise Adaptive Rate Scaling (LARS) to each Transformer Encoder layer to adjust the learning rate and prevent overfitting.
[0066] For a query statement, after determining the performance prediction results for each corresponding OLAP engine, a suitable OLAP engine is selected as the target OLAP engine to process the query statement based on its execution requirements. For example, considering all performance metrics, the OLAP engine corresponding to the best performance prediction result is selected as the target OLAP engine; alternatively, a single performance metric can be considered individually or given priority, and the performance prediction results can be processed by setting selection weights for the performance metric, and a suitable OLAP engine is selected as the target OLAP engine based on the processing result.
[0067] The method provided in the above embodiments preprocesses the received query statement to determine its timeliness; based on the timeliness, it determines at least two Online Analytical Processing (OLAP) engines corresponding to the query statement; based on the performance prediction model of each OLAP engine, it determines the performance prediction result of each OLAP engine; it compares the performance prediction results of all OLAP engines and determines the target OLAP engine for executing the query statement based on the comparison result. Compared with the prior art, which can only process query statements according to a predetermined OLAP engine, this application constructs a performance prediction model for each OLAP engine. When executing each query statement, it selects the most suitable OLAP engine to process the query statement based on the performance prediction model corresponding to each OLAP engine, thus effectively improving the processing efficiency of query statements.
[0068] In one embodiment, the received query statement is preprocessed to determine the timeliness of the query, including:
[0069] Parse the query statement to obtain the parsed results;
[0070] Based on the parsing results, determine whether the query statement is a real-time query.
[0071] The query statement is parsed and analyzed, breaking it down into statements that the computer can recognize, and extracting the statement parameter information to form the parsing result. Based on the parsing result, it is determined whether the query is a real-time query. The above embodiment, by parsing the query statement and obtaining its statement parameter information, can quickly determine the timeliness of the query.
[0072] In one embodiment, such as Figure 3 As shown, based on timeliness, at least two online analytical processing engines are identified for the query statement, including:
[0073] Step 302: When the query statement is a real-time query, determine at least two online analytical processing engines corresponding to the query statement as the first type of online analytical processing engine. The first type of online analytical processing engine includes ClickHouse, Doris and ElasticSearch.
[0074] Step 304: If the query statement is not a real-time query, determine that at least two online analytical processing engines corresponding to the query statement are second-type online analytical processing engines. The second-type online analytical processing engines include Kylin, Clickhouse, Doris, and ElasticSearch.
[0075] In database queries, among all types of OLAP engines, Kylin does not support real-time queries. Therefore, before using a performance prediction model, it is necessary to parse the SQL or non-SQL query statement to determine whether it is a real-time query. If it is a real-time query, only performance prediction for ClickHouse, Doris, and ElasticSearch will be performed; otherwise, performance prediction for all four OLAP engines (Kylin, ClickHouse, Doris, and ElasticSearch) will be performed.
[0076] In the method provided in the above embodiments, the corresponding OLAP engine is determined for the query statement based on the query timeliness, so as to avoid performing performance prediction on OLAP engines that are not suitable for the query statement, which would affect the processing efficiency.
[0077] In one embodiment, the performance prediction model of each online analytical processing engine includes a query processing sub-model and a metric prediction sub-model. The query processing sub-model is built on the online analytical processing engine and is used to process query statements. The metric prediction sub-model is used to predict the performance metrics of the query processing sub-model based on the processing results of the query statements by the query processing sub-model.
[0078] The query processing sub-model is used to process query statements according to the OLAP engine, simulating the actual execution process of the query statements. The metric prediction sub-model is used to evaluate the processing process of the query processing sub-model and determine the performance metrics of the query processing sub-model. Specifically, in one embodiment, such as... Figure 4 As shown, the training process for the performance prediction model of each online analytics engine includes:
[0079] Step 402: Obtain the sample dataset, where each sample has a corresponding artificial label;
[0080] Step 404: Process each sample according to the query statement processing sub-model, and determine the prediction performance index when the query statement processing sub-model processes each sample.
[0081] Step 406: Calculate the loss function based on the manual label and prediction performance index corresponding to each sample;
[0082] Step 408: Adjust the model parameters of the indicator prediction sub-model, and return to the steps of processing each sample according to the query statement processing sub-model, and determining the prediction performance index of the indicator prediction sub-model when the query statement processing sub-model processes each sample.
[0083] Step 410 continues until the loss function is minimized, determining the model parameters of the indicator prediction sub-model.
[0084] A query processing sub-model and an indicator prediction sub-model are built based on a pre-defined model, and the entire model is trained using labeled training data. During training, model parameters are adjusted by minimizing the loss function. For example, in one embodiment, the learning rate is adaptively adjusted using LARS (Layer-wise Adaptive Rate Scaling) in each Transformer Encoder layer to prevent overfitting. Specifically, a loss function suitable for the regression task is defined, such as mean squared error (MSE). A suitable optimizer, such as the Adam optimizer, is selected, and the learning rate is specified. LARS is used in the optimizer to adaptively adjust the learning rate of each layer to prevent overfitting. Advantages of LARS:
[0085] (1) LARS is an adaptive learning rate adjustment method that can dynamically adjust the learning rate according to the gradient magnitude of each layer, thereby better balancing the training speed between different layers.
[0086] (2) Due to the adaptive adjustment of the learning rate, LARS can more effectively avoid gradient explosion or vanishing problems during training, which helps to improve the training stability of the model.
[0087] (3) LARS can train deep neural networks more effectively, reduce gradient propagation problems caused by deep network structures, and thus reduce the risk of overfitting.
[0088] Specifically, in this embodiment, a pre-trained BERT model and its corresponding tokenizer can be loaded using Hugging Face's Transformers library. A suitable pre-trained BERT model is selected, such as 'bert-base-uncased'. A Transformer Encoder is defined as the model architecture with fully connected layers. This model accepts the output of the BERT model as input and predicts query latency, CPU usage, and memory usage through one or more Transformer Encoder layers. In each Transformer Encoder layer, LARS (Layer-wise Adaptive Rate Scaling) is added to adjust the learning rate to prevent overfitting.
[0089] Because the query statements used to input data are diverse, including SQL and non-SQL statements, and can also be nested, it's impossible to determine the specific categories in advance. If there are only single-table queries, then pre-sorting based on all SQL query syntax is feasible. However, actual business queries involve multi-table joins, built-in query functions from various frameworks, and even non-SQL queries from Elasticsearch. Therefore, pre-sorting based on all SQL query syntax is insufficient, lacking universality and scalability. Therefore, this patent uses the Tokenizer from the BERT model for data preprocessing.
[0090] The SQL or non-SQL query statement is tokenized using a tokenizer, and the tokenized input is converted into tensor form. The sample dataset is split into an 80% training set and a 20% test set to ensure a reasonable dataset distribution. After BERT's tokenizer preprocessing of an SQL or non-SQL query, the output is typically a dictionary containing several key pieces of information, including input_ids, token_type_ids, and attention_mask. This information constitutes the model's input. Below is an example:
[0091] Suppose we have an SQL query: SELECT * FROM table WHERE id = 1; after BERT's tokenizer preprocessing, the output might look like this:
[0092] {
[0093] 'input_ids':[101,4297,1008,2013,2795,2073,2795,1136,102,2349,102], # Tokenized representation of the input
[0094] 'token_type_ids':[0,0,0,0,0,0,0,0,0,0,0],#token_type_ids, because it is a single-line input, all are 0.
[0095] 'attention_mask': [1,1,1,1,1,1,1,1,1,1,1]#attention_mask, all tokens are valid}
[0096] 'input_ids': The index value of each token in the vocabulary after the SQL statement is tokenized. For example, the tokens corresponding to [101,4297,1008,2013,2795,2073,2795,1136,102] are [CLS], SELECT,*,FROM,table,WHERE,id,=,[SEP].
[0097] 'token_type_ids': Identifiers used to distinguish different sentences. Since this is a single-sentence input, all values are 0.
[0098] 'attention_mask': A binary mask used to indicate which tokens are valid and which are padding tokens. Because this is a valid SQL query, all tokens are valid, and their corresponding positions are set to 1.
[0099] After training the performance prediction model using the training set data, evaluate the model's performance on the test set, calculating the model's loss value and evaluation metrics, such as mean squared error (MSE) or R-squared value. The model's performance is then checked by evaluating its generalization ability.
[0100] In the method provided in the above embodiments, the performance prediction model of each OLAP engine is pre-trained using a sample dataset, and then the query statement is processed based on the trained performance prediction model to obtain the performance prediction result of each OLAP engine. This method can cover all OLAP engines, thereby selecting the optimal OLAP engine to process the query statement.
[0101] In one embodiment, the performance prediction result for each online analytical processing (OLAP) engine is determined based on the performance prediction model for each OLAP engine, including:
[0102] Based on the performance prediction model of each online analytical processing engine, determine the performance metrics of each online analytical processing engine when executing query statements;
[0103] The performance metrics are normalized to obtain the normalized performance metrics results.
[0104] Based on the normalization results, the performance prediction results for each online analytical processing engine are determined.
[0105] The performance prediction model must include at least one performance metric. When there are multiple performance metrics, the model can output each metric simultaneously. Since each performance metric has a different value and unit (e.g., time consumption is measured in seconds, while CPU utilization is a percentage), all performance metrics need to be normalized before determining the prediction result. For example, if the time consumption exceeds 3 seconds, the normalized time_use can be set to 1; if the time consumption is less than 3 seconds, the normalized time_use can be set to time / 3. The values of CPU utilization (cup_use) and memory utilization (memory_use) are already between 0 and 1, so their original values can be used. The performance prediction result is then determined based on the normalized result.
[0106] Furthermore, in determining performance prediction results through normalization, the degree of influence of each performance indicator can be determined based on actual usage. For example, a weight can be set for each performance indicator to represent the emphasis on that indicator. For instance, in an application scenario, if the weight of time consumption is set to 80%, the weight of CPU usage is 10%, and the weight of memory utilization is 10%, then the performance prediction result could be:
[0107] result=0.8time_use+0.1cup_use+0.1memory_use;
[0108] It should be noted that the weight of each performance metric can be changed in real time according to query requirements.
[0109] In the method provided in the above embodiments, the evaluation results of all performance indicators are evaluated using the same reference standard through normalization processing to evaluate the processing performance of the OLAP engine, thereby avoiding data inaccuracies caused by direct data superposition.
[0110] In one embodiment, determining the target online analytical processing engine for executing the query statement based on the comparison results includes:
[0111] The OLAP engine with the best performance prediction result is used as the target OLAP engine for the query statement, and the query statement is executed based on the target OLAP engine to return the query results.
[0112] It should be understood that although the steps in the flowcharts of the above embodiments are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the above embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.
[0113] Based on the same inventive concept, this application also provides an online query statement analysis and processing device. The solution provided by this device is similar to the solution described in the above method. Therefore, the specific limitations of one or more online query statement analysis and processing device embodiments provided below can be found in the limitations of the online query statement analysis and processing method above, and will not be repeated here.
[0114] In one embodiment, such as Figure 5 As shown, an online query statement analysis and processing apparatus 500 is provided, including a preprocessing module 501, a first determining module 502, a second determining module 503, and a third determining module 504, wherein:
[0115] The preprocessing module 501 is used to preprocess the received query statement and determine the query timeliness of the query statement;
[0116] The first determining module 502 is used to determine at least two online analytical processing engines corresponding to the query statement based on the timeliness.
[0117] The second determining module 503 is used to determine the performance prediction result of each online analytical processing engine based on the performance prediction model of each online analytical processing engine.
[0118] The third determination module 504 is used to compare the performance prediction results of all online analytical processing engines and determine the target online analytical processing engine for executing the query statement based on the comparison results.
[0119] In one embodiment, the preprocessing module 501 is further configured to:
[0120] Parse the query statement to obtain the parsed results;
[0121] Based on the parsing results, determine whether the query statement is a real-time query.
[0122] In one embodiment, the first determining module 502 is further configured to:
[0123] When the query is a real-time query, at least two online analytical processing engines corresponding to the query are identified as Type I online analytical processing engines, which include ClickHouse, Doris and ElasticSearch.
[0124] If the query is not a real-time query, at least two online analytical processing engines corresponding to the query are identified as second-type online analytical processing engines. Second-type online analytical processing engines include Kylin, Clickhouse, Doris, and ElasticSearch.
[0125] In one embodiment, the second determining module 503 is further configured to determine that the performance prediction model of each online analytical processing engine includes a query statement processing sub-model and an indicator prediction sub-model. The query statement processing sub-model is built based on the online analytical processing engine and is used to process query statements. The indicator prediction sub-model is used to predict the performance indicators of the query statement processing sub-model based on the processing results of the query statement by the query statement processing sub-model.
[0126] In one embodiment, the second determining module 503 is further configured to determine the training process of the performance prediction model for each online analytics engine, including:
[0127] Obtain the sample dataset, where each sample has a corresponding artificial label;
[0128] The query processing sub-model processes each sample, and the index prediction sub-model determines the prediction performance index when the query processing sub-model processes each sample.
[0129] Calculate the loss function based on the manual label and prediction performance metrics corresponding to each sample;
[0130] Adjust the model parameters of the indicator prediction sub-model, return the steps of processing each sample according to the query statement processing sub-model, and determine the prediction performance index of the indicator prediction sub-model when the query statement processing sub-model processes each sample.
[0131] The model parameters of the indicator prediction sub-model are determined until the loss function is minimized.
[0132] In one embodiment, the second determining module 503 is further configured to:
[0133] The model parameters of each coding layer are adjusted by the gradient magnitude of each coding layer.
[0134] In one embodiment, the second determining module 503 is further configured to:
[0135] Based on the performance prediction model of each online analytical processing engine, determine the performance metrics of each online analytical processing engine when executing query statements;
[0136] The performance metrics are normalized to obtain the normalized performance metrics results.
[0137] Based on the normalization results, the performance prediction results for each online analytical processing engine are determined.
[0138] In one embodiment, the third determining module 504 is further configured to:
[0139] The OLAP engine with the best performance prediction result is used as the target OLAP engine for the query statement, and the query statement is executed based on the target OLAP engine to return the query results.
[0140] The modules in the online query analysis and processing device described above can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of a computer device in software form, so that the processor can call and execute the operations corresponding to each module.
[0141] like Figure 6 As shown in the figure, this application embodiment provides a computer device 600, which includes a memory 601 and a processor 602.
[0142] Among them, memory 601 is used to store computer-executable instructions that can be executed by the processor;
[0143] The processor 602 implements the various steps of the method in the above embodiments when executing computer execution instructions. For details, please refer to the relevant descriptions in the foregoing method embodiments.
[0144] Optionally, the memory 601 can be either standalone or integrated with the processor 602. When the memory 601 is set up independently, the computer device also includes a bus for connecting the memory 601 and the processor 602.
[0145] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the steps of the methods described above.
[0146] This application also provides a computer program product, including computer execution instructions, which, when executed by a processor, implement the various steps in the methods described above.
[0147] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer execution instructions in the computer program are executed, they can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.
[0148] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0149] The above embodiments are merely illustrative of several implementation methods of this application, and their descriptions are relatively specific and detailed. However, they should not be construed as limiting the scope of this application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.
Claims
1. A method for online analysis and processing of query statements, characterized in that, include: The received query statement is preprocessed to determine the timeliness of the query statement; Based on the timeliness, at least two online analytical processing engines are identified corresponding to the query statement; Based on the performance prediction model of each online analytical processing engine, determine the performance prediction results for each online analytical processing engine. Compare the performance prediction results of all online analytical processing engines, and determine the target online analytical processing engine to execute the query statement based on the comparison results; The performance prediction model for each online analytical processing engine includes a query processing sub-model and an indicator prediction sub-model. The query processing sub-model is built based on the online analytical processing engine and is used to process query statements. The indicator prediction sub-model is used to predict the performance indicators of the query processing sub-model based on the processing results of the query statements by the query processing sub-model. The training process for the performance prediction model of each online analytics engine includes: Obtain a sample dataset, wherein each sample in the sample dataset has a corresponding artificial label; The query statement processing sub-model processes each sample, and the index prediction sub-model determines the prediction performance index when the query statement processing sub-model processes each sample. Calculate the loss function based on the manual label and prediction performance metrics corresponding to each sample; The steps of adjusting the model parameters of the indicator prediction sub-model, returning to the process of each sample according to the query statement processing sub-model, and the indicator prediction sub-model determining the prediction performance index when the query statement processing sub-model processes each sample. The model parameters of the indicator prediction sub-model are determined until the loss function is minimized.
2. The method according to claim 1, characterized in that, The preprocessing of the received query statement to determine the timeliness of the query statement includes: Parse the query statement to obtain the parsing result of the query statement; Based on the parsing results, determine whether the query statement is a real-time query.
3. The method according to claim 2, characterized in that, The step of determining at least two online analytical processing engines corresponding to the query statement based on the timeliness includes: When the query statement is a real-time query, at least two online analytical processing engines corresponding to the query statement are identified as first type online analytical processing engines, including ClickHouse, Doris and ElasticSearch; If the query statement is not a real-time query, at least two online analytical processing engines corresponding to the query statement are identified as second type online analytical processing engines, including Kylin, Clickhouse, Doris, and ElasticSearch.
4. The method according to claim 1, characterized in that, The indicator prediction sub-model includes multiple encoding layers; adjusting the model parameters of the indicator prediction sub-model includes: The model parameters of each coding layer are adjusted by the gradient magnitude of each coding layer.
5. The method according to claim 1, characterized in that, The step of determining the performance prediction result for each online analytical processing (OLAP) engine based on its performance prediction model includes: Based on the performance prediction model of each online analytical processing engine, determine the performance metrics of each online analytical processing engine when executing the query statement; The performance indicators are normalized to obtain the normalized results of the performance indicators; Based on the normalization results, the performance prediction results for each online analytical processing engine are determined.
6. The method according to claim 1, characterized in that, The step of determining the target online analytical processing engine for executing the query statement based on the comparison results includes: The online analytical processing engine with the best performance prediction result is used as the target online analytical processing engine for the query statement, and the query statement is executed according to the target online analytical processing engine to return the query result.
7. A computer device, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in any one of claims 1 to 6.
8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1 to 6.
Citation Information
Patent Citations
Multi-dimensional data analysis method and system based on hybrid engine
CN112286954A
Calculation engine determination method and device, storage medium and computer equipment
CN117009374A
