Chapter 6. Building the virtual database

The CREATE DATABASE and USE DATABASE statements specify the name of the virtual database and indicate that subsequent SQL operations in the DDL apply to this database.

The DDL in our CR uses the following statements to indicate that we’ll create and use a database called Portfolio for our virtual database.

CREATE DATABASE Portfolio OPTIONS (ANNOTATION 'The Portfolio VDB');
USE DATABASE Portfolio;

In this case, the CREATE DATABASE statement assigns a database name (Portfolio) that matches the name portfolio that we assigned to the metadata:name property in the CR:

  apiVersion: teiid.io/v1alpha1
  kind: VirtualDatabase
  metadata:
    name: portfolio

While it is not required that the two names match, using the same name makes it easier to understand that the two entities are related.

The annotation in the CREATE DATABASE statement assigns a metadata description to the database. The metadata is purely descriptive and it could be used as a label in a user interface. It is not directly used during the virtual database build or deployment process.

A virtual database uses adapters, known as translators or data wrappers, to convert the data that it receives from different types of external data sources into a standard format. Data virtualization uses a wide range of data wrappers. The DDL for our virtual database uses the following SQL statements to define data wrappers for the postgreSQL and REST data sources that we’re using in this tutorial:

CREATE FOREIGN DATA WRAPPER rest;
CREATE FOREIGN DATA WRAPPER postgresql;

Our virtual database connects to external two data sources to retrieve data. In the preceding sections, we defined the translators and the connection properties for these data sources. Now we’ll add the DDL to define two SERVER elements to represent the connections to the data sources. The names that we assign to the SERVER objects tie them to the datasource properties that we specified earlier.

The SQL in this section also defines a FOREIGN DATA WRAPPER (FDW) for each connection. We add these foreign data wrappers to enable the virtual database to manage the data that is stored on these external servers. By defining these FDWs for the virtual database we enable it to query the postgreSQL and REST data sources as if they were local tables.

CREATE SERVER "accountdb" FOREIGN DATA WRAPPER postgresql;
CREATE SERVER "quotesvc" FOREIGN DATA WRAPPER rest;

A virtual database is a collection of multiple schemas. These schemas can be physical, representing a foreign data source such as our postgreSQL database, or they can be virtual as with the schema that provides in the data virtualization layer.

CREATE SCHEMA marketdata SERVER "quotesvc";
CREATE SCHEMA accounts SERVER "accountdb";

CREATE VIRTUAL SCHEMA Portfolio;

Physical schema such as the marketdata and accounts schema that we define here, represent data elements in the external data sources. The physical schema definitions in the preceding DDL excerpt refer to the SERVER objects that we defined for those external data sources in Section 6.1.2.3, “External data source definitions”. The final statement in the preceding excerpt establishes a virtual schema for our Portfolio virtual database.

In Section 6.1.2.3, “External data source definitions” we defined the foreign schema and associated them with their corresponding SERVER objects. However, we have not provided the data virtualization service with the information that it needs to work with the schema in the foreign data sources. To provide that information, we add the following statements to our DDL:

SET SCHEMA marketdata;
IMPORT FROM SERVER "quotesvc" INTO marketdata;

SET SCHEMA accounts;
IMPORT FROM SERVER "accountdb" INTO accounts OPTIONS (
        "importer.useFullSchemaName" 'false',
        "importer.tableTypes" 'TABLE,VIEW');

In the preceding DDL excerpt we import metadata from our data sources into the schema that we created in Section 6.1.2.4, “Schema creation”.

The final section of the DDL defines the data abstraction layer of the virtual database. Here we define a schema that uses the elements of the metadata that we defined in the preceding PHYSICAL schema. This virtual schema is where the heavy lifting occurs. It defines the logic for combining information from our source tables.

You can define any number of virtual schema layers, but for simplicity, in this example we create a single layer.

In this final portion of the DDL, we create the following virtual views:

SET SCHEMA Portfolio;

      CREATE VIEW StockPrice (
          symbol string PRIMARY KEY,
          price double,
          CONSTRAINT ACS ACCESSPATTERN (symbol)
      ) AS
          SELECT p.symbol, y.price
          FROM accounts.PRODUCT as p, TABLE(call invokeHttp(action=>'GET', endpoint=>QUERYSTRING('quote', p.symbol as "symbol", `STOCK_QUOTE_API_KEY` as "token"), headers=>jsonObject('application/json' as "Content-Type"))) as x,
          JSONTABLE(JSONPARSE(x.result,true), '$' COLUMNS price double path '@.c') as y

      CREATE VIEW AccountValues (
          LastName string PRIMARY KEY,
          FirstName string,
          StockValue double
      ) AS
          SELECT c.lastname as LastName, c.firstname as FirstName, sum((h.shares_count*sp.price)) as StockValue
          FROM Customer c JOIN Account a on c.SSN=a.SSN
          JOIN Holdings h on a.account_id = h.account_id
          JOIN product p on h.product_id=p.id
          JOIN StockPrice sp on sp.symbol = p.symbol
          WHERE a.type='Active'
          GROUP BY c.lastname, c.firstname;

      CREATE VIEW CustomerHoldings (
          LastName string PRIMARY KEY,
          FirstName string,
          symbol string,
          ShareCount integer,
          StockValue double,
          CONSTRAINT ACS ACCESSPATTERN (LastName)
      ) AS
          SELECT c.lastname as LastName, c.firstname as FirstName, p.symbol as symbol, h.shares_count as ShareCount, (h.shares_count*sp.price) as StockValue
          FROM Customer c JOIN Account a on c.SSN=a.SSN
          JOIN Holdings h on a.account_id = h.account_id
          JOIN product p on h.product_id=p.id
          JOIN StockPrice sp on sp.symbol = p.symbol
          WHERE a.type='Active';

That completes our tour of the virtual database CR. See Section 6.2, “Completed virtual database custom resource file” to review the CR in its entirety.