Processing method and device of SQL statement, electronic equipment and storage medium

Abstract

Embodiments of the present application provide a SQL statement processing method and device, electronic equipment and computer readable storage medium, and relate to the field of databases. The method comprises: obtaining at least one SQL statement to be processed; based on at least one of an SPJG rule, a common expression identification rule and a time column identification rule, extracting a reference SQL clause with at least one of business semantics, reusability and timeliness from each SQL statement to be processed; inputting each reference SQL clause and a preset prompt word into a large language model to obtain at least one first SQL clause; 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 reusability potential, calculation overhead, semantic rationality and incremental support, in combination with natural language description information of each reference SQL. Embodiments of the present application can select a SQL clause more suitable for encapsulation as a virtual table.

Description

Technical Field

[0001] This application relates to the field of database technology, and more specifically, to a method, apparatus, electronic device, computer-readable storage medium, and computer program product for processing SQL statements. Background Technology

[0002] SQL statements are the standard language used to manipulate databases.

[0003] A virtual table (usually referring to a view) is a logical abstraction mechanism in SQL, defined by SQL statements and dynamically generating result sets. The core relationship is that the SQL statement is the implementation carrier of the virtual table, while the virtual table is the encapsulated result of the SQL statement.

[0004] Virtual tables are purely logical structures, requiring dynamic SQL execution for each query; while materialized views pre-calculate and store query results as physical tables, allowing direct reading of stored data and significantly improving performance.

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

[0006] This application provides 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:

[0007] According to one aspect of the embodiments of this application, a method for processing SQL statements is provided, the method comprising:

[0008] Obtain at least one SQL statement to be processed;

[0009] Based on at least one of SPJG rules, common expression recognition rules, and time column recognition rules, extract reference SQL clauses from each SQL statement to be processed that have at least one of business semantics, reusability, and time characteristics.

[0010] Each reference SQL clause and preset prompt words are input into the large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended to be encapsulated as a virtual table.

[0011] 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 support, combined with the natural language description information of each reference SQL.

[0012] According to another aspect of the embodiments of this application, an apparatus for processing SQL statements is provided, the apparatus comprising:

[0013] The statement retrieval module is used to retrieve at least one SQL statement to be processed.

[0014] The clause filtering module is used to extract reference SQL clauses from each SQL statement to be processed based on at least one of the following: SPJG rules, common expression recognition rules, and time column recognition rules. These clauses possess at least one of the following characteristics: business semantics, reusability, and time-related features.

[0015] The clause recommendation module is used to input various reference SQL clauses and preset prompt words into the large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended and encapsulated as a virtual table.

[0016] The preset prompt word is used to indicate the selection of the first SQL clause from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental support, combined with the natural language description information of each reference SQL.

[0017] In some alternative embodiments, the apparatus further includes:

[0018] The clause display module is used to display the at least one first SQL clause;

[0019] The virtual table encapsulation module is configured to encapsulate each selected target SQL clause into a virtual table in response to a selection operation on the at least one first SQL clause;

[0020] The virtual table filtering module is used to count the usage frequency of each virtual table and select virtual tables whose usage frequency exceeds a preset threshold as candidate virtual tables.

[0021] The materialization priority determination module is used to determine the materialization priority of each candidate virtual table based on the data size, query response latency, and usage frequency of the candidate virtual table.

[0022] The virtual table recommendation module is used to recommend candidate virtual tables with a materialization priority higher than a preset threshold as target virtual tables for generating materialized views.

[0023] In some alternative embodiments, the apparatus further includes:

[0024] The incremental column determination module is used to respond to the operation of selecting candidate incremental columns from the target virtual table, and to determine that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table through virtual table model and abstract syntax tree (AST) syntax analysis; the virtual table model is used to represent the lineage relationship between various virtual tables and between virtual tables and data source tables;

[0025] A finer-grained determination module is used to determine that the candidate incremental column supports incremental update if the at least one target incremental column is distributed in the same target data source table, or if the target incremental column is not the result of an aggregation operator calculation, and to display a control for setting the update granularity.

[0026] The condition generation module is used to generate judgment conditions for incremental data based on the set update granularity in response to the setting operation of the control.

[0027] The view generation module is used to periodically scan the target data source table according to judgment conditions and generate new materialized views based on the incremental data scanned.

[0028] In some alternative embodiments, the apparatus further includes:

[0029] The incremental column determination module is used to determine, in response to the operation of selecting candidate incremental columns from the target virtual table, that the candidate incremental column has at least one target incremental column with a mapping relationship in the generated materialized view through virtual table model and AST syntax analysis.

[0030] A finer-grained determination module is used to display controls for setting update granularity, and in response to the setting operation of the controls, generates judgment conditions for incremental data based on the set update granularity;

[0031] The condition generation module is used to generate 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.

[0032] The view generation module is used to periodically scan the generated materialized views according to judgment conditions and generate new materialized views based on the scanned incremental data.

[0033] In some optional embodiments, the clause recommendation module is specifically used for:

[0034] Each reference SQL clause and the first prompt word are input into the large language model to obtain the natural language description information corresponding to each reference SQL clause output by the large language model.

[0035] Input 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;

[0036] The preset prompt words include the first prompt word and the second prompt word;

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

[0038] The second prompt word is used to indicate the selection of the first SQL clause from the various reference SQL clauses. The second prompt word includes a selection rule related to at least one of reusability potential, computational overhead, semantic rationality, and incremental support.

[0039] In some optional embodiments, the time series identification rules include:

[0040] The SQL statement is converted into a corresponding abstract syntax tree, and the presence of a time column in the SCAN node is identified within the abstract syntax tree.

[0041] If it exists, then mark the time column;

[0042] If it does not exist, backtrack from bottom to top to identify at least one of the JOIN node, WHERE node, ORDER BY node, and GROUP BY node to identify a derivable time expression.

[0043] If, through expression lineage analysis, it is determined that a field in the time expression is directly or indirectly derived from a time field, then the time expression is determined to be a time column.

[0044] Use the SQL clause containing the time column as the reference SQL clause.

[0045] In some optional embodiments, the SPJG rules include:

[0046] Transform SQL statements into corresponding abstract syntax trees;

[0047] Each SELECT and PROJECT node in the abstract syntax tree is taken as the starting point of the traversal. The abstract syntax tree is traversed starting from the first starting point. Starting from the current starting point of the traversal, the JOIN or GROUPBY node is taken as the current ending point of the traversal. The syntax subtree between the current starting point and the current ending point is extracted as a candidate SQL clause. The traversal continues from the next variable starting point until all traversal starting points are traversed.

[0048] For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics, then the candidate SQL clause is used as a reference SQL clause.

[0049] In some optional embodiments, the common expression identification rules include:

[0050] If the SQL statement is a common expression, each SQL clause in the SQL statement is determined to be reusable and is used as a reference SQL clause.

[0051] In some optional embodiments, the SQL statement to be processed is obtained in the following way:

[0052] Collect SQL logs, which include the content, execution time, and number of executions of multiple historical SQL statements;

[0053] The recommended priority of the multiple historical SQL statements is determined based on the SQL logs, wherein the recommended priority of each historical SQL statement is proportional to the complexity of the content of the historical SQL statement, the execution time, and the number of times it is executed;

[0054] Select a preset number of historical SQL statements that are ranked at the top as the SQL statements to be processed.

[0055] According to another aspect of the embodiments of this application, an electronic device is provided, the electronic device including a memory, a processor and a computer program stored in the memory, the processor executing the computer program to implement the above-described method.

[0056] According to another aspect of the embodiments of this application, a computer-readable storage medium is provided, on which a computer program is stored, which, when executed by a processor, implements the above-described method.

[0057] According to one aspect of the embodiments of this application, a computer program product is provided, including a computer program that, when executed by a processor, implements the above-described method.

[0058] The beneficial effects of the technical solution provided in this application are: by obtaining at least one SQL statement to be processed, since the SQL statement may not meet the requirements for direct materialization processing, the system does not directly process the complete SQL statement. Instead of materializing recommendations, a SQL clause decomposition strategy is adopted. Reference SQL clauses with at least one of the following characteristics—business semantics, reusability, and timeliness—are extracted. The natural language generation and analysis capabilities of the large language model are then utilized to ensure that the recommended virtual tables are highly generalizable and conform to business semantics, reducing redundant definitions. Each reference SQL clause and preset prompts are input into the large language model. The preset prompts indicate at least one of the following criteria: reusability potential, computational cost, semantic rationality, and incremental support. Combined with the natural language description information of each reference SQL clause, a first SQL clause is selected from the reference SQL clauses. This results in at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended and encapsulated as a virtual table. Since the first SQL clause is selected from each reference SQL clause and possesses at least one of the following characteristics—data reusability potential, computational cost, semantic rationality, and incremental support—it lays the foundation for avoiding blind materialization of low-frequency views and is more conducive to controlling storage space consumption. Attached Figure Description

[0059] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments of this application will be briefly introduced below.

[0060] Figure 1 This is a schematic diagram of the system architecture for implementing SQL statement processing provided in an embodiment of this application;

[0061] Figure 2 A flowchart illustrating a method for processing SQL statements provided in an embodiment of this application;

[0062] Figure 3 A flowchart illustrating the process of collecting SQL statements is provided for an embodiment of this application;

[0063] Figure 4 A flowchart illustrating the process of extracting reference SQL clauses from an SQL statement, provided for an embodiment of this application;

[0064] Figure 5 A flowchart illustrating a method for processing SQL statements provided in another embodiment of this application;

[0065] Figure 6 A flowchart illustrating a method for processing SQL statements provided in another embodiment of this application;

[0066] Figure 7 A flowchart illustrating a method for processing SQL statements provided in yet another embodiment of this application;

[0067] Figure 8 A schematic diagram of the structure of an SQL statement processing device provided in an embodiment of this application;

[0068] Figure 9 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0069] The embodiments of this application are described below with reference to the accompanying drawings. It should be understood that the embodiments described below with reference to the accompanying drawings are exemplary descriptions for explaining the technical solutions of the embodiments of this application, and do not constitute a limitation on the technical solutions of the embodiments of this application.

[0070] Those skilled in the art will understand that, unless otherwise stated, the singular forms “a,” “an,” and “the” used herein may also include the plural forms. It should be further understood that the terms “comprising” and “including” as used in embodiments of this application mean that the corresponding feature can be implemented as the presented feature, information, data, step, operation, element, and / or component, but do not exclude implementation as other features, information, data, step, operation, element, component, and / or combinations thereof supported by the art. It should be understood that when we say that an element is “connected” or “coupled” to another element, the one element can be directly connected or coupled to the other element, or it can mean that the one element and the other element establish a connection relationship through an intermediate element. Furthermore, “connected” or “coupled” as used herein can include wireless connection or wireless coupling. The term “and / or” as used herein 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.”

[0071] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings.

[0072] In the process of database design and use, there are often problems such as poor SQL reusability, complex query logic, high maintenance costs, and coupling between application and database structure. To address these issues, related technologies have introduced virtual tables, which are abstract transformations of the underlying tables and the actual business application tables.

[0073] Underlying data refers to the actual base table data stored in the database, typically the raw data directly generated and inserted 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 accuracy of upper-level queries.

[0074] 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 virtual bottleneck of virtual tables. However, materialized views also have limitations; using too many or unnecessary materialized views can consume additional storage space. SQL queries need to meet specific refresh strategies, expiration times, and dependencies, all of which require additional design and management by the user.

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

[0076] Currently, 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 low model evolution efficiency during development and operation, making it difficult to meet the higher requirements of modern data systems for performance, flexibility, and intelligence.

[0077] If many regular views are defined in the early stages of a business application, and query performance needs to be improved later, they must be manually rewritten as materialized views. This increases development costs and maintenance complexity. Furthermore, because switching between virtual tables and views is inconvenient, developers cannot flexibly adjust based on system load or storage resources, leading to delayed performance optimization. This often results in either sacrificing performance (depending on the context) or... Figure 1 (either real-time computation) or excessive consumption of storage space (long-term retention of materialized views).

[0078] To improve the efficiency of accessing and abstracting the underlying data, this application's embodiments introduce virtual tables and intelligent materialization mechanisms to achieve efficient modeling and optimized access to the underlying data.

[0079] The SQL statement processing methods, apparatus, electronic devices, computer-readable storage media, and computer program products provided in this application are intended to solve the above-mentioned technical problems of the prior art.

[0080] The technical solutions of this application and their effects are described below through several exemplary embodiments. It should be noted that the following embodiments can be referenced, borrowed from, or combined with each other. Identical terms, similar features, and similar implementation steps in different embodiments will not be repeated.

[0081] Figure 1The system architecture diagram for implementing the SQL statement processing method provided in the embodiments of this application is shown in the figure. The system includes a terminal device 110 and a server 120.

[0082] In the embodiments of this application, the terminal device includes, but is not limited to, mobile phones, tablets, laptops, desktop computers, vehicle terminals, etc.; the terminal device may have a client related to the processing of SQL statements installed. The client may be software (such as browsers, database management software, etc.), or web pages, mini programs, etc., and the server is a backend server corresponding to the software, web pages, mini programs, etc., or a server specifically used for processing SQL statements. This application does not make specific limitations.

[0083] A server can be a standalone physical server, a server cluster or distributed system consisting 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 communication, middleware services, domain name services, security services, content delivery networks (CDN), and big data and artificial intelligence platforms.

[0084] It should be noted that the SQL statement processing method in this application embodiment can be executed by an electronic device, which can be a server or a terminal device. That is, the method can be executed by the server or the terminal device alone, or by both the server and the terminal device. For example, when executed by both the server and the terminal device, the terminal device sends each SQL statement to the server. Based on the obtained SQL statements, the server extracts reference SQL clauses from each SQL statement to be processed, based on at least one of SPJG rules, common expression recognition rules, and time column recognition rules, which possess at least one of the following: business semantics, reusability, and timeliness. The reference SQL clauses and preset prompt words are input into a large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended to be encapsulated as a virtual table.

[0085] In one alternative implementation, the terminal device and the server can communicate via a communication network.

[0086] In one alternative implementation, the communication network is a wired network or a wireless network.

[0087] It should be noted that, Figure 1 The examples shown are merely illustrative; in reality, the number of terminal devices and servers is unlimited and is not specifically limited in the embodiments of this application.

[0088] In this application embodiment, when there are multiple servers, the multiple servers can form a blockchain, and the server is a node on the blockchain; as disclosed in the SQL statement processing method of this application embodiment, the SQL statements involved can be stored on the blockchain, etc.

[0089] Furthermore, the embodiments of this application can be applied to various scenarios, including not only SQL statement processing scenarios, but also scenarios such as cloud technology, artificial intelligence, smart transportation, and assisted driving. For example, in OLAP scenarios, real-time analysis platforms, and systems with large-scale joins and ags, it significantly improves system query performance, with remarkable results.

[0090] This application provides a method for processing SQL statements, such as... Figure 2 As shown, the method includes:

[0091] S101. Obtain at least one SQL statement to be processed.

[0092] In some embodiments, the user's historical SQL logs can be collected periodically or irregularly, and the SQL statements involved in the historical SQL logs can be used as SQL statements to be processed.

[0093] In some embodiments, an interface may also be provided to the user to display a plurality of selectable SQL statements, and in response to the selection of at least one SQL statement, the selected SQL statement is set as the SQL statement to be processed. It is understood that the selectable SQL statements displayed in the interface may come from the user's historical SQL logs.

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

[0095] In some embodiments, the SQL statement to be processed is obtained in the following way:

[0096] Collect SQL logs, which include the content, execution time, and number of executions of multiple historical SQL statements;

[0097] The recommended priority of the multiple historical SQL statements is determined based on the SQL logs;

[0098] Select a preset number of historical SQL statements that are ranked at the top as the SQL statements to be processed.

[0099] Please see Figure 3 The figure illustrates an exemplary flowchart of the SQL statement collection process in an embodiment of this application. As shown, 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 by background tasks and written in batches to a statistics table. The SQL statements in the statistics table are persisted to obtain statistical information.

[0100] The SQL logs collected in this embodiment include SQL statement content, execution time, execution count, etc. By statistically analyzing the execution time, the average latency of historical SQL statements can be determined, and by analyzing the execution count, the access frequency of SQL statements can be determined.

[0101] This application can further determine the recommended priority of each historical SQL statement based on its content, execution time, and number of executions. The recommended priority of each historical SQL statement is proportional to the complexity of its content, execution time, and number of executions. Furthermore, a preset number of top-ranked historical SQL statements are selected as the SQL statements to be processed, thereby filtering out SQL statements that are more suitable for encapsulation into virtual tables or even for building materialized views from the source.

[0102] In some embodiments, historical SQL statements can be sorted and optimized according to the following scoring function, and the top-N SQL statements with the highest scores can be selected as candidates for further subquery decomposition.

[0103]

[0104] in, As these are adjustable parameters, they can be flexibly adjusted according to the user's desired business scenario. SQL complexity can be comprehensively evaluated based on multiple dimensions such as the number of nesting levels, the number of SQL clauses contained in the SQL statement, and the number of JOINs. It can be understood that the higher the number of nesting levels, the number of SQL clauses, and the number of JOINs, the higher the SQL complexity.

[0105] S102. Based on at least one of SPJG rules, common expression recognition rules, and time column recognition rules, extract reference SQL clauses from each SQL statement to be processed that possess at least one of the following characteristics: business semantics, reusability, and timeliness.

[0106] This embodiment of the application takes into account that historical SQL may not meet the requirements for direct materialization processing. Therefore, the system does not directly recommend materialization for complete SQL, but instead adopts a subquery (i.e., SQL clause) decomposition strategy to extract SQL clauses with reusable value. The decomposition process uses heuristic rules.

[0107] The heuristic rules in this application include three categories: SPJG rules, Common Subexpressions (CSE) recognition rules, and time series recognition rules.

[0108] SPJG rules are an efficient algorithm for rewriting materialized view queries. They are mainly based on matching of selection, projection, join, and group-by operators. The core of the algorithm is to rewrite the query into an equivalent materialized view form through structured matching to improve query performance.

[0109] A common expression refers to a subquery, expression, or computational logic that appears multiple times in an SQL statement. A common expression identification rule is a set of rules used to identify common expressions. This application can employ syntax analysis, abstract syntax tree analysis, and query optimizer analysis, among others, to identify common expressions. Taking query optimizer analysis as an example, database query optimizers (such as MySQL, Oracle, and PostgreSQL) typically automatically identify common expressions and perform optimizations (such as subquery expansion and common subexpression elimination).

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

[0111] Please see Figure 4 The example illustrates a flowchart of extracting reference SQL clauses from SQL statements in an embodiment of this application. As shown in the figure, the background task can periodically (e.g., weekly) retrieve statistical SQL statements from the statistics table. First, based on a preset blacklist or other filtering rules, SQL statements that are not recommended to be encapsulated as virtual tables are filtered out. Then, each SQL clause in the SQL statement is filtered through SPJG rules, common expression recognition rules, and time column recognition rules, and the obtained reference SQL clauses are stored in the initial screening recommendation table.

[0112] The embodiments of this application, through the above-described heuristic rules, are able to extract reference SQL clauses from complex SQL statements that possess at least one of the following characteristics: business semantics, high reusability potential, and timeliness.

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

[0114] This application further introduces a semantic evaluation mechanism for SQL clauses based on a large language model, which intelligently sorts and filters reference SQL clauses. In order for the large language model to have the ability to filter the first SQL clause, this application requires inputting preset prompt words when inputting reference SQL clauses into the large language model. The preset prompt words are used to indicate that the first SQL clause is selected from the reference SQL clauses based on at least one of reusability potential, computational cost, semantic rationality and incremental support, combined with the natural language description information of each reference SQL.

[0115] In other words, the preset prompts in this embodiment need to instruct the large language model to convert each reference SQL clause into a natural language expression, and also need to provide the large language model with filtering rules. These filtering rules are used to evaluate reusability potential, computational cost, semantic rationality, and incremental support. Thus, the large language model can generate scores based on the preset prompts according to four dimensions—reusability potential, computational cost, semantic rationality, and incremental support—and select the most suitable first SQL clause to be encapsulated as a virtual table, thereby completing the filtering of reference SQL clauses.

[0116] The SQL statement processing method provided in this application involves obtaining at least one SQL statement to be processed. Since the SQL statement may not meet the requirements for direct materialization processing, the system does not directly process the complete SQL statement. Instead of materializing recommendations, a SQL clause decomposition strategy is adopted. Reference SQL clauses with at least one of the following characteristics—business semantics, reusability, and timeliness—are extracted. The natural language generation and analysis capabilities of the large language model are then utilized to ensure that the recommended virtual tables are highly generalizable and conform to business semantics, reducing redundant definitions. Each reference SQL clause and preset prompts are input into the large language model. The preset prompts indicate at least one of the following criteria: reusability potential, computational cost, semantic rationality, and incremental support. Combined with the natural language description information of each reference SQL clause, a first SQL clause is selected from the reference SQL clauses. This results in at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended and encapsulated as a virtual table. Since the first SQL clause is selected from each reference SQL clause and possesses at least one of the following characteristics—data reusability potential, computational cost, semantic rationality, and incremental support—it lays the foundation for avoiding blind materialization of low-frequency views and is more conducive to controlling storage space consumption.

[0117] Please see Figure 5 The figure illustrates a flowchart of a method for processing SQL statements provided in another embodiment of this application, as shown in the figure, including:

[0118] S201. Obtain at least one SQL statement to be processed;

[0119] S202. Based on at least one of SPJG rules, common expression recognition rules, and time column recognition rules, extract reference SQL clauses from each SQL statement to be processed that have at least one of business semantics, reusability, and timeliness.

[0120] S203. Input each reference SQL clause and preset prompt words into the large language model, obtain and display at least one first SQL clause output by the large language model;

[0121] S204. In response to the selection operation of the target SQL clause in the at least one first SQL clause, each selected target SQL clause is encapsulated into a virtual table;

[0122] S205. Statistically analyze the usage frequency of each virtual table and select virtual tables whose usage frequency exceeds a preset threshold as candidate virtual tables.

[0123] S206. For each candidate virtual table, determine the materialization priority of the candidate virtual table based on the data size, query response latency, and usage frequency of the candidate virtual table.

[0124] S207. Select candidate virtual tables with materialization priority higher than a preset threshold as target virtual tables for generating materialized views.

[0125] In this embodiment of the application, after obtaining the first SQL clause output by the large language model, the first SQL clause can be displayed through a preset interface. Users can browse these first SQL clauses, select the target SQL clause that needs to be encapsulated into a virtual table, and encapsulate each target SQL clause into a virtual table.

[0126] By monitoring the usage of each encapsulated virtual table, this application can dynamically decide whether each virtual table needs to be materialized to balance performance gains and storage costs. Specifically, this application will statistically analyze the usage frequency of each virtual table, which may include at least one of query frequency, update frequency, and reference frequency.

[0127] In some embodiments, this application can statistically analyze the access frequency and number of calls to virtual tables within a unit of time. Virtual tables used infrequently (access frequency below a set threshold) will not participate in the subsequent materialization evaluation process to avoid resource waste, and the retained virtual tables are referred to as candidate virtual tables.

[0128] In some embodiments, this application determines the materialization priority of each candidate virtual table based on its data size, query response latency, and usage frequency. It can be calculated using the following formula:

[0129]

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

[0131] If the materialization priority of a virtual table is higher than a preset threshold (which can be customized by the user), the system will include it in the candidate materialization view set and record its materialization priority (Score), access statistics, data volume and other information, and store them in the target recommendation table set for subsequent materialization or user review.

[0132] Please see Figure 6 The figure illustrates, by way of example, a flowchart of a method for processing SQL statements provided in another embodiment of this application, as shown in the figure, including:

[0133] S301. Display candidate virtual tables with materialization priority higher than a preset threshold as recommended target virtual tables for generating materialized views.

[0134] S302. In response to the operation of selecting candidate incremental columns from the target virtual table, through virtual table model and abstract syntax tree (AST) syntax analysis, determine that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table;

[0135] S303. If at least one target incremental column is distributed in the same target data source table, or if the target incremental column is not the result of the aggregation operator calculation, then it is determined that the candidate incremental column supports incremental update, and the control for setting the update granularity is displayed.

[0136] S304. In response to the setting operation of the control, a judgment condition for generating incremental data based on the set update granularity is generated;

[0137] S305. Periodically scan the target data source table according to the judgment conditions, and generate a new materialized view based on the incremental data scanned.

[0138] In the above embodiments, when a user selects materialized recommendations or manually specifies 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 materialization processing logic that supports incremental refresh, thereby improving data processing efficiency and reducing system load.

[0139] The virtual table model in this application embodiment is used to represent the lineage relationship between various virtual tables and between virtual tables and data source tables. For example, the virtual table model can determine that virtual table a originates from virtual table b, virtual table b originates from virtual table c, and can even be traced all the way back to the data table. Therefore, the virtual table model can be used to realize the tracing and derivation of virtual tables.

[0140] An Abstract Syntax Tree (AST) is a tree-like representation of the syntactic structure of source code. It abstracts away the specific syntactic details of the code (such as semicolons and parentheses), retaining only the core syntactic structure. AST parsing involves three aspects:

[0141] SQL parsing: Parses the SQL statement that defines the virtual table into an AST, and analyzes the column definitions, expressions, function calls, etc.

[0142] Column mapping analysis: By traversing the AST, the source of virtual table columns 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 contained in the AST.

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

[0144] This application embodiment can extract the column definitions and their sources from the virtual table through AST syntax analysis. Then, for each virtual table column, it checks whether it is directly mapped from the data source table column. If it is indirectly mapped (such as generated by an expression or function), the AST nodes of the expression or function are further analyzed to determine the data source table column it depends on. Then, the mapping relationship obtained by AST analysis is verified through the lineage information in the virtual table model to ensure that the candidate incremental column has a corresponding target incremental column in the data source table. The mapping relationship between the virtual table column and the data source table column is recorded for subsequent incremental synchronization or data lineage analysis.

[0145] The following section uses a target virtual table to illustrate the process of determining the target incremental column in this application:

[0146] Suppose we have the following virtual table definition:

[0147] SQL

[0148] CREATE VIEW virtual_table AS

[0149] SELECT

[0150] user_id,

[0151] user_name,

[0152] age + 1 AS next_year_age

[0153] FROM

[0154] source_table;

[0155] AST analysis:

[0156] user_id and user_name are directly mapped to columns in source_table.

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

[0158] Mapping relationship:

[0159] virtual_table.user_id→source_table.user_id

[0160] virtual_table.user_name→source_table.user_name

[0161] virtual_table.next_year_age→source_table.age

[0162] Incremental column recognition:

[0163] If next_year_age is a candidate increment column, then its corresponding target increment column is source_table.age.

[0164] In some embodiments, the present application parses the virtual table a using AST syntax to determine that the candidate incremental column aA in the virtual table a is mapped to column bB of the source table virtual table b. The virtual table b is then parsed using AST syntax to determine that column bB is obtained from func(cC). This process is repeated until the data source table p is reached, and it is then known that the column aA is mapped to the column pP of the source table.

[0165] After determining the target incremental column, this application further requires determining whether the candidate incremental column supports incremental updates based on the target incremental column. Specifically, this application adopts a method of determining 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 an aggregation operator calculation. If the 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 an aggregation operator calculation, then the candidate incremental column is determined to support incremental updates.

[0166] For candidate incremental columns that support incremental updates, this application embodiment will correspondingly display a control for setting the update granularity. This application embodiment does not limit the unit of update granularity, such as minutes, hours, days, weeks, months, etc.

[0167] By responding to the setting operation of the control, the application generates incremental data judgment conditions based on the set update granularity, and periodically scans the target data source table according to the judgment conditions. The application can use either polling mode or CDC (ChangeData Capture) mode to scan incremental data. Polling mode means periodically scanning the target source data table in turn, while CDC mode captures data changes in real time by connecting to change log sources such as Binlog and WAL, and generates a new materialized view based on the scanned incremental data.

[0168] Please see Figure 7 The figure illustrates a flowchart of a method for processing SQL statements according to another embodiment of this application, as shown in the figure, including:

[0169] S401. Display candidate virtual tables with materialization priority higher than a preset threshold as target virtual tables for generating materialized views.

[0170] S402. In response to the operation of selecting candidate incremental columns from the target virtual table, the candidate incremental columns are determined to have at least one target incremental column with a mapping relationship in the generated materialized view through virtual table model and AST syntax analysis.

[0171] S403. Display a control for setting the update granularity, and in response to the setting operation of the control, generate a judgment condition for incremental data based on the set update granularity;

[0172] S404. 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.

[0173] S405. Periodically scan the generated materialized view according to the judgment conditions, and generate a new materialized view based on the scanned incremental data.

[0174] In the above embodiments, this application uses a virtual table model and AST syntax analysis to determine whether the candidate incremental column exists in the generated materialized view. If the target incremental column is found 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 the new materialized view and the processing SQL, the judgment condition of the generated materialized view cannot be used. Instead, the judgment condition for generating incremental data needs to be generated according to the set update granularity.

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

[0176] Based on the above embodiments, as an optional embodiment, each reference SQL clause and preset prompt words are input into the large language model to obtain the first SQL clause of the recommendation encapsulated in the virtual table by the large language model, including:

[0177] Each reference SQL clause and the first prompt word are input into the large language model to obtain the natural language description information corresponding to each reference SQL clause output by the large language model.

[0178] 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.

[0179] The preset prompt words in this embodiment include two sets of prompt words, namely the first prompt word and the second prompt word.

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

[0181] In some embodiments, the first prompt mainly includes the structural information of the tables involved in the SQL, the field annotation information (i.e., the business meaning or purpose of the fields), and a description of the business scenario of this SQL query, etc., to enhance the context awareness capability of the large model. For example, an SQL statement for querying regional sales:

[0182] SELECT r.province, SUM(o.sales_amount) AS total_sales

[0183] FROM sales_order o

[0184] JOIN region_info r ON o.city_code = r.city_code

[0185] WHERE o.order_date>= '2024-01-01'

[0186] GROUP BY r.province;

[0187] The first hint includes the table structure of the sales_order table and the region_info table, their purpose (the sales_order table records the sales details of each order, etc.), the meaning of each field (such as sales_order.city_code representing the city code associated with the order), and the business scenario of this SQL query, namely, to analyze the total sales of each provincial region since 2024, and the query results are used to support the headquarters in formulating provincial sales strategies, etc.

[0188] The second prompt word is used to indicate which first SQL clause to select from the various reference SQL clauses. The second prompt word includes a selection rule related to at least one of reusability potential, computational overhead, semantic rationality, and incremental support.

[0189] In some embodiments, the second prompt should provide filtering rules, such as cost ratio coefficient, reuse ratio coefficient, etc., as well as sufficient contextual semantics. For example, what is the current business scenario, the table structure of each table, the data volume, the SQL statement, and the set of SQL clauses extracted so far. The large speech model can then determine the generality of each reference SQL clause and the final selected first SQL clause based on the structure of the SQL clause (e.g., filters may limit reusability) and the SQL statement, the cost based on the data volume, and whether incremental updates are supported based on the time column.

[0190] Based on the above embodiments, as an optional embodiment, the time series identification rule includes:

[0191] The SQL statement is converted into a corresponding abstract syntax tree, and the presence of a time column in the SCAN node is identified within the abstract syntax tree.

[0192] If it exists, then mark the time column;

[0193] If not, backtrack from bottom to top through at least one of the JOIN node, WHERE node, ORDER BY node, and GROUP BY node to identify a deducible time expression (such as the calculation or filtering of time fields).

[0194] If, through expression lineage analysis, it is determined that a field in the time expression is directly or indirectly derived from a time field, then the time expression is determined to be a time column.

[0195] Use the SQL clause containing the time column as the reference SQL clause.

[0196] In this embodiment, the SQL statement is first converted into an abstract syntax tree, and the SCAN node in the abstract syntax tree is identified. In this embodiment, the SCAN node may represent a full table scan operation on the database table in the AST, that is, reading all the data in the table row by row to find the record that meets the query conditions. The SCAN node usually corresponds to the query operation when there is no valid index or the index is unavailable in the SQL statement.

[0197] If a SCAN node has a time column, it will mark that time column and use the corresponding SQL clause as a reference SQL clause.

[0198] For a SCAN node that does not have a time column, the system will backtrack from bottom to top through at least one of the JOIN node, WHERE node, ORDERBY node, and GROUP BY node to identify a deducible time expression.

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

[0200] The WHERE node represents the filtering conditions in an SQL statement, used to select records that meet specific criteria. Time expressions in the WHERE node are typically used for filtering records, such as 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.

[0201] The ORDER BY node represents the sorting operation in the SQL statement, used to sort the query results. The time expression in the ORDER BY node is used to sort the query results, such as ORDER BY date DESC. The time field is usually used to arrange records in chronological order.

[0202] The GROUP BY node represents the grouping operation in an SQL statement, used to group query results according to a specified field. The time expression within the GROUP BY node is used to group records by a time field, for example, GROUP BY DATE(date). The time expression may involve date functions such as DATE(), MONTH(), YEAR(), etc.

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

[0204] Based on the above embodiments, as an optional embodiment, the SPJG rule includes:

[0205] Transform SQL statements into corresponding abstract syntax trees;

[0206] Each SELECT and PROJECT node in the abstract syntax tree is taken as the starting point of the traversal. The abstract syntax tree is traversed starting from the first starting point. Starting from the current starting point of the traversal, the JOIN or GROUPBY node is taken as the current ending point of the traversal. The syntax subtree between the current starting point and the current ending point is extracted as a candidate SQL clause. The traversal continues from the next variable starting point until all traversal starting points are traversed.

[0207] For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics (e.g., fields are parsable, logic is complete, etc.), then the candidate SQL clause is used as the reference SQL clause.

[0208] The embodiments of this application can extract SQL clauses with business semantics and high reusability potential from complex SQL statements.

[0209] Based on the above embodiments, the common expression recognition rules include:

[0210] If the SQL statement is a common expression, each SQL clause in the SQL statement is determined to be reusable and is used as a reference SQL clause.

[0211] It should be noted that if the SQL statement uses the WITH ... AS syntax structure (i.e., Common Expression Form), since the CTE itself usually has semantic independence and reusability, it is naturally suitable as the basis for a virtual table. Therefore, in this embodiment, the SQL clauses defined in the structure are directly extracted as reference SQL clauses.

[0212] The technical effects of the embodiments of this application are as follows:

[0213] I. Significantly improve system query performance

[0214] By intelligently analyzing historical SQL access patterns, high-frequency, high-latency, and highly repetitive query logic is identified, and materialized views are automatically recommended.

[0215] This allows for the pre-materialization of complex SQL query results, avoiding repetitive calculations for each query, significantly reducing query latency, and improving overall system throughput.

[0216] It is particularly suitable for OLAP scenarios, real-time analysis platforms, and large-scale join &ag systems, with significant results.

[0217] II. Reduce storage and maintenance costs

[0218] The system automatically performs dynamic evaluations based on the usage frequency, data size, and refresh cost of virtual tables, avoiding the blind materialization of low-frequency views and controlling storage space consumption.

[0219] It supports incremental refresh logic derivation, reducing the overhead of full refresh and lowering operation and maintenance scheduling costs.

[0220] Maintenance personnel no longer need to manually manage the dependencies and refresh strategies of virtual table materialization, reducing human configuration errors.

[0221] III. Improve Development and Modeling Efficiency

[0222] In the early product database modeling stage, virtual tables are automatically recommended by decomposing historical SQL structures, reducing the workload of developers manually creating virtual tables.

[0223] By using SQL heuristic partitioning and large-scale model semantic understanding, we ensure that the recommended virtual tables are highly generalizable, conform to business semantics, and reduce redundant definitions.

[0224] The virtual table and materialized view can be seamlessly connected, supporting rapid trial operation of the business and on-demand materialization, thus reducing the threshold for model adjustment.

[0225] This application provides an SQL statement processing device, such as... Figure 8As shown, the SQL statement processing device may include: a statement acquisition module 801, a clause filtering module 802, and a clause recommendation module 803, wherein,

[0226] Statement acquisition module 801 is used to acquire at least one SQL statement to be processed;

[0227] Clause filtering module 802 is used to extract reference SQL clauses with at least one of business semantics, reusability and time-related characteristics from each SQL statement to be processed based on at least one of SPJG rules, common expression recognition rules and time column recognition rules.

[0228] The clause recommendation module 803 is used to input each reference SQL clause and preset prompt words into the large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended and encapsulated as a virtual table.

[0229] The preset prompt word is used to indicate the selection of the first SQL clause from each reference SQL clause based on at least one of reuse potential, computational overhead, semantic rationality, and incremental support, combined with the natural language description information of each reference SQL.

[0230] In some alternative embodiments, the apparatus further includes:

[0231] The clause display module is used to display the at least one first SQL clause;

[0232] The virtual table encapsulation module is configured to encapsulate each selected target SQL clause into a virtual table in response to a selection operation on the at least one first SQL clause;

[0233] The virtual table filtering module is used to count the usage frequency of each virtual table and select virtual tables whose usage frequency exceeds a preset threshold as candidate virtual tables.

[0234] The materialization priority determination module is used to determine the materialization priority of each candidate virtual table based on the data size, query response latency, and usage frequency of the candidate virtual table.

[0235] The virtual table recommendation module is used to recommend candidate virtual tables with a materialization priority higher than a preset threshold as target virtual tables for generating materialized views.

[0236] In some alternative embodiments, the apparatus further includes:

[0237] The incremental column determination module is used to respond to the operation of selecting candidate incremental columns from the target virtual table, and to determine that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table through virtual table model and abstract syntax tree (AST) syntax analysis; the virtual table model is used to represent the lineage relationship between various virtual tables and between virtual tables and data source tables;

[0238] A finer-grained determination module is used to determine that the candidate incremental column supports incremental update if the at least one target incremental column is distributed in the same target data source table, or if the target incremental column is not the result of an aggregation operator calculation, and to display a control for setting the update granularity.

[0239] The condition generation module is used to generate judgment conditions for incremental data based on the set update granularity in response to the setting operation of the control.

[0240] The view generation module is used to periodically scan the target data source table according to judgment conditions and generate new materialized views based on the incremental data scanned.

[0241] In some alternative embodiments, the apparatus further includes:

[0242] The incremental column determination module is used to determine, in response to the operation of selecting candidate incremental columns from the target virtual table, that the candidate incremental column has at least one target incremental column with a mapping relationship in the generated materialized view through virtual table model and AST syntax analysis.

[0243] A finer-grained determination module is used to display controls for setting update granularity, and in response to the setting operation of the controls, generates judgment conditions for incremental data based on the set update granularity;

[0244] The condition generation module is used to generate 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.

[0245] The view generation module is used to periodically scan the generated materialized views according to judgment conditions and generate new materialized views based on the scanned incremental data.

[0246] In some optional embodiments, the clause recommendation module is specifically used for:

[0247] Each reference SQL clause and the first prompt word are input into the large language model to obtain the natural language description information corresponding to each reference SQL clause output by the large language model.

[0248] Input 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;

[0249] The preset prompt words include the first prompt word and the second prompt word;

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

[0251] The second prompt word is used to indicate the selection of the first SQL clause from the various reference SQL clauses. The second prompt word includes a selection rule related to at least one of reusability potential, computational overhead, semantic rationality, and incremental support.

[0252] In some optional embodiments, the time series identification rules include:

[0253] The SQL statement is converted into a corresponding abstract syntax tree, and the presence of a time column in the SCAN node is identified within the abstract syntax tree.

[0254] If it exists, then mark the time column;

[0255] If it does not exist, backtrack from bottom to top to identify at least one of the JOIN node, WHERE node, ORDER BY node, and GROUP BY node to identify a derivable time expression.

[0256] If, through expression lineage analysis, it is determined that a field in the time expression is directly or indirectly derived from a time field, then the time expression is determined to be a time column.

[0257] Use the SQL clause containing the time column as the reference SQL clause.

[0258] In some optional embodiments, the SPJG rules include:

[0259] Transform SQL statements into corresponding abstract syntax trees;

[0260] Each SELECT and PROJECT node in the abstract syntax tree is taken as the starting point of the traversal. The abstract syntax tree is traversed starting from the first starting point. Starting from the current starting point of the traversal, the JOIN or GROUPBY node is taken as the current ending point of the traversal. The syntax subtree between the current starting point and the current ending point is extracted as a candidate SQL clause. The traversal continues from the next variable starting point until all traversal starting points are traversed.

[0261] For each candidate SQL clause, if it is determined that the candidate SQL clause has business semantics, then the candidate SQL clause is used as a reference SQL clause.

[0262] In some optional embodiments, the common expression identification rules include:

[0263] If the SQL statement is a common expression, each SQL clause in the SQL statement is determined to be reusable and is used as a reference SQL clause.

[0264] In some optional embodiments, the SQL statement to be processed is obtained in the following way:

[0265] Collect SQL logs, which include the content, execution time, and number of executions of multiple historical SQL statements;

[0266] The recommended priority of the multiple historical SQL statements is determined based on the SQL logs, wherein the recommended priority of each historical SQL statement is proportional to the complexity of the content of the historical SQL statement, the execution time, and the number of times it is executed;

[0267] Select a preset number of historical SQL statements that are ranked at the top as the SQL statements to be processed.

[0268] The apparatus in this application embodiment can execute the method provided in this application embodiment, and the implementation principle is similar. The actions performed by each module in the apparatus of each embodiment of this application correspond to the steps in the method of each embodiment of this application. For detailed functional descriptions of each module of the apparatus, please refer to the descriptions in the corresponding methods shown above, which will not be repeated here.

[0269] This application provides an electronic device including a memory, a processor, and a computer program stored in the memory. The processor executes the computer program to implement the steps of a method for processing SQL statements. Compared with related technologies, this method can achieve the following: by acquiring at least one SQL statement to be processed, since the SQL statement may not meet the requirements for direct materialization processing, the system does not directly process the complete SQL statement. Instead of materializing recommendations, a SQL clause decomposition strategy is adopted. Reference SQL clauses with at least one of the following characteristics—business semantics, reusability, and timeliness—are extracted. The natural language generation and analysis capabilities of the large language model are then utilized to ensure that the recommended virtual tables are highly generalizable and conform to business semantics, reducing redundant definitions. Each reference SQL clause and preset prompts are input into the large language model. The preset prompts indicate at least one of the following criteria: reusability potential, computational cost, semantic rationality, and incremental support. Combined with the natural language description information of each reference SQL clause, a first SQL clause is selected from the reference SQL clauses. This results in at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended and encapsulated as a virtual table. Since the first SQL clause is selected from each reference SQL clause and possesses at least one of the following characteristics—data reusability potential, computational cost, semantic rationality, and incremental support—it lays the foundation for avoiding blind materialization of low-frequency views and is more conducive to controlling storage space consumption.

[0270] In one alternative embodiment, an electronic device is provided, such as Figure 9 As shown, Figure 9 The illustrated electronic device 4000 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 can be used for data interaction between the electronic device and other electronic devices, such as sending and / or receiving data. It should be noted that in practical applications, the transceiver 4004 is not limited to one type, and the structure of the electronic device 4000 does not constitute a limitation on the embodiments of this application.

[0271] Processor 4001 may 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 may also be a combination that implements computational functions, such as including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc.

[0272] Bus 4002 may include a pathway 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, etc. Bus 4002 can be divided into address bus, data bus, control bus, etc. For ease of representation, bus 4002 is represented by only one thick line in the figure, but this does not indicate that there is only one bus or one type of bus.

[0273] The memory 4003 may be ROM (Read Only Memory) or other types of static storage devices capable of storing static information and instructions, RAM (Random Access Memory) or other types of dynamic storage devices capable of storing information and instructions, or EEPROM (Electrically Erasable Programmable Read Only Memory), CD-ROM (Compact Disc Read Only Memory) or other optical disc storage, optical disc storage (including compressed optical discs, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media, other magnetic storage devices, or any other medium capable of carrying or storing computer programs and capable of being read by a computer, without limitation herein.

[0274] The memory 4003 is used to store computer programs that execute the embodiments of this application, and the execution is controlled by the processor 4001. The processor 4001 is used to execute the computer programs stored in the memory 4003 to implement the steps shown in the foregoing method embodiments.

[0275] This application provides a computer-readable storage medium storing a computer program. When the computer program is executed by a processor, it can implement the steps and corresponding content of the aforementioned method embodiments.

[0276] This application also provides a computer program product, including a computer program that, when executed by a processor, can implement the steps and corresponding content of the aforementioned method embodiments.

[0277] The terms "first," "second," "third," "fourth," "1," "2," etc. (if present) in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in a sequence other than that shown in the illustrations or text descriptions.

[0278] It should be understood that although arrows indicate various operation steps in the flowcharts of this application's embodiments, the order in which these steps are implemented is not limited to the order indicated by the arrows. Unless explicitly stated herein, in some implementation scenarios of this application's embodiments, the implementation steps in each flowchart can be executed in other orders as required. Furthermore, some or all steps in each flowchart, based on the actual implementation scenario, may include multiple sub-steps or multiple stages. Some or all of these sub-steps or stages can be executed at the same time, and each sub-step or stage can also be executed at different times. In scenarios where execution times differ, the execution order of these sub-steps or stages can be flexibly configured according to requirements, and this application's embodiments do not limit this.

[0279] The above description is only an optional implementation method for some implementation scenarios of this application. It should be noted that for those skilled in the art, other similar implementation methods based on the technical concept of this application without departing from the technical concept of this application also fall within the protection scope of the embodiments of this application.

Claims

1. A method for processing SQL statements, characterized in that, include: Obtain at least one SQL statement to be processed; Based on at least one of SPJG rules, common expression recognition rules, and time column recognition rules, extract reference SQL clauses from each SQL statement to be processed that have at least one of business semantics, reusability, and time characteristics. Each reference SQL clause and a preset prompt word are input into a large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended to be encapsulated as a virtual table. The preset prompt word is used to indicate the selection of the first SQL clause from each reference SQL clause based on at least one of reusability potential, computational overhead, semantic rationality, and incremental support, combined with the natural language description information of each reference SQL. Display at least one first SQL clause; In response to the selection operation of the target SQL clause in the at least one first SQL clause, each selected target SQL clause is encapsulated as a virtual table; The usage frequency of each virtual table is statistically analyzed, and virtual tables whose usage frequency exceeds a preset threshold are selected as candidate virtual tables. For each candidate virtual table, the materialization priority of the candidate virtual table is determined based on the data size, query response latency, and usage frequency of the candidate virtual table. Candidate virtual tables with a materialization priority higher than a preset threshold are recommended as target virtual tables for generating materialized views. In response to the operation of selecting candidate incremental columns from a target virtual table, the candidate incremental columns are determined to have at least one target incremental column with a mapping relationship in at least one data source table through virtual table model and abstract syntax tree (AST) syntactic analysis; the virtual table model is used to represent the lineage 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 if the target incremental column is not the result of an aggregation operator calculation, then the candidate incremental column is determined to support incremental update, and a control for setting the update granularity is displayed. In response to the setting operation of the control, a judgment condition for generating incremental data is generated based on the set update granularity; The target data source table is periodically scanned based on the judgment criteria, and a new materialized view is generated based on the incremental data scanned.

2. The method according to claim 1, characterized in that, The step of using candidate virtual tables with a materialization priority higher than a preset threshold as recommended target virtual tables for generating materialized views further includes: In response to the operation of selecting candidate incremental columns from the target virtual table, the candidate incremental columns are determined to have at least one target incremental column with a mapping relationship in the generated materialized view through virtual table model and AST syntax analysis. Displays a control for setting the update granularity, and in response to the setting operation of the control, generates a judgment condition for incremental data based on 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 periodically scanned according to the judgment conditions, and a new materialized view is generated based on the incremental data scanned.

3. The method according to claim 1, characterized in that, Each reference SQL clause and preset prompt words are input into the large language model to obtain the first SQL clause of the recommendation encapsulated in the virtual table by the output of the large language model, including: Each reference SQL clause and the first prompt word are input into the large language model to obtain the natural language description information corresponding to each reference SQL clause output by the large language model. Input 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; The preset prompt words include the first prompt word and the second prompt word; The first prompt word is used to indicate that the reference SQL clause is converted into natural language description information. The first prompt word includes the table structure, field comments, business scenario and natural language description information of the sample SQL clause. The second prompt word is used to indicate the selection of the first SQL clause from the various reference SQL clauses. The second prompt word includes a selection rule related to at least one of reusability potential, computational overhead, semantic rationality, and incremental support.

4. The method according to any one of claims 1-3, characterized in that, The time series identification rules include: The SQL statement is converted into a corresponding abstract syntax tree, and the presence of a time column in the SCAN node is identified within the abstract syntax tree. If it exists, then mark the time column; If it does not exist, backtrack from bottom to top to identify at least one of the JOIN node, WHERE node, ORDER BY node, and GROUP BY node to identify a derivable time expression; If, through expression lineage analysis, it is determined that a field in the time expression is directly or indirectly derived from a time field, then the time expression is determined to be a time column. Use the SQL clause containing the time column as the reference SQL clause.

5. The method according to any one of claims 1-3, characterized in that, The SPJG rules include: Transform SQL statements into corresponding abstract syntax trees; Each SELECT and PROJECT node in the abstract syntax tree is taken as the starting point of the traversal. The abstract syntax tree is traversed starting from the first starting point. Starting from the current starting point of the traversal, the JOIN or GROUP BY node is taken as the current ending point of the traversal. The syntax subtree between the current starting point and the current ending point is extracted as a candidate SQL clause. The traversal continues 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, then the candidate SQL clause is used as a reference SQL clause.

6. The method according to any one of claims 1-3, characterized in that, The common expression recognition rules include: If the SQL statement is a common expression, each SQL clause in the SQL statement is determined to be reusable and is used as a reference SQL clause.

7. The method according to any one of claims 1-3, 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 number of executions of multiple historical SQL statements; The recommended priority of the multiple historical SQL statements is determined based on the SQL logs, wherein the recommended priority of each historical SQL statement is proportional to the complexity of the content of the historical SQL statement, the execution time, and the number of times it is executed; Select a preset number of historical SQL statements that are ranked at the top as the SQL statements to be processed.

8. A device for processing SQL statements, characterized in that, include: The statement retrieval module is used to retrieve at least one SQL statement to be processed. The clause filtering module is used to extract reference SQL clauses from each SQL statement to be processed based on at least one of the following: SPJG rules, common expression recognition rules, and time column recognition rules. These clauses possess at least one of the following characteristics: business semantics, reusability, and time-related features. The clause recommendation module is used to input each reference SQL clause and preset prompt words into the large language model to obtain at least one first SQL clause output by the large language model. The first SQL clause refers to the SQL clause recommended and encapsulated as a virtual table. The preset prompt words are used to indicate the selection of the first SQL clause from each reference SQL clause based on at least one of reusability potential, computational overhead, semantic rationality, and incremental support, combined with the natural language description information of each reference SQL. The clause display module is used to display the at least one first SQL clause; The virtual table encapsulation module is configured to encapsulate each selected target SQL clause into a virtual table in response to a selection operation on the at least one first SQL clause; The virtual table filtering module is used to count the usage frequency of each virtual table and select virtual tables whose usage frequency exceeds a preset threshold as candidate virtual tables. The materialization priority determination module is used to determine the materialization priority of each candidate virtual table based on the data size, query response latency, and usage frequency of the candidate virtual table. The virtual table recommendation module is used to recommend candidate virtual tables with a materialization priority higher than a preset threshold as target virtual tables for generating materialized views. The incremental column determination module is used to respond to the operation of selecting candidate incremental columns from the target virtual table, and to determine that the candidate incremental column has at least one target incremental column with a mapping relationship in at least one data source table through virtual table model and abstract syntax tree (AST) syntax analysis; the virtual table model is used to represent the lineage relationship between various virtual tables and between virtual tables and data source tables; A finer-grained determination module is used to determine that the candidate incremental column supports incremental update if the at least one target incremental column is distributed in the same target data source table, or if the target incremental column is not the result of an aggregation operator calculation, and to display a control for setting the update granularity. The condition generation module is used to generate judgment conditions for incremental data based on the set update granularity in response to the setting operation of the control. The view generation module is used to periodically scan the target data source table according to judgment conditions and generate new materialized views based on the incremental data scanned.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory, characterized in that, The processor executes the computer program to implement the SQL statement processing method according to any one of claims 1-7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the SQL statement processing method according to any one of claims 1-7.

11. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the SQL statement processing method according to any one of claims 1-7.

Images