Utilize the MySQL database on OpenShift Data Virtualization in Designer

This article will show you how to utilize the MySQL database on your OpenShift Data Virtualization instance.  We'll guide you through the steps required to create the source model in Teiid Designer, so that you can join it with any other sources, then query using Teiid.

Prerequisites:

Please follow these articles, which will instruct you to deploy Data Virtualization to OpenShift, add a MySQL database and connect to the Data Virtualization instance from Teiid Designer.

1. Provision Data Virtualization on OpenShift

2. Add a MySQL database to your OpenShift Data Virtualization instance

3. Provision Data Virtualization on OpenShift and Connect from Teiid Designer

Create the MySQL source model in Teiid Designer

1. Launch JBoss Developer Studio and switch to the Teiid Designer perspective.  The perspective options are shown in the upper right corner of JBDS.

2. Connect to the Data Virtualization instance in Teiid Designer as demonstrated in article 3.

3. Create a Model Project (or you can use any existing project).

• in ModelExplorer, Rt-click > New > Teiid Model Project
• in the Model Project wizard, enter MyProj for the Project name.  Click Finish to create the empty project:

4. Select the project, then Rt-click > Import... You will see the import dialog (shown below).  Select Teiid Connection >> Source Model then click Next.  We are using the Teiid Connection Importer since we cannot directly connect to the DB port on OpenShift.  This importer allows us retrieve the DB schema through Teiid.

5. On the first page of the import wizard, select the MySQLDS DataSource.  Click Next.

6. On the second page of the wizard, enter MySqlSourceMdl for the name of the Source Model to generate.  Click Next.

7. The third page of the wizard shows the source schema DDL produced by Teiid.  Click Next.

8. On the final page, the table which will be created (PricesTable) is displayed.  Click Finish.

Source Model in Model Explorer

Upon completion of the import, you will see the source model MySqlSourceMdl displayed in the Model Explorer view:

Notice that the imported model has errors.  Click on the bottom item (Unique Constraint PRIMARY), then Rt-Click > Delete to remove it. This item is not needed and will not be generated in future Designer releases.

The source model can now be used in addition to other sources that you may have.

Conclusion

We've shown how to import the MySQL database on OpenShift Data Virtualization into Teiid Designer.  You can join the MySQL data with any other sources, add to a VDB and deploy the VDB to the Data Virtualization instance.  Then you may even choose to expose the data as an OData feed - as shown in this article, or as a REST service - as shown in this article.