Chapter 2. Creating virtual databases

To add a virtual database, you must complete the following tasks:

  1. Install the Data Virtualization Operator.
  2. Design and develop the database.

    Note

    If you want to use a custom TLS certificate to encrypt virtual database traffic, you must obtain and configure the certificate before you deploy the virtual database. For more information, see Section 7.1, “Certificates and data virtualization”.

  3. Create a custom resource (CR) file for deploying the database.
  4. Deploy the virtual database to OpenShift by running the Data Virtualization Operator with the CR.

You can use the following methods to design a virtual database.

Create a virtual database from a DDL file
Define the entire contents of a virtual database, including the DDL, in a YAML file. For more information, see Chapter 3, Creating a virtual database by embedding DDL statements in a custom resource (CR) .
Create a virtual database as a Maven artifact
Create a virtual database from one or more DDL files and generate a Maven artifact for deployment For more information, see Chapter 4, Creating a virtual database as a Maven artifact.

In each of the methods, you use SQL data definition language (DDL) to specify the structure of the virtual database, and you then configure the data sources that you want the virtual database to read from and write to.

There are advantages and disadvantages to using each method, the runtime virtualizations that any of the methods create have equivalent features. Choose a method based on the complexity of your project and on whether you want to be able to test the virtualization as a standalone component or on OpenShift only.

After you define the virtual database, you use the Data Virtualization Operator to deploy the virtualization from a custom resource (CR). The custom resource that you use to deploy a virtual database varies with the method that you used to design the virtual database. For more information, see Chapter 6, Running the Data Virtualization Operator to deploy a virtual database.

After you set up connections to a data source, you can optionally configure authentication to Red Hat SSO to secure the connections, and enable single sign-on.

Note

You can also create virtual databases in Fuse Online (Technology Preview). Virtual databases that you create in Fuse Online provide a limited set of features.

2.1. Compatible data sources

You can create virtual databases from a range of different data sources.

For each data source that you configure, you specify a set of properties in a custom resource (CR) YAML file. A translator property specifies the name of a component that provides the logic to interpret the commands and data exchanged that pass between the data source and the virtual database. Each data sources uses a specific named translator.

The following table lists the data source types that you can include in a virtual database, and the names of the translators for each data source:

Data sourceTranslator name

Amazon S3/ Ceph

amazon-s3

Google Sheets

google-spreadsheet

Data Grid (Infinispan)

infinispan-hotrod

MongoDB

mongodb

Relational databases

 
 

Amazon Athena

amazon-athena or jdbc-ansi

 

Amazon Redshift

redshift

 

Db2

db2

 

Microsoft SQL Server (JDBC)

sqlserver

 

MySQL

mysql

 

Oracle

oracle

 

PostgreSQL

postgresql

 

SAP HANA (JDBC)

hana

OData

odata

OData4

odata4

OpenAPI

openapi

REST

ws

Salesforce

salesforce

SFTP

file

SOAP

soap or ws

2.2. Creating custom resources to deploy virtualizations

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.

When you run the Data Virtualization Operator, it reads information from the CR that it needs to convert a data virtualization artifact into an image and deploy it to OpenShift.

Properties in the CR specify environment variables that store the credentials that the Operator requires to connect to a data source. You can specify the values directly in the CR, or provide references to an OpenShift secret that stores the values. For more information about creating secrets, see Section 7.2, “Using secrets to store data source credentials”.

Note

Period characters (.) are not valid for use in environment variables. When you add variable names to the CR, use underscore characters (_) as separators.

The information that you add to the CR depends on the type of artifact that you created for the virtualization and the location of artifact. You can also supply configuration information in the CR.

Note

If you want OpenShift to create an HTTP endpoint for the deployed virtualization, add the property spec/exposeVia3scale to the CR, and set its value to false. If the value is set to true it is assumed that 3scale manages the endpoint, and no HTTP endpoint is created.

2.2.1. Configuring an OpenShift load balancer service to enable external JDBC clients to access the virtual database

After you deploy a virtual database, it is automatically available to internal JDBC clients, that is, clients that are installed on the OpenShift cluster that hosts the virtual database. By default, external JDBC clients are unable to access the virtual database service. To enable external clients to access the virtual database service, you must add an OpenShift load balancer service.

To configure a load balancer for the virtual database, you define an attribute in the custom resource. Afterwards, when you run the Data Virtualization Operator to build and deploy the virtual database, the Operator creates the load balancer service automatically.

Note

Although OpenShift typically requires you to create a route to the service that you want to expose, you do not have to create routes for virtual database services that you deploy with the Data Virtualization Operator. When the Operator deploys the virtual database, it automatically exposes the JDBC route to the virtual database service.

Prerequisites

  • You have access to an OpenShift cluster that permits you to add a LoadBalancer Ingress Service.
  • You have a custom resource (CR) to which you can add the attribute to enable the load balancer service.

Procedure

  1. Add a load balancer service for the virtual database by setting the value of spec.expose in your virtual database CR to LoadBalancer.

    To provide flexibility in exposing other resources in the future, precede the value with a hyphen (-) to indicate that it is an element in an array, as in the following example:

    apiVersion: teiid.io/v1alpha1
    kind: VirtualDatabase
    metadata:
      name: dv-customer
    spec:
      replicas: 1
      expose:
        - LoadBalancer
    ....
  2. After you deploy the virtual database, you can run the following command from a terminal window to identify the exposed host and port:

    oc get svc VDB_NAME-external

    For example,

    oc get svc dv-customer-external

    The command returns network information for the service, including the cluster IP address, external host name, and port number and type. For example:

    NAME               TYPE          CLUSTER-IP     EXTERNAL-IP                            PORT(S)         AGE
    dv-customer-ingress  LoadBalancer  172.30.22.226  ad42f5d8b303045-487804948.example.com  3306:30357/TCP  15m

Additional resources

2.2.2. Environment variables in custom resources

You set environment variables in the custom resource file to enable your virtual database to connect to data sources.

Because you typically deploy virtual databases to multiple OpenShift environments, such as to a staging and a production environment, you might want to define different data source properties for each environment. For example, the login credentials that you must provide to access a data source in the staging environment are probably different from the credentials that you use to access the data source in the production environment. To define unique values in each environment, you can use environment variables.

The environment variables that you define in a CR replace any static properties that you might set elsewhere. If you define a property in the properties file and in the CR, the value in the CR file takes precedence.

You can combine the use of environment variables and secret objects to specify and protect the unique details for each environment. Instead of specifying static values for environment variables directly in the CR, 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 CR 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 CR across environments. The secret objects that you deploy in each environment must have the same name, but in each environment you assign token values that are specific to the environment.

Additional resources