This article was written by Jun Liu (liuljun@cn.ibm.com) and Zeng Qiang Wang (wangzq@cn.ibm.com) based on customer PoC.
Introduction
Data Virtualization in IBM Cloud Pak for Data can connect multiple data sources across hybrid cloud and multiple clouds. You can join the data from various data sources into one virtualized view which makes data simple and much easier to get values out of them. After you create connections to your data sources, you can quickly view all of data in your organization. And then you can virtualize the data and query them for real-time analytics without data moving, replicating, ETLs, and additional storage requirements. The processing time is significantly accelerated using intelligent query pushdown technology in the self-organized computation mesh. You can retrieve the query results for the BI/AI applications much faster than methods that don't use virtualization.
To connect your applications to the Data Virtualization service by using JDBC or ODBC, you must install IBM® Data Server driver packages. In IBM Cloud Pak for Data 4.x, you can connect your applications to the Data Virtualization service by using REST APIs, which are more convenient than using JDBC or ODBC. You don't need install the driver packages anymore. The following sections introduce the steps to deploy and utilize the REST services.
Part 1. Deploy Db2 REST service
- After your provisioning of the Data Virtualization service instance, you can edit the db2ucluster custom resource. Find your respective db2ucluster resource by running the following command:
[root@api.ocp4915pw01.cp.fyre.ibm.com ~]# oc get db2ucluster
The command results will match your deployment_Name, in the example below db2u-dv:
NAME STATE MAINTENANCESTATE AGE
db2u-dv Ready None 44d
2.Run the following command to edit the add-ons file:
oc edit db2ucluster deployment_Name
Set the rest value to enabled: true. The edited file should look like the following example:
addOns:
rest:
enabled: true
3. Run the following command to view the REST service and deployment information:
oc get formations.db2u.databases.ibm.com db2u-dv -o go-template='{{range .status.components}}{{printf "%s,%s,%s\n" .kind .name .status.state}}{{end}}' | column -s, -t
The results should look similar to the following example:
Deployment c-db2u-dv-rest OK
Job c-db2u-dv-restore-morph OK
4.Run the following command to view the REST service pods as part of your deployment:
oc get pods |grep db2
Here is an example with the REST service pods:
c-db2u-dv-db2u-0 1/1 Running 1 (15h ago) 38h
c-db2u-dv-db2u-1 1/1 Running 0 38h
c-db2u-dv-dvapi-6698b4678-tr6wd 1/1 Running 9 (28d ago) 43d
c-db2u-dv-dvcaching-6bdc446d9d-9lb72 1/1 Running 1 40d
c-db2u-dv-dvutils-0 1/1 Running 5 (30d ago) 40d
c-db2u-dv-hurricane-dv-5dc9c4dc55-lq955 2/2 Running 0 2d5h
c-db2u-dv-rest-5bd66db46b-bfvxl 1/1 Running 0 3d9h
dv-addon-6c8c9cbc55-dtb7r 1/1 Running 5 (30d ago) 43d
dv-service-provider-6bfdb864cb-842jw 1/1 Running 2 (30d ago) 40d
5. Creating the route for the Db2 REST service
After you enable the Db2 REST service, you need to create a route to expose the REST server to clients that reside outside of the cluster.
Before you create the route, you need the name of the Db2 REST service. Run the following command to find the service name:
oc get svc |grep db2u-rest-svc
The result should look similar to the following example:
c-db2u-dv-db2u-rest-svc NodePort 172.30.141.221 50050:30489/TCP
In the example, the Db2 REST service name is c-db2u-dv-db2u-rest-svc
Run the following command to create the route:
oc create route passthrough rest --service rest_service_name
Using the above example, rest_service_name would be c-db2u-dv-db2u-rest-svc
To view the route that you created, run the following command:
oc get routes
The result should look similar to the following example:
NAME HOST/PORT PATH SERVICES PORT TERMINATION WILDCARD
cpd cpd-cdl.apps.ocp4915pw01.cp.fyre.ibm.com ibm-nginx-svc ibm-nginx-https-port passthrough/Redirect None
rest rest-cdl.apps.ocp4915pw01.cp.fyre.ibm.com c-db2u-dv-db2u-rest-svc rest-server passthrough None
Part 2. Create rest service and manager permission using Watson Studio Notebook
1. Authenticate Db2 REST service:
# Import the class libraries
import requests import ssl
import json from pprint
import pprint from requests
import Response import pandas as pd
import time
from requests.packages.urllib3.exceptions import InsecureRequestWarning
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
from IPython.display import IFrame
from IPython.display import display, HTML
from pandas import json_normalize
%matplotlib inline
import matplotlib
import matplotlib.pyplot as plt
token = ""
restHostname = "rest-cdl.apps.ocp4915pw01.cp.fyre.ibm.com"
restPort = "443"
#These values must be specified based on your database instance
username = "admin"
password = "password"
#These values can be left unchanged to authenticate in a Cloud Pak for Data instance.
#If authenticating with a different database instance these values must be specified
dbHostname = ""
dbName = ""
dbPort = -1
def authenticate():
global token
url = "https://%s:%s/v1/auth"% (restHostname, restPort)
json = {
"dbParms": { "dbHost": dbHostname,
"dbName": dbName,
"dbPort": dbPort,
"isSSLConnection": True,
"username": username,
"password": password,
},
"expiryTime": "24h"
}
response = requests.post(url, verify = False, json = json, proxies = None)
if response.status_code == 200:
token = response.json()["token"]
print("Authenticated user with token: successful")
else:
print("Authentication failed")
print(response.status_code, response.reason)
2. Setup metadata for rest service:
def setup_metadata():
#If the schema is not specified DB2REST will be used.
schema = ""
url = "https://%s:%s/v1/metadata/setup"% (restHostname,restPort)
header = {
"content-type": "application/json",
"authorization": token
}
body = {
"schema": schema
}
response = requests.post(url, verify = False, headers = header, json = body)
if response.status_code != 201:
print("Setup metadata failed")
print(response.status_code, response.reason)
print(response.json())
else:
print("Created setup metadata")
3. Create a Rest Service to datasets which you want to access:
def create_list_cust_address_service():
url = "https://%s:%s/v1/services" % (restHostname)
headers = {
"content-type": "application/json",
"authorization": token
}
json = {
"serviceName": "list_customer_address",
"serviceDescription": "Lists customer address",
"sqlStatement": "SELECT * FROM admin.customer_address limit 10",
"version": "2.0",
"isQuery": True,
}
response = requests.post(url, verify = False, headers = headers, proxies = None, json = json)
if response.status_code == 201:
print("Success.")
else:
pprint(response.json())
4. Grant the permission to the REST Service:
def grant_list_cust_address_service():
url = "https://%s:%s/v1/services/grant/list_customer_address/2.0" % (restHostname)
headers = {
"content-type": "application/json",
"authorization": token
}
json = {
"users": {
"names": ["dvusr"]
}
}
response = requests.put(url, verify = False, headers = headers, proxies = None, json = json)
if response.status_code == 200:
print("Success.")
else:
pprint(response.json())
Invoke below 4 functions to pass the authentication, create service and grant permission operations:
authenticate()
setup_metadata()
create_list_cust_address_service()
grant_list_cust_address_service()
Part 3. Use rest service in the notebook
1. Call Rest Service and fetch data by rest service
url = "https://%s:443/v1/services/list_customer_address/2.0"
header = {
"content-type": "application/json",
"authorization": token
}
body = {
"sync": True
}
print("Fetching data. Please wait...")
r = requests.post(url, verify = False, headers = header, json = body)
if (r.status_code == 200):
json = r.json()
df = pd.DataFrame(json_normalize(json['resultSet']))
display(df)
else:
print (r.status_code)
print (r.content)
You can set up your Db2 REST service so that application programmers can create REST endpoints that can be used to interact with virtualized data. Each endpoint is associated with a single SQL statement. Authenticated users of web, mobile, or cloud applications can use these REST endpoints from any REST HTTP client without having to install any JDBC/ODBC drivers. The Db2 REST server accepts an HTTP request, processes the request body, and returns results in JavaScript Object Notation (JSON).
Reference
https://www.ibm.com/docs/en/cloud-paks/cp-data/4.0?topic=applications-db2-rest-service#CloudPakforDataGroup#Highlights#Highlights-home