Map a query as a virtual table

Question

Is it possible within Apache Calcite to map a JDBC based query as a virtual table? I would like to expose a table to the user, but behind the scenes have it be implemented as a query via jdbc to a datasource. I am aware that I can subclass Table and implement myself if necessary, but I wanted to see if there was a supported way to do this.

Any suggestions would be appreciated.

score 2 · Answer 1 · answered Jul 05 '18 at 14:03

It sounds like what you want to do is create a view. You can see an example in the tutorial. View definitions can be added to the model.json you use to define your schema as in the example below.

{
  version: '1.0',
  defaultSchema: 'SALES',
  schemas: [
    {
      name: 'SALES',
      type: 'custom',
      factory: 'org.apache.calcite.adapter.csv.CsvSchemaFactory',
      operand: {
        directory: 'target/test-classes/sales'
      },
      tables: [
        {
          name: 'FEMALE_EMPS',
          type: 'view',
          sql: 'SELECT * FROM emps WHERE gender = \'F\''
        }
      ]
    }
  ]
}

Map a query as a virtual table

1 Answers1