Chapter 8. Connecting clients to the virtual database

After you deploy the virtual database, you can make it available to clients. You can provide access through the following interfaces:

  • JDBC. You can separately enable access from internal and external JDBC clients.
  • OData/REST API.
Note

ODBC access is also available, but a description of how to enable ODBC access is beyond the scope of the present tutorial.

JDBC access

By default, after you deploy a virtual database, internal services, that is, services in the same OpenShift cluster, can access the database via JDBC. No further configuration needed. Remote clients are a different story, but we’ll get to them in a minute.

JDBC clients within the cluster
Clients in the same OpenShift cluster as the virtual database automatically have access to it over JDBC. We’ll install a simple command-line SQL client to the cluster to demonstrate how easy it is.
External JDBC clients
Clients outside of the OpenShift cluster don’t automatically have access to the virtual database server. To enable access, you must either set up an OpenShift load balancer service, or configure port forwarding. We’ll use the SQuirreL SQL client to connect to the service from outside the cluster.

OData access

After you create the virtual database service, an OData route is generated automatically. You can provide the OData route to HTTP/REST clients, which can then submit OData queries to the virtual database. We’ll submit some simple queries from a standard browser to demonstrate.

8.1. Connecting an internal JDBC client

To test internal JDBC access, you can use the SQLLine tool. The SQLLine tool is a command line utility that can serve as a simple SQL client for connecting to relational databases and running SQL commands. You can install the SQLLine tool on your OpenShift cluster and use it to access the virtual database.

Note

SQLLine is not part of Red Hat Integration. References in this tutorial to using SQLLine are provided as a convenience to customers who want to test connectivity to a virtual database from an internal JDBC client.

8.1.1. Installing SQLLine

To install SQLLine to your OpenShift cluster, open a terminal window and run the following command: 

oc run -it --restart=Never --attach --rm --image quay.io/teiid/sqlline:latest sqlline

The command installs the SQLLine client image to the OpenShift cluster, and opens a SQL command line: 

sqlline>

You are now ready to connect the SQLLine tool to the virtual database.

8.1.2. Connecting SQLLine to the Portfolio virtual database

After you install SQLLine, you can run standard SQL queries from the SQLLine command prompt. SQLLine also provides control commands for interacting with the database and managing the behavior of the tool. The following table lists some common SQLLine commands.

Table 8.1. SQLLine commands

Command nameDescriptionCommand format

dbinfo

Retrieves information about the database.

!dbinfo

tables

Lists all of the tables in the database.

!tables

help

Displays information about SQLLine commands.

!help

quit

Exits SQLLine.

!quit

For a complete list of SQLLine commands, see the SQLLine Manual.

Procedure

  1. From the sqlline prompt, type the following command to connect to the Portfolio database that you created earlier. 

    !connect jdbc:teiid:portfolio@mm://portfolio:31000;

  2. When prompted type a user name and password to log in: 

    Enter username for jdbc:teiid:portfolio@mm://portfolio:31000;: ANY_USER_NAME
Enter password for jdbc:teiid:portfolio@mm://portfolio:31000;: ANY_PASSWORD

    The credentials that you supply at this point are only dummy values. You can type any value.

    The command responds with the following output: 

    Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_READ_COMMITTED) will be used instead.
0: jdbc:teiid:portfolio@mm://portfolio:31000>

    You are now connected to the virtual database service. You can submit SQL queries at prompt.

    For information about some sample queries that you can try, see Sample queries.

  3. Type !quit to end the SQLLine session.

    The SQLLine pod is deleted.

    You must re-run the installation command to run SQLLine again.

8.2. Connecting to the virtual database from an external JDBC client

By default, JDBC clients have access to a virtual database only if they share the same OpenShift cluster.

To provide access to external clients, you must create an OpenShift LoadBalancer service. LoadBalancer services open an external port to allow access to clients outside the cluster.

8.2.1. Configuring an OpenShift load balancer service to enable external JDBC clients to access the virtual database

To configure a load balancer for the virtual database, you define an attribute in the custom resource. When you run the Data Virtualization Operator to build and deploy the virtual database, the Operator creates the load balancer service automatically.

When the Operator deploys the virtual database, it automatically exposes the JDBC route to the virtual database service. Although OpenShift typically requires you to create a route to the service that you want to expose, you do not have to create routes for virtual database services that you deploy with the Data Virtualization Operator.

Prerequisites

  • You have access to an OpenShift cluster that permits you to add a LoadBalancer Ingress Service.
  • You have a custom resource (CR) to which you can add the attribute to enable the load balancer service.
Note

Some environments prohibit configuring an external load balancer. If your OpenShift cluster does not permit you to add a load balancer, you can enable access for external clients by enabling port forwarding. For more information, see Section 8.2.2, “Enabling external JDBC client access through port forwarding”.

Procedure

  1. Add a load balancer service for the virtual database by setting the value of spec.expose in your virtual database CR to LoadBalancer.

    To provide flexibility in exposing other resources in the future, precede the value with a hyphen (-) to indicate that it is an element in an array, as in the following example: 

    apiVersion: teiid.io/v1alpha1
kind: VirtualDatabase
metadata:
  name: portfolio
spec:
  replicas: 1
  expose:
    - LoadBalancer
....

  2. After you deploy the virtual database, you can run the following command from a terminal window to identify the exposed host and port: 

    oc get svc portfolio-external

    The command returns network information for the service, including the cluster IP address, external host name, and port number and type. For example: 

    NAME               TYPE          CLUSTER-IP     EXTERNAL-IP                            PORT(S)         AGE
portfolio-external  LoadBalancer  172.30.22.226  ad42f5d8b303045-487804948.example.com  3306:30357/TCP  15m

Additional resources

8.2.2. Enabling external JDBC client access through port forwarding

In environments where you do not have permission to configure an external load balancer, you can use the workaround of enabling port forwarding. Port forwarding maps the OpenShift cluster address and port to a local port on your computer.

Note

Port forwarding enables you to test external JDBC clients from a single local computer in the absence of a load balancer. Port forwarding cannot provide stable long-term access in a production environment.

  1. From a terminal window, type the following command to obtain the name of the portfolio pod: 

    oc get pods

    The command returns the list of available pods. For example: 

    NAME                              READY   STATUS      RESTARTS    AGE
accounts-1-deploy                 0/1     Completed   0           21h
accounts-1-q5z6m                  1/1     Running     0           21h
portfolio-1-build                 0/1     Completed   0           21h
portfolio-6bbf99fb8d-hgh9d        1/1     Running     0           21h
teiid-operator-598874685b-c8m9q   1/1     Running     0           21h
virtualdatabase-builder-1-build   0/1     Completed   0           21h

  2. Using the name of the virtual database pod, type the following command to forward the cluster port to your local computer: 

     oc port-forward <dv-pod-name> 31000:31000

    For example: 

    oc port-forward portfolio-6bbf99fb8d-hgh9d 31000:31000

    The command returns the following response: 

    Forwarding from 127.0.0.1:31000 -> 31000
Forwarding from [::1]:31000 -> 31000

    You can now access the pod from applications running on your computer. Install and run the SQuirreL SQL client to test it out.

Additional resources

For more information about port forwarding on OpenShift, see https:docs.openshift.com/container-platform/4.4/nodes/containers/nodes-containers-port-forwarding.html[the OpenShift documentation].

8.2.3. Installing the SQuirreL JDBC client

To test JDBC access to the virtual database from an external client, we need to install a local JDBC client. In this tutorial we’ll use SQuirreL, a free open source Java SQL client, but you can use any JDBC client. SQuirreL provides tools to view the structure of the virtual database, browse its views, and run SQL commands.

Note

SQuirreL is not part of Red Hat Integration. References in this tutorial to using SQuirreL are provided as a convenience to customers who want to test connectivity to a virtual database from an external JDBC client.

Because SQuirreL is installed outside the cluster, it does not have access to the portfolio database by default.

Prerequisites

You have completed the following tasks:

  • Enabled access to the virtual database from an external JDBC client.

For testing purposes, this tutorial uses an unsecured connection on port 31000, the default unsecured port.

Important

For production deployments, always use a secure port that encrypts network traffic.

Procedure

  1. Download the SQuirreL installation JAR from the following page: http://squirrel-sql.sourceforge.net/#installation.

  2. To install SQuirreL, open a terminal window to the directory that contains the downloaded file, and type the following command: 

    java -jar squirrel-sql-<version>-install.jar
  3. Follow the prompts in the installation wizard to complete the installation process.

8.2.4. Configuring SQuirreL to connect to the Portfolio virtual database

After you install the SQuirreL client, download the Teiid JDBC driver and configure the client to access the virtual database.

Procedure

  1. If you enabled external client access by adding a load balancer to the OpenShift cluster, retrieve the connection information for the virtual database by running the following command, otherwise skip to Step 2. 

    oc get service portfolio

    The command returns the cluster IP address, and the available ports, for example: 

    NAME       TYPE       CLUSTER-IP    EXTERNAL-IP  PORT(S)                                                              AGE
portfolio  ClusterIP  172.30.25.27  <none>       8080/TCP,8778/TCP,9779/TCP,31000/TCP,35432/TCP,31443/TCP,35443/TCP   8m23s

  2. Download the Teiid Java driver from the following page: http://teiid.io/teiid_wildfly/downloads/

    For more information about using a JDBC driver to connect external Java programs to virtual databases on OpenShift, see https://github.com/teiid/teiid-openshift-examples/blob/master/jdbc.adoc.

  3. Open SquirreL, and add the Teiid driver.

    1. From the menu, click Drivers > New Driver.
    2. In the Add Driver dialog box, type Teiid in Name field.

    3. In the Example URL field, type: 

      jdbc:teiid:<vdb-name>@mms://<host>:<port>
      Note

      Port 31000 is a non-secure port. If you connect to the virtual database on port 31000, type mm instead of mms in the Example URL field.

    4. In the Website URL field, type: 

      `http://teiid.org`
    5. Click Extra Class Path and then click Add.
    6. Navigate to the directory where you downloaded the Teiid driver, click the driver JAR file, and then click Open.
    7. Click List Drivers. The Class Name field displays org.teiid.jdbc.TeiidDriver.
    8. Click OK.

  4. Add an Alias for the connection.

    1. Click the Aliases tab, and from the menu click Aliases > New Alias.
    2. In the Name field, type portfolio.
    3. In the Driver field, select Teiid

    4. In the URL field, type jdbc:teiid:portfolio.1@mm://IPADDRESS:PORT_NUMBER in place of the example URL.

      For example, if you used port forwarding to provide external access, type the following localhost URL: 

      jdbc:teiid:portfolio.1@mm://127.0.0.1:31000

    5. In the User Name field, type any value.

      Note

      The user name, and the password that you provide in the next step, are dummy values. Although you must supply a user name and password to create the alias, the values that you provide are not used in authenticating with the virtual database.

    6. In the Password field, type any value.
    7. To test your connection, click Test, and then click Connect.
    8. Click OK to confirm that the test was successful, and then click OK again to save the alias.

8.2.5. Querying the Portfolio virtual database from the SQuirreL SQL client

Procedure

  1. From the Aliases tab, right-click the alias that you created, and then click Connect to connect to the virtual database.
  2. After the connection is established, click the SQL tab.
  3. Type a SQL query and and press Ctrl+Enter to see the results.

For information about some sample queries that you can try, see Sample queries.

8.3. Sample queries

You can query the virtual database from internal or external JDBC clients. Here are a few sample queries that you can use to test how the clients interacts with the virtual database.

Example: Retrieve IBM stock price
SELECT * FROM STOCKPRICE WHERE symbol = 'IBM';
Retrieve the full account value for customer with last name 'Dragon'
SELECT * FROM AccountValues WHERE LastName = 'Dragon';

8.4. Access the virtual database through the OData API

You can use the data virtualization OData API to query the virtual database service. You append your OData query to the HTTP route to the service. You retrieve the HTTP route by querying the OpenShift server.

  1. Query the OpenShift server for the route that is created for the service. For example: 

     oc get route

    The server returns the HTTP route to the server. For example: 

    NAME        HOST/PORT PATH                                      SERVICES    PORT   TERMINATION   WILDCARD
portfolio   portfolio-dv-tutorial.apps.cluster-xyz.example.com  portfolio   8080   edge          None

  2. To test OData access from a browser, type the host string in the address bar, and append an OData query URL.

    Example: OData query requesting data in JSON format

    https://portfolio-dv-tutorial.apps.cluster-xyz.example.com/odata/portfolio/AccountValues('Dragon')?$format=json

    Replace the host value in the preceding URI with the HTTP route to your service. The server returns a result similar to the following: 

    {
   "@odata.context":"https://HOST/odata/portfolio/$metadata#AccountValues/$entity",
   "LastName":"Dragon",
   "FirstName":"Bonnie",
   "StockValue":30299.04
}

    Example: OData query requesting data in XML format

    https://portfolio-dv-tutorial.apps.cluster-xyz.example.com/odata/portfolio/StockPrice('IBM')?$format=xml

    The server returns a result similar to the following: 

    <a:entry xmlns:a="http://www.w3.org/2005/Atom" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns:d="http://docs.oasis-open.org/odata/ns/data" m:context="https://portfolio-dv-tutorial.apps.cluster-xyz.example.com:443/odata/portfolio/$metadata#StockPrice/$entity">
<a:id>
  https://portfolio-dv-tutorial.apps.cluster-xyz.example.com:443/odata/portfolio/StockPrice('IBM')
</a:id>
<a:title/>
<a:summary/>
<a:updated>2020-05-27T01:40:35Z</a:updated>
<a:author>
  <a:name/>
</a:author>
<a:link rel="edit" href="https://portfolio-dv-tutorial.apps.cluster-xyz.example.com:443/odata/portfolio/StockPrice('IBM')"/>
<a:category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="\#Portfolio.1.Portfolio.StockPrice"/>
 <a:content type="application/xml">
  <m:properties>
    <d:symbol>IBM</d:symbol>
    <d:price m:type="Double">121.76</d:price>
  </m:properties>
 </a:content>
</a:entry>