ShardingSphere-Proxy be sharded by month

[Vain-vayne]

Can ShardingSphere-Proxy be sharded by month?

• Version number: 5.5.0
• How to handle monthly database sharding? For example, 202301 and 202401 are both in the ds_01.test database. How to configure? The following configuration shows an error after inserting data: org.apache.shardingsphere.sharding.exception.algorithm.NoShardingDatabaseRouteInfoException: No sharding database route info.

shardingAlgorithms:
shardingalgorithms_interval:
type: INTERVAL
props:
datetime-pattern: "yyyyMM"
datetime-lower: "202301"
sharding-suffix-pattern: "MM"
datetime-interval-amount: 1
datetime-interval-unit: "MONTHS"

[terrymanu]

Can you polish the title? The meaning of this title is not clear.

[Vain-vayne]

• ShardingSphere-Proxy configuration file

databaseName: log
dataSources:
  ds_01:
    ...
  ds_02:
  ...
rules:
- !SHARDING
  tables:
    log_test:
      actualDataNodes: ds_${01..12}.log_test
      databaseStrategy:
        standard:
          shardingColumn: tran_month
          shardingAlgorithmName: shardingalgorithms_interval
      tableStrategy:
        none:
...
  shardingAlgorithms:
    shardingalgorithms_interval:
      type: INTERVAL
      props:
        datetime-pattern: "yyyyMM"
        datetime-lower: "202401"
        sharding-suffix-pattern: "MM"
        datetime-interval-amount: 1
        datetime-interval-unit: "MONTHS"
...

• SQL

INSERT INTO `log_test` (`TRAN_SN`, `TRAN_MONTH`, `TRAN_DATE`, `TRAN_TIME`) VALUES ('111111111','202401', '2024-01-10', '2024-01-10 11:26:08');

• Error after executing SQL statement

org.apache.shardingsphere.sharding.exception.algorithm.NoShardingDatabaseRouteInfoException: No sharding database route info.

• The configuration file must be changed to the following configuration to insert successfully

databaseName: log

dataSources:
  ds_202401:
    ...
  ds_202402:
  ...
rules:
- !SHARDING
  tables:
    log_acct_five_vld:
      actualDataNodes: ds_${202401..202412}.log_test
      databaseStrategy:
        standard:
          shardingColumn: tran_month
          shardingAlgorithmName: shardingalgorithms_interval
      tableStrategy:
        none:
...
  shardingAlgorithms:
    shardingalgorithms_interval:
      type: INTERVAL
      props:
        datetime-pattern: "yyyyMM"
        datetime-lower: "202401"
        sharding-suffix-pattern: "yyyyMM"
        datetime-interval-amount: 1
        datetime-interval-unit: "MONTHS"
...

• sharding-suffix-pattern needs to use MM, not yyyyMM
• I want to shard by month instead of year-month. How can I modify the configuration file?

[lazyboyl]

You can use the sharding jdbc composite sharding algorithm to implement it, then package it in a jar file and place it in the ext lib directory of the proxy. You can refer to the example of this repository: https://github.com/lazyboyl/shardingsphere-spring-boot-starter-example