Quality evaluation method and device for SQL statement and storage medium

Abstract

The embodiment of the invention provides a quality evaluation method and device for SQL statements and a storage medium. The quality evaluation method and device are used for accurately evaluating the quality of the SQL statements. The method comprises the steps that a first instruction is received, the first instruction comprises an execution plan text, the execution plan text comprises a plurality of plan segments, and the plan segments comprise execution plan information of a first SQL statement on execution nodes; 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 an analysis result; in response to the first instruction, inputting the execution plan text to a first parser, generating a first parse tree, extracting an index value of at least one first index from the first parse tree, and sending the index value to an interaction device, the method and the device are used for determining the quality score corresponding to the first SQL statement by the interaction device, so that the quality of the SQL statement can be accurately evaluated.

Description

Technical Field

[0001] This application relates to the field of big data technology, and in particular to a method, device and storage medium for evaluating the quality of SQL statements. Background Technology

[0002] In the era of big data, massive amounts of data are generated constantly. Structured Query Language (SQL) is frequently used in database management systems. SQL statements allow for various data operations on the database, such as querying. Impala SQL is a SQL query language that runs on the Apache Impala engine, an open-source, massively parallel processing architecture designed specifically for the Hadoop ecosystem, aiming to achieve low-latency, high-performance interactive query analysis of massive amounts of data.

[0003] Before executing SQL statements, the Apache Impala engine performs a quality assessment on the SQL statements, providing guidance for users to write high-quality SQL. Currently, the quality assessment of SQL statements to be executed typically relies on parsing the SQL text and then combining it with table metadata information to evaluate the SQL statement's quality, thus determining whether to execute the SQL statement based on the quality assessment result. However, this parsing method cannot accurately assess the quality of SQL statements. Summary of the Invention

[0004] This application provides a method, device, and storage medium for evaluating the quality of SQL statements, so as to accurately evaluate the quality of SQL statements.

[0005] In a first aspect, embodiments of this application provide a method for quality assessment of SQL statements. This method is executed by a parsing device and includes: receiving a first instruction, the first instruction including execution plan text, the execution plan text including multiple plan fragments, the plan fragments including execution planning information of a first SQL statement at each execution node; the first instruction instructing the parsing device to parse the execution plan text and return the index value of at least one first indicator based on the parsing result of the execution plan text; responding to the first instruction, inputting the execution plan text into a first parser to generate a first parse tree, the first parse tree including multiple first nodes corresponding one-to-one with the multiple plan fragments, each first node storing the execution planning information included in the corresponding plan fragment, the connection relationship between the multiple first nodes being determined according to the data flow direction between the multiple plan fragments; extracting the index value of at least one first indicator from the first parse tree; and sending the index value of at least one first indicator to an interactive device, the index value of the at least one first indicator being used to determine the quality score corresponding to the first SQL statement.

[0006] In the above scheme, by performing structured parsing on the execution plan text corresponding to the first SQL statement to generate a first parse tree, it is helpful to accurately extract the indicator value of at least one first indicator from the first parse tree. Then, based on the indicator value of at least one first indicator, the quality score corresponding to the first SQL statement is determined, which helps to accurately evaluate the quality of the SQL statement.

[0007] Optionally, the at least one first indicator includes at least one of the following: number of partitions scanned in a single table; number of files scanned in a single table; size of files scanned in a single table; total file size scanned by the first SQL statement; number of single table scans; broadcast file size; large table JOIN small table; estimated memory usage per node.

[0008] Optionally, the method further includes: receiving a second instruction, the second instruction being used to instruct the parsing device to parse the first SQL statement and return the index value of the second index based on the obtained second parsing result; responding to the second instruction, inputting the first SQL statement into the second parser to generate a syntax parse tree corresponding to the first SQL statement; and extracting the index value of the second index from the syntax parse tree, the index value of the second index being used to determine the second quality score of the first SQL statement.

[0009] Secondly, embodiments of this application provide a method for evaluating the quality of an SQL statement. This method is executed by an interactive device and includes: receiving a second instruction, which instructs the parsing device to parse a first SQL statement and return the index value of at least one second indicator based on the parsing result of the first SQL statement; responding to the second instruction, inputting the first SQL statement into a second parser to generate a syntax parse tree corresponding to the first SQL statement; and extracting the index value of the at least one second indicator from the syntax parse tree, wherein the index value of the at least one second indicator is used to determine the quality score of the first SQL statement.

[0010] Optionally, at least one of the following is included as a first indicator: number of partitions scanned per table; number of files scanned per table; file size scanned per table; total file size scanned by the first SQL statement; number of table scans; broadcast file size; large table JOIN small table; estimated memory usage per node.

[0011] Optionally, determining the quality score corresponding to the first SQL statement based on the indicator value of at least one first indicator includes: determining the deduction value corresponding to each of the at least one first indicator based on the indicator value of at least one first indicator and at least one first scoring rule; at least one first indicator corresponds one-to-one with at least one first scoring rule, and the first scoring rule includes the deduction value corresponding to the indicator value of the corresponding first indicator when the indicator value meets the threshold condition; and determining the quality score corresponding to the first SQL statement based on the deduction value corresponding to each of the at least one first indicator.

[0012] Optionally, after receiving the first SQL statement submitted by the user, the method further includes: sending a second instruction to the parsing device, the second instruction being used to instruct the parsing device to parse the first SQL statement and return the value of at least one second indicator based on the parsing result of the first SQL statement; receiving the value of at least one second indicator returned by the parsing device; and determining the quality score corresponding to the first SQL statement based on the value of at least one first indicator, including: determining the quality score corresponding to the first SQL statement based on the value of at least one first indicator and the value of at least one second indicator.

[0013] Optionally, at least one of the following secondary indicators may be included: whether the JOIN operation lacks an ON condition; the number of query fields; and the number of partitions scanned in a single table.

[0014] Optionally, determining the quality score corresponding to the first SQL statement based on the indicator values ​​of at least one first indicator and at least one second indicator includes: determining the deduction value corresponding to each of the at least one first indicator according to the indicator values ​​of at least one first indicator and at least one first scoring rule; at least one first indicator corresponds one-to-one with at least one first scoring rule, and the first scoring rule includes the deduction value corresponding to the indicator value of the corresponding first indicator when it meets a threshold condition; determining the deduction value corresponding to each of the at least one second indicator according to the indicator values ​​of at least one second indicator and at least one second scoring rule; at least one second indicator corresponds one-to-one with at least one second scoring rule, and the second scoring rule includes the deduction value corresponding to the indicator value of the corresponding second indicator when it meets a threshold condition; and determining the quality score corresponding to the first SQL statement based on the deduction values ​​corresponding to each of the at least one first indicator and the deduction values ​​corresponding to each of the at least one first indicator.

[0015] Optionally, before sending the first instruction to the parsing device, the method further includes: determining that the quality score corresponding to the first SQL statement is greater than or equal to the first threshold based on the deduction values ​​corresponding to at least one second indicator.

[0016] Optionally, the method further includes: displaying the quality score, a first control, and a second control corresponding to the first SQL statement through a display interface, wherein the first control is used to trigger an instruction to execute the first SQL statement, and the second control is used to trigger an instruction to abandon the execution of the first SQL statement.

[0017] Thirdly, embodiments of this application also provide an interactive device, including: An interface unit is configured to receive a first instruction, the first instruction including execution plan text, the execution plan text including multiple plan fragments, the plan fragments including execution planning information of the first SQL statement at each execution node; the first instruction is configured to instruct a parsing device to parse the execution plan text and return the indicator value of at least one first indicator based on the parsing result of the execution plan text. The processing unit is configured to, in response to the first instruction, input the execution plan text into a first parser to generate a first parse tree, the first parse tree including a plurality of first nodes corresponding one-to-one with the plurality of plan segments, each first node storing the execution planning information included in the corresponding plan segment, the connection relationship between the plurality of first nodes being determined according to the data flow direction between the plurality of plan segments; and extract the indicator value of the at least one first indicator from the first parse tree. The interface unit is also configured to send the index value of the at least one first index to the interactive device, the index value of the at least one first index being used to determine the quality score corresponding to the first SQL statement.

[0018] Fourthly, embodiments of this application also provide a parsing apparatus, including: An interface unit is configured to receive a first SQL statement submitted by a user; obtain the execution plan text corresponding to the first SQL statement; send a first instruction to a parsing device, the first instruction including the execution plan text, the first instruction being configured to instruct the parsing device to parse the execution plan text, and return the index value of at least one first index based on the parsing result of the execution plan text; and receive the index value of the at least one first index returned by the parsing device. The processing unit is configured to determine the quality score corresponding to the first SQL statement based on the index value of the at least one first index.

[0019] Fifthly, embodiments of this application provide a computing device, including: Memory, used to store program instructions; A processor is configured to invoke program instructions stored in the memory and execute the foregoing aspects and any alternative methods relating to those aspects in accordance with the obtained program instructions.

[0020] Sixthly, embodiments of this application provide a computer-readable storage medium storing computer-executable instructions for causing a computer to perform the foregoing aspects and any of the optional methods associated with those aspects.

[0021] In a seventh aspect, embodiments of this application provide a computer program product that, when run on a computer, causes the computer to perform the method steps in the first aspect or any possible implementation of the first aspect. Attached Figure Description

[0022] Figure 1 This is a system architecture diagram applicable to the embodiments of this application; Figure 2 A flowchart illustrating a method for evaluating the quality of SQL statements provided in an embodiment of this application; Figure 3 This application provides a schematic diagram of explain text parsing in an embodiment. Figure 4 A schematic diagram of a display interface provided in an embodiment of this application; Figure 5 This is a schematic diagram of another display interface provided in an embodiment of this application; Figure 6 This is a schematic diagram of the analytical device structure provided in an embodiment of this application; Figure 7 This is a schematic diagram of the structure of a computing device provided in an embodiment of this application. Detailed Implementation

[0023] To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0024] As described in the background section, current quality assessment of SQL statements executed on big data platforms (such as Impala) typically relies on parsing the SQL text and then combining it with table metadata information to evaluate the SQL statement's quality, thereby determining whether to execute the SQL statement based on the quality assessment result. However, this parsing method cannot accurately assess the quality of SQL statements.

[0025] Therefore, embodiments of this application provide a method for evaluating the quality of SQL statements, so as to accurately evaluate the quality of SQL statements.

[0026] First, this application provides a suitable system architecture, such as... Figure 1 As shown, the system architecture includes a terminal device 101, a server 102 running an interactive site, a server 103 running a parsing site, a server 104 running the Impala engine, and a server 105 that actually executes SQL statements. The server used to execute SQL statements can also be called an execution site.

[0027] Terminal devices may include, but are not limited to, smartphones, tablets, laptops, and desktop computers. Servers may be independent physical servers, server clusters or distributed systems composed of multiple physical servers, or cloud servers providing basic cloud computing services such as cloud services, cloud databases, cloud computing, and big data and artificial intelligence platforms. Terminal devices and servers may be connected directly or indirectly via wired or wireless communication; this application does not impose any restrictions on this.

[0028] In this embodiment of the application, the terminal device can receive user input to perform operations on the database, such as receiving SQL statements input by the user and sending instructions to multiple servers to parse or execute the SQL statements.

[0029] Users submit SQL statements to be executed on their terminal devices. These statements are then sent to a server running an interactive site. Firstly, the interactive site sends the SQL statement to a parsing site for initial parsing (pre-parse). After initial parsing, the parsing site sends the parsing result back to the interactive site. Secondly, the interactive site sends the SQL statement to the Impala engine to retrieve the execution plan text. This execution plan text is then sent to the parsing site for second-level parsing (pre-parse). After second-level parsing, the parsing site sends the parsing result back to the interactive site.

[0030] The interactive site can calculate the quality score of the SQL statement based on at least one of the parsing results from the first-level and second-level parsing layers. This quality score is used to assess whether the SQL statement should continue execution. The interactive site can determine whether to continue execution based on preset rules, and it can also display the SQL statement's quality score on the user's terminal interface, allowing the user to decide whether to continue. If the user chooses to continue, the interactive site sends instructions to the Impala engine to execute the SQL statement. The Impala engine then executes the SQL statement through multiple execution sites, which can be servers or server clusters.

[0031] Based on the above description, embodiments of this application provide a flowchart of a method for evaluating the quality of SQL statements. For ease of description, the method is described using an interactive device and a parsing device interacting together. The interactive device can be... Figure 1 The server 102 or interactive site shown can be used as a parsing device. Figure 1 The server shown is 103, which is running a DNS server or a DNS server. For example... Figure 2 As shown, the method includes the following steps: Step 201: The interactive device receives the first SQL statement submitted by the user.

[0032] In some embodiments, the first SQL statement may include a single SQL statement, such as: insertoverwrite table ods.test select columnm1, columnm2, columnm3 from test.table; the first SQL statement may also include multiple SQL statements, and this application does not limit this.

[0033] Step 202: The interactive device obtains the execution plan text corresponding to the first SQL statement.

[0034] In some embodiments, after receiving the first SQL statement submitted by the user, the interactive device sends the first SQL statement to the processing engine, which generates the execution plan text corresponding to the first SQL statement based on the first SQL statement, and the interactive device obtains the execution plan text corresponding to the first SQL statement from the processing engine.

[0035] For example, the interactive device sends a plan generation instruction to a server running a processing engine. The plan generation instruction includes a first SQL statement. The plan generation instruction is used to instruct the processing engine to generate the execution plan text corresponding to the first SQL statement and return the execution plan text. After generating the execution plan text, the processing engine sends it to the interactive device, so that the interactive device can obtain the execution plan text.

[0036] The execution plan text includes multiple plan fragments, each containing execution planning information for the first SQL statement at various execution nodes. In this embodiment, the plan fragments may also be referred to as plan slices, which will not be elaborated further below.

[0037] In some embodiments, the processing engine is, for example, the Apache Impala distributed SQL query engine, or simply the Impala engine. After receiving the first SQL statement, the Impala engine generates an Impala execution plan (Explain Plan) based on it. This Impala execution plan can be a distributed parallel execution scheme represented in a tree structure, generated by the Impala engine for the received first SQL statement. This Impala execution plan is automatically generated by the Query Planner before the first SQL statement is actually executed. The Impala execution plan details the operation steps, data flow paths, and resource allocation of the first SQL statement on each execution node in the cluster. The core feature of the Impala execution plan is that it represents the entire first SQL statement as a complete execution plan tree. By dividing it into multiple parallel-executable plan fragments, combined with optimization techniques such as predicate pushdown and partition pruning, it avoids the overhead of writing multi-stage intermediate results to disk in traditional batch processing models, enabling high-performance, low-latency interactive queries on large-scale data.

[0038] The execution plan is output in text form, that is, the execution plan text obtained by the interactive device.

[0039] Below is an example of an execution plan text: MAX PER-HOST RESOURCE RESERVATION: MEMORY=72.00MB THREADS=4 PER-HOST RESOURCE ESTIMATES: MEMORY=316MB WARNING: THE FOLLOWING TABLES ARE MISSING RELEVANT TABLE AND / ORCOLUMN STATISTICS. ODS.CPU ANALYZED QUERY: SELECT DISTINCT (SN) FROM ODS.CPU

[0040] F02:……

[0041] F01:……

[0042] F00:PLAN FRAGMENT [RANDOM] HOSTS=4 INSTANCES=4

[0043] PER-HOST RESOURCES: MEM-ESTIMATE=168.00MB MEM- RESERVATION=38.00MBTHREAD-RESERVATION=2

[0044] DATASTREAM SINK [FRAGMENT=F01, EXCHANGE=02, HASH((SN))]

[0045] | MEM-ESTIMATE=0B MEM-RESERVATION=0B THREAD- RESERVATION=0

[0046] 01:AGGREGATE [STREAMING]

[0047] | GROUP BY: (SN)

[0048] | MEM-ESTIMATE=128.00MB MEM-RESERVATION=34.00MB SPILL-BUFFER=2.00MBTHREAD-RESERVATION=0

[0049] | TUPLE-IDS=1 ROW-SIZE=12B CARDINALITY=1.04G

[0050] | IN PIPELINES: 00(GETNEXT)

[0051] |

[0052] 00:SCAN HDFS [ODS.CPU, RANDOM]

[0053] HDFS PARTITIONS=17 / 303 FILES=367 SIZE=9.40GB

[0054] STORED STATISTICS:

[0055] TABLE: ROWS=UNAVAILABLE SIZE=UNAVAILABLE

[0056] PARTITIONS: 17 / 17 ROWS=1.04G

[0057] COLUMNS: UNAVAILABLE

[0058] EXTRAPOLATED-ROWS=DISABLED MAX-SCAN-RANGE-ROWS=2.98M

[0059] MEM-ESTIMATE=40.00MB MEM-RESERVATION=4.00MB THREAD- RESERVATION=1

[0060] TUPLE-IDS=0 ROW-SIZE=12B CARDINALITY=1.04G

[0061] IN PIPELINES: 00 (GET NEXT)

[0062] In this example of the execution plan text, the execution plan text includes header information and multiple plan fragments. In the example above, the first five paragraphs are the header information, followed by multiple plan fragments. In the example above, the plan fragments following the header information are, in order, plan fragment F02, plan fragment F01, and plan fragment F00. The specific content of plan fragments F02 and F01 in the example above is indicated by “…”, and their specific content is similar to that of plan fragment F00. Please refer to the specific content of plan fragment F00, which will not be listed here.

[0063] The header information is used for resource estimation and reservation. In the example above, "MAX PER-HOSTRESOURCE RESERVATION: MEMORY=72.00MB THREADS=4" indicates that each node participating in the query is expected to require a maximum of 72MB of memory and 4 execution threads. "PER-HOST RESOURCE ESTIMATES: MEMORY=316MB" represents the optimizer's estimate of the total memory usage for each node. It is normal for the reserved value to be less than the estimated value, because the reservation is the minimum resource required to guarantee execution, while the estimate includes the possible usage throughout the entire lifecycle.

[0064] In the above example of multiple plan segments, plan segment F00 is the starting point of the query, responsible for reading data from the storage layer and performing the first round of processing. Its function is to scan HDFS data and perform preliminary deduplication and aggregation. The specific content of plan segment F00 is described below.

[0065] "HOSTS=4 INSTANCES=4" means that the task is distributed across 4 execution nodes for parallel execution, with each execution node running one instance, which helps to accelerate the processing of large amounts of data.

[0066] “MEM-ESTIMATE=168.00MB MEM-RESERVATION=38.00MB THREAD-RESERVATION=2” represents some reservation information, where “MEM-ESTIMATE” indicates the estimated value of memory, MEM-RESERVATION indicates the reserved value of memory, and THREAD-RESERVATION indicates the reserved value of threads.

[0067] “DATASTREAM SINK [FRAGMENT=F01, EXCHANGE=02, HASH((SN))]” indicates the direction of data flow. The data will flow to EXCHANGE=02 in FRAGMENT=F01, and the HASH logic will be processed according to the SN field.

[0068] "00:SCAN HDFS [ODS.CPU, RANDOM]" indicates that node 00 is responsible for scanning table ODS.CPU. "RANDOM" indicates that data is read in a non-deterministic order.

[0069] "HDFS PARTITIONS=17 / 303 FILES=367 SIZE=9.40GB" means there are 303 partitions, but the query only scanned 17 of them. These 17 partitions contain 367 files, and the data volume of these 17 partitions reaches 9.4GB. "PARTITIONS: 17 / 17 ROWS=1.04G" means that the 17 partitions scanned contain approximately 1.04 billion rows of data.

[0070] "01:AGGREGATE [STREAMING]" indicates that after scanning the data, execution node 01 immediately performs a deduplication operation on the SN column locally on each execution node. "STREAMING" is an efficient aggregation method that performs the aggregation step by step during the data flow, without waiting for all the data to arrive.

[0071] Step 203: The interactive device sends a first instruction to the parsing device. The first instruction includes an execution plan text. The first instruction is used to instruct the parsing device to parse the execution plan text and return the index value of at least one first index based on the parsing result of the execution plan text.

[0072] Correspondingly, the parsing device receives the first instruction. Then, step 204 is executed.

[0073] Step 204: In response to the first instruction, the parsing device inputs the execution plan text into the first parser to generate a first parse tree. The first parse tree includes multiple first nodes that correspond one-to-one with multiple plan segments. Each first node stores the execution planning information included in the corresponding plan segment. The connection relationship between the multiple first nodes is determined according to the data flow direction between the multiple plan segments.

[0074] In some embodiments, after the parsing device inputs the execution plan text into the first parser, the first parser can parse the header information and multiple plan fragments of the execution plan text according to preset parsing rules, thereby obtaining detailed execution planning information in the header information and multiple plan fragments.

[0075] In this embodiment of the application, the preset parsing rules are as follows (1) to (17): (1) In ROOT, “Max Per-Host Resource Reservation: Memory=%s Threads=%d” represents the maximum reserved value for each execution node, where “Memory=%s” represents the reserved memory value and “Threads=%d” represents the reserved number of threads. For example, “Max Per-Host Resource Reservation: Memory=177.94MB Threads=9” means that the maximum reserved memory value for each execution node is 177.94MB and the maximum reserved number of threads is 9.

[0076] (2) In ROOT, “Per-Host Resource Estimates: Memory=%s” represents the estimated value for each execution node, where “Memory=%s” represents the estimated memory value. For example, “Per-Host Resource Estimates: Memory=2.92GB” means that the estimated memory value for each execution node is 2.92GB.

[0077] (3) In the Fragment, “%s%s:PLAN FRAGMENT [%s] hosts=%d instances=%d” indicates the display of the Fragment. “%s%s” represents the prefix and fragmentId, where fragmentId represents the identifier of the Fragment. “PLAN FRAGMENT [%s]” defines how the data is distributed among the nodes. Common types include UNPARTITIONED (results are concentrated at a single point) and RANDOM (data is randomly distributed). hosts=%d indicates the number of nodes executing the Fragment, and instances=%d indicates the number of instances executing the Fragment. Since the mt_dop parameter is not set, it is consistent with the number of nodes executing the Fragment. For example, “F05:PLAN FRAGMENT [HASH(t0.user_id)] ​​hosts=28 instances=28” means that the number of nodes executing Fragment05 is 28, and the number of instances executing Fragment05 is 28.

[0078] (4) In the Fragment, “Per-Host Resources: mem-estimate=%s mem-reservation=%s thread-reservation=%d” indicates some resource estimation information for a certain fragment. “mem-estimate=%s” indicates the estimated memory value, “mem-reservation=%s” indicates the minimum reserved memory value, and “thread-reservation=%d” indicates the number of reserved threads. For example, “Per-Host Resources: mem-estimate=1.02GB mem-reservation=1.94MB thread-reservation=1” means that the estimated memory value for a certain Fragment for each execution node is 1.02GB, the minimum reserved memory value is 1.94MB, and the number of reserved threads is 1.

[0079] (5) In the subtype "HdfsTableSink" of DataSink, "%sWRITE TO HDFS [%s, OVERWRITE=%s, PARTITION-KEYS=(%s)]| partitions=%d| output exprs: %s| mem-estimate=%s mem-reservation =%s thread-reservation=%d" indicates some information for writing to HDFS. "[%s" indicates the table information to be written. "OVERWRITE=%s" indicates whether to overwrite the write. "PARTITION-KEYS=(%s)" indicates the partition key to be written, which is optional and can have multiple keys. "partitions=%d" indicates the number of partitions to be written. "output exprs: %s" indicates the field information to be written. "mem-estimate=%s" indicates the estimated memory value. "mem-reservation=%s" indicates the minimum reserved memory value. "thread-reservation=%d" indicates the number of reserved threads. For example, "WRITE TO HDFS[lkzc.zqy_loan_stats_mid, OVERWRITE=true]| partitions=1| output exprs:userid, content_cardtype, content_instshareprofitnew, activitytime, eventid,content_usertype, dt| mem-estimate=17.14MB mem-reservation=0B thread-reservation=0" means that the table written to HDFS is lkzc.zqy_loan_stats_mid, overwriting the existing data, with 1 partition written, and the fields written being userid, content_cardtype, content_instshareprofitnew, activitytime, eventid, content_usertype, and dt; the estimated memory usage is 17.14MB, the minimum reserved memory is 0B, and the reserved thread count is 0.

[0080] (6) In the subtype "PlanRootSink" of DataSink, "%sPLAN-ROOT SINK|output exprs:%s|mem-estimate =%s mem-reservation=%s thread-reservation=%d" represents the output information for select, which is generally PlanRootSink. Output exprs: %s represents the field information written, mem-estimate=%s represents the estimated memory value, mem-reservation=%s represents the minimum reserved memory value, and thread-reservation=%d represents the number of reserved threads. For example, "PLAN-ROOT SINK|output exprs: area,anquan, shuliang, sum(shuliang)| mem-estimate=0B mem-reservation=0B thread-reservation=0" means that the field information written is area, anquan, shuliang, sum(shuliang); the estimated memory value is 0B, the minimum reserved memory value is 0B, and the number of reserved threads is 0.

[0081] (7) In the subtype "JoinBuildSink" of DataSink, "%sJOIN BUILD|join-table-id=%splan-id=%s cohort-id=%s" means that "join-table-id=%s" represents a unique identifier for a table participating in the Join operation, which is only used within the query plan to distinguish different tables; "plan-id=%s" represents a specific execution plan shard in the query plan; and "cohort-id=%s" represents the target group to which the table data should be sent in distributed execution. For example, "JOIN BUILD | join-table-id=1 plan-id=1 cohort-id=0" means that in plan shard 1 (plan-id=1), data is being prepared to build the table with ID 1 in the join operation (join-table-id=1, i.e., the customers table), and this data will be distributed to all execution nodes in cohort 0 (cohort-id=0).

[0082] (8) In the subtype "DataStreamSink" of DataSink, "%sDATASTREAM SINK [FRAGMENT=%s, EXCHANGE=%s, %s]", each FRAGMENT is an independent execution unit of the query plan. "FRAGMENT=%s" indicates the FRAGMENT ID that will be executed next; "EXCHANGE=%s" indicates that this ID will be executed by both the sender (DATASTREAMSINK) and the receiver (EXCHANGE). In connection with each other, "%s" describes the data distribution strategy among cluster nodes. Common types include: HASH, BROADCAST, RANDOM, and UNPARTITIONED. HASH distributes data to corresponding nodes based on the hash values ​​of one or more columns, often used for subsequent hash joins or group aggregations. BROADCAST replicates all data and sends it to all execution nodes involved in the query, typically used for join operations when a table is small. RANDOM distributes data randomly to evenly distribute the load, such as during data redistribution. UNPARTITIONED indicates that data is not partitioned, typically used to aggregate results to the coordinator node. For example, "DATASTREAM SINK[FRAGMENT=F02, EXCHANGE=02, HASH(c.region)]" means that DATASTREAM SINK points to the node EXCHANGE=02 within FRAGMENT=F02. This indicates that the F02 node will receive data distributed by c.region (region) HASH (hash) and perform calculations there.

[0083] (9) The "%s:AGGREGATE [%s]| output: %s| group by: %s| having:%s| mem-estimate=%s mem-reservation=%s spill-buffer=%s thread-reservation=%d|tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)" in PlanNode indicates that this execution node is the execution node for aggregation calculation. The first parameter "%s" indicates whether nodeId[%s] uses STREAMING or FINALIZE. STREAMING means this node uses streamingpreaggregation. Invalid if this is a merge agg. FINALIZE means it performs the finalize step. output: %s indicates the output field information, group by: %s indicates the grouping field information, having: %s indicates the filtering conditions after grouping, and mem-estimate=%s This indicates the estimated memory usage. `mem-reservation=%s` represents the minimum reserved memory value, `spill-buffer=%s` represents the estimated overflow buffer size, and `thread-reservation=%d` represents the number of reserved threads. `tuple-ids=%d` indicates that the ids are materialized by the tree rooted at this node. `row-size=%s` represents the average size per row. `cardinality=%s` represents the cardinality, noting that the unit is incremented in 1000. In pipelines: `%s(%s)` represents the PlanNode associated with the pipeline processing. Here, `%s(%s)` represents the PlanNode's id and the type of `TExecNodePhase`.For example, "02:AGGREGATE [STREAMING]| output: min(reg_time), min(reg_qudao), min(guiyin_qudao)| group by: user_id| mem-estimate=128.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0| tuple-ids=2 row-size=44B cardinality=251.62M| in pipelines: 01(GETNEXT)" indicates that the current node is the execution node performing aggregation calculations, with nodeId 02, using STREAMING, and the output fields are min(reg_time), min(reg_qudao), min(guiyin_qudao); the grouping field is user_id; the estimated memory is 128.00MB; the minimum reserved memory is 34.00MB; and the estimated overflow buffer size is 2.00MB. The number of reserved threads is 0; tuple-ids=2 is an identifier pointing to a specific form or position of data in the processing pipeline; a tuple can be understood as a set of fields in a row of data that are related to a specific operation (such as scan, aggregation, join); a complex query will generate multiple tuples; here tuple-ids=2 indicates that the data processed or output by the current operation (such as aggregation, join) is defined by the tuple with ID 2; the average size of each row is 44B; the cardinality is 251.62M, and the PlanNode associated with pipeline processing is 01 (GETNEXT).

[0084] (10) The “%s:UNION| predicates: %s| constant-operands=%d|pass-through-operands: %s| mem-estimate=%s mem-reservation=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)” in PlanNode indicates that the node is the execution node for merging. The first parameter “%s:” represents the nodeId, “predicates: %s” represents the pushdown condition, “constant-operands=%d” is unknown, “pass-through-operands: %s” is unknown, “mem-estimate=%s” represents the estimated memory value, “mem-reservation=%s” represents the minimum reserved memory value, “spill-buffer=%s” represents the estimated overflow buffer size, and “thread-reservation=%d” represents the reserved thread number. `tuple-ids=%d` indicates that the ids are materialized by the tree rooted at this node. `row-size=%s` indicates the average size of each row. `cardinality=%s` indicates the cardinality, noting that the unit is incremented in 1000. In pipelines: `%s(%s)` indicates the PlanNode associated with the pipeline processing. Here, `%s(%s)` represents the PlanNode's id and the type of the `TExecNodePhase` class.For example, the following code: "| 02:UNION| | pass-through-operands: 15,17| | mem-estimate=0B mem-reservation=0B thread-reservation=0| | tuple-ids=13 row-size=68B cardinality=0| | in pipelines: 15(GETNEXT), 17(GETNEXT), 19(GETNEXT), 21(GETNEXT)" indicates that the current node is the execution node for the merge operation, with node ID 02; the estimated memory is 0B, the minimum reserved memory is 0B, the reserved thread count is 0, and tuple-ids=13 indicates the data processed or output by the current operation (such as merge), whose structure is defined by the tuple with ID 13, the average size of each row is 68B, the cardinality is 0, and the pipeline processing associated "PlanNode15(GETNEXT) is 17(GETNEXT), 19(GETNEXT)". "21(GETNEXT)" indicates that this UNION node needs to merge results from four different data sources, which are four independent pipelines: 15, 17, 19, and 21.

[0085] (11) The "%s:SCAN HDFS [%s, %s]| partition predicates: %s|HDFS partitions=%d / %d files=%d size=%s| predicates: %s| runtime filters: %s| stored statistics:| table: rows=%s size=%s| partitions: %d / %d rows=%s| columns: %s| extrapolated-rows=%s max-scan-range-rows=%s| parquet statisticspredicates: %s| parquet dictionary predicates: %s| mem-estimate=%s mem-reservation=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)" in PlanNode indicates that this execution node is the execution node that scans HDFS files. "%s:" indicates nodeId[%s, %s] This indicates the table name and the data type of the scanned partitions. The partition type here is generally RANDOM. "HDFSpartitions=%d / %d files=%d size=%s" indicates the file information in HDFS being scanned. "%d / %d" is the total number of partitions scanned. "files=%d" indicates the number of files scanned. "size=%s" indicates the size of the scanned files."predicates:%s" indicates the pushdown condition; "runtime filters:%s" indicates runtime filtering, commonly a bloom filter; "storedstatistics" indicates statistical information, which may be unavailable; "extrapolated-rows=%s" indicates the estimated number of rows to scan, generally unavailable; "max-scan-range-rows=%s" indicates the maximum number of rows to scan; "parquetstatistics predicates:%s" is unknown and also related to the pushdown condition; "parquet dictionary predicates:%s" is unknown and also related to the pushdown condition; "mem-reservation=%s" indicates the minimum reserved memory value; "spill-buffer=%s" indicates the estimated spill buffer size; "thread-reservation=%d" indicates the number of reserved threads; "tuple-ids=%d" indicates that ids are materialized by the tree rooted at this node; "row-size=%s" indicates the average size per row; "cardinality=%s" indicates the cardinality, note that the unit is incremented by 1000. pipelines: %s(%s), %s(%s) indicates the PlanNode associated with the pipeline processing. Here, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0086] (12) The "%s:ANALYTIC| functions: %s| partition by: %s| orderby: %s| window: %s| predicates: %s| mem-estimate=%s mem-reservation=%s spill-buffer=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| inpipelines: %s(%s), %s(%s)" in PlanNode indicates that this execution node is the execution node for merging. The first parameter "%s:" represents the nodeId; "functions: %s" represents the functions used; "partition by: %s" represents the partition information; "order by: %s" represents the sorting information; "window: %s" represents the window information; "predicates: "%s" represents predicate information, "mem-reservation=%s" represents the minimum reserved memory value, "spill-buffer=%s" represents the estimated overflow buffer size, and "thread-reservation=%d" represents the number of reserved threads. "tuple-ids=%d" indicates that the ids are materialized by the tree rooted at this node. "row-size=%s" indicates the average size per row. "cardinality=%s" indicates the cardinality, note that the unit is incremented in 1000. "in pipelines: %s(%s), %s(%s)" indicates the PlanNode associated with pipeline processing. Among them, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0087] (13) In PlanNode, “%s:SELECT| predicates: %s| mem-estimate=%s mem-reservation=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)” indicates that the execution node is the one performing the SELECT operation. “%s:” indicates nodeId. “predicates: %s” indicates predicate information. “mem-reservation=%s” indicates the minimum reserved memory value. “spill-buffer=%s” indicates the estimated overflow buffer size. “thread-reservation=%d” indicates the number of reserved threads. “tuple-ids=%d” indicates that ids are materialized by the tree rooted at this node. “row-size=%s” indicates the average size of each row. “cardinality=%s” indicates the cardinality, note that the unit is incremented by 1000. “inpipelines: %s(%s), "%s(%s)" indicates the PlanNode associated with the pipeline processing. Here, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0088] (14) In PlanNode, “%s:%s [LIMIT=%s OFFSET=%s]| order by: %s|materialized: %s| mem-estimate=%s mem-reservation=%s spill-buffer=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)” indicates that the execution node is the sorting execution node. “%s:%s” means that the nodeId and SORT / TOP-N / PARTIAL SORT[LIMIT=%s OFFSET=%s] will be displayed if limit and offset exist. “order” means that the nodeId and SORT / TOP-N / PARTIAL SORT[LIMIT=%s OFFSET=%s] will be displayed if limit and offset exist. "by:%s" indicates sorting information; "mem-reservation=%s" indicates the minimum reserved memory value; "spill-buffer=%s" indicates the estimated overflow buffer size; "thread-reservation=%d" indicates the number of reserved threads; "tuple-ids=%d" indicates that the ids are materialized by the tree rooted at this node; "row-size=%s" indicates the average size per row; "cardinality=%s" indicates the cardinality, note that the unit is incremented in 1000. "in pipelines: %s(%s), %s(%s) " indicates the PlanNode associated with pipeline processing. Here, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0089] (15) In PlanNode, “%s:%s [%s]| offset: %s| order by: %s| limit: %s|mem-estimate=%s mem-reservation=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)” indicates that the execution node is the sorting execution node. “%s:%s” represents nodeId and EXCHANGE / MERGING-EXCHANGE; “[%s]” represents BROADCAST or SUFFLE, and SUFFLE will require DataPartition information. “offset: %s” represents the offset in the SQL. “order by: %s” represents the sorting information. “limit: %s” represents the limit in the SQL. "mem-reservation=%s" represents the minimum reserved memory value, "spill-buffer=%s" represents the estimated overflow buffer size, and "thread-reservation=%d" represents the number of reserved threads. "tuple-ids=%d" indicates that the ids are materialized by the treerooted at this node. "row-size=%s" represents the average size per row. "cardinality=%s" represents the cardinality, noting that the unit is incremented in 1000. "in pipelines: %s(%s), %s(%s)" indicates the PlanNode associated with the pipeline processing. Here, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0090] (16) In PlanNode, “%s:NESTED LOOP JOIN [%s, %s]| join table id: %s|join predicates: %s| predicates: %s| mem-estimate=%s mem-reservation=%sthread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines:%s(%s), %s(%s)” indicates that the execution node is the execution node performing a nested loop join. %s: represents nodeId. [%s, %s] represents the JOIN type and DistributeMode type. The JOIN type is referenced in JoinOperator, and the DistributeMode type is BROADCAST and PARTITIONED. join table id: %s is not used. “joinpredicates: %s” represents the predicate information of the JOIN. “predicates: %s” represents the predicate information. "mem-reservation=%s" indicates the minimum reserved memory value, "spill-buffer=%s" indicates the estimated overflow buffer size, and "thread-reservation=%d" indicates the number of reserved threads. "tuple-ids=%d" indicates that the ids are materialized by the tree rooted at this node. "row-size=%s" indicates the average size per row. "cardinality=%s" indicates the cardinality, noting that the unit is incremented in 1000. "in pipelines: %s(%s), %s(%s)" indicates the PlanNode associated with the pipeline processing. Here, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0091] (17) In PlanNode, “%s:HASH JOIN [%s, %s]| hash-table-id=%s| hashpredicates: %s| fk / pk conjuncts: %s| other join predicates: %s| otherpredicates: %s| runtime filters: %s| mem-estimate=%s mem-reservation=%sspill-buffer=%s thread-reservation=%d| tuple-ids=%d row-size=%s cardinality=%s| in pipelines: %s(%s), %s(%s)” indicates that the execution node is the execution node performing a hash join. “%s:” represents the nodeId; “[%s, %s]” represents the JOIN type and DistributeMode type. For the JOIN type, refer to JoinOperator. The DistributeMode type is BROADCAST and PARTITIONED. The join table id: %s is not used. "hash predicates: %s" indicates predicate information for HASH JOIN. "fk / pk conjuncts: %s" indicates primary key information. "other join predicates: %s" indicates other AND JOIN predicate information. "otherpredicates: %s" indicates primary key information. "runtime filters: %s" indicates filtering information. "mem-reservation=%s" indicates the minimum reserved memory value, "spill-buffer=%s" indicates the estimated overflow buffer size, and "thread-reservation=%d" indicates the number of reserved threads. "tuple-ids=%d" indicates that ids are materialized by the tree rooted at this node. "row-size=%s" indicates the average size per row. "cardinality=%s" indicates the cardinality, noting that the unit is incremented in 1000. "in pipelines: %s(%s), %s(%s)" indicates the PlanNode associated with the pipeline processing. Here, "%s(%s)" represents the PlanNode's id and the type of the TExecNodePhase class.

[0092] The parser parses the header information and multiple plan fragments in the example execution plan text according to the above parsing rules, and obtains the parsing result corresponding to the execution plan text. Then, the parser assembles the parsing result corresponding to each plan fragment according to the data flow between the plan fragments to obtain the syntax parse tree corresponding to the execution plan text.

[0093] Figure 3 This illustration shows a schematic diagram of an explain text parsing method provided in an embodiment of this application. For example... Figure 3 As shown, the explain text includes header information and multiple plan fragments, including Fragment1, Fragment2, Fragment3, ..., Fragment6. After the explain text is input into the first parser, the first parser performs header information extraction and plan fragment (Fragment) information extraction based on parsing rules. Header information extraction extracts information such as memory and threads from the header information. For each plan fragment (Fragment), plan fragment (Fragment) information extraction is performed, specifically including steps such as type differentiation, information extraction, and branch addition. The type differentiation step identifies the various information types in the plan fragment, such as ROOT, DataSink, and PlanNode. The information extraction step extracts the parsing information corresponding to each parsing item according to the parsing rules for each information type. The branch addition step adds the parsing information corresponding to each parsing item in each plan fragment to the leaf nodes of the syntax parse tree. Each computation fragment corresponds to one leaf node, thus assembling the parsing information of each plan fragment into a syntax parse tree. For example... Figure 3 In the syntax parse tree shown, F01 represents the parsing information of Fragment1, F02 represents the parsing information of Fragment2, F03 represents the parsing information of Fragment3, F04 represents the parsing information of Fragment4, F05 represents the parsing information of Fragment5, and F06 represents the parsing information of Fragment6.

[0094] After the first parser generates the syntax parse tree corresponding to the execution plan text, it fills the parsing information of the plan fragments on each leaf node of the syntax parse tree into the Java entity class. Each plan fragment corresponds to a Java entity class. Then, according to the connection relationship of each leaf node on the syntax parse tree, the Java entity classes corresponding to each plan fragment are concatenated into a tree structure, which is the first parse tree mentioned above.

[0095] Since the execution plan text cannot be directly used to extract the first indicator, and a complex SQL statement often has dozens or hundreds of plan fragments, it is very difficult to accurately read the information of a certain fragment. In this embodiment of the application, the execution plan text is parsed to generate a first parse tree, and the indicator value of the required first indicator can be found by following the first parse tree, which helps to accurately extract the indicator value.

[0096] Step 205: The parsing device extracts the index value of at least one first index from the first parsing tree.

[0097] At least one of the aforementioned first indicators includes at least one of the following A1 to A8: A1, number of partitions scanned in a single table; The number of partitions scanned in a single table is the total number of partitions scanned in a single data table.

[0098] A2, Number of files scanned in a single table; The number of scanned files per table is the total number of scanned files for a single data table.

[0099] A3, size of a single table scan file; The size of a single table scan file is the size of a single data table scan file.

[0100] A4, total scanned file size; The total scan file size is the sum of the sizes of all scan files corresponding to the first SQL statement.

[0101] A5, Number of single table scans; The number of times a single table is scanned refers to the number of times a single data table is scanned.

[0102] A6, Broadcast file size; A7, Large table JOIN small table; A8, estimated memory usage per node.

[0103] Step 206: The parsing device sends the index value of at least one first indicator to the interactive device. The index value of at least one first indicator is used to determine the quality score corresponding to the first SQL statement.

[0104] Correspondingly, the interactive device receives the index value of at least one first index returned by the parsing device, and then executes step 207.

[0105] Step 207: The interactive device determines the quality score corresponding to the first SQL statement based on the index value of at least one first indicator.

[0106] There are several possible implementations of step 207 above.

[0107] In one possible implementation, the interactive device determines a deduction value corresponding to at least one first indicator based on the indicator value of at least one first indicator and at least one first scoring rule; the first scoring rule includes the deduction value corresponding to when the indicator value of the corresponding first indicator meets a threshold condition; the interactive device determines the quality score corresponding to the first SQL statement based on the deduction values ​​corresponding to at least one first indicator. The at least one first indicator corresponds one-to-one with the at least one first scoring rule.

[0108] Wherein, at least one of the above-mentioned first scoring rules includes at least one of the following B1 to B8: B1 indicates that too many partitions have been scanned in a single table.

[0109] The first scoring rule B1 corresponds to the first indicator A1. The interactive device can determine the deduction value corresponding to the first indicator A1 based on the indicator value of the first indicator A1 and the first scoring rule B1.

[0110] In some embodiments, the first scoring rule B1 includes a deduction value corresponding to the number of single-table scan partitions when the value of the number of single-table scan partitions meets the first threshold condition, which is a preset deduction value corresponding to the first threshold condition, wherein the first threshold condition is one of a plurality of threshold conditions set in the first scoring rule B1. Taking the first scoring rule B1 (too many partitions scanned in a single table) as an example, the threshold conditions set include threshold condition 11, threshold condition 12, and threshold condition 13. Threshold condition 11 includes a single table scanned partition number greater than 100 and less than or equal to 200, with a preset deduction value of 10. Threshold condition 12 includes a single table scanned partition number greater than 200 and less than or equal to 300, with a preset deduction value of 20. Threshold condition 13 includes a single table scanned partition number greater than 300, with a preset deduction value of 30. For example, if the number of scanned partitions in the data table ods.cpu is 221, which meets threshold condition 12, then the deduction value for the first indicator A1 (number of scanned partitions in a single table) of the data table ods.cpu is 20. Similarly, if the number of scanned partitions in the data table ods.p is 201, which meets threshold condition 11, then the deduction value for the first indicator A1 (number of scanned partitions in a single table) of the data table ods.p is 10.

[0111] In some embodiments, the first scoring rule B1 (too many partitions scanned in a single table) includes a deduction value corresponding to the number of partitions scanned in a single table when the value of the number of partitions scanned in a single table meets a first threshold condition. This deduction value is the product of the default deduction value corresponding to the first threshold condition and the weight corresponding to the first threshold condition. The first threshold condition is one of multiple threshold conditions set in the first scoring rule B1. Taking the multiple threshold conditions set in the first scoring rule B1 (number of partitions scanned in a single table) as threshold condition 11, threshold condition 12, and threshold condition 13, this is illustrated here with an example where the default deduction value corresponding to each threshold condition is 100. It should be understood that different default deduction values ​​can also be set for different threshold conditions. Threshold condition 11 includes a single table scan partition number greater than 100 and less than or equal to 200, with a weight of 0.1. Threshold condition 12 includes a single table scan partition number greater than 200 and less than or equal to 300, with a weight of 0.3. Threshold condition 13 includes a single table scan partition number greater than 300, with a weight of 0.5. For example, the scan partition number of the data table ods.cpu is 221, which satisfies threshold condition 12. Therefore, the data table ods.cpu... The deduction value for the first indicator A1 (number of scanned partitions in a single table) of pu is the product of the default deduction value of 100 corresponding to threshold condition 2 and the weight of 0.3 corresponding to threshold condition 2, which is a deduction value of 30. For example, the number of scanned partitions in the data table ods.p is 170, which satisfies threshold condition 11. Therefore, the deduction value for the first indicator A1 (number of scanned partitions in a single table) of the data table ods.p is the product of the default deduction value of 100 corresponding to threshold condition 11 and the weight of 0.1 corresponding to threshold condition 11, which is a deduction value of 10.

[0112] It should be understood that the specific values ​​of the preset deduction values, the specific values ​​of the weights, and the threshold ranges in the threshold conditions in the embodiments of this application are all exemplary values. This application does not limit the specific values ​​of these parameters, nor does it limit the specific number of threshold conditions corresponding to each first scoring rule and each second scoring rule. These will not be repeated below.

[0113] B2 indicates that too many files have been scanned in a single table.

[0114] Among them, the first scoring rule B2 corresponds to the first indicator A2. The interactive device can determine the deduction value corresponding to the first indicator A2 based on the indicator value of the first indicator A2 and the first scoring rule B2.

[0115] In some embodiments, the first scoring rule B2 includes a deduction value corresponding to the number of single-table scanned files when the value of the number of single-table scanned files meets the second threshold condition, which is a preset deduction value corresponding to the second threshold condition, wherein the second threshold condition is one of a plurality of threshold conditions set in the first scoring rule B2. Taking the first scoring rule B2 (too many files scanned in a single table) as an example, the threshold conditions set include threshold condition 21 and threshold condition 22. Threshold condition 21 is that the number of files scanned in a single table is greater than 500 and less than or equal to 1000, and the preset deduction value for threshold condition 21 is 10. Threshold condition 22 is that the number of files scanned in a single table is greater than 1000, and the preset deduction value for threshold condition 22 is 20. For example, if the number of scanned files in the data table ods.cpu is 759, this value meets threshold condition 21. Therefore, the deduction value for the first indicator A2 (number of files scanned in a single table) of the data table ods.cpu is 10. As another example, if the number of scanned files in the data table ods.p is 1840, this value meets threshold condition 22. Therefore, the deduction value for the first indicator A2 (number of files scanned in a single table) of the data table ods.p is 20.

[0116] In some embodiments, the first scoring rule B2 (number of files scanned in a single table) includes a deduction value corresponding to the number of files scanned in a single table when the value of the number of files scanned in a single table meets the second threshold condition. This deduction value is the product of the default deduction value corresponding to the second threshold condition and the weight corresponding to the second threshold condition. The second threshold condition is one of multiple threshold conditions set in the first scoring rule B2. Taking the multiple threshold conditions set in the first scoring rule B2 (number of files scanned in a single table) as threshold condition 21 and threshold condition 22, this is illustrated here with an example where the default deduction value corresponding to each threshold condition is 100. It should be understood that different default deduction values ​​can also be set for different threshold conditions. Threshold condition 21 is that the number of scanned files in a single table is greater than 500 and less than or equal to 1000, with a weight of 0.1. Threshold condition 22 is that the number of scanned files in a single table is greater than 1000 and less than or equal to 300, with a weight of 0.2. For example, if the number of scanned files in the data table ods.cpu is 221, which satisfies threshold condition 22, then the deduction value for the first indicator A2 (number of scanned files in a single table) of the data table ods.cpu is the product of the default deduction value of 100 corresponding to threshold condition 22 and the weight of 0.2 corresponding to threshold condition 22, which is a deduction value of 20. As another example, if the number of scanned files in the data table ods.p is 201, which satisfies threshold condition 21, then the deduction value for the first indicator A2 (number of scanned files in a single table) of the data table ods.p is the product of the default deduction value of 100 corresponding to threshold condition 21 and the weight of 0.1 corresponding to threshold condition 21, which is a deduction value of 10.

[0117] B3 indicates a single table scan of an excessively large file.

[0118] Among them, the first scoring rule B3 corresponds to the first indicator A3. The interactive device can determine the deduction value corresponding to the first indicator A3 based on the indicator value of the first indicator A3 and the first scoring rule B3.

[0119] In some embodiments, the first scoring rule B3 includes a deduction value corresponding to the single-table scan file size when the numerical value meets the third threshold condition. The third threshold condition is one of multiple threshold conditions set in the first scoring rule B3. For example, the multiple threshold conditions set in the first scoring rule B3 (files with excessively large single-table scans) include threshold condition 31 and threshold condition 32. Threshold condition 31 is a single-table scan file size greater than 10G and less than or equal to 20G, with a preset deduction value of 10. Threshold condition 32 is a single-table scan file size greater than 20G, with a preset deduction value of 20. For example, if the third threshold condition is threshold condition 31, then the deduction value corresponding to the first indicator A3 is 10.

[0120] In other embodiments, the first scoring rule B3 includes a deduction value corresponding to the single-table scan file size when the value satisfies the third threshold condition. This deduction value is the product of the default deduction value corresponding to the third threshold condition and the weight corresponding to the third threshold condition. The third threshold condition is one of multiple threshold conditions set in the first scoring rule B3. For example, the multiple threshold conditions set in the first scoring rule B3 (files scanned too large in a single table) include threshold condition 31 and threshold condition 32. The default deduction value for each threshold condition is 100. It should be understood that different default deduction values ​​can also be set for different threshold conditions. The specific content of threshold conditions 31 and 32 is described in the foregoing related descriptions. The weight corresponding to threshold condition 31 is 0.1, and the weight corresponding to threshold condition 32 is 0.3. For example, if the third threshold condition is threshold condition 32, then the deduction value corresponding to the first indicator A3 is 30.

[0121] B4 indicates a file that is too large for multi-table scanning.

[0122] Among them, the first scoring rule B4 corresponds to the first indicator A4. The interactive device can determine the deduction value corresponding to the first indicator A4 based on the indicator value of the first indicator A4 and the first scoring rule B4.

[0123] In some embodiments, the first scoring rule B4 includes a deduction value corresponding to the total scanned file size when the total scanned file size meets a fourth threshold condition. The fourth threshold condition is one of multiple threshold conditions set in the first scoring rule B4. For example, the multiple threshold conditions set in the first scoring rule B4 (files with excessively large multi-table scans) include threshold condition 41 and threshold condition 42. Threshold condition 41 is a total scanned file size greater than 50G and less than or equal to 100G, with a preset deduction value of 10. Threshold condition 42 is a total scanned file size greater than 100G, with a preset deduction value of 20. For example, if the fourth threshold condition is threshold condition 42, then the deduction value corresponding to the first indicator A4 is 20.

[0124] In other embodiments, the first scoring rule B4 includes a deduction value corresponding to the total scanned file size when the total scanned file size satisfies the fourth threshold condition. This deduction value is the product of the default deduction value corresponding to the fourth threshold condition and the weight corresponding to the fourth threshold condition. The fourth threshold condition is one of multiple threshold conditions set in the first scoring rule B4. For example, the multiple threshold conditions set in the first scoring rule B4 (files scanned too large in multiple tables) include threshold condition 41 and threshold condition 42. The default deduction value for each threshold condition is 100. It should be understood that different default deduction values ​​can also be set for different threshold conditions. The specific content of threshold conditions 41 and 42 is described in the foregoing related descriptions. The weight corresponding to threshold condition 41 is 0.1, and the weight corresponding to threshold condition 42 is 0.2. For example, if the fourth threshold condition is threshold condition 41, then the deduction value corresponding to the first indicator A4 is 10.

[0125] B5 indicates that too many single-table scans have been performed.

[0126] Among them, the first scoring rule B5 corresponds to the first indicator A5. The interactive device can determine the deduction value corresponding to the first indicator A5 based on the indicator value of the first indicator A5 and the first scoring rule B5.

[0127] In some embodiments, the first scoring rule B5 includes a deduction value corresponding to the number of single table scans when the value of the number of single table scans meets a fifth threshold condition. The fifth threshold condition is one of multiple threshold conditions set in the first scoring rule B5. For example, the multiple threshold conditions set in the first scoring rule B5 (excessive number of single table scans) include threshold condition 51 and threshold condition 52. Threshold condition 51 is a single table scan count greater than 100 and less than or equal to 200, with a preset deduction value of 15. Threshold condition 52 is a single table scan count greater than 200, with a preset deduction value of 30. For example, if the fifth threshold condition is threshold condition 52, then the deduction value corresponding to the first indicator A5 is 30.

[0128] In other embodiments, the first scoring rule B5 includes a deduction value for the number of single-table scans when the value satisfies the fifth threshold condition. This deduction value is the product of the default deduction value corresponding to the fifth threshold condition and the weight corresponding to the fifth threshold condition. The fifth threshold condition is one of multiple threshold conditions set in the first scoring rule B5. For example, the multiple threshold conditions set in the first scoring rule B5 (excessive number of single-table scans) include threshold condition 51 and threshold condition 52. Each threshold condition has a default deduction value of 100. It should be understood that different threshold conditions can also correspond to different default deduction values. The specific content of threshold conditions 51 and 52 is described above. Threshold condition 51 has a weight of 15, and threshold condition 52 has a weight of 30. For example, if the fifth threshold condition is threshold condition 51, then the deduction value corresponding to the first indicator A5 is 15.

[0129] B6, Broadcasting a file that is too large.

[0130] Among them, the first scoring rule B6 corresponds to the first indicator A6. The interactive device can determine the deduction value corresponding to the first indicator A6 based on the indicator value of the first indicator A6 and the first scoring rule B6.

[0131] In some embodiments, the first scoring rule B6 includes a deduction value corresponding to the broadcast file size when the numerical value of the broadcast file size meets the sixth threshold condition. The sixth threshold condition is one of multiple threshold conditions set in the first scoring rule B6. For example, the multiple threshold conditions set in the first scoring rule B6 (broadcast file too large) include threshold condition 61 and threshold condition 62. Threshold condition 61 is a broadcast file size greater than 10G and less than or equal to 50G, with a preset deduction value of 10. Threshold condition 62 is a broadcast file size greater than 50G, with a preset deduction value of 20. For example, if the sixth threshold condition is threshold condition 61, then the deduction value corresponding to the first indicator A6 is 10.

[0132] In other embodiments, the first scoring rule B6 includes a deduction value corresponding to the broadcast file size when the numerical value of the broadcast file size meets the sixth threshold condition. This deduction value is the product of the default deduction value corresponding to the sixth threshold condition and the weight corresponding to the sixth threshold condition. The sixth threshold condition is one of multiple threshold conditions set in the first scoring rule B6. For example, the multiple threshold conditions set in the first scoring rule B6 (broadcasting excessively large files) include threshold condition 61 and threshold condition 62. The specific contents of threshold conditions 61 and 62 are described in the foregoing related descriptions. The weight corresponding to threshold condition 61 is 0.1, and the weight corresponding to threshold condition 62 is 0.2. For example, if the sixth threshold condition is threshold condition 62, then the deduction value corresponding to the first indicator A6 is 20.

[0133] B7, Large table JOIN small table.

[0134] Among them, the first scoring rule B7 corresponds to the first indicator A7. The interactive device can determine the deduction value corresponding to the first indicator A7 based on the indicator value of the first indicator A7 and the first scoring rule B7.

[0135] In some embodiments, when the first scoring rule B7 includes a first data table joining a second data table, the deduction value corresponding to the larger table joining the smaller table when the size of the first data table and the size of the second data table meet the seventh threshold condition is a preset deduction value corresponding to the seventh threshold condition. The seventh threshold condition is one of multiple threshold conditions set in the first scoring rule B7. For example, the multiple threshold conditions set in the first scoring rule B7 (large table joining small table) include threshold condition 71, threshold condition 72, and threshold condition 73. Threshold condition 71 is when the second data table is larger than 10G and its size is 50 to 100 times the size of the first data table, with a preset deduction value of 10. Threshold condition 72 is when the second data table is larger than 10G and its size is 100 to 500 times the size of the first table, with a preset deduction value of 20. Threshold condition 73 is when the second data table is larger than 10G and its size is more than 500 times the size of the first table, with a preset deduction value of 30. For example, if the seventh threshold condition is threshold condition 73, then the deduction value corresponding to the first indicator A7 is 30.

[0136] In other embodiments, when the first scoring rule B7 includes a first data table joining a second data table, the deduction value for a larger table joining a smaller table when the size of the first data table and the size of the second data table satisfy the seventh threshold condition is the product of the default deduction value corresponding to the seventh threshold condition and the weight corresponding to the seventh threshold condition. The seventh threshold condition is one of multiple threshold conditions set in the first scoring rule B7. For example, the multiple threshold conditions set in the first scoring rule B7 (large table joining small table) include threshold condition 71, threshold condition 72, and threshold condition 73. The default deduction value corresponding to each threshold condition is 100. It should be understood that different default deduction values ​​can also be set for different threshold conditions. The specific content of threshold conditions 71 and 72 is described in the foregoing related descriptions. The weight corresponding to threshold condition 71 is 0.1, the weight corresponding to threshold condition 72 is 0.2, and the weight corresponding to threshold condition 73 is 0.3. For example, if the seventh threshold condition is threshold condition 72, then the deduction value corresponding to the first indicator A7 is 20.

[0137] B8, excessive memory estimation for a single node.

[0138] Among them, the first scoring rule B8 corresponds to the first indicator A8. The interactive device can determine the deduction value corresponding to the first indicator A8 based on the indicator value of the first indicator A8 and the first scoring rule B8.

[0139] In some embodiments, the first scoring rule B8 includes a deduction value corresponding to the estimated memory usage of a single node when the value satisfies the eighth threshold condition. The eighth threshold condition is one of multiple threshold conditions set in the first scoring rule B8. For example, the multiple threshold conditions set in the first scoring rule B8 (excessive estimated memory usage by a single node) include threshold condition 81 and threshold condition 82. Threshold condition 81 is an estimated memory usage of a single node greater than 600M and less than 1000M, with a preset deduction value of 10. Threshold condition 82 is an estimated memory usage of a single node greater than 1000M, with a preset deduction value of 20. For example, if the eighth threshold condition is threshold condition 81, then the deduction value corresponding to the first indicator A78 is 10.

[0140] In other embodiments, the first scoring rule B8 includes a deduction value for the estimated memory usage of a single node when the value satisfies the eighth threshold condition. This deduction value is the product of the default deduction value corresponding to the eighth threshold condition and the weight corresponding to the eighth threshold condition. The eighth threshold condition is one of multiple threshold conditions set in the first scoring rule B8. For example, the multiple threshold conditions set in the first scoring rule B8 (excessive memory usage by a single node) include threshold condition 81 and threshold condition 82. The default deduction value for each threshold condition is 100. It should be understood that different default deduction values ​​can also be set for different threshold conditions. The specific content of threshold conditions 81 and 82 is described in the foregoing related descriptions. The weight corresponding to threshold condition 81 is 0.1, and the weight corresponding to threshold condition 82 is 0.3. For example, if the eighth threshold condition is threshold condition 82, then the deduction value corresponding to the first indicator A78 is 30.

[0141] In step 207 above, at least one first indicator may include one or more first indicators from A1 to A8. Taking at least one first indicator including first indicator A1 and first indicator A2 as an example, the first possible implementation method described above will be explained: The interactive device can determine the deduction values ​​corresponding to first indicator A1 and first indicator A2 respectively based on the indicator values ​​of first indicator A1 and first indicator A2, as well as first scoring rule B1 and first scoring rule B2. Then, based on the deduction values ​​corresponding to first indicator A1 and first indicator A2 respectively, the quality score corresponding to the first SQL statement is determined.

[0142] Taking an initial score of 100 as an example, the deduction value for the first indicator A1 (number of partitions scanned per table) of the data table ods.cpu is 20, the deduction value for the first indicator A2 (number of files scanned per table) of the data table ods.cpu is 10, the deduction value for the first indicator A1 (number of partitions scanned per table) of the data table ods.p is 10, and the deduction value for the first indicator A2 (number of files scanned per table) of the data table ods.p is 20, the total deduction value for at least one first indicator of each data table is 60, and the quality score corresponding to the first SQL statement is the difference between the initial score and the total deduction value, which is 40.

[0143] It should be understood that if at least one primary indicator includes more or fewer primary indicators, the relevant descriptions of at least one primary indicator including primary indicator A1 and primary indicator A2 can be found, and will not be repeated here.

[0144] In some embodiments of this application, after step 201 described above, the interactive device may further send a second instruction to the parsing device. The second instruction instructs the parsing device to parse the first SQL statement and return the value of at least one second indicator based on the parsing result. The interactive device receives the value of at least one second indicator returned by the parsing device. Correspondingly, the parsing device receives the second instruction, which instructs the parsing device to parse the first SQL statement and return the value of at least one second indicator based on the parsing result. In response to the second instruction, the parsing device inputs the first SQL statement into a second parser to generate a syntax parse tree corresponding to the first SQL statement. It then extracts the value of at least one second indicator from the syntax parse tree, and the value of the at least one second indicator is used to determine the quality score of the first SQL statement.

[0145] The following provides a specific example of how the second parser parses the first SQL statement after receiving it.

[0146] Before inputting the first SQL statement into the second parser, the first SQL statement can be preprocessed. The preprocessing methods include, but are not limited to, at least one of the following: (1) formatting the first SQL statement according to a preset format; (2) trimming the unnecessary information in the first SQL statement; for example, if the user needs to extract the table name in the first SQL statement, then the table name in the first SQL statement is the required content (or necessary information), and the other information is unnecessary information and can be trimmed; (3) removing the escape characters in the first SQL statement.

[0147] The preprocessed first SQL statement is input to the second parser. The second parser first determines the syntax type of the preprocessed first SQL statement. Common syntax types include, but are not limited to, INSERT, CREATE, DELETE, and SELECT statements. SELECT statements can exist independently, while INSERT and CREATE statements may contain SELECT statements. Then, based on the determined syntax type, the second parser selects the corresponding parsing rule and parses the preprocessed first SQL statement according to this rule. If the syntax of the preprocessed first SQL statement is correct, the second parser will split the preprocessed first SQL statement according to the parsing rule corresponding to the determined syntax type, generating a syntax parse tree for the first SQL statement. If a parsing error occurs, it indicates that the syntax of the input preprocessed first SQL statement is incorrect, and the second parser will return a prompt message to indicate that a first SQL statement with correct syntax should be input.

[0148] Then, the second parser extracts at least one second indicator value from the syntax parse tree corresponding to the first SQL statement.

[0149] At least one of the above-mentioned second indicators includes at least one of the following C1 to C3: C1, is the JOIN operation missing an ON condition? C2, the number of fields to query; C3, number of partitions scanned in a single table.

[0150] In a possible second implementation, the interactive device determines the quality score corresponding to the first SQL statement based on the index value of at least one first index and the index value of at least one second index.

[0151] In some embodiments, the interactive device determines the deduction value corresponding to at least one first indicator based on the indicator value of at least one first indicator and at least one first scoring rule; the at least one first indicator corresponds one-to-one with the at least one first scoring rule, and the first scoring rule includes the deduction value corresponding to the indicator value of the corresponding first indicator when the indicator value meets a threshold condition; the interactive device determines the deduction value corresponding to at least one second indicator based on the indicator value of at least one second indicator and at least one second scoring rule; the at least one second indicator corresponds one-to-one with the at least one second scoring rule, and the second scoring rule includes the deduction value corresponding to the indicator value of the corresponding second indicator when the indicator value meets a threshold condition; the interactive device determines the quality score corresponding to the first SQL statement based on the deduction values ​​corresponding to at least one first indicator and the deduction values ​​corresponding to at least one first indicator.

[0152] Wherein, at least one of the above-mentioned second scoring rules includes at least one of the following D1 to D3: D1, JOIN has no ON condition; The second scoring rule D1 corresponds to the second indicator C1. The interactive device can determine the deduction value corresponding to the second indicator C1 based on the indicator value of the second indicator C1 and the second scoring rule D1.

[0153] In some embodiments, the second scoring rule D1 includes a pre-defined deduction value for the deduction value corresponding to the absence of the ON condition in a JOIN operation.

[0154] In other embodiments, the second scoring rule D1 includes a deduction value for when the JOIN operation lacks the ON condition, which is the product of a preset deduction value and the weight corresponding to the lack of the ON condition in the JOIN operation.

[0155] D2, too many fields were queried; The second scoring rule D2 corresponds to the second indicator C2. The interactive device can determine the deduction value corresponding to the second indicator C2 based on the indicator value of the second indicator C2 and the second scoring rule D2.

[0156] In some embodiments, the second scoring rule D2 includes a deduction value corresponding to the number of query fields when the value of the number of query fields meets the ninth threshold condition. The ninth threshold condition is one of multiple threshold conditions set in the first scoring rule B9. For example, the multiple threshold conditions set in the second scoring rule D2 (too many query fields) include threshold condition 91 and threshold condition 92. Threshold condition 91 is when the number of query fields is greater than 600 and less than or equal to 1300, and the preset deduction value for threshold condition 91 is 10. Threshold condition 92 is when the number of query fields is greater than 1300, and the preset deduction value for threshold condition 92 is 20. For example, if the ninth threshold condition is threshold condition 91, then the deduction value for the second indicator C2 is 10.

[0157] In other embodiments, the second scoring rule D2 includes a deduction value corresponding to the number of query fields that satisfies the ninth threshold condition. This deduction value is the product of the default deduction value corresponding to the ninth threshold condition and the weight corresponding to the ninth threshold condition. The ninth threshold condition is one of multiple threshold conditions set in the first scoring rule D2. For example, the multiple threshold conditions set in the second scoring rule D2 (too many query fields) include threshold condition 91 and threshold condition 92. Each threshold condition has a default deduction value of 100, threshold condition 91 has a weight of 0.2, and threshold condition 92 has a weight of 0.5. For example, if the ninth threshold condition is threshold condition 91, then the deduction value corresponding to the second indicator C2 is 20.

[0158] D3 indicates that too many partitions have been scanned in a single table.

[0159] The second scoring rule D3 corresponds to the second indicator C3. The interactive device can determine the deduction value corresponding to the second indicator C3 based on the indicator value of the second indicator C3 and the second scoring rule D3.

[0160] In some embodiments, the second scoring rule D3 includes a deduction value corresponding to the number of partitions scanned in a single table when the value of the number of partitions scanned in a single table meets the tenth threshold condition. The tenth threshold condition is one of the multiple threshold conditions set in the first scoring rule D3. The multiple threshold conditions set in the first scoring rule D3 can be referred to the relevant description of the multiple threshold conditions set in the aforementioned first scoring rule B1 (too many partitions scanned in a single table), and will not be repeated here.

[0161] In some embodiments, the second scoring rule D3 includes the product of the default deduction value corresponding to the tenth threshold condition and the weight corresponding to the tenth threshold condition when the value of the number of partitions scanned by a single table meets the tenth threshold condition.

[0162] In the above-described possible implementation two, at least one second indicator may include one or more second indicators from C1 to C3. Taking at least one first indicator including first indicator A1 and first indicator A2, and at least one second indicator including second indicator C1 and second indicator C3 as an example, the above implementation two will be explained: The interactive device can determine the deduction values ​​corresponding to first indicator A1 and first indicator A2 respectively based on the indicator values ​​of first indicator A1 and first indicator A2, as well as first scoring rules B1 and first scoring rules B2. Furthermore, it can determine the deduction values ​​corresponding to second indicator C1 and second indicator C3 respectively based on the indicator values ​​of second indicator C1 and second indicator C3, as well as second scoring rules D1 and second scoring rules D3. Then, based on the deduction values ​​corresponding to first indicator A1, first indicator A2, second indicator C1, and second indicator C3, the quality score corresponding to the first SQL statement is determined.

[0163] It should be understood that in the second possible implementation, if at least one first indicator includes more or fewer first indicators, the relevant description of at least one first indicator including first indicator A1 and first indicator A2 can be referred to, and will not be repeated here; if at least one second indicator includes more or fewer second indicators, the relevant description of at least one second indicator including second indicator C1 and second indicator C3 can be referred to, and will not be repeated here.

[0164] In this embodiment, by performing deep structured parsing on the execution plan text, complex information (such as nested loops, resource estimation, etc.) in the execution plan can be extracted more accurately and completely. Compared with the use of regular expression matching in the prior art, this embodiment can reduce the probability of misjudgment and omission, which helps to significantly improve the accuracy of SQL statement quality assessment.

[0165] In some embodiments of this application, the interactive device further includes a scoring rule metadata table (assist_sql_score_rule_config) for storing basic information of the first scoring rules B1 to B8, and / or the basic information of the second scoring rules D1 to D3, wherein the basic information includes, for example, rule name, rule description, optimization suggestions, etc.

[0166] The interactive device may also include a scoring rule weight configuration table (assist_sql_score_rule_weight_config): storing the dynamic thresholds (e.g., the upper and lower limits of each threshold condition included in the first scoring rule, or the upper and lower limits of each threshold condition included in the second scoring rule) and weights (the weights corresponding to each first indicator and the weights corresponding to each second indicator) of each scoring rule under different clusters (cluster_name) and different engines (engine_type), as well as a flag indicating whether to trigger a warning (score_rule_warn), thereby enabling flexible configuration of scores.

[0167] In some embodiments, each of the first scoring rules and each of the second scoring rules is provided with a corresponding scoring rule level, as shown in Table 1 below:

[0168] In Table 1, a scoring rule level of 1 indicates the scoring rule used for the first layer of parsing, whereby the interactive device determines the deduction value corresponding to at least one second indicator based on the indicator value of at least one second indicator returned by the parsing device for parsing the first SQL statement, and selects each scoring rule item with a scoring rule level of 1. A scoring rule level of 2 indicates the scoring rule used for the second layer of parsing, whereby the interactive device determines the deduction value corresponding to at least one first indicator based on the indicator value of at least one first indicator returned by the parsing device for parsing the execution plan text corresponding to the first SQL statement, and selects each scoring rule item with a scoring rule level of 2.

[0169] The interactive device may also include a scoring result record table (beeswax_query_history_task_score_v2): which records in detail the scoring rule items triggered by each SQL execution, the hit threshold, the deduction value, the warning status, and other information, for historical tracing and front-end display, such as through the user terminal's display interface.

[0170] In some embodiments of this application, before the interactive device sends the first instruction to the parsing device, the interactive device can also determine that the quality score of the first SQL statement is greater than or equal to the first threshold based on the scores corresponding to at least one second indicator. That is, the interactive device first sends the second instruction to the parsing device, and then determines the quality score of the first SQL statement based on the indicator values ​​of at least one second indicator returned by the parsing device. The description of determining the quality score of the first SQL statement based on the indicator values ​​of at least one first indicator can be referred to above, and will not be repeated here. Then, the interactive device can determine whether the quality score of the first SQL statement is greater than or equal to the first threshold. If the quality score of the first SQL statement is determined to be less than the first threshold based on the indicator values ​​of at least one second indicator, the interactive device returns an alarm prompt to the user terminal to indicate that the quality score of the first SQL statement is lower than the first threshold; or, if the interactive device determines not to execute the first SQL statement, it can also return a prompt message indicating that the first SQL statement will not be executed to the user terminal; if the quality score of the first SQL statement is determined to be greater than or equal to the first threshold based on the indicator values ​​of at least one second indicator, the interactive device sends the first instruction to the parsing device to obtain the indicator values ​​of at least one first indicator, and then determines the quality score corresponding to the first SQL statement according to the above-described embodiment two.

[0171] It should be understood that the parsing device responding to the second instruction to parse the first SQL statement can be referred to as first-level parsing; the parsing device responding to the first instruction to parse the execution plan text corresponding to the first SQL statement can be referred to as second-level parsing. The first-level and second-level parsing can be performed by the same parsing device located in the server, or by different parsing devices located in the same or different servers. For example, the server may include both a first-level and a second-level parsing device; or, server one may include a first-level parsing device, and server two may include a second-level parsing device. The first-level and second-level parsing can be used separately or in combination. For example, combining the first quality score obtained after parsing the first SQL statement to be executed using first-level parsing, and the second quality score obtained after parsing the first SQL statement to be executed using second-level parsing, and then comprehensively evaluating the total quality score of the SQL statement based on the first and second quality scores.

[0172] In some embodiments of this application, the interactive device can display the quality score corresponding to the first SQL statement through a display interface, for example, displaying the quality score of the first SQL determined based on the indicator value of at least one first indicator; or, for example, displaying the quality score of the first SQL determined based on the indicator value of at least one second indicator; or, for example, displaying the quality score of the first SQL determined based on the indicator value of at least one first indicator and the indicator value of at least one second indicator; or, for example, displaying any combination of the above quality scores.

[0173] Figure 4 This illustration shows a display interface diagram provided in an embodiment of this application, such as... Figure 4 As shown, the display interface includes the quality score corresponding to the first SQL statement: 0.0, as... Figure 4 The dashed box 401 is shown in the figure.

[0174] In this embodiment, besides displaying the quality score corresponding to the first SQL statement on the display interface, a prompt or blocking operation can be directly displayed on the terminal device's display interface when a warning condition is triggered, along with a pop-up prompt. This blocking operation can prevent (or abandon) the execution of the first SQL statement. The blocking operation can be triggered when the quality score corresponding to the first SQL statement is too low or a serious warning is triggered. Optionally, the display interface may further include a first control and a second control, wherein the first control is used to trigger the instruction to execute the first SQL statement, and the second control is used to trigger the instruction to abandon the execution of the first SQL statement. For example, the first control... Figure 4 The control 402 shown in the figure, the second control, for example Figure 4 The application does not restrict the names of controls 402 and 403 as shown in the diagram. Thus, users can choose to continue executing the first SQL statement or abandon its execution using the first and second controls.

[0175] Optionally, the above display interface may also include a record of deductions, for example... Figure 4 The deduction item enclosed in the dashed box 404 shown in the figure is: the table ods.x did not use the partition field par_dt in the WHERE condition, the number of partitions scanned was 200, which exceeds the threshold of 100, and the deduction value corresponding to this deduction item is 10.0.

[0176] Optionally, the above display interface may also include a view control 405 for the optimization scheme corresponding to the deduction item. When the view control 405 is clicked, the optimization scheme corresponding to the deduction item is displayed on the display interface, such as... Figure 5In the other display interface diagram shown, the optimization scheme outlined by dashed box 406 includes the reason for setting the scoring rule corresponding to the deduction item (scanning too many partitions): In Impala, when a query does not use a partition field in the WHERE or ON condition, it will result in a full table scan or scan too many partitions, which will significantly increase I / O overhead and query latency, reduce cluster resource utilization efficiency, and affect the performance of other queries; the optimization scheme outlined by dashed box 406 also includes optimization suggestions, as follows (1) to (3): (1) Add partition filter conditions: Add a partition field filter condition in the WHERE clause to limit the query range.

[0177] (2) Optimize partition design: Adjust the partition strategy according to common query patterns and select appropriate partition granularity.

[0178] (3) Optimize JOIN queries: For multi-table JOIN queries, ensure that all tables involved in the JOIN utilize partition pruning to avoid performing a full table scan for each JOIN.

[0179] In this embodiment of the application, an evaluation report of the first SQL statement can be displayed on the display interface of the terminal device, which may include at least one of the following: the quality score corresponding to the first SQL statement, each deduction item, the optimization scheme corresponding to the deduction item, alarm prompts, alarm levels, a first control, and a second control.

[0180] In this embodiment of the application, the specific values ​​of each threshold involved are not limited, nor is the specific setting method of the threshold limited. For example, it can be set to a default value, or it can be dynamically set by the user according to actual needs.

[0181] Based on the same technological concept Figure 6 An example of an apparatus provided in this application is shown, which can perform the above-described... Figure 2 The operation steps performed by the interactive device or the operation steps performed by the parsing device in the method shown.

[0182] like Figure 6 As shown, the device 600 includes an interface unit 601 and a processing unit 602.

[0183] When the device 600 performs the above... Figure 2 The analytical device performs the following operational steps in the method shown: Interface unit 601 is used to receive a first instruction, the first instruction including an execution plan text, the execution plan text including multiple plan fragments, the plan fragments including execution planning information of the first SQL statement at each execution node; the first instruction is used to instruct the parsing device to parse the execution plan text and return the index value of at least one first index based on the parsing result of the execution plan text; Processing unit 602 is configured to respond to a first instruction by inputting the execution plan text into a first parser to generate a first parse tree. The first parse tree includes multiple first nodes that correspond one-to-one with multiple plan segments. Each first node stores the execution planning information included in the corresponding plan segment. The connection relationship between the multiple first nodes is determined according to the data flow direction between the multiple plan segments. At least one first indicator value is extracted from the first parse tree. The interface unit 601 is also configured to send the index value of at least one first index to the interactive device, wherein the index value of at least one first index is used to determine the quality score corresponding to the first SQL statement.

[0184] Optionally, at least one of the following is included as a first indicator: number of partitions scanned per table; number of files scanned per table; file size scanned per table; total file size scanned by the first SQL statement; number of table scans; broadcast file size; large table JOIN small table; estimated memory usage per node.

[0185] Optionally, the interface unit 601 is further configured to: receive a second instruction, the second instruction being configured to instruct the parsing device to parse the first SQL statement and return the index value of at least one second index based on the parsing result of the first SQL statement; the processing unit 602 is further configured to: in response to the second instruction, input the first SQL statement to the second parser to generate a syntax parse tree corresponding to the first SQL statement; extract the index value of at least one second index from the syntax parse tree, the index value of at least one second index being used to determine the quality score of the first SQL statement.

[0186] Optionally, the second metric may include at least one of the following: whether the JOIN operation lacks an ON condition; the number of fields queried; and the number of partitions scanned in a single table.

[0187] When the device 600 performs the above... Figure 2 The operation steps performed by the interactive device in the method shown are as follows: Interface unit 601 is configured to receive a first SQL statement submitted by a user; obtain the execution plan text corresponding to the first SQL statement; send a first instruction to the parsing device, the first instruction including the execution plan text, the first instruction being configured to instruct the parsing device to parse the execution plan text, and return the index value of at least one first index based on the parsing result of the execution plan text; and receive the index value of at least one first index returned by the parsing device. Processing unit 602 is used to determine the quality score corresponding to the first SQL statement based on the index value of at least one first index.

[0188] Optionally, at least one of the following is included as a first indicator: number of partitions scanned per table; number of files scanned per table; file size scanned per table; total file size scanned by the first SQL statement; number of table scans; broadcast file size; large table JOIN small table; estimated memory usage per node.

[0189] Optionally, the processing unit 602 is specifically configured to: determine the deduction value corresponding to at least one first indicator based on the indicator value of at least one first indicator and at least one first scoring rule; at least one first indicator corresponds one-to-one with at least one first scoring rule, the first scoring rule including the deduction value corresponding to the indicator value of the corresponding first indicator when the indicator value meets the threshold condition; and determine the quality score corresponding to the first SQL statement based on the deduction value corresponding to at least one first indicator.

[0190] Optionally, the interface unit 601 is further configured to: send a second instruction to the parsing device, the second instruction being configured to instruct the parsing device to parse the first SQL statement and return the index value of at least one second index based on the parsing result of the first SQL statement; receive the index value of at least one second index returned by the parsing device; and the processing unit 602 is specifically configured to: determine the quality score corresponding to the first SQL statement based on the index value of at least one first index and the index value of at least one second index.

[0191] Optionally, the second metric may include at least one of the following: whether the JOIN operation lacks an ON condition; the number of fields queried; and the number of partitions scanned in a single table.

[0192] Optionally, the processing unit 602 is specifically configured to: determine the deduction value corresponding to at least one first indicator based on the indicator value of at least one first indicator and at least one first scoring rule; at least one first indicator corresponds one-to-one with at least one first scoring rule, the first scoring rule including the deduction value corresponding to the indicator value of the corresponding first indicator when it meets a threshold condition; determine the deduction value corresponding to at least one second indicator based on the indicator value of at least one second indicator and at least one second scoring rule; at least one second indicator corresponds one-to-one with at least one second scoring rule, the second scoring rule including the deduction value corresponding to the indicator value of the corresponding second indicator when it meets a threshold condition; and determine the quality score corresponding to the first SQL statement based on the deduction values ​​corresponding to at least one first indicator and the deduction values ​​corresponding to at least one first indicator.

[0193] Optionally, the processing unit 602 is specifically used to: determine that the quality score corresponding to the first SQL statement is greater than or equal to the first threshold based on the deduction value corresponding to at least one second indicator.

[0194] Optionally, the processing unit 602 is further configured to: display the quality score, first control, and second control corresponding to the first SQL statement through a display interface, wherein the first control is used to trigger an instruction to execute the first SQL statement, and the second control is used to trigger an instruction to abandon the execution of the first SQL statement.

[0195] It should be noted that the above Figure 6 The structure of the analytical device shown is merely an example and does not constitute a limitation on the device. The device may include more or fewer modules than those shown, and this application does not impose any restrictions on this.

[0196] Based on the same technical concept, embodiments of this application provide a computing device.

[0197] like Figure 7 As shown, the computing device 700 may include a processor 710, a memory 720, a network interface 730, and a communication bus 740. The communication bus 740 is used to enable communication between these components.

[0198] The processor 710 is the control center of the computing device 700. It connects various parts of the computing device 700 through various interfaces and lines. By running or executing software programs and / or modules stored in the memory 720, and by calling data stored in the memory 720, it performs various functions and handles data of the computing device 700. Optionally, the processor 710 may include one or more processing units.

[0199] The memory 720 can be used to store software programs and modules. The processor 710 performs data processing by running the software programs and modules stored in the memory 720. The memory 720 may mainly include a program storage area and a data storage area. The program storage area may store the operating system, at least one application program required for a function, etc.; the data storage area may store SQL statements, the parsed results of the SQL statements, etc. In addition, the memory 720 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other volatile solid-state storage device.

[0200] Optionally, the network interface 730 may include a standard wired interface or a wireless interface (such as a Wi-Fi interface). In this embodiment, the network interface 730 is mainly used to connect to a backend server or terminal device and to communicate with the backend server or terminal device.

[0201] The processor 710 can be used to call a program stored in the memory 720 for performing the method described in any of the foregoing embodiments, and to perform the operations in the method described in any of the foregoing embodiments.

[0202] It should be noted that the above Figure 7 The structure shown is merely an example and does not constitute a limitation on the device. It may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0203] Based on the same technical concept, embodiments of this application provide a computer-readable storage medium storing computer-executable instructions for causing a computer to perform the methods in any of the foregoing embodiments.

[0204] Based on the same technical concept, this application provides a computer program product, which includes a computer program or instructions. When the computer program or instructions are executed by a computer, the method in any of the foregoing embodiments is implemented.

[0205] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0206] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0207] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0208] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0209] Although preferred embodiments of this application have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments as well as all changes and modifications falling within the scope of this application.

[0210] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

Claims

1. A method for evaluating the quality of SQL statements, characterized in that, Applied to a parsing apparatus, the method includes: The system receives a first instruction, which includes an execution plan text. The execution plan text includes multiple plan fragments, each of which includes execution planning information for the first SQL statement at each execution node. The first instruction instructs a parsing device to parse the execution plan text and return the value of at least one first indicator based on the parsing result of the execution plan text. In response to the first instruction, the execution plan text is input to the first parser to generate a first parse tree. The first parse tree includes multiple first nodes that correspond one-to-one with the multiple plan segments. Each first node stores the execution planning information included in the corresponding plan segment. The connection relationship between the multiple first nodes is determined according to the data flow direction between the multiple plan segments. Extract the index value of the at least one first index from the first parse tree; The indicator value of the at least one first indicator is sent to the interactive device, and the indicator value of the at least one first indicator is used to determine the quality score corresponding to the first SQL statement.

2. The method as described in claim 1, characterized in that, The method further includes: The second instruction is received, which instructs the parsing device to parse the first SQL statement and return the index value of at least one second index based on the parsing result of the first SQL statement. In response to the second instruction, the first SQL statement is input to the second parser to generate a syntax parse tree corresponding to the first SQL statement; The index value of the at least one second index is extracted from the syntax parse tree, and the index value of the at least one second index is used to determine the quality score of the first SQL statement.

3. A method for evaluating the quality of SQL statements, characterized in that, Applied to an interactive device, the method includes: Receive the first SQL statement submitted by the user; Retrieve the execution plan text corresponding to the first SQL statement; Send a first instruction to the parsing device, the first instruction including the execution plan text, the first instruction being used to instruct the parsing device to parse the execution plan text, and to return the index value of at least one first index based on the parsing result of the execution plan text; Receive the index value of the at least one first index returned by the parsing device; Based on the index value of the at least one first indicator, the quality score corresponding to the first SQL statement is determined.

4. The method as described in claim 3, characterized in that, Determining the quality score corresponding to the first SQL statement based on the index value of at least one first indicator includes: Based on the index value of the at least one first indicator and the at least one first scoring rule, the deduction value corresponding to the at least one first indicator is determined respectively; the at least one first indicator corresponds one-to-one with the at least one first scoring rule, and the first scoring rule includes the deduction value corresponding to the index value of the corresponding first indicator when the index value meets the threshold condition; The quality score corresponding to the first SQL statement is determined based on the deduction value corresponding to at least one first indicator.

5. The method as described in claim 3, characterized in that, After receiving the first SQL statement submitted by the user, the process also includes: Send a second instruction to the parsing device, the second instruction being used to instruct the parsing device to parse the first SQL statement and return the index value of at least one second index based on the parsing result of the first SQL statement; Receive the index value of the at least one second index returned by the parsing device; Determining the quality score corresponding to the first SQL statement based on the index value of the at least one first index includes: The quality score corresponding to the first SQL statement is determined based on the index values ​​of the at least one first indicator and the at least one second indicator.

6. The method as described in claim 5, characterized in that, Determining the quality score corresponding to the first SQL statement based on the index values ​​of the at least one first indicator and the at least one second indicator includes: Based on the index value of the at least one first indicator and the at least one first scoring rule, the deduction value corresponding to the at least one first indicator is determined respectively; the at least one first indicator corresponds one-to-one with the at least one first scoring rule, and the first scoring rule includes the deduction value corresponding to the index value of the corresponding first indicator when the index value meets the threshold condition; Based on the index value of the at least one second indicator and the at least one second scoring rule, the deduction value corresponding to the at least one second indicator is determined respectively; the at least one second indicator corresponds one-to-one with the at least one second scoring rule, and the second scoring rule includes the deduction value corresponding to the index value of the corresponding second indicator when the index value meets the threshold condition; The quality score corresponding to the first SQL statement is determined based on the deduction values ​​corresponding to the at least one first indicator and the deduction values ​​corresponding to the at least one first indicator.

7. The method according to any one of claims 1-6, characterized in that, The at least one first indicator includes at least one of the following: Number of partitions scanned in a single table; Number of files scanned per table; Single table scan file size; The total scanned file size of the first SQL statement; Number of single table scans; Broadcast file size; Large table joins small table; Estimated memory usage per node.

8. The method according to any one of claims 2, 5-6, characterized in that, The second indicator includes at least one of the following: Does the JOIN operation lack an ON condition? Number of fields to query; Number of partitions scanned in a single table.

9. A computing device, characterized in that, include: Memory, used to store program instructions; A processor is configured to invoke program instructions stored in the memory and execute the method according to any one of claims 1 to 8.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions for causing a computer to perform the method according to any one of claims 1 to 8.

Images