Dynamic VDB

[mahavird]

Hi Teiid Team,

We are developing Dynamic VDB and we have following use case which needs solution.

I have following scenario...

     1. We are having a Oracle Database.

     2. In the Oracle database, we have a procedure named DO_SECURITY(...)

     3. After calling the DO_SECURITY('SCHEMA_NAME') only, some of the SCHEMAS and the TABLES in the schema will become visible.

     4. Is it possible to dynamically execute query against this SCHEMAS and TABLES which is visible only after calling DO_SECURITY(...)

Any solutions like refresh metadata, Native queries, directly executing the query on foreign schemas ?

If any solution exist, is it possible to have a example ?

In Native queries we have tried many, But requires structure of the table to be hardcoded in query using (ARRAYTABLE), in our example it is not possible to know the structure in advance.

In refresh metadata, VDB needs to do reload from my understanding. Or is there any way to metadata refresh dynamically from VDB ?

Also query on foreign schemas we need the entire schemas to be loaded (by passing the DO_SECURITY(...) procedure).

Any solution solves the above use case is helpful.

Thanks,

Mahavir

[rareddy]

You need to implement a custom metadata repository [1] to do the special DO_SECURITY call before actual metadata is read from the database. You can also extend the NativeMetadataRepository [2] that Teiid uses to read metadata from Database and add in necessary hooks there. Once you implement the class, you need to create a module out and install in the WildFly and then you need to configure the VDB to use this custom repository to fetch the metadata. See for more details here [3] & [4]

HTH

Ramesh..

[1] teiid/MetadataRepository.java at master · teiid/teiid · GitHub

[2] teiid/NativeMetadataRepository.java at master · teiid/teiid · GitHub

[3] Custom Metadata Repository · GitBook

[4] Metadata Repositories · GitBook

[mahavird]

Hi Ramesh Reddy,

Thanks for the reply.

We need to reload the metadata after calling the security function DO_SECURITY("SCHEMA_NAME").

The DO_SECURITY("SCHEMA_NAME") will be called inside the procedure in VDB.

Is there any way to reload the metadata after the DO_SECURITY is called from VDB ?

Thanks

Mahavir

[rareddy]

As I mentioned above you do that in the CustomMetadataRepository, not in VDB.

[mahavird]

Hi Ramesh Reddy,

When this CustomMetadataRepository hook will be called ?

Thanks,

Mahavir

[mahavird]

Hi Ramesh Reddy,

I have to refresh the metadata each time after calling the security function DO_SECURITY(...).

Is it possible to refresh multiple times the meta data in custom metadata repository?

Thanks & Regards,

Mahavir Dhanadevan

[rareddy]

A VDB can be loaded only at the time of deployment, that means metadata can be loaded only at the time of VDB deployment. If you need reload you need to redeploy the vdb.

Ramesh..