CN116431556A - SQL routing method, device and equipment based on self-adaptive execution engine - Google Patents

SQL routing method, device and equipment based on self-adaptive execution engine Download PDF

Info

Publication number
CN116431556A
CN116431556A CN202310417338.6A CN202310417338A CN116431556A CN 116431556 A CN116431556 A CN 116431556A CN 202310417338 A CN202310417338 A CN 202310417338A CN 116431556 A CN116431556 A CN 116431556A
Authority
CN
China
Prior art keywords
sql
execution engine
data
execution
sql statement
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202310417338.6A
Other languages
Chinese (zh)
Inventor
龙丽明
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Shanghai Zhongtongji Network Technology Co Ltd
Original Assignee
Shanghai Zhongtongji Network Technology Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Shanghai Zhongtongji Network Technology Co Ltd filed Critical Shanghai Zhongtongji Network Technology Co Ltd
Priority to CN202310417338.6A priority Critical patent/CN116431556A/en
Publication of CN116431556A publication Critical patent/CN116431556A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F15/00—Digital computers in general; Data processing equipment in general
    • G06F15/16—Combinations of two or more digital computers each having at least an arithmetic unit, a program unit and a register, e.g. for a simultaneous processing of several programs
    • G06F15/163—Interprocessor communication
    • G06F15/173—Interprocessor communication using an interconnection network, e.g. matrix, shuffle, pyramid, star, snowflake
    • G06F15/17306—Intercommunication techniques
    • G06F15/17312—Routing techniques specific to parallel machines, e.g. wormhole, store and forward, shortest path problem congestion
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Abstract

The invention relates to an SQL routing method, device and equipment based on a self-adaptive execution engine, and belongs to the technical field of big data application. The invention obtains SQL sentences and user parameters; judging whether a designated execution engine exists according to the user parameters: if the specified execution engine does not exist, matching the SQL sentence with a preset system rule; if the matching is successful, a first target execution engine is obtained, and the SQL sentence is routed to the first target execution engine; if the matching fails, according to the SQL statement and the historical execution conditions, carrying out matching prediction on the SQL statement and each candidate execution engine to obtain a second target execution engine, and routing the SQL statement to the second target execution engine. The SQL sentence is intelligently routed to the optimal execution engine by respectively matching the SQL sentence with the user parameters, the preset system rules and the historical execution conditions, so that the running cost is reduced, and the resources are saved.

Description

SQL routing method, device and equipment based on self-adaptive execution engine
Technical Field
The invention belongs to the technical field of big data application, and particularly relates to an SQL routing method, device and equipment based on a self-adaptive execution engine.
Background
SQL (Structured Query Language ) is the most common data processing language, and can complete operations such as adding, deleting, modifying and searching the database. At present, the large data distribution type characteristic enables the computing engines of Hadoop (distributed computing and storage framework) ecologically owned by the storage and computing separation to be more diversified, and the advantages and disadvantages brought by different computing engines are also particularly obvious, for example, mapReduce can be operated on a data set with the largest scale in the cheapest way, spark can make tasks which run on Hive before faster by means of memory, prest is limited in data scale of processing due to complete memory processing, but the processing speed is faster.
Because the cost (time, resources) of running the same SQL on different execution engines is different, as in prest, it takes only 1 second to execute the SQL and several minutes in Hive to succeed, and the available resources of each execution engine are different at the same time. Thus, there is a need for a solution that can route SQL to the appropriate execution engine for revenue at the lowest cost.
Currently, it is basically determined which execution engine is selected by relying on the existing experience of the developer, or by setting a default execution engine, the execution engine will be degraded to other execution engines for execution after the execution fails. However, although the degradation can succeed in this scheme, the first failure takes a lot of time to complete, and the time for processing the data is increased, so that the downstream dependent job cannot be completed within a specified time, and a lot of resources are consumed. Thus, existing approaches to routing SQL to the best execution engine are less intelligent.
Disclosure of Invention
Therefore, the invention provides the SQL routing method, the device and the equipment based on the self-adaptive execution engine, which are helpful for solving the problem of low intelligent degree in routing SQL to the optimal execution engine.
In order to achieve the above purpose, the invention adopts the following technical scheme:
in a first aspect, the present invention provides an SQL routing method based on an adaptive execution engine, including:
acquiring SQL sentences and user parameters;
judging whether a designated execution engine exists according to the user parameters:
if the appointed execution engine does not exist, matching the SQL sentence with a preset system rule;
if the matching is successful, a first target execution engine is obtained, and the SQL statement is routed to the first target execution engine;
and if the matching fails, carrying out matching prediction on the SQL statement and each candidate execution engine according to the SQL statement and the historical execution condition to obtain a second target execution engine, and routing the SQL statement to the second target execution engine.
Further, matching the SQL statement with a preset system rule includes:
analyzing the data calculated by the SQL sentence to obtain corresponding analysis data; the analysis data comprises data size, file number and complexity labels of data calculated by SQL sentences;
and matching the analysis data with the preset system rule.
Further, analyzing the data calculated by the SQL sentence to obtain corresponding analysis data, including:
performing partition cutting and column cutting on the data calculated by the SQL sentence to obtain the data size and the number of files of the data calculated by the SQL sentence;
and carrying out complexity analysis on the data calculated by the SQL sentence to obtain a complexity label of the data calculated by the SQL sentence.
Further, according to the SQL statement and the historical execution situation, performing matching prediction on the SQL statement and each candidate execution engine to obtain a second target execution engine, including:
according to the data size, the number of files and the complexity labels of the data calculated by the SQL sentence, carrying out running cost prediction on each candidate execution engine by combining the historical execution conditions, and selecting the candidate execution engine with the lowest running cost as the second target execution engine;
the historical execution condition comprises preset time period data, contemporaneous data and cyclic period data of the current SQL sentence and similar SQL sentences.
Further, the method further comprises:
counting historical submitted data, analysis data, routing data and execution data of the current SQL sentence and the similar SQL sentences to obtain corresponding statistical data;
and analyzing the statistical data through an AI linear regression method to obtain the running cost of the SQL sentence under each execution engine and the selected running times.
Further, before routing the SQL statement to the first target execution engine, the method further comprises:
judging whether the first target execution engine is consistent with a default execution engine or not;
if the SQL sentences are inconsistent, converting the SQL sentences to obtain converted SQL sentences;
and routing the SQL statement to the first target execution engine, comprising:
the converted SQL statement is routed to the first target execution engine.
Further, before routing the SQL statement to the first target execution engine, the method further comprises:
registering the SQL statement to a monitoring service, and regularly monitoring the execution condition of the SQL statement.
Further, the method further comprises:
if the SQL statement fails to be executed, performing degradation execution on the SQL statement on other candidate execution engines;
if the SQL statement has execution timeout, restarting a shadow job on other candidate execution engines.
In a second aspect, the present invention provides an SQL routing device based on an adaptive execution engine, comprising:
the SQL acquisition module is used for acquiring SQL sentences and user parameters;
the RBO Manager module is used for judging whether a designated execution engine exists or not according to the user parameters; if the appointed execution engine does not exist, matching the SQL sentence with a preset system rule; if the matching is successful, a first target execution engine is obtained, and the SQL statement is routed to the first target execution engine;
and the CBOManager module is used for carrying out matching prediction on the SQL statement and each candidate execution engine according to the SQL statement and the historical execution condition if the matching fails, obtaining a second target execution engine, and routing the SQL statement to the second target execution engine.
In a third aspect, the present invention provides an SQL routing device based on an adaptive execution engine, comprising:
one or more memories having executable programs stored thereon;
one or more processors configured to execute the executable program in the memory to implement the steps of any of the methods described above.
The invention adopts the technical proposal and has at least the following beneficial effects:
the invention obtains SQL sentences and user parameters; judging whether a designated execution engine exists according to the user parameters: if the specified execution engine does not exist, matching the SQL sentence with a preset system rule; if the matching is successful, a first target execution engine is obtained, and the SQL sentence is routed to the first target execution engine; if the matching fails, according to the SQL statement and the historical execution conditions, carrying out matching prediction on the SQL statement and each candidate execution engine to obtain a second target execution engine, and routing the SQL statement to the second target execution engine. The SQL sentence is intelligently routed to the optimal execution engine by respectively matching the SQL sentence with the user parameters, the preset system rules and the historical execution conditions, so that the running cost is reduced, and the resources are saved.
It is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the invention as claimed.
Drawings
In order to more clearly illustrate the embodiments of the invention or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a flow chart of an SQL routing method based on an adaptive execution engine according to an embodiment of the invention;
FIG. 2 is a block diagram of an SQL routing device based on an adaptive execution engine according to an embodiment of the present invention;
FIG. 3 is a block diagram of an SQL routing device based on an adaptive execution engine according to an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the technical solutions of the present invention will be described in detail below. It will be apparent that the described embodiments are only some, but not all, embodiments of the invention. All other embodiments, based on the examples herein, which are within the scope of the invention as defined by the claims, will be within the scope of the invention as defined by the claims.
Referring to fig. 1, fig. 1 is a flowchart of an SQL routing method based on an adaptive execution engine according to an embodiment of the present invention, and as shown in fig. 1, the method includes the following steps:
step S11, acquiring SQL sentences and user parameters;
step S12, judging whether a designated execution engine exists according to the user parameters:
step S13, if the appointed execution engine does not exist, matching the SQL sentence with a preset system rule;
step S14, if the matching is successful, a first target execution engine is obtained, and the SQL sentence is routed to the first target execution engine;
and step S15, if the matching fails, carrying out matching prediction on the SQL statement and each candidate execution engine according to the SQL statement and the historical execution conditions to obtain a second target execution engine, and routing the SQL statement to the second target execution engine.
In the present embodiment, the execution body is an electronic device such as a server.
It should be noted that, the specified execution engine is an execution engine preset by a user according to the SQL statement, some parameters are configured in advance in the system, and some SQL statements are specified to be executed by using the set execution engine. For example, specifying that certain SQL statements are to be run on Spark execution engines does not allow the system to route them onto Presto execution engines. Because these SQL sentences have special grammar, other execution engines are not necessarily compatible, so that the user can configure some parameters according to actual conditions to set the designated execution engines. In this embodiment, the user parameter is a parameter that is preconfigured by the user according to actual needs, and this embodiment is not particularly limited.
It should be noted that, under the condition that the user does not specify the execution engine, the system performs the SQL routing of the self-adaptive execution engine according to the preset system rule, and matches the SQL statement with the preset system rule. When matching to the appropriate execution engine, the SQL statement is routed to run on this execution engine. At this time, the appropriate execution engine obtained by the matching is the first target execution engine described above. In this embodiment, the preset system rule is a preset matching rule according to the characteristics of each SQL statement and each execution engine in practical application, and the specific content of the matching rule is not limited in this embodiment.
If the proper execution engine is not matched according to the preset system rule, the SQL sentence and each candidate execution engine are subjected to matching prediction according to the SQL sentence and the historical execution condition, the SQL sentence is matched to the proper execution engine, namely the second target execution engine, and the SQL sentence is routed to the execution engine for operation.
It is to be understood that the invention obtains SQL sentences and user parameters; judging whether a designated execution engine exists according to the user parameters: if the specified execution engine does not exist, matching the SQL sentence with a preset system rule; if the matching is successful, a first target execution engine is obtained, and the SQL sentence is routed to the first target execution engine; if the matching fails, according to the SQL statement and the historical execution conditions, carrying out matching prediction on the SQL statement and each candidate execution engine to obtain a second target execution engine, and routing the SQL statement to the second target execution engine. The SQL sentence is intelligently routed to the optimal execution engine by respectively matching the SQL sentence with the user parameters, the preset system rules and the historical execution conditions, so that the running cost is reduced, and the resources are saved.
Further, matching the SQL statement with a preset system rule includes:
analyzing the data calculated by the SQL sentence to obtain corresponding analysis data; the analysis data comprises data size, file number and complexity labels of data calculated by SQL sentences;
and matching the analysis data with the preset system rule.
It should be noted that, by performing data analysis on the SQL statement, the data size, the number of files and the complexity label of the data calculated by the SQL statement are obtained. And matching the data size, the number of files and the complexity labels of the data calculated by the SQL sentence with a preset system rule to be matched with a proper execution engine.
Further, analyzing the data calculated by the SQL sentence to obtain corresponding analysis data, including:
performing partition cutting and column cutting on the data calculated by the SQL sentence to obtain the data size and the number of files of the data calculated by the SQL sentence;
and carrying out complexity analysis on the data calculated by the SQL sentence to obtain a complexity label of the data calculated by the SQL sentence.
It should be noted that, the data analysis is mainly performed on the SQL statement through the Meta module. Performing partition cutting and column cutting on the data calculated by the SQL sentence to obtain the data size and the number of files of the data calculated by the SQL sentence; and carrying out complexity analysis on the data calculated by the SQL sentence to obtain a complexity label of the data calculated by the SQL sentence. The data is stored in a separate metadata base for use.
Further, according to the SQL statement and the historical execution situation, performing matching prediction on the SQL statement and each candidate execution engine to obtain a second target execution engine, including:
according to the data size, the number of files and the complexity labels of the data calculated by the SQL sentence, carrying out running cost prediction on each candidate execution engine by combining the historical execution conditions, and selecting the candidate execution engine with the lowest running cost as the second target execution engine;
the historical execution condition comprises preset time period data, contemporaneous data and cyclic period data of the current SQL sentence and similar SQL sentences.
It should be noted that, in the case that the proper execution engine is not matched according to the preset system rule, the matching prediction is performed on the SQL statement and each candidate execution engine according to the SQL statement and the historical execution condition, the SQL statement is matched to the proper execution engine, that is, the second target execution engine, and the SQL statement is routed to the execution engine to run. The method mainly comprises the steps of predicting the running cost of each candidate execution engine according to the data size, the number of files and the complexity label of data calculated by SQL sentences and combining the historical execution conditions, and selecting the candidate execution engine with the lowest running cost as the second target execution engine. The historical execution condition comprises preset time period data, contemporaneous data and cyclic period data of the current SQL sentence and similar SQL sentences. According to the historical execution conditions of the current SQL sentence and the similar SQL sentences and the comprehensive consideration of the data volume, the number of files and the complexity labels of the data calculated by the SQL sentence, the most suitable execution engine is matched, so that the running cost is reduced, and the resources are saved.
Further, the method further comprises:
counting historical submitted data, analysis data, routing data and execution data of the current SQL sentence and the similar SQL sentences to obtain corresponding statistical data;
and analyzing the statistical data through an AI linear regression method to obtain the running cost of the SQL sentence under each execution engine and the selected running times.
When analyzing the historical execution condition of the SQL sentence, statistics is needed to be carried out on the historical submitted data, the analysis data, the routing data and the execution data of the current SQL sentence and the similar SQL sentence to obtain corresponding statistical data; and analyzing the statistical data through an AI linear regression method to obtain the running cost of the SQL sentence under each execution engine and the selected running times. And according to the analysis result obtained by the AI linear regression method on the statistical data, matching the running cost and the execution engine with the optimal frequency to run, thereby reducing the running cost and saving the resources.
Further, before routing the SQL statement to the first target execution engine, the method further comprises:
judging whether the first target execution engine is consistent with a default execution engine or not;
if the SQL sentences are inconsistent, converting the SQL sentences to obtain converted SQL sentences;
and routing the SQL statement to the first target execution engine, comprising:
the converted SQL statement is routed to the first target execution engine.
It should be noted that, since each execution engine has its own unique syntax of SQL statements, in order to route the SQL statements written by the user to the target execution engine, the user must convert the SQL statements written by the user into syntax recognizable by all execution engines, that is, the user sets a default execution engine, and the SQL statements requiring adaptive routing uniformly use the SQL statement standard of the default execution engine, and need to convert the default execution engine into the SQL statements recognized by other execution engines. Therefore, it is necessary to determine whether the first target execution engine is consistent with the default execution engine, if not, convert the SQL statement to obtain a converted SQL statement, and route the converted SQL statement to the first target execution engine.
For example, in this embodiment, if the default execution engine is set to be a Hive execution engine, the SQL of the Hive execution engine needs to be uniformly used by the SQL statement standard of the Hive execution engine in an adaptive routing mode, and the Calcite module only needs to convert the Hive execution engine into the SQL statement identified by other execution engines.
The SQL statement conversion is performed except that the user sets the specified execution engine. In the present embodiment, the setting type of the default execution engine is not particularly limited.
Further, before routing the SQL statement to the first target execution engine, the method further comprises:
registering the SQL statement to a monitoring service, and regularly monitoring the execution condition of the SQL statement.
It should be noted that each SQL statement must be registered with the monitoring service before being routed to a particular execution engine. The monitoring service can monitor the running state of the SQL statement at regular time and monitor whether the statement execution succeeds or fails.
Further, the method further comprises:
if the SQL statement fails to be executed, performing degradation execution on the SQL statement on other candidate execution engines;
if the SQL statement has execution timeout, restarting a shadow job on other candidate execution engines.
It should be noted that if the execution of the SQL statement fails, it will choose to downgrade the execution on other candidate execution engines, such as Spark execution engine downgrade to Mapreduce execution engine; if the execution may timeout, a shadow job is restarted, that is, a demotion task is restarted to other execution engines to perform predictive execution, so that the running cost and time are saved.
Referring to fig. 2, fig. 2 is a schematic block diagram of an SQL routing device based on an adaptive execution engine according to an embodiment of the present invention, where the SQL routing device 2 based on the adaptive execution engine includes:
the SQL acquisition module 21 is used for acquiring SQL sentences and user parameters;
the RBO Manager module 22 is configured to determine whether an execution engine is specified according to the user parameter; if the appointed execution engine does not exist, matching the SQL sentence with a preset system rule; if the matching is successful, a first target execution engine is obtained, and the SQL statement is routed to the first target execution engine;
and the CBOManager module 23 is configured to, if the matching fails, predict the matching of the SQL statement and each candidate execution engine according to the SQL statement and the historical execution conditions, obtain a second target execution engine, and route the SQL statement to the second target execution engine.
The RBO Manager module 22 is specifically configured to:
analyzing the data calculated by the SQL sentence to obtain corresponding analysis data; the analysis data comprises data size, file number and complexity labels of data calculated by SQL sentences;
and matching the analysis data with the preset system rule.
The CBOManager module 23 is specifically configured to:
according to the data size, the number of files and the complexity labels of the data calculated by the SQL sentence, carrying out running cost prediction on each candidate execution engine by combining the historical execution conditions, and selecting the candidate execution engine with the lowest running cost as the second target execution engine;
the historical execution condition comprises preset time period data, contemporaneous data and cyclic period data of the current SQL sentence and similar SQL sentences.
It should be noted that, the SQL routing device 2 based on the adaptive execution engine mainly includes an SQL obtaining module 21, an rbo Manager module 22, and a CBOManager module 23.
The SQL obtaining module 21 is mainly used for obtaining SQL sentences and user parameters configured by users.
The RBO Manager module 22 is a rule-based optimization Manager, and mainly routes execution engines according to set system rules, and directly converts SQL to meet the rules and then routes the SQL to a designated execution engine, wherein the system rules comprise the size and the number of files of the data volume calculated by the current SQL, SQL complexity, and routes the SQL to a pure memory execution engine such as prest under a minimum value, and routes the SQL to a very large-scale data calculation engine such as Mapreduce above a maximum value.
The CBO Manager module 23 is a cost-based optimization Manager, and CBO is mainly used for comprehensively selecting execution conditions according to the current data size, file number, SQL complexity and historical execution conditions of the SQL. Judging that there are mainly two directions according to the historical execution conditions, wherein the first is the historical execution condition of the SQL; and secondly, historical execution conditions of SQL of the same type. The history word also has multi-layer meaning, and comprises data from the last time, contemporaneous data and cyclic period data. The historical data is linearly regressed according to AI means to obtain the cost and the selection times of the SQL under different execution engines.
In addition, the SQL routing device 2 based on the adaptive execution engine further comprises other auxiliary modules including a Meta module, a History Statistics module, a Calcite module, a monitoring service module and the like.
The Meta module is metadata statistics analysis service and is mainly used for analyzing SQL sentences, and comprises the steps of analyzing the SQL sentences and marking complexity labels; and carrying out partition cutting and column cutting on the SQL sentence to obtain the data size and the number of data files. And stores the data on separate metadata bases for use by the RBOManager module and the cbmanager module.
The History Statistics module is used for carrying out statistical analysis on historical execution conditions, recording the submitted data condition, the analyzed data condition, the route data condition and the execution data condition of each SQL statement, then serving subsequent AI linear regression, problem investigation record, accelerating SQL analysis and the like, and mainly used by the subsequent CBOManaager module. To ensure accuracy of reusability, large versions and small versions are distinguished on the storage of historical execution conditions, a large version is recorded once when an SQL statement modifies a keyword, and the SQL statement can be almost regarded as a new SQL statement, and if only a field is modified, aliases and the like are distinguished by small versions.
The Calcite module is an SQL statement conversion service, and because each execution engine has its own unique SQL statement grammar, to route it to the target execution engine, the SQL statement written by the user must be converted into a grammar that all execution engines can recognize. For example, the default execution engine is set as the Hive execution engine, and the SQL that needs adaptive routing is unified to use the SQL sentence standard of the Hive execution engine except that the user sets the specified execution engine, and the Calcite module only needs to convert the Hive execution engine into the SQL sentence identified by other execution engines. The result of the conversion is also recorded in History Statistics module for next use.
Each SQL statement must be registered with the monitoring service before it is submitted to a specific execution engine. The monitoring service can regularly monitor the running state of the SQL statement, and if the execution is failed, the execution is degraded, for example, a Spark execution engine is degraded to a Mapreduce execution engine; if execution may timeout Speculative Execution, a demotion task is restarted to other execution engines, a shadow job is restarted, if successful, it is determined whether a shadow task is completed, if so, the resource is released.
The specific manner in which the modules of the SQL routing device 2 based on the adaptive execution engine perform the operations in the foregoing embodiments of the foregoing related methods has been described in detail in the foregoing embodiments, and will not be described in detail herein.
Referring to fig. 3, fig. 3 is a schematic block diagram of an SQL routing device based on an adaptive execution engine according to an embodiment of the present invention, where the SQL routing device 3 based on the adaptive execution engine includes:
one or more memories 31 on which executable programs are stored;
one or more processors 32 for executing the executable programs in the memory 31 to implement the steps of any of the methods described above.
The specific manner in which the processor 32 executes the program in the memory 31 of the SQL routing device 3 based on the adaptive execution engine in the above embodiment has been described in detail in the embodiment related to the method, and will not be described in detail here.
It is to be understood that the same or similar parts in the above embodiments may be referred to each other, and that in some embodiments, the same or similar parts in other embodiments may be referred to.
It should be noted that in the description of the present invention, the terms "first," "second," and the like are used for descriptive purposes only and are not to be construed as indicating or implying relative importance. Furthermore, in the description of the present invention, unless otherwise indicated, the meaning of "plurality", "multiple" means at least two.
It will be understood that when an element is referred to as being "mounted" or "disposed" on another element, it can be directly on the other element or intervening elements may also be present; when an element is referred to as being "connected" to another element, it can be directly connected to the other element or intervening elements may be present, and further, as used herein, connection may comprise a wireless connection; the use of the term "and/or" includes any and all combinations of one or more of the associated listed items.
Any process or method description in a flowchart or otherwise described herein may be understood as: means, segments, or portions of code representing executable instructions including one or more steps for implementing specific logical functions or processes are included in the preferred embodiment of the present invention in which functions may be executed out of order from that shown or discussed, including in a substantially simultaneous manner or in an inverse order, depending upon the function involved, as would be understood by those skilled in the art of embodiments of the present invention.
It is to be understood that portions of the present invention may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, may be implemented using any one or combination of the following techniques, as is well known in the art: discrete logic circuits having logic gates for implementing logic functions on data signals, application specific integrated circuits having suitable combinational logic gates, programmable Gate Arrays (PGAs), field Programmable Gate Arrays (FPGAs), and the like.
Those of ordinary skill in the art will appreciate that all or a portion of the steps carried out in the method of the above-described embodiments may be implemented by a program to instruct related hardware, where the program may be stored in a computer readable storage medium, and where the program, when executed, includes one or a combination of the steps of the method embodiments.
In addition, each functional unit in the embodiments of the present invention may be integrated in one processing module, or each unit may exist alone physically, or two or more units may be integrated in one module. The integrated modules may be implemented in hardware or in software functional modules. The integrated modules may also be stored in a computer readable storage medium if implemented in the form of software functional modules and sold or used as a stand-alone product.
The above-mentioned storage medium may be a read-only memory, a magnetic disk or an optical disk, or the like.
In the description of the present specification, a description referring to terms "one embodiment," "some embodiments," "examples," "specific examples," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the present invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiments or examples. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.
While embodiments of the present invention have been shown and described above, it will be understood that the above embodiments are illustrative and not to be construed as limiting the invention, and that variations, modifications, alternatives and variations may be made to the above embodiments by one of ordinary skill in the art within the scope of the invention.

Claims (10)

1. An SQL routing method based on an adaptive execution engine, comprising:
acquiring SQL sentences and user parameters;
judging whether a designated execution engine exists according to the user parameters:
if the appointed execution engine does not exist, matching the SQL sentence with a preset system rule;
if the matching is successful, a first target execution engine is obtained, and the SQL statement is routed to the first target execution engine;
and if the matching fails, carrying out matching prediction on the SQL statement and each candidate execution engine according to the SQL statement and the historical execution condition to obtain a second target execution engine, and routing the SQL statement to the second target execution engine.
2. The method of claim 1, wherein matching the SQL statement with a preset system rule comprises:
analyzing the data calculated by the SQL sentence to obtain corresponding analysis data; the analysis data comprises data size, file number and complexity labels of data calculated by SQL sentences;
and matching the analysis data with the preset system rule.
3. The method according to claim 2, wherein parsing the data calculated by the SQL statement to obtain corresponding parsed data comprises:
performing partition cutting and column cutting on the data calculated by the SQL sentence to obtain the data size and the number of files of the data calculated by the SQL sentence;
and carrying out complexity analysis on the data calculated by the SQL sentence to obtain a complexity label of the data calculated by the SQL sentence.
4. The method of claim 1, wherein matching the SQL statement with each candidate execution engine to obtain a second target execution engine according to the SQL statement and the historical execution, comprises:
according to the data size, the number of files and the complexity labels of the data calculated by the SQL sentence, carrying out running cost prediction on each candidate execution engine by combining the historical execution conditions, and selecting the candidate execution engine with the lowest running cost as the second target execution engine;
the historical execution condition comprises preset time period data, contemporaneous data and cyclic period data of the current SQL sentence and similar SQL sentences.
5. The method according to claim 4, wherein the method further comprises:
counting historical submitted data, analysis data, routing data and execution data of the current SQL sentence and the similar SQL sentences to obtain corresponding statistical data;
and analyzing the statistical data through an AI linear regression method to obtain the running cost of the SQL sentence under each execution engine and the selected running times.
6. The method of claim 1, wherein prior to routing the SQL statement to the first target execution engine, the method further comprises:
judging whether the first target execution engine is consistent with a default execution engine or not;
if the SQL sentences are inconsistent, converting the SQL sentences to obtain converted SQL sentences;
and routing the SQL statement to the first target execution engine, comprising:
the converted SQL statement is routed to the first target execution engine.
7. The method of claim 1, wherein prior to routing the SQL statement to the first target execution engine, the method further comprises:
registering the SQL statement to a monitoring service, and regularly monitoring the execution condition of the SQL statement.
8. The method of claim 7, wherein the method further comprises:
if the SQL statement fails to be executed, performing degradation execution on the SQL statement on other candidate execution engines;
if the SQL statement has execution timeout, restarting a shadow job on other candidate execution engines.
9. An adaptive execution engine-based SQL routing device, comprising:
the SQL acquisition module is used for acquiring SQL sentences and user parameters;
the RBO Manager module is used for judging whether a designated execution engine exists or not according to the user parameters; if the appointed execution engine does not exist, matching the SQL sentence with a preset system rule; if the matching is successful, a first target execution engine is obtained, and the SQL statement is routed to the first target execution engine;
and the CBOManager module is used for carrying out matching prediction on the SQL statement and each candidate execution engine according to the SQL statement and the historical execution condition if the matching fails, obtaining a second target execution engine, and routing the SQL statement to the second target execution engine.
10. An adaptive execution engine-based SQL routing device, comprising:
one or more memories having executable programs stored thereon;
one or more processors configured to execute the executable program in the memory to implement the steps of the method of any one of claims 1-8.
CN202310417338.6A 2023-04-18 2023-04-18 SQL routing method, device and equipment based on self-adaptive execution engine Pending CN116431556A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202310417338.6A CN116431556A (en) 2023-04-18 2023-04-18 SQL routing method, device and equipment based on self-adaptive execution engine

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202310417338.6A CN116431556A (en) 2023-04-18 2023-04-18 SQL routing method, device and equipment based on self-adaptive execution engine

Publications (1)

Publication Number Publication Date
CN116431556A true CN116431556A (en) 2023-07-14

Family

ID=87079417

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202310417338.6A Pending CN116431556A (en) 2023-04-18 2023-04-18 SQL routing method, device and equipment based on self-adaptive execution engine

Country Status (1)

Country Link
CN (1) CN116431556A (en)

Similar Documents

Publication Publication Date Title
US11620286B2 (en) Continuous cloud-scale query optimization and processing
US10515002B2 (en) Utilizing artificial intelligence to test cloud applications
US8219581B2 (en) Method and system for analyzing ordered data using pattern matching in a relational database
US20100241893A1 (en) Interpretation and execution of a customizable database request using an extensible computer process and an available computing environment
JP2017515180A (en) Processing data sets in big data repositories
CN111752959B (en) Real-time database cross-database SQL interaction method and system
CN108694221B (en) Data real-time analysis method, module, equipment and device
US10706107B2 (en) Search systems and methods utilizing search based user clustering
JP2017505936A (en) System and method for hosting an in-memory database
US10896180B2 (en) Statement parsing method for database statement
US20170316006A1 (en) Searching for information relating to virtualization environments
CN112835924A (en) Real-time computing task processing method, device, equipment and storage medium
CN111752945A (en) Time sequence database data interaction method and system based on container and hierarchical model
US20190129961A1 (en) System and method for ranking search results
US11650966B2 (en) Assisted problem identification in a computing system
US20200356885A1 (en) Service management in a dbms
CN114077494A (en) Configuration method and system of data processing assembly
CN116431556A (en) SQL routing method, device and equipment based on self-adaptive execution engine
CN116561208A (en) Method, device and equipment for unifying OpenAPI query interfaces
Ocaña et al. Data analytics in bioinformatics: data science in practice for genomics analysis workflows
CN115114333A (en) Multi-engine visual data stream implementation method, device, equipment and storage medium
KR102202792B1 (en) Method and device for performing multi-caching on data sources of same or different types by using cluster-based processing system
CN112783758B (en) Test case library and feature library generation method, device and storage medium
CN112925889A (en) Natural language processing method, device, electronic equipment and storage medium
US20240143293A1 (en) Reusing and recommending user interface (ui) contents based on semantic information

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination