Chapter 4. Setting up the environment

Before we can install the Data Virtualization Operator and create a virtual database, we must set up our environment. In this section we’ll complete the following tasks:

  • Download or clone resources from the Teiid OpenShift examples GitHub repository. This tutorial requires local copies of some resources that are available in the examples repository.
  • Create the postgreSQL database that will serve as one of the data sources for our virtual database.

4.1. Cloning the tutorial resources

To complete the steps in this tutorial we’ll need local copies of some resources that are available in the Teiid OpenShift examples GitHub repository. Teiid is the open source community project that serves as the basis for data virtualization.

We could complete the tutorial by creating all of the necessary files from scratch. But we’ll simplify our lives by downloading or cloning existing resources from the repository.

So let’s grab the files that we need.

Procedure

  1. From a terminal window, change to the directory where you want to save the repository content.
  2. Type the following command to clone the repository:

    $ git clone https://github.com/teiid/teiid-openshift-examples.git

The next step is to create the postgreSQL database that will function as the source database for our virtual database.

4.2. Creating the source database

After you install the Operator and it is running, you can create the postgreSQL database and populate it with sample data.

  1. From a terminal window, change to the 01-base-example folder of the Teiid repository that you cloned, for example /home/username/git/teiid-openshift-examples/examples/01-base-example
  2. Type the following command to create a sample postgreSQL database from the template in the 01-base-example folder.

    oc new-app -f postgresql-ephemeral-template.json \
      -p DATABASE_SERVICE_NAME=accounts \
      -p POSTGRESQL_USER=user \
      -p POSTGRESQL_PASSWORD=changeit \
      -p POSTGRESQL_DATABASE=accounts

    A database with the name accounts is created.

  3. Type the following command to retrieve the list of pods from OpenShift:

    oc get pods | grep accounts

    The command returns the status of the database deployment process and of the created accounts database, for example:

    accounts-1-deploy   0/1     Completed   0          10m
    accounts-1-fcd92    1/1     Running     0          10m

    You can also click Workloads > Pods from the OpenShift console to check the status of the accounts database pod.

  4. Using the name that OpenShift assigns to the accounts postgreSQL database, type the following command to copy contents of the local folder, including the schema file accounts-schema.sql, to the remote pod directory:

    oc rsync . __accounts-x-xxxxx__:/tmp

    For example,

    oc rsync . accounts-1-fcd92:/tmp
    Note

    The command might return the following error:

    rsync: failed to set permissions on "/tmp/.": Operation not permitted (1)
    rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1179) [sender=3.1.2]
    error: exit status 23

    You can ignore the error and continue with the next step.

  5. From a terminal window, establish a remote shell connection to the postgreSQL database and then use psql to populate the database with the tables of sample data. Type the following command:

    oc rsh accounts-x-xxxxx psql -U user -d accounts -f /tmp/accounts-schema.sql

    The command returns the following output:

    psql:/tmp/accounts-schema.sql:1: NOTICE:  table "customer" does not exist, skipping
    DROP TABLE
    psql:/tmp/accounts-schema.sql:2: NOTICE:  table "account" does not exist, skipping
    DROP TABLE
    psql:/tmp/accounts-schema.sql:3: NOTICE:  table "product" does not exist, skipping
    DROP TABLE
    psql:/tmp/accounts-schema.sql:4: NOTICE:  table "holdings" does not exist, skipping
    DROP TABLE
    CREATE TABLE
    CREATE TABLE
    CREATE TABLE
    CREATE TABLE
    INSERT 0 1
     .
     .
     .
    INSERT 0 1

Your accounts postgreSQL database is now populated with sample data and is ready for you to configure it as a data source.

The next step is to add the Data Virtualization Operator to our project.