Cloud Pak for Data Group

 View Only

How to access virtualized data using REST APIs

By JUN LIU posted 28 days ago

  
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

  1. 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
#Highlights
#Highlights-home
0 comments
311 views

Permalink