SQL (Structured Query Language) statement processing method and device, electronic equipment and storage medium

Abstract

The embodiment of the invention provides an SQL statement processing method and device, electronic equipment and a computer readable storage medium, and relates to the field of databases. The method comprises the steps of obtaining at least one SQL statement to be processed; based on at least one of an SPJG rule, a public expression recognition rule and a time column recognition rule, extracting a reference SQL clause with at least one of business semantics, reusability and time from each SQL statement to be processed; inputting each reference SQL clause and a preset cue word into a large language model to obtain at least one first SQL clause; the preset cue word is used for indicating to screen out a first SQL clause from each reference SQL clause by combining natural language description information of each reference SQL based on at least one of multiplexing potential, calculation overhead, semantic rationality and incremental support. According to the embodiment of the invention, the SQL clauses which are more suitable for being packaged into the virtual table can be selected.

Description

Technical Field

[0001] The present application relates to the field of database technology. Specifically, the present application relates to a method, device, electronic device, computer-readable storage medium, and computer program product for processing SQL statements. Background Art

[0002] SQL statements are a standard language for operating databases.

[0003] A virtual table (often referred to as a view) is a logical abstraction mechanism in SQL that defines and dynamically generates result sets through SQL statements. The core relationship is that SQL statements are the implementation carrier of virtual tables, while virtual tables are the encapsulated results of SQL statements.

[0004] Virtual tables are purely logical structures, and each query requires dynamic SQL execution. Materialized views, on the other hand, precompute query results and store them as physical tables, allowing direct access to stored data, significantly improving performance.

[0005] In related technologies, which SQL statements are encapsulated as virtual tables and whether virtual tables are suitable for being stored as physical tables will directly affect the performance of the database system. Related technologies either sacrifice performance (depending on the Figure 1 Direct real-time computing) or excessive consumption of storage space (long-term retention of materialized views). Summary of the Invention

[0006] The embodiments of the present application provide a method, apparatus, electronic device, computer-readable storage medium, and computer program product for processing SQL statements, which can solve the above-mentioned problems of the prior art. The technical solution is as follows: According to one aspect of an embodiment of the present application, a method for processing an SQL statement is provided, the method comprising: Get at least one SQL statement to be processed; Extracting a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of an SPJG rule, a common expression recognition rule, and a time column recognition rule; Inputting each reference SQL clause and a preset prompt word into a large language model to obtain at least one first SQL clause output by the large language model, where the first SQL clause is a SQL clause recommended to be encapsulated as a virtual table; The preset prompt word is used to indicate that the first SQL clause is selected from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability, combined with natural language description information of each reference SQL.

[0007] According to another aspect of an embodiment of the present application, a device for processing an SQL statement is provided, the device comprising: A statement acquisition module, used to acquire at least one SQL statement to be processed; A clause screening module, configured to extract a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of an SPJG rule, a common expression recognition rule, and a time column recognition rule; a clause recommendation module, configured to input each reference SQL clause and a preset prompt word into a large language model, and obtain at least one first SQL clause output by the large language model, wherein the first SQL clause is a recommended SQL clause encapsulated as a virtual table; The preset prompt word is used to indicate that the first SQL clause is selected from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability, combined with natural language description information of each reference SQL.

[0008] In some optional embodiments, the apparatus further comprises: A clause display module, configured to display the at least one first SQL clause; a virtual table encapsulation module, configured to, in response to a selection operation on a target SQL clause in the at least one first SQL clause, encapsulate each selected target SQL clause into a virtual table; A virtual table screening module is used to count the usage frequency of each virtual table and select virtual tables with usage frequencies exceeding a preset threshold as candidate virtual tables; a materialization priority determination module, configured to determine, for each candidate virtual table, the materialization priority of the candidate virtual table according to the data size, query response delay, and usage frequency of the candidate virtual table; The virtual table recommendation module is used to recommend candidate virtual tables with materialization priorities higher than a preset threshold as target virtual tables for generating materialized views.

[0009] In some optional embodiments, the apparatus further comprises: An incremental column determination module is configured to, in response to an operation of selecting a candidate incremental column from a target virtual table, determine, through a virtual table model and an abstract syntax tree (AST) grammatical analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table; the virtual table model is configured to represent the kinship relationship between virtual tables and between a virtual table and a data source table; A finer-grained determination module is configured to determine whether the candidate incremental column supports incremental updates and display a control for setting the update granularity if the at least one target incremental column is distributed in the same target data source table or the target incremental column is not a result of an aggregation operator calculation; A condition generation module, configured to generate a judgment condition for incremental data according to a set update granularity in response to a setting operation on the control; The view generation module is used to periodically scan the target data source table according to the judgment conditions and generate a new materialized view according to the scanned incremental data.

[0010] In some optional embodiments, the apparatus further comprises: an incremental column determination module, configured to, in response to an operation of selecting a candidate incremental column from a target virtual table, determine, through a virtual table model and AST syntax analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in the generated materialized view; A finer granularity determination module, configured to display a control for setting an update granularity, and in response to a setting operation on the control, generate a judgment condition for incremental data according to the set update granularity; a condition generation module, configured to generate a judgment condition for incremental data based on the generated materialized view if the currently set update granularity is the same as the update granularity corresponding to the generated materialized view; The view generation module is used to periodically scan the generated materialized view according to the judgment condition and generate a new materialized view according to the scanned incremental data.

[0011] In some optional embodiments, the clause recommendation module is specifically configured to: Inputting each reference SQL clause and the first prompt word into the large language model, and obtaining natural language description information corresponding to each reference SQL clause output by the large language model; Inputting each reference SQL clause and the second prompt word into the large language model to obtain the first SQL clause output by the large language model; Wherein, the preset prompt words include the first prompt word and the second prompt word; The first prompt word is used to instruct to convert the reference SQL clause into natural language description information, and the first prompt word includes the table structure, field annotation, business scenario, and natural language description information of the sample SQL clause; The second hint word is used to instruct to filter out the first SQL clause from various reference SQL clauses, and the second hint word includes a filtering rule related to at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability.

[0012] In some optional embodiments, the time column identification rule includes: Convert the SQL statement into a corresponding abstract syntax tree, and identify whether the SCAN node has a time column in the abstract syntax tree; If it exists, mark the time column; If not, trace back from the bottom up to at least one of the JOIN nodes, WHERE nodes, ORDER BY nodes, and GROUP BY nodes to identify a derivable time expression; If it is determined through expression lineage analysis that the field in the time expression is directly or indirectly derived from the time field, then the time expression is determined to be a time column; The SQL clause containing the time column is used as the reference SQL clause.

[0013] In some optional embodiments, the SPJG rules include: Convert SQL statements into corresponding abstract syntax trees; Using each SELECT and PROJECT node of the abstract syntax tree as a traversal starting point, the abstract syntax tree is traversed from the first traversal starting point; starting from the traversal starting point of the current traversal, the traversed JOIN or GROUP BY node is used as the traversal end point of the current traversal, the syntax subtree between the current traversal starting point and the current traversal end point is intercepted as a candidate SQL clause, and the traversal is continued from the next variable starting point until all traversal starting points are traversed; For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics, the candidate SQL clause is used as a reference SQL clause.

[0014] In some optional embodiments, the common expression recognition rules include: If the SQL statement is a common expression, it is determined that each SQL clause in the SQL statement is reusable and serves as a reference SQL clause.

[0015] In some optional embodiments, the SQL statement to be processed is obtained in the following manner: Collect SQL logs, which include the content, execution time, and execution count of multiple historical SQL statements; Determining the recommendation priorities of the plurality of historical SQL statements according to the SQL log, wherein the recommendation priority of each historical SQL statement is proportional to the complexity of the content, the execution time, and the number of executions of the historical SQL statement; A preset number of top-ranked historical SQL statements are selected as the SQL statements to be processed.

[0016] According to another aspect of an embodiment of the present application, an electronic device is provided. The electronic device includes a memory, a processor, and a computer program stored in the memory, and the processor executes the computer program to implement the above method.

[0017] According to another aspect of the embodiments of the present application, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the above method is implemented.

[0018] According to one aspect of an embodiment of the present application, a computer program product is provided, including a computer program, which implements the above method when executed by a processor.

[0019] The beneficial effect of the technical solution provided by the embodiment of the present application is that by obtaining at least one SQL statement to be processed, since the SQL statement does not necessarily meet the direct materialization processing requirements, the system does not directly process the complete SQL statement. Instead of performing materialized recommendations, a SQL clause decomposition strategy is adopted to extract reference SQL clauses that meet at least one of the following criteria: business semantics, reusability, and timeliness. The large language model's natural language generation and analysis capabilities are further leveraged to ensure that the recommended virtual tables are highly versatile and conform to business semantics, reducing duplicate definitions. Each reference SQL clause and a preset prompt word are input into the large language model. The preset prompt word is used to indicate at least one of the following criteria: reuse potential, computational overhead, semantic rationality, and incremental supportability. Combined with the natural language description information of each reference SQL, a first SQL clause is screened from each reference SQL clause to obtain at least one first SQL clause output by the large language model. The first SQL clause is the SQL clause recommended for encapsulation as a virtual table. Since the first SQL clause is the SQL clause with the best reuse potential, computational overhead, semantic rationality, and incremental supportability, selected from each reference SQL clause, this lays the foundation for avoiding blind materialization of low-frequency views and is more conducive to controlling storage space consumption. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following briefly introduces the drawings required for describing the embodiments of the present application.

[0021] Figure 1 A schematic diagram of the system architecture for implementing SQL statement processing provided in an embodiment of the present application; Figure 2 A flowchart of a method for processing SQL statements provided in an embodiment of the present application; Figure 3 A schematic diagram of a process for collecting SQL statements provided in an embodiment of the present application; Figure 4A schematic diagram of a process for separating reference SQL clauses from SQL statements provided in an embodiment of the present application; Figure 5 A flowchart of a method for processing SQL statements provided in another embodiment of the present application; Figure 6 A flowchart of a method for processing SQL statements provided in yet another embodiment of the present application; Figure 7 A flowchart of a method for processing SQL statements provided in yet another embodiment of the present application; Figure 8 A schematic diagram of the structure of a device for processing SQL statements provided in an embodiment of the present application; Figure 9 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0022] The following describes the embodiments of the present application in conjunction with the accompanying drawings. It should be understood that the embodiments described below in conjunction with the accompanying drawings are exemplary descriptions for explaining the technical solutions of the embodiments of the present application and do not constitute a limitation on the technical solutions of the embodiments of the present application.

[0023] Those skilled in the art will understand that, unless otherwise stated, the singular forms "a", "an" and "the" used herein may also include plural forms. It should be further understood that the terms "including" and "comprising" used in the embodiments of the present application mean that the corresponding features can be implemented as the presented features, information, data, steps, operations, elements and / or components, but do not exclude implementation as other features, information, data, steps, operations, elements, components and / or combinations thereof supported by the present technical field. It should be understood that when we say that an element is "connected" or "coupled" to another element, the element can be directly connected or coupled to the other element, or it can refer to that the element and the other element establish a connection relationship through an intermediate element. In addition, the "connection" or "coupling" used here can include wireless connection or wireless coupling. The term "and / or" used here indicates at least one of the items defined by the term, for example, "A and / or B" can be implemented as "A", or as "B", or as "A and B".

[0024] In order to make the objectives, technical solutions and advantages of this application clearer, the implementation methods of this application will be further described in detail below with reference to the accompanying drawings.

[0025] During database design and use, problems such as poor SQL reuse, complex query logic, high maintenance costs, and coupling between application and database structures often arise. To address these issues, relevant technologies have introduced virtual tables, which are a transformation and abstraction between underlying tables and actual business application tables.

[0026] Underlying data refers to the underlying table data actually stored in the database, typically raw data generated and inserted directly by the business system. This data serves as the core data source for upper-level structures such as virtual tables and materialized views, directly impacting the performance and correctness of upper-level queries.

[0027] However, traditional virtual tables have limitations. They only simplify logic but don't actually improve query efficiency. Therefore, materialized views were introduced to address the bottleneck of virtual tables. However, materialized views also have limitations. Excessive or unnecessary materialization consumes storage space. SQL must also meet processing refresh policies, expiration times, and dependencies, requiring additional design and management by users.

[0028] Therefore, intelligent materialization based on virtual tables combines the optimization of both virtual tables and materialization. It is used in XEngine's data business foundation solution. It uses virtual tables for rapid early modeling and logical abstraction, facilitating schema trial runs. During operation, it intelligently recommends which virtual tables to materialize based on access patterns and performance bottlenecks, and dynamically balances performance and storage costs globally. Through full and incremental materialization strategies, it achieves better system performance.

[0029] Current mainstream database systems generally lack the ability to directly convert ordinary views into materialized views. This leads to problems such as duplicate view and materialized view definitions, delayed performance tuning, data consistency risks, uneven resource utilization, and inefficient model evolution during development and operation. These problems make it difficult to meet the higher performance, flexibility, and intelligence requirements of modern data systems.

[0030] If a business defines many common views in the early stage, when query performance needs to be improved later, they must be manually rewritten as materialized views, which increases development costs and maintenance difficulties. In addition, because virtual tables and views are inconvenient to switch between, developers cannot flexibly adjust according to system load or storage resources, and system performance optimization will become delayed. Usually, it will eventually evolve into either sacrificing performance (view Figure 1 Direct real-time computing) or excessive consumption of storage space (long-term retention of materialized views).

[0031] In order to improve the access efficiency and abstract encapsulation capabilities of the underlying data, the embodiments of the present application introduce virtual tables and intelligent materialization mechanisms to achieve efficient modeling and access optimization of the underlying data.

[0032] The SQL statement processing method, device, electronic device, computer-readable storage medium, and computer program product provided in this application are intended to solve the above technical problems in the prior art.

[0033] The following describes several exemplary embodiments to illustrate the technical solutions of the embodiments of the present application and the technical effects produced by the technical solutions of the present application. It should be noted that the following embodiments can refer to, draw on, or combine with each other, and the same terms, similar features, and similar implementation steps in different embodiments will not be repeated.

[0034] Figure 1 A schematic diagram of the system architecture for implementing a method for processing SQL statements provided in an embodiment of the present application is shown. As shown in the figure, the system includes a terminal device 110 and a server 120.

[0035] In the embodiments of the present application, terminal devices include but are not limited to mobile phones, tablet computers, laptops, desktop computers, vehicle-mounted terminals and other devices; a client related to the processing of SQL statements can be installed on the terminal device, and the client can be software (such as a browser, database management software, etc.), or a web page, mini program, etc. The server is a background server corresponding to the software or web page, mini program, etc., or a server specifically used for processing SQL statements. This application does not make specific limitations.

[0036] The server can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDNs), as well as big data and artificial intelligence platforms.

[0037] It should be noted that the SQL statement processing method in the embodiments of the present application can be executed by an electronic device, which can be a server or a terminal device. That is, the method can be executed independently by the server or terminal device, or jointly by the server and the terminal device. For example, when executed jointly by a server and a terminal device, the terminal device sends each SQL statement to the server. Based on the obtained SQL statements, the server extracts reference SQL clauses that have at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of the SPJG rule, the common expression recognition rule, and the time column recognition rule. The server then inputs each reference SQL clause and a preset prompt word into a large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause is a SQL clause recommended for encapsulation as a virtual table.

[0038] In an optional implementation, the terminal device and the server may communicate via a communication network.

[0039] In an optional implementation, the communication network is a wired network or a wireless network.

[0040] It should be noted that Figure 1 The examples shown are just for illustration. In fact, the number of terminal devices and servers is not limited and is not specifically limited in the embodiments of this application.

[0041] In an embodiment of the present application, when there are multiple servers, the multiple servers can be combined into a blockchain, and the servers are nodes on the blockchain; such as the SQL statement processing method disclosed in the embodiment of the present application, the SQL statements involved can be saved on the blockchain, etc.

[0042] Furthermore, the embodiments of this application can be applied to various scenarios, including not only SQL statement processing scenarios, but also cloud technology, artificial intelligence, smart transportation, assisted driving, and other scenarios. For example, in OLAP scenarios, real-time analysis platforms, and large-scale join and aggregation systems, the system query performance can be significantly improved, with obvious results.

[0043] The present application provides a method for processing SQL statements, such as Figure 2 As shown, the method includes: S101: Obtain at least one SQL statement to be processed.

[0044] In some embodiments, historical SQL logs of users may be collected regularly or irregularly, and SQL statements involved in the historical SQL logs may be used as SQL statements to be processed.

[0045] In some embodiments, an interface may be provided to the user, the interface being used to display multiple SQL statements for selection. In response to selecting at least one SQL statement, the selected SQL statement is used as the SQL statement to be processed. It is understood that the SQL statements for selection displayed in the interface may be from the user's historical SQL log.

[0046] It should be understood that the SQL log is a tool for recording the execution status of SQL statements in the database system. The SQL log in the embodiment of the present application can be at least one of a query log, a slow query log, an error log, and a binary log.

[0047] In some embodiments, the SQL statement to be processed is obtained in the following manner: Collect SQL logs, which include the content, execution time, and execution count of multiple historical SQL statements; Determining the recommendation priorities of the multiple historical SQL statements according to the SQL log; A preset number of top-ranked historical SQL statements are selected as the SQL statements to be processed.

[0048] See Figure 3 , which exemplarily shows a flow chart of collecting SQL statements in an embodiment of the present application. As shown in the figure, the user initiates a query to the computing engine, and the computing engine provides the query to the user through the query process to complete the query. The SQL statements involved in the query are accumulated through background tasks and written into the statistical table in batches. The SQL statements in the statistical table are processed through persistence to obtain statistical information.

[0049] The SQL logs collected in the embodiment of the present application include SQL statement content, execution time, execution times, etc. By counting the execution time, the average delay of historical SQL statements can be determined, and by analyzing the execution times, the access frequency of SQL statements can be determined.

[0050] This application can further determine the recommendation priority of each historical SQL statement based on the content, execution time, and execution count of each historical SQL statement, wherein the recommendation priority of each historical SQL statement is proportional to the complexity of the content, execution time, and execution count of the historical SQL statement. A preset number of top-ranked historical SQL statements are further used as the SQL statements to be processed, and SQL statements that are more suitable for encapsulation into virtual tables or even for constructing materialized views are screened out from the source.

[0051] In some embodiments, historical SQL statements can be sorted and optimized according to the following scoring function, and the SQL statements ranked top-N by scores are selected as candidates to proceed to the next step of subquery decomposition:

[0052] in, This is an adjustable parameter that can be flexibly adjusted based on the user's desired business scenario. SQL complexity can be comprehensively evaluated based on multiple indicators such as the number of nesting levels, the number of SQL clauses contained in the SQL statement, and the number of JOINs. It is understandable that the greater the number of nesting levels, the number of SQL clauses, and the number of JOINs, the higher the SQL complexity.

[0053] S102 : Based on at least one of the SPJG rule, the common expression identification rule, and the time column identification rule, extract a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed.

[0054] In this embodiment, considering that historical SQL statements may not necessarily meet the requirements for direct materialization, the system does not directly recommend materialization of complete SQL statements. Instead, it uses a subquery (i.e., SQL clause) decomposition strategy to extract SQL clauses with reusability. The decomposition process uses heuristic rules.

[0055] The heuristic rules of the embodiment of the present application include three categories: SPJG rules, common subexpressions (CSE) identification rules, and time column identification rules.

[0056] The SPJG rule is an efficient algorithm for rewriting materialized view queries. It is primarily based on matching selection, projection, join, and group-by operators. Its core is to rewrite queries into equivalent materialized view forms through structured matching to improve query performance.

[0057] Common expressions refer to the same or equivalent subqueries, expressions, or calculation logic that appear multiple times in an SQL statement. Common expression identification rules are rules used to identify common expressions. This application can use syntax analysis, abstract syntax tree analysis, query optimizer analysis, etc. to identify common expressions. Taking query optimizer analysis as an example, the query optimizer of the database (such as MySQL, Oracle, PostgreSQL) usually automatically identifies common expressions and optimizes them (such as subquery expansion and common subexpression elimination).

[0058] Time columns are typically used to record data creation, update, and event occurrence times, and are key to analyzing data trends and filtering data ranges. Time column identification rules are used to identify SQL statements that contain time columns.

[0059] See Figure 4 , which exemplarily shows a flow chart of splitting reference SQL clauses from SQL statements in an embodiment of the present application. As shown in the figure, the background task can obtain statistical SQL statements from the statistical table on a regular basis (for example, weekly). First, based on the preset blacklist or other filtering rules, the SQL statements that are not recommended to be encapsulated as virtual tables are filtered out. Then, the various SQL clauses in the SQL statements are screened through SPJG rules, common expression recognition rules, and time column recognition rules, and the obtained reference SQL clauses are stored in the preliminary screening recommendation table.

[0060] The embodiment of the present application uses the above heuristic rules to extract reference SQL clauses with at least one of business semantics, high reuse potential, and timeliness from complex SQL statements.

[0061] S103: Input each reference SQL clause and preset prompt word into a large language model to obtain at least one first SQL clause output by the large language model, where the first SQL clause is a SQL clause recommended to be encapsulated as a virtual table.

[0062] This application further introduces a SQL clause semantic evaluation mechanism based on a large language model to intelligently sort and filter reference SQL clauses. In order to enable the large language model to have the ability to filter the first SQL clause, this application requires inputting a preset prompt word while inputting the reference SQL clause into the large language model. The preset prompt word is used to indicate that the first SQL clause should be filtered out from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability, combined with the natural language description information of each reference SQL.

[0063] In other words, the preset prompt words in the embodiments of this application need to, on the one hand, instruct the large language model to convert each reference SQL clause into a natural language expression, and on the other hand, provide the large language model with screening rules. The screening rules are rules used to evaluate reuse potential, computational cost, semantic rationality, and incremental support. Thus, based on the preset prompt words, the large language model can generate a score based on the four dimensions of reuse potential, computational cost, semantic rationality, and incremental support, and select the first SQL clause that is most suitable for encapsulation into a virtual table, completing the screening of the reference SQL clauses.

[0064] The SQL statement processing method provided in the embodiment of the present application obtains at least one SQL statement to be processed. Since the SQL statement does not necessarily meet the direct materialization processing requirements, the system does not directly process the complete SQL statement. Instead of performing materialized recommendations, a SQL clause decomposition strategy is adopted to extract reference SQL clauses that meet at least one of the following criteria: business semantics, reusability, and timeliness. The large language model's natural language generation and analysis capabilities are further leveraged to ensure that the recommended virtual tables are highly versatile and conform to business semantics, reducing duplicate definitions. Each reference SQL clause and a preset prompt word are input into the large language model. The preset prompt word is used to indicate at least one of the following criteria: reuse potential, computational overhead, semantic rationality, and incremental supportability. Combined with the natural language description information of each reference SQL, a first SQL clause is screened from each reference SQL clause to obtain at least one first SQL clause output by the large language model. The first SQL clause is the SQL clause recommended for encapsulation as a virtual table. Since the first SQL clause is the SQL clause with the best reuse potential, computational overhead, semantic rationality, and incremental supportability, selected from each reference SQL clause, this lays the foundation for avoiding blind materialization of low-frequency views and is more conducive to controlling storage space consumption.

[0065] See Figure 5, which exemplarily shows a flow chart of a method for processing SQL statements provided by another embodiment of the present application, as shown in the figure, including: S201. Obtain at least one SQL statement to be processed; S202, extracting a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of the SPJG rule, the common expression recognition rule, and the time column recognition rule; S203: Input each reference SQL clause and preset prompt word into the large language model, and obtain and display at least one first SQL clause output by the large language model; S204: In response to a selection operation on a target SQL clause in the at least one first SQL clause, encapsulate each selected target SQL clause into a virtual table; S205: Count the usage frequencies of the virtual tables, and select virtual tables whose usage frequencies exceed a preset threshold as candidate virtual tables; S206. For each candidate virtual table, determine the materialization priority of the candidate virtual table according to the data size, query response delay, and usage frequency of the candidate virtual table; S207: The candidate virtual table with a materialization priority higher than a preset threshold is recommended as a target virtual table for generating a materialized view.

[0066] After obtaining the first SQL clause output by the large language model, the embodiment of the present application can display the first SQL clause through a preset interface. The user browses these first SQL clauses, selects the target SQL clause that needs to be encapsulated as a virtual table, and encapsulates each target SQL clause as a virtual table.

[0067] By monitoring the usage of each encapsulated virtual table, the present application can dynamically decide whether each virtual table needs to be materialized to balance performance benefits and storage costs. Specifically, the present application will collect statistics on the usage frequency of each virtual table, which can include at least one of query frequency, update frequency, and reference frequency.

[0068] In some embodiments, the present application can count the access frequency and call count of the virtual table in unit time. Virtual tables that are used infrequently (access frequency is lower than a set threshold) will not participate in the subsequent materialization evaluation process to avoid resource waste. The retained virtual tables are called candidate virtual tables.

[0069] In some embodiments, the present application determines the materialization priority of each candidate virtual table based on the data size, query response delay and usage frequency of the candidate virtual table. It can be calculated by the following formula:

[0070] α, β, and γ are weighting factors set by the system and are used to balance the impact of different factors on performance benefits. In some embodiments, the data scale can be represented by the number of data rows.

[0071] If the materialization priority of a virtual table is higher than the preset threshold (user-defined), the system will include it in the candidate materialized view set and record its materialization priority (Score), access statistics, data volume, and other information. The information will be saved in the target recommended table set for subsequent materialization or user review.

[0072] See Figure 6 , which exemplarily shows a flow chart of a method for processing SQL statements provided in yet another embodiment of the present application, as shown in the figure, including: S301: Display candidate virtual tables with materialization priorities higher than a preset threshold as target virtual tables recommended for generating materialized views; S302: In response to the operation of selecting a candidate incremental column from the target virtual table, determining, through a virtual table model and an Abstract Syntax Tree (AST) syntax analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table; S303: If at least one target incremental column is distributed in the same target data source table, or the target incremental column is not a result of an aggregation operator calculation, determine that the candidate incremental column supports incremental update, and display a control for setting the update granularity; S304: In response to the setting operation on the control, a judgment condition for generating incremental data according to the set update granularity; S305 : Scan the target data source table regularly according to the judgment condition, and generate a new materialized view according to the scanned incremental data.

[0073] In the above embodiment, when the user selects materialization recommendation or manually specifies the candidate incremental columns in the virtual table that need to be materialized, the system will automatically establish a bidirectional index relationship between the virtual table and the materialized view, and derive the materialization processing logic that can support incremental refresh, thereby improving data processing efficiency and reducing system load.

[0074] The virtual table model in the embodiment of the present application is used to represent the blood relationship between various virtual tables and between virtual tables and data source tables. For example, through the virtual table model, it can be determined that virtual table a originates from virtual table b, virtual table b originates from virtual table c, and can even be traced back to the data table. Therefore, the virtual table model can be used to realize the traceability deduction of virtual tables.

[0075] An Abstract Syntax Tree (AST) is a tree-like representation of the source code's syntax structure. It abstracts away the specific syntax details (such as semicolons, brackets, etc.) and retains only the core syntax structure. AST syntax analysis involves three aspects of analysis: SQL parsing: Parse the SQL statement that defines the virtual table into AST, and analyze the column definitions, expressions, function calls, etc.

[0076] Column mapping analysis: By traversing the AST, the source of the virtual table column is identified. If the virtual table column is directly mapped from the data source table column, the column will be directly referenced in the AST. If the virtual table column is generated by an expression or function, the corresponding expression or function call node will be included in the AST.

[0077] Target incremental column identification: Locate candidate incremental columns in the AST, trace their sources, and determine the target incremental columns in the corresponding data source table.

[0078] The embodiment of the present application can extract the column definitions and their sources in the virtual table through AST syntax analysis, and then for each virtual table column, check whether it is directly mapped from the data source table column. If it is an indirect mapping (such as generated by an expression or function), further analyze the AST node of the expression or function to determine the data source table column it depends on. Then, through the lineage relationship information in the virtual table model, verify whether the mapping relationship obtained by AST analysis is correct, ensure that the candidate incremental column has a corresponding target incremental column in the data source table, and record the mapping relationship between the virtual table column and the data source table column for subsequent incremental synchronization or data lineage analysis.

[0079] The following describes the process of determining the target increment column for this application using a target virtual table: Assume the following virtual table definition: SQL CREATE VIEW virtual_table AS SELECT user_id, user_name, age + 1 AS next_year_age FROM source_table; AST analysis: user_id and user_name are directly mapped from the columns of source_table.

[0080] next_year_age is generated by the expression age + 1, which depends on the age column of source_table.

[0081] Mapping relationship: virtual_table.user_id→source_table.user_id virtual_table.user_name→source_table.user_name virtual_table.next_year_age→source_table.age Incremental column identification: If next_year_age is a candidate increment column, its corresponding target increment column is source_table.age.

[0082] In some embodiments, the embodiments of the present application parse virtual table a through AST syntax, determine that the candidate incremental column aA in virtual table a is mapped to the bB column of the source table virtual table b, continue to parse virtual table b through AST syntax, determine that the acquired column bB comes from func(cC), and parse all the way to the data source table p, and then know that the column of aA is mapped to the column of the source table pP.

[0083] After determining the target incremental column, the embodiment of the present application further needs to judge whether the candidate incremental column supports incremental update based on the target incremental column. Specifically, the present application adopts a judgment method based on whether each target incremental column is distributed in the same data source table (target data source table) and whether the target incremental column is the result of aggregation operator calculation. If at least one target incremental column is distributed in the same target data source table, or the target incremental column is not the result of aggregation operator calculation, it is determined that the candidate incremental column supports incremental update.

[0084] For candidate incremental columns that support incremental updates, the embodiment of the present application will correspondingly display controls for setting the update granularity. The embodiment of the present application does not limit the unit of update granularity, for example, it can be minutes, hours, days, weeks, months, etc.

[0085] By responding to the setting operation of the control, generating the judgment condition of incremental data according to the set update granularity, and regularly scanning the target data source table according to the judgment condition, this application can adopt polling mode or CDC (ChangeData Capture) mode to scan incremental data. The polling mode is to scan the target source data table in turn at regular intervals. The CDC mode is to capture data changes in real time by connecting to change log sources such as Binlog and WAL, and generate new materialized views based on the scanned incremental data.

[0086] See Figure 7 , which exemplarily shows a flow chart of a method for processing SQL statements provided by another embodiment of the present application, as shown in the figure, including: S401: Display candidate virtual tables with materialization priorities higher than a preset threshold as target virtual tables recommended for generating materialized views; S402: In response to the operation of selecting a candidate incremental column from the target virtual table, determining, through the virtual table model and AST syntax analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in the generated materialized view; S403: Display a control for setting update granularity, and in response to a setting operation on the control, generate a judgment condition for incremental data according to the set update granularity; S404: If the currently set update granularity is the same as the update granularity corresponding to the generated materialized view, a judgment condition for generating incremental data is generated according to the generated materialized view; S405 : Scan the generated materialized view periodically according to the judgment condition, and generate a new materialized view according to the scanned incremental data.

[0087] In the above embodiment, the present application determines whether the candidate incremental column exists in the generated materialized view through virtual table model and AST syntax analysis. If the target incremental column is found to exist in the generated materialized view, a control for updating the granularity will also be displayed. In response to the setting operation of the control, since the judgment condition is a filtering condition for processing SQL for the new materialized view, the judgment condition of the generated materialized view cannot be used. Instead, it is necessary to generate a judgment condition for the incremental data based on the set update granularity.

[0088] The embodiment of the present application further scans the target data source table periodically based on the judgment conditions. The present application can use polling mode or CDC (Change Data Capture) mode to scan incremental data. The polling mode is to scan the target source data table in turn periodically. The CDC mode is to capture data changes in real time by connecting to change log sources such as Binlog and WAL, and generate new materialized views based on the scanned incremental data.

[0089] Based on the above embodiments, as an optional embodiment, each reference SQL clause and a preset prompt word are input into a large language model, and the first SQL clause output by the large language model is obtained and encapsulated as a virtual table, including: Inputting each reference SQL clause and the first prompt word into the large language model, and obtaining natural language description information corresponding to each reference SQL clause output by the large language model; Each reference SQL clause and the second prompt word are input into the large language model to obtain the first SQL clause output by the large language model.

[0090] The preset prompt words in the embodiment of the present application include two groups of prompt words, namely, first prompt words and second prompt words.

[0091] The first prompt word is used to instruct to convert the reference SQL clause into natural language description information, and the first prompt word includes the table structure, field comments, business scenario and natural language description information of the sample SQL clause.

[0092] In some embodiments, the first prompt word mainly includes the structure information of the table involved in the SQL, the annotation information of the field (that is, the business meaning or purpose of the field), and the business scenario description of the SQL query, etc., to enhance the context awareness of the large model. For example, a SQL statement to query regional sales: SELECT r.province, SUM(o.sales_amount) AS total_sales FROM sales_order o JOIN region_info r ON o.city_code = r.city_code WHERE o.order_date>= '2024-01-01' GROUP BY r.province; The first prompt includes the table structure and purpose of the sales_order and region_info tables (the sales_order table records the sales details of each order, etc.), the meaning of each field (such as sales_order.city_code represents the city code associated with the order), and the business scenario of this SQL statement, which is used to analyze the total sales of each provincial region since 2024. The query results are used to support the headquarters in formulating provincial sales strategies, etc.

[0093] The second hint word is used to instruct to filter out the first SQL clause from the reference SQL clauses, and the second hint word includes a filtering rule related to at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability.

[0094] In some embodiments, the second prompt word should include filtering criteria, such as overhead ratios, reuse ratios, and sufficient contextual semantics. For example, the current business scenario, the table structure of each table, the data volume, the SQL statement, and the currently separated SQL clause set should be considered. The big speech model can use the SQL clause structure (for example, filters can limit reuse) and SQL statements to determine versatility, data volume to determine overhead, and time columns to determine whether incremental support is supported. This allows the model to score each reference SQL clause and ultimately select the top SQL clause.

[0095] Based on the above embodiments, as an optional embodiment, the time column identification rule includes: Convert the SQL statement into a corresponding abstract syntax tree, and identify whether the SCAN node has a time column in the abstract syntax tree; If it exists, mark the time column; If not, trace back from the bottom up to at least one of the JOIN nodes, WHERE nodes, ORDER BY nodes, and GROUP BY nodes to identify a derivable time expression (such as calculation or filtering of a time field). If it is determined through expression lineage analysis that the field in the time expression is directly or indirectly derived from the time field, then the time expression is determined to be a time column; The SQL clause containing the time column is used as the reference SQL clause.

[0096] The embodiment of the present application first converts the SQL statement into an abstract syntax tree and identifies the SCAN node in the abstract syntax tree. The SCAN node of the embodiment of the present application may represent a full table scan operation on the database table in the AST, that is, reading all data in the table row by row to find records that meet the query conditions. The SCAN node usually corresponds to a query operation when there is no valid index in the SQL statement or the index is unavailable.

[0097] If a SCAN node has a time column, the time column will be marked, and the SQL clause corresponding to the SCAN node will be used as the reference SQL clause.

[0098] For a SCAN node that does not have a time column, the system will backtrack from the bottom up to at least one of the JOIN nodes, WHERE nodes, ORDER BY nodes, and GROUP BY nodes to identify a derivable time expression.

[0099] A JOIN node represents a table join operation in an SQL statement, used to associate data from multiple tables based on specified conditions. In a JOIN operation, a time expression typically appears in the ON clause to specify the join condition between tables. For example, the date field in ON t1.date = t2.date might be a time expression.

[0100] The WHERE node represents a filter condition in an SQL statement, used to select records that meet the specified criteria. A time expression in a WHERE node is typically used to filter records, for example, WHERE date>= '2023-01-01' AND date<'2023-02-01'. Time expressions may involve date functions such as CURRENT_DATE, DATE_ADD, and DATE_SUB.

[0101] The ORDER BY node represents a sorting operation in an SQL statement and is used to sort query results. The time expression in the ORDER BY node is used to sort query results, for example, ORDER BY date DESC. The time field is usually used to arrange records in chronological order during sorting.

[0102] The GROUP BY node represents a grouping operation in an SQL statement, used to group query results by a specified field. A time expression in a GROUP BY node groups records by a time field, for example, GROUP BY DATE(date). This time expression may involve date functions such as DATE(), MONTH(), and YEAR().

[0103] Through these optimization measures, the embodiments of the present application can significantly improve the execution efficiency of queries, especially in application scenarios that process large amounts of time data.

[0104] Based on the above embodiments, as an optional embodiment, the SPJG rule includes: Convert SQL statements into corresponding abstract syntax trees; Using each SELECT and PROJECT node of the abstract syntax tree as a traversal starting point, the abstract syntax tree is traversed from the first traversal starting point; starting from the traversal starting point of the current traversal, the traversed JOIN or GROUP BY node is used as the traversal end point of the current traversal, the syntax subtree between the current traversal starting point and the current traversal end point is intercepted as a candidate SQL clause, and the traversal is continued from the next variable starting point until all traversal starting points are traversed; For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics (for example, fields are parseable, logic is complete, etc.), the candidate SQL clause is used as a reference SQL clause.

[0105] The embodiments of the present application can extract SQL clauses that have business semantics and high reuse potential from complex SQL statements.

[0106] Based on the above embodiments, the common expression recognition rules include: If the SQL statement is a common expression, it is determined that each SQL clause in the SQL statement is reusable and serves as a reference SQL clause.

[0107] It should be noted that if the SQL statement uses the WITH ... AS syntax structure (i.e., a common expression CTE), since the CTE itself usually has semantic independence and reusability and is naturally suitable as the basis of a virtual table, the embodiment of the present application directly extracts the SQL clause defined in the structure as a reference SQL clause.

[0108] The technical effects of the embodiments of this application are as follows: 1. Significantly improve system query performance Through intelligent analysis of historical SQL access patterns, it identifies high-frequency, high-latency, and highly repetitive query logic and automatically recommends materialized views.

[0109] This allows complex SQL query results to be materialized in advance, avoiding repeated calculations for each query, significantly reducing query latency, and improving overall system throughput.

[0110] It is particularly suitable for OLAP scenarios, real-time analysis platforms, and large-scale join and aggregation systems, with significant effects.

[0111] 2. Reduce storage and operation and maintenance costs The system automatically performs dynamic evaluation based on the frequency of virtual table usage, data size, and refresh cost to avoid blind materialization of low-frequency views and control storage space consumption.

[0112] Supports incremental refresh logic deduction, reduces full refresh overhead, and lowers operation and maintenance scheduling costs.

[0113] Maintenance personnel do not need to manually manage the dependencies and refresh policies of virtual table materialization, reducing human configuration errors.

[0114] 3. Improve development and modeling efficiency In the early stage of product database modeling, virtual tables are automatically recommended by disassembling historical SQL structures, reducing the workload of developers in manually creating virtual tables.

[0115] Use SQL heuristic splitting + large model semantic understanding to ensure that the recommended virtual tables are highly versatile, consistent with business semantics, and reduce duplicate definitions.

[0116] Virtual tables and materialized views can be smoothly connected, supporting rapid trial operation of the business and on-demand materialization, lowering the threshold for model adjustment.

[0117] The embodiment of the present application provides a device for processing SQL statements, such as Figure 8 As shown, the SQL statement processing device may include: a statement acquisition module 801, a clause screening module 802 and a clause recommendation module 803, wherein: A statement acquisition module 801 is used to acquire at least one SQL statement to be processed; A clause screening module 802 is configured to extract a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of an SPJG rule, a common expression recognition rule, and a time column recognition rule; A clause recommendation module 803 is configured to input each reference SQL clause and a preset prompt word into a large language model to obtain at least one first SQL clause output by the large language model, where the first SQL clause is a recommended SQL clause encapsulated as a virtual table; The preset prompt word is used to indicate that the first SQL clause is selected from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability, combined with natural language description information of each reference SQL.

[0118] In some optional embodiments, the apparatus further comprises: A clause display module, configured to display the at least one first SQL clause; a virtual table encapsulation module, configured to, in response to a selection operation on a target SQL clause in the at least one first SQL clause, encapsulate each selected target SQL clause into a virtual table; A virtual table screening module is used to count the usage frequency of each virtual table and select virtual tables with usage frequencies exceeding a preset threshold as candidate virtual tables; a materialization priority determination module, configured to determine, for each candidate virtual table, the materialization priority of the candidate virtual table according to the data size, query response delay, and usage frequency of the candidate virtual table; The virtual table recommendation module is used to recommend candidate virtual tables with materialization priorities higher than a preset threshold as target virtual tables for generating materialized views.

[0119] In some optional embodiments, the apparatus further comprises: An incremental column determination module is configured to, in response to an operation of selecting a candidate incremental column from a target virtual table, determine, through a virtual table model and an abstract syntax tree (AST) grammatical analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table; the virtual table model is configured to represent the kinship relationship between virtual tables and between a virtual table and a data source table; A finer-grained determination module is configured to determine whether the candidate incremental column supports incremental updates and display a control for setting the update granularity if the at least one target incremental column is distributed in the same target data source table or the target incremental column is not a result of an aggregation operator calculation; A condition generation module, configured to generate a judgment condition for incremental data according to a set update granularity in response to a setting operation on the control; The view generation module is used to periodically scan the target data source table according to the judgment conditions and generate a new materialized view according to the scanned incremental data.

[0120] In some optional embodiments, the apparatus further comprises: an incremental column determination module, configured to, in response to an operation of selecting a candidate incremental column from a target virtual table, determine, through a virtual table model and AST syntax analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in the generated materialized view; A finer granularity determination module, configured to display a control for setting an update granularity, and in response to a setting operation on the control, generate a judgment condition for incremental data according to the set update granularity; a condition generation module, configured to generate a judgment condition for incremental data based on the generated materialized view if the currently set update granularity is the same as the update granularity corresponding to the generated materialized view; The view generation module is used to periodically scan the generated materialized view according to the judgment condition and generate a new materialized view according to the scanned incremental data.

[0121] In some optional embodiments, the clause recommendation module is specifically configured to: Inputting each reference SQL clause and the first prompt word into the large language model, and obtaining natural language description information corresponding to each reference SQL clause output by the large language model; Inputting each reference SQL clause and the second prompt word into the large language model to obtain the first SQL clause output by the large language model; Wherein, the preset prompt words include the first prompt word and the second prompt word; The first prompt word is used to instruct to convert the reference SQL clause into natural language description information, and the first prompt word includes the table structure, field annotation, business scenario, and natural language description information of the sample SQL clause; The second hint word is used to instruct to filter out the first SQL clause from various reference SQL clauses, and the second hint word includes a filtering rule related to at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability.

[0122] In some optional embodiments, the time column identification rule includes: Convert the SQL statement into a corresponding abstract syntax tree, and identify whether the SCAN node has a time column in the abstract syntax tree; If it exists, mark the time column; If not, trace back from the bottom up to at least one of the JOIN nodes, WHERE nodes, ORDER BY nodes, and GROUP BY nodes to identify a derivable time expression; If it is determined through expression lineage analysis that the field in the time expression is directly or indirectly derived from the time field, then the time expression is determined to be a time column; The SQL clause containing the time column is used as the reference SQL clause.

[0123] In some optional embodiments, the SPJG rules include: Convert SQL statements into corresponding abstract syntax trees; Using each SELECT and PROJECT node of the abstract syntax tree as a traversal starting point, the abstract syntax tree is traversed from the first traversal starting point; starting from the traversal starting point of the current traversal, the traversed JOIN or GROUP BY node is used as the traversal end point of the current traversal, the syntax subtree between the current traversal starting point and the current traversal end point is intercepted as a candidate SQL clause, and the traversal is continued from the next variable starting point until all traversal starting points are traversed; For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics, the candidate SQL clause is used as a reference SQL clause.

[0124] In some optional embodiments, the common expression recognition rules include: If the SQL statement is a common expression, it is determined that each SQL clause in the SQL statement is reusable and serves as a reference SQL clause.

[0125] In some optional embodiments, the SQL statement to be processed is obtained in the following manner: Collect SQL logs, which include the content, execution time, and execution count of multiple historical SQL statements; Determining the recommendation priorities of the plurality of historical SQL statements according to the SQL log, wherein the recommendation priority of each historical SQL statement is proportional to the complexity of the content, the execution time, and the number of executions of the historical SQL statement; A preset number of top-ranked historical SQL statements are selected as the SQL statements to be processed.

[0126] The device of the embodiment of the present application can execute the method provided by the embodiment of the present application, and its implementation principle is similar. The actions performed by each module in the device of each embodiment of the present application correspond to the steps in the method of each embodiment of the present application. For the detailed functional description of each module of the device, please refer to the description in the corresponding method shown in the previous text, and will not be repeated here.

[0127] In an embodiment of the present application, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory. The processor executes the computer program to implement the steps of the method for processing SQL statements. Compared with the related art, the following can be achieved: by obtaining at least one SQL statement to be processed, since the SQL statement does not necessarily meet the requirements of direct materialization processing, the system does not directly process the complete SQL statement. Instead of performing materialized recommendations, a SQL clause decomposition strategy is adopted to extract reference SQL clauses that meet at least one of the following criteria: business semantics, reusability, and timeliness. The large language model's natural language generation and analysis capabilities are further leveraged to ensure that the recommended virtual tables are highly versatile and conform to business semantics, reducing duplicate definitions. Each reference SQL clause and a preset prompt word are input into the large language model. The preset prompt word is used to indicate at least one of the following criteria: reuse potential, computational overhead, semantic rationality, and incremental supportability. Combined with the natural language description information of each reference SQL, a first SQL clause is screened from each reference SQL clause to obtain at least one first SQL clause output by the large language model. The first SQL clause is the SQL clause recommended for encapsulation as a virtual table. Since the first SQL clause is the SQL clause with the best reuse potential, computational overhead, semantic rationality, and incremental supportability, selected from each reference SQL clause, this lays the foundation for avoiding blind materialization of low-frequency views and is more conducive to controlling storage space consumption.

[0128] In an alternative embodiment, an electronic device is provided, such as Figure 9 As shown, Figure 9The electronic device 4000 shown includes: a processor 4001 and a memory 4003. The processor 4001 and the memory 4003 are connected, for example, via a bus 4002. Optionally, the electronic device 4000 may further include a transceiver 4004, which may be used for data exchange between the electronic device and other electronic devices, such as data transmission and / or data reception. It should be noted that in actual applications, the number of transceivers 4004 is not limited to one, and the structure of the electronic device 4000 does not constitute a limitation on the embodiments of the present application.

[0129] Processor 4001 can be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute the various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this application. Processor 4001 can also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0130] Bus 4002 may include a path for transmitting information between the aforementioned components. Bus 4002 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, for example. Bus 4002 may be divided into an address bus, a data bus, a control bus, and so on. For ease of illustration, bus 4002 is represented by a single thick line in the figure, but this does not indicate that there is only one bus or only one type of bus.

[0131] The memory 4003 may be a ROM (Read Only Memory) or other type of static storage device that can store static information and instructions, a RAM (Random Access Memory) or other type of dynamic storage device that can store information and instructions, or an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory) or other optical disk storage, optical disk storage (including compact discs, laser discs, optical discs, digital versatile discs, Blu-ray discs, etc.), magnetic disk storage media, other magnetic storage devices, or any other medium that can be used to carry or store computer programs and can be read by a computer, without limitation herein.

[0132] The memory 4003 is used to store the computer program for executing the embodiment of the present application, and the execution is controlled by the processor 4001. The processor 4001 is used to execute the computer program stored in the memory 4003 to implement the steps shown in the above method embodiment.

[0133] An embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps and corresponding contents of the aforementioned method embodiment can be implemented.

[0134] An embodiment of the present application also provides a computer program product, including a computer program, which can implement the steps and corresponding contents of the aforementioned method embodiment when executed by a processor.

[0135] In the specification and claims of this application and the accompanying drawings, the terms "first," "second," "third," "fourth," "1," "2," and so on (if any) are used to distinguish similar objects and are not necessarily used to describe a particular order or sequence. It should be understood that the terms used in this manner are interchangeable where appropriate, such that the embodiments of the present application described herein can be practiced in an order other than that shown or described.

[0136] It should be understood that, although each operation step is indicated by arrows in the flowchart of the embodiment of the present application, the order of implementation of these steps is not limited to the order indicated by the arrows. Unless otherwise clearly stated herein, in some implementation scenarios of the embodiment of the present application, the implementation steps in each flowchart can be performed in other orders according to demand. In addition, some or all of the steps in each flowchart can include multiple sub-steps or multiple stages based on actual implementation scenarios. Some or all of these sub-steps or stages can be executed at the same time, and each sub-step or stage in these sub-steps or stages can also be executed at different times respectively. Under different scenarios at the execution time, the execution order of these sub-steps or stages can be flexibly configured according to demand, and the embodiment of the present application does not limit this.

[0137] The above description is only an optional implementation method for some implementation scenarios of this application. It should be pointed out that for ordinary technicians in this technical field, without departing from the technical concept of the solution of this application, the use of other similar implementation methods based on the technical ideas of this application also falls within the protection scope of the embodiments of this application.

Claims

1. A method for processing SQL statements, characterized in that: include: Get at least one SQL statement to be processed; Extracting a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of an SPJG rule, a common expression recognition rule, and a time column recognition rule; Inputting each reference SQL clause and a preset prompt word into a large language model to obtain at least one first SQL clause output by the large language model, where the first SQL clause is a SQL clause recommended to be encapsulated as a virtual table; The preset prompt word is used to indicate that the first SQL clause is selected from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability, combined with natural language description information of each reference SQL.

2. The method according to claim 1, characterized in that Also includes: displaying the at least one first SQL clause; In response to a selection operation on a target SQL clause in the at least one first SQL clause, encapsulating each selected target SQL clause into a virtual table; Collect statistics on the usage frequency of each virtual table and select virtual tables with usage frequencies exceeding a preset threshold as candidate virtual tables; For each candidate virtual table, determining a materialization priority of the candidate virtual table according to the data size, query response delay, and usage frequency of the candidate virtual table; The candidate virtual tables whose materialization priority is higher than the preset threshold are recommended as target virtual tables for generating materialized views.

3. The method according to claim 2, characterized in that The method further includes: selecting a candidate virtual table with a materialization priority higher than a preset threshold as a target virtual table for recommending materialized view generation; In response to an operation of selecting a candidate incremental column from a target virtual table, determining, through a virtual table model and an abstract syntax tree (AST) syntax analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table; the virtual table model is used to represent the kinship relationship between virtual tables and between virtual tables and data source tables; If the at least one target incremental column is distributed in the same target data source table, or the target incremental column is not a result of aggregation operator calculation, it is determined that the candidate incremental column supports incremental update, and a control for setting the update granularity is displayed; In response to a setting operation on the control, a judgment condition for generating incremental data according to a set update granularity; The target data source table is scanned periodically according to the judgment condition, and a new materialized view is generated according to the scanned incremental data.

4. The method according to claim 2, characterized in that The method further includes: selecting a candidate virtual table with a materialization priority higher than a preset threshold as a target virtual table for recommending materialized view generation; In response to an operation of selecting a candidate incremental column from the target virtual table, determining, through the virtual table model and AST syntax analysis, that the candidate incremental column has at least one target incremental column with a mapping relationship in the generated materialized view; Displaying a control for setting update granularity, and in response to a setting operation on the control, generating a judgment condition for incremental data according to the set update granularity; If the currently set update granularity is the same as the update granularity corresponding to the generated materialized view, then the judgment condition for generating incremental data is based on the generated materialized view; The generated materialized view is scanned periodically according to the judgment condition, and a new materialized view is generated according to the scanned incremental data.

5. The method according to any one of claims 1 to 4, characterized in that Inputting each reference SQL clause and a preset prompt word into the large language model, obtaining a first SQL clause output by the large language model and encapsulating the recommendation into a virtual table, including: Inputting each reference SQL clause and the first prompt word into the large language model, and obtaining natural language description information corresponding to each reference SQL clause output by the large language model; Inputting each reference SQL clause and the second prompt word into the large language model to obtain the first SQL clause output by the large language model; Wherein, the preset prompt words include the first prompt word and the second prompt word; The first prompt word is used to instruct to convert the reference SQL clause into natural language description information, and the first prompt word includes the table structure, field annotation, business scenario, and natural language description information of the sample SQL clause; The second hint word is used to instruct to filter out the first SQL clause from various reference SQL clauses, and the second hint word includes a filtering rule related to at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability.

6. The method according to any one of claims 1 to 4, characterized in that The time column identification rules include: Convert the SQL statement into a corresponding abstract syntax tree, and identify whether the SCAN node has a time column in the abstract syntax tree; If it exists, mark the time column; If not, trace back from the bottom up to at least one of the JOIN nodes, WHERE nodes, ORDER BY nodes, and GROUP BY nodes to identify a derivable time expression; If it is determined through expression lineage analysis that the field in the time expression is directly or indirectly derived from the time field, then the time expression is determined to be a time column; The SQL clause containing the time column is used as the reference SQL clause.

7. The method according to any one of claims 1 to 4, characterized in that The SPJG rules include: Convert SQL statements into corresponding abstract syntax trees; Using each SELECT and PROJECT node of the abstract syntax tree as a traversal starting point, traversing the abstract syntax tree from the first traversal starting point; starting from the traversal starting point of the current traversal, using the traversed JOIN or GROUP BY node as the traversal end point of the current traversal, intercepting the syntax subtree between the current traversal starting point and the current traversal end point as a candidate SQL clause, and continuing the traversal from the next variable starting point until all traversal starting points are traversed; For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics, the candidate SQL clause is used as a reference SQL clause.

8. The method according to any one of claims 1 to 4, characterized in that The common expression recognition rules include: If the SQL statement is a common expression, it is determined that each SQL clause in the SQL statement is reusable and serves as a reference SQL clause.

9. The method according to any one of claims 1 to 4, characterized in that: The SQL statement to be processed is obtained in the following way: Collect SQL logs, which include the content, execution time, and execution count of multiple historical SQL statements; Determining the recommendation priorities of the plurality of historical SQL statements according to the SQL log, wherein the recommendation priority of each historical SQL statement is proportional to the complexity of the content, the execution time, and the number of executions of the historical SQL statement; A preset number of top-ranked historical SQL statements are selected as the SQL statements to be processed.

10. A device for processing SQL statements, characterized in that: include: A statement acquisition module, used to acquire at least one SQL statement to be processed; A clause screening module, configured to extract a reference SQL clause having at least one of business semantics, reusability, and timeliness from each SQL statement to be processed based on at least one of an SPJG rule, a common expression recognition rule, and a time column recognition rule; a clause recommendation module, configured to input each reference SQL clause and a preset prompt word into a large language model, and obtain at least one first SQL clause output by the large language model, wherein the first SQL clause is a recommended SQL clause encapsulated as a virtual table; The preset prompt word is used to indicate that the first SQL clause is selected from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental supportability, combined with natural language description information of each reference SQL.

11. An electronic device comprising a memory, a processor, and a computer program stored in the memory, wherein: The processor executes the computer program to implement the method for processing an SQL statement according to any one of claims 1 to 9.

12. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for processing the SQL statement according to any one of claims 1 to 9 is implemented.

13. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the method for processing the SQL statement according to any one of claims 1 to 9 is implemented.

Images