Chapter 28. Connecting to virtual databases

In Fuse Online, you can create a virtual database that integrates data from multiple data sources that you choose. After you deploy the resulting virtual database service, you can connect to as if it were a single physical database.

Important

Data virtualization in Fuse Online is a Technology Preview feature only. Technology Preview features are not supported with Red Hat production service level agreements (SLAs) and might not be functionally complete. Red Hat does not recommend using them in production. These features provide early access to upcoming product features, enabling customers to test functionality and provide feedback during the development process. For more information about the support scope of Red Hat Technology Preview features, see https://access.redhat.com/support/offerings/techpreview/.

After you create a virtual database in Fuse Online, you can use Fuse Online tools to:

  • Add or remove data sources.
  • Add or edit views of data from different tables or sources.
  • Submit SQL queries to test that views return the expected results.
  • Modify the schema that defines the virtual database.
  • Publish the virtual database to make it available on OpenShift.
  • Delete the virtual database.

Prerequisites

  • The data virtualization UI was enabled during installation.

For Fuse Online on OCP installations, see the Descriptions of custom resource attributes that configure Fuse Online section in Installing and Operating Fuse Online on OpenShift Container Platform.

28.1. Creating a virtual database in Fuse Online

In Fuse Online, you can create virtual databases that import views from applications or services that are available from the Connections page.

For each virtual database that you create, you must import data sources, and select the tables from each data source that you want to include. The views in the resulting virtual database map directly to the database tables that you import. After the initial creation, you can add views to a virtual database that join data from more than one table.

Note

In this release, you can create virtual databases in Fuse Online only from relational databases, MongoDB, and Salesforce.

Prerequisites

  • Your Fuse Online environment has a connection to one or more of the following data sources:

    • Relational database, such as postgreSQL or MySQL.
    • MongoDB database
    • Salesforce database

Procedure

  1. From the navigation sidebar in Fuse Online, click Data.
  2. Click Create Data Virtualization.
  3. On the Create new Data Virtualization page, type a name for the virtual database and then click Create.

    • Provide a name that informs people about the database contents or purpose, and that is short enough for application developers and SQL users to easily insert in their code.
    • Names can include only alphanumeric ([a-z]|[A-Z], [0-9]), and hyphen (-) characters.
  4. On the Create views page, click Import views.
  5. On the Select connection page, click the tile for an active data source, and then click Next.
  6. On the Select views page, select one or more tables to include in your virtual database and then click Done.

    A confirmation message reports when the import completes. The Views tab for the draft virtualization lists a view for each table that you imported.

    You can now edit the existing views, create another view, or publish the virtual database to make it available for use.

28.2. Adding a view to a virtual database

Add a view to a virtual database to provide a view of the data in a new table.

After you first create a virtual database, it contains only the views that you imported from the initial data source. Add views to the virtual database if you want to incorporate data from other tables. You can add views based on tables in the original data source or from other data sources.

Prerequisites

  • The virtual database that you want to add a view to is available in Fuse Online in a Stopped or Published state. You cannot use Fuse Online to add views to virtual databases that were created outside of Fuse Online.
  • A Fuse Online connection exists to the data source that contains the table that you want integrate.
  • You know the name of the table that you want to use in the view.

Procedure

  1. From the navigation sidebar in Fuse Online, click Data.
  2. From the list on the Data Virtualizations page, find the virtual database that you want to modify and click Edit.
  3. Click Create a view.
  4. Expand a data source to view the tables that it contains.
  5. Select the table that you want to add to the virtual database, and then click Next.
  6. On the Name your view page, type a name in the View Name field, and then click Done.

    The View editor displays the SQL for the view that you created. The Preview panel displays the data in the view.

  7. If no data displays, click the Refresh icon.
  8. Click Done to close the view.

    If the virtual database was previously published, you must republish it to make the new view available.

Additional resources

28.3. Using the View Editor in Fuse Online to modify the DDL that defines a virtual database

The process of creating a virtual database in Fuse Online is designed to automate many tasks and hide the complexities of the underlying SQL code.

When you create a view for a virtual database, Fuse Online automatically generates the data definition language (DDL) that defines the view. The DDL is a set of SQL statements that describe the view’s schema, tables, columns, and other fields.

Fuse Online provides tools to add basic views for a virtual database, but if you know SQL and you want greater control in designing a view, you can directly edit the DDL for the view. In Fuse Online, developers can use the embedded View Editor to modify these SQL statements. To assist you, this SQL editor includes a code-completion feature that provides a list of SQL keywords.

After you save your changes, a built-in validation tool runs to ensure that the SQL code does not contain syntax errors.

Prerequisites

  • You have experience using a data definition language (DDL) that is based on the SQL-MED specification to define database structures and to integrate externally stored data.

Procedure

  1. From the navigation sidebar in Fuse Online, click Data.
  2. On the Data Virtualizations page, find the virtual database that you want to modify and click Edit.
  3. In the Views tab, find the view that you want to edit, and then click Edit.
  4. Update the SQL as needed. As you edit, press Ctrl+Space to open the code completion tool.
  5. After you complete your changes, click Save.

    Fuse Online validates the SQL and returns an error if the view contains invalid code.

    After the SQL validates, the preview panel shows the result of the updates that you made to the view. The preview displays the first fifteen rows of the results set.

  6. Click Done to close the View Editor and return to the list of views.

    If the virtual database was previously published, you must republish it to put your changes into effect.

Additional resources

28.4. Previewing a virtual database in Fuse Online by submitting SQL test queries

Before you publish a virtual database and make it available to applications, you can run test queries against its views to verify that it returns the information that you expect.

Although the default preview shows you the first 15 results returned when a SQL SELECT * FROM statement is submitted to a virtual database view, you can use the embedded SQL client in Fuse Online to send modified test queries to your views. You can adjust the default results set by specifying the row limits and row offsets.

If the view that you query originates from a non-SQL data source, the data virtualization engine converts the SQL query into a format that the data source can interpret.

Prerequisites

  • You have a valid virtual database that was created in Fuse Online.

Procedure

  1. From the navigation sidebar in Fuse Online, click Data.
  2. On the Data Virtualizations page, click Edit in the entry for the virtual database that contains the view that you want to test.
  3. Click the SQL client tab.
  4. From the View field, select the view that you want to test.
  5. In the Row limit field, specify the number of rows to display.
  6. In the Row offset field, specify the number of rows to skip.
  7. Click Submit. The Query results table displays the result set.

28.5. Publishing virtual databases in Fuse Online to make them available for access

After you define a virtual database in Fuse Online, you must publish it to make it available for users and applications to access.

Publishing a virtual database builds the schema definition that you implemented by importing data sources and views into a runtime image. Fuse Online deploys the runtime image to OpenShift as a virtual database container image that you can scale independently.

After you publish the virtual database, it becomes available as a service and is represented on the Fuse Online Connections page. The service behaves like any relational database, and clients can connect to it over standard interfaces. It can be incorporated into Fuse Online integration workflows, and it is available to JDBC and OData clients.

Prerequisites

  • You created a virtual database in Fuse Online.
  • You added any views that you want to the virtual database.

Procedure

  1. From the navigation sidebar in Fuse Online, click Data.
  2. On the Data Virtualizations page, find a virtual database that you want to publish, click Three Vertical Dots and then click Publish.

    A confirmation message notifies you that the virtual database was submitted for publishing, and a progress bar reports the status of the process.

    • If the publishing process succeeds, Fuse Online makes the following updates:

      • The status label of the virtual database entry on the Data virtualizations page changes from Stopped to Running.
      • The virtual database entry displays a URL link to the OData endpoint for the virtual database.
      • The virtual database service is added to the Connections page, and a JDBC connection to it is created.

        You can verify the JDBC URL by opening the entry for the virtual database service from the Connections page.

    • If the publishing process fails, the entry is flagged with the label Error.

28.6. Deleting a virtual database in Fuse Online

You can permanently delete virtual databases that you create in Fuse Online. You can delete virtual databases whether they are published or in draft.

The data sources that a virtual database consumes are not affected by the deletion. Connections between Fuse Online and the data sources remain in place.

Prerequisites

  • You have a virtual database that was created in Fuse Online and you want to remove it.

Procedure

  1. From the navigation sidebar in Fuse Online, click Data.
  2. On the Data Virtualizations page, click the overflow menu for the virtual database that you want to delete, and then click Delete.
  3. When prompted, click Delete to confirm that you want to delete the virtual database.

    A confirmation message reports when the virtualization is deleted.