How to set Source/View Model schema in Embedded mode

[capoorhimanshu]

Hi,

I am using teiid embedded which do not provide congiguration stuff.  I created my own UI to create configurations where i can specify source information . Model information where user can

specify what all JDBC -tables should be available (i.e. selected list of tables) , and can also rename the column names. On these models user can define the VDB's i.e. the same use model how the teiid designer works.

Now reading these configurations i would like to create models , and VDB's and i am using following api's. Sample code:

        string strVDBName = "myVDB";

        EmbeddedServer

        jdbcModel = new ModelMetaData();

        jdbcModel.setName("XXX");

        jdbcModel.setSchemaSourceType("native");

        jdbcModel.addSourceMapping("XXX", "sqlserver", "XXX");

        jdbcModel1 = new ModelMetaData()

        jdbcModel1.setName("YYY");

        jdbcModel1.setSchemaSourceType("native");

        jdbcModel1.addSourceMapping("YYY", "sqlserver", "YYY");

        List<ModelMetaData> modelsList= new ArrayList<ModelMetaData>();

        list.add(jdbcModel);

        list.add(jdbcModel1);

       teiidServer.deployVDB(strVDBName, models);

      When i am using these api's the complete list of tables (not the selected one ) are available in VDB on which user can query on. Also the columns actual name are getting displayed where as i want the renamed columns.

Are there any api's in embedded mode ,  where i can mention the schema of models i.e. what all tables should be available(say i want only handful of tables available) and also the mapping

of the columns (i.e. renamed column and actual columns) . So that a VDB user can query only on those handful tables and also on the renemed columns.

For e.g.

if source table have 100 tables i want only 10 tables to be available to the VDB user , and say if table1 has schema say EmployeName and EMPID , where as i renamed EmployeName  to EmpName , so i want i can write query "select EmpName  from table1 "; and internaly it maps to the actual column name and fetches the data for me.

Regards

Himanshu Kapoor

[shawkins]

> When i am using these api's the complete list of tables (not the selected one ) are available in VDB on which user can query on.

There are a couple of possiblities here. 

The first is to exclude the tables entirely.  This means that they should not be imported in the first place.  See https://docs.jboss.org/author/display/TEIID/JDBC+Translator for the JDBC import properties.  In particular when you have a set of tables that to include that can't be satisfied by just JDBC DatabaseMetadata like predicate filters, then you'll want to use the excludeTables regex to exclude or include tables in a more specific way.

A weaker option is to just marke the importing model as not visible - ModelMetadata.setVisible(false) - this will exclude the tables from appearing in Teiid's JDBC DatabaseMetadata - however the tables/procedures may still be referenced by fully qualified name by a client.

Another option that is avaialble using a embedded deploy method that takes a full vdb.xml artifact (or by extending the EmbeddedServer to use the deployVDB(VDBMetadata, VDBResources) method) is to specify a any authenticated data role that disallows user queries from referencing tables you don't want them to use.  See also https://docs.jboss.org/author/display/TEIID/Data+Roles

> Also the columns actual name are getting displayed where as i want the renamed columns.

Renaming columns is not directly supported by the built-in JDBC importer.  There are a couple of possiblities here also. 

You can introduce a separate view model that performs the renaming using DDL:  "create view table1 as SELECT EmployeName as EmpName FROM XXX.table1;".

You can chain a custom metadata repository to directly modify the Table objects after importing with the standard JDBC importer.

Or you can just directly define your source access as DDL to control exactly want get's imported and how it's name (it's best here to work off of a ddl dump - see the EmbeddedServer.getSchemaDdl method): "create foreign table table1 (EmpName string options (NAMEINSOURCE "EmployeName"));"

[capoorhimanshu]

Thanks Steve , i did not got much from the reply intially but in recent times i debugged the teiid embedded source code and got the more understanding regarding what you mentione above.

First query i raised was excluding tables :

You mentioned two things to do this :

• excludeTables regex: I looke in source code and found its been used in addTable and getColumns metod of JDBCMetdataProcessor class. So that thing got clearer to me that when we are deploying the VDB,  it go further to load the meta data using metadata factory , it loads the execution factory i.e tranlator stuff and loads the meta data using connectors and uses excludeTables and schema patterns to load the meta data accordingly.Please let me know in case my understanding is not right .

• Second theing you mentioned about setting up the ModelMetadata.setVisible(false) which i need to look further that how i can set setvisible false for the tables which i do not want to expose to

        end user. Is there any setvisible property in table class, so that we can hide it when we add this in metadatafactory in addTable method of JDBCMetdataProcessor class ?

Second query i raised was renaming columns

You mentioned two things to do this :

• First thing was creating a view . In this while creating the model , i will tell the setSchemaSourceType as ddl , so when we deploy the vdb , models MetadataRepository is of type DDLMetadataRepository which parses the raw meta data i.e. Sql statement we used to create the view , and metadatafactory gets created accordingly.

I did not got much about last two points you mentioned regarding chain a custom metadata repository to directly modify the Table objects after importing with the standard JDBC importer

and also the other one just directly define your source access as DDL to control exactly want get's imported and how it's name.

It will be really helpful if you can explain this in bit details. 

Regards

Himanshu Kapoor

[shawkins]

> Please let me know in case my understanding is not right .

No that is correct the importer properties passed to the translator determine what metadata it supplies to the engine.

> Is there any setvisible property in table class, so that we can hide it when we add this in metadatafactory in addTable method of JDBCMetdataProcessor class

You would set this as an attribute visible="false" on the model element in the vdb.xml.  This hides the metadata from the system tables, but it is still accessable.

> First thing was creating a view .

There are several ways you can do this.

Create a separate view model:

<model name="XXX" visible="false">

        ...

</model>

<model name="views" type="VIRTUAL">

         <metadata type = "DDL"><![CDATA[

              create view table1 as SELECT EmployeName as EmpName FROM XXX.table1;

              ...

         ]]>

         </metadata>

</model>

Then you would set your physical model as hidden and issue queries such as "select * from table1" - which will resolve to views.table1 since XXX is not visible or "select * from views.table1".

You can also intermix the DDL in a single model:

<model name="XXX">

        ...

         <metadata type = "NATIVE,DDL"><![CDATA[

              create view table1_vw as SELECT EmployeName as EmpName FROM XXX.table1;

              ...

         ]]>

         </metadata>

</model>

Note the chainging of the metadata types.  Here you have to use different view name as to not conflict with the table names and you also wouldn't want to set the whole model as not visible.

This latter approach can be done programtically with a custom metadata repository, which can use the MetadataFactory to inject views with something like:

Table t = metadataFactory.addTable("table1_vw");

t.setVirtual(true);

t.setSelectTransformation("SELECT EmployeName as EmpName FROM XXX.table1");

Alternatively you can change the physical metadata.

The first approach would as I mention above is to just hard code the altered metadata:

<model name="XXX">

        ...

         <metadata type="DDL" ><![CDATA[

              create foreign table table1 (EmpName string options (NAMEINSOURCE "EmployeName"));

              ...

         ]]>

         </metadata>

</model>

The other approach is to allow a custom metadata repository to work off of the imported Tables:

<model name="XXX">

        ...

         <metadata type="NATIVE,CUSTOM" ></metadata>

</model>

Such that your custom repository would use the MetadataFactory to alter the column names:

Table t = metadataFactory.getSchema().getTable("table1");

Column c = t.getColumnByName("EmployeName");

c.setName("EmpName");

t.setColumns(t.getColumns()); //the metadata currently doesn't have the notion of an alter column, so we have to reinit the columns after modifying a name

Steve

[capoorhimanshu]

Hi ,

Thanks for the reply Steve. I am able to set the column names and table names as mentione by you above.

While working on it , one scenario striked my mind. Like i mentioned above , since teiid embedded do not provide configuration stuff , so i am writting my own basic configuration UI and persisting the configurations in XML. Now suppose when user configures the Models and VDB's at one point of time using the basic configuration UI written by me , and chosses to deploy the vdb later and in the mean time schema of the underneath data source changes , how to handle this ? As teiid embedded fetches the schema at the deployment time so the schema in the teiid engine (metadatafactory) for that particular vdb will be different from the schema on which user has configured that vdb and there will be no way to intimate the user that its time to reconfigure the model/view and vdb again.

What i was thinking first is comparing the schema stored by me in configuration to the schema fetched by translators get meta data function (i.e.called internally when we call the embeddedServer.deployVDB())) and if mismatch occurs , throw the schema change exception. If everything goes right ,fetch the exlude table information from the stored configuration and  hides the tables in metadatafactory , fetch the changes in table names or column name (for e.g. table 1 renamed to MyTable)from the configuration and set them in metadatafactory using the various ways suggested by you.

However while working on it , i believes that schema comparison can be a buggy design, so what i am thinking is to extend the translator class and override the getmetadata method in which instead of going to actual data source and fetching the meta data , i will fetch my persisted configuration convert them into the format what teiid engine expects and set them in metadatafactory so thta i do not have to do the schema validation. In this case when teiid engine will go to validate the vdb , it will throw the exception on its own since the schema of metadatafactory and underneath data sources will not match. So i do not have to validate the schema on my own as well as i will be able to prompt the error to the user that schema of the underneath data sources has been changed.

This is what i am assuming is possible , on whatever teiid code i have debugged till now or whatever little knowledge i have of the code base, however it will be really great if you can suggest me which is the right way to go , out of the two approaches i mentioned above or is there any better way to handle these kind of scenarios in teiid embedded.

Thanks & Regards

Himanshu Kapoor.

[rareddy]

Himanshu,

On the second approach should work as the Teiid does validate the schema against the views you created. If there are additional tables, or tables that you did not use in your view they will go un noticed. Only if source table reference is in the view and source table's definition of that changed (table renamed, deleted, column renamed, deleted or changed type etc) will cause an exception.

Also you do not need to store the metadata, Teiid offers caching of source metadata for server restarts. Use that if you do not want query the source each time.

Ramesh..

[capoorhimanshu]

Hi,

Thanks Ramesh, for providing the valuable suggestions. I will evaluate the second approach and will share my findings on the forum,  that how things went.

Regarding metadata, it will be really great if you can mention the code piece or API's used to cache the Meta data in teiid embedded server. It will really help me to explore that piece further. I will try

to debug that piece further and see how i can map those things in my case , as it will be really helpful for me and will also improve the server start up performance.

As if I choose to connect to source to fetch the metadata for all the deployed VDBs will definitely slow down the server start up , however I was planning to implement some kind of lazy loading around that but for sure ,I would love to evaluate the Meta data caching capabilities of teiid embedded and see if it can save me to implement the lazy loading mechanism in order to fasten the server start up.

Thanks

Himanshu Kapoor

[rareddy]

Himanshu,

Sorry, I forgot that you were working with the "embedded". The automatic caching is feature of full server version. Yes you can also do the same for embedded scenario, "MetadataFactory" is a serializable object, so first time after you load it, save it and then re-use it from there upon when ever you need to skip loading. For that you need to overload the

protected void deployVDB(VDBMetaData vdb, VDBResources resources)

method, and avoid the "loadmetadata" call, and supply the metadata factory from your cache.

HTH

Ramesh..

[shawkins]

There is too much loigc in deployVDB to be an effective place to change the load logic.  You'd want to override:

{code}
protected abstract void loadMetadata(VDBMetaData vdb, ModelMetaData model,
            ConnectorManagerRepository cmr,
            MetadataRepository metadataRepository, MetadataStore store,
            AtomicInteger loadCount, VDBResources vdbResources) throws TranslatorException;
{code}

Just be sure to call the metadataLoaded method and ideally MetadataFactory.correctDatatypes if using a serialized MetadataFactory.  It would be somewhat more convienent though if we provided a hook for just the load logic, such that it already creates the MetadataFactory and calls metadataLoad afterwords.  Alternatively you could just override the loadMetadata method to wrap the MetadataRepository by a custom MetadataRepository that has the caching logic and then just call the super method.

Steve 

[capoorhimanshu]

Hi,

I implemented the second approach i mentioned above and worked good. What i did was exteded the class from SQlExecutionFactory

@Translator(name="dvsqlserver", description="A translator for Microsoft SQL Server Database Loading meta data from persisted configuration")

public class DVSQLServerExecutionFactory extends SQLServerExecutionFactory

{

  @Override

  public void getMetadata(MetadataFactory metadataFactory, Connection conn) throws TranslatorException

  {

    JDBCConfigMetaDataProcessor metadataProcessor = new JDBCConfigMetaDataProcessor(); // config meta deta prcoseeor to load the saved configuration in teiid meta data factory insead of going to Data source

    PropertiesUtils.setBeanProperties(metadataProcessor, metadataFactory.getModelProperties(), "importer"); //$NON-NLS-1$

    metadataProcessor.getMetadata(metadataFactory);

  }

}

JDBCConfigMetaDataProcessor is the class where i implemeted logic to read the persisted configuration and load it into teiid meta data factory.

This way i was able to load the persisted configurations in teiid meta data factory while deploying the VDB , so that later when i go to excute the query over it, i can catch the schema missmatch exceptions thrown by teiid execution engine (JdbcQueryExecution) in case underneath data source has been changed from the time when configurations were saved and can easily alert the user that there is a schema mismatch.

Thanks for your suggestions .

I will look further into the meta data caching options as mentioned in the above posts , so that i do not have to load the meta data every time.

Thanks

Himanshu Kapoor