Dynamic VDB and DDL, best practice?

[jason.marley]

What is best practice for creating dynamic VDB's? Generally I usually generate a DTD using a database tool and then export to DTD then add it to the dynamic VDB. Is the DTD the preferred approach to working with a Dynamic VDB? To me, it certainly seems the easiest, minus the whole generating a DTD and then adding to -vdb.xml file.

[rareddy]

No need to generate DTD, a XSD file is provided for the -vdb.xml file is provided. Also the file will be validated during the deployment. If your question is for generating java classes to build the Dynamic VDB for ease of development, there are couple choices

* Use the xml schema provided for the -vdb.xml file https://github.com/teiid/teiid/blob/master/client/src/main/resources/vdb-deployer.xsd

* Although not public API, you can use teiid/admin/src/main/java/org/teiid/adminapi/impl/VDBMetaData.java at master · teiid/teiid · GitHub, and see example of usage at https://github.com/teiid/teiid/blob/master/admin/src/test/java/org/teiid/adminapi/impl/TestVDBMetaData.java

Ramesh..

[jason.marley]

Haha, I meant to say DDL not DTD .

Maybe I am looking at the Dynamic VDB differently (wrongly). But my understanding was the Dynamic VDB is primarily used to bridge the gaps between the teiid designer and datasources that do not have a translator available to it. For this use case is it more advantageous to create a Dynamic VDB using DDL so that it can then be deployed and added to the teiid designer and used as a virtual base layer?

I think I understand the use case that you've provided above where a developer wants to create a database on the fly for their application, which would build a dynamic vdb? For the developer they have to create a pojo using the XSD and then develop the vdb from that, but would it be easier to generate a DDL via a tool then add into Dynamic VDB?

[rareddy]

Jason,

Wow, too much confusion! DTD != DDL

Dynamic VDBs are another simpler way of creating the VDBs without tooling. This got no relation to bridge the gaps with Designer based VDB.  Two totally independent ways of creating VDBs. Also currently only Dynamic VDBs can be programatically created.

Also DDL metadata and Dynamic VDB are two different inter-related concepts. VDB represents a schema package, where as DDL is representation of metadata in that package. Dynamic VDBs can be designed to work with DDL or you can "plugin" a "custom metadata repository" to read the metadata from. DDL is just externalized form that it understands works right OOB. Yes, using tooling can be easier but in many situations we need the ability to dynamically create vdbs or you can think of it as head-less mode.

The translator is essential part either in Dynamic VDB or Designer VDB. Both of them need a translator to read from a source. Previously (MMx and early Teiid days) Designer tooling was THE only way to read metadata from the sources. That is not case anymore, most of the translators can read metadata from source, that is what Dynamic VDB takes advantage of in building the metadata from source. However there are couple translators like MongoDB and LDAP the translators can not read metadata from source. In those situations, you can supply metadata using DDL in Dynamic VDBs.

We also following the trend in using the translator to read metadata int he Designer. So, when ever you use "Teiid Connection --> Source" importer, underneath it is using the same translator logic as dynamic vdb to import the metadata.

HTH

Ramesh..

[jason.marley]

This does help a lot (the more I know, the more I understand what I don't )

Do these Dynamic VDB use cases make sense:

Use Case: Developer wants to use a database (e.g. mongoDB), but wants to use a simple api, so they would then use the teiid (mongodb) translator to hook into mongoDB and would then be able to get metadata and could create a dynamic vdb. Then the developer can write an application that queries the vdb?

Use Case: DBA wants to federate multiple sources (e.g. mongoDB, greenPlum, etc.) using the teiid designer. The DBA would first want to create dynamic VDB for each source that doesn't have a plugin for designer and then deploy to app server, then add those VDB's as a source so that the teiid designer could be used to federate them?

Jason

[shawkins]

> Then the developer can write an application that queries the vdb?

Yes, using Teiid as a JDBC/ODBC/etc. access library to Mongo or other sources is a great dynamic vdb usecase.

> The DBA would first want to create dynamic VDB for each source that doesn't have a plugin for designer and then deploy to app server, then add those VDB's as a source so that the teiid designer could be used to federate them?

Teiid Designer has been working on refining this functionality in the last several releases with a generic importer that can target a deployed translator instance.  It's perfectly valid though to do that on your own with dynamic vdb's and vdb imports. 

[jason.marley]

Thanks guys, that makes things more clear.

For the longest time I've always viewed teiid designer as the point where developers could hook into what a DBA has done (regardless the model complexity), but in fact that is just 1 use case. I mean unless the virtual model is complicated with federation and DB management, it probably makes more sense to create the dynamic vdb directly in development?

Is it safe to say, teiid designer is primarily for federation or quickly experimenting with slicing and dicing of data for DBA/Data Analyst users?

[rareddy]

> Then the developer can write an application that queries the vdb?

To add to Steve's response, just having the Dynamic VDB does not mean you have access to the source, you need to have a translator that understands the source, then that can be used in the Dynamic VDB. On top of it, translator should have capability to interpret source schema into Teiid schema (salesforce, oracle, db2, solr..) to be used. If the translator does not support the metadata retrieval from source to convert into Teiid metadata (like mongo, ldap), you can inject through DDL.

> DBA wants to federate multiple sources (e.g. mongoDB, greenPlum, etc.) using the teiid designer.

Designer is tooling to build the VDB artifacts, that has models etc graphical environment to provides the ease of use. It should not be viewed as source of federation. You can do federation using the Dynamic VDBs too, however you would need to provide all the necessary DDL(metadata) for the views.

> I mean unless the virtual model is complicated with federation and DB management, it probably makes more sense to create the dynamic vdb directly in development?

Surely if the user is only looking at integration (no views) and translators are capable of reading metadata then Dynamic VDB is simpler. If you working to create view layer that is different from source layers then Designer helps tremendously in the ease of use. Also don't forget the programatic angle on the Dynamic VDB.

>teiid designer is primarily for federation or quickly experimenting with slicing and dicing of data for DBA/Data Analyst users?

Preview functionality in Designer is primarily for quickly pulling data together and experimenting during  development. Apart from that Designer should not be used as analytical tool. The intended use is to build the VDBs that can be deployed into Teiid runtime, then you can use any tool/app on top using one of the exposed APIs.

Ramesh..

[jason.marley]

Thanks Guys, always a big help!