Method for constructing dblink access under distributed transactional database

Abstract

The invention relates to the technical field of databases, and discloses a dblink access construction method under a distributed transactional database, an external table and a table space are created as required when the external table is accessed for the first time, the external table is stored on a coordination node and a storage node of the distributed transactional database, and the external table is directly called to access an external database during subsequent access. By dynamically executing plan selection, predicate push-down optimization and network path optimization, the cross-library query efficiency is effectively improved, the system resource consumption is reduced, the expansibility is enhanced, and the problems of low access efficiency, intermediate table redundancy, system table expansion and the like of the traditional dblink are solved.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a method for constructing dblink access under a distributed transaction database. Background Art

[0002] As shown in the Figure 1 accompanying drawings, a traditional distributed database consists of storage nodes (Data Node, abbreviated as DN) and coordinator nodes (Coordinator Node, abbreviated as CN). The coordinator node (CN) is responsible for receiving client requests and decomposing tasks to each storage node (DN) for execution according to the location of data storage. The dblink task is also sent to the storage node (DN). Sending all dblink tasks to the storage node (DN) results in the data link for obtaining data from an external database being: external database → storage node (DN) → coordinator node (CN) → client. There is a large network latency and low efficiency.

[0003] Between a local table and an external table in a traditional dblink, there is an intermediate table for mapping, with low query efficiency and it will affect predicate pushdown. Creating a large number of mapping relationships when creating a dblink causes the system table to expand sharply, affecting subsequent execution efficiency.

[0004] To solve the above problems, the present invention proposes a method for constructing dblink access under a distributed transaction database. Summary of the Invention

[0005] The present invention aims to solve at least one of the technical problems existing in the related art. For this purpose, the present invention provides a method for constructing dblink access under a distributed transaction database, aiming to solve the performance bottleneck problem of cross-database access in a distributed database scenario.

[0006] A method for constructing dblink access under a distributed transaction database includes the following steps: Receiving a command to create a dblink and storing the connection information of the dblink on multiple nodes of the distributed transaction database; When a request to access a table in an external database through a dblink is received for the first time, obtaining the structure definition of the table in the external database on multiple nodes of the distributed transaction database, and creating an external table corresponding to the table in the external database for access according to the structure definition and the dblink name in the connection information; When a query request to access an external table through a dblink is received, using the external table and selecting a corresponding query execution path according to the characteristics of the query request to obtain a query result.

[0007] Further, the steps of creating the external table include: Automatically create a tablespace on multiple nodes of the distributed transaction database, and create it only when the tablespace does not exist; Create an external table within the tablespace; The name of the tablespace is generated by concatenating the dblink name and name_space; The name_space is an identifier for space naming.

[0008] Further, the steps of accessing the external table include: Locate the tablespace based on the dblink name and the identifier of the namespace; Execute a query through the external table in the tablespace, and push down the filtering conditions in the query to the external database.

[0009] Further, use dblink to complete multiple distributed execution plans, and dynamically adjust the operator execution between the storage node and the coordination node, The multiple distributed execution plans include LightProxy, Fast Query Shipping, Stream, and RemoteQuery; When executing the distributed execution plan, the coordination node directly accesses the external table without passing through the storage node.

[0010] Further, when the query involves external tables of multiple dblinks, the coordination node decomposes the query into multiple sub-queries; Send the sub-queries to the corresponding external databases for execution respectively; The coordination node merges the results returned by each external database.

[0011] Further, when all tables in the query are under the same dblink and do not involve local tables, The computing node directly sends the SQL of the client to the external database to obtain data; The external database returns the result to the computing node, and then the computing node returns it to the client.

[0012] Further, when the external table is combined with the local table, select a distributed execution plan according to the predicate pushdown and operator pushdown rules.

[0013] Further, the multiple nodes of the distributed transaction database include a coordination node and a storage node.

[0014] Further, the connection information includes the dblink name, the corresponding login account, password, ip of the external database, port, and name of the external database; The connection information is extracted by parsing the dblink creation command input by the user and stored in the system tables of all coordination nodes and storage nodes.

[0015] Furthermore, when the external table corresponding to the table in the external database to be accessed already exists, the subsequent access reuses the created external table.

[0016] One or more of the above technical solutions in the embodiments of the present invention have at least one of the following technical effects: 1. Reduce system table expansion: Only create the corresponding external table when accessing the external table for the first time, avoiding the problem of system table expansion caused by the large-scale creation of intermediate tables in the traditional method; creating external tables on demand can save a large number of external table counts and improve system access efficiency.

[0017] 2. Improve query efficiency: Dynamic execution path selection: Adapt to different query scenarios through distributed execution plans such as LightProxy and RemoteQuery, and dynamically adjust the operations of the dblink operator between the coordination node (CN) and the storage node (DN).

[0018] Predicate pushdown optimization: Push the filtering conditions down to the external database for execution to reduce the data transmission volume.

[0019] 3. Improve network latency: When the query only involves external tables under the same dblink, the CN directly interacts with the external database, reducing the DN node level and shortening the data link (external database → CN → client).

[0020] 4. Enhance scalability: The tablespace name is generated by concatenating the dblink with the external tablespace name, enabling quick location of the external table, reducing intermediate links, and directly and quickly obtaining the external table definition through the dblink name and tablespace name, greatly improving the external table access efficiency.

[0021] Support multiple execution plans (such as Stream, Fast Query Shipping) to adapt to complex query scenarios.

[0022] The additional aspects and advantages of the present invention will be partially given in the following description, partially become obvious from the following description, or be understood through the practice of the present invention. Brief Description of the Drawings

[0023] To more clearly illustrate the technical solutions in the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0024] Figure 1 is the network structure diagram of the existing distributed transaction database dblink access.

[0025] Figure 2 is the network structure diagram of the dblink access implemented by the distributed transaction database provided by the present invention. Detailed implementation manners

[0026] To make the objectives, technical solutions and advantages of the present invention clearer, the following will clearly and completely describe the technical solutions in the present invention with reference to the drawings in the present invention. Obviously, the described embodiments are some, but not all, of the embodiments of the present invention. Based on the embodiments in the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts fall within the protection scope of the present invention. The following embodiments are used to illustrate the present invention, but cannot be used to limit the scope of the present invention.

[0027] The following combines Figure 2 to describe the solution for the dblink access to the distributed transaction database implemented by the present invention.

[0028] The method for constructing the dblink access based on the distributed transaction database implemented by the present invention includes the following steps: I. Improvement steps of "automatically creating external tables on demand, reducing the number of external tables, avoiding system table expansion and affecting efficiency" and "automatically creating a tablespace name by concatenating the db_link name and the external tablespace name as the tablespace name for storing the corresponding external tables, realizing fast search for external tables, improving efficiency and reducing the intermediate table link" at all coordinating nodes (CNs) and storage nodes (DNs): Step 1. When a client connects to a remote database to create a db_link by specifying a user name and password, the connection information corresponding to the db_link is saved in the system tables of each storage node (DN) and coordinating node (CN). The connection information includes the dblink name, the corresponding login account, password, the ip of the external database, port and the name of the external database.

[0029] Step 2. When the customer queries data remotely through a database link and accesses a table in an external database, if this is the first time the cluster accesses this table, external tables corresponding to this table are created on each storage node (DN) and computing node (CN) of the cluster. The creation method and steps are as follows: 1) Obtain the structure definition of the table from the external database. If the table definition is obtained successfully, proceed to the next step; 2) Automatically create a new tablespace named by concatenating db_link and name_space on all coordinator nodes (CNs) and storage nodes (DNs). If this tablespace already exists, it will not be created repeatedly. name_space is the identifier for naming the space.

[0030] For example: for the external table name_space.table_name@db_link, the db_link_name_space tablespace is automatically created to store the table_name external table, so that the tablespace where the external table is located can be quickly locked through dblink and name_space.

[0031] 3) On all coordinator nodes (CNs) and storage nodes (DNs), create external tables corresponding to this table on each node under this tablespace.

[0032] Step 3. When the customer subsequently accesses the table in this external database again, the external tables created during the first access can be directly used. The process of the second access is as follows: 1) Use the concatenation of db_link and name_space as the tablespace name to directly access the corresponding external table quickly through this tablespace name, reducing intermediate links and achieving efficient access to the external table.

[0033] 2) When using the automatically created external table to access the database, predicate pushdown can be achieved. The data pulled when accessing the external table is saved, improving efficiency.

[0034] 3) Since each storage node (DN) and computing node (CN) has this external table, all distributed execution plans such as LightProxy, Fast Query Shipping, Stream, and RemoteQuery can be adapted. The pull-up and push-down between the storage node (DN) and the computing node (CN) nodes of each operator can be realized.

[0035] 4) Since the corresponding external tables are only created when accessing the tables in the external database for the first time, the number of external tables is very small. Compared with creating all tables in the external database as external tables, the system tables are much smaller, improving the operating efficiency of the system.

[0036] The adaptation scenarios of dblink in each distributed execution plan are listed in Table 1.

[0037] Table 1

[0038] II. Further optimization of the access parsing process for the distributed execution plan for dblink. The optimized access parsing process is as follows: When the client accesses external tables under the same dblink and there is no interaction with internal tables, the computing node (CN) directly sends the client's SQL to the external database to obtain data, reducing the storage node (DN) link and improving the dblink access efficiency. Step 1: The computing node (CN) completes lexical parsing of the SQL statement for querying data that meets specific conditions in the remote database. When performing semantic parsing, if it is found that all table names in the SQL are the same dblink, the client's SQL is directly sent to the external database to obtain data, reducing the storage node (DN) link and improving the dblink access efficiency. Pushing down where a = 1 to the external database for execution is a typical scenario of predicate pushdown.

[0039] Step 2: The external database directly returns the result to the computing node (CN), and then the computing node (CN) returns it to the client.

[0040] When the client accesses external tables under different dblinks and there is no interaction with internal tables, the computing node (CN) directly sends the SQL after decomposing the task to different external databases to obtain data, and then performs calculations at the computing node (CN), reducing the storage node (DN) link and improving the dblink access efficiency. Step 1: The computing node (CN) completes lexical parsing of the SQL query statement that simultaneously accesses tables in different remote databases. When performing semantic parsing, if it is found that all table names in the SQL are external tables and are in different dblinks, the client's SQL is directly sent to the external database to obtain data, reducing the storage node (DN) link and improving the dblink access efficiency.

[0041] Step 2: The external database directly returns the result to the computing node (CN), and then the computing node (CN) returns it to the client.

[0042] The above method realizes the fast access to external tables in a distributed database through various methods such as reducing the number of external tables, quickly accessing external tables through the combination of dblink name and tablespace name, predicate pushdown of external tables, and optimization of the distributed execution plan for dblink.

[0043] A specific implementation example is given. Step 1. When the customer uses the command "CREATE DATABASE LINK db_link CONNECT TO user_name IDENTIFIED BY password USING 'ip:port / db_name';" to create a dblink, the login account, password, ip of the external database, port, and database name corresponding to the dblink are saved in the system tables of each storage node (DN) and coordinator node (CN).

[0044] Step 2. When the customer uses " name _space.table _name@db_link;" to access the table of the external database, if it is the first time for this cluster to access this table, then create an external table corresponding to this table in each storage node (DN) and computing node (CN) of the cluster. The creation method and steps are as follows: Obtain the structure definition of the table from the external database. If the table definition is obtained successfully, then proceed to the next step; Automatically create a new tablespace named by concatenating db_link and name_space, and create this tablespace in each node of the cluster. If this tablespace already exists, it will not be created repeatedly; In each node of the cluster, create an external table corresponding to this table under this tablespace in each node.

[0045] Step 3. When the customer subsequently accesses the table of the external database again through "name_space.table_name@db_link;", the external table created during the first access can be directly used.

[0046] The access process for the second and subsequent times is as follows: Use the concatenation of db_link and name_space as the tablespace name, and directly access the corresponding external table quickly through db_link_name_space.table_name, reducing the intermediate links and achieving efficient access to the external table.

[0047] When using the automatically created external table to access the database, predicate pushdown can be achieved. It saves the data pulled when accessing the external table and improves the efficiency.

[0048] Since each storage node (DN) and computing node (CN) has this external table, it can adapt to all distributed execution plans such as LightProxy, FastQuery Shipping, Stream, RemoteQuery, etc. It can achieve the pull-up and push-down between the storage node (DN) and computing node (CN) nodes for each operator.

[0049] Since the corresponding external table is created only when the table in the external database is accessed for the first time, the number of external tables is very small. Compared with creating all tables in the external database as external tables, the system table is much smaller, improving the operating efficiency of the system.

[0050] Step 4. When the operation node (CN) finishes lexical analysis of the received SQL (" name_space.table_name@db_link where a=1;"), and performs semantic analysis, if it is found that all table names (name_space.table_name@db_link) in the SQL are for the same dblink (@db_link), the SQL of the client is directly sent to the external database to obtain data, reducing the storage node (DN) link and improving the dblink access efficiency. Pushing down where a=1 to the external database for execution is a typical scenario of predicate pushdown.

[0051] Step 5. The external database directly returns the result to the operation node (CN), and then the operation node (CN) returns it to the client.

[0052] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements for some of the technical features. However, such modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for constructing dblink access under a distributed transaction database, characterized in that It includes the following steps: Receive a command to create a dblink, and store the connection information of the dblink on multiple nodes of the distributed transaction database; When a request to access a table in an external database through a dblink is received for the first time, on multiple nodes of the distributed transaction database, obtain the structure definition of the table in the external database, and create an external table corresponding to the table in the external database according to the structure definition and the dblink name in the connection information; When a query request to access an external table through a dblink is received, use the external table and select a corresponding query execution path according to the characteristics of the query request to obtain the query result.

2. The method for constructing dblink access under the distributed transaction database according to claim 1, wherein: The step of creating the external table includes: Automatically create a tablespace on multiple nodes of the distributed transaction database, and create it only when the tablespace does not exist; Create an external table within the tablespace; The name of the tablespace is generated by concatenating the dblink name and name_space; The name_space is an identifier for space naming.

3. The method for constructing dblink access under the distributed transaction database according to claim 2, wherein: The step of accessing the external table includes: Locate the tablespace based on the dblink name and the identifier of the namespace; Execute the query through the external table in the tablespace, and push down the filter conditions in the query to the external database.

4. The method for constructing dblink access under the distributed transaction database according to claim 3, wherein: Use dblink to complete multiple distributed execution plans, and dynamically adjust the operator execution between the storage node and the coordination node, The multiple distributed execution plans include LightProxy, Fast Query Shipping, Stream, and RemoteQuery; When executing the distributed execution plan, the coordination node directly accesses the external table without passing through the storage node.

5. The method for constructing dblink access under the distributed transaction database according to claim 4, wherein: When a query involves external tables of multiple dblinks, the coordination node decomposes the query into multiple sub-queries; Send the sub-queries to the corresponding external databases for execution respectively; The coordination node merges the results returned by each external database.

6. The method for constructing dblink access under the distributed transaction database according to claim 4, wherein: When all tables in the query are under the same dblink and do not involve local tables, The computing node directly sends the SQL of the client to the external database to obtain data; The external database returns the result to the computing node, and then the computing node returns it to the client.

7. The method for constructing dblink access under the distributed transaction database according to claim 4, wherein: When an external table is combined with a local table, select a distributed execution plan according to the predicate push-down and operator push-down rules.

8. The method for constructing dblink access under a distributed transaction database according to claim 1, wherein the multiple nodes of the distributed transaction database include a coordination node and storage nodes.

9. The method for constructing dblink access under a distributed transaction database according to claim 1, wherein the connection information includes the dblink name, the corresponding login account, password, IP of the external database, port, and name of the external database; the connection information is extracted by parsing the dblink creation command input by the user and stored in the system tables of all coordination nodes and storage nodes.

10. The method for constructing dblink access under a distributed transaction database according to claim 1, wherein when the external table corresponding to the table in the external database to be accessed already exists, subsequent accesses reuse the created external table.

Images