You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "sivaraman-27 (via GitHub)" <gi...@apache.org> on 2023/10/12 06:57:33 UTC

[I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

sivaraman-27 opened a new issue, #28733:
URL: https://github.com/apache/shardingsphere/issues/28733

   ## Please understand my question below
   - Using Mysql as database
   - Have two tables 'orders' and 'order_details'. We have a primary database and 10 secondary databases
   - 'orders' table is only present in primary database and it's column 'order_id' is mapped to specific secondary database [OrderId: 4 --> Go to DB 3] 
   - 'order_details' is present in 10 secondary databases. The name 'order_details' is same in all secondary databases and schema is also same in all.
   - For each 'order_id', we need to get the corresponding secondary database from 'orders' table and query the 'order_details' table in the secondary database.
   - Now, I would to like to know if this apache shardingsphere will assist me in implementing this routing logic (Perhaps, apache sharding proxy helps?)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1779264196

   Hi @strongduanmu,
   I have another doubt, it seems like the only thing that apache sharding sphere handles for us is 'connection pooling'. Could you let me know any other use cases for this library (considering our specific use case).
   
   The reason I'm asking is we can manage the connection pooling (already doing for few), and don't want to incorporate a library for such use case.
   
   Example: 
   **Using apache sharding sphere:**
   Query = "/* SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=ds_1 */ select * from orders";
   
   **Using our own mechanism:**
   Query = select * from ds_1.orders";
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1761100676

   @strongduanmu So, a **single query** will be used everytime, only thing that changes is the shard before executing the query.
   
   Hope I'm Clear. Awaiting your answer.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1760543446

   I think it would be more helpful if you could provide sql examples.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1760737051

   Added some info below: (The tables below are just for example representation)
   
   **Tables involved:**
   - shard_lookup
   - orders
   - order_details
   - order_ratings
   
   shard_lookup:
   | order_id  | shard_no |
   | ------------- | ------------- |
   | 1  | 1  |
   | 2  | 1  |
   | 3  | 2 |
   | 4  | 2 | 
   
   orders: [This table is present in shard_no = 1]
   | order_id | user_id | order_amount 
   | --------- | -------- | ---------------- | 
   | 1 | 100 | $8 |
   | 2 | 101 | $10 | 
   
   order_details: [This table is present in shard_no = 1]
   | order_id | restaurant_id | ordered_on |  delivered_on
   | --------- | -------- | ---------------- | ----------------- |
   | 1 | 8001 | epoch_time | epoch_time |
   | 2 | 8002 | epoch_time  | epoch_time |
   
   
   order_ratings: [This table is present in shard_no = 1]
   | order_id | rating_value | rated_on |  will_recommed_to_others
   | --------- | -------- | ---------------- | ----------------- |
   | 1 | 5 | epoch_time | Yes |
   | 2 | 4 | epoch_time  | Yes |
   
   * The tables above are present in shard_no = 1. Assume these tables with same name and structure is present in all shards.
   
   * So, for querying any data from 'orders', 'order_details', 'order_ratings', first correct shard is obtained from 'shard_lookup' using order_id, then appropriate table is queried.
   
   
   **Examples:**
   1. For getting amount for order_id = 1, below steps are followed
       * Query 'shard_lookup' for order_id = 1, which gives shard_1
       * Go to shard_1, query 'orders' table for order_id = 1 which will give '$8'
    2. For getting amount for order_id = 4, below steps are followed
       * Query 'shard_lookup' for order_id = 4, which gives shard_2
       * Go to shard_2, query 'orders' table which will give appropriate amount
   
   
   * I know I can write custom logic, but just wanted to know if apache shardingsphere/proxy will be helpful because you guys would have solved lots of common problems.
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1762463714

   You should config sharding table with hint strategy. For more details, you can refer this doc - https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/rules/sharding/


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1761308769

   _Thank you for your example. I think you can use SQL Hint for sharding. For orders, order_details, order_ratings, **you can specify shard_no** in the way of SQL comments when querying, and include order_id in the sql condition. SQL Hint completes the routing of the table, and order_id filters the data.
   You can refer this doc - https://shardingsphere.apache.org/document/current/en/user-manual/common-config/sql-hint/ to use sql hint._
   ___
   Hi @strongduanmu,
   I have a doubt, if I use 'SQL Hint', then is 'shard_no' (see highlighted text above sent by you) the corresponding datasource name specified in the 'yaml.file'?
   
   **Example:**
   I want to know the amount for order_id = 1, then I do the below query
   ```html
   /* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE="datasource_1" */ select amount from orders where order_id = 1;
   ``` 
   
   Then I specify **datasource_1 as below in 'yaml.config'** file (Or use Java for dynamic updations)
   
   ```python
   dataSources :
     ds 0:
     dataSourceC1assName: com.zaxxer.hikari.HikariDataSource
     driverC1assName: com.mysql.jdbc.Driver
     jdbcUr1: ***************** I specify hostname of shard_no = 1 *****************************************
     server Timezone=UTC&useSSL=fa1se&useUnicode=true&characterEncoding=UTF-8
     username: --
     password : --
   ``` 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #28733: Does this library support sharding tables with same name and schema in different databases
URL: https://github.com/apache/shardingsphere/issues/28733


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1761202416

   /
   
   > orders order_details order_ratings @sivaraman-27 These three tables do not contain sharding keys?
   
   @strongduanmu Nope, only 'shard_lookup' has the keys


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1760738400

   > Added some info below: (The tables below are just for example representation)
   > 
   > **Tables involved:**
   > 
   > * shard_lookup
   > * orders
   > * order_details
   > * order_ratings
   > 
   > shard_lookup:
   > 
   > order_id	shard_no
   > 1	1
   > 2	1
   > 3	2
   > 4	2
   > orders: [This table is present in shard_no = 1]
   > 
   > order_id	user_id	order_amount
   > 1	100	$8
   > 2	101	$10
   > order_details: [This table is present in shard_no = 1]
   > 
   > order_id	restaurant_id	ordered_on	delivered_on
   > 1	8001	epoch_time	epoch_time
   > 2	8002	epoch_time	epoch_time
   > order_ratings: [This table is present in shard_no = 1]
   > 
   > order_id	rating_value	rated_on	will_recommed_to_others
   > 1	5	epoch_time	Yes
   > 2	4	epoch_time	Yes
   > * The tables above are present in shard_no = 1. Assume these tables with same name and structure is present in all shards.
   > * So, for querying any data from 'orders', 'order_details', 'order_ratings', first correct shard is obtained from 'shard_lookup' using order_id, then appropriate table is queried.
   > 
   > **Examples:**
   > 
   > 1. For getting amount for order_id = 1, below steps are followed
   >    
   >    * Query 'shard_lookup' for order_id = 1, which gives shard_1
   >    * Go to shard_1, query 'orders' table for order_id = 1 which will give '$8'
   > 2. For getting amount for order_id = 4, below steps are followed
   >    
   >    * Query 'shard_lookup' for order_id = 4, which gives shard_2
   >    * Go to shard_2, query 'orders' table which will give appropriate amount
   > 
   > * I know I can write custom logic, but just wanted to know if apache shardingsphere/proxy will be helpful because you guys would have solved lots of common problems.
   
   @strongduanmu I have add some information above, please checkout it out and let me know your thoughts.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1826410042

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1761161588

   Hi @sivaraman-27, thank you for your example. I think you can use SQL Hint for sharding. For orders, order_details, order_ratings, you can specify shard_no in the way of SQL comments when querying, and include order_id in the sql condition. SQL Hint completes the routing of the table, and order_id filters the data.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1761156313

   orders
   order_details
   order_ratings
   @sivaraman-27 These three tables do not contain sharding keys?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1780368859

   > I would like to know if joining two tables in different datasources is feasible with any of apache shardingsphere's tools?
   
   You can try the experimental feature - sql federation.
   
   ```yaml
   sqlFederation:
     sqlFederationEnabled: true
     executionPlanCache:
       initialCapacity: 2000
       maximumSize: 65535
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1778892749

   Hi @strongduanmu,
   I would like to know if joining two tables in different datasources is feasible with any of apache shardingsphere's tools?
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1759034755

   @TeslaCN, @strongduanmu  Could you please reply to my query above.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Does this library support sharding tables with same name and schema in different databases [shardingsphere]

Posted by "sivaraman-27 (via GitHub)" <gi...@apache.org>.
sivaraman-27 commented on issue #28733:
URL: https://github.com/apache/shardingsphere/issues/28733#issuecomment-1759510339

   Basically it's like dictionary sharding [Look up table which map ids to shards]


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org