A cross-source SQL method and device

Abstract

The embodiment of the present application provides a cross-source SQL method and device. When receiving SQL query operations for multiple databases, the target data type is determined according to the correspondence between the SQL operation and the data type. A data type conversion step is added to the first SQL operation to obtain a second SQL operation. After the database executes the second SQL operation, the data type of the query result output is unified, thereby achieving the conversion of the data type of the query result of each database to the process of executing SQL in each database, so there is no need to perform additional data type conversion operations on the entire amount of data. Finally, the query results of the unified data type only need to be integrated to obtain the final query result. Compared with first executing the SQL operation to obtain the query result and then converting the data type of the query result, this method reduces the computational complexity of the data type conversion, thereby improving the efficiency of the cross-source SQL operation.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a cross-source SQL method and device. Background Art

[0002] In some scenarios, data is distributed in different databases. For example, the first database stores a part of the data in the data set, and the second database stores another part of the data in the data set. If the user needs to query and count all the data in the data set, the query and count operation needs to be performed on the first database and the second database respectively. This process is called cross-source SQL (Structured Query Language) in this article.

[0003] In the related art, the cross-source SQL process is often as follows: perform query statistics in the first database to obtain query statistical results (hereinafter referred to as the first query results), perform query statistics in the second database to obtain query statistical results (hereinafter referred to as the second query results), and then integrate the first query results and the second query results to obtain the final query results.

[0004] However, due to the different specifications of different databases, even if the same query statistical operation is performed on the first database and the second database, the data type of the query results obtained may be different, which means that before the first query result and the second query result are integrated, the first query result and the second query result need to be converted into the same data type. This conversion process often requires a large amount of computing, which leads to low cross-source SQL efficiency. Summary of the invention

[0005] The purpose of the embodiment of the present application is to provide a cross-source SQL method and device to improve the efficiency of cross-source SQL. The specific technical solution is as follows:

[0006] In a first aspect, an embodiment of the present application provides a cross-source SQL method, the method comprising:

[0007] In response to a first SQL operation on multiple databases, determining a target data type corresponding to the first SQL operation according to a correspondence between the SQL operation and the data type;

[0008] On the basis of the first SQL operation, a step of converting the data type of the query result of each database into the target data type is added to obtain a second SQL operation;

[0009] Execute the second SQL operation to obtain query results of each of the databases;

[0010] The query results of each of the databases are integrated to obtain the query result of the first SQL operation.

[0011] In a possible implementation, determining the target data type corresponding to the first SQL operation according to the correspondence between the SQL operation and the data type includes:

[0012] Determine the data targeted by the first SQL operation, and use the data type of the determined data as the first data type;

[0013] Determine a basic function used to implement the first SQL operation as a target basic function; and determine a calling order of each of the target basic functions in the process of implementing the first SQL operation as a target calling order;

[0014] Determine, according to a correspondence between an input data type and an output data type pre-set for the current basic function, a data type corresponding to the second data type as a third data type, wherein the current basic function is initially the first target basic function in the target calling sequence, and the second data type is initially the first data type;

[0015] Taking the target basic function located next to the current basic function in the target calling sequence as the new current basic function and the third data type as the new second data type, returning to the step of determining the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function;

[0016] Until all the target basic functions are traversed, the latest third data type is used as the target data type.

[0017] In a possible implementation, the method further includes:

[0018] Identify functions that cannot be implemented by other functions as basic functions.

[0019] In a possible implementation manner, the first SQL operation is average value calculation;

[0020] The determining of a basic function for implementing the first SQL operation as a target basic function includes:

[0021] Determine a sum function, a counting function, and a quotient function as target basic functions;

[0022] The determining, as a target calling sequence, of the target basic functions in the process of implementing the first SQL operation includes:

[0023] The order of the sum function, the counting function and the quotient function is determined in sequence as the first order to the third order.

[0024] In a possible implementation manner, the first SQL operation is a population covariance calculation;

[0025] The determining of a basic function for implementing the first SQL operation as a target basic function includes:

[0026] Determine a first sum function, a second sum function, a third sum function, a product function, a difference function, a first counting function, a first quotient function, a second counting function, and a second quotient function as target basic functions;

[0027] The determining, as a target calling sequence, of the target basic functions in the process of implementing the first SQL operation includes:

[0028] The first summation function, the second summation function, the third summation function, the product function, the difference function, the first counting function, the first quotient function, the second counting function and the second quotient function are sequentially determined to be from the first to the ninth order.

[0029] In a second aspect, an embodiment of the present application provides a cross-source SQL device, the device comprising:

[0030] A response module, configured to respond to a first SQL operation on multiple databases and determine a target data type corresponding to the first SQL operation according to a correspondence between the SQL operation and the data type;

[0031] An adding module is used to add a step of converting the data type of the query result of each database into the target data type on the basis of the first SQL operation, so as to obtain a second SQL operation;

[0032] An execution module, used for executing the second SQL operation to obtain query results of each of the databases;

[0033] The integration module is used to integrate the query results of each of the databases to obtain the query result of the first SQL operation.

[0034] In a possible implementation, the response module includes:

[0035] A first response submodule, configured to determine the data targeted by the first SQL operation, and use the data type of the determined data as the first data type;

[0036] A second response submodule is used to determine a basic function used to implement the first SQL operation as a target basic function; and determine a calling order of each of the target basic functions in the process of implementing the first SQL operation as a target calling order;

[0037] A third response submodule is used to determine the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function, wherein the current basic function is initially the first target basic function in the target calling sequence, and the second data type is initially the first data type;

[0038] A fourth response submodule is used for taking the target basic function located next to the current basic function in the target calling sequence as the new current basic function and the third data type as the new second data type, and returning to execute the step of determining the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function;

[0039] The fifth response submodule is used to traverse all the target basic functions and use the latest third data type as the target data type.

[0040] In a possible implementation, the device further includes:

[0041] A determination module is used to determine functions that cannot be realized by other functions as basic functions;

[0042] The first SQL operation is average value calculation;

[0043] The second response submodule includes:

[0044] A first response unit is used to determine a sum function, a counting function and a quotient function as target basic functions;

[0045] The second response submodule includes:

[0046] The second response unit is used to sequentially determine the order of the sum function, the counting function and the quotient function from the first order to the third order.

[0047] In a possible implementation manner, the first SQL operation is a population covariance calculation;

[0048] The second response submodule includes:

[0049] a third response unit, for determining a first sum function, a second sum function, a third sum function, a product function, a difference function, a first counting function, a first quotient function, a second counting function, and a second quotient function as target basic functions;

[0050] The second response submodule includes:

[0051] The fourth response unit is used to determine the order of the first summation function, the second summation function, the third summation function, the product function, the difference function, the first counting function, the first quotient function, the second counting function and the second quotient function in sequence from the first order to the ninth order.

[0052] In a third aspect, an embodiment of the present application provides an electronic device, including:

[0053] Memory, used to store computer programs;

[0054] The processor is used to implement any of the above-mentioned cross-source SQL methods when executing the program stored in the memory.

[0055] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the computer program implements any of the above-mentioned cross-source SQL methods.

[0056] In a fifth aspect, an embodiment of the present application provides an embodiment of the present application further provides a computer program product comprising instructions, which, when executed on a computer, enables the computer to execute any of the cross-source SQL methods described above.

[0057] Beneficial effects of the embodiments of the present application:

[0058] A cross-source SQL method and device provided in an embodiment of the present application, when receiving SQL query operations for multiple databases, first determines the target data type according to the correspondence between the SQL operation and the data type. Subsequently, a data type conversion step is added to the first SQL operation to obtain a second SQL operation. After the database executes the second SQL operation, the data type of the query result output is unified, thereby achieving the conversion of the data type of the query result of each database to the process of executing SQL in each database, so there is no need to perform additional data type conversion operations on the entire amount of data. Finally, it is only necessary to integrate these query results of unified data types to obtain the final query result. Compared with first executing the SQL operation to obtain the query result and then converting the data type of the query result, this method significantly reduces the amount of calculation of the data type conversion, thereby improving the efficiency of the cross-source SQL operation.

[0059] Of course, implementing any product or method of the present application does not necessarily require achieving all of the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0060] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application, and for ordinary technicians in this field, other embodiments can also be obtained based on these drawings.

[0061] Figure 1 A schematic diagram of an existing cross-source SQL method provided in an embodiment of the present application;

[0062] Figure 2 A schematic diagram of the process flow of the cross-source SQL method provided in an embodiment of the present application;

[0063] Figure 3 A schematic diagram of a cross-source SQL method provided in an embodiment of the present application;

[0064] Figure 4 A schematic diagram of a first process for confirming a target data type provided in an embodiment of the present application;

[0065] Figure 5 A second flow chart of confirming the target data type provided in an embodiment of the present application;

[0066] Figure 6 A schematic diagram of a process for performing an AVG operation provided in an embodiment of the present application;

[0067] Figure 7 A schematic diagram of a process for performing a COVAR_POP operation provided in an embodiment of the present application;

[0068] Figure 8 A schematic diagram of the structure of a cross-source SQL device provided in an embodiment of the present application;

[0069] Fig. 9 A schematic diagram of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0070] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field based on the present application belong to the scope of protection of the present application.

[0071] Different open source databases have different definition rules for the data types (including input data types and output data types) of the same function. The data types include but are not limited to Decimal (decimal data type), Integer (integer data type), Double (a floating-point number type), and Float (another floating-point number type).

[0072] Take the AVG (average) function as an example. In Apache Spark (‌ an open source distributed computing system), the output data type of the AVG function follows the following rules:

[0073] If the input data type of the AVG function is Decimal, the output data type of the AVG function is also Decimal. At the same time, in order to ensure the accuracy of the query results, the precision and decimal columns are specially processed by adding 4 respectively based on the input data type of Decimal. If the input data type of the AVG function is Integer type instead of Decimal, the output data type of the AVG function is Double.

[0074] In Apache Calcite (a framework for query optimization of heterogeneous data sources), the output data type of the AVG function is consistent with the input data type of the AVG function. For example, when the AVG function calculates the average of Integer type data, its output data type is also Integer type.

[0075] For example, a cross-source SQL statement is executed on a first database based on Apache Spark and a second database based on Apache Calcite, such as executing a cross-source AVG operation (i.e., executing an AVG function). Figure 1 , Figure 1A schematic diagram of an existing cross-source SQL method provided in an embodiment of the present application, in the first database 101, the input data type of the AVG operation (i.e., the average value calculation in the figure) 100 is the Integer type, and the data type of the first query result 1011 obtained by querying the first database 101 based on the AVG operation 100 is the Double type. In the second database 102, the input type of the AVG operation 100 is also the Integer type, but the data type of the second query result 1021 obtained by querying the second database 102 based on the AVG operation 100 is the Integer type. It can be seen that the data types of the first query result 1011 and the second query result 1021 are different. Before integrating the first query result 1011 of the Double type and the second query result 1021 of the Integer type, it is necessary to convert the first query result 1011 and the second query result 1021 into the same data type. For example, the data types of the first query result 1011 and the second query result 1021 can be converted into the Integer type, or the data types of the first query result 1011 and the second query result 1021 can be converted into the Double type, or the data types of the first query result 1011 and the second query result 1021 can be converted into other data types except the Integer type and the Double type. Figure 1 In the example, only the data type of the first query result 1011 is converted to the Integer type to obtain the first query result 1012 of the Integer type, and the first query result 1012 and the second query result 1021 are integrated to obtain the final query result 103 of the Integer type. During the data type conversion process, if the data type of a part of the data in the first query result is already the Integer type, but the data object of the data type conversion operation is still the full data corresponding to the first query result, the conversion process often requires a large amount of calculation, resulting in low cross-source SQL efficiency.

[0076] Based on this, the present application embodiment provides a cross-source SQL method, see Figure 2 , Figure 2 A schematic diagram of a cross-source SQL method provided in an embodiment of the present application, the method comprising:

[0077] S201, in response to a first SQL operation on multiple databases (ie, the first structured query language operation in the figure), according to the correspondence between the SQL operation and the data type, determining a target data type corresponding to the first SQL operation.

[0078] S202, based on the first SQL operation, a step of converting the type of the query result of each database into a target data type is added to obtain a second SQL operation (ie, the second structured query language operation in the figure).

[0079] S203, executing a second SQL operation to obtain query results of each database.

[0080] S204, integrating the query results of each database to obtain the query result of the first SQL operation.

[0081] Applying the above embodiment, when receiving SQL query operations for multiple databases, first determine a unified target data type based on the correspondence between the SQL operation and the data type. Subsequently, add a data type conversion step to the original SQL query (i.e., the first SQL operation) to obtain the second SQL operation. After the database executes the second SQL operation, the data type of the query result output is unified, thereby achieving the conversion of the data type of the query result of each database to the process of executing SQL in each database, so there is no need to perform additional data type conversion operations on the entire amount of data. Finally, it is only necessary to integrate these query results of unified data types to obtain the final query result. Compared with first executing the SQL operation to obtain the query result and then converting the data type of the query result, this method reduces the amount of calculation for data type conversion, thereby improving the efficiency of cross-source SQL operations, allowing users to more quickly query and count data sets distributed in different databases.

[0082] The above steps S201 to S204 are described below:

[0083] In step S201, the first SQL operation is an SQL query statement for performing data query in multiple databases to obtain query results. The first SQL operation can be an SQL query statement directly input by the user, or an SQL query statement generated based on a natural language text input by the user, which is not specifically limited here. The SQL operation in this article is an operation performed on the data in each database indicated in the SQL statement, and does not include an operation indicated in the SQL statement to integrate the query results obtained from the individual databases. Exemplarily, the first SQL operation is an AVG operation, a COVAR_POP (overall covariance) operation, etc.

[0084] The correspondence between SQL operations and data types is a predefined rule or mapping, which is used to indicate which data type each SQL operation should correspond to. When predefining rules and mappings, certain principles should be followed. For example, the data type corresponding to the SQL operation should be the legal data type of the output data of the SQL operation. For example, if a SQL operation outputs a numerical value, then the data type corresponding to the SQL operation can be Integer type or Double type. However, if the result of an SQL operation output is not a numerical value, such as a letter, then the data type should be similar to character (char), string (string), etc. For example, the data type corresponding to the predefined AVG operation is Float type, and the COVAR_POP operation is performed based on two sets of data. Therefore, the data type corresponding to the predefined COVAR_POP function is (Integer type, Float type).

[0085] In step S202, a step of converting the data type of the query result of each database into the target data type is added on the basis of the first SQL operation, that is, an additional instruction or function is added to the first SQL operation. The function of these instructions or functions is to convert the data type of the query result into the target data type determined based on the corresponding relationship before the database returns the query result.

[0086] The second SQL operation adds a step based on the original SQL operation (ie, the first SQL operation), and the newly added step can convert the data type of the query result obtained by the first SQL operation from the database into the target data type.

[0087] Taking the aforementioned first SQL operation as an AVG operation as an example, the first SQL operation is: select avg(column) asavg1 from spark.table as table1 left join (select avg(column) as avg2 fromcalcite.table) as table2 on avg2 = avg1;

[0088] Among them, spark.table is the name of the data table, calcite.table is the name of another data table, column is the name of the column in both tables to be aggregated, avg(column) as avg1 and avg(column) asavg2, these two expressions calculate the average value of the column in spark.table and calcite.table respectively, as avg1 and as avg2 are aliases given to the columns of these average calculation results. Left join is a SQL join operation, which is used to combine data from two or more tables according to a certain condition, and on avg2 = avg1 is the join condition of left join.

[0089] For example, the data type corresponding to the AVG operation is predefined as Float type, and the second SQL operation is:

[0090] select cast(avg(column) as float) as avg1 from spark.table as table1left join (select cast(avg(column) as float) as avg2 from calcite.table) astable2 on avg2 = avg1;

[0091] Among them, cast(avg(column) as float) is used to convert the average value calculated by avg(column) into the Float type. The meanings of other fields have been explained in the first SQL operation and will not be repeated here.

[0092] In step S203, each database executes the second SQL operation to obtain a query result of each database, and the data type of each query result is the target data type.

[0093] In step S204, query results from different databases that have been converted into a unified target data type are merged or summarized.

[0094] The specific method of integration depends on the specific needs, which may include operations such as JOIN, UNION, INTERSECT, or more complex data processing logic, which will not be listed here.

[0095] In order to more clearly explain the above cross-source SQL method, for example, see Figure 3 , Figure 3The schematic diagram of the cross-source SQL method provided for the embodiment of the present application still takes the aforementioned execution of the AVG operation on the first database based on Apache Spark and the second database based on Apache Calcite as an example. If the corresponding data type is pre-defined as the Integer type, it is determined that the target data type corresponding to the AVG operation 300 (i.e., the first SQL operation) is the Integer type, and a step of converting the type of the query result of each database into the Integer type is added to the AVG operation 300 to obtain the modified AVG operation 3000 (i.e., the second SQL operation).

[0096] In the first database 301, the input data type of the AVG operation is the Integer type, and the data type of the first query result 3011 obtained by querying the first database 301 based on the modified AVG operation 3000 is the Integer type. In the second database 302, the input type of the AVG operation 300 is also the Integer type, and the data type of the second query result 3021 obtained by querying the second database 302 based on the modified AVG operation 3000 is the Integer type. It can be seen that the data types of the first query result 3011 and the second query result 3021 are the same, both of which are the target data type Integer type. The first query result 3011 and the second query result 3021 are integrated to obtain the query result 303 of the first SQL operation with the data type of Integer.

[0097] Through the above cross-source SQL method, compared with first executing the SQL operation to obtain the query result and then converting the data type of the query result, this method reduces the calculation amount of data type conversion, thereby improving the efficiency of the cross-source SQL operation. In addition, since there is no need to convert the data type of the query result, the influence of the data type conversion process on the accuracy of the query result is avoided, thereby improving the accuracy of the cross-source SQL.

[0098] Since the first SQL operation is implemented by basic functions, for example, the AVG operation is implemented based on the SUM (sum) function, the COUNT (count) function, and the DIVIDE (quotient) function, and the COVAR_POP operation is implemented based on the SUM function, the MULTIPL (product) function, the MINUS (difference) function, the REGR_COUNT (non-empty number pair count) function, etc. Based on this, in a possible embodiment, the target data type can be confirmed by the following method, see Figure 4 , Figure 4 A first flow chart of confirming the target data type provided in an embodiment of the present application includes:

[0099] S401: Determine data targeted by a first SQL operation, and use the data type of the determined data as a first data type.

[0100] S402, determining a basic function for implementing the first SQL operation as a target basic function; and determining a calling sequence of each target basic function in the process of implementing the first SQL operation as a target calling sequence.

[0101] S403: Determine a data type corresponding to the second data type as a third data type according to a correspondence relationship between an input data type and an output data type pre-set for the current basic function.

[0102] Among them, the current basic function is initially the first target basic function in the target calling sequence, and the second data type is initially the first data type.

[0103] S404, taking the target basic function located next to the current basic function in the target calling order as the new current basic function and the third data type as the new second data type, returns to execute the step of determining the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function.

[0104] S405, until all target basic functions are traversed, and the latest third data type is used as the target data type.

[0105] In step S401, the data type of the data targeted by the first SQL operation is determined as the first data type.

[0106] In step S402, the first SQL operation is decomposed into a series of basic functions, and the calling sequence between the basic functions is determined according to the requirements of the SQL operation, and the calling sequence is recorded as the target calling sequence.

[0107] In order to improve the accuracy of the derivation of the target data type, the basic function is a function that cannot be implemented by other functions, such as the SUM function, the COUNT function, the DIVIDE function, the MINUS function, the MULTIPL function, etc.

[0108] Step S403 to step S405 is an iterative process, which gradually applies the conversion rules of the input data type and output data type of each basic function, starting from the data type of the data directly involved in the SQL operation, and gradually derives the data type after the entire SQL execution process as the data type corresponding to the SQL operation. The step of iteratively determining the correspondence between the input data type and the output data type set for the current basic function is also the aforementioned step of determining the correspondence between the SQL operation and the data type.

[0109] In step S403-step S405, the input data type of the current basic function is the output data type of the previous basic function of the current basic function, and the output data type of the current basic function is the input data type of the next basic function of the current basic function, see Figure 5 , Figure 5 The second flow chart for confirming the target data type provided in the embodiment of the present application is that the input data type of the basic function 502 is the output data type of the basic function 501, the output data type of the basic function 502 is the input data type of the basic function 503, and the output data type of the basic function 503 is the input data type of the basic function 504. The input data type of the basic function 501 and the output data type of the basic function 504 are not repeated in the figure.

[0110] For example, if the first SQL operation is average value calculation, that is, AVG operation, the data type of the data targeted by the AVG operation is Integer type, and the AVG operation can be converted into the form of SUM divided by COUNT, then it is confirmed that the obtained target function is implemented by the SUM function, COUNT function and DIVIDE function, and the execution order of the SUM function, COUNT function and DIVIDE function are the first, second and third order respectively.

[0111] For details, see Figure 6 , Figure 6 The flowchart for executing the AVG operation provided in the embodiment of the present application is as follows: the target data type of the AVG operation can first be derived that the output data type 6011 of the SUM function 601 (i.e., the summation function in the figure) when the input data type is the Integer type is the Integer type, and then it can be derived that the output data type 6021 of the COUNT function 602 (i.e., the counting function in the figure) when the input data type is the Integer type is the Bigint type (an integer data type). The output data type 6011 of the SUM function 601 and the output data type 6021 of the COUNT function 602 are used as the input data type 6031 of the DIVIDE function 603 (i.e., the quotient function in the figure) for calculation, and then the output data type 6032 is the Bigint type, that is, the Bigint type is the target data type of the AVG operation.

[0112] By applying the above embodiment, the AVG operation is decomposed into the SUM function, the COUNT function and the DIVIDE function, and their ordered calling order is determined, which improves the accuracy and efficiency of the target data type derivation corresponding to the AVG operation, thereby improving the accuracy and efficiency of the cross-source SQL.

[0113] If the first SQL operation is the population covariance calculation, it is the COVAR_POP operation. The COVAR_POP operation is used to calculate the population covariance of two sets of data x and y. The COVAR_POP operation is: COVAR_POP(x,y)=(SUM(x y)-SUM(x) SUM(y) / REGR_COUNT(x,y)) / REGR_COUNT(x,y). The data type of the data targeted by the COVAR_POP operation is (Integer type, Float type), and the target basic functions for implementing the COVAR_POP operation include: the first sum function, the second sum function, the third sum function, the product function, the difference function, the first counting function, the first quotient function, the second counting function, and the second quotient function.

[0114] The first summation function corresponds to the above SUM(x y), the second summation function corresponds to the above SUM(x), the third summation function corresponds to the above SUM(y), and the quadrature function corresponds to the above SUM(x) SUM(y), difference function corresponds to the above SUM(x y)-SUM(x) SUM(y), the first counting function corresponds to the above REGR_COUNT(x,y), the first quotient function corresponds to the above (SUM(x y)-SUM(x) SUM(y) / REGR_COUNT(x,y)), the second counting function corresponds to the last REGR_COUNT(x,y) above, and the second quotient function corresponds to the above (SUM(x y)-SUM(x) SUM(y) / REGR_COUNT(x,y)) / REGR_COUNT(x,y).

[0115] When executing the COVAR_POP operation, the execution order of the first sum function, the second sum function, the third sum function, the product function, the difference function, the first counting function, the first quotient function, the second counting function and the second quotient function are respectively from the first to the ninth.

[0116] For details, see Figure 7 , Figure 7The flowchart of executing the COVAR_POP operation provided in the embodiment of the present application first calculates that the output data type 7011 of the first summation function 701 whose input data types are Integer type and Float type is Float type, and then calculates that the output data type 7021 of the second summation function 702 whose input data type is Integer type is Integer type. The output data type 7031 of the third summation function 703 whose input data type is Float type is Float type. The output data types 7021 of the second summation function 702 and 7031 of the third summation function 703 are used as the input data type 7041 of the product function 704, and the output data type 7042 of the product function 704 is Float type. The output data type 7051 of the first counting function 705 is calculated as Bigint type, and the output data type 7042 of the product function 704 and the output function type 7051 of the first counting function 705 are used as the input data type 7061 of the first quotient function 706, and the output data type 7062 of the first quotient function 706 is Float type. The output data type 7011 of the first sum function 701 and the output data type 7062 of the first quotient function 706 are used as the input data type 7071 of the difference function 707, and the output data type 7072 of the difference function 707 is of Float type. The output data type 7081 of the second counting function 708 is calculated as Bigint type, and the output data type 7072 of the difference function 707 and the output data type 7081 of the second counting function 708 are used as the input data type 7091 of the second quotient function 709, and the output data type 7092 of the second quotient function 709 is of Float type, and the Float type is used as the target data type of the COVAR_POP operation.

[0117] By applying the above embodiment, the COVAR_POP operation is decomposed into the SUM function, the MULTIPL function, the MINUS function and the COUNT function, and their ordered calling order is determined, thereby improving the accuracy and efficiency of the target data type derivation corresponding to the COVAR_POP operation, thereby improving the accuracy and efficiency of the cross-source SQL.

[0118] By applying the above embodiment, the data targeted by the first SQL operation is identified, and its data type is determined as the first data type accordingly, and the starting point for confirming the target data type is determined. By determining the basic functions used to implement the SQL operation and their calling order, the target data type is gradually derived using the correspondence between the input data type and the output data type of each basic function. This process processes each target basic function in turn in an iterative manner, and continuously determines the input data type of the next function based on the output data type of the previous function until all target basic functions are traversed, and finally the target data type is obtained, thereby improving the accuracy and efficiency of the target data type derivation, thereby improving the accuracy and efficiency of the cross-source SQL.

[0119] Corresponding to the aforementioned cross-source SQL method, the present application embodiment also provides a cross-source SQL device, see Figure 8 , Figure 8 A schematic diagram of the structure of a cross-source SQL device provided in an embodiment of the present application, the device includes:

[0120] A response module 801 is used to respond to a first SQL operation on multiple databases and determine a target data type corresponding to the first SQL operation according to a correspondence between the SQL operation and the data type;

[0121] An adding module 802 is used to add a step of converting the data type of the query result of each database into the target data type on the basis of the first SQL operation, so as to obtain a second SQL operation;

[0122] An execution module 803 is used to execute the second SQL operation to obtain query results of each of the databases;

[0123] The integration module 804 is used to integrate the query results of each of the databases to obtain the query result of the first SQL operation.

[0124] Applying the above embodiment, when receiving SQL query operations for multiple databases, first determine a unified target data type based on the correspondence between the SQL operation and the data type. Subsequently, add a data type conversion step to the original SQL query (i.e., the first SQL operation) to obtain the second SQL operation. After the database executes the second SQL operation, the data type of the query result output is unified, thereby achieving the conversion of the data type of the query result of each database to the process of executing SQL in each database, so there is no need to perform additional data type conversion operations on the entire amount of data. Finally, it is only necessary to integrate these query results of unified data types to obtain the final query result. Compared with first executing the SQL operation to obtain the query result and then converting the data type of the query result, this method reduces the amount of calculation for data type conversion, thereby improving the efficiency of cross-source SQL operations, allowing users to more quickly query and count data sets distributed in different databases.

[0125] In a possible implementation, the response module includes:

[0126] A first response submodule, configured to determine the data targeted by the first SQL operation, and use the data type of the determined data as the first data type;

[0127] A second response submodule is used to determine a basic function used to implement the first SQL operation as a target basic function; and determine a calling order of each of the target basic functions in the process of implementing the first SQL operation as a target calling order;

[0128] A third response submodule is used to determine the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function, wherein the current basic function is initially the first target basic function in the target calling sequence, and the second data type is initially the first data type;

[0129] A fourth response submodule is used for taking the target basic function located next to the current basic function in the target calling sequence as the new current basic function and the third data type as the new second data type, and returning to execute the step of determining the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function;

[0130] The fifth response submodule is used to traverse all the target basic functions and use the latest third data type as the target data type.

[0131] In a possible implementation manner, the device further includes:

[0132] A determination module is used to determine functions that cannot be realized by other functions as basic functions;

[0133] The first SQL operation is average value calculation;

[0134] The second response submodule includes:

[0135] A first response unit is used to determine a sum function, a counting function and a quotient function as target basic functions;

[0136] The second response submodule includes:

[0137] The second response unit is used to sequentially determine the order of the sum function, the counting function and the quotient function from the first order to the third order.

[0138] In a possible implementation manner, the first SQL operation is a population covariance calculation;

[0139] The second response submodule includes:

[0140] a third response unit, for determining a first sum function, a second sum function, a third sum function, a product function, a difference function, a first counting function, a first quotient function, a second counting function, and a second quotient function as target basic functions;

[0141] The second response submodule includes:

[0142] The fourth response unit is used to determine the order of the first summation function, the second summation function, the third summation function, the product function, the difference function, the first counting function, the first quotient function, the second counting function and the second quotient function in sequence from the first order to the ninth order.

[0143] The present application also provides an electronic device, such as Fig. 9 As shown, including:

[0144] Memory 901, used for storing computer programs;

[0145] The processor 902 is used to execute the program stored in the memory 901, and implements the following steps:

[0146] In response to a first SQL operation on multiple databases, determining a target data type corresponding to the first SQL operation according to a correspondence between the SQL operation and the data type;

[0147] On the basis of the first SQL operation, a step of converting the data type of the query result of each database into the target data type is added to obtain a second SQL operation;

[0148] Execute the second SQL operation to obtain query results of each of the databases;

[0149] The query results of each of the databases are integrated to obtain the query result of the first SQL operation.

[0150] Furthermore, the electronic device may further include a communication bus and / or a communication interface, and the processor 902, the communication interface, and the memory 901 communicate with each other via the communication bus.

[0151] The communication bus mentioned in the above electronic device can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0152] The communication interface is used for communication between the above electronic device and other devices.

[0153] The memory may include a random access memory (RAM) or a non-volatile memory (NVM), such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0154] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0155] In another embodiment provided by the present application, a computer-readable storage medium is further provided, in which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above cross-source SQL methods are implemented.

[0156] In another embodiment provided by the present application, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute any cross-source SQL method in the above embodiments.

[0157] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions may be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a solid-state hard disk (SSD), etc.

[0158] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0159] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0160] The above description is only a preferred embodiment of the present application and is not intended to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application are included in the protection scope of the present application.

Claims

1. A cross-source SQL method, characterized in that: The method comprises: In response to a first SQL operation on multiple databases, determining a target data type corresponding to the first SQL operation according to a correspondence between the SQL operation and the data type; On the basis of the first SQL operation, a step of converting the data type of the query result of each database into the target data type is added to obtain a second SQL operation; Execute the second SQL operation to obtain query results of each of the databases; Integrate the query results of each of the databases to obtain the query result of the first SQL operation; The determining, according to the correspondence between the SQL operation and the data type, a target data type corresponding to the first SQL operation includes: Determine the data targeted by the first SQL operation, and use the data type of the determined data as the first data type; Determine a basic function used to implement the first SQL operation as a target basic function; and determine a calling order of each of the target basic functions in the process of implementing the first SQL operation as a target calling order; Determine, according to a correspondence between an input data type and an output data type pre-set for a current basic function, a data type corresponding to the second data type as a third data type, wherein the current basic function is initially the first target basic function in the target calling sequence, and the second data type is initially the first data type; Taking the target basic function located next to the current basic function in the target calling sequence as the new current basic function and the third data type as the new second data type, returning to the step of determining the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function; Until all the target basic functions are traversed, the latest third data type is used as the target data type.

2. The method according to claim 1, characterized in that The method further comprises: Identify functions that cannot be implemented by other functions as basic functions.

3. The method according to claim 1, characterized in that The first SQL operation is average value calculation; The determining of a basic function for implementing the first SQL operation as a target basic function includes: Determine a sum function, a counting function, and a quotient function as target basic functions; The determining, as a target calling sequence, of the target basic functions in the process of implementing the first SQL operation includes: The order of the sum function, the counting function and the quotient function is determined in sequence as the first order to the third order.

4. The method according to claim 1, characterized in that: The first SQL operation is to calculate the overall covariance of; The determining of a basic function for implementing the first SQL operation as a target basic function includes: Determine a first sum function, a second sum function, a third sum function, a product function, a difference function, a first counting function, a first quotient function, a second counting function, and a second quotient function as target basic functions; The determining, as a target calling sequence, of the target basic functions in the process of implementing the first SQL operation includes: The first summation function, the second summation function, the third summation function, the product function, the difference function, the first counting function, the first quotient function, the second counting function and the second quotient function are sequentially determined to be from the first to the ninth order.

5. A cross-source SQL device, characterized in that: The device comprises: A response module, configured to respond to a first SQL operation on multiple databases and determine a target data type corresponding to the first SQL operation according to a correspondence between the SQL operation and the data type; An adding module is used to add a step of converting the data type of the query result of each database into the target data type on the basis of the first SQL operation, so as to obtain a second SQL operation; An execution module, used for executing the second SQL operation to obtain query results of each of the databases; An integration module, used to integrate the query results of each of the databases to obtain the query result of the first SQL operation; The response module comprises: A first response submodule, configured to determine the data targeted by the first SQL operation, and use the data type of the determined data as the first data type; A second response submodule is used to determine a basic function used to implement the first SQL operation as a target basic function; and determine a calling order of each of the target basic functions in the process of implementing the first SQL operation as a target calling order; A third response submodule is used to determine the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function, wherein the current basic function is initially the first target basic function in the target calling sequence, and the second data type is initially the first data type; A fourth response submodule is used for taking the target basic function located next to the current basic function in the target calling sequence as the new current basic function and the third data type as the new second data type, and returning to execute the step of determining the data type corresponding to the second data type as the third data type according to the correspondence between the input data type and the output data type pre-set for the current basic function; The fifth response submodule is used to traverse all the target basic functions and use the latest third data type as the target data type.

6. The device according to claim 5, characterized in that The device also includes: A determination module is used to determine functions that cannot be realized by other functions as basic functions; The first SQL operation is average value calculation; The second response submodule includes: A first response unit is used to determine a sum function, a counting function and a quotient function as target basic functions; The second response submodule includes: A second response unit, used to sequentially determine the order of the sum function, the counting function and the quotient function from the first order to the third order; The first SQL operation is to calculate the overall covariance of; The second response submodule includes: a third response unit, for determining a first sum function, a second sum function, a third sum function, a product function, a difference function, a first counting function, a first quotient function, a second counting function, and a second quotient function as target basic functions; The second response submodule includes: The fourth response unit is used to determine the order of the first summation function, the second summation function, the third summation function, the product function, the difference function, the first counting function, the first quotient function, the second counting function and the second quotient function in sequence from the first order to the ninth order.

7. An electronic device, characterized in that: include: Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-4 when executing a program stored in a memory.

8. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images