Methods, devices, equipment and storage media for cross-database queries of heterogeneous databases
By generating transaction requests at the database application layer and merging query results from heterogeneous databases, the problem of inconsistent query patterns across heterogeneous databases is solved, achieving both effectiveness and flexibility in cross-database queries.
Patent Information
- Application Number
- CN202211195075.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2022-09-28
- Publication Date
- 2026-11-10
- Estimated Expiration
- 2042-09-28
AI Technical Summary
Heterogeneous databases cannot use the same query pattern to perform cross-database queries directly, and there is a lack of effective cross-database query solutions.
By generating transaction requests at the database application layer and sending them to different levels of heterogeneous databases, the query results are returned and merged at the application layer, thus enabling cross-database queries using application layer services.
It enables cross-database queries between heterogeneous databases, solves the problem of inconsistent query patterns, and allows multiple databases to be deployed on different network systems.
Smart Images

Figure CN115408448B_ABST
Abstract
Description
Technical Field
[0001] This disclosure relates to the field of database technology and can be used in the financial field or other fields. In particular, it relates to a method, apparatus, device, medium and program product for cross-database querying of heterogeneous databases. Background Technology
[0002] In some database query scenarios, cross-database queries often occur across multiple databases. These data sources can be homogeneous or heterogeneous databases. However, related technologies focus more on improving the efficiency of cross-database queries on homogeneous databases.
[0003] In realizing the concept of this disclosure, the inventors discovered at least the following problems in the related technology:
[0004] Due to differences in database schemas, cross-database queries on heterogeneous databases cannot be performed directly using the same query schema, and there is currently no good solution for cross-database queries on heterogeneous databases. Summary of the Invention
[0005] In view of the above problems, this disclosure provides methods, apparatus, devices, media and program products for cross-database querying of heterogeneous databases.
[0006] According to a first aspect of this disclosure, a method for cross-database querying of heterogeneous databases is provided, comprising: responding to a cross-database query instruction, invoking a service within a first application layer of a first database to generate a first transaction request and a second transaction request, wherein the first transaction request and the second transaction request contain the same query information; sending the first transaction request to a first database layer of the first database through the first application layer to obtain a first query result obtained from the first database layer based on the query information; sending the second transaction request to a second application layer of a second database through the first application layer to obtain a second query result obtained from the second database layer of the second database based on the query information, wherein the first database and the second database are heterogeneous databases; and merging the first query result and the second query result from the second application layer through the first application layer to obtain a cross-database query result.
[0007] According to embodiments of this disclosure, the query information includes a query identifier and query parameters, wherein the query identifier is used to obtain a query statement, and the query parameters are the parameters of the query statement.
[0008] According to embodiments of this disclosure, the heterogeneous database cross-database query method further includes modifying the query statement to be consistent with the query language used by the first database or the second database when the query statement does not match the query language used by the first database or the second database.
[0009] According to embodiments of this disclosure, the first database is a distributed database, and the second database is a centralized database; the step of sending the first transaction request to the database layer of the first database through the first application layer to obtain the first query result based on the query information from the first database layer includes: sending the first transaction request to the first data proxy layer of the first database through the first application layer, so that the first data proxy layer obtains the first query result from the first database layer; and receiving the first query result from the first data proxy layer through the first application layer.
[0010] According to embodiments of this disclosure, the heterogeneous database further includes multiple third databases, which are distributed databases isomorphic to the first database. The step of having the data proxy layer obtain the first query result from the first database layer includes: using the first data proxy layer to generate multiple access requests containing the query information based on the first transaction request, the number of access requests being the same as the number of distributed databases; sending the access requests to the first database layer and the third database layers of the multiple third databases respectively through the first data proxy layer to obtain multiple third query results from the first database layer and the multiple third database layers respectively; and merging the multiple third query results through the first data proxy layer to obtain the first query result.
[0011] According to embodiments of this disclosure, merging the first query result and the second query result from the second application layer through the first application layer to obtain a cross-database query result includes: merging the first query result and the second query result by using a local sorting method when the amount of data to be queried is lower than a preset threshold, utilizing the first application layer.
[0012] According to an embodiment of this disclosure, merging the first query result and the second query result using a local sorting method includes: sequentially obtaining the first query result and the second query result from the first database and the second database according to a preset query order between the first database and the second database, and then sorting them.
[0013] According to embodiments of this disclosure, the process of merging the first query result and the second query result from the application layer of the second database using the application layer of the first database to obtain a cross-database query result further includes: merging the first query result and the second query result using a global sorting method when the amount of data to be queried is lower than a preset threshold, by utilizing the first application layer.
[0014] A second aspect of this disclosure provides a heterogeneous database cross-database query apparatus, comprising: a generation module, configured to, in response to a cross-database query instruction, invoke an application-layer service of a first database to generate a first transaction request and a second transaction request, wherein the first transaction request and the second transaction request contain the same query information; a first sending module, configured to send the first transaction request to a first database layer of the first database through the first application layer to obtain a first query result based on the query information from the first database layer; a second sending module, configured to send the second transaction request to a second application layer of the second database through the first application layer to obtain a second query result based on the query information from the second database layer of the second database; and a merging module, configured to merge the first query result and the second query result from the second application layer through the first application layer to obtain a cross-database query result.
[0015] According to embodiments of this disclosure, the query information includes a query identifier and query parameters, wherein the query identifier is used to obtain a query statement, and the query parameters are the parameters of the query statement.
[0016] According to an embodiment of this disclosure, the heterogeneous database cross-database query device further includes: a conversion module, used to convert the query statement into a language consistent with the query language used by the first database or the second database when the query statement does not match the query language used by the first database or the second database.
[0017] According to embodiments of this disclosure, the first database is a distributed database, and the second database is a centralized database;
[0018] The first sending module includes: a first sending submodule, configured to send the first transaction request to a first data proxy layer of the first database through the first application layer, so that the first data proxy layer obtains the first query result from the first database layer; and a first receiving submodule, configured to receive the first query result from the first data proxy layer through the first application layer.
[0019] According to embodiments of this disclosure, the heterogeneous database further includes multiple third databases, which are distributed databases isomorphic to the first database; the first sending submodule includes a generation unit, used to generate multiple access requests containing the query information based on the first transaction request using the first data proxy layer, the number of access requests being the same as the number of distributed databases; a sending unit, used to send the access requests to the first database layer and the third database layers of the multiple third databases respectively through the first data proxy layer, so as to obtain multiple third query results from the first database layer and the multiple third database layers respectively; and a merging unit, used to merge the multiple third query results through the first data proxy layer to obtain the first query result.
[0020] According to an embodiment of this disclosure, the merging module includes a local sorting submodule, which is used to merge the first query result and the second query result by using local sorting when the amount of data to be queried is lower than a preset threshold.
[0021] According to an embodiment of this disclosure, the local sorting submodule includes an acquisition unit, configured to sequentially acquire the first query result and the second query result from the first database and the second database according to a preset query order between the first database and the second database, and then sort them.
[0022] According to an embodiment of this disclosure, the merging module further includes a global sorting submodule, which is used to merge the first query result and the second query result by using a global sorting method when the amount of data to be queried is lower than a preset threshold.
[0023] A third aspect of this disclosure provides an electronic device comprising: one or more processors; and a memory for storing one or more programs, wherein, when the one or more programs are executed by the one or more processors, the one or more processors perform the above-described heterogeneous database cross-database query method.
[0024] A fourth aspect of this disclosure also provides a computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, cause the processor to perform the above-described heterogeneous database cross-database query method.
[0025] The fifth aspect of this disclosure also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method for cross-database querying of heterogeneous databases.
[0026] According to the heterogeneous database cross-database query method, apparatus, device, medium, and program product provided in this disclosure, a transaction request containing the same query information is generated by calling a service within the first application layer of the first database. This transaction request is then sent to the second application layer of the second database and the first database layer of the first database. The returned query results are merged within the first application layer of the first database to obtain the cross-database query result. Because data querying is performed through transactions, and independent transaction requests for heterogeneous databases are generated at the application layer, interaction between different layers between heterogeneous databases allows the database layer of the heterogeneous database to return query data. This achieves cross-database querying of heterogeneous databases using database application layer services, without requiring multiple databases to have the same query pattern. Multiple databases can also be deployed in different network systems. Therefore, it at least partially solves the problem that heterogeneous databases cannot be directly queried and accessed using the same query pattern, achieving the technical effect of heterogeneous database cross-database querying. Attached Figure Description
[0027] The foregoing contents, as well as other objects, features, and advantages of this disclosure, will become clearer from the following description of embodiments with reference to the accompanying drawings, in which:
[0028] Figure 1 The illustration schematically depicts an application scenario of a heterogeneous database cross-database query method, apparatus, device, medium, and program product according to embodiments of the present disclosure;
[0029] Figure 2 A flowchart illustrating a method for cross-database querying of heterogeneous databases according to an embodiment of the present disclosure is shown schematically.
[0030] Figure 3 This schematically illustrates a flowchart of obtaining a first query result from a distributed database through a first application layer according to an embodiment of the present disclosure;
[0031] Figure 4 This schematically illustrates a flowchart of a process for a first data proxy layer to obtain a first query result from a first database layer according to an embodiment of the present disclosure;
[0032] Figure 5 A flowchart illustrating a method for cross-database querying of heterogeneous databases according to another embodiment of this disclosure is shown schematically.
[0033] Figure 6 A schematic diagram illustrating a heterogeneous database cross-database query apparatus according to embodiments of the present disclosure is shown; and
[0034] Figure 7 A block diagram schematically illustrates an electronic device suitable for implementing a cross-database query method for heterogeneous databases according to an embodiment of the present disclosure. Detailed Implementation
[0035] The embodiments of the present disclosure will now be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the disclosure. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of the present disclosure for ease of explanation. However, it will be apparent that one or more embodiments may be practiced without these specific details. Furthermore, descriptions of well-known structures and techniques are omitted in the following description to avoid unnecessarily obscuring the concepts of the present disclosure.
[0036] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit this disclosure. The terms “comprising,” “including,” etc., as used herein indicate the presence of the stated features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.
[0037] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein are to be interpreted in a manner consistent with the context of this specification, and not in an idealized or overly rigid way.
[0038] When using expressions such as "at least one of A, B, and C", they should generally be interpreted in accordance with the meaning that is commonly understood by a person skilled in the art (e.g., "a system having at least one of A, B, and C" should include, but is not limited to, a system having A alone, a system having B alone, a system having C alone, a system having A and B, a system having A and C, a system having B and C, and / or a system having A, B, and C, etc.).
[0039] In the technical solutions disclosed herein, the collection, storage, use, processing, transmission, provision, disclosure, and application of data (including but not limited to user personal information) comply with the provisions of relevant laws and regulations, necessary confidentiality measures have been taken, and they do not violate public order and good morals.
[0040] In related technologies, cross-database query refers to querying and merging data in multiple databases, which can be homogeneous or heterogeneous. A common approach is to implement cross-database queries in homogeneous databases, such as distributed databases. However, heterogeneous databases, due to their different schemas, cannot be directly accessed using the same query pattern, and there is currently no satisfactory solution for cross-database queries in heterogeneous databases. In implementing this disclosure, it was discovered that using transaction requests at the database application layer to query data from heterogeneous databases, followed by sorting and merging operations at the same layer, can achieve cross-database queries in heterogeneous databases.
[0041] The embodiments of this disclosure provide a method for cross-database querying of heterogeneous databases. In response to a cross-database query instruction, a service within the first application layer of a first database is invoked to generate a first transaction request and a second transaction request, wherein the first and second transaction requests contain the same query information. The first transaction request is sent from the first application layer to the first database layer of the first database to obtain a first query result based on the query information from the first database layer. A second transaction request is sent from the first application layer to the second application layer of a second database to obtain a second query result based on the query information from the second database layer of the second database. The first and second databases are heterogeneous databases. The first query result and the second query result from the second application layer are merged by the first application layer to obtain a cross-database query result.
[0042] Figure 1 The illustration schematically depicts an application scenario of a heterogeneous database cross-database query method, apparatus, device, medium, and program product according to embodiments of the present disclosure.
[0043] like Figure 1 As shown, application scenario 100 according to this embodiment may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 serves as a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 may include various connection types, such as wired or wireless communication links, or fiber optic cables, etc.
[0044] Users can use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 via the network 104 to receive or send messages, etc. Various communication client applications can be installed on the first terminal device 101, the second terminal device 102, and the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social media platform software, etc. (for example only).
[0045] The first terminal device 101, the second terminal device 102, and the third terminal device 103 can be various electronic devices with displays and support web browsing, including but not limited to smartphones, tablets, laptops, and desktop computers.
[0046] Server 105 can be a server providing various services, including application servers and database servers. The application server is located between the network and the database server, and is used to provide application services, such as a backend management server supporting websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103 (this is just an example). The backend management server can analyze and process received user transaction requests and other data, send transaction requests to the database server, and return the request results to the terminal devices. The database server is mainly used to perform data management tasks such as data analysis, data storage, and data processing. The database architecture disclosed herein mainly includes the application layer where the application server resides and the database layer where the database server resides. It should be noted that the application server and the database server can be the same physical server or multiple independent servers.
[0047] It should be noted that the heterogeneous database cross-database query method provided in this embodiment can generally be executed by server 105. Correspondingly, the heterogeneous database cross-database query device provided in this embodiment can generally be located in server 105. The heterogeneous database cross-database query method provided in this embodiment can also be executed by a server or server cluster that is different from server 105 and capable of communicating with the first terminal device 101, the second terminal device 102, the third terminal device 103, and / or server 105. Correspondingly, the heterogeneous database cross-database query device provided in this embodiment can also be located in a server or server cluster that is different from server 105 and capable of communicating with the first terminal device 101, the second terminal device 102, the third terminal device 103, and / or server 105.
[0048] It should be understood that Figure 1 The number of terminal devices, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of terminal devices, networks, and servers can be included.
[0049] The following will be based on Figure 1 The described scene, through Figures 2-5 The method for cross-database querying of heterogeneous databases according to the disclosed embodiments is described in detail.
[0050] Figure 2 A flowchart illustrating a method for cross-database querying of heterogeneous databases according to an embodiment of the present disclosure is shown.
[0051] like Figure 2 As shown, the heterogeneous database cross-database query method 200 of this embodiment includes operations S210 to S240. This transaction processing method can be executed by a server or a server cluster or other devices.
[0052] In operation S210, in response to the cross-database query instruction, the service within the first application layer of the first database is invoked to generate a first transaction request and a second transaction request, wherein the first transaction request and the second transaction request contain the same query information;
[0053] In operation S220, a first transaction request is sent from the first application layer to the first database layer of the first database to obtain the first query result obtained from the first database layer based on the query information.
[0054] In operation S230, a second transaction request is sent from the first application layer to the second application layer of the second database, so that the second application layer can obtain the second query result based on the query information from the second database layer of the second database. Here, the first database and the second database are heterogeneous databases.
[0055] In operation S240, the first query result and the second query result from the second application layer are merged through the first application layer to obtain the cross-database query result.
[0056] According to embodiments of this disclosure, the first database can be a distributed database, such as a MySQL database, or a centralized database, and the second database can be a centralized database, such as an Oracle database, but is not limited thereto. Alternatively, the first database can be a centralized database, while the second database can be a distributed database.
[0057] According to embodiments of this disclosure, the first database and the second database mainly include an application layer and a database layer in their architecture, respectively. The application layer is used to provide application services, and the database layer is used to provide data services.
[0058] According to embodiments of this disclosure, the databases used for cross-database queries are not limited to the first and second databases; for example, a third database may also be included, and the number is not particularly limited. The third database can be a heterogeneous database with both the first and second databases, or it can be a homogeneous database with either the first or second database. A cross-database query method for homogeneous databases can be coupled on the basis of homogeneous databases.
[0059] According to embodiments of this disclosure, the data to be queried originates from a cross-database query scenario involving multiple distributed and centralized databases, such as a distributed system transformation scenario. In a distributed system transformation scenario, to enable a smooth transition from centralized to distributed systems, and considering the different architectural designs employed in various database products, multiple heterogeneous database cross-database query methods are provided to achieve database independence and support different database products.
[0060] According to embodiments of this disclosure, a cross-database query instruction is a data query instruction issued by the caller. For example, for bank query services, the caller represents clients from different channels, such as mobile banking clients, computer online banking clients, SMS clients, or telephone clients. The cross-database query instruction can be, for example, a balance query instruction for balance query services.
[0061] According to embodiments of this disclosure, since a cross-database query instruction relates to one or more query services, the first transaction request and the second transaction request generated by calling a service within the first application layer also relate to the same query service. The two should contain the same query information, and the cross-database query results obtained based on the same query information can be merged.
[0062] According to embodiments of this disclosure, a transaction request containing the same query information is generated by calling a service within the first application layer of the first database. This transaction request is then sent to the second application layer of the second database and the first database layer of the first database, respectively. The returned query results are merged within the first application layer of the first database to obtain a cross-database query result. Because data querying is performed via transactions, and independent transaction requests for heterogeneous databases are generated at the application layer, interaction between different layers allows the database layers of heterogeneous databases to return query data. This achieves cross-database querying of heterogeneous databases using database application layer services, without requiring multiple databases to have the same query pattern. Multiple databases can also be deployed in different network systems. Therefore, it at least partially solves the problem that heterogeneous databases cannot be directly queried and accessed using the same query pattern, achieving the technical effect of cross-database querying of heterogeneous databases.
[0063] According to embodiments of this disclosure, the query information includes a query identifier and query parameters. The query identifier is used to obtain the query statement, and the query parameters are the parameters of the query statement. For example, for Structured Query Language (SQL), the query identifier SQL ID can be an identifier composed of numbers and letters, and the query parameters are the parameter information required by the query statement, such as the content represented by m and n in the WHERE condition where field a = "m" and field b = "n".
[0064] According to embodiments of this disclosure, the same SQL statement can be generated based on the same query identifier and query parameters, thereby querying data in the first database layer and the second database layer respectively. Thus, the query results obtained for the same query business can be merged at the application layer.
[0065] According to embodiments of this disclosure, the heterogeneous database query method further includes: when the query statement obtained based on the query identifier does not match the query language used by the first database or the second database, converting the query statement into a language consistent with the query language used by utilizing the first application layer of the first database or the second application layer of the second database.
[0066] For example, if the first database uses the more efficient EXISTS syntax, then the IN statement in the query statement obtained based on the query identifier can be converted into an EXISTS statement in the first application layer, thus making it consistent with the EXISTS syntax used by the first database.
[0067] Therefore, even if there are syntactic differences in the query languages of heterogeneous databases, the syntax can be converted to be consistent with the query language of the heterogeneous database, thereby enabling data querying for the same query business in heterogeneous databases.
[0068] According to an embodiment of this disclosure, taking a first database as a distributed database and a second database as a centralized database as an example, the method of obtaining the first query result of the distributed database will be described.
[0069] Figure 3 The flowchart illustrating the process of obtaining a first query result of a distributed database through a first application layer according to an embodiment of the present disclosure is shown.
[0070] In operation S220, such as Figure 3 As shown, it specifically includes sub-operations S221 to S222.
[0071] In sub-operation S221, a first transaction request is sent from the first application layer to the first data proxy layer of the first database, so that the first data proxy layer can obtain the first query result from the first database layer.
[0072] In sub-operation S222, the first query result from the first data proxy layer is received through the first application layer.
[0073] According to embodiments of this disclosure, the method of obtaining the first query result by accessing the first database layer through the first data proxy layer can shield the access relationship between the first application layer and the first database layer. It is understood that if the first database layer is a single database with a one-to-one correspondence with the first data proxy layer, then the first data proxy layer does not need to be set up.
[0074] According to embodiments of this disclosure, the heterogeneous database further includes multiple third databases, which are distributed databases isomorphic to the first database. Therefore, sub-operation S221 may also include cross-database query operations of the isomorphic databases.
[0075] According to embodiments of this disclosure, in some distributed database scenarios, to prevent various business modules from interfering with each other, the application layer can be split according to the distribution of the business modules, allowing each module to access its corresponding database layer independently. For example, based on the north-south geographical distribution of banking business, it can be divided into a first database and a third database. The first database includes a first application layer and a first database layer, and the third database includes a third application layer and a third database layer.
[0076] Figure 4 The flowchart illustrating a first query result obtained by a first data proxy layer from a first database layer according to an embodiment of the present disclosure is shown.
[0077] In suboperation S221, such as Figure 4 As shown, the process of enabling the first data proxy layer to obtain the first query result from the first database layer specifically includes sub-operations S2211 to S2213.
[0078] In sub-operation S2211, the first data proxy layer generates multiple access requests containing query information based on the first transaction request. The number of access requests is the same as the number of distributed databases.
[0079] In sub-operation S2212, access requests are sent through the first data proxy layer to the first database layer and the third database layer of multiple third databases respectively, so as to obtain multiple third query results from the first database layer and the multiple third database layers respectively;
[0080] In sub-operation S2213, multiple third query results are merged through the first data proxy layer to obtain the first query result.
[0081] According to embodiments of this disclosure, a scheme combining homogeneous database cross-database queries with heterogeneous database cross-database queries is implemented through a data proxy layer, thereby achieving centralized and distributed cross-database queries across multiple databases.
[0082] Figure 5 A flowchart illustrating a method for cross-database querying of heterogeneous databases according to another embodiment of this disclosure is shown.
[0083] like Figure 5 As shown, the first database includes a first application layer (i.e., distributed SPU1-AP), a first data proxy layer including a general proxy layer (i.e., super-proxy-n) and two sub-proxy layers (i.e., proxy-n and proxy-s), and a first database layer including a cluster library represented by cylinders. The second database includes a second application layer (i.e., centralized AP), and a second database layer including a cluster library represented by cylinders. The third database has the same structure as the first database, and will not be described further here.
[0084] According to embodiments of this disclosure, the method for cross-database querying of heterogeneous databases includes:
[0085] First, the calling party sends a cross-database query instruction to the first application layer, and the first application layer responds to the cross-database query instruction by generating a first transaction request and a second transaction request.
[0086] Then, the first transaction request is sent to the first data proxy layer through the first application layer. The first data proxy layer converts it into two access requests to access the first database layer and the second database layer respectively. The query results are returned to the first data proxy layer, merged into the first query result in the first data proxy layer, and returned to the first application layer.
[0087] At the same time, the first application layer also sends the second transaction request to the second application layer, which then obtains the second query result from the second database layer and returns the second query result to the first application layer.
[0088] Then, the first query result and the second query result from the second application layer are merged through the first application layer to obtain the cross-database query result.
[0089] According to embodiments of this disclosure, based on the aforementioned heterogeneous database cross-database query method, sorted pagination queries often occur in database pagination queries. Therefore, when merging query results from multiple databases within the application layer, a secondary sorting problem arises in the application layer memory. Specifically, when merging the first and second query results in the first application layer, a global sorting method is typically used to ensure the order and uniqueness of the data sorting. This can lead to memory pressure when the amount of data to be queried is large.
[0090] According to an embodiment of this disclosure, the global sorting method is to obtain all query results from the first database and the second database respectively and sort them according to the alternating query order between the first database and the second database.
[0091] For example, when querying table TABLE1 and sorting by field A, the first database query returns the data as 1, 2, 3, 4, 5, 7, 8, 9, 10, 12. The second database query returns the data as: 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23.
[0092] When performing a paginated query with 5 records per page, to query the first page, it is necessary to obtain the first 5 records in the first database (1, 2, 3, 4, 5) and the first 5 records in the second database (13, 14, 15, 16, 17), then reorder the data in the two databases to 1, 2, 3, 4, 5, 13, 14, 15, 16, 17, and finally retrieve the first 5 records (1, 2, 3, 4, 5).
[0093] When performing a paginated query, with 5 records per page, to query the second page, it is necessary to retrieve the top 10 records in the first database (1, 2, 3, 4, 5, 7, 8, 9, 10, 12) and the top 5 records in the second database (13, 14, 15, 16, 17, 18, 19, 20, 21, 22). The data in both databases are then re-sorted (1, 2, 3, 4, 5, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22). Finally, records 6 through 10 (7, 8, 9, 10, 12) are retrieved.
[0094] This method ensures global sorting of data, but when the query content is on a later page, the number of data pages that need to be retrieved will increase dramatically. The number of data query accesses increases with the number of pages by m (number of pages) * n (number of pages per page) * k (number of database shards), leading to memory pressure.
[0095] According to embodiments of this disclosure, in the case of paginated queries, in order to balance the order and efficiency of data query merging, when merging the first query result and the second query result at the first application layer, a local sorting method can be used to merge the first query result and the second query result.
[0096] Specifically, operation S240 includes: when the amount of data to be queried is lower than a preset threshold, the first application layer uses a local sorting method to merge the first query result and the second query result.
[0097] According to an embodiment of this disclosure, the local sorting method is to sequentially obtain the first query result and the second query result from the first database and the second database according to a preset query order between the first database and the second database, and then sort them.
[0098] For example, the data retrieved from the first database query is 1, 2, 3, 4, 5, 7, 8, 9, 10, 12. The data retrieved from the second database query is: 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23.
[0099] When performing a paginated query with 5 records per page, to retrieve the first page of data, you need to obtain the first 5 records in the database in sequence: 1, 2, 3, 4, 5.
[0100] When performing a paginated query, if there are 5 records on one page, to query the second page of data, it is necessary to retrieve the 6th to 10th records from the first database in the order of 1, 2, 3, 4, 5, 7, 8, 9, 10, 12.
[0101] When performing a paginated query, with 5 records per page, to query the third page of data, it is necessary to retrieve records 11 to 15 (0) from the first database and records 13, 14, 15, 16, and 17 from the second database.
[0102] According to the embodiments of this disclosure, the method of local sorting cannot guarantee the global uniqueness of data, but when the pagination consistency requirement is not high, the query efficiency is higher, achieving the same query efficiency as single-database query.
[0103] According to an embodiment of this disclosure, operation S240 further includes: using the first application layer to merge the first query result and the second query result by global sorting when the amount of data to be queried is lower than a preset threshold.
[0104] According to embodiments of this disclosure, based on the needs of query services, two pagination query modes can be supported simultaneously, and the selection can be configured based on specific transaction requirements, thus balancing the orderliness and efficiency of pagination queries.
[0105] Based on the above-described method for cross-database querying of heterogeneous databases, this disclosure also provides a device for cross-database querying of heterogeneous databases. The following will combine... Figure 6 The device is described in detail.
[0106] Figure 6 A schematic block diagram of a heterogeneous database cross-database query apparatus according to an embodiment of the present disclosure is shown.
[0107] like Figure 6 As shown, the heterogeneous database cross-database query device 600 of this embodiment includes a generation module 610, a first sending module 620, a second sending module 630, and a merging module 640.
[0108] The generation module 610 is used to respond to a cross-database query instruction by calling an application-layer service of the first database to generate a first transaction request and a second transaction request, wherein the first transaction request and the second transaction request contain the same query information. In one embodiment, the generation module 610 can be used to perform the operation S210 described above, which will not be repeated here.
[0109] The first sending module 620 is used to send a first transaction request to the first database layer of the first database through the first application layer, so as to obtain a first query result based on query information from the first database layer. In one embodiment, the first sending module 620 can be used to perform the operation S220 described above, which will not be repeated here.
[0110] The second sending module 630 is used to send a second transaction request to the second application layer of the second database through the first application layer, so that the second application layer can obtain a second query result based on query information from the second database layer of the second database. In one embodiment, the second sending module 630 can be used to perform the operation S230 described above, which will not be repeated here.
[0111] The merging module 640 is used to merge the first query result and the second query result from the second application layer through the first application layer to obtain the cross-database query result.
[0112] According to embodiments of this disclosure, the query information includes a query identifier and query parameters. The query identifier is used to obtain a query statement, and the query parameters are the parameters of the query statement.
[0113] According to embodiments of this disclosure, the heterogeneous database cross-database query apparatus further includes: a conversion module, used to convert the query statement into a query language consistent with the query language used by the first application layer of the first database or the second application layer of the second database when the query statement does not conform to the query language used by the first database or the second database.
[0114] According to embodiments of this disclosure, the first database is a distributed database, and the second database is a centralized database;
[0115] The first sending module includes: a first sending submodule, configured to send a first transaction request to a first data proxy layer of the first database through the first application layer, so that the first data proxy layer obtains a first query result from the first database layer; and a first receiving submodule, configured to receive the first query result from the first data proxy layer through the first application layer.
[0116] According to embodiments of this disclosure, the heterogeneous database further includes multiple third databases, which are distributed databases isomorphic to the first database; the first sending submodule includes a generation unit, used to generate multiple access requests containing query information based on a first transaction request using a first data proxy layer, the number of access requests being the same as the number of distributed databases; a sending unit, used to send access requests to the first database layer and the third database layers of the multiple third databases respectively through the first data proxy layer, so as to obtain multiple third query results from the first database layer and the multiple third database layers respectively; and a merging unit, used to merge the multiple third query results through the first data proxy layer to obtain a first query result.
[0117] According to embodiments of this disclosure, the merging module includes a local sorting submodule, which is used by the first application layer to merge the first query result and the second query result by using local sorting when the amount of data to be queried is lower than a preset threshold.
[0118] According to an embodiment of this disclosure, the local sorting submodule includes an acquisition unit, which is used to sequentially acquire and sort the first query result and the second query result from the first database and the second database according to a preset query order between the first database and the second database.
[0119] According to embodiments of this disclosure, the merging module further includes a global sorting submodule, which is used to merge the first query result and the second query result by using a global sorting method when the amount of data to be queried is lower than a preset threshold, utilizing the first application layer.
[0120] According to embodiments of this disclosure, any plurality of modules among the generation module 610, the first transmission module 620, the second transmission module 630, and the merging module 640 may be merged into one module, or any one of these modules may be split into multiple modules. Alternatively, at least a portion of the functionality of one or more of these modules may be combined with at least a portion of the functionality of other modules and implemented in one module. According to embodiments of this disclosure, at least one of the generation module 610, the first transmission module 620, the second transmission module 630, and the merging module 640 may be at least partially implemented as hardware circuitry, such as a field-programmable gate array (FPGA), a programmable logic array (PLA), a system-on-a-chip, a system-on-a-substrate, a system-on-package, an application-specific integrated circuit (ASIC), or implemented in hardware or firmware by any other reasonable means of integrating or packaging the circuitry, or implemented in any one of software, hardware, and firmware methods, or in a suitable combination of any of these methods. Alternatively, at least one of the generation module 610, the first sending module 620, the second sending module 630, and the merging module 640 may be implemented at least partially as a computer program module, which can perform corresponding functions when the computer program module is run.
[0121] Figure 7 A block diagram schematically illustrates an electronic device suitable for implementing a cross-database query method for heterogeneous databases according to an embodiment of the present disclosure.
[0122] like Figure 7As shown, an electronic device 700 according to an embodiment of the present disclosure includes a processor 701, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 702 or a program loaded from a storage portion 708 into a random access memory (RAM) 703. The processor 701 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or an associated chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 701 may also include onboard memory for caching purposes. The processor 701 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present disclosure.
[0123] RAM 703 stores various programs and data required for the operation of electronic device 700. Processor 701, ROM 702, and RAM 703 are interconnected via bus 704. Processor 701 performs various operations of the method flow according to embodiments of the present disclosure by executing programs in ROM 702 and / or RAM 703. It should be noted that programs may also be stored in one or more memories other than ROM 702 and RAM 703. Processor 701 may also perform various operations of the method flow according to embodiments of the present disclosure by executing programs stored in one or more memories.
[0124] According to embodiments of this disclosure, the electronic device 700 may further include an input / output (I / O) interface 705, which is also connected to a bus 704. The electronic device 700 may also include one or more of the following components connected to the I / O interface 705: an input section 706 including a keyboard, mouse, etc.; an output section 707 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 708 including a hard disk, etc.; and a communication section 709 including a network interface card such as a LAN card, modem, etc. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. A removable medium 711, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 710 as needed so that computer programs read from it can be installed into the storage section 708 as needed.
[0125] This disclosure also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or it may exist independently and not assembled into the device / apparatus / system. The computer-readable storage medium carries one or more programs that, when executed, implement the method according to the embodiments of this disclosure.
[0126] According to embodiments of this disclosure, the computer-readable storage medium may be a non-volatile computer-readable storage medium, such as, but not limited to: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this disclosure, the computer-readable storage medium may be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. For example, according to embodiments of this disclosure, the computer-readable storage medium may include ROM 702 and / or RAM 703 and / or one or more memories other than ROM 702 and RAM 703 described above.
[0127] Embodiments of this disclosure also include a computer program product comprising a computer program containing program code for performing the methods shown in the flowchart. When the computer program product is run on a computer system, the program code is used to cause the computer system to implement the item recommendation method provided in the embodiments of this disclosure.
[0128] When the computer program is executed by the processor 701, it performs the functions defined in the system / apparatus of this disclosure embodiments. According to embodiments of this disclosure, the systems, apparatuses, modules, units, etc., described above can be implemented by computer program modules.
[0129] In one embodiment, the computer program may rely on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may also be transmitted and distributed in the form of signals over a network medium, and may be downloaded and installed via the communication section 709, and / or installed from a removable medium 711. The program code contained in the computer program can be transmitted using any suitable network medium, including but not limited to: wireless, wired, etc., or any suitable combination thereof.
[0130] In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 709, and / or installed from the removable medium 711. When the computer program is executed by the processor 701, it performs the functions defined in the system of this disclosure embodiment. According to embodiments of this disclosure, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.
[0131] According to embodiments of this disclosure, program code for executing the computer programs provided in embodiments of this disclosure can be written in any combination of one or more programming languages. Specifically, these computational programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages include, but are not limited to, languages such as Java, C++, Python, "C", or similar programming languages. The program code can execute entirely on the user's computing device, partially on the user's device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).
[0132] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this disclosure. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.
[0133] Those skilled in the art will understand that the features described in the various embodiments and / or claims of this disclosure can be combined or combined in various ways, even if such combinations or combinations are not explicitly described in this disclosure. In particular, the features described in the various embodiments and / or claims of this disclosure can be combined or combined in various ways without departing from the spirit and teachings of this disclosure. All such combinations and / or combinations fall within the scope of this disclosure.
[0134] The embodiments of this disclosure have been described above. However, these embodiments are for illustrative purposes only and are not intended to limit the scope of this disclosure. Although various embodiments have been described above, this does not mean that the measures in the various embodiments cannot be used advantageously in combination. The scope of this disclosure is defined by the appended claims and their equivalents. Various substitutions and modifications can be made by those skilled in the art without departing from the scope of this disclosure, and all such substitutions and modifications should fall within the scope of this disclosure.
Claims
1. A method for cross-database queries in heterogeneous databases, applicable to both homogeneous and heterogeneous database cross-database queries, comprising: In response to a cross-database query command, a service within the first application layer of the first database is invoked to generate a first transaction request and a second transaction request. The first and second transaction requests contain the same business query information, enabling the generation of query statements corresponding to the first database layer of the first database and the second application layer of the second database, respectively, based on this same business query information. The business query information includes a query identifier and query parameters. The query identifier is used to obtain the query statement, and the query parameters are the parameters of the query statement. The first and second databases are architecturally comprised of an application layer and a database layer, respectively. The application layer provides application services, and the database layer provides data services. The first database is a distributed database, and the second database is a centralized database. The system also includes multiple third databases, which are distributed databases isomorphic to the first database. Sending the first transaction request to the first database layer of the first database through the first application layer to obtain a first query result based on the query information from the first database layer, this is implemented as a cross-database query of homogeneous databases through a data proxy layer, including: sending access requests to the first database layer and the third database layers of multiple third databases through the first data proxy layer to obtain multiple third query results from the first database layer and the multiple third database layers respectively; merging the multiple third query results through the first data proxy layer to obtain a first query result; and sending the second transaction request to the second application layer of the second database through the first application layer to enable the second application layer to obtain a second query result based on the query information from the second database layer of the second database, wherein the first database and the second database are heterogeneous databases; The first query result and the second query result from the second application layer are merged by the first application layer to obtain the cross-database query result.
2. The heterogeneous database cross-database query method according to claim 1 further includes: If the query statement does not match the query language used by the first database or the second database, the query statement is converted to match the query language used by the first application layer of the first database or the second application layer of the second database.
3. The heterogeneous database cross-database query method according to claim 1, wherein, The step of sending the first transaction request to the database layer of the first database through the first application layer to obtain the first query result based on the query information from the first database layer includes: The first application layer sends the first transaction request to the first data proxy layer of the first database, so that the first data proxy layer can obtain the first query result from the first database layer. The first query result is received from the first data proxy layer through the first application layer.
4. The heterogeneous database cross-database query method according to claim 3 further includes: The first data proxy layer generates multiple access requests containing the query information based on the first transaction request, and the number of access requests is the same as the number of distributed databases.
5. The method for cross-database querying of heterogeneous databases according to any one of claims 1 to 4, wherein, The first application layer merges the first query result with the second query result from the second application layer to obtain cross-database query results, including: When the amount of data to be queried is less than a preset threshold, the first application layer uses a local sorting method to merge the first query result and the second query result.
6. The method for cross-database querying of heterogeneous databases according to claim 5, wherein, The step of merging the first query result and the second query result using a local sorting method includes: According to the preset query order between the first database and the second database, the first query result and the second query result are obtained from the first database and the second database in sequence and then sorted.
7. The method for cross-database querying of heterogeneous databases according to claim 1, wherein, The process of merging the first query result from the application layer of the first database with the second query result from the application layer of the second database to obtain a cross-database query result also includes: When the amount of data to be queried is less than a preset threshold, the first application layer uses a global sorting method to merge the first query result and the second query result.
8. A heterogeneous database cross-database query device, used for both homogeneous database cross-database queries and heterogeneous database cross-database queries, comprising: A generation module is used to respond to a cross-database query command by calling services within the application layer of the first database to generate a first transaction request and a second transaction request. The first and second transaction requests contain the same business query information, enabling the generation of query statements corresponding to the first database layer of the first database and the second application layer of the second database, respectively. The query information includes a query identifier and query parameters. The query identifier is used to obtain the query statement, and the query parameters are the parameters of the query statement. The first and second databases are architecturally comprised of an application layer and a database layer, respectively. The application layer provides application services, and the database layer provides data services. The first database is a distributed database, and the second database is a centralized database. The module also includes multiple third databases, which are distributed databases isomorphic to the first database. The first sending module is configured to send the first transaction request to the first database layer of the first database through the first application layer, so as to obtain the first query result based on the query information from the first database layer. This is implemented by performing a cross-database query of homogeneous databases through a data proxy layer, including: sending access requests to the first database layer and the third database layers of multiple third databases through the first data proxy layer, so as to obtain multiple third query results from the first database layer and the multiple third database layers respectively; and merging the multiple third query results through the first data proxy layer to obtain the first query result. The second sending module is configured to send the second transaction request to the second application layer of the second database through the first application layer, so that the second application layer obtains a second query result based on the query information from the second database layer of the second database; and The merging module is used to merge the first query result and the second query result from the second application layer through the first application layer to obtain a cross-database query result.
9. An electronic device, comprising: One or more processors; Storage device for storing one or more programs. Wherein, when the one or more programs are executed by the one or more processors, the one or more processors perform the method according to any one of claims 1 to 7.
10. A computer-readable storage medium having stored thereon executable instructions that, when executed by a processor, cause the processor to perform the method according to any one of claims 1 to 7.
11. A computer program product comprising a computer program that, when executed by a processor, implements the method according to any one of claims 1 to 7.
Citation Information
Patent Citations
Cross-heterogeneous data source real-time connection method and apparatus
CN108733724A
Query method and device based on heterogeneous database
CN111581231A
