CN-122020381-A - Quality evaluation method, device and storage medium of SQL (structured query language) statement

CN122020381ACN 122020381 ACN122020381 ACN 122020381ACN-122020381-A

Abstract

The embodiment of the application provides a quality evaluation method, equipment and storage medium of SQL sentences, which are used for accurately evaluating the quality of the SQL sentences. The method comprises the steps of receiving a first instruction, wherein the first instruction comprises an execution plan text, the execution plan text comprises a plurality of plan fragments, the plan fragments comprise execution plan information of a first SQL sentence on each execution node, the first instruction is used for indicating to analyze the execution plan text and returning an index value of at least one first index based on analysis results, responding to the first instruction, inputting the execution plan text into a first analyzer to generate a first analysis tree, the first nodes on the first analysis tree store the execution plan information included in the corresponding plan fragments, extracting the index value of the at least one first index from the first analysis tree and sending the index value to an interaction device for the interaction device to determine the quality score corresponding to the first SQL sentence, and therefore quality assessment of the SQL sentence can be accurately achieved.

Inventors

LIANG XIAONAN
LIU WENWU
JIA HAILIANG
ZHANG YUCHEN

Assignees

海南上湖信息技术有限公司
上海上湖信息技术有限公司

Dates

Publication Date: 20260512
Application Date: 20260130

Claims (10)

1. A quality assessment method of an SQL statement, applied to an parsing apparatus, the method comprising: The method comprises the steps of receiving a first instruction, wherein the first instruction comprises an execution plan text, the execution plan text comprises a plurality of plan fragments, and the plan fragments comprise execution plan information of the first SQL sentence on each execution node; Responding to the first instruction, inputting the execution plan text to a first parser, and generating a first parse tree, wherein the first parse tree comprises a plurality of first nodes which are in one-to-one correspondence with the plurality of plan fragments, each first node stores execution plan information contained in the corresponding plan fragment, and the connection relation among the plurality of first nodes is determined according to the data flow direction among the plurality of plan fragments; Extracting an index value of the at least one first index from the first parse tree; And sending the index value of the at least one first index to an interaction device, wherein the index value of the at least one first index is used for determining the quality score corresponding to the first SQL sentence.
2. The method of claim 1, wherein the method further comprises: Receiving a second instruction, wherein the second instruction is used for indicating the analysis device to analyze the first SQL statement and returning at least one index value of a second index based on the analysis result of the first SQL statement; Responding to the second instruction, inputting the first SQL statement to a second parser, and generating a grammar parsing tree corresponding to the first SQL statement; And extracting index values of the at least one second index from the grammar parsing tree, wherein the index values of the at least one second index are used for determining the quality score of the first SQL sentence.
3. A quality assessment method of an SQL statement, applied to an interaction device, the method comprising: Receiving a first SQL statement submitted by a user; Acquiring an execution plan text corresponding to the first SQL sentence; Transmitting a first instruction to an analysis device, wherein the first instruction comprises the execution plan text, and the first instruction is used for instructing the analysis device to analyze the execution plan text and returning an index value of at least one first index based on an analysis result of the execution plan text; receiving an index value of the at least one first index returned by the analysis device; and determining the quality score corresponding to the first SQL sentence based on the index value of the at least one first index.
4. The method of claim 3, wherein determining the quality score corresponding to the first SQL statement based on the index value of the at least one first index comprises: Determining the deduction value corresponding to the at least one first index respectively according to the index value of the at least one first index and at least one first scoring rule, wherein the at least one first index corresponds to the at least one first scoring rule one by one, and the first scoring rule comprises the deduction value corresponding to the index value of the corresponding first index when the index value of the corresponding first index meets a threshold value condition; And determining the quality score corresponding to the first SQL sentence according to the deduction value corresponding to at least one first index.
5. The method of claim 3, wherein after receiving the first SQL statement submitted by the user, further comprising: Sending a second instruction to the analysis device, wherein the second instruction is used for instructing the analysis device to analyze the first SQL sentence and returning at least one index value of a second index based on the analysis result of the first SQL sentence; receiving an index value of the at least one second index returned by the analysis device; the determining the quality score corresponding to the first SQL statement based on the index value of the at least one first index includes: and determining the quality score corresponding to the first SQL sentence based on the index value of the at least one first index and the index value of the at least one second index.
6. The method of claim 5, wherein the determining the quality score corresponding to the first SQL statement based on the index value of the at least one first index and the index value of the at least one second index comprises: Determining the deduction value corresponding to the at least one first index respectively according to the index value of the at least one first index and at least one first scoring rule, wherein the at least one first index corresponds to the at least one first scoring rule one by one, and the first scoring rule comprises the deduction value corresponding to the index value of the corresponding first index when the index value of the corresponding first index meets a threshold value condition; determining the deduction value corresponding to the at least one second index according to the index value of the at least one second index and at least one second scoring rule, wherein the at least one second index corresponds to the at least one second scoring rule one by one, and the second scoring rule comprises the deduction value corresponding to the index value of the corresponding second index when the index value of the corresponding second index meets a threshold value condition; And determining the quality score corresponding to the first SQL sentence according to the deduction value respectively corresponding to the at least one first index and the deduction value respectively corresponding to the at least one first index.
7. The method of any one of claims 1-6, wherein the at least one first indicator comprises at least one of: scanning the partition number of the single table; a single table scan file number; Scanning the file size by a single table; The total scanned file size of the first SQL statement; Number of single table scans; broadcast file size; large table JOIN small table; the single node estimates the size of the used memory.
8. The method of any one of claims 2, 5-6, wherein the second index comprises at least one of: Whether the JOIN operation lacks an ON condition; Inquiring the number of the fields; the single table scans the number of partitions.
9. A computing device, comprising: a memory for storing program instructions; A processor for invoking program instructions stored in said memory to perform the method of any of claims 1 to 8 in accordance with the obtained program.
10. A computer-readable storage medium storing computer-executable instructions for causing a computer to perform the method of any one of claims 1 to 8.

Description

Quality evaluation method, device and storage medium of SQL (structured query language) statement Technical Field The embodiment of the application relates to the technical field of big data, in particular to a quality evaluation method, equipment and storage medium of SQL sentences. Background In the big data age, a large amount of data is not being produced at any time. In database management systems, structured query language (Structured Query Language, SQL) is often used, and various operations of data, such as query operations, can be performed on a database using SQL statements. IMPALA SQL is SQL query language running on APACHE IMPALA engine, the APACHE IMPALA engine is a large-scale parallel processing architecture which is specially designed for Hadoop ecosystem and is open-source, and aims to realize low-delay and high-performance interactive query analysis of mass data. Before APACHE IMPALA engine executes SQL statement, quality evaluation is carried out on SQL statement, so that guidance basis can be provided for writing high-quality SQL for users. Currently, quality evaluation of an SQL statement to be executed generally depends on parsing an SQL text, and then quality evaluation is performed on the SQL statement in combination with metadata information of a table, so as to determine whether to execute the SQL statement based on a quality evaluation result. But this parsing method cannot accurately evaluate the quality of the SQL statement. Disclosure of Invention The embodiment of the application provides a quality evaluation method, equipment and storage medium of SQL sentences, which are used for accurately evaluating the quality of the SQL sentences. In a first aspect, an embodiment of the application provides a quality evaluation method of an SQL sentence, which is executed by an analysis device, and the method comprises the steps of receiving a first instruction, wherein the first instruction comprises an execution plan text, the execution plan text comprises a plurality of plan fragments, the plan fragments comprise execution plan information of a first SQL sentence on each execution node, the first instruction is used for indicating the analysis device to analyze the execution plan text and returning an index value of at least one first index based on an analysis result of the execution plan text, responding to the first instruction, inputting the execution plan text into a first analyzer to generate a first analysis tree, the first analysis tree comprises a plurality of first nodes which are in one-to-one correspondence with the plurality of plan fragments, each first node stores the execution plan information which is included by the corresponding plan fragment, the connection relation among the plurality of first nodes is determined according to a data flow direction among the plurality of plan fragments, extracting the index value of the at least one first index from the first analysis tree, and sending the index value of the at least one first index to the interaction device, and the index value of the at least one first index is used for determining the quality corresponding to the SQL sentence. In the scheme, the first analysis tree is generated by carrying out structural analysis on the execution plan text corresponding to the first SQL sentence, so that the index value of at least one first index is extracted from the first analysis tree accurately, and then the quality score corresponding to the first SQL sentence is determined based on the index value of at least one first index, so that the quality evaluation of the SQL sentence can be realized accurately. Optionally, the at least one first index includes at least one of a single table scan partition number, a single table scan file size, a total scan file size of the first SQL statement, a single table scan number, a broadcast file size, a large table JOIN small table, and a single node estimated usage memory size. Optionally, the method further comprises the steps of receiving a second instruction, wherein the second instruction is used for indicating the analysis device to analyze the first SQL sentence and returning an index value of a second index based on an obtained second analysis result, responding to the second instruction, inputting the first SQL sentence to a second analyzer to generate a grammar analysis tree corresponding to the first SQL sentence, and extracting the index value of the second index from the grammar analysis tree, wherein the index value of the second index is used for determining a second quality score of the first SQL sentence. In a second aspect, an embodiment of the application provides a quality evaluation method of an SQL sentence, which is executed by an interaction device, and the method comprises the steps of receiving a second instruction, wherein the second instruction is used for indicating the analysis device to analyze the first SQL sentence, returning an index value of at least on