Programatically design VDB

[joshuabezaleel]

Hi everyone,

I am currently planning to build a tool integrated with Ontop, an Ontology-Based Data Access framework, following this tutorial continued to this (no teiid part yet) and this (talking about teiid), and I have several questions:

1. Is there any way to programatically (and automatically, not using Teiid Designer from Eclipse) create the VDB from CSV and Excel files? From creating the Teiid Metadata Model, Connection Profile, Data Source, to creating the VDB itself.

2. I did all the steps at this tutorial, specifically at the "Create Data Source" part, but I can't seem to find .xml file (bookprices-file-ds.xml) for the Data Source. But, I can make it work through the end of the tutorial, including combining the CSV file to the existing VDB and querying it. Why is that?

Thank you very much. Any kind of help is much appreciated.

All the best for all of the Teiid team.

Regards,

Joshua.

[rareddy]

joshuabezaleel

1. Is there any way to programatically (and automatically, not using Teiid Designer from Eclipse) create the VDB from CSV and Excel files? From creating the Teiid Metadata Model, Connection Profile, Data Source, to creating the VDB itself.

Yes, there are Java-based API's available to create a VDB. It does require a certain level of familiarity with Teiid and involved APIs. For building a plain VDB you can see an example here [1]. For CSV and Excel it would be little different. As per creating the datasource, it depends on the deployed environment like WildFly. For example, in WildFly you can create datasource like as shown in here [2]. Also note in [2] how a created VDB can be deployed from your client into Teiid/WildFly server etc.

Then again you can take a completely different direction, embed the Teiid engine into OnTop using either Teiid Embedded or Teiid Spring Boot libraries, where VDB creation is same but creating the data sources is all up to your program, and Teiid engine would require a connection object to work which you would need to configure.

2. I did all the steps at this tutorial, specifically at the "Create Data Source" part, but I can't seem to find .xml file (bookprices-file-ds.xml) for the Data Source. But, I can make it work through the end of the tutorial, including combining the CSV file to the existing VDB and querying it. Why is that?

I am not sure about that perticular guide, however since you used the Teiid Designer it will create the necessary artifacts underneath. Typically all -ds.xml files define connection configuration to either database, file, excel sources. There is an option you can directly edit the "standalone.xml" file and add the contents of the -ds.xml file or place the -ds.xml file in the server's "standalone/deploy" folder then the runtime will create the connection automatically. For sample -ds.xml files look the directory "<teiid-server>/docs/teiid/datasources"

HTH

Ramesh..

[1] teiid/TestVDBUtility.java at master · rareddy/teiid · GitHub

[2] teiid/IntegrationTestDeployment.java at 11.2.x · teiid/teiid · GitHub

[joshuabezaleel]

Hi Ramesh,

First of all, thank you very much for your thorough response. Definitely will look into it.

I did some searching and I found out the term "Dynamic VDB" from here and here. Is that not the case anymore for creating VDB without Teiid Designer, or does the Java code that you gave me earlier practically did the same thing as creating the .xml file (for Dynamic VDB) with a program?

And from here, it said that "Starting with Teiid 8.13 there is no longer an embedded distribution. You can still should run directly from maven or your IDE".  Is this still case, where embedded is not supported anymore?

Thank you very much.

Regards,

Joshua

[rareddy]

Joshua,

Yes, the Java API I showed above do create the Dynamic VDB. A Dynamic VDB is simple XML file with embedded DDL for metadata part. There is a lot of documentation on DDL constructs that can be used, or you can define the metadata programmatically too as all of them have API behind them. Previously we did a .zip file for Embedded Bundle, we stopped that. All the JAR files are now in maven central, so as long as you can use tools like Maven or Gradle you can refer to the needed modules from their maven co-ordinates. Take look at one of the embedded examples [3].  Note that you can use Dynamic VDBs with Teiid Server too, but note the Admin API to create the datasources is only available in Teiid Server mode, as I mentioned above in Embedded Model you are responsible for creating your own connections to sources. Teiid Spring Boot library gives the functionality somewhere in between the server and embedded mode.

Ramesh..

[3] GitHub - teiid/teiid-embedded-examples: Examples for Teiid(http://teiid.org)

[joshuabezaleel]

Hi Ramesh,

I see that from [1] we can create the VDB programatically by using the org.teiid.adminapi.impl.VDBMetaData (please correct me if I am wrong) and from [2] we can deploy VDB by using the admin.deploy method, but are those two different? The first one is the programmaticaly created VDB while at the [2], the VDB is already exist in the form of .vdb or .xml file. How can we deploy to the Teiid Server the VDB that we created at [1]? Because the parameter that admin.deploy method can take is just string (a vdb file that is already created (.vdb or .xml) and the path to the file), not an instance of VDBMetaData Class like the one we define in [1].

Thank you very much.

Regards,

Joshua

[1] teiid/TestVDBUtility.java at master · rareddy/teiid · GitHub

[2] teiid/IntegrationTestDeployment.java at 11.2.x · teiid/teiid · GitHub

[rareddy]

You marshall the VDBMetadata object to get an XML format of the data and pass into deploy methods as the input stream. Use VDBMetadataParser class for marshall and unmarshall.

[joshuabezaleel]

Hi Ramesh,

I really thank you for the answers the you've been giving me. Will definitely take a look at the VDBMetadataParser class for marshalling and unmarshalling.

I saw from the Quick Start page [1] that we need to create the connection to the text file data source by adding the .xml below to the "resource-adapter" subsystem at the standalone-teiid.xml configuration file :

<resource-adapter id="fileQS">     <module slot="main" id="org.jboss.teiid.resource-adapter.file"/>     <connection-definitions>         <connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory"         jndi-name="java:/marketdata-file" enabled="true" use-java-context="true" pool-name="fileDS">             <config-property name="ParentDirectory">                 ${jboss.home.dir}/teiidfiles/             </config-property>             <config-property name="AllowParentPaths">                 true             </config-property>         </connection-definition>     </connection-definitions> </resource-adapter>

I tried to search for the keyword org.teiid.resource.adapter.file.FileManagedConnectionFactory at the teiid/teiid repo and found this code at this Class file [2]:

Properties props = new Properties();

props.setProperty("ParentDirectory", UnitTestUtil.getTestDataFile("/arquillian/txt/").getAbsolutePath());

props.setProperty("AllowParentPaths", "true");

props.setProperty("class-name", "org.teiid.resource.adapter.file.FileManagedConnectionFactory");

AdminUtil.createDataSource(admin, "test-file", "file", props);

Are those 5 line of code that all we need to create datasource/create a connection to the text file based source? How about other metadata like "slot=main","use-java-context=true", "id=org.jboss.teiid.resource-adapter.file" and others? Do we not need to add them?

Thank you very much. All the best for the Teiid team.

Regards,

Joshua

[1] Hello World Teiid Data Federation Example - Teiid Examples - Project Documentation Editor

[2] teiid/IntegrationTestMultisource.java at 1f2872b03147b7638a4143f027be41bff495d8d2 · teiid/teiid · GitHub

[rareddy]

Joshua,

The connection creation is very specific to the environment in which Teiid is being used. The XML you showed is applicable in the WildFly environments, and so is the java code. For embedded see examples here teiid-embedded-examples/embedded-portfolio at master · teiid/teiid-embedded-examples · GitHub