ApsaraDB RDS:ShardingSphere middleware development guide

config-sharding.yaml structure

schemaName:              # Name of the logical data source (used as the database name when connecting)

dataSources:             # One entry per physical database; no connection pool configuration needed (unlike Sharding-JDBC)
  <data_source_name>:
    url:                 # JDBC connection URL for the ApsaraDB RDS instance
    username:            # Database account username
    password:            # Database account password
    connectionTimeoutMilliseconds: 30000   # Connection timeout
    idleTimeoutMilliseconds: 60000         # Idle connection reclaim timeout
    maxLifetimeMilliseconds: 1800000       # Maximum connection lifetime
    maxPoolSize: 65                        # Maximum connections in the pool

shardingRule:            # Sharding rules; same structure as Sharding-JDBC

server.yaml structure

Proxy settings control thread pool size, transaction type, and diagnostic features:

props:
  acceptor.size:                   # Worker threads for accepting client connections (default: CPU cores x 2)
  proxy.transaction.type:          # Transaction type: LOCAL (default), XA (uses Atomikos), or BASE
                                   # Note: to use BASE, copy the .jar that implements ShardingTransactionManager to the lib directory
  proxy.opentracing.enabled:       # Enable link tracing (default: false)
  check.table.metadata.enabled:    # Check shard table metadata consistency at startup (default: false)
  proxy.frontend.flush.threshold:  # Batch packet size for complex queries

Authentication controls which users can log in and which logical databases they can access:

authentication:
  users:
    <username>:
      password: <password>
      authorizedSchemas: <db1>,<db2>   # Omit to grant access to all schemas

For the full configuration reference, see the .

Step 1: Install Java on the ECS instance

sudo yum install -y java

Step 2: Configure the ApsaraDB RDS instance

1. Create a database account with username r1 and password PW123321!.

2. Create four databases owned by r1: db0, db1, db2, and db3.

3. Add the ECS instance's IP address to the ApsaraDB RDS instance's IP address whitelist.

For details, see Create a database and an account on an ApsaraDB RDS for PostgreSQL instance and Configure an IP address whitelist for an ApsaraDB RDS for PostgreSQL instance.

Step 3: Configure server.yaml

vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/server.yaml

Set the following:

authentication:
  users:
    r1:
      password: PW123321!
      authorizedSchemas: db0,db1,db2,db3
props:
  executor.size: 16
  sql.show: false

Step 1: Configure config-sharding.yaml

vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/config-sharding.yaml

The following configuration maps four physical databases (db0-db3) to a single logical data source named sdb. It shards t_order across 4 databases x 8 tables (32 physical shards total) using user_id for database routing and order_id for table routing.

schemaName: sdb

dataSources:
  db0:
    url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db0
    username: r1
    password: PW123321!
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  db1:
    url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db1
    username: r1
    password: PW123321!
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  db2:
    url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db2
    username: r1
    password: PW123321!
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  db3:
    url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db3
    username: r1
    password: PW123321!
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65

shardingRule:
  tables:
    t_order:
      actualDataNodes: db${0..3}.t_order${0..7}  # 4 databases x 8 tables = 32 shards
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: db${user_id % 4}   # Routes to db0-db3 based on user_id
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order${order_id % 8}  # Routes to t_order0-t_order7 based on order_id
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: db${0..3}.t_order_item${0..7}
      databaseStrategy:
        inline:
          shardingColumn: user_id
          algorithmExpression: db${user_id % 4}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item${order_id % 8}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item   # Declares these tables share the same sharding strategy to avoid cross-shard joins
  defaultTableStrategy:
    none:

Step 2: Start Sharding-Proxy

Start the proxy and listen on port 8001:

cd ~/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/bin/
./start.sh 8001

Step 3: Connect and create tables

Connect to the logical data source sdb as user r1:

psql -h 127.0.0.1 -p 8001 -U r1 sdb

Create the sharded tables:

CREATE TABLE t_order (
    order_id   int8      PRIMARY KEY,
    user_id    int8,
    info       text,
    c1         int,
    crt_time   timestamp
);

CREATE TABLE t_order_item (
    order_item_id  int8      PRIMARY KEY,
    order_id       int8,
    user_id        int8,
    info           text,
    c1 int, c2 int, c3 int, c4 int, c5 int,
    crt_time       timestamp
);