Building an automatic DB -> GraphQL API converter, using Apache Calcite. Would ShardingSphere be a good fit to provide execution + federated querying?

[GavinRay97]

Hello, this seems a very cool project =)

It is very large and so while I have read the docs, I wanted to ask for advice since not everything is clear.

I have been building a tool which introspects datasources supported by Apache Calcite, and generates GraphQL API's for them:

• https://github.com/GavinRay97/GraphQLCalcite
• YouTube: Calcite Meetup January 2022 - GraphQL For Apache Calcite

The API's allow you to query or (soon) update data across all datasources.
You can make virtual relationships, like:
mysql1.db1.blogpost.id <-> postgres1.db2.public.comment.blogpost_id

And then the API generated allows you to query like:

query BlogpostsFromMySQLAndCommentsFromPostgres {
  mysql1 {
    db1 {
     # Generate SELECT for "blogpost" in mysql1 -> db1
      blogpost {
        title
        # JOIN on "comment" in postgres1 -> db1 -> public schema
        comments {
          text
       }

To do this, I am generating Calcite Relational Expression nodes (RelNode) for the query statements:

Would Apache ShardingSphere be a good fit to use as the "execution engine" for the queries?
So ShardingSphere can distribute the federated reads/writes, and manage the DB connections, etc.

Can I create ShardingSphere query expression directly, without SQL (like Calcite Relational Expression) if so?

Thank you 🙂

[yy2so]

Hi @GavinRay97 welcome to the community!
As you said, the project is quite large, but AFAIK it should be possible 😉
Apache ShardingSphere could fit in your plan as it can effectively distribute read/writes, and manage DB connections too. It wouldn't require much setup and wouldn't be heavy

[andrisi]

would be great to see examples of Shardingspace doing queryies among multiple connected DBMS systems, like https://github.com/teiid/teiid does (or did) because even tough it seems like the project for it, it is very hard to get a grasp from the docs. I'm an "old" IT guy, not a newbie, so that's not the problem. I'd be happy to have a quick chat with someone from the team. Thanks!

[tristaZero]

Hello @GavinRay97
Thanks for your question. It's clear, but I still have some confusion, could I have more explanation？

The API's allow you to query or (soon) update data across all datasources.

1. AFAIK, GraphQL has the capacity to query from different data sources, right? If so, why does your project still need calcite?
2. Update can not be supported by GraphQL, can it?
3. Calcite could help execute SQL, why do you still need another executor?

Try to reply to your question
It looks you will get the best physical plan, and then an executor engine is needed.

1. ShardingSphere has the capability to manage the DB connections from different Databases (read/write).
2. ShardingSphere still partially use calcite for some federation queries, so it's possible to use the generated physical plan to get the result. However, there is remaining something to do, i.e., CustomizedFilterableExecutor. If you're interested in calcite and executor, welcome to join this community!
3. ShardingSphere still has its original executor to deal with most of SQLs on tables in the same DB. If you want to use this one to get the result, DriverJDBCExecutor could be valuable for your diving.
4. The entrance of 2 and 3 is of DriverExecutor of ShardingSpherePreparedStatement. It's easier for a developer, I mean, you to get my context here. ;-)

Notice, since your expectation is

Can I create ShardingSphere query expression directly, without SQL (like Calcite Relational Expression) if so?

So I give 2, 3, 4. But If you want to use ShardingSphere from the outer, then Quick start could help you use it as an executor by passing the DB connection info and relational SQL to it.