A dblink access construction method under a distributed transaction database

Abstract

The application relates to the technical field of databases, and discloses a dblink access construction method under a distributed transaction database, an external table and a table space are created on demand 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 transaction database, subsequent access directly calls the external table to access an external database, dynamic execution plan selection, predicate push-down optimization, and network path optimization are adopted, cross-database query efficiency is effectively improved, system resource consumption is reduced, and expansibility is enhanced, and problems such as low dblink access efficiency, intermediate table redundancy, and system table expansion in the prior art are solved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a dblink access construction method under a distributed transaction database. BACKGROUND

[0002] As shown in the accompanying drawings, Figure 1 A traditional distributed database is composed of a storage node (Data Node, referred to as DN) and a coordination node (Coordinator Node, referred to as CN). The coordination node (CN) is responsible for receiving a client request, and according to the location of data storage, the task is decomposed to each storage node (DN) for execution, and the dblink task is also issued to the storage node (DN). The dblink task is all issued to the storage node (DN), resulting in a data link for obtaining an external database: external database→storage node (DN)→coordination node (CN)→client. The network delay is large, and the efficiency is low.

[0003] The traditional dblink has an intermediate table as a mapping between a local table and an external table, the query efficiency is low, and the predicate pushdown is affected. When creating the dblink, a large number of mapping relationships are created, resulting in a sharp expansion of the system table, and affecting the subsequent execution efficiency.

[0004] In order to solve the above problems, the present application provides a construction method for realizing dblink access under a distributed transaction database. SUMMARY

[0005] The present application aims to at least solve one of the technical problems in the related art. To this end, the present application provides a dblink access construction method under a distributed transaction database, aiming to solve the performance bottleneck problem of cross-database access in the distributed database scenario.

[0006] A dblink access construction method under a distributed transaction database, comprising the following steps:

[0007] Receiving a command for creating a dblink, and storing connection information of the dblink on multiple nodes of the distributed transaction database;

[0008] When a request for accessing a table in an external database through the dblink is first received, the structure definition of the table in the external database is obtained on the multiple nodes of the distributed transaction database, and according to the structure definition and the dblink name in the connection information, an external table corresponding to the table in the external database is created for accessing the external table;

[0009] When a query request for accessing an external table through the dblink is received, the external table is used and according to the characteristics of the query request, a corresponding query execution path is selected to obtain a query result.

[0010] Further, the step of creating the external table comprises:

[0011] automatically creating a table space on the multiple nodes of the distributed transaction database, and creating only when the table space does not exist;

[0012] creating an external table in the table space;

[0013] the name of the table space is generated by concatenating the dblink name and the name_space;

[0014] the name_space is an identifier of the space naming.

[0015] Further, the step of accessing the external table comprises:

[0016] locating the table space based on the dblink name and the identifier of the naming space;

[0017] executing a query through the external table in the table space, and pushing down filter conditions in the query to the external database.

[0018] Further, multiple distributed execution plans are completed by using the dblink, and operators are executed dynamically between the storage node and the coordination node,

[0019] the multiple distributed execution plans include LightProxy, Fast Query Shipping, Stream and RemoteQuery;

[0020] when the distributed execution plan is executed, the coordination node directly accesses the external table without going through the storage node.

[0021] Further, when the query involves multiple external tables of the dblink, the query is decomposed into multiple sub-queries by the coordination node;

[0022] the sub-queries are respectively issued to the corresponding external databases for execution;

[0023] the results returned by the external databases are combined by the coordination node.

[0024] Further, when all tables in the query are located under the same dblink and do not involve local tables,

[0025] the coordination node directly issues the SQL of the client to the external database to obtain data;

[0026] the external database returns the results to the coordination node, which then returns the results to the client.

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

[0028] Further, the plurality of nodes of the distributed transaction database comprises a coordination node and a storage node.

[0029] Further, the connection information comprises a dblink name, a corresponding login account, a password, an IP of the external database, a port, and a name of the external database.

[0030] The connection information is extracted by parsing a dblink creation command input by a user, and is stored in a system table of all coordination nodes and storage nodes.

[0031] Further, when the table in the external database to be accessed corresponds to an existing external table, subsequent access reuses the created external table.

[0032] The one or more technical solutions in the embodiments of the present application have at least one of the following technical effects:

[0033] 1. Reducing system table expansion: the corresponding external table is created only when the external table is accessed for the first time, thereby avoiding the system table expansion problem caused by the large number of intermediate tables created in the traditional method; the external table is created on demand, thereby greatly saving the number of external tables and improving the system access efficiency.

[0034] 2. Improving query efficiency:

[0035] Dynamic execution path selection: the LightProxy, RemoteQuery and other distributed execution plans are adapted to different query scenarios, and the operation of the dblink operator between the coordination node (CN) and the storage node (DN) is dynamically adjusted.

[0036] Predicate pushdown optimization: the filtering condition is pushed down to the external database for execution, thereby reducing the data transmission amount.

[0037] 3. Improving network delay:

[0038] When the query only involves the external tables under the same dblink, the CN directly interacts with the external database, thereby reducing the DN node level and shortening the data link (external database -> CN -> client).

[0039] 4. Enhancing scalability:

[0040] The table space name is generated by splicing the dblink and the external table space name, thereby realizing quick positioning of the external table, reducing intermediate links, and enabling the dblink name and the table space name to directly and quickly obtain the external table definition, thereby greatly improving the external table access efficiency.

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

[0042] Additional aspects and advantages of the application will be set forth in part in the description which follows, and in part will become apparent to those skilled in the art upon examination of the following description, or can be learned by practice of the application. BRIEF DESCRIPTION OF DRAWINGS

[0043] In order to more clearly illustrate the technical solutions in the present application or the prior art, the following will briefly introduce the drawings needed to be used in the embodiments or the prior art description. Obviously, the drawings in the following description are some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor on the basis of these drawings.

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

[0045] Figure 2 is a network structure diagram of the dblink access implemented by the distributed transaction database provided by the present application. DETAILED DESCRIPTION

[0046] In order to make the objects, technical solutions and advantages of the present application clearer, the technical solutions in the present application will be described clearly and completely in the following combined with the drawings in the present application. Obviously, the described embodiments are some embodiments of the present application, not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor belong to the protection scope of the present application. The following embodiments are used to illustrate the present application, but cannot be used to limit the scope of the present application.

[0047] The following will be combined Figure 2 to describe the scheme of the dblink access distributed transaction database implemented by the present application.

[0048] The dblink access construction method implemented by the distributed transaction database in the present application comprises the following steps:

[0049] I. In all coordination nodes (CN) and storage nodes (DN), the 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 table space name spliced by the db_link name and the external table space name as the table space name for storing the corresponding external table, realizing fast searching of external tables, improving efficiency and reducing intermediate table links":

[0050] Step 1, when the client connects to the remote database to create a db_link by specifying a username and password, the connection information corresponding to the db_link is saved in the system table of each storage node (DN) and coordination node (CN), including the dblink name, corresponding login account, password, external database IP, port, and external database name.

[0051] Step 2, when the client performs data query operations from the remote through the database link and accesses the table of the external database, if it is the first time that the cluster accesses the table, an external table corresponding to the table is created on each storage node (DN) and coordination node (CN) of the cluster. The creation method and steps are as follows:

[0052] 1) Get the table structure definition from the external database. If the table definition is successfully obtained, proceed to the next step.

[0053] 2) Automatically create a new table space named by concatenating the db_link and name_space on all coordination nodes (CN) and storage nodes (DN). If the table space already exists, it will not be created again. name_space is the identification of the space name.

[0054] For example: external table name_space.table_name@db_link, through the automatic creation of db_link_name_space table space, to store table_name external table, so that the external table can be quickly locked through dblink and name_space.

[0055] 3) Create an external table corresponding to the table on each node under the table space on all coordination nodes (CN) and storage nodes (DN).

[0056] Step 3, when the client accesses the table of the external database again, the external table created during the first access can be directly used. The second access process is as follows:

[0057] 1) Use the concatenation of db_link and name_space as the table space name to quickly access the corresponding external table directly through the table space name, reducing intermediate links and achieving efficient access to external tables.

[0058] 2) When accessing the database using the automatically created external table, predicate pushdown can be achieved, saving the data pulled when accessing the external table and improving efficiency.

[0059] 3) Because each storage node (DN) and coordination node (CN) has the external table, all distributed execution plans such as LightProxy, Fast Query Shipping, Stream, RemoteQuery can be adapted. The pull and push of each operator between the storage node (DN) and the coordination node (CN) can be realized.

[0060] 4) Because the corresponding external table is created only when the table of the external database is accessed for the first time, the number of external tables is small, and compared with creating all the tables of the external database as external tables, the system table is greatly reduced, and the running efficiency of the system is improved.

[0061] The adaptation of dblink in each distributed execution plan is listed in Table 1.

[0062] Table 1

[0063]

[0064] II. Further optimization of the distributed execution plan for dblink to the access analysis process, the optimization of the access analysis process is as follows:

[0065] When the client accesses the external table under the same dblink and there is no interaction with the internal table, the coordination node (CN) directly issues the sql of the client to the external database to obtain data, reduces the link of the storage node (DN), and improves the access efficiency of dblink. Step 1, the coordination node (CN) completes the lexical analysis of the SQL statement received in the remote database to query the data meeting the specific condition, and when the semantic analysis is performed, if it is found that all the table names in the SQL are the same dblink, the sql of the client is directly issued to the external database to obtain data, reduces the link of the storage node (DN), and improves the access efficiency of dblink. Pushing where a = 1 to the external database for execution is a typical scenario of predicate pushdown.

[0066] Step 2, the external database directly returns the result to the coordination node (CN), and then the coordination node (CN) returns it to the client.

[0067] When the client accesses the external table under different dblink and has no interaction with the internal table, the coordinator node (CN) directly issues the sql after decomposition to different external databases to obtain data, and then performs calculation at the coordinator node (CN), reducing the storage node (DN) link and improving the dblink access efficiency. Step 1, the coordinator node (CN) completes the lexical analysis on the received SQL query statement which simultaneously accesses tables in different remote databases, and when performing semantic analysis, if it is found that all table names in the SQL are external tables and are under different dblinks, the client's sql is directly issued to the external database to obtain data, reducing the storage node (DN) link and improving the dblink access efficiency.

[0068] Step 2, the external database directly returns the result to the coordinator node (CN), and then the coordinator node (CN) returns it to the client.

[0069] The above method realizes the fast access of external tables in distributed databases through reducing the number of external tables, constructing a table space to quickly access external tables through the combination of dblink name and table space name, external table predicate pushdown, and distributed execution plan optimization for dblink.

[0070] A specific use embodiment is given,

[0071] Step 1, when the client creates a dblink using the "CREATE DATABASE LINK db_link CONNECT TO user_name IDENTIFIED BY password USING 'ip:port / db_name';" command, the login account, password, ip, port, and database name of the external database corresponding to the dblink are saved in the system table of each storage node (DN) and coordinator node (CN).

[0072] Step 2, when the client accesses the table of the external database using "name_space.table_name@db_link;", if it is the first time for the cluster to access the table, the external table corresponding to the table is created in each storage node (DN) and coordinator node (CN) of the cluster. The creation method and steps are as follows: name _space.table _name@db_link;” accesses the table of the external database, if it is the first time for the cluster to access the table. Then the external table corresponding to the table is created in each storage node (DN) and coordinator node (CN) of the cluster. The creation method and steps are as follows:

[0073] Obtain the structure definition of the table from the external database, if the table definition is successfully obtained, execute the next step;

[0074] Automatically create a new table space named by splicing db_link and name_space, and create this table space in each node of the cluster. If the table space already exists, it will not be created again;

[0075] In each node of the cluster, each node under the table space creates an external table corresponding to the table.

[0076] Step 3: When the client accesses the table of the external database again through "name_space.table_name@db_link;" in the future, the external table created in the first access can be directly used.

[0077] The second and subsequent access processes are as follows:

[0078] Using db_link and name_space splicing as the table space name, the corresponding external table can be quickly accessed directly through db_link_name_space.table_name, reducing the intermediate links and realizing efficient access to the external table.

[0079] When accessing the database using the automatically created external table, predicate pushdown can be realized. The pulled data when accessing the external table is saved, and the efficiency is improved.

[0080] Since each storage node (DN) and coordination node (CN) has the external table, it can adapt to all distributed execution plans such as LightProxy, FastQuery Shipping, Stream, RemoteQuery, etc. The pull and push of each operator between the storage node (DN) and the coordination node (CN) can be realized.

[0081] Since the corresponding external table is only created when accessing the table of the external database for the first time, the number of external tables is small, and compared with creating all tables of the external database as external tables, the system table is greatly reduced, and the running efficiency of the system is improved.

[0082] Step 4: When the coordination node (CN) completes the lexical analysis and semantic analysis of the received SQL ("name_space.table_name@db_link where a=1;") If it is found that all table names (name_space.table_name@db_link) in the SQL are the same dblink (@db_link), the client's sql is directly issued to the external database to obtain data, reducing the storage node (DN) link and improving the dblink access efficiency. Pushing the where a=1 to the external database for execution is a typical scenario of predicate pushdown.

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

[0084] It should be pointed out finally that the above embodiments are only used to illustrate the technical solutions of the present application, but not to limit the same; and although the present application has been described in detail with reference to the foregoing embodiments, it should be appreciated by those skilled in the art that the technical solutions recorded in the foregoing embodiments can be modified, or some technical features thereof can be replaced equivalently; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for constructing a dblink access under a distributed transaction database, characterized in that, The method comprises the following steps: receiving a command for creating a dblink, and storing connection information of the dblink on multiple nodes of the distributed transaction database; when a request for accessing a table in an external database through the dblink is first received, obtaining a structure definition of the table in the external database on the multiple nodes of the distributed transaction database, and creating an external table for accessing the table in the external database according to the structure definition and a dblink name in the connection information; when a query request for accessing the external table through the dblink is received, using the external table and selecting a corresponding query execution path according to characteristics of the query request to obtain a query result; the step of creating the external table comprises: automatically creating a table space on the multiple nodes of the distributed transaction database, and creating the table space only when the table space does not exist; creating the external table in the table space; a name of the table space is generated by concatenating the dblink name and a name_space; the name_space is an identifier of a space name; the step of accessing the external table comprises: locating the table space based on the dblink name and the identifier of the space name; executing a query through the external table in the table space, and pushing a filter condition in the query to the external database; using the dblink to complete multiple distributed execution plans, and dynamically adjusting operator execution between a storage node and a coordination node, when the distributed execution plan is executed, when the external table and a local table are combined, selecting a distributed execution plan according to a predicate pushdown rule and an operator pushdown rule; when the local table is not involved, the coordination node directly accesses the external table without passing through the storage node.

2. The dblink access construction method in the distributed transaction database according to claim 1, wherein when a query involves multiple external tables of the dblinks, the coordination node decomposes the query into multiple sub-queries; the sub-queries are respectively sent to corresponding external databases for execution; the coordination node combines results returned by the external databases.

3. The dblink access construction method in the distributed transaction database according to claim 1, wherein when all tables in a query are located in a same dblink and do not involve a local table, the coordination node directly sends a SQL of a client to an external database to obtain data; the external database returns a result to the coordination node, and the coordination node returns the result to the client.

4. The dblink access construction method in the distributed transaction database according to claim 1, wherein the multiple nodes of the distributed transaction database comprise a coordination node and a storage node.

5. The dblink access construction method in the distributed transaction database according to claim 1, wherein the connection information comprises a dblink name, a corresponding login account, a password, an ip of an external database, a port, and a name of the external database; the connection information is extracted by analyzing a dblink creation command input by a user, and is stored in a system table of all coordination nodes and storage nodes. 6.The method of claim 1, wherein, when the external table corresponding to the table in the external database to be accessed already exists, subsequent access reuses the created external table.

Images