Help Center/ GaussDB(DWS)/ Standard Data Warehouse (9.1.0.x)/ GaussDB(DWS) Data Query/ Data Reading/Writing Across Logical Clusters
Updated on 2024-12-18 GMT+08:00
View PDF

Data Reading/Writing Across Logical Clusters

Scenario

After an associated logical cluster user is created, the query or modification (including Insert, Delete, and Update) submitted by the user is calculated and executed in the associated logical cluster. If the user submits a query or modification request to a table in a different logical cluster, the optimizer generates a cross-logical cluster query or modification plan to enable the user to query or modify the table.

Figure 1 Querying data across logical clusters

Figure 2 Writing data across logical clusters

Procedure

  1. Create a cluster by referring to Creating a DWS Cluster with Decoupled Storage and Compute. After a cluster is created, it is converted to a logical cluster v3_logical by default.
  2. Add three nodes to the elastic cluster, and then add the logical cluster lc2.
  3. Create user u1 and associate it with logical cluster v3_logical. 

    1
    		 
    CREATE USER u1 with SYSADMIN NODE GROUP "v3_logical" password  "Password@123";

  4. Create user u2 and associate it with logical cluster lc2. 

    1
    		 
    CREATE USER u2 with SYSADMIN NODE GROUP "lc2" password  "Password@123";

  5. Log in to the database as user u1, create tables t1 and t2, and insert test data into the tables. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
    		 
    CREATE TABLE  public.t1 
( 
id integer not null,  
data integer, 
age integer 
)  
WITH (ORIENTATION =COLUMN, COLVERSION =3.0) 
DISTRIBUTE BY ROUNDROBIN; 

CREATE TABLE public.t2 
( 
id integer not null,  
data integer, 
age integer 
)  
WITH (ORIENTATION = COLUMN, COLVERSION =3.0) 
DISTRIBUTE BY ROUNDROBIN;

INSERT INTO public.t1 VALUES (1,2,10),(2,3,11);
INSERT INTO public.t2 VALUES (1,2,10),(2,3,11);

  6. Log in to the database as user u2 and run the commands below to query t1 and write data.

    According to the result, user u2 can query and write data across logical clusters. 
    1
2
    		 
    SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t2;

Parent topic: GaussDB(DWS) Data Query