PeterK
Xpert
Status: New Idea

Summary

It would be very useful sometimes to be able to pass arbitrary parameters to a virtual schema at query execution time to assist in building the pushdown SQL.

Example

A virtual schema might be used to connect to arbitrary MySQL instances on the backend. There are currently 2 ways to tell it which instance to connect to:

  1. Via a virtual schema property. The downside is that these cannot be specified at query runtime so you need a separate virtual schema for every single MySQL instance you wish to connect to. This can lead to many redundant schemas.
  2. Via an extra virtual column (e.g. table.db_instance) in one of the tables in the schema. This would then allow you to specify which instance you wish to connect to using criteria. The downside of this is that there are cases where the optimizer will choose not to push down that column criteria to the virtual schema and so the VS doesn't know which instance to connect to.

Suggested Solution

Many DBMSs have the concept of arbitrary session-scoped variables that can be set and later referenced in queries as constants. e.g.

 

SET @db_instance = 'db123.example.com';

 

If Exasol supported session-scoped variables and then made those variables accessible in the virtual-schema framework via the global ctx object then the pushdown-sql builder could use the variable value to connect to the correct backend.