Perform cross-database queries by using Remote AnalyticDB data source access-AnalyticDB(AnalyticDB)-阿里云帮助中心

The Remote AnalyticDB data source access feature lets you run a joint query on data in other AnalyticDB for PostgreSQL instances under your Alibaba Cloud account. By treating these instances as external data sources, you ensure real-time data access and reduce data redundancy.

A cross-database query involves at least two instances. To distinguish between them, this topic refers to the instance that initiates the query as the local instance and the instance that serves as an external data source as the remote instance.

Limitations

AnalyticDB for PostgreSQL V6.0 instances must have a minor engine version of v6.6.0.0 or later.
AnalyticDB for PostgreSQL V7.0 instances must have a minor engine version of v7.0.3.0 or later.
AnalyticDB for PostgreSQL in Serverless mode instances must have a minor engine version of v2.1.1.5 or later.

Note

You can view the minor version on the Basic Information page of an instance in the AnalyticDB for PostgreSQL console. If your instance does not meet the required versions, update the minor version of the instance.

Prerequisites

The local instance and the remote instance must reside in the same virtual private cloud (VPC).

Preparations

The steps show you how to query tables in the remote_db database of instance B from within the local_db database of instance A to perform a joint query.

Create initial accounts for local instance A and remote instance B.
Add the IP address or CIDR block of your client to the whitelist of local AnalyticDB for PostgreSQL instance A and remote instance B.
Prepare test data.
- On local instance A, create a database and a schema for creating foreign tables and accessing data from remote instance B.
  1. Connect to local instance A.
```
psql -h gp-bp166cyrtr4p*****-master.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount
```
    If your Elastic Compute Service (ECS) instance and your AnalyticDB for PostgreSQL instance are not in the same VPC, or if you are connecting to your AnalyticDB for PostgreSQL instance from on-premises, use a public endpoint.
  2. On local instance A, create a database named local_db and then connect to the local_db database.
```
CREATE DATABASE local_db;
\c local_db
```
  3. In the local_db database of local instance A, create a schema.
```
CREATE SCHEMA s02;
```
- On remote instance B, create a database and tables that local instance A will query.
  1. Connect to remote instance B as you did for local instance A.
  2. On remote instance B, create a database named remote_db and then connect to the remote_db database.
```
CREATE DATABASE remote_db;
\c remote_db
```
  3. Prepare test data in the remote_db database of remote instance B.
```
CREATE SCHEMA s01;

CREATE TABLE s01.t1(a int, b int, c text); 
CREATE TABLE s01.t2(a int, b int, c text); 
CREATE TABLE s01.t3(a int, b int, c text); 

INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');
INSERT INTO s01.t2 VALUES(generate_series(11,20),generate_series(11,20),'t2');
INSERT INTO s01.t3 VALUES(generate_series(21,30),generate_series(11,20),'t3');
```

Procedure

Step 1: Add a data source

Log on to the AnalyticDB for PostgreSQL console. Find local instance A and click its instance ID.
In the navigation pane on the left, click External Data Source Management.

On the Remote AnalyticDB Data Source Access tab, click Add Data Source and configure the following parameters.

Parameter	Description
Local Instance ID	This parameter is automatically specified.
Local Database Name	Select the `local_db` database of local instance A.
Local Initial Account	If you have already created an initial account, you do not need to configure this parameter.
Local Initial Password	The password of the initial account.
Remote Instance ID	Select the instance ID of remote instance B.
Remote Database Name	Select the `remote_db` database of remote instance B.
Remote Initial Account	The initial account of remote instance B.
Remote Initial Password	The password of the initial account.
Data Source Name	Specify a custom name for the data source. Example: example_name.

Click OK. Wait until the data source state changes to Running. You can then perform cross-database queries.
Note
- After you add the data source, its state changes to Adding Remote AnalyticDB Data Source. This process typically takes less than one minute and does not affect your instance's operation or data I/O.
- After the data source is created, you can perform the following operations:
  - Edit the data source: In the Actions column, click Edit to modify the account and password of the remote instance.
  - Remove the data source: In the Actions column, click Delete.

Step 2: Perform a cross-instance query

Important

If the remote instance is an AnalyticDB for PostgreSQL in Serverless mode instance, you cannot access its data while it is scaling.

Connect to local instance A and switch to the s02 schema in the local_db database.

In the s02 schema of local instance A, create foreign tables for the t1, t2, and t3 tables of remote instance B.

Important

The local schema cannot contain a table that shares a name with a remote table. Otherwise, the operation fails.

Import multiple tables

Syntax:

IMPORT FOREIGN SCHEMA remote_schema -- Remote schema name.
    [LIMIT TO ( table_name [, ...] ) ] -- Remote table names.
    FROM SERVER server_name -- Data source name from Step 1.
    INTO local_schema -- Local schema name.

Example:

IMPORT FOREIGN SCHEMA s01 LIMIT TO (t1, t2, t3) 
	FROM SERVER example_name INTO s02;

Note

For more syntax details, see IMPORT FOREIGN SCHEMA.

Import a single table

Syntax:

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ -- Remote table name.
  { column_name data_type } ] -- Remote table structure.
    [, ... ] )
  SERVER server_name -- Data source name from Step 1.
[ OPTIONS ( option 'value' [, ... ] ) ] -- Local schema and table names.

Example:

CREATE FOREIGN TABLE s01.t1(a int, b int) 
	SERVER example_name OPTIONS(schema_name 's02', table_name 't1');

Note

For more syntax details, see CREATE FOREIGN TABLE.

From within the local_db database of local instance A, query the t1 table in the remote_db database of remote instance B.

Note
AnalyticDB for PostgreSQL does not support accessing data using the Database.Schema.Table format.
```
SELECT * FROM s02.t1;
```
The following result is returned:
```
 a  | b  | c
----+----+----
  2 | 12 | t1
  3 | 13 | t1
  4 | 14 | t1
  7 | 17 | t1
  8 | 18 | t1
  1 | 11 | t1
  5 | 15 | t1
  6 | 16 | t1
  9 | 19 | t1
 10 | 20 | t1
(10 rows)
```

References

Cross-database query: If your data is stored in different databases on the same instance, you can perform a joint query across those databases.