Data Virtualization

Install the data virtualization operator so that you can use it to deploy virtual database images to OpenShift from YAML-based custom resources (CRs).

After a cluster administrator adds the data virtualization operator to an OpenShift cluster, other users can run the operator to create a virtual database.

The data virtualization operator processes a virtual database custom resource (CR) to create and deploy a virtual database object on OpenShift. You specify the configuration properties for the data source in the CR. Each type of data source requires a specific set of configuration properties. When you run the operator, you provide it with a .yaml file that contains the CR. By running the operator with different CRs, you can create virtual databases from a range of data sources. For more information about specifying data source properties, see Section 3.4, “Custom resources to support data sources”

The deployed service supports connections from the following clients:

After a cluster administrator adds the data virtualization operator from the OperatorHub, other users can run the operator from web console to create a virtual database.

When you run the operator from the web console, you can use the embedded editor to create the custom resource (CR) that defines the virtual database. The CR includes properties that specify how to connect to the data source, and a DDL section that determines how information in the data source is used in your virtual database. You use YAML or JSON to define connection properties, and you use SQL-MED in the DDL section to specify the virtual database schema.

The editor includes a sample CR that you can edit in-place, or download to edit it off-line. You can also use drag and drop to upload local copies of properties definitions or DDL files. For information about the properties to use for different data sources, see Section 3.4, “Custom resources to support data sources”

Before you can use the data virtualization operator to create a virtual database, you must specify properties for the data source in a custom resource (CR) file.

By providing configuration information in a CR, you provide the operator with directions for how to create virtual databases from any of multiple types of data sources. When you run the data virtualization operator, it reads information from the CR to determine the type of the data source, its schema structure, and how to connect to it and authenticate with it.

The CR uses SQL Data Definition Language (DDL) commands to describe the schema of the virtual database and the data source, the data that you want to import into the virtual database, and the mapping between the source schema and the virtual schema. The CR also specifies the translator to use to convert the format of commands and data that pass between the virtual database and the data source.

You can specify the values of configuration properties directly in the CR file, or you can reference values that you define in OpenShift secrets. For more information about creating secrets, see Section 4.2, “Creating and deploying secrets”.

CREATE SCHEMA sourceModel SERVER oService OPTIONS ("importer.metadataUrl" '/swagger.json');

SPRING_TEIID_REST_OSERVICE_IMPORTER_METADATAURL=/swagger.json

Use Maven to generate a Java shell project that you can then modify.

Create and deploy secret objects to store values for your environment variables.

Although secrets exist primarily to protect sensitive data by obscuring the value of a property, you can use them to store the value of any property.

- name: SPRING_DATASOURCE_SAMPLEDB_PASSWORD
 valueFrom:
    secretKeyRef:
      name: postgresql
      key: database-password

Use secret objects to securely store sensitive information about how your virtual database connects to its data sources.

For OpenShift to open a connection to a data source on behalf your virtual database application, it must pass login credentials and other information to the data source. Typically, to maintain security administrators limit access to database credentials, and do not expose credentials to developers directly. To enable developers indirect access to credentials, it’s possible to deploy secret objects on OpenShift to securely store and pass credentials.

Use secrets in combination with environment variables. Rather than specify static values directly in your configuration files, you can configure OpenShift to retrieve values for environment variables from secret objects. When a key in an environment variable refers to a secret object, to obtain the key value, OpenShift examines the secret to find a token that has a name that matches the key name. It extracts the token value and then passes it to the environment variable.

For example, the following environment variable is set to retrieve the value for the database-user key from a secret object that has the name postgresql.

- name: SPRING_DATASOURCE_SAMPLEDB_USERNAME
  valueFrom:
     secretKeyRef:
       name: postgresql
       key: database-user

When the data virtualization service needs to retrieve the value for the preceding environment variable, it accesses the secret object with the name postgresql and reads the value of the database-user key.

In the following secret object, the database-user token is assigned the value bob. OpenShift passes that value to the environment variable.

apiVersion: v1
kind: Secret
metadata:
  name: postgresql
type: Opaque
stringData:
  database-user: bob

To provide your Java shell project with the details that are required to build the project, edit the pom.xml file to specify project dependencies, plugins to run, and build profiles.

Some properties in pom.xml are common to all data source types. For example, the Teiid Spring Boot starter and the Spring Boot Maven plugin are required to connect to both a postgreSQL database and a Salesforce database. Other properties, such as the drivers that a data source requires, are specific to individual data sources.

Use the sample pom.xml file in the Teiid OpenShift repository as the basis for your own file. The sample file contains settings for a postgreSQL database, but the settings in the <dependencyManagement>, <build>, and <profiles> elements apply to any data source. The build resource must be set to the Spring Boot Maven plugin. This plugin, spring-boot-maven-plugin, converts the virtual database schema project into a Spring Boot executable Uber JAR file that becomes the basis for an OpenShift container image. The OpenShift profile element must be set to use the Fabric8 Maven plugin (<artifactId>fabric8-maven-plugin</artifactId>), which helps to build a container image from the executable JAR, and optionally deploy it into OpenShift.

Modify the values in the <dependencies> element as needed to enable connectivity to the data sources that you want to use.

After you complete changes to the pom.xml file, you’re ready to define the structure of your virtual database. You define a virtual database through a text-based DDL file. You can supply an existing DDL file, if you have one, or you can create one.

If you want to create your own DDL file you can use the sample DDL file in the Teiid OpenShift repository as a guide. But be mindful that the entries in your DDL file are unique to your environment and must include settings that are specific to the data sources that you use.

The design of a DDL file can be complex and is beyond the scope of this documentation. For more information about using SQL in DDL files that support data virtualization, see the Teiid Reference Guide.

DDL files contain SQL commands that describe and define the structure of the virtual database, such as its views or schema, procedures, functions, tables, records, columns, and other fields.

When you are ready to build your virtual database, the data virtualization service reads the information in the DDL file and uses that information to generate the virtual database image.

Structures that you define in the DDL file are converted to relational database tables. Translators in the data virtualization service import and convert data from your data sources, and use the data to create views in the virtual database.

A typical DDL file define database elements by using statements similar to those in the following list:

In the application library of your virtual database project, create a Java application file, Application.java, and a data source class file, DataSources.java.

Application.java is the main Spring Boot application file that bootstraps the virtual database application. DataSources.java adds @Bean methods for each data source that you want to use as an input to your virtual database. The Java class serves as a resource adapter, which provides access to the data source.

When the virtual database starts, the virtualization service reads the metadata and generates an internal model from it. The service then uses that model to read and write to the virtual database. Entities within a data source become available in the virtual database for users to access as tables. For example, if you use a Salesforce database as a data source, then SObjects in Salesforce become available as tables in the virtual database.

The Datasources.java file adds a class to represent a connection to a data source. The file also establishes a prefix in the ConfigurationProperties argument (spring.datasource.sampledb). This prefix must be used in the names of data source properties that you specify in the application.properties file.

You can define multiple data sources in Datasources.java by adding multiple classes, each with its own prefix designation. In each case you must add corresponding entries to the DDL file and to the properties or deployment configuration files.

To associate the Java bean with the data source that is defined in your DDL file, the bean name must be the same as the name of the SERVER and resource-name properties in the DDL file. For example, the following sample file establishes a connection to a PostgreSQL database called sampledb, which is the name that is assigned in the DDL file to the data source SERVER object and to its resource-name definition.

package com.example;

import javax.sql.DataSource;

import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DataSources {

    @ConfigurationProperties(prefix = "spring.datasource.sampledb") 1
    @Bean
    public DataSource sampledb() {    2
        return DataSourceBuilder.create().build();
    }

}

You define static properties for your virtual database application in an application.properties file in the /src/main/resource directory. Static properties are configuration settings that remain constant across different environments. After you deploy a virtual database on OpenShift, any modifications that you make to the application.properties file are not effective unless you rebuild and redeploy your virtual database.

At minimum the application.properties file must contain a value for the teiid.vdb-file property, which names the DDL file that defines the structure of the the virtual database. For example, teiid.vdb-file=customer-vdb.ddl.

You can also use the application.properties file to define other properties, such as data source properties, including their names, their drivers, and the URLs, user names, and passwords that are required to connect to them. Using static properties to assign values for these can be useful in a test environment. But if you deploy your virtual database in multiple OpenShift environments, it’s best to use environment variables to dynamically assign unique values for each environment. For more information about using environment variables in your virtual database configuration, see Section 4.12, “Setting the deployment configuration”.

If you define data source properties in the application.properties file, you must prefix the configuration properties string that you specified in the Datasources.java file. The prefix establishes a connection between the properties and the Java class. For example, if you establish the configuration properties prefix spring.datasource.sampledb in the Datasources.java file, then you must precede the names of the properties that you define in your application.properties file with that string. For example,

spring.datasource.sampledb.username=<username>
spring.datasource.sampledb.password=<password>

The following application.properties file includes settings for connecting to a postgreSQL database. You might use a file similar to this for testing purposes, but for an OpenShift deployment, it’s best to specify data source properties in a deploymentconfig.yml file.

You can specify source-specfic properties for other types of data sources. For more information, see Section 4.13, “Connection settings for other data sources”.

spring.datasource.sampledb.jdbc-url=jdbc:postgresql://localhost/sampledb 1 2
spring.datasource.sampledb.username=user 3
spring.datasource.sampledb.password=user
spring.datasource.sampledb.driver-class-name=org.postgresql.Driver 4
spring.datasource.sampledb.platform=sampledb 5

# VDB location
teiid.vdb-file=customer-vdb.ddl 6

# true to allow JDBC connections
teiid.jdbc-enable=true

#logging.level.org.teiid=DEBUG 7

A deployment configuration file stores settings that govern how the Fabric8 Maven plugin builds and deploys the container image for your virtual database.

The deploymentconfig.yml file can also define environment variables for the properties that are required to configure data sources for your virtual databases. The environment variables that you define in the deploymentconfig.yml file map to properties in the application.properties file. But unlike settings in the properties file, the settings that you define in the deploymentconfig.yml file are dynamic. That is, if you add an environment variable or change its value, you do not have to rebuild the virtual database service to put the change into effect.

Environment variables and their corresponding properties have similar names, but they are formatted differently. Separators in the property names are converted from dots or dashes to underscores, and alphabetic characters are converted to uppercase.

Because you commonly deploy virtual databases to multiple OpenShift environments, for example a staging and a production environment, you typically specify different data source properties in each environment. For example, the login credentials for accessing a data source in your staging environment probably differ from the credentials that you need to access the data source in the production environment. To define unique values in each environment, you can use environment variables.

The environment variables in deploymentconfig.yml replace any static properties that you might set in the application.properties file. If a property is defined in both files, the value in the deploymentconfig.yml file takes precedence.

You use a single version of the file across environments and use secret objects to isolate the unique details of each environment. Instead of specifying static values for environment variables directly in the file, you can store the values for each deployment environment in secret objects that are unique to each environment. The value of each environment variable in the file contains only a key reference, which specifies the name of a secret object, and the name of a token in the secret. The token stores the actual value. At runtime, environment variables retrieve their values from the tokens.

By using secrets to store the values of your environment variables, you can use a single version of the deploymentconfig.yml across environments. The secret objects that you deploy in each environment must have the same name, but each secret object contains token values that are specific to its environment.

You set the deployment configuration by editing a deploymentconfiguration.yml file. You can define environment variables in the file for each data source that the virtual database uses.

To enable a virtual database to connect to a data source, you must provide connection details such as the name of the data source, the driver to use, the user credentials and so forth. You specify these settings across several files.

The sample files in the Teiid OpenShift repository, or elsewhere in this documentation provide configuration information that is consistent with using a postgreSQL database as the data source for your virtual database. If you want to a different data source, you must modify settings in the postgreSQL versions of the following files:

When the virtual database service starts, the data virtualization service scans the application’s packages for dependency annotations and uses the information to build the metadata to create the virtual database and deploy it to the OpenShift server.

<dependency>
   <groupId>org.teiid</groupId>
   <artifactId>teiid-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.teiid.spring</groupId>
    <artifactId>spring-data-salesforce</artifactId>
</dependency>

package org.teiid.spring.example;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.teiid.spring.data.salesforce.SalesforceConfiguration;
import org.teiid.spring.data.salesforce.SalesforceConnectionFactory;

@Configuration
public class DataSources {

    @Bean
    public SalesforceConnectionFactory accounts(SalesforceConfiguration config) {
        return new SalesforceConnectionFactory(config);
    }

    @Bean
    @ConfigurationProperties("spring.teiid.data.salesforce")
    public SalesforceConfiguration salesforceConfig() {
        return new SalesforceConfiguration();
    }
}

<dependency>
   <groupId>org.teiid</groupId>
   <artifactId>teiid-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.teiid.spring</groupId>
    <artifactId>spring-data-google</artifactId>
</dependency>

@Configuration
public class DataSources {

    @Bean
    public SpreadsheetConnectionFactory accounts(SpreadSheetConfiguration config) {
        return new SpreadsheetConnectionFactory(config);
    }

    @Bean
    @ConfigurationProperties("spring.teiid.data.google.sheets")
    public SpreadSheetConfiguration sheetsConfig() {
        return new SpreadSheetConfiguration();
    }
}

After you complete the configuration tasks for your virtual database, you are ready to run Maven to build the project.

The Spring Boot Maven plugin creates a self-contained Uber JAR that includes all of the application code and dependencies in a single JAR file. The resulting JAR file serves as the basis for an OpenShift image. The OpenShift profile includes a Fabric8 Maven plugin that compiles the current build with the Uber JAR to generate an image that you can deploy to OpenShift.

After you make certain changes to the virtual database configuration, for example adding protocol services or routes for the virtual database, you must re-compile the project to update the deployed image.

mvn clean install -Popenshift -Dfabric8.namespace=`oc project -q`

The relational model of the data in a virtual database is automatically mapped to JSON or XML to allow applications to consume the data through OData APIs. However, if you want the virtual database to be available to OData clients, you must explicitly define an OData service and route. The default settings in the odata-svc.yml and odata-route.yml files that are available from the Teiid OpenShift sample repository are configured to enable an OData service and route.

Do not edit instances of the variable ${project.artifactId} that appear in the configuration files. At build time, these variables are replaced automatically with information from elements in the pom.xml file.

To enable JDBC client applications to access your virtual database, you must configure a JDBC service. After the JDBC service is enabled, OpenShift applications that share the cluster with the virtual database can access the database over JDBC. Third-party client applications that are outside of the OpenShift cluster have no JDBC access to the virtual database.

To provide JDBC access to external clients, you do not create a JDBC route as you do to enable OData access to external clients. Rather, you must configure an OpenShift load balancer service to configure ingress cluster traffic. After that you must provide external applications with the IP address that the load balancer service assigns to the virtual database.

Do not edit instances of the variable ${project.artifactId} that appear in the configuration files. At build time, these variables are replaced automatically with information from elements in the pom.xml file.

After you deploy a virtual database, you can share the OData URL with application developers so that they can use REST APIs to query and retrieve data from the virtual database.

To retrieve the OData URL for virtual databases that you create using the Spring Boot Maven plugin, you run an OpenShift command. You then append /odata to the URL that command returns to establish the API endpoint for the service. .

After you finish setting up single sign-on in OpenShift, you must modify virtual database settings before you can use SSO to secure your OData APIs. You must update the following artifacts:

After you update the virtual database configuration, update the 3scale configuration to complete the integration.

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.

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.

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.

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.

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.

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.

You can run a test conversion on a legacy virtual database to check for validation errors and view the resulting DDL file. When you run the migration tool in this way, the converted DDL file is not saved.

You can run the migration tool to convert a legacy virtual database file to .ddl format, and then save the .ddl file to a specified directory. The migration tool checks the .xml file that you provide for validation errors. If there are no validation errors, the migration tool converts the file to .ddl format and saves it to the file name and directory that you specify.