[FEATURE] Add Calcite JDBC executor as new frontend in `shardingsphere-proxy` to allow federated querying of any datasources via MySQL/PG clients

[GavinRay97]

Proposal

I would like to propose adding two new executors to ShardingSphere Proxy:

• Postgres Frontend, Calcite JDBC Executor
• MySQL Frontend, Calcite JDBC Executor

This would allow using any tool/client that is compatible with either the MySQL or Postgres wire protocols to query across datasources:

flowchart LR
    MySQL_or_PG_Client
    subgraph ShardingSphere_Proxy
        direction LR
        oracle[(Oracle)]
        mongo[(Mongo)]
        mysql[(MySQL)]
        spark[(Spark)]
        Calcite_JDBC
        Calcite_JDBC<-->oracle
        Calcite_JDBC<-->mongo
        Calcite_JDBC<-->mysql
        Calcite_JDBC<-->spark
    end
    MySQL_or_PG_Client-. "SELECT ... FROM mysql.db1 \n INNER JOIN oracle.db2 \n INNER JOIN mongo.db3 " .->ShardingSphere_Proxy

Design

Disclaimer: I have spent a few hours on the source code of ShardingSphere, but do not understand it very well

• Two new frontend executors should be created:
  • shardingsphere-proxy-frontend-calcite-mysql
  • shardingsphere-proxy-frontend-calcite-postgres
• These executors should re-use all of the wire protocol/serialization code (like the openGauss executor does with Postgres)
• What is different is that instead of querying MySQL/PG, the executor will hold a single internal connection to a jdbc://calcite source
• Multiple datasources can be added to this (what Calcite calls) root Schema
  • This could be done with YAML configuration or programmatically

IE:

dataSources:
    calcite_postgres:
        # Purposefully empty, this is a virtual database that exists only in-memory to aggregate other datasources
        url: "jdbc:calcite:fun=postgresql;"
        meta:
            schemas:
                mysql1:
                    url: jdbc:calcite:schemaType=JDBC; schema.jdbcUrl=jdbc:mysql://foo:bar@url/db
                postgres1:
                    url: jdbc:calcite:schemaType=JDBC; schema.jdbcUrl=jdbc:postgresql://baz:qux@url/db
                mongo1:
                    url: jdbc:calcite:schemaFactory=org.apache.calcite.adapter.mongodb.MongoSchemaFactory; schema.host=localhost; schema.db=somedb
    calcite_mysql:
        url: "jdbc:calcite:fun=mysql; lex=MYSQL;"
        meta:
            schemas:
                # ...

[terrymanu]

Good idea, but it need more time to design and impl, I just set it as good amateur issue, welcome interested friend join and discuss

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[kaori-seasons]

It sounds like this involves dialect conversion and calcite translator adaptation when querying with tables

[github-actions[bot]]

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