HiveQL optimization method and device, equipment, medium and product

Abstract

The embodiment of the invention provides a HiveQL optimization method and device, equipment, a medium and a product, and relates to the field of big data. The method comprises the following steps: analyzing the complexity of a HiveQL statement by adopting a diagnosis model and a preset complexity index to obtain a complexity analysis result; based on the HiveQL statement and the complexity analysis result, the operation parameters are optimized, and the optimized operation parameters are obtained; on the basis of a complexity analysis result, a historical slow statement optimization set, a historical rewriting optimization rule set and a statement development specification set, a diagnosis model and a statement optimization model are adopted to conduct optimization processing on the HiveQL statement, and the optimized HiveQL statement is obtained, the statement optimization model is a model which is trained in advance and is used for modifying the HiveQL statement according to the natural language description content. According to the method provided by the invention, the HiveQL optimization efficiency is improved, and the user optimization threshold is reduced.

Description

Technical Field

[0001] The present application relates to the field of big data, and in particular to a HiveQL optimization method, device, equipment, medium and product. Background Art

[0002] With the rapid development of big data technology, Hive, as a data warehouse tool based on the Hadoop file system, has been widely used in the field of data query and analysis processing. Users can query and analyze files stored in the file system according to the Hive Query Language (HiveQL) provided by it.

[0003] In the prior art, developers generally optimize HiveQL performance based on empirical rules or manual log analysis to address the problems of low task query efficiency and waste of cluster resources caused by existing HiveQL tasks with performance issues. However, the prior art methods not only have low efficiency in optimizing HiveQL performance, but also have the problem of high optimization threshold. Summary of the invention

[0004] The present application provides a HiveQL optimization method, device, equipment, medium and product to solve the problems of low performance optimization efficiency and high optimization threshold of HiveQL tasks.

[0005] In a first aspect, the present application provides a HiveQL optimization method, comprising:

[0006] Receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device;

[0007] The complexity of the HiveQL statement is analyzed by using a diagnostic model and a preset complexity index to obtain a complexity analysis result, wherein the diagnostic model includes a plurality of sub-models for performing different-dimensional diagnostics on the HiveQL statement;

[0008] Based on the HiveQL statement and the complexity analysis result, the job parameters are optimized to obtain optimized job parameters;

[0009] Based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, the HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement, wherein the statement optimization model is a model pre-trained to modify the HiveQL statement according to the natural language description content;

[0010] The optimized job parameters and the optimized HiveQL statement are returned to the terminal device.

[0011] In a second aspect, the present application provides a HiveQL optimization device, including:

[0012] The receiving module is used to receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device;

[0013] An analysis module, used to analyze the complexity of the HiveQL statement using a diagnosis model and a preset complexity index to obtain a complexity analysis result, wherein the diagnosis model includes a plurality of sub-models for performing different-dimensional diagnosis on the HiveQL statement;

[0014] A first optimization module, configured to optimize the job parameters based on the HiveQL statement and the complexity analysis result to obtain optimized job parameters;

[0015] A second optimization module is used to optimize the HiveQL statement using the diagnosis model and the statement optimization model based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set to obtain an optimized HiveQL statement, wherein the statement optimization model is a model pre-trained to modify the HiveQL statement according to the natural language description content;

[0016] A return module is used to return the optimized job parameters and the optimized HiveQL statement to the terminal device.

[0017] In a third aspect, the present application provides an electronic device, comprising: a processor, and a memory communicatively connected to the processor;

[0018] The memory stores computer-executable instructions;

[0019] The processor executes the computer-executable instructions stored in the memory to implement the HiveQL optimization method of the first aspect.

[0020] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the HiveQL optimization method of the first aspect.

[0021] In a fifth aspect, the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the HiveQL optimization method of the first aspect of the claims.

[0022] The HiveQL optimization method, device, equipment, medium and product provided in the present application receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device; use the diagnosis model and the preset complexity index to analyze the complexity of the HiveQL statement to obtain the complexity analysis result, wherein the diagnosis model includes a plurality of sub-models for diagnosing the HiveQL statement in different dimensions; based on the HiveQL statement and the complexity analysis result, the job parameters are optimized to obtain the optimized job parameters; based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, the diagnosis model and the statement optimization model are used to optimize the HiveQL statement to obtain the optimized HiveQL statement, wherein the statement optimization model is a model pre-trained for modifying the HiveQL statement according to the natural language description content; the optimized job parameters and the optimized HiveQL statement are returned to the terminal device means, so as to achieve the technical effect of improving the HiveQL optimization efficiency and lowering the user optimization threshold. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0024] Figure 1 A schematic diagram of a scenario of the HiveQL optimization method provided in this application;

[0025] Figure 2 A flowchart of the HiveQL optimization method provided in Example 1 of the present application;

[0026] Figure 3 A schematic diagram of the structure of the HiveQL optimization device provided in Example 3 of the present application;

[0027] Figure 4 This is a schematic diagram of the structure of an electronic device provided in Example 4 of the present application.

[0028] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0029] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0030] It should be noted that the HiveQL optimization method, device, equipment, medium and product provided in this application can be used in the field of big data, and can also be used in any field other than big data. The application field of the HiveQL optimization method, device, equipment, medium and product in this application is not limited.

[0031] In order to make it easier to understand the technical solution of this application, the background technology of this application is introduced in detail below:

[0032] Hive is a data warehouse tool for the Hadoop file system, which is used to query and analyze data sets stored in files on the file system. Users can query and analyze files stored on the file system based on the HiveQL query language it provides. However, some HiveQL tasks may have performance issues due to insufficient parameter configuration or complex nested relationships of HiveQL statements, resulting in low query efficiency and even a waste of cluster resources.

[0033] In the prior art, developers often configure the job parameters in the HiveQL task according to their personal development experience and compile the HiveQL statement, or developers analyze the background log data after the HiveQL task is run and parse out the slow query problem in the HiveQL task according to the records of the log data.

[0034] However, the HiveQL optimization method in the prior art requires professional developers to manually analyze the problems of query tasks, which not only has low optimization efficiency, but also has the problem of high optimization threshold. In addition, in the prior art solution, HiveQL cannot be optimized before the user submits the query, so it is impossible to avoid the impact of slow queries on other task processes in the task cluster, resulting in a waste of resources.

[0035] Based on the description of the above background technology, the inventor's technical conception is as follows: In the process of studying the HiveQL optimization method, the inventor found that the submitted HiveQL can be treated preferentially through the diagnostic function of the big model, and many possible problems of HiveQL (such as slow query statements, non-compliance with development specifications, etc.) can be diagnosed from multiple aspects and a diagnostic analysis report can be given. The ability of the big model to modify HiveQL statements according to natural language can be relied on to automatically optimize HiveQL. Based on this, automatic optimization of HiveQL statements can be achieved, which can not only improve the optimization efficiency and lower the optimization threshold, but also optimize HiveQL in advance to effectively avoid the impact of slow queries on other task processes.

[0036] The HiveQL optimization method provided in the present application receives the job parameters and HiveQL statements of the HiveQL task sent by the terminal device; uses a diagnostic model and a preset complexity index to analyze the complexity of the HiveQL statement to obtain a complexity analysis result, wherein the diagnostic model includes a plurality of sub-models for diagnosing the HiveQL statement in different dimensions; based on the HiveQL statement and the complexity analysis result, the job parameters are optimized to obtain optimized job parameters; based on the complexity analysis result, a historical slow statement optimization set, a historical rewrite optimization rule set, and a statement development specification set, the diagnostic model and the statement optimization model are used to optimize the HiveQL statement to obtain an optimized HiveQL statement, wherein the statement optimization model is a model pre-trained for modifying the HiveQL statement according to the natural language description content; the optimized job parameters and the optimized HiveQL statement are returned to the terminal device means, so as to achieve the technical effect of improving the HiveQL optimization efficiency and lowering the user optimization threshold.

[0037] Figure 1 A schematic diagram of a scenario of the HiveQL optimization method provided for this application is shown in FIG. Figure 1 As shown, this application scenario includes: a terminal device 101 and a server 102. The terminal device 101 and the server 102 are connected in communication, the terminal device 101 is held by a user, and the user can operate the terminal device 101. The terminal device 101 can be a smart phone, a laptop, a tablet computer, and other devices.

[0038] Optionally, the communication connection between the above devices can be wireless or wired. The wired connection can be Ethernet, connecting the devices through network cables. It can also be serial communication, for example, including RS-232, RS-485 and other standards. It can also be optical fiber communication, for example, metropolitan area network and wide area network. Wireless communication can be wireless LAN, Bluetooth, Zigbee protocol (Zigbee), cellular network, low-power wide area network. For scenarios in remote areas, satellite communication can also be used.

[0039] The user uploads the job parameters and HiveQL statements of the HiveQL task through the terminal device 101 .

[0040] The server 102 is used to receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device, and adopts a sub-model that can diagnose the HiveQL statement in different dimensions and a preset complexity index to analyze the complexity of the HiveQL statement to obtain a complexity analysis result, and optimizes the job parameters based on the HiveQL statement and the complexity analysis result to obtain the optimized job parameters; then, based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification, the diagnostic model and the statement optimization model pre-trained for modifying the HiveQL statement according to the natural language description content are adopted to optimize the HiveQL statement to obtain the optimized HiveQL statement, and finally, the optimized job parameters and the optimized HiveQL statement are returned to the terminal device 102.

[0041] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0042] Figure 2 A flow chart of the HiveQL optimization method provided in Example 1 of the present application is shown as follows: Figure 2 As shown, the HiveQL optimization method provided in this application includes:

[0043] S201. Receive job parameters and HiveQL statements of a HiveQL task sent by a terminal device.

[0044] In this solution, after setting the job parameters of the HiveQL task and compiling the HiveQL statement according to the query requirements, the user can upload the job parameters and HiveQL statement of the HiveQL task to the server through the data upload interface of the terminal device. The server can optimize the job parameters and HiveQL statement of the HiveQL task sent by the terminal device based on the diagnostic analysis capability of the model and the rewriting capability of the statement, and return the optimized job parameters and HiveQL statement to the terminal device. The user can view the optimized job parameters and HiveQL statement in the display interface of the terminal device.

[0045] In this step, in order to achieve advance optimization of the job parameters and HiveQL statements of the HiveQL task, the server will receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device.

[0046] Among them, HiveQL tasks usually refer to data definition, data query, data analysis and other operations on data stored in the file system. This step focuses on data query operations; job parameters refer to the relevant parameters that need to be set in the configuration file in the Hive configuration directory before executing the HiveQL task, which are used to limit the query process. Exemplarily, job parameters include: "the maximum number of dynamic partitions that can be created in a query process", "the maximum number of dynamic partitions that can be created for a node task" and "whether to force bucket sorting" and other parameters. HiveQL statements refer to the query language to be executed for data query operations.

[0047] S202: Analyze the complexity of the HiveQL statement using the diagnostic model and the preset complexity index to obtain a complexity analysis result.

[0048] In this step, the diagnosis model includes multiple sub-models for diagnosing HiveQL statements in different dimensions. According to the preset complexity evaluation index, the complexity diagnosis sub-model in the multiple diagnosis sub-models is used to analyze the complexity of the HiveQL statement and give the complexity analysis result.

[0049] The complexity analysis results include the quantity statistics or type determination corresponding to each complexity evaluation indicator in the HiveQL statement, as well as targeted complexity evaluation and statement rewriting suggestions based on the counted quantity and determined type.

[0050] In a specific implementation, the complexity evaluation indicators include: the number of complex operation keywords, the number of nested levels, and the JOIN operation type.

[0051] Specifically, the number of complex operation keywords includes keywords for multi-table joins, such as "JOIN", "INNERJOIN", "LEFT JOIN", "RIGHT JOIN", etc.; keywords for subqueries, such as "SELECT", "FROM", "WHERE", etc.; keywords for aggregation, such as "GROUP BY", "HAVING", etc.; keywords for logical operators, such as "AND", "OR", "NOT", etc. The number of nested levels is determined based on the join logic of the HiveQL statement, as well as the number of multi-table join keywords and subquery keywords. JOIN operation types include JOIN operation, INNER JOIN operation, LEFT JOIN operation, RIGHT JOIN operation, FULL JOIN operation, etc. The complexity of different JOIN operation types is different. For example, the operation logic of FULL JOIN operation is more complex than that of INNER JOIN operation.

[0052] In a specific implementation, a diagnostic model is used to process the complexity of a HiveQL statement based on a complexity index and a complexity analysis rule to obtain a complexity analysis result;

[0053] The complexity analysis rules include:

[0054] The number of complex operation keywords is proportional to the complexity;

[0055] The number of nesting levels is proportional to the complexity;

[0056] The complexity of different JOIN operation types.

[0057] In this implementation, the diagnostic model counts the number of complexity operation keywords and the number of nested levels in the HiveQL statement according to the pre-set complexity indicators, and confirms the type of JOIN operation in the HiveQL statement; then, based on the complexity analysis rules, it gives the complexity evaluation corresponding to each complexity indicator in the HiveQL statement, and finally gives statement rewriting suggestions.

[0058] For example, if there are many table join keywords in the HiveQL statement, the complexity evaluation and rewrite suggestion in the complexity analysis result may be: there are many table join keywords in the HiveQL statement, and it is recommended to reduce the table join operations in the HiveQL statement.

[0059] S203: Optimize the job parameters based on the HiveQL statement and the complexity analysis result to obtain optimized job parameters.

[0060] In this step, the job parameters are optimized based on the information of the tables involved in the HiveQL statement and the complexity evaluation in the complexity analysis result.

[0061] In a specific implementation, based on the HiveQL statement and the complexity analysis result, the job parameters are optimized to obtain the optimized job parameters, including:

[0062] Obtain statistical information about the table data volume involved in the HiveQL statement; optimize the job parameters based on the statistical information about the table data volume and the complexity analysis results to obtain the optimized job parameters.

[0063] Specifically, according to the HiveQL statement, the amount of data in each of the multiple tables involved in the HiveQL statement is counted to obtain statistical information on the amount of data in the tables involved. According to the statistical information on the amount of data in the tables involved and the complexity evaluation in the complexity analysis result, the job parameters are optimized.

[0064] For example, according to the amount of table data involved in the HiveQL statement, adjust the maximum number of dynamic partitions that can be created in a query process in the job parameters. When the amount of table data involved in the HiveQL statement is large, reduce the "maximum number of dynamic partitions that can be created in a query process" (the default value of "maximum number of dynamic partitions that can be created in a query process" is 100). This can effectively avoid performance problems caused by too many partitions.

[0065] For another example, according to the amount of table data involved in the HiveQL statement and the number of JOIN operations indicated in the complexity evaluation, adjust the "Whether to force bucket sort" in the job parameters. When the amount of table data involved in the HiveQL statement is large and the number of JOIN operations is large, change "Whether to force bucket sort" to "true" (the default value of "Whether to force bucket sort" is "fales").

[0066] S204, based on the complexity analysis results, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, the diagnostic model and the statement optimization model are used to optimize the HiveQL statement to obtain the optimized HiveQL statement.

[0067] In this step, in order to achieve automated, multi-dimensional optimization of HiveQL statements, it is necessary to first diagnose the existence of slow statements in HiveQL statements based on the historical slow statement optimization set, using the slow statement submodule of the diagnostic model, and obtain the slow statement diagnosis conclusion; based on the historical rewrite optimization rule set, using the rewrite optimization rule submodule of the diagnostic model, diagnose the satisfaction of the statement optimization rules of the HiveQL statements, and obtain the statement optimization rule diagnosis conclusion; based on the statement development specification set, using the development specification submodule of the diagnostic module, diagnose the satisfaction of the development specifications of the HiveQL statements, and obtain the development rule diagnosis conclusion. Then, based on the complexity analysis results, slow statement diagnosis conclusions, statement optimization rule diagnosis conclusions, and natural statement descriptions of the development rule diagnosis conclusions, the statement optimization model is used to optimize the HiveQL statements and obtain the optimized HiveQL statements. Among them, the statement optimization model is a model pre-trained for modifying HiveQL statements according to natural language descriptions.

[0068] Furthermore, in a specific implementation, the historical slow statement optimization set includes multiple slow HiveQL statements that generate slow query results obtained based on historical data analysis, as well as the code optimization logic described in natural language corresponding to each HiveQL statement and the modified code.

[0069] Accordingly, based on the complexity analysis results, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, the diagnostic model and the statement optimization model are used to optimize the HiveQL statement to obtain the optimized HiveQL statement, including:

[0070] Step 1: Based on the complexity analysis result, the HiveQL statement is rewritten to reduce the complexity using the statement optimization model to obtain the first HiveQL statement.

[0071] In this step, in order to determine the slow statements existing in the HiveQL statement, the natural language description of the statement modification suggestion in the complexity analysis result obtained in step S202 and the HiveQL statement can be first input into the statement optimization model. The statement optimization model performs synonymous rewriting on the HiveQL statement according to the natural language description of the statement modification suggestion to obtain the HiveQL statement with reduced complexity (i.e., the first HiveQL statement).

[0072] Step 2: Use the diagnostic model to match the first HiveQL statement with multiple slow HiveQL statements in the historical slow statement optimization set, and determine whether there is a target slow HiveQL statement similar to the first HiveQL statement in the multiple slow HiveQL statements;

[0073] In this step, the slow sentence submodule of the diagnostic model is used to compare the features of the HiveQL statement after complexity reduction (i.e., the first HiveQL statement) with the features of multiple slow HiveQL statements that have appeared in history, and to determine whether there is a target slow HiveQL statement similar to the first HiveQL statement among the multiple slow HiveQL statements.

[0074] Step 3: If there is a target slow HiveQL statement similar to the first HiveQL statement among the multiple slow HiveQL statements, the statement optimization model is used to optimize and modify the first HiveQL statement according to the code optimization logic corresponding to the target slow HiveQL statement and the modified code to obtain a second HiveQL statement.

[0075] In this step, if there is a target slow HiveQL statement similar to the first HiveQL statement among multiple slow HiveQL statements, it is necessary to obtain the code optimization logic described in natural language and the modified code corresponding to the target slow HiveQL statement from the historical slow statement optimization set, and input the code optimization logic described in natural language and the modified code into the statement optimization model. The statement optimization model optimizes and modifies the first HiveQL statement according to the code optimization logic described in natural language and the modified code, and obtains the preliminarily optimized HiveQL statement (i.e., the second HiveQL statement).

[0076] It should be understood that in steps 1 to 3 of the present implementation, based on the complexity analysis result, a statement optimization model is first used to reduce the complexity of the HiveQL statement to obtain a first HiveQL statement; then the slow statement submodule of the diagnosis model is used to obtain a slow statement diagnosis conclusion including the code optimization logic described in the natural language and the modified code, and the statement optimization model is used to perform preliminary optimization and modification on the first HiveQL statement to obtain a second HiveQL statement. This method realizes preliminary optimization of HiveQL statements with slow HiveQL statement characteristics and improves the query efficiency of HiveQL statements during execution.

[0077] Step 4: According to the historical rewrite optimization rule set and the statement development specification set, the second HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement.

[0078] In this step, based on the historical rewrite optimization rule set, the rewrite optimization rule submodule of the diagnostic model is used to diagnose the compliance of the HiveQL statement optimization rule to obtain the statement optimization rule diagnosis conclusion; based on the statement development specification set, the development specification submodule of the diagnostic module is used to diagnose the compliance of the HiveQL statement development specification to obtain the development rule diagnosis conclusion. Then, based on the statement optimization rule diagnosis conclusion and the natural statement description of the development rule diagnosis conclusion, the statement optimization model is used to optimize the second HiveQL statement to obtain the optimized HiveQL statement.

[0079] Furthermore, in a specific implementation, the historical rewrite optimization rule set includes multiple statement optimization rules obtained based on historical data analysis and code rewriting logic described in a natural language corresponding to each statement optimization rule.

[0080] Accordingly, according to the historical rewrite optimization rule set and the statement development specification set, the second HiveQL statement is optimized using the diagnostic model and the statement optimization model to obtain the optimized HiveQL statement, including:

[0081] Step 4.1: Use the diagnostic model to determine whether there is a target statement optimization rule that is not satisfied by the second HiveQL statement among multiple statement optimization rules.

[0082] In this step, the statement optimization rules in the historical rewrite optimization rule set refer to the experience rules obtained by developers after analyzing historical data. The code rewriting logic described in natural language corresponding to each statement optimization rule is the code rewriting logic obtained by developers based on historical rewriting experience, which is used to modify HiveQL statements to meet the statement optimization rules.

[0083] Exemplarily, statement optimization rules may include: columns used in the WHERE clause should have indexes; the WHERE clause should be used to filter data as early as possible; Cartesian products (CROSS JOIN) should be avoided; partition columns should be used for pruning in JOIN and WHERE clauses, and the like.

[0084] Accordingly, the code rewriting logic may include:

[0085] a. The code rewrite logic corresponding to "columns used in the WHERE clause should have indexes" is: if the query performance bottleneck lies in scanning a large amount of data, you can create an index to speed up the execution of the filtering condition;

[0086] b. The code optimization logic corresponding to "use the WHERE clause to filter data as early as possible" is: put the filtering condition before the JOIN operation to reduce the amount of data involved in the JOIN operation;

[0087] c. The code rewrite logic corresponding to "Avoid using Cartesian products (CROSS JOIN)" is to ensure that if JOIN must be used, the columns of the JOIN are comparable and the table with smaller data volume is placed in front;

[0088] d. The code optimization logic corresponding to "Partition columns should be used for pruning in JOIN and WHERE clauses" is: reduce the number of partitions that need to be scanned by specifying partitions.

[0089] Step 4.2: If there is a target statement optimization rule that the second HiveQL statement does not satisfy, the statement optimization model is used to optimize and modify the second HiveQL statement according to the code rewriting logic corresponding to the target statement optimization rule to obtain a third HiveQL statement.

[0090] In this step, the rewrite optimization rule submodule of the diagnostic model is used to check the possible situations in the second HiveQL statement that do not meet the statement optimization rules according to the statement optimization rules; if there is a target statement optimization rule in the statement optimization rules that the second HiveQL statement does not meet, the code rewrite logic corresponding to the target statement optimization rule is obtained from the historical rewrite optimization rule set; then the code rewrite logic described in natural language is input into the statement optimization model, and the statement optimization model optimizes and modifies the second HiveQL statement according to the code rewrite logic to obtain the third HiveQL statement.

[0091] It should be understood that step 4.1 and step 4.2 in this implementation scheme use the problem diagnosis capability of the rewrite optimization rule submodule to troubleshoot problems in the second HiveQL statement, obtain statement optimization rule diagnosis conclusions including code rewriting logic described in natural language, and rely on the statement optimization model's ability to rewrite code according to natural language descriptions to successfully optimize the problems in the second HiveQL statement that do not meet the statement optimization rules, further improving the statement's fast query capability.

[0092] Step 4.3: According to the statement development specification set, the third HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement.

[0093] In this step, based on the statement development specification set, the development specification submodule of the diagnosis module is used to diagnose the compliance of the development specification of the HiveQL statement and obtain the development rule diagnosis conclusion. Then, based on the natural statement description of the development rule diagnosis conclusion, the statement optimization model is used to optimize the third HiveQL statement and obtain the optimized HiveQL statement. Among them, the development specification collection includes a series of specifications issued by the organization that all developers need to follow together (including mandatory or recommended regulations, etc.).

[0094] Furthermore, in a specific implementation, the statement development specification set includes: at least one specification requirement of library name, table name, field name, table partition setting, view naming, and variable naming described in natural language, and / or mandatory requirements for hiveQL code development.

[0095] Accordingly, according to the statement development specification set, the third HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement, including:

[0096] Step 4.3.1: Use the diagnostic model to determine whether there are target requirements that are not met by the third HiveQL statement in the statement development specification set.

[0097] In this step, the development specification submodule of the diagnosis module is used to diagnose whether there are any problems with the library name, table name, field name, table partition setting, view naming, and variable naming in the third HiveQL statement that do not meet the specification requirements, and whether the third HiveQL statement does not meet the mandatory requirements of development, according to the statement development specification set.

[0098] For example, at least one of the specification requirements for library names, table names, field names, table partition settings, view names, and variable names includes: it is strictly forbidden to use "a", "b", "t1", "t2" as field aliases or result set names, etc.; lowercase letters should be used for naming; special characters or keywords are prohibited in naming; the name should be less than 32 characters, etc.

[0099] For example, mandatory requirements for hiveQL code development may include: prohibiting Cartesian product associations; prohibiting more than three levels of SELECT nesting and more than four JOIN operations, and using WITH AS to extract into separate result sets; prohibiting full table scans; prohibiting the use of SELECT *, etc.

[0100] In step 4.3.2, if there are target requirements that are not met by the third HiveQL statement, the statement optimization model is used to optimize and modify the third HiveQL statement based on the target requirements described in natural language to obtain an optimized HiveQL statement.

[0101] In this step, after diagnosis is performed using the development specification submodule of the diagnosis module, if the second HiveQL statement does not meet the specification requirements or mandatory requirements, the target requirements that are not met in the second HiveQL statement and the second HiveQL statement are used as inputs to the statement optimization model; the statement optimization model optimizes and modifies the second HiveQL statement according to the description of the natural statement to obtain the optimized second HiveQL statement.

[0102] It should be understood that step 4.3 uses the problem diagnosis capability of the development specification submodule to troubleshoot problems in the second HiveQL statement, obtains the development specification diagnosis conclusion of the target development specification including the natural language description, and relies on the statement optimization model to rewrite the code according to the natural language description. The problem of not satisfying the statement optimization rules in the second HiveQL statement is successfully optimized to further improve the standardization and readability of the statement, and effectively avoids problems such as difficulty in integrating the statement with other tools and poor query performance during execution.

[0103] S205. Return the optimized job parameters and the optimized HiveQL statement to the terminal device.

[0104] In this step, the optimized job parameters obtained in step S203 and the optimized HiveQL statements obtained in step S204 are returned to the user's terminal device; the user can view them on the visual interface of the terminal device.

[0105] Optionally, the complexity analysis results, slow statement diagnosis conclusions, statement optimization rule diagnosis conclusions, and development specification diagnosis conclusions can be returned to the terminal device together. Based on the above complexity analysis results and diagnosis conclusions, the user can learn about the problems in the original job parameters or HiveQL statements, so that the user can pay more attention to this problem during the subsequent compilation of parameters or statements to reduce the time for HiveQL optimization. Furthermore, the user can analyze the correctness of HiveQL optimization based on the above complexity analysis results and diagnosis conclusions. If there are errors in the analysis or diagnosis, the diagnostic model, complexity evaluation indicators, historical slow statement optimization set, historical rewrite optimization rule set, and statement development specification set can be optimized and modified in a targeted manner.

[0106] The HiveQL optimization method provided in this embodiment receives the job parameters and HiveQL statements of the HiveQL task sent by the terminal device; uses the diagnosis model and the preset complexity index to analyze the complexity of the HiveQL statement to obtain the complexity analysis result, wherein the diagnosis model includes a plurality of sub-models for diagnosing the HiveQL statement in different dimensions; based on the HiveQL statement and the complexity analysis result, the job parameters are optimized to obtain the optimized job parameters; based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement A set of development specifications is developed, and the HiveQL statements are optimized using a diagnostic model and a statement optimization model to obtain optimized HiveQL statements; the optimized job parameters and the optimized HiveQL statements are returned to the terminal device means, and the analytical and diagnostic capabilities of the diagnostic model are relied upon to automatically troubleshoot the problems existing in HiveQL from multiple dimensions, and a description of the problem modification method in the form of a natural language description is given, and then the problems existing in HiveQL are optimized according to the description according to the statement optimization model, thereby achieving the technical effect of improving the HiveQL optimization efficiency and lowering the user optimization threshold.

[0107] The following is a specific HiveQL optimization method provided in Example 2 of the present application, which includes:

[0108] Step 1: Receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device;

[0109] Step 2: Use the diagnostic model and the preset complexity index to analyze the complexity of the HiveQL statement and obtain the complexity analysis result;

[0110] Step 3: Obtain statistical information about the data volume of the table involved in the HiveQL statement; optimize the job parameters based on the statistical information about the data volume of the table and the complexity analysis results to obtain optimized job parameters;

[0111] Step 4: Based on the complexity analysis result, the HiveQL statement is rewritten to reduce the complexity using the statement optimization model to obtain the first HiveQL statement;

[0112] Step 5: According to the historical slow statement optimization set, the slow statement submodule of the diagnostic model is used to obtain the slow statement diagnosis conclusion including the code optimization logic described in natural language and the modified code; according to the slow statement diagnosis conclusion, the statement optimization model is used to optimize and modify the first HiveQL statement to obtain the second HiveQL statement;

[0113] Step 6: According to the historical rewrite optimization rule set, the rewrite optimization rule submodule of the diagnosis model is used to obtain a statement optimization rule diagnosis conclusion including the code rewrite logic described in natural language; according to the diagnosis of the statement optimization rule, the statement optimization model is used to optimize and modify the second HiveQL statement to obtain a third HiveQL statement;

[0114] Step 7: According to the historical development specification set, the development specification submodule of the diagnosis model is used to obtain a development specification diagnosis conclusion of the target development specification described in natural language; according to the development specification diagnosis conclusion, the statement optimization model is used to optimize and modify the third HiveQL statement to obtain an optimized HiveQL statement;

[0115] Step 8: Return the optimized job parameters and optimized HiveQL statements to the terminal device.

[0116] The HiveQL optimization method provided in this embodiment relies on the analytical and diagnostic capabilities of the diagnostic model to automatically troubleshoot HiveQL problems from multiple dimensions, and gives a description of the problem modification method in the form of a natural language description, and then optimizes the HiveQL problems according to the description based on the statement optimization model, thereby achieving the technical effect of improving HiveQL optimization efficiency and lowering the user optimization threshold.

[0117] Figure 3 This is a schematic diagram of the structure of the HiveQL optimization device provided in Example 3 of the present application, such as Figure 3 As shown, the HiveQL optimization device 30 provided in the present application includes:

[0118] The receiving module 301 is used to receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device;

[0119] An analysis module 302 is used to analyze the complexity of the HiveQL statement using a diagnosis model and a preset complexity index to obtain a complexity analysis result, wherein the diagnosis model includes a plurality of sub-models for performing different-dimensional diagnosis on the HiveQL statement;

[0120] A first optimization module 303 is used to optimize the job parameters based on the HiveQL statement and the complexity analysis result to obtain optimized job parameters;

[0121] The second optimization module 304 is used to optimize the HiveQL statement based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, using the diagnosis model and the statement optimization model to obtain the optimized HiveQL statement, wherein the statement optimization model is a model pre-trained to modify the HiveQL statement according to the natural language description content;

[0122] The return module 304 is used to return the optimized job parameters and the optimized HiveQL statement to the terminal device.

[0123] The HiveQL optimization device 30 provided in this embodiment can execute the method provided in the above method embodiment, and its implementation principle and technical effect are similar, which will not be described in detail in this embodiment.

[0124] In a possible implementation, the first optimization module 303 is specifically configured to:

[0125] Get the statistics of table data volume involved in HiveQL statements;

[0126] Based on the table data volume statistics and complexity analysis results, the job parameters are optimized to obtain the optimized job parameters.

[0127] In a possible implementation, the second optimization module 304 is specifically configured to:

[0128] Based on the complexity analysis results, the HiveQL statement is rewritten to reduce complexity using the statement optimization model to obtain the first HiveQL statement;

[0129] Using the diagnostic model, the first HiveQL statement is matched with multiple slow HiveQL statements in the historical slow statement optimization set, and it is determined whether there is a target slow HiveQL statement similar to the first HiveQL statement in the multiple slow HiveQL statements;

[0130] If there is a target slow HiveQL statement similar to the first HiveQL statement among the multiple slow HiveQL statements, the statement optimization model is used to optimize and modify the first HiveQL statement according to the code optimization logic corresponding to the target slow HiveQL statement and the modified code to obtain a second HiveQL statement;

[0131] According to the historical rewrite optimization rule set and the statement development specification set, the second HiveQL statement is optimized by using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement.

[0132] In a possible implementation, the second optimization module 304 is specifically configured to:

[0133] Using a diagnostic model, determining whether there is a target statement optimization rule that is not satisfied by the second HiveQL statement among multiple statement optimization rules;

[0134] If there is a target statement optimization rule that the second HiveQL statement does not satisfy, the statement optimization model is used to optimize and modify the second HiveQL statement according to the code rewriting logic corresponding to the target statement optimization rule to obtain a third HiveQL statement;

[0135] According to the statement development specification set, the third HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement.

[0136] In a possible implementation, the second optimization module 304 is specifically configured to:

[0137] Using the diagnostic model, determine whether there is a target requirement that is not met by the third HiveQL statement in the statement development specification set;

[0138] If there are target requirements that are not met by the third HiveQL statement, the statement optimization model is used to optimize and modify the third HiveQL statement based on the target requirements described in natural language to obtain an optimized HiveQL statement.

[0139] In a possible implementation, the complexity indicators in the analysis module 302 include: the number of complex operation keywords, the number of nesting levels, and the JOIN operation type.

[0140] In a possible implementation, the analysis module 302 is specifically configured to:

[0141] The diagnostic model is used to process the complexity of HiveQL statements based on complexity indicators and complexity analysis rules to obtain complexity analysis results.

[0142] Among them, the complexity analysis rules include: the number of complex operation keywords is proportional to the complexity; the number of nested layers is proportional to the complexity; the complexity corresponding to different JOIN operation types.

[0143] The HiveQL optimization device 30 provided in this embodiment can execute the method provided in the above method embodiment, and its implementation principle and technical effect are similar, which will not be described in detail in this embodiment.

[0144] Figure 4 This is a schematic diagram of the structure of an electronic device provided in Embodiment 4 of the present application. Figure 4As shown, the electronic device 40 provided in this embodiment includes: at least one processor 401 and a memory 402. Optionally, the device 40 also includes a communication component 403. The processor 401, the memory 402 and the communication component 403 are connected via a bus 404.

[0145] In a specific implementation process, at least one processor 401 executes the computer-executable instructions stored in the memory 402, so that at least one processor 401 executes the above method.

[0146] The specific implementation process of the processor 401 can be found in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be repeated here.

[0147] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When a processor executes the computer-executable instructions, the above method is implemented.

[0148] The present application also provides a computer program product, including a computer program, which implements the above method when executed by a processor.

[0149] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.

[0150] It should be further noted that, although the various steps in the flowchart are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a portion of the steps in the flowchart may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.

[0151] It should be understood that the above-mentioned device embodiments are only illustrative, and the device of the present application can also be implemented in other ways. For example, the division of units / modules in the above-mentioned embodiments is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units, modules or components can be combined, or can be integrated into another system, or some features can be ignored or not executed.

[0152] In addition, unless otherwise specified, each functional unit / module in each embodiment of the present application may be integrated into one unit / module, each unit / module may exist physically separately, or two or more units / modules may be integrated together. The above-mentioned integrated unit / module may be implemented in the form of hardware or in the form of a software program module.

[0153] If the integrated unit / module is implemented in the form of hardware, the hardware may be a digital circuit, an analog circuit, etc. The physical implementation of the hardware structure includes but is not limited to transistors, memristors, etc. If not otherwise specified, the processor may be any appropriate hardware processor, such as a central processing unit (CPU), a graphics processing unit (GPU), a field-programmable gate array (FPGA), a digital signal processor (DSP), an application-specific integrated circuit (ASIC), etc. If not otherwise specified, the memory may be any appropriate magnetic storage medium or magneto-optical storage medium, such as a resistive random access memory (RRAM), a dynamic random access memory (DRAM), a static random access memory (SRAM), an enhanced dynamic random access memory (EDRAM), a high-bandwidth memory (HBM), a hybrid memory cube (HMC), etc.

[0154] If the integrated unit / module is implemented in the form of a software program module and sold or used as an independent product, it can be stored in a computer-readable memory. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a memory, including a number of instructions to enable a computer device (which can be a personal computer, server or network device, etc.) to execute all or part of the steps of the various embodiments of the present application. The aforementioned memory includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.

[0155] In the above embodiments, the description of each embodiment has its own emphasis. For the part not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not 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.

[0156] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the following claims.

[0157] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.

Claims

1. A HiveQL optimization method, characterized in that: include: Receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device; The complexity of the HiveQL statement is analyzed by using a diagnostic model and a preset complexity index to obtain a complexity analysis result, wherein the diagnostic model includes a plurality of sub-models for performing different-dimensional diagnostics on the HiveQL statement; Based on the HiveQL statement and the complexity analysis result, the job parameters are optimized to obtain optimized job parameters; Based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, the HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement, wherein the statement optimization model is a model pre-trained to modify the HiveQL statement according to the natural language description content; The optimized job parameters and the optimized HiveQL statement are returned to the terminal device.

2. The method according to claim 1, characterized in that The optimizing the job parameters based on the HiveQL statement and the complexity analysis result to obtain optimized job parameters includes: Obtaining statistical information on the amount of table data involved in the HiveQL statement; Based on the table data volume statistics information and the complexity analysis result, the operation parameters are optimized to obtain optimized operation parameters.

3. The method according to claim 1, characterized in that: The historical slow statement optimization set includes multiple slow HiveQL statements that generate slow query results obtained based on historical data analysis, as well as code optimization logic described in natural language corresponding to each HiveQL statement and modified code; Accordingly, based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set, the HiveQL statement is optimized using the diagnostic model and the statement optimization model to obtain an optimized HiveQL statement, including: Based on the complexity analysis result, rewrite the HiveQL statement to reduce complexity using the statement optimization model to obtain a first HiveQL statement; Using the diagnostic model, matching the first HiveQL statement with multiple slow HiveQL statements in the historical slow statement optimization set, and determining whether there is a target slow HiveQL statement similar to the first HiveQL statement in the multiple slow HiveQL statements; If there is a target slow HiveQL statement similar to the first HiveQL statement among the multiple slow HiveQL statements, the statement optimization model is used to optimize and modify the first HiveQL statement according to the code optimization logic corresponding to the target slow HiveQL statement and the modified code, so as to obtain a second HiveQL statement; According to the historical rewrite optimization rule set and the statement development specification set, the second HiveQL statement is optimized using the diagnostic model and the statement optimization model to obtain an optimized HiveQL statement.

4. The method according to claim 3, characterized in that The historical rewriting optimization rule set includes multiple statement optimization rules obtained based on historical data analysis and code rewriting logic described in natural language corresponding to each statement optimization rule; Accordingly, the second HiveQL statement is optimized according to the historical rewrite optimization rule set and the statement development specification set by using the diagnostic model and the statement optimization model to obtain an optimized HiveQL statement, including: Using the diagnostic model, determining whether there is a target statement optimization rule that is not satisfied by the second HiveQL statement among the multiple statement optimization rules; If there is a target statement optimization rule that the second HiveQL statement does not satisfy, the statement optimization model is used to optimize and modify the second HiveQL statement according to the code rewriting logic corresponding to the target statement optimization rule to obtain a third HiveQL statement; According to the statement development specification set, the third HiveQL statement is optimized using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement.

5. The method according to claim 4, characterized in that The statement development specification set includes: at least one specification requirement of library name, table name, field name, table partition setting, view naming, variable naming described in natural language, and / or mandatory requirements for hiveQL code development; Accordingly, the third HiveQL statement is optimized according to the statement development specification set by using the diagnosis model and the statement optimization model to obtain an optimized HiveQL statement, including: Using the diagnostic model, determining whether there is a target requirement not satisfied by the third HiveQL statement in the statement development specification set; If there is a target requirement that the third HiveQL statement does not meet, the statement optimization model is used to optimize and modify the third HiveQL statement based on the target requirement described in natural language to obtain the optimized HiveQL statement.

6. The method according to any one of claims 1 to 5, characterized in that: The complexity indicators include: the number of complex operation keywords, the number of nested levels, and the JOIN operation type.

7. The method according to claim 6, characterized in that The complexity of the HiveQL statement is analyzed by using the diagnostic model and the preset complexity index to obtain the complexity analysis result, including: Using the diagnostic model, based on the complexity index and complexity analysis rules, the complexity of the HiveQL statement is processed to obtain the complexity analysis result; The complexity analysis rules include: The number of complex operation keywords is proportional to the complexity; The number of nesting levels is proportional to the complexity; The complexity of different JOIN operation types.

8. A HiveQL optimization device, characterized in that: include: The receiving module is used to receive the job parameters and HiveQL statements of the HiveQL task sent by the terminal device; An analysis module, used to analyze the complexity of the HiveQL statement using a diagnosis model and a preset complexity index to obtain a complexity analysis result, wherein the diagnosis model includes a plurality of sub-models for performing different-dimensional diagnosis on the HiveQL statement; A first optimization module, configured to optimize the job parameters based on the HiveQL statement and the complexity analysis result to obtain optimized job parameters; A second optimization module is used to optimize the HiveQL statement using the diagnosis model and the statement optimization model based on the complexity analysis result, the historical slow statement optimization set, the historical rewrite optimization rule set, and the statement development specification set to obtain an optimized HiveQL statement, wherein the statement optimization model is a model pre-trained to modify the HiveQL statement according to the natural language description content; A return module is used to return the optimized job parameters and the optimized HiveQL statement to the terminal device.

9. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

11. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a processor.

Images