搜索

此内容没有您所选择的语言版本。

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

download PDF

You can define the structure of a virtual database by adding DDL statements directly within a custom resource file. During deployment, the Operator runs a source-to-image (S2I) build on OpenShift based on the dependencies that it detects in the virtual database artifact. To prevent build failures, ensure that any dependencies that your virtual database requires, such as JDBC driver dependencies, can be found at build time.

Advantages of using DDL in the CR to create a virtual database
  • Simple and minimalistic.
  • Code and configuration for a virtualization are in a single file. No need to create a separate CR file.
  • Easy to manage.
Disadvantages of using DDL in the CR to create a virtual database
  • Embedding the DDL for the virtual database in the custom resource (CR) file results in a large file.
  • Because the DDL is embedded in the CR YAML file, you cannot version the DDL and other aspects of the configuration independently.
  • If you deploy to multiple environments, you must store properties in configuration maps or secrets to make them independent of the custom resource.

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.

After you create the YAML file, you can run the Data Virtualization Operator to deploy the virtual database to OpenShift. For more information, see Chapter 6, Running the data virtualization operator to deploy a virtual database.

3.1. Creating a CR to deploy a DDL artifact

If you create a virtual databases by embedding DDL directly in a CR, you already have the CR that the Data Virtualization Operator uses for deployment. For information about the CR for a DDL artifact, see Chapter 3, Creating a virtual database by embedding DDL statements in a custom resource (CR).

Run the Data Virtualization Operator with the CR to generate the virtual database and deploy it to OpenShift.

Red Hat logoGithubRedditYoutubeTwitter

学习

尝试、购买和销售

社区

关于红帽文档

通过我们的产品和服务,以及可以信赖的内容,帮助红帽用户创新并实现他们的目标。

让开源更具包容性

红帽致力于替换我们的代码、文档和 Web 属性中存在问题的语言。欲了解更多详情,请参阅红帽博客.

關於紅帽

我们提供强化的解决方案,使企业能够更轻松地跨平台和环境(从核心数据中心到网络边缘)工作。

© 2024 Red Hat, Inc.