Procedure

1. Open a text editor, create a file with the name of the virtualization, and save it with the extension .yaml, for example, dv-customer.yaml.

2. Add information to define the custom resource kind, name, and source. The following annotated example provides guidance on the contents to include in the CR:

   dv-customer.yaml

   apiVersion: teiid.io/v1alpha1
   kind: VirtualDatabase
   metadata:
     name: dv-customer
   spec:
     replicas: 1
     env:
     - name: SPRING_DATASOURCE_SAMPLEDB_USERNAME 1
     value: user
     - name: SPRING_DATASOURCE_SAMPLEDB_PASSWORD
     value: mypassword
     - name: SPRING_DATASOURCE_SAMPLEDB_DATABASENAME
     value: sampledb
     - name: SPRING_DATASOURCE_SAMPLEDB_JDBCURL 2
     value: jdbc:postgresql://postgresql/$(SPRING_DATASOURCE_SAMPLEDB_DATABASENAME)
     resources:
       memory: 1024Mi
       cpu: 2.0
     build:
       source:
         maven: com.example:customer-vdb:1.0.0:vdb 3
       mavenRepositories: 4
         central: https://repo.maven.apache.org/maven2

   1

   Specifies the credentials for signing in to the data source. Although this example shows credentials that are defined within the CR, in production use, use secrets to specify credentials, rather than exposing them in plain text. For information about adding credentials to secrets, see xref:

   2

   Specifies the URL for connecting to the data source.

   3

   Specifies the Maven location of the virtual database by providing the groupId, artifactId, and version (GAV) coordinates.

   4

   If you are using a private Maven repository, specify its URL.

Procedure

1. Add an application.properties file to the src/main/resources folder of your Java project.

2. Within the file, define properties that are required to connect to your data source, such as authentication credentials.

   Note

   Properties that you do not define in the application.properties file, must be defined in the CR YAML file.

   Note

   If you define a property in application.properties and define a corresponding environment variables in the CR, the value in the CR takes precedence over the value that is set in the application.properties file.

   For example:

   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
   
   # spring overrides
   spring.teiid.model.package=io.integration
   spring.main.allow-bean-definition-overriding=true
   
   # open jdbc/odbc ports
   teiid.jdbc-secure-enable=true
   teiid.pg-secure-enable=true
   teiid.jdbc-enable=true
   teiid.pg-enable=true
   
   # How to debug?
   #logging.level.org.teiid=DEBUG 6

   1

   The JDBC URL that the virtual database uses to connect to a local postgreSQL database as its data source.

   2

   The prefix that is used in each of these properties matches the prefix that is defined in the Datasources.java file.

   3

   The user name and password values listed here are displayed in plain text. To enable secure storage of authentication credentials, use environment variables in a CR file to reference the secret object that defines these values.

   4

   The driver that is required to connect to the data source. The driver that you reference here must be defined as a dependency in the pom.xml file. For an example of a pom.xml file for creating a virtual database as a fat JAR, see the teiid/dv-customer-fatjar repository.

   5

   The name of the data source.

   6

   Uncomment this statement to enable debug logging.

Procedure

1. . Open a text editor, create a file with the name of the virtualization, and save it with the extension .yaml, for example, dv-customer.yaml.

2. Add information to define the custom resource kind, name, and source.

   The following example shows a CR that is designed to deploy a virtual database that is created as a fat JAR:

   apiVersion: teiid.io/v1alpha1
   kind: VirtualDatabase
   metadata:
     name: dv-customer
   spec:
     replicas: 1
     env:
     - name: SPRING_DATASOURCE_SAMPLEDB_USERNAME 1
       value: user
     - name: SPRING_DATASOURCE_SAMPLEDB_PASSWORD
       value: mypassword
     - name: SPRING_DATASOURCE_SAMPLEDB_DATABASENAME
       value: sampledb
     - name: SPRING_DATASOURCE_SAMPLEDB_JDBCURL
       value: jdbc:postgresql://postgresql/$(SPRING_DATASOURCE_SAMPLEDB_DATABASENAME)
     resources:
       memory: 1024Mi
       cpu: 2.0
     build:
       source:
         maven: org.teiid:dv-customer-fatjar:1.1 2

Installing on OpenShift 3.11

1. From a terminal window, log in to the OpenShift cluster as a cluster administrator.

   oc login

2. Create or open a project where you want to deploy a virtual database.

3. Type the following commands:

   export OP_ROOT=https://raw.githubusercontent.com/teiid/teiid-operator/7.6-0.0.x/deploy
   oc create -f $OP_ROOT/crds/virtualdatabase.crd.yaml  1
   oc create -f $OP_ROOT/service_account.yaml
   oc create -f $OP_ROOT/role.yaml
   oc create -f $OP_ROOT/role_binding.yaml
   oc create -f $OP_ROOT/operator.yaml

   1

   If you previously created a CRD in the cluster, the command returns an error, reporting that the CRD already exists. You can ignore the message.

4. Type the following commands to create a pull secret that you can use to access the Red Hat image registry:

   oc create secret docker-registry dv-pull-secret /
   --docker-server=registry.redhat.io /
   --docker-username=$username / 1
   --docker-password=$password /
   --docker-email=$email_address
   oc secrets link builder dv-pull-secret
   oc secrets link builder dv-pull-secret --for=pull

   1

   Substitute the user name and password that you use to log in to the Red Hat Customer Portal.

   If the command completes with no errors, the operator is deployed to your OpenShift instance within the current OpenShift project.

5. To enable the data virtualization operator to retrieve images from the Red Hat registry so that you can create virtual databases, link the secret that you created in Step 4 to the service account for the operator.

   oc secrets link dv-operator dv-pull-secret --for=pull

Installing on OpenShift 4.2 or greater

1. From a terminal window, type the following commands to log in to the OpenShift cluster and create a pull secret that you can use to access the Red Hat image registry:

   oc login
   oc create secret docker-registry dv-pull-secret /
   --docker-server=registry.redhat.io /
   --docker-username=$username /  1
   --docker-password=$password /
   --docker-email=$email_address
   oc secrets link builder dv-pull-secret
   oc secrets link builder dv-pull-secret --for=pull

   1

   Use your Red Hat Customer Portal login credentials.

2. Log in to the OpenShift web console as a cluster administrator.
3. From the OpenShift menu, expand Operators and click OperatorHub.
4. Click Data Virtualization Operator 7.6.0 provided by Red Hat, Inc., and then click Install.

5. From the Create Operator Subscription page, verify that the selected namespace matches the name of the project where you want to install the operator, and then click Subscribe.

   The Installed Operators page lists the Data Virtualization Operator and reports the status of the installation.

6. From the OpenShift menu, expand Workloads and click Pods to check the status of the operator pod. After a few minutes, the pod for the operator service begins to run.

7. To enable the data virtualization operator to retrieve images from the Red Hat registry so that you can create virtual databases, link the secret that you created in Step 1 to the service account for the operator.

   oc secrets link dv-operator dv-pull-secret --for=pull

Procedure

1. From a terminal window, log in to OpenShift and open the project where you want to create the virtual database.
2. On your computer, change to the directory that contains the .yaml file that contains the CR.

3. Type the following command to run the operator to create the virtual database:

   oc create -f <cr_filename.yaml>

   Replace <cr_filename.yaml> with the name of the CR file for your data source. For example,

   oc create -f dv-customer.yaml

   After the deployment completes, a virtual database service is added to the OpenShift cluster. The name of the service matches the name that is specified in the custom resource.

4. Type the following command to verify that the virtual database is created:

   oc get vdbs

   OpenShift returns the list of virtual databases in the project. To see whether a particular virtualization is available, type the following command:

Procedure

1. Create a YAML file to define a secret of type kubernetes.io/tls, and include the following information:

   • The public and private keys of the TLS key pair.
   • The name of the virtual database that you want to create.

   • The OpenShift namespace in which you want to create the virtual database.

     For example:

     apiVersion: v1
     kind: Secret
     type: kubernetes.io/tls
     metadata:
       name: dv-customer 1
       namespace: myproject 2
     
     data: 3
       tls.crt: >-
           -----BEGIN CERTIFICATE-----
           [...]
           -----END CERTIFICATE-----
       tls.key: >-
           -----BEGIN PRIVATE KEY-----
           [...]
           -----END PRIVATE KEY-----

     1

     The name of the secret. The secret name must match the name of the virtual database object in the CR YAML file that the Data Virtualization Operator uses to create a virtual database, for example, dv-customer.

     2

     The OpenShift namespace in which the virtual database service is deployed, for example, myproject.

     3

     The data value is made up of the contents of the TLS public key certificate (tls.crt), and the private encryption key (tls.key) in base64-encoded PEM format.

2. Save the file as tls_secret.yaml.

3. Open a terminal window, sign in to the OpenShift project where you want to add the secret, and then type the following command:

   $ oc apply -f tls_secret.yaml

4. After you deploy the TLS secret to OpenShift, run the Data Virtualization Operator to create a virtual database with the name that is specified in the secret.

   When the Operator creates the virtual database, it matches the name in the secret to the name specified for the service in the CR. The Operator then configures the service to use the secret to encrypt client communications with the service.

Procedure

1. Create a secrets file to contain the credentials for your data source, and save it locally as a YAML file. For example,

   Sample secrets.yml file

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

2. Deploy the secret object on OpenShift.

   1. Log in to OpenShift, and open the project that you want to use for your virtual database. For example,

      oc login --token=<token> --server=https://<server>oc project <projectName>

   2. Run the following command to deploy the secret file:

      oc create -f ./secret.yaml

3. Set an environment variable to retrieve its value from the secret.

   1. In the environment variable, use the format valueFrom:/secretKeyRef to specify that the variable retrieves it value from a key in the secret that you created in Step 1.

      For example, in the following excerpt, the SPRING_DATASOURCE_SAMPLEDB_PASSWORD retrieves its value from a reference to the database-password key of the postgresql secret:

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 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 page for your virtualization, click Import Data Source.
5. On the Import Data Source page, click the tile for an active data source, and then click Next.

6. From the list of tables that are available, 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.

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 Create a 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 Refresh.

8. Click Done to close the view.

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

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.

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.

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, and from the overflow menu, 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 Draft to Published.
     • 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.

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.

Procedure

1. Open the directory that contains the settings.xml file that you downloaded from the Teiid OpenShift repository, and type the following command:

   $ mvn -s settings.xml exec:java -Dvdb=<path_to_vdb_xml_file>

   For example:

   $ mvn -s settings.xml exec:java -Dvdb=rdbms-example/src/main/resources/vdb.xml

   The migration tool checks the specified .xml file, and reports any validation errors. If there are no validation errors, the migration tool displays a .ddl version of the virtual database on the screen.