Monday, September 23, 2019

Google Cloud Function call Oracle ADW Rest end-point

When running an Oracle Autonomous Database, for example an Oracle Autonomous Data Warehouse (ADW for short) it is very likely that multiple applications and solutions do want to have access to the data available in the ADW.  A common scenario is that, a department in the enterprise has been developing an application in isolation and at one point in time requires some additional data from the data warehouse. In this case the data warehouse is the Oracle Autonomous Data Warehouse.

Call Oracle ADW from Google Cloud Functions
When developing an application in the Google Cloud you can make use of Google Cloud Functions. As Google Cloud Functions support development in Python you can write a generic function to retrieve, for example, customer details based upon a customer ID. We have deployed the Oracle ADW restfull data service in a previous blogpost. In this blogpost we want to call it with a GET request from the Google Cloud.

One generic function
When building an application using Google Cloud Functions which at several points need to interact with data in the Oracle ADW you do not want to code these multiple times. A more logical way of doing things is building one function to interact with Oracle ADW to obtain the needed data.

Every time your application calls the Google Cloud Function, with the propper JSON payload which contains a valid customer ID the Google Cloud function will call the Oracle ORDS endpoint which we developed as part of Oracle ADW. The return message from Oracle ADW will be the return message from the Google Cloud Function.

By building this "interaction layer" developers will only have to build the interaction with the Oracle Cloud based Oracle ADW once and after that they can work within Google Cloud to complete their specific Google Cloud based application.

Deploy a Google Cloud Function for Oracle Database
Deploying a Google Cloud function for Oracle Database starts with the same steps as deploying any cloud function. In our case we build a Python based application. The below image showcases the initial creation of the function:

We indicate that we want to use Python 3.7 and that the function inside our code, which is the entrypoint for execution, is named getCustomer.

The code used is shown below. Do note; when developing a production solution you most likely want to add additional security and a lot more error handling than shown in this example. This is just a very (very very) not production ready example. Additionally, the full URL of the Oracle ADW has been substituted with XXXX

import urllib.request

def getCustomerResponse(requistedCustomerId):
    :param    :return:    """
    baseUrl = ""
    fullUrl = baseUrl + requistedCustomerId
    operUrl = urllib.request.urlopen(fullUrl)

        data =
        data("Error receiving data from ADW", operUrl.getcode())
    return data

def getCustomer(request):
    :param request:    :return:    """
    requestJson = request.get_json(silent=True)
    requestArgs = request.args

    if requestJson and 'customer_id' in requestJson:
        customerId = requestJson['customer_id']
    elif requestArgs and 'customer_id' in requestArgs:
        customerId = requestArgs['customer_id']
        customerId = 'ERROR'
    if customerId == "ERROR":
        responseData = "No customer_id provided"    else:

        responseData = getCustomerResponse(customerId)
    return responseData

Testing the function
Upon deployment you can test the google cloud function using the test functionality in the Google UI (or by calling it directly) from another location. If all is working you should receive a JSON style return message as shown in the below screenshot.

In the above screenshot the trigger event field contains our test JSON payload and the  function output contains a JSON response which originates for the Oracle ADW. 

When developing applications on multiple platforms, multiple clouds and multiple technologies and you require access to one central source of the truth you can use multiple technologies to connect to a centrally located Oracle Autonomous Data Warehouse. However, using a REST interface is in most cases a very simple and "fit for the job" kind of solution. 

When developing a solution like this it will require more strict error handling and it will require strict authentication and authorization however the base principle stands that hybrid multi-cloud applications can integrate with an Oracle Autonomous Data Warehouse in a very easy and cloud native manner. 

No comments: