A SQL generation method, system, and application for machine learning inference pipelines

By parsing and fusing the machine learning inference pipeline into a directed acyclic graph, and applying ML feature-driven operator fusion rules and enumeration/greedy strategies to generate SQL, the inefficiency problem in existing technologies is solved, and efficient SQL generation and ML pipeline execution are achieved.

CN123115175APending Publication Date: 2026-11-17EAST CHINA NORMAL UNIV
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202510621496.2
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2025-05-14
Publication Date
2026-11-17

AI Technical Summary

Technical Problem

In existing technologies, the conversion of machine learning inference pipelines into SQL is inefficient, and existing projects support a limited variety of ML operators, making it difficult to meet the business requirements of high throughput and low latency.

Method used

This paper presents a SQL generation method for machine learning inference pipelines. It parses the trained pipeline into a directed acyclic graph, applies ML feature-driven operator fusion rules and enumeration/greedy strategies to generate candidate graphs with the lowest cost, and translates the operators into SQL expressions.

Benefits of technology

It significantly improves the performance of SQL generation, reduces latency in scenarios such as fraud transaction detection, supports SQL generation with higher loads, and can natively execute ML pipelines without modifying the underlying database engine.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN123115175A_ABST
    Figure CN123115175A_ABST
Patent Text Reader

Abstract

This invention discloses a SQL generation method for machine learning inference pipelines. The method includes: Step 1, receiving a trained machine learning pipeline and parsing it into an initial graph in the form of a directed acyclic graph; Step 2, rewriting the initial graph obtained in Step 1 according to fusion rules to generate one or more candidate equivalent graphs; Step 3, selecting the candidate graph with the lowest cost based on an enumeration strategy and / or a greedy strategy; Step 4, translating each machine learning operator in the selected candidate graph into a corresponding SQL expression and combining them into a complete SQL statement. This invention also discloses a system for implementing the above method, which has wide application value.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] This invention belongs to the field of database technology and relates to a SQL generation method, system and application for machine learning inference pipeline. Background Technology

[0002] The rapid development of machine learning has enabled analysts to explore data stored in databases through trained machine learning (ML) inference pipelines, such as for fraud transaction detection. To execute ML pipelines within a database, the database calls ML frameworks (such as scikit-learn) from Python UDFs (User-defined Functions) to execute the pre-trained pipeline. This results in pipeline execution spanning both the database and the ML system, incurring data transformation / copying overhead. Some databases, such as TiDB, don't even support executing Python UDFs. Therefore, existing research has focused on converting ML operators in ML pipelines into pure SQL, enabling native execution within the database system. However, the SQL generated by current techniques is inefficient and fails to meet the high throughput and low latency goals of real-world business applications.

[0003] For example, in Masq, Raven supports converting traditional ML operators into SQL, but it supports a limited number of operators and doesn't fully utilize potential optimization opportunities (such as operator fusion). Applying different operator fusion rules can rewrite a graph into multiple equivalent candidate graphs. Currently, to find the lowest-cost graph among all candidates, the database optimizer uses an enumeration strategy, that is, enumerating all candidate graphs and then selecting the one with the lowest cost. However, the database optimizer only considers the search space generated by factors such as join order, and does not consider the search space generated by ML operator fusion. Existing technologies involved include the following:

[0004] GaussML integrates the open-source openGauss database with the Apache MADlib machine learning extension package. It is adapted, extended and enhanced based on the original MADlib, and has better performance than MADlib running on the PostgreSQL database and supports more commonly used machine learning models.

[0005] PostgresML: An open-source project designed to integrate machine learning capabilities directly into PostgreSQL databases. It supports various classic ML algorithms, allowing users to train and deploy models using simple SQL functions. It achieves efficient inference by reducing data copying.

[0006] MASQ: Transforms ML inference pipelines into standard SQL queries and executes them within the DBMS. It addresses column count limitations through triple representation and optimizes performance using operator fusion.

[0007] Microsoft Raven: Raven integrates relational algebra and ML operators using a unified intermediate representation, supports collaborative optimization such as data predicate pruning model branching and sparse pushdown projection, and supports dynamic selection of the best execution method (ML2SQL or ML2DNN) to fully utilize GPU hardware acceleration.

[0008] However, existing projects either do not support converting traditional ML inference pipelines into SQL, or support a limited variety of ML operators, and the generated SQL is inefficient. Summary of the Invention

[0009] To address the shortcomings of existing technologies, the purpose of this invention is to provide a SQL generation method, system, and application for machine learning inference pipelines.

[0010] This invention provides a SQL generation method for machine learning inference pipelines, the method comprising:

[0011] Step 1: Receive the trained machine learning pipeline and parse it into an initial graph in the form of a directed acyclic graph;

[0012] Step 2: Rewrite the initial graph obtained in Step 1 according to the fusion rules to generate one or more candidate equivalent graphs;

[0013] Step 3: Select the candidate graph with the lowest cost based on an enumeration strategy and / or a greedy strategy;

[0014] Step 4: Translate each machine learning operator in the selected candidate graph into a corresponding SQL expression and combine them into a complete SQL statement.

[0015] Step four also includes outputting the generated SQL statement and executing it in the target database.

[0016] In step one, the machine learning pipeline can be divided into simple pipelines and complex pipelines based on the number of operators;

[0017] In a specific implementation, the simple pipeline generally refers to a pipeline with 10 or fewer operators, and the complex pipeline generally refers to a pipeline with more than 10 operators.

[0018] In step two, an operator fusion method based on ML features is used to generate fusion rules for ML operator combinations through operator classification and ML feature-driven fusion templates. The fusion of operator combinations is then realized according to the fusion rules. The ML features include mathematical operations, conditional judgments, constant outputs, etc.

[0019] The fusion templates include: templates based on mathematical simplification, templates based on conditional reduction, and templates based on constant propagation;

[0020] Each fusion template only fuses combinations of operators that satisfy the conditions of the corresponding template;

[0021] And / or,

[0022] The ML operators are divided according to the abstract operator type and then combined into operator combinations;

[0023] In a specific implementation process, ML operators are divided into 7 abstract operator types, with a total of 49 operator combinations; operator combinations that will not appear in actual applications are also pre-excluded.

[0024] And / or,

[0025] The fusion rules include beneficial rules and rules with uncertain impacts:

[0026] Specifically, the beneficial rule refers to a fusion rule that has a clear positive optimization effect on performance (such as execution time, resource overhead, etc.) after the fusion operation is completed. The uncertain impact rule refers to a rule that, although the fusion operation is logically feasible, the impact on SQL execution performance after fusion depends on factors such as data characteristics, execution environment, or operator parameter configuration, and the optimization effect is uncertain.

[0027] Step three, selecting candidate graphs based on the enumeration strategy, includes:

[0028] Perform in-chain enumeration on each operator chain in the initial graph to exhaust all possible fusion orders and whether to fuse;

[0029] The process iterates through all operator chains, performing inter-chain enumeration to form all possible fusion schemes. Each fusion scheme includes information such as the fusion order, whether to fuse, and fusion rules.

[0030] Each fusion scheme is encapsulated as a drawing in turn, and the rewriter is called to generate an equivalent graph; the cost of all candidate graphs is evaluated and the graph with the lowest cost is returned, thus ending the selection based on enumeration.

[0031] In step three, the selection of candidate graphs based on a greedy strategy includes:

[0032] During the exploration phase, other operator chains are fixed, and multiple equivalence graphs are generated by performing in-chain enumeration only on the target chain.

[0033] During the pruning phase, a cost model is used to evaluate the cost of the equivalent graphs, and the graph with the lowest cost is selected as the graph for the next iteration, until all chains are traversed; finally, the greedy selection ends.

[0034] Different operator chains are independent of each other, and the lowest-cost fusion scheme on each chain is determined independently.

[0035] The cost model evaluates the cost of each operator in the graph and sums them up, then outputs the summation result.

[0036] In one specific embodiment of the present invention, the cost model used includes the following:

[0037] Suppose a graph contains n chains, and each chain contains m operators. Represent the j-th operator on the i-th chain as O(n log n). (i:j) Express its cost as but:

[0038] The cost of an operator The calculation can be approximated as the unit cost (U) of the computational unit contained in the operator multiplied by the number of times the computational unit is executed (N), that is,

[0039]

[0040] The computational unit refers to various operations such as addition, subtraction, multiplication, division, less than or equal to judgment, equal to judgment, IN judgment, and concatenation. U is obtained through analysis. N is obtained by statistically analyzing the data distribution of the training data.

[0041] The cost of a graph is the sum of the costs of all operators, that is:

[0042]

[0043] In step three, the cost of the candidate graph is evaluated, and the cost includes the execution cost corresponding to the candidate graph.

[0044] This invention also provides an SQL generation system for implementing the above-described SQL generation method, comprising: a compiler, an optimizer, and a translator; wherein,

[0045] The compiler is equipped with a graph parser, which receives the trained machine learning pipeline and parses it into an initial graph.

[0046] The optimizer module includes a rule generator, a graph selector, and a rewriter. The rule generator is used to generate fusion rules suitable for different combinations of machine learning operators based on multiple fusion templates. The graph selector is used to generate graphs based on the fusion rules and select candidate equivalent graphs generated by the rewriter. The rewriter is used to rewrite the graph structure according to the graph output by the graph selection strategy to generate equivalent graphs.

[0047] The translator includes an SQL generator module, which translates each machine learning operator in the target graph into a corresponding SQL expression and combines them to generate a complete SQL statement.

[0048] This invention also provides the application of the above-mentioned SQL generation method or SQL generation system in automobile price prediction, fraud transaction detection, equipment failure detection, traffic flow prediction, etc.

[0049] The beneficial effects of this invention include: This invention delves into the characteristics of all abstract operators and proposes three feature-driven templates. This invention utilizes a rule generator and three templates to generate fusion rules, ensuring that the generated rule set can cover all possible operator combinations in practice. Under FTBench benchmark load testing, compared with the industry's most advanced SQL generation technology, the fusion rules of this invention can bring up to two orders of magnitude performance improvement, significantly reducing the latency of executing machine learning pipelines in scenarios such as fraud transaction detection. Compared to MASQ, this invention can further reduce the execution time of generated SQL, such as... Figure 4 As shown. Therefore, this invention provides a universal fusion method for commonly used operator combinations in practice, significantly improving the performance of SQL generation.

[0050] This invention proposes two graph selection strategies—enumeration-based and greedy-based—for the initial graph obtained from parsing ML pipelines, applicable to different scenarios. For simple ML pipelines, the enumeration strategy rewrites the initial graph using combination and fusion rules, generating candidate graphs with varying costs, and then selects the graph with the lowest cost. For complex ML pipelines, the greedy strategy also rewrites the initial graph using combination and fusion rules, but it prunes the search space by leveraging the independence between ML operators, fundamentally solving the combinatorial explosion problem faced by the enumeration strategy. Compared to conservatively applying only beneficial rules or aggressively applying all rules, the enumeration-based and greedy-based graph selection strategies can find graphs with lower costs. Specifically, the greedy strategy can reduce the graph search time, which originally took more than a day, to less than 10 minutes. Therefore, this invention can select the lowest-cost graph for ML pipelines within an acceptable timeframe.

[0051] Furthermore, since MASQ supports a limited number of ML operators, it cannot successfully convert all workloads into SQL. This invention supports SQL generation for all workloads, as shown in Table 1. Compared to executing ML pipelines via Python UDFs, this invention enables TiDB to natively execute ML pipelines without modifying the underlying database engine, such as TiDB. Attached Figure Description

[0052] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0053] Figure 1 This is the system architecture diagram of the present invention.

[0054] Figure 2 This is an execution flowchart of the graph selection strategy based on enumeration of the present invention.

[0055] Figure 3 This is the execution flowchart of the greedy graph selection strategy of this invention.

[0056] Figure 4 This is a graph showing the performance improvement of the present invention compared to the MASQ system.

[0057] Figure 5 This is a schematic diagram of the craftsman package required in the implementation of this invention.

[0058] Figure 6 This is a schematic diagram of the code for defining an ML pipeline for predicting car prices in a specific embodiment of the present invention.

[0059] Figure 7 This is a schematic diagram of the code for training and saving a machine learning pipeline in a specific embodiment of the present invention.

[0060] Figure 8 This is a schematic diagram of the code for generating SQL in a specific embodiment of the present invention.

[0061] Figure 9 This is a schematic diagram of the SQL code corresponding to the ML pipeline for car price prediction in a specific embodiment of the present invention.

[0062] Figure 10 This is a schematic diagram of the code for creating a car data table in a database and importing car data in a specific embodiment of the present invention.

[0063] Figure 11 This is a schematic diagram of the SQL code corresponding to the price prediction ML pipeline executed in DuckDB in another specific embodiment of the present invention.

[0064] Figure 12 This is a schematic diagram of the code for defining an ML pipeline for fraud transaction detection in another specific embodiment of the present invention.

[0065] Figure 13This is a schematic diagram of the SQL generation code for the fraud transaction detection ML pipeline in another specific embodiment of the present invention.

[0066] Figure 14 This is a schematic diagram of the SQL code corresponding to the fraud transaction detection ML pipeline in another specific embodiment of the present invention.

[0067] Figure 15 This is a schematic diagram of the code for creating a transaction data table in the database and importing transaction data in another specific embodiment of the present invention.

[0068] Figure 16 This is a schematic diagram of the SQL code corresponding to the fraud transaction detection ML pipeline executed in DuckDB in another specific embodiment of the present invention. Detailed Implementation

[0069] The present invention will be further described in detail below with reference to the specific embodiments and accompanying drawings. Except for the contents specifically mentioned below, the processes, conditions, and experimental methods for implementing the present invention are all common knowledge and general knowledge in the art, and the present invention does not have any particular limitations.

[0070] The following are explanations of some of the terms used in this invention:

[0071] Machine learning (ML): In this invention, it specifically refers to traditional machine learning, which is an AI technology that automatically learns patterns from structured data through statistical methods and algorithms (such as decision trees). Its advantages lie in its lightweight nature, high computational efficiency, and interpretable models.

[0072] A trained machine learning operator, or simply "machine learning operator" or "operator," is used to perform specific transformations on input data. A preprocessing operation (such as normalization) and an inference model (such as linear regression) can both be considered a machine learning operator. The parameters in the operator are known constants that have been fitted during the training phase.

[0073] Machine learning inference pipeline: abbreviated as "machine learning pipeline", is a series of ordered machine learning operators used to calculate prediction results for input data.

[0074] Database expressions: Database expressions are code snippets composed of constants, column names, operators, etc., used to calculate, compare, or manipulate data, including arithmetic expressions, conditional expressions, etc.

[0075] User-defined functions (UDFs): Custom data analysis functions implemented by users using external programming languages, such as Python. Users can call UDFs in database query languages ​​to complete data analysis tasks.

[0076] Directed Acyclic Graph (DAG): A well-trained machine learning inference pipeline can be parsed into a graph structure. This graph consists of several nodes and edges between the nodes. Nodes represent machine learning operators, and edges represent data dependencies between operators.

[0077] Operator chain: abbreviated as "chain", is a chain of preprocessors on the same field and the final model inference.

[0078] Fusion rule: Combine two consecutive operators within a chain into one operator.

[0079] Drawings: These contain information such as the merging order, merging rules, and whether to merge flags, and are used to guide the rewriter on how to rewrite the graph.

[0080] Graph rewriting: The graph rewriter rewrites the graph based on information such as the order and whether it is merged, generating a graph that is semantically equivalent to the original graph.

[0081] Equivalent graph: A graph that is generated by rewriting an original graph and is semantically equivalent to the original graph.

[0082] Candidate diagrams: Multiple equivalent diagrams are generated by rewriting a diagram based on different drawings.

[0083] Initial graph: also known as the original graph, is a directed acyclic graph obtained through the analytical pipeline.

[0084] This invention provides a set of ML feature-driven operator fusion rules, which aims to provide a general fusion method for common ML operator combinations in practice.

[0085] This invention also provides a cost-based graph selection strategy, aiming to efficiently select the lowest-cost graph for ML pipelines. Based on the complexity of the pipeline, this invention proposes an enumeration-based graph selection strategy and a greedy graph selection strategy, respectively for simple and complex pipelines. The enumeration-based strategy is inspired by plan enumeration in database optimizers, which generates candidate graphs by enumerating the connection order and the implementation of relation operators. The difference in this invention's strategy is that it generates candidate graphs by combining different fusion rules. To address the combinatorial explosion problem faced by the enumeration-based strategy in complex pipelines, a greedy strategy is chosen to improve graph selection efficiency in complex pipeline scenarios by leveraging the independence between ML operators.

[0086] Existing technologies such as MASQ support fusion for ML operators, but only consider the fusion of a few specific operator combinations. They do not provide a general fusion method for commonly used ML operator combinations in practice. Therefore, this invention aims to provide a general and efficient fusion method for operator combinations that exist in practice.

[0087] Combining different fusion rules generates candidate graphs with varying costs. To find the lowest-cost graph among all candidates, the database optimizer enumerates all candidate graphs and then selects the one with the lowest cost. However, the database optimizer primarily generates candidate graphs by enumerating join orders and implementations of relational operators. These do not support fusion between ML operators, thus failing to select efficient graphs for ML pipelines. Therefore, this invention aims to propose a graph selection strategy for ML pipelines to select efficient graphs.

[0088] The first technical problem this invention addresses is how to design fusion rules for a wide variety of ML operators. Existing fusion techniques based on loop fusion, such as Weld and TVM, aim to reduce materialization or kernel startup overhead through fusion. However, these techniques neglect the semantics of ML operators, and simply using such techniques to fuse ML operators can lead to missed optimization opportunities and even negative consequences. Therefore, this invention proposes an operator fusion driven by ML features (including mathematical operations, conditional judgments, and constant outputs). By using operator classification and ML feature-driven templates, fusion rules are generated for common combinations of ML operators in practice, achieving the goal of efficiently fusing existing operator combinations.

[0089] The second technical problem this invention aims to solve is how to select an efficient graph within an acceptable timeframe. Existing enumeration-based techniques enumerate all candidate graphs and select the one with the lowest cost. However, when the ML pipeline is complex, the number of candidate graphs can reach tens of millions. Existing techniques cannot select an efficient graph within an acceptable timeframe. Therefore, this invention proposes an enumeration-based graph selection strategy and a greedy graph selection strategy based on pipeline complexity. By extending existing enumeration techniques and utilizing the independence between ML operators, the invention achieves the goal of selecting an efficient graph within an acceptable timeframe.

[0090] This invention provides a SQL generation method or system for ML inference pipelines, aiming to improve the execution efficiency of generated SQL by utilizing operator fusion technology. Based on the characteristics of common ML operators, this invention proposes a fusion template, which generates a set of operator fusion rules to support efficient fusion of common ML operators. Furthermore, rewriting the graph using different rules generates one or more candidate graphs with unequal costs but semantic equivalence, and the SQL generated by these graphs has different execution times. To find the graph with the lowest cost, this invention employs an enumeration-based and / or greedy graph selection strategy to find the graph with the lowest cost within an acceptable timeframe. The SQL generated by this method is compatible with mainstream databases such as DuckDB, ClickHouse, and TiDB, effectively improving the efficiency of ML inference within these databases.

[0091] like Figure 1As shown, the SQL generation system of this invention consists of three parts: a compiler, an optimizer, and a translator. The rule generator in the optimizer is responsible for generating a set of fusion rules for ML operators. For a joblib format ML pipeline, the compiler first parses it into an initial graph. Then, the graph selector uses a selection strategy to find a valid graph. Specifically, the graph selector explores the fusion order between operators and the fusion rules to be applied, encapsulating this information in a graph structure. It then passes the graph and the corresponding graph to the rewriter, which rewrites the graph into equivalent graphs based on the graph. The selector chooses one from these equivalent graphs and passes it to the translator. Finally, the translator translates each ML operator in the selected graph into a corresponding SQL fragment and combines them into a complete SQL statement. This SQL statement can be executed in the target database.

[0092] Rule Generator and Fusion Rules: The rule generator contains three fusion templates: a template based on mathematical simplification, a template based on conditional reduction, and a template based on constant propagation. Each template has a condition; when a combination of operators satisfies the condition of a template, they can be fused using that template. Specifically, when the first operator contains only mathematical operations and the second operator also contains mathematical operations, these two operators will be fused using the template based on mathematical simplification; when the first operator contains case expressions and the second operator does not contain arithmetic operations, these two operators will be fused using the template based on conditional reduction; when the output space of the first operator consists of constants and the second operator does not contain multiple case expressions, these two operators will be fused using the template based on constant propagation. Based on the type of input and output characteristics of the operators and the integration method of the computational units, this invention classifies commonly used ML operators into seven abstract operator types, including Con-n-Con, Con-s-Con, Con-s-Cat, Cat-s-Cat, Cat-J-Vec, *-s-*, and Vec-N-*. The meanings of each symbol are as follows:

[0093] Con indicates that the type of input / output feature is continuous;

[0094] Cat indicates that the type of input / output feature is discrete;

[0095] Vec: Indicates that the data is a vector composed of multiple Con or Cat;

[0096] *: Indicates that the data type to be processed can be Con or Cat;

[0097] n: indicates that the operator does not contain a case expression;

[0098] s: indicates that the computational unit is integrated in a single case expression;

[0099] J: Indicates that the computation unit is integrated in multiple case expressions, which are parallel to each other;

[0100] N: Indicates that the computation unit is integrated in multiple case expressions, which are nested.

[0101] There are 49 possible operator combinations by pairwise pairwise combinations. After excluding combinations that would not occur in practical applications, the rule generator sequentially determines whether each combination satisfies the conditions of a certain template and generates corresponding fusion rules (37 in total) based on the template. According to the impact of applying the rules on the graph, this invention divides the fusion rules into beneficial rules and rules with uncertain impact.

[0102] Graph selection strategies include enumeration-based and greedy-based strategies.

[0103] Enumeration strategies include inter-chain enumeration and intra-chain enumeration. For example... Figure 2 As shown, the enumeration strategy performs intra-chain enumeration on the initial graph to rewrite its first chain, exhausting all possible operator association sequences. For each association sequence, the enumeration strategy determines whether to apply a rule based on the rules in the sequence matching rule set and marks it with an application flag. Then, the enumeration strategy traverses all operator chains for inter-chain enumeration, combining them to form all possible fusion schemes. Subsequently, the enumeration strategy encapsulates the fusion scheme (including fusion order, fusion flag, and fusion rules) into a graph and calls the rewriter to generate an equivalent graph. Finally, it evaluates the cost of all candidate graphs and returns the graph with the lowest cost.

[0104] The greedy strategy consists of two phases: exploration and pruning. The pruning operation leverages the independence between chains; that is, the computations between chains are independent and do not affect each other. Therefore, it is only necessary to independently determine the lowest-cost fusion scheme for each chain. Figure 3 As shown, during the exploration phase, other chains remain fixed, and all equivalent graphs are generated only by performing in-chain enumeration on a given chain. Then, during the pruning phase, a greedy strategy uses a cost model to estimate the cost of each equivalent graph and selects the graph with the lowest cost as the graph for the next iteration. The cost model evaluates the cost of each operator in the graph using statistical data and some measured parameters, then sums the costs of all operators and uses the sum as the output of the cost model, i.e., the cost of the entire graph. The greedy strategy leverages the optimal substructure property and the greedy selection property inherent in solving the graph cost problem, ensuring that the selected graph is close to the global optimum.

[0105] Figure 1 This is the system architecture diagram of the present invention. Figure 1In this context, kbd is the label of the discretization operator in the pipeline, and dt is the label of the decision tree classifier in the pipeline.

[0106] Figure 2 This is a flowchart illustrating the execution of the enumeration-based graph selection strategy of this invention, describing the core process of the strategy. Intra-chain enumeration exhaustively examines all possible operator association sequences within a chain. For each association sequence, the enumeration strategy determines whether to apply a rule based on the rules in the sequence matching rule set and marks it with an application flag. Then, the enumeration strategy traverses all operator chains for inter-chain enumeration, combining them to form all possible fusion schemes. Each fusion scheme includes information such as the fusion order, a fusion flag, and fusion rules. The enumeration strategy encapsulates the fusion scheme containing the fusion order, fusion flag, and fusion rules into a graph and calls a rewriter to generate an equivalent graph. The enumeration strategy evaluates the cost of all candidate graphs and returns the graph with the lowest cost.

[0107] Figure 3 This is an execution flowchart of the greedy graph selection strategy of the present invention, which describes the core process of the strategy; wherein, the in-chain enumeration process is the same as the in-chain enumeration process in the enumeration-based graph selection strategy.

[0108] The method of this invention supports SQL generation for all workloads, as shown in Table 1. Compared to executing ML pipelines via Python UDFs, this invention enables TiDB to natively execute ML pipelines without modifying the underlying database engine, such as TiDB.

[0109] Table 1. Load Support of MASQ and the Method of the Invention

[0110]

[0111]

[0112] Dataset source:

[0113] Credit card:

[0114] https: / / www.kaggle.com / datasets / mlg-ulb / creditcardfraud

[0115] Adult:

[0116] https: / / archive.ics.uci.edu / ml / machine-learning-databases / adult / adult.data

[0117] Crypto:

[0118] https: / / www.kaggle.com / competitions / g-research-crypto-forecasting / data

[0119] Price:

[0120] https: / / www.kaggle.com / datasets / avikasliwal / used-cars-price-prediction

[0121] Cat:

[0122] https: / / www.kaggle.com / c / cat-in-the-dat / data

[0123] Criteo:

[0124] https: / / ailab.criteo.com / ressources /

[0125] Figure 4 This is a performance improvement graph comparing the present invention with the MASQ system. In the graph, under loads 3 and 6, the MASQ system has a shorter SQL generation execution time than the unoptimized solution, while the method in this invention has an even shorter execution time than MASQ, demonstrating a significant efficiency improvement.

[0126] In load 3, the execution time of the query generated without any optimization is 15.90 seconds, the execution time of the query generated with MASQ optimization is 7.16 seconds, and the execution time of the query generated by this invention is 4.32 seconds.

[0127] In load 6, the execution time of the query generated without any optimization is 91.68 seconds, the execution time of the query generated with MASQ optimization is 67.84 seconds, and the execution time of the query generated by this invention is 20.66 seconds.

[0128] Example 1

[0129] Car Price Prediction: By training an ML inference pipeline, we analyze car data in the database that has no price or missing prices to predict car prices.

[0130] The craftsman package is used in this embodiment.

[0131] 1. Import the Python packages used in this example.

[0132] Before using trainer_helper, users need to import packages that begin with "craftsman", such as... Figure 5 As shown, the Python package starting with "craftsman" is also where the source code is located in this embodiment.

[0133] 2. Use CraftsmanColumnTransformer to build an ML pipeline for car price prediction.

[0134] When building a machine learning pipeline using the trainer_helper toolkit, the steps should be defined as shown in the diagram below. Preprocessing operators for different features can be organized within the same CraftsmanColumnTransformer; however, different preprocessing operators for the same feature need to be placed in two separate steps, each using a different CraftsmanColumnTransformer. The CraftsmanColumnTransformer also has an additional input parameter, `input_data`, which is the training data transformed by the previous steps. Finally, all steps are organized using a Pipeline, and the final defined Pipeline is the constructed machine learning pipeline.

[0135] 3. Use wrapper classes and native classes to define preprocessing operators.

[0136] To obtain training statistics for preprocessing operators and the model, as well as the names of features captured in the final machine learning model, some machine learning operators in Scikit-learn are wrapped. Therefore, when constructing the corresponding preprocessing operators, the wrapper classes provided in the trainer_helper toolbox should be used. Preprocessing operators declared in Table 2 require the use of those with the Craftsman prefix; other preprocessing operators can use the native preprocessing operators in Scikit-learn.

[0137] Table 2 shows the preprocessing operators used with wrapper classes and native classes.

[0138]

[0139]

[0140] The code for the ML pipeline defined in this embodiment is as follows: Figure 6 As shown.

[0141] After training the machine learning pipeline, save it in Joblib file format, such as... Figure 7 As shown.

[0142] 4. Use the transformer_manager tool to generate SQL, such as... Figure 8 As shown. The generated SQL is as follows. Figure 9 As shown.

[0143] Parameters for using the generate_query tool:

[0144] ●pre_sql: Other SQL statements, such as 'EXPLAIN ANALYZE'

[0145] ●group: Graph selection algorithm, 'enum' (i.e., an enumeration-based algorithm) / 'prune' (i.e., a greedy algorithm)

[0146] ●max_process_num: The degree of parallelism for enumeration-based algorithms.

[0147] ●pipeline_save_path: The file path of the trained machine pipeline.

[0148] 5. Run machine learning pipelines in the database

[0149] Create data tables in the database and import datasets according to the table creation statements of different databases. Figure 10 Take DuckDB as an example.

[0150] The generated SQL can be directly executed by the database. Taking DuckDB as an example, the statement to execute the generated SQL is as follows: Figure 11 As shown.

[0151] Example 2

[0152] Fraudulent transaction detection: By training an ML inference pipeline, the system analyzes transaction data in the database to identify whether a transaction is fraudulent.

[0153] 1. Import the Python packages used in this embodiment.

[0154] Same as in Example 1, such as Figure 5 As shown.

[0155] 2. Use CraftsmanColumnTransformer to build an ML pipeline for fraud transaction detection.

[0156] 3. Use wrapper classes and native classes to define preprocessing operators. The code for the ML pipeline defined in this embodiment is as follows: Figure 12 As shown. After training the machine learning pipeline, save it in Joblib file format.

[0157] 4. Use the transformer_manager tool to generate SQL, such as... Figure 13 As shown. The generated SQL is as follows. Figure 14 As shown.

[0158] 5. Run a machine learning pipeline in the database. Create tables in the database and import datasets according to the table creation statements for different databases. Figure 15 This demonstrates table creation and data import statements using DuckDB as an example. The generated SQL can be directly executed by the database. For example, the statement to execute the generated SQL using DuckDB is as follows: Figure 16 As shown.

[0159] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code. The solutions in the embodiments of this application can be implemented in various computer languages, such as the object-oriented programming language Java and the interpreted scripting language JavaScript.

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

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

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

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

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

[0165] The scope of protection of this invention is not limited to the above embodiments. Any variations and advantages that can be conceived by those skilled in the art without departing from the spirit and scope of this invention are included in this invention and are protected by the appended claims.

Claims

1. A SQL generation method for machine learning inference pipelines, characterized in that, The method includes: Step 1: Receive the trained machine learning pipeline and parse it into an initial graph in the form of a directed acyclic graph; Step 2: Rewrite the initial graph obtained in Step 1 according to the fusion rules to generate one or more candidate equivalent graphs; Step 3: Select the candidate graph with the lowest cost based on an enumeration strategy and / or a greedy strategy; Step 4: Translate each machine learning operator in the selected candidate graph into a corresponding SQL expression and combine them into a complete SQL statement.

2. The method as described in claim 1, characterized in that, Step four also includes outputting the generated SQL statement and executing it in the target database.

3. The method as described in claim 1, characterized in that, The machine learning pipeline is categorized into simple pipelines and complex pipelines based on the number of operators. And / or, The simple pipeline refers to a pipeline with 10 or fewer operators, while the complex pipeline refers to a pipeline with more than 10 operators.

4. The method as described in claim 1, characterized in that, In step two, an operator fusion method based on ML features is used to generate fusion rules for ML operator combinations through operator classification and ML feature-driven fusion templates, and the fusion of operator combinations is realized according to the fusion rules. The ML features include mathematical operations, conditional judgments, and constant outputs.

5. The method as described in claim 4, characterized in that, The fusion templates include: templates based on mathematical simplification, templates based on conditional reduction, and templates based on constant propagation; Each fusion template only fuses combinations of operators that satisfy the conditions of the corresponding template; And / or, The ML operators are divided according to the abstract operator type and then combined into operator combinations; And / or, During the generation of fusion rules, operator combinations that would not appear in practical applications are pre-excluded. And / or, The fusion rules include beneficial rules and rules with uncertain impacts.

6. The method as described in claim 1, characterized in that, Step three, selecting candidate graphs based on the enumeration strategy, includes: Perform in-chain enumeration on each operator chain in the initial graph to exhaust all possible fusion orders and whether to fuse; Perform inter-chain enumeration on all operator chains and combine them to form all possible fusion schemes; Each fusion scheme is encapsulated as a drawing, and a rewriter is called to generate an equivalent graph; the cost of all candidate graphs is evaluated and the graph with the lowest cost is returned.

7. The method as described in claim 1, characterized in that, In step three, the selection of candidate graphs based on a greedy strategy includes: During the exploration phase, other operator chains are fixed, and multiple equivalence graphs are generated by performing in-chain enumeration only on the target chain. During the pruning phase, a cost model is used to evaluate the cost of the equivalent graph, and the graph with the lowest cost is selected as the next iteration graph until all chains are traversed. The cost refers to the execution cost corresponding to the candidate graph; And / or, Different operator chains are independent of each other, and the lowest-cost fusion scheme on each chain is determined independently. And / or, The cost model evaluates the cost of each operator in the graph and sums them up, then outputs the summation result.

8. The method as described in claim 7, characterized in that, Operator cost is calculated using the following formula: in, Let U represent the cost of the j-th operator on the i-th chain, U represent the unit cost of the computational unit contained in the operator, and N represent the number of times the computational unit is executed; The cost of a graph is the sum of the costs of all operators, expressed as follows:

9. A SQL generation system implementing the method of any one of claims 1-8, characterized in that, include: Compiler, optimizer, translator; among them, The compiler is equipped with a graph parser, which receives the trained machine learning pipeline and parses it into an initial graph. The optimizer module includes a rule generator, a graph selector, and a rewriter. The rule generator is used to generate fusion rules suitable for different combinations of machine learning operators based on multiple fusion templates. The graph selector is used to generate graphs based on the fusion rules and select candidate equivalent graphs generated by the rewriter. The rewriter is used to rewrite the graph structure according to the graph output by the graph selection strategy to generate equivalent graphs. The translator includes an SQL generator module, which translates each machine learning operator in the target graph into a corresponding SQL expression and combines them to generate a complete SQL statement.

10. The SQL generation method as described in any one of claims 1-8, or the SQL generation system as described in claim 9, is applied to automobile price prediction, fraudulent transaction detection, equipment failure detection, and traffic flow prediction.