Hive-based query plan optimization method and device

Abstract

The invention provides a Hive-based query plan optimization method and device, is applied to the technical field of big data, and is used for improving data query efficiency and accuracy. The method comprises the following steps: detecting that at least one table in a query range of a first query plan has data update, and obtaining update data of the at least one table; updating statistical information of the target data warehouse based on the update data; multiple alternative query plans corresponding to the target query statement are generated based on the updated statistical information, the execution cost of each alternative query plan in the multiple alternative query plans is evaluated based on the updated statistical information, and the execution cost is used for indicating the size of resources needed for executing each alternative query plan; and updating the first query plan based on the alternative query plan with the minimum execution cost.

Description

Technical Field

[0001] The present application relates to the field of big data technology, and in particular to a Hive-based query plan optimization method and device. Background Art

[0002] With the continuous development of big data technology, Hive, as a commonly used data warehouse tool, plays a vital role in big data processing and analysis. However, existing Hive systems rely on static information when generating query plans, resulting in execution plans that are unable to adapt to actual data updates, reducing query efficiency and accuracy.

[0003] In view of this, how to improve data query efficiency and accuracy is an urgent problem to be solved. Summary of the Invention

[0004] The present application provides a Hive-based query plan optimization method and device for improving data query efficiency and accuracy.

[0005] In a first aspect, embodiments of the present application provide a Hive-based query plan optimization method applicable to any electronic device with processing capabilities, the method comprising:

[0006] Detecting that data is updated in at least one table within a query scope of a first query plan, and obtaining updated data of the at least one table; the first query plan is an optimal query plan generated based on a target query statement, and the query scope belongs to a target data warehouse;

[0007] updating statistical information of the target data warehouse based on the updated data, the statistical information including data distribution of a specific data type in the target data warehouse and data indicating characteristics of the specific data type;

[0008] Generate multiple alternative query plans corresponding to the target query statement based on the updated statistical information, and evaluate the execution cost of each of the multiple alternative query plans based on the updated statistical information, where the execution cost indicates the amount of resources required to execute each alternative query plan;

[0009] The first query plan is updated based on the alternative query plan with the smallest execution cost.

[0010] In this solution, when at least one table update is detected within the query scope of the first query plan, the statistical information of the target data warehouse is updated based on the updated data of the at least one table. Multiple new alternative query plans are then generated based on the updated statistical information. The execution cost of each alternative query plan is evaluated, and the first query plan is updated based on the alternative query plan with the lowest execution cost. In this way, during the execution of the first query plan, the statistical information is dynamically adjusted based on the table data updates, ensuring that the statistical information can promptly reflect data changes. The first query plan is then updated based on the updated statistical information, ensuring that the first query plan maintains the current optimal query plan, thereby improving data query efficiency and accuracy.

[0011] Optionally, detecting that there is data update for at least one table within the query scope of the first query plan includes: determining the update cycle and update range of each table based on the preset update frequency, data volume and priority of each table in the target data warehouse; the priority is used to indicate the importance of each table; detecting the data update status of each table in the target data warehouse according to the update cycle of each table, and when detecting that there is data update for any table in the target data warehouse, obtaining the update range of any table and determining whether the update range overlaps with the query range of the first query plan.

[0012] Optionally, updating the statistical information of the target data warehouse based on the updated data includes: based on the updated data of at least one table, calculating the statistical values ​​of all data contained in each data type in each table, each partition in each table, and each column of data in each table, the data distribution of a specific data type, and data used to indicate the characteristics of the specific data type.

[0013] Optionally, the execution cost of each alternative query plan in multiple alternative query plans is evaluated based on the updated statistical information, including: determining the task complexity of each alternative query plan and the total amount of data in the query scope based on the updated statistical information; the query scope includes at least one item of table, partition, and column; and evaluating the execution cost of each alternative query plan based on the task complexity of each alternative query plan and the total amount of data in the query scope.

[0014] Optionally, evaluating the execution cost of each alternative query plan in multiple alternative query plans based on the updated statistical information also includes: obtaining multiple first alternative query plans generated based on the statistical information before the update; if there is a target first alternative query plan in the multiple first alternative query plans that has the same operation as any alternative query plan in the multiple alternative query plans, obtaining the original execution cost required for each operation of the target first alternative query plan; evaluating the cost adjustment item for each operation of any alternative query plan based on the updated statistical information; the cost adjustment item is used to indicate the resources that need to be increased or decreased based on the original execution cost required for each operation determined based on the updated statistical information; and obtaining the execution cost of any alternative query plan based on the cost adjustment item and the original execution cost of each operation.

[0015] In a second aspect, an embodiment of the present application provides a Hive-based query plan optimization device, comprising:

[0016] a detection module configured to detect that data has been updated in at least one table within a query scope of a first query plan, and obtain the updated data of the at least one table; the first query plan is an optimal query plan generated based on a target query statement, and the query scope belongs to a target data warehouse;

[0017] An updating module, configured to update statistical information of a target data warehouse based on updated data, the statistical information including data distribution of a specific data type in the target data warehouse and data indicating characteristics of the specific data type;

[0018] An optimization module is used to: generate multiple alternative query plans corresponding to the target query statement based on the updated statistical information, and evaluate the execution cost of each of the multiple alternative query plans based on the updated statistical information, where the execution cost is used to indicate the size of resources required to execute each alternative query plan; and update the first query plan based on the alternative query plan with the smallest execution cost.

[0019] Optionally, a detection module is specifically used to: determine the update cycle and update range of each table in the target data warehouse based on the preset update frequency, data size and priority of each table in the target data warehouse; the priority is used to indicate the importance of each table; detect the data update status of each table in the target data warehouse according to the update cycle of each table, and when it is detected that there is data update in any table in the target data warehouse, obtain the update range of any table and determine whether the update range overlaps with the query range of the first query plan.

[0020] Optionally, the update module is specifically used to: calculate, based on the updated data of at least one table, the statistical values ​​of all data contained in each data type in each table, each partition in each table, and each column of data in each table, the data distribution of a specific data type, and data used to indicate the characteristics of a specific data type.

[0021] Optionally, when evaluating the execution cost of each alternative query plan among multiple alternative query plans based on the updated statistical information, the optimization module is used to: determine the task complexity of each alternative query plan and the total amount of data in the query scope based on the updated statistical information; the query scope includes at least one item of the table, partition, and column; and evaluate the execution cost of each alternative query plan based on the task complexity of each alternative query plan and the total amount of data in the query scope.

[0022] Optionally, when evaluating the execution cost of each alternative query plan in multiple alternative query plans based on the updated statistical information, the optimization module is also used to: obtain multiple first alternative query plans generated based on the statistical information before the update; if there is a target first alternative query plan in the multiple first alternative query plans that has the same operation as any alternative query plan in the multiple alternative query plans, then obtain the original execution cost required for each operation of the target first alternative query plan; evaluate the cost adjustment item for each operation of any alternative query plan based on the updated statistical information; the cost adjustment item is used to indicate the resources that need to be increased or decreased based on the original execution cost required for each operation determined based on the updated statistical information; and obtain the execution cost of any alternative query plan based on the cost adjustment item and the original execution cost of each operation.

[0023] In a third aspect, an embodiment of the present application provides an electronic device comprising at least one processor, wherein the at least one processor is configured to implement a method as in the first aspect or any optional embodiment of the first aspect when executing a computer program stored in a memory.

[0024] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium for storing instructions, which, when executed, enables the method in the first aspect or any optional embodiment of the first aspect to be implemented.

[0025] In a fifth aspect, an embodiment of the present application provides a computer program product, comprising a computer program code, which, when executed on a computer, enables the method according to the first aspect or any optional embodiment of the first aspect to be implemented.

[0026] The technical effects or advantages of one or more technical solutions provided in the second, third, fourth and fifth aspects of the embodiments of this application can be explained by the technical effects or advantages of the corresponding one or more technical solutions provided in the first aspect. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 A flowchart of a Hive-based query plan optimization method provided in an embodiment of the present application;

[0028] Figure 2 A structural diagram of a Hive-based query plan optimization device provided in an embodiment of the present application;

[0029] Figure 3 A structural diagram of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0030] In the technical solution of this application, the collection, dissemination, and use of data comply with the requirements of relevant national laws and regulations.

[0031] It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned. They should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present application, but it does not mean that the applicant has or will necessarily use the solution.

[0032] The technical solution of the present application is described in detail below through the accompanying drawings and specific embodiments. It should be understood that the embodiments of the present application and the specific features in the embodiments are detailed descriptions of the technical solution of the present application, rather than limitations on the technical solution of the present application. Unless there is a conflict, the embodiments of the present application and the technical features in the embodiments can be combined with each other.

[0033] It should be understood that "multiple" in the description of the embodiments of the present application refers to two or more. "First", "second" etc. in the embodiments of the present application are used to distinguish different objects, rather than to describe a specific order. The term "and / or" in the embodiments of the present application is merely a description of the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. In addition, the term "including" and any of its variations are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not limited to the listed steps or units, but optionally also includes steps or units that are not listed, or optionally also includes other steps or units inherent to these processes, methods, products or devices. The module in the embodiments of the present application refers to a part with independent functions in a software system.

[0034] In order to facilitate a better understanding of the solutions of the embodiments of the present application, the following is a brief introduction to some professional data involved in the embodiments of the present application.

[0035] 1. Hive: Hive is a Hadoop-based data warehouse software used for large-scale data storage and analysis. It stores structured data in the Hadoop Distributed File System (HDFS) and provides a query language (HiveQL) similar to Structured Query Language (SQL), allowing users to easily query, analyze, and aggregate data. Hive is commonly used in big data environments to process massive amounts of batch data.

[0036] 2. The Central Processing Unit (CPU) is the core component of the computer, responsible for executing instructions in computer programs and processing data. It is the "brain" of the computer and determines the processing speed and computing power of the system.

[0037] 3. Input / output (I / O) is the process used in a computer system to exchange data with the outside world. I / O usually refers to the transfer of data between a computer and external devices.

[0038] With the continuous development of big data technology, Hive, a commonly used data warehouse tool, plays a vital role in big data processing and analysis. Existing Hive relies heavily on statistical information during query calculations. This static information can be inaccurate or incomplete, leading to significant discrepancies between the estimated and actual values ​​in query plans, impacting query efficiency and accuracy.

[0039] In view of this, an embodiment of the present application provides a query plan optimization method based on Hive. When it is detected that there is data update in at least one table within the query scope of the first query plan, the updated data of the at least one table is obtained and the statistical information of the target data warehouse is updated based on the updated data; that is, the statistical information is dynamically adjusted according to the data update of the table to ensure that the statistical information can reflect the changes in the data in a timely manner; and then multiple new alternative query plans for the target query statement are generated based on the updated statistical information, and the execution cost of each alternative query plan is evaluated. The first query plan is updated based on the alternative query plan with the lowest execution cost to ensure that the first query plan always remains the current optimal query plan, which can improve data query efficiency and accuracy.

[0040] See also Figure 1, is a flow chart of a query plan optimization method based on Hive provided in an embodiment of the present application. The method is applicable to any electronic device with processing capabilities. The method can also be directly deployed in a Hive cluster environment as a plug-in for the Hive tool. The method includes steps S101 to S104:

[0041] S101: Detect that data is updated in at least one table within a query scope of a first query plan, and obtain updated data of the at least one table.

[0042] The first query plan is an optimal query plan generated based on the target query statement, and the query scope belongs to the target data warehouse. The target data warehouse is a data warehouse in the Hive cluster used to store business data of each cluster server.

[0043] It is understandable that, considering that Hive usually relies on the static data of the data warehouse when performing metadata collection, that is, after generating the first query plan based on the statistical information of the table in the target data warehouse, it will not automatically optimize the first query plan based on the data update in the data warehouse. Manual input of instructions is required to implement data updates and optimization of the first query plan. It is unable to perceive the changes of data in the data warehouse in real time, affecting the efficiency and accuracy of data query. In view of this, the embodiment of the present application sets the updated data detection function through the above-mentioned step S101, which can automatically detect whether there are data updates for each table within the query range of the first query plan, and obtain the updated data in a timely manner for subsequent optimization of the first query plan.

[0044] In a possible embodiment, the specific implementation of step S101 is as follows:

[0045] S101-1. Determine the update cycle and update range of each table in the target data warehouse based on the preset update frequency, data volume, and priority of each table; the priority is used to indicate the importance of each table;

[0046] S101-2. Detect the data update status of each table in the target data warehouse according to the update cycle of each table. When it is detected that any table in the target data warehouse has data update, obtain the update range of any table and determine whether the update range overlaps with the query range of the first query plan.

[0047] For example, the preset update frequency, data size, and priority of each table can be set manually, for example, based on the specific content stored in each table, the operation permissions of the table, the viewing frequency of the table, etc.

[0048] The specific implementation of step S101-1 may be:

[0049] The expression T=f(U,S,I) is used to determine the update period, and the expression E=g(U,S,I) is used to determine the update range; where T is the update period, E is the update range, U is the preset update frequency, S is the data size of the table, I is the priority (i.e., importance) of the table, and g and f are functions determined based on actual conditions.

[0050] For example, a weight can be set for each table based on the preset update frequency, data size and priority of the table. If the weight value is greater than the preset weight threshold, the update method of the table is determined to be real-time incremental update or periodic incremental update (shorter cycle); if the weight value is not greater than the preset weight threshold, periodic full update (longer cycle) is adopted, etc.

[0051] For example, for a table that is frequently updated, has a small amount of data, and has a high priority, you can set a larger weight value for the table and use real-time incremental updates or periodic incremental updates.

[0052] For tables with fewer updates, larger data volumes, and lower priorities, you can set a smaller weight for the table and use regular full updates.

[0053] It can be understood that the method for determining the weight value of each table and the preset weight threshold can be set according to actual needs, and the embodiments of the present application do not limit this.

[0054] The above is only a possible example of determining the update period and update range of a table provided in an embodiment of the present application, and is not limited thereto.

[0055] The specific implementation of step S101-2 may be:

[0056] After determining the update cycle and update range of each table, each table can be checked according to the update cycle of each table. If there is data update in any table in the target data warehouse, the update range of any table is obtained, and it is determined whether the update range overlaps with the query range of the first query plan.

[0057] Exemplarily, the update scope can be a column, a partition, or the entire table of any table. The specific range can be determined based on the query scope setting of the first query plan. For example, if the query scope of the first query plan includes a column of any table, then if there is an update to that column of any table, then the updated data of that table needs to be retrieved. If the query scope of the first query plan includes any table, then if there is an update to any data in that table, then the updated data of that table needs to be retrieved.

[0058] In this way, it is possible to ensure that the update status of each table can be obtained in a timely manner, and then the first query plan can be dynamically adjusted to improve data query efficiency and accuracy.

[0059] Optionally, the specific implementation of step S101-2 may also be:

[0060] The update status of all tables in the target data warehouse is detected according to a preset period. If an update is detected in at least one table within the query scope of the first query plan within the preset period, the updated data of the at least one table is obtained.

[0061] In this way, all tables in the target data warehouse are uniformly tested according to a preset cycle. Compared with obtaining the update status of each table in real time according to the update cycle of each table, the testing cost can be reduced.

[0062] In actual applications, a method for detecting the update status of each table can be selected according to actual needs, and the embodiments of the present application do not limit this.

[0063] S102: Update the statistical information of the target data warehouse based on the updated data.

[0064] The statistical information includes data distribution of a specific data type in the target data warehouse and data indicating characteristics of the specific data type.

[0065] In a possible embodiment, the specific implementation of step S102 is as follows:

[0066] Based on updated data of at least one table, statistical values ​​of all data contained in each data type in each table, each partition in each table, and each column of data in each table, data distribution of a specific data type, and data used to indicate characteristics of the specific data type are calculated.

[0067] Exemplarily, specific data types are usually complex data types (such as nested data types, arrays, mappings, etc.) and custom data structures, and the specific contents included can be set according to actual needs.

[0068] The data distribution of a specific data type is mainly used to indicate the distribution of storage locations of each specific data type.

[0069] Depending on the specific data type, the data collected to indicate the characteristics of the specific data type will also be different. For example, for nested data types, the depth of the nested fields needs to be collected; for array types, the length distribution needs to be collected; for mapping types, the number distribution of key-value pairs needs to be collected, etc.

[0070] In addition, an example of a method for calculating the statistical value of all data contained in each data type can be as follows:

[0071] For numeric fields, the statistical values include: maximum value, minimum value, average value, median value, standard deviation, variance, etc. For example, for a numeric field storing employees' salaries, the statistical values can include: the minimum value is 2000, the maximum value is 20000, the average value is 3000, the standard deviation is 3000, etc.;

[0072] For string fields, the statistical values include: the distribution of string lengths (such as minimum length, maximum length, average length), statistical information of prefixes and suffixes, etc. For example, for a string field storing names, the statistical values can include: the average length is 5, and most names start with Chinese characters such as "Zhang", "Li", "Wang", etc.;

[0073] For date fields, the statistical values include: the earliest date, the latest date, the distribution of dates (such as the distribution by year, month, day). For example, for a field storing order dates, the statistical values can include: the earliest date is 2024-01-01, the latest date is 2024-12-31, and the orders mainly concentrate in the second half of the year, etc.

[0074] The above are only some method examples for calculating the statistical values of all data included in each data type provided by the embodiments of this application, and the actual situation is not limited to this.

[0075] Optionally, if it is the first time to generate a query plan corresponding to the target query statement, the statistical values of all tables in the target data warehouse can be calculated according to the above method for calculating the statistical values of at least one table, so as to obtain the statistical information of the target data warehouse.

[0076] In the embodiments of this application, considering that when Hive performs metadata collection, it usually directly obtains the structured data stored in HDFS and then mounts it to the tables in Hive, and does not directly calculate the statistical information of each table. If statistical information needs to be calculated, the user needs to manually input the analyze command to force the calculation of the statistical values to be collected, which may result in the loss of metadata information, and the user needs to be proficient in the usage method of the analyze command, increasing the workload of the user and prone to errors caused by human subjective mistakes. In view of this, the embodiments of this application can automatically calculate the statistical values to be collected through the above method, improve stability, and can also perceive the changes in the data distribution of each data type in real time, such as data volume growth, data skew, etc., providing a more accurate basis for query plan optimization.

[0077] Moreover, by increasing the collection of statistical information for specific data types, the comprehensiveness and accuracy of statistical information can be ensured, so as to improve the accuracy of the subsequent generated alternative query plans, and further improve the efficiency and accuracy of data query.

[0078] S103: Generate multiple candidate query plans corresponding to the target query statement based on the updated statistical information, and evaluate the execution cost of each of the multiple candidate query plans based on the updated statistical information.

[0079] The execution cost is used to indicate the size of resources required to execute each alternative query plan, including but not limited to CPU resources, memory resources, I / O resources, execution time, etc.

[0080] Exemplarily, Hive can automatically generate multiple alternative query plans based on the semantic analysis of the target query statement and the updated statistical information. The specific implementation method can refer to the existing conventional method of generating query plans in Hive, which will not be repeated in the embodiments of this application.

[0081] In a possible embodiment, the specific implementation method of evaluating the execution cost of each of the multiple candidate query plans based on the updated statistical information is as follows:

[0082] Determine the task complexity of each candidate query plan and the total amount of data in the query range based on the updated statistical information; the query range includes at least one of a table, partition, and column;

[0083] The execution cost of each query plan candidate is evaluated based on the task complexity of each query plan candidate and the total amount of data in the query range.

[0084] It can be understood that the embodiment of the present application evaluates the execution cost of each alternative query plan based on the task complexity of each alternative query plan (including the number of query operations, difficulty, time, required resources, etc.) and the total amount of data in the query scope. In actual applications, other factors can be added to evaluate the execution cost of each alternative query plan, and the embodiment of the present application does not limit this.

[0085] In one possible example, if the update of statistical information has little impact on the query plan, that is, among the multiple first candidate query plans generated based on the statistical information before the update, there is a target first candidate query plan that is the same as any of the multiple candidate query plans generated based on the updated statistical information, then adjustments can be made based on the original execution cost of the target first candidate query plan to obtain the execution cost of any of the candidate query plans, without having to recalculate the execution cost of any of the candidate query plans, thereby reducing computational costs and improving efficiency. Specifically:

[0086] Obtain multiple first candidate query plans generated based on pre-update statistical information;

[0087] If there is a target first alternative query plan among the multiple first alternative query plans that has the same operation as any of the multiple alternative query plans, obtaining the original execution cost required for each operation of the target first alternative query plan;

[0088] Evaluate a cost adjustment for each operation of any candidate query plan based on the updated statistics; the cost adjustment indicates the amount of resources that need to be added or subtracted from the original execution cost of each operation based on the updated statistics;

[0089] Based on the cost adjustment and original execution cost of each operation, the execution cost of any alternative query plan is obtained.

[0090] It can be understood that the execution cost of any alternative query plan can be expressed as: C'=C+h(S); where C is the original cost model (i.e., the execution cost of the target first alternative query plan), C' is the optimized cost model (i.e., the execution cost of any alternative query plan); and h(S) is the cost adjustment item calculated based on the statistical information S.

[0091] For example, assuming that the target query statement is used to query the sales amount in January, the sales amount from January 1 to January 10 is stored in Table 1, and the sales amount from January 11 to January 31 is stored in Table 2. The target first alternative query plan is to query and select the numeric field of the sales amount in January in Table 1, and query and select the numeric field of the sales amount in January in Table 2.

[0092] If the data is updated to transfer the sales amount from January 11 to January 15 from Table 2 to Table 1, then any alternative query plan will still query and select the numerical field of the January sales amount in Table 1, and query and select the numerical field of the January sales amount in Table 2, that is, the operation of any alternative query plan is the same as that of the target first alternative query plan. The cost adjustment item (for example, increasing the time to query Table 1, reducing the time to query Table 2, etc.) can be directly added to the execution cost of each operation of the target first alternative query plan to obtain the execution cost of any alternative query plan.

[0093] If the data is updated to transfer the sales amount from January 11 to January 15 from Table 2 to Table 3, a new query on Table 3 is required. That is, any alternative query plan is different from the multiple first alternative query plans, and the execution cost of any alternative query plan needs to be completely recalculated.

[0094] S104: Update the first query plan based on the alternative query plan with the minimum execution cost.

[0095] It is understood that the embodiments of the present application use execution cost as the basis for selecting the optimal query plan, taking into account the size of the resources required to execute the query plan, including CPU resources, memory resources, I / O resources, execution time, etc. In actual applications, the size of one or more of the required resources or factors affecting the size of the execution cost, such as the operational difficulty or number of operations of the query plan, can also be selected as the standard for determining the optimal query plan based on needs, and the embodiments of the present application do not impose any restrictions on this.

[0096] In this embodiment, when at least one table update is detected within the query scope of the first query plan, the statistical information of the target data warehouse is updated based on the updated data of the at least one table, and multiple new alternative query plans are generated based on the updated statistical information. The execution cost of each alternative query plan is evaluated, and the first query plan is updated based on the alternative query plan with the lowest execution cost. In this way, during the execution of the first query plan, the statistical information can be dynamically adjusted based on the table data update, ensuring that the statistical information can promptly reflect data changes. The first query plan is then updated based on the updated statistical information to ensure that the first query plan is the currently optimal query plan, thereby improving data query efficiency and accuracy.

[0097] In one possible design, because Hive's physical execution plan is typically based on static resource estimates, it doesn't fully consider dynamic changes in the cluster environment, such as the addition or removal of cluster nodes or temporary resource shortages. This results in the execution plan being unable to adapt to actual resource conditions, impacting query efficiency. Therefore, during the execution of the first query plan, this embodiment of the application also provides a method for dynamically adjusting resource allocation, specifically implemented as follows:

[0098] Monitor the usage of Hive cluster resources in real time, collecting data such as CPU usage, memory usage, disk I / O rate, and network bandwidth. That is, the usage of Hive cluster resources can be expressed as: R = [R CPU ,R MEM ,R IO ,R NET ], where R CPU is the CPU usage, R MEM is the memory usage, R IO is the disk I / O rate, R NET The network bandwidth.

[0099] Dynamically adjust the resource allocation plan based on the task complexity, priority (pre-set manually or determined based on task complexity, query target, execution frequency, etc.) and resource requirements (i.e., execution cost) of the first query plan, as well as the usage of Hive cluster resources.

[0100] The method for dynamically adjusting resource allocation may employ the function: A(Q, R) = Allocate Resources for Query "Q" based on "R"; wherein A(Q, R) represents a resource allocation function; Q represents the first query plan, and R represents the usage of cluster resources.

[0101] For example, the following are some examples of dynamic resource allocation and adjustment schemes for quotas when executing the first query plan provided in an embodiment of the present application.

[0102] Example 1: Cluster resources are scarce.

[0103] When the cluster CPU usage exceeds the preset CPU usage (for example, 30%), the memory usage exceeds the preset memory usage (for example, 90%), etc., if the first query plan is a high-priority query plan (such as real-time report generation), its resources need to be guaranteed. Resources can be temporarily called from low-priority plans (such as historical data archiving) to be allocated to the first query plan, and the low-priority plans can reduce resource allocation or delay execution.

[0104] Example 2: Query plan resource requirements fluctuate greatly.

[0105] If the first query plan is a complex multi-stage query plan, preset memory and CPU resources are initially allocated. During the execution of the query plan, if the memory and CPU usage continue to rise and approach the upper limit, the memory and CPU resource allocation of the query plan is dynamically increased.

[0106] Example 3: Cluster node failure.

[0107] If a cluster has multiple nodes and the node used to execute the first query plan fails, all query plans on the failed node need to be migrated; if the resources of the remaining nodes are sufficient, the query plans on the failed node are migrated to suitable nodes according to priority and resource requirements; if the resources of the remaining nodes are tight, priority is given to resource allocation of high-priority plans, the execution order of the query plans of the remaining nodes is adjusted, and query plans that do not rely on data from the failed node are executed in advance.

[0108] Example 4: The execution time of the first query plan exceeds the expected time.

[0109] If the preset execution time of the first query plan is 10 minutes, but the first query plan is not completed after 15 minutes of execution and the memory usage is high, the memory allocation of the first query plan is dynamically increased, or the first query plan is re-evaluated and optimized.

[0110] It can be understood that the above are only some possible examples provided in the embodiments of the present application and are not limited thereto.

[0111] In this way, by dynamically adjusting the resource allocation during the execution of the first query plan, resource utilization can be improved, thereby improving the efficiency of data query.

[0112] The above describes the method provided by the embodiment of the present application, and the following describes the device provided by the embodiment of the present application.

[0113] Based on the same technical concept, embodiments of the present application provide a Hive-based query plan optimization device, which includes modules / units / means for executing the method performed by the electronic device in the above method embodiments. The modules / units / means can be implemented in software or hardware, or the corresponding software implementation can be executed by hardware.

[0114] For example, see Figure 2 , the apparatus 200 comprises:

[0115] Detection module 201 is configured to detect that data has been updated in at least one table within a query scope of a first query plan, and obtain the updated data of the at least one table; the first query plan is an optimal query plan generated based on a target query statement, and the query scope belongs to a target data warehouse;

[0116] An updating module 202 is configured to update statistical information of a target data warehouse based on the updated data, the statistical information including data distribution of a specific data type in the target data warehouse and data indicating characteristics of the specific data type;

[0117] The optimization module 203 is used to: generate multiple alternative query plans corresponding to the target query statement based on the updated statistical information, and evaluate the execution cost of each of the multiple alternative query plans based on the updated statistical information, where the execution cost is used to indicate the size of resources required to execute each alternative query plan; and update the first query plan based on the alternative query plan with the smallest execution cost.

[0118] Optionally, the detection module 201 is specifically used to: determine the update cycle and update range of each table according to the preset update frequency, data size and priority of each table in the target data warehouse; the priority is used to indicate the importance of each table; detect the data update status of each table in the target data warehouse according to the update cycle of each table, and when it is detected that there is data update in any table in the target data warehouse, obtain the update range of any table and determine whether the update range overlaps with the query range of the first query plan.

[0119] Optionally, the update module 202 is specifically used to calculate, based on the updated data of at least one table, the statistical values ​​of all data contained in each data type in each table, each partition in each table, and each column of data in each table, the data distribution of a specific data type, and data used to indicate the characteristics of a specific data type.

[0120] Optionally, when evaluating the execution cost of each alternative query plan among multiple alternative query plans based on the updated statistical information, the optimization module 203 is used to: determine the task complexity of each alternative query plan and the total amount of data in the query scope based on the updated statistical information; the query scope includes at least one item of the table, partition, and column; and evaluate the execution cost of each alternative query plan based on the task complexity of each alternative query plan and the total amount of data in the query scope.

[0121] Optionally, when evaluating the execution cost of each alternative query plan in multiple alternative query plans based on the updated statistical information, the optimization module 203 is also used to: obtain multiple first alternative query plans generated based on the statistical information before the update; if there is a target first alternative query plan in the multiple first alternative query plans that has the same operation as any alternative query plan in the multiple alternative query plans, then obtain the original execution cost required for each operation of the target first alternative query plan; evaluate the cost adjustment item of each operation of any alternative query plan based on the updated statistical information; the cost adjustment item is used to indicate the resources that need to be increased or decreased based on the original execution cost required for each operation determined based on the updated statistical information; and obtain the execution cost of any alternative query plan based on the cost adjustment item and the original execution cost of each operation.

[0122] It should be understood that all relevant contents of each step involved in the above method embodiment can be referred to the functional description of the corresponding functional module and will not be repeated here.

[0123] Based on the same technical concept, see Figure 3 , an embodiment of the present application further provides an electronic device 300, including:

[0124] At least one processor 301; and a communication interface 303 communicatively connected to the at least one processor 301; the at least one processor 301 executes instructions stored in the memory 302, so that the electronic device 300 executes the method steps performed by the electronic device in the above method embodiment through the communication interface 303.

[0125] Optionally, the memory 302 is located outside the electronic device 300 .

[0126] Optionally, the electronic device 300 includes the memory 302, which is connected to the at least one processor 301, and the memory 302 contains instructions that can be executed by the at least one processor 301. Figure 3 The dotted lines indicate that the memory 302 is optional for the electronic device 300 .

[0127] The at least one processor 301 and the memory 302 may be coupled via an interface circuit or may be integrated together, which is not limited here.

[0128] The specific connection medium between the at least one processor 301, the memory 302 and the communication interface 303 is not limited in the embodiment of the present application. Figure 3 In the embodiment, at least one processor 301, memory 302 and communication interface 303 are connected via a bus 304. Figure 3 The bus portion may be an address bus, a data bus, a control bus, etc. For ease of representation, Figure 3 Just one thick line is used, but this does not mean there is only one bus or one type of bus.

[0129] It should be understood that the processors mentioned in the embodiments of the present application can be implemented by hardware or software. When implemented by hardware, the processor can be a logic circuit, an integrated circuit, etc. When implemented by software, the processor can be a general-purpose processor that is implemented by reading software code stored in a memory.

[0130] Exemplarily, the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0131] It should be understood that the memory mentioned in the embodiments of the present application may be a volatile memory or a non-volatile memory, or may include both a volatile memory and a non-volatile memory. Among them, the non-volatile memory may be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), or a flash memory. The volatile memory may be a random access memory (RAM), which acts as an external cache. By way of example and not limitation, many forms of RAM are available, such as static random access memory (SRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDR SDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synchronous link dynamic random access memory (SLDRAM), and direct RAM bus random access memory (DR RAM).

[0132] It should be noted that when the processor is a general-purpose processor, DSP, ASIC, FPGA or other programmable logic device, discrete gate or transistor logic device, discrete hardware component, the memory (storage module) can be integrated into the processor.

[0133] It should be noted that the memory described herein is intended to include, but is not limited to, these and any other suitable types of memory.

[0134] Based on the same technical concept, an embodiment of the present application also provides a computer-readable storage medium, which is used to store instructions. When the instructions are executed, the computer executes the method steps executed by any device in the above method embodiments.

[0135] Based on the same technical concept, an embodiment of the present application also provides a computer program product, including computer program code. When the computer program code is run on a computer, the method steps executed by any device in the above method embodiment are implemented.

[0136] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0137] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the present application. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0138] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0139] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The steps for the function specified in one or more boxes.

[0140] Obviously, those skilled in the art may make various changes and modifications to the present application without departing from the scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is intended to include these modifications and variations.

Claims

1. A query plan optimization method based on Hive, characterized in that: include: detecting that data update exists in at least one table within the query scope of the first query plan, and obtaining updated data of the at least one table; The first query plan is an optimal query plan generated based on the target query statement, and the query scope belongs to the target data warehouse; updating statistical information of the target data warehouse based on the updated data, wherein the statistical information includes data distribution of a specific data type in the target data warehouse and data indicating characteristics of the specific data type; Generating multiple alternative query plans corresponding to the target query statement based on the updated statistical information, and evaluating an execution cost of each of the multiple alternative query plans based on the updated statistical information, where the execution cost is used to indicate a size of resources required to execute each alternative query plan; The first query plan is updated based on the alternative query plan with the minimum execution cost.

2. The method according to claim 1, wherein The detecting that data update exists in at least one table within the query scope of the first query plan includes: Determine the update cycle and update range of each table in the target data warehouse according to the preset update frequency, data volume, and priority of each table; the priority is used to indicate the importance of each table; The data update status of each table in the target data warehouse is detected according to the update cycle of each table. When it is detected that there is data update in any table in the target data warehouse, the update range of any table is obtained and it is determined whether the update range overlaps with the query range of the first query plan.

3. The method according to claim 1, wherein Updating the statistical information of the target data warehouse based on the updated data includes: Based on the updated data of the at least one table, the statistical values ​​of all data contained in each data type in each table, each partition in each table, and each column of data in each table, the data distribution of the specific data type, and data used to indicate the characteristics of the specific data type are calculated.

4. The method according to any one of claims 1 to 3, wherein The evaluating the execution cost of each of the multiple candidate query plans based on the updated statistical information includes: Determine the task complexity of each candidate query plan and the total amount of data in the query range based on the updated statistical information; the query range includes at least one of a table, a partition, and a column; The execution cost of each candidate query plan is evaluated based on the task complexity of each candidate query plan and the total amount of data in the query scope.

5. The method according to claim 4, wherein The evaluating the execution cost of each of the multiple candidate query plans based on the updated statistical information further includes: Obtain multiple first candidate query plans generated based on pre-update statistical information; If there is a target first alternative query plan among the multiple first alternative query plans that has the same operation as any alternative query plan among the multiple alternative query plans, obtaining the original execution cost required for each operation of the target first alternative query plan; Evaluate a cost adjustment item for each operation of any candidate query plan based on the updated statistical information; the cost adjustment item is used to indicate resources that need to be increased or decreased based on the original execution cost required for each operation determined based on the updated statistical information; The execution cost of any candidate query plan is obtained based on the cost adjustment item of each operation and the original execution cost.

6. A query plan optimization device based on Hive, characterized in that: include: A detection module is configured to: detect that data update exists in at least one table within the query scope of the first query plan, and obtain updated data of the at least one table; The first query plan is an optimal query plan generated based on the target query statement, and the query scope belongs to the target data warehouse; An updating module, configured to update statistical information of the target data warehouse based on the update data, wherein the statistical information includes data distribution of a specific data type in the target data warehouse and data indicating characteristics of the specific data type; an optimization module, configured to: generate multiple query plans corresponding to the target query statement based on the updated statistical information, and evaluate an execution cost of each of the multiple alternative query plans based on the updated statistical information, wherein the execution cost is used to indicate a size of resources required to execute each alternative query plan; Based on execution The alternative query plan with the minimum cost updates the first query plan.

7. The device according to claim 6, characterized in that The detection device is specifically used for: Determine the update cycle and update range of each table in the target data warehouse according to the preset update frequency, data volume, and priority of each table; the priority is used to indicate the importance of each table; The data update status of each table in the target data warehouse is detected according to the update cycle of each table. When it is detected that there is data update in any table in the target data warehouse, the update range of any table is obtained and it is determined whether the update range overlaps with the query range of the first query plan.

8. An electronic device, characterized in that: include: a memory for storing program instructions; A processor is configured to call the program instructions stored in the memory and execute the steps included in the method according to any one of claims 1 to 5 according to the obtained program instructions.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium is used to store a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a computer, the method according to any one of claims 1 to 5 is implemented.

10. A computer program product, characterized in that The computer program product comprises: a computer program code, and when the computer program code is run on a computer, the computer is enabled to execute the method according to any one of claims 1 to 5.

Images