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
  spec:
    replicas: 1 1
    env: 2
    - name: SPRING_DATASOURCE_SAMPLEDB_USERNAME
      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)
  build:
      source:
        dependencies: 3
        - org.postgresql:postgresql:42.1.4
        ddl: | 4
          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: 5
          central: https://repo.maven.apache.org/maven2

  1

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

  2

  Specifies the configuration properties for this virtualization, primarily the configuration for connecting to data sources. 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”.

  3

  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.

  4

  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.

  5

  Specifies the location of any private or non-public repositories that contain dependencies or other virtual databases. You can specify multiple repositories.