Chapter 5. Creating a virtual database as a fat JAR

You can use the Teiid Springboot starter to create a virtualization file as a Fat JAR. You can then publish the JAR to a Maven repository and use a YAML-based custom resource to deploy the virtual database to OpenShift. For more information about the Teiid Spring Boot starter, see https://github.com/teiid/teiid-spring-boot.

The Spring Boot Maven plugin creates a self-contained Uber JAR or fat JAR that includes all of the application code and dependencies in a single JAR file.

You define the virtual database in the resource files for the project (for example, the DDL file and application.properties), and specify the dependencies that are required to build the virtual database as a Spring Boot Java executable in the pom.xml file. When you run the build, Maven reads the pom.xml file and resolves its contents to incorporate external resources into the build.

When you build the project, it creates a virtual database as a Spring Boot Java executable. You can then test the resulting executable locally.

After local testing is complete, you can deploy the JAR file to a Maven repository. Then after your FAT JAR is available in the Maven repository, you can use a YAML based custom resource similar to deploy the virtual database to OpenShift.

Advantages of creating a virtual database as a fat JAR
  • Establishes a clean separation between the DDL code that represents the virtual database and the configuration.
  • Provides for local testing of the virtualization outside of OpenShift. Of course, caching, authentication, and other capabilities that depend on the OpenShift environment do not work locally.
  • Supports extensions such as user-defined functions (UDFs), custom translators, and so forth as part of the project and they will be incorporated into the runtime virtual database automatically.
  • Suitable for deployment into multiple environments.
  • Versioning is done at the level of the overall project.
Disadvantages of creating a virtual database as a fat JAR
  • Requires a working knowledge of Java, Maven, Teiid Spring Boot starters, Spring, and Teiid.

Prerequisites

  • You have a working knowledge of Java development, Maven, Teiid Spring Boot starters, Spring, and Teiid.
  • You have Maven 3.2 or later installed.
  • You have JDK 11 (Java Platform, Standard Edition 11 Development Kit) or later installed.
  • You have a compatible data source and the OpenShift cluster can access it.
  • You have a pom.xml file that specifies the dependencies that are required to build your virtual database.
  • If the driver for your data source is not available from the public Maven repository, you have downloaded the driver and deployed it to your local Maven repository.
  • The Data Virtualization operator has access to the Maven repositories that contain build dependencies for the virtual database.
  • You have a DDL file for the virtual database that you want to create, or you know how to write SQL code and create DDL files.

Procedure

  1. Create a Java Maven project with the following directory structure for your virtual database:
dv-customer-fatjar/
├── pom.xml
└── src
    └── main
        ├── java
        │   └── io
        │       └── integration
        │           ├── Application.java
        │           └── DataSources.java
        └── resources
            ├── application.properties
            └── vdb.ddl
  1. In the pom.xml, define the repository locations, drivers, and user credentials that are required to build your virtual database.
  2. In the application library of the virtual database project, create a Java application file, Application.java
  3. In the same directory, add a Datasources.java class file, and add a bean method for each data source that you want your virtual database to connect to. For an example of a Datasources.java file that is designed to work with a postgreSQL database, see Section 5.1, “Sample Datasources.java file”.
  4. In /src/main/resources, add an application.properties file and connection properties for your data sources to it. For more information, see Section 5.2, “Specifying application properties”.
  5. In /resources/vdb.ddl, add DDL statements to specify the structure of the virtual database, including any views. For example vdb.ddl

    The following example shows a sample DDL file for a virtual database that uses a postgreSQL data source:

    Example: vdb.ddl

    CREATE DATABASE customer OPTIONS (ANNOTATION 'Customer VDB');
    USE DATABASE customer;
    
    CREATE FOREIGN DATA WRAPPER postgresql;
    CREATE SERVER sampledb TYPE 'NONE' FOREIGN DATA WRAPPER postgresql;
    
    CREATE SCHEMA accounts SERVER sampledb;
    CREATE VIRTUAL SCHEMA portfolio;
    
    SET SCHEMA accounts;
    IMPORT FOREIGN SCHEMA public FROM SERVER sampledb INTO accounts OPTIONS("importer.useFullSchemaName" 'false');
    
    SET SCHEMA portfolio;
    
    CREATE VIEW CustomerZip(id bigint PRIMARY KEY, name string, ssn string, zip string) AS
       SELECT c.ID as id, c.NAME as name, c.SSN as ssn, a.ZIP as zip
       FROM accounts.CUSTOMER c LEFT OUTER JOIN accounts.ADDRESS a
       ON c.ID = a.CUSTOMER_ID;

    For information about how to use DDL to define a virtual database, see DDL metadata for schema objects in the Data virtualization reference guide. Instructions for how to define the complete DDL for a virtual database is beyond the scope of this document.

  6. Build the virtual database artifact. Open a terminal window and type the following command:

    mvn clean install

    The command generates a ${project.name}-$2020.Q1.vdb file in your target repository.

  7. Deploy the artifact to a remote repository by typing the following command:

    mvn clean install deploy

After the virtual database artifact is available in a Maven repository, you can use a YAML-based custom resource to deploy the virtual database to OpenShift.

5.1. Sample Datasources.java file

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 datasources properties in the CR file.

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();
    }

}
1
The prefix must match the prefix that you assign to properties that you define in the application.properties file.
2
The name sampledb in the prefix definition and in the method name must match the name in the SERVER and resource-name objects that are defined in the virtual database DDL file. The Spring Boot framework automatically associates the names of methods in the Datasources.java file with the names of data sources in the DDL file.

5.2. Specifying application properties

When you create a virtualization as a fat JAR, you must supply an application.properties file. You can define some 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.

You must prefix data source properties that you define in the application.properties file, with the configuration properties string that is 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 the application.properties file with that string, as in the following property definitions:

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

Prerequisites

  • You have a Datasources.java file in your Java class folder that specifies an application prefix.

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.

5.3. Creating a CR to deploy a fat JAR

After you develop a virtual database from the teiid-springboot starter, you deploy the resulting JAR to Maven repository. You then create a YAML custom resource file for deploying the virtual database to OpenShift.

The CR file for deploying a virtual database created as a fat JAR resembles the CR that you use to deploy a virtual database that is created as a Maven artifact, as described in Section 4.1, “Creating a custom resource (CR) to deploy a Maven artifact”. Only the Maven GAV coordinates differ. In this case, the CR provides the Maven coordinates of the JAR file.

Prerequisites

  • You created a virtualization as a fat JAR, according to the instructions in Chapter 5, Creating a virtual database as a fat JAR.
  • You deployed the virtualization to a Maven repository that the Data Virtualization Operator can access.
  • You have the login credentials to access the data source.
  • You are familiar with the creation of custom resource files in YAML format.

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
1
Sample environment variables for a postgreSQL data source.
2
The Maven coordinates of a fat JAR artifact that you deployed to a Maven repository in Chapter 5, Creating a virtual database as a fat JAR.

After you create the CR YAML file, you can run the Data Virtualization Operator to deploy the virtual database to OpenShift.