Chapter 3. Creating a virtual database by embedding DDL statements in a custom resource (CR)

Prerequisites

• You have Developer or Administrator access to an OpenShift cluster in which the data virtualization operator is installed.
• You have a compatible data source and the OpenShift cluster can access it.
• The data virtualization operator has access to any Maven repositories that contain build dependencies for the virtual database.
• You have information about the connection settings for your data sources, including login credentials.
• You have a DDL file for the virtual database that you want to create, or you know how to write the SQL code to design the database.

Procedure

• Create a CR text file in YAML format and save it with a .yaml or .yml extension, for example dv-customer.yaml

  The following example shows the elements to include in a CR for a virtual database that uses a postgreSQL data source:

  Example: dv-customer.yaml

  apiVersion: teiid.io/v1alpha1
  kind: VirtualDatabase
  metadata:
    name: dv-customer 1
  spec:
    replicas: 1   2
    datasources:  3
      - name: sampledb
        type: postgresql
        properties:
          - name: username
            value: USER
          - name: password
            value: MYPASSWORD
          - name: jdbc-url
            value: jdbc:postgresql://accounts/accounts
  build:
      source:
        dependencies:  4
        - org.postgresql:postgresql:42.1.4
        ddl: |  5
          CREATE DATABASE customer OPTIONS (ANNOTATION 'Customer VDB');
          USE DATABASE customer;
  
          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;
        mavenRepositories: 6
          central: https://repo.maven.apache.org/maven2

  1

  The name of the virtual database.

  2

  Specifies the number of instances to deploy. The default setting is 1.

  3

  Specifies the data source properties for the virtual database. The properties in the example apply to a connection to a PostgreSQL database. For information about supported data sources and their properties, see Section 2.1, “Compatible data sources”.

  4

  Specifies a list of Maven dependency JAR files in GAV format (groupId:artifactid:version). These files define the JDBC driver files and any custom dependencies for the data source. Typically, the Operator build automatically adds libraries that are available in public Maven repositories.

  5

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

  6

  Specifies the location of any private or non-public repositories that contain dependencies or other virtual databases. You can specify multiple repositories. If dependencies are in a repository other than the public Maven Central repository, specify the repository location. For more information about using private Maven repositories, see Section 4.3, “Private Maven repositories”.