Help Center> Data Warehouse Service (DWS)> 3.0 Developer Guide> Development Practices> Data Reading/Writing Across Logical Clusters
Updated on 2023-11-30 GMT+08:00
View PDF

Data Reading/Writing Across Logical Clusters

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 the table.

Figure 1 Querying data across logical clusters

Figure 2 Data writing across logical clusters

  1. Create a GaussDB(DWS) 3.0 cluster. 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 following commands 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: Development Practices