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