Oracle Fusion Analytics - Refresh Materialized Views via Custom Function After Pipeline Completion

January 10, 2024 | 14 minute read
Lisa Garczynski
Consulting Solutions Architect, Oracle Analytics
Krishna Prasad Kotti
Senior Member of Technical Staff
Text Size 100%:

Introduction

Oracle Fusion Data Intelligence Platform (FDIP, formerly Fusion Analytics Warehouse ) is an Oracle Cloud Infrastructure (OCI) native service that leverages the power of Oracle Autonomous Data Warehouse (ADW) and Oracle Analytics Cloud (OAC). It provides a comprehensive analytics solution that includes a data pipeline, data warehouse, semantic model, and pre-built content such as dashboards and reports. 

As businesses expand and develop, the importance of automation in managing data and analytics applications also grows. Based on user input, Oracle has delivered in Preview the Fusion Analytics Event Producer service, giving you more control over tracking customized events and notifications. This service is an effective automated solution for creating consolidated events and notification workflows that can streamline your Fusion Analytics business operations.

To review the Event Notifications feature, see Get notified when your data refresh completes using event notifications and Features Available for Preview.

Example Description

The purpose of this example is to provide instructions for creating a custom OCI function that the Fusion Analytics pipeline complete event triggers as an action. The custom function calls an Oracle Rest Data Services (ORDS) URL, which executes a PL/SQL procedure in the database for Fusion Analytics to refresh all materialized views in the OAX_USER schema.

custom_function_flow

 

Prerequisites

Ensure that you've completed the following before undertaking this example:

  1. Download the fn project repository to your development environment (https://github.com/fnproject/fn.git).
  2. Prepare the environment for function development using Function QuickStart Guides available here:
    https://docs.oracle.com/en-us/iaas/Content/Functions/Tasks/functionsquickstartguidestop.htm

Create the Application

Change the subnet id for your subnet with a command such as the following one:

fn create app --annotation oracle.com/oci/subnetIds='["ocid1.subnet.oc1.iad..."]' faw-mview-demo

Obtain the List of Applications

Assuming you have successfully completed the prerequisites, enter the following command to see your application in the list of applications:

fn ls apps

Create the Refresh Mviews Procedure

As OAX_USER in your database for Fusion Analytics, create the refresh_mviews procedure. This procedure loops through the names of all materialized views in the OAX_USER schema and refreshes each of them one at a time. You can update this script to handle any ordering or to specify that certain materialized views are refreshed.

CREATE OR REPLACE PROCEDURE oax_user.refresh_mviews (

    result OUT VARCHAR2
) AS
BEGIN
    FOR i IN (
        SELECT
            mview_name
        FROM
            user_mviews
    ) LOOP
        dbms_mview.refresh(i.mview_name);
    END LOOP;

    result := 'Materialized Views Refreshed Successfully!';
EXCEPTION
    WHEN OTHERS THEN
        htp.print(sqlerrm);
        result := 'Materialized View Refresh Error, check logs!';
END refresh_mviews;

set serveroutput on

DECLARE
    result VARCHAR2(200);
BEGIN
    refresh_mviews(result);
    dbms_output.put_line(result);
END;
/

You should see a message that reads: "Materialized Views Refreshed Successfully!"

You can also check the last_refresh_end_time in the user_mviews view using a command such as the following:

select mview_name, to_char(last_refresh_end_time, 'dd-mon-yyyy hh24:mi:ss') from user_mviews;

Enable Oracle Rest Data Services (ORDS) for OAX_USER and the refresh_mviews Procedure

As an administrator in your database for Fusion Analytics, use the following code for these tasks:

  1. Enable OAX_USER for ORDS.
  2. Create a module, template, and handler for the refresh_mviews procedure to create the ORDS URL that the function will call via a https request.
  3. Define a response parameter to output the result of the procedure (that is, a success or failure message).
  4. Create a role and privilege for OAUTH2.
DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'OAX_USER',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'oax_user',
      p_auto_rest_auth      => FALSE);

  ORDS.DEFINE_MODULE(
      p_module_name    => 'mview',
      p_base_path      => '/mview/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'mview',
      p_pattern        => 'refresh/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'mview',
      p_pattern        => 'refresh/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         =>
'DECLARE
  P_RESULT VARCHAR2(200);
BEGIN
  REFRESH_MVIEWS(p_result);
:result := p_result;
END;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'mview',
      p_pattern            => 'refresh/',
      p_method             => 'POST',
      p_name               => 'result',
      p_bind_variable_name => 'result',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.CREATE_ROLE(p_role_name => 'oracle.dbtools.role.OAX_USER.REFRESH_MVIEWS');

  l_roles(1) := 'OAuth Client Developer';
  l_roles(2) := 'RESTful Services';
  l_roles(3) := 'oracle.dbtools.role.OAX_USER.REFRESH_MVIEWS';
  l_modules(1) := 'mview';

  ORDS.DEFINE_PRIVILEGE(
      p_privilege_name => 'oracle.dbtools.privilege.OAX_USER.MVIEW_REFRESH',
      p_roles          => l_roles,
      p_patterns       => l_patterns,
      p_modules        => l_modules,
      p_label          => 'mview_refresh',
      p_description    => 'Privilege for module mview_refresh',
      p_comments       => NULL);

  l_roles.DELETE;
  l_modules.DELETE;
  l_patterns.DELETE;

COMMIT;

END;
/

Create the OAUTH User and Grant Roles and Privileges

As an administrator in your database for Fusion Analytics, create the OAUTH client user and grant roles and privileges for ORDS using OAUTH with code such as the following.

BEGIN
    OAUTH.CREATE_CLIENT(
        P_NAME => 'Materialized View Refresh Client',
        P_GRANT_TYPE => 'client_credentials',
        P_OWNER => 'OAX_USER',
        P_DESCRIPTION => 'Client Used to Refresh Materialized Views after Fusion Analytics pipeline completes',
        P_ORIGINS_ALLOWED => '',
        P_REDIRECT_URI => '',
        P_SUPPORT_EMAIL => 'support@example.com',
        P_SUPPORT_URI => 'https://www.example.com',
        P_PRIVILEGE_NAMES => 'oracle.dbtools.privilege.OAX_USER.MVIEW_REFRESH'
    );

  ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(
      p_client_name     => 'Materialized View Refresh Client',
      p_role_name => 'OAuth2 Client Developer'); 

  ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(
      p_client_name     => 'Materialized View Refresh Client',
      p_role_name => 'RESTful Services'); 

  ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(
      p_client_name     => 'Materialized View Refresh Client',
      p_role_name => 'oracle.dbtools.role.OAX_USER.REFRESH_MVIEWS'); 

    COMMIT;
END;
/

As OAX_USER, find the client_id and client_secret for the user you just created with commands such as the following ones:

select id, name, client_id, client_secret
from user_ords_clients
where name = 'Materialized View Refresh Client';

Update Your Application with client_id and client_secret

Update your application with the client_id and client_secret from the code shown earlier in this article:

fn config app faw-mview-demo clientId abc..
fn config app faw-mview-demo clientSecret xyz..

Always encrypt any configuration variables that contain sensitive information. You can create a function to encrypt and decrypt the variables; see this article for an example.

Find the ordsBaseURL

Log into the Oracle Cloud Console, select Oracle Database, select Oracle Autonomous Database, and select the database for Fusion Analytics.

In the Autonomous Database Console, select Database actions, then REST.

database for Fusion Analytics

The rest-workshop URL looks similar to the following:

https://[random chars]-oax[numbers].adb.us-ashburn-1.oraclecloudapps.com/ords/admin/rest_workshop

To obtain ordsBaseUrl, copy everything before the word admin and change admin to oax_user:

https://[random chars]-oax[numbers].adb.us-ashburn-1.oraclecloudapps.com/ords/oax_user/

This is the base URL for the ORDS services in the oax_user schema.

Create the Custom Java Function

Use commands such as the following to create a "hello world" java function.

fn init --runtime java faw-mview-demo-fn

cd faw-mview-demo-fn/src/main/java/com/example/fn

mv HelloFunction.java RefreshMview.java

Update the Code

Replace the HelloFunction.java code in the RefreshMvew.java file with this code and modify the ordsBaseUrl with your URL from earlier in this article:

package com.example.fn;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.lang.String;
import java.io.IOException;
import java.net.HttpURLConnection;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.util.*;

public class RefreshMview {

    private final String ordsBaseUrl =  "https://xxxxx-oax123.adb.us-ashburn-1.oraclecloudapps.com/ords/oax_user";
    private final HttpClient httpClient = HttpClient.newHttpClient();

    public static class Mview {
       public String result;
    }

    public Mview handleRequest() {
        Mview mview = null;
        try {
            HttpRequest request = HttpRequest.newBuilder( new URI( this.ordsBaseUrl + "/mview/refresh/" ) )
                    .header("Authorization", "Bearer " + getAuthToken())
      .POST(HttpRequest.BodyPublishers.noBody())
                    .build();
     HttpResponse<String> response = this.httpClient.send(request, HttpResponse.BodyHandlers.ofString());
            String responseBodyOutput = response.body();

            if( response.statusCode() == HttpURLConnection.HTTP_NOT_FOUND ) {
                System.out.println("URL not found!");
            }
            else {
      mview = new ObjectMapper().readValue(responseBodyOutput, Mview.class);
            }
   }
        catch (URISyntaxException | IOException | InterruptedException e) {
            e.printStackTrace();
        }
return mview;
    }

    private String getAuthToken() {
        String authToken = "";
        try {
            Map<String, String> env = System.getenv();
            for (String envName : env.keySet()) {
                System.out.format("%s=%s%n", envName, env.get(envName));
            }
            String clientId = System.getenv().get("clientId");
            String clientSecret = System.getenv().get("clientSecret");
            String authString =  clientId + ":" + clientSecret;
            String authEncoded = "Basic " + Base64.getEncoder().encodeToString(authString.getBytes());
            HttpRequest request = HttpRequest.newBuilder(new URI(this.ordsBaseUrl + "/oauth/token"))
                    .header("Authorization", authEncoded)
                    .header("Content-Type", "application/x-www-form-urlencoded")
                    .POST(HttpRequest.BodyPublishers.ofString("grant_type=client_credentials"))
                    .build();
            HttpResponse<String> response = this.httpClient.send(request, HttpResponse.BodyHandlers.ofString());
            String responseBody = response.body();
            ObjectMapper mapper = new ObjectMapper();
            TypeReference<HashMap<String, String>> typeRef = new TypeReference<HashMap<String, String>>() {};
            HashMap<String, String> result = mapper.readValue(responseBody, typeRef);
            authToken = result.get("access_token");
        }
        catch (URISyntaxException | IOException | InterruptedException e) {
            e.printStackTrace();
        }
        return authToken;
    }
}

Update the Test Java Function

Use code such as the following to update the test java function:

cd faw-mview-demo-fn/src/test/java/com/example/fn
mv HelloFunction.java RefreshMviewTest.java

In RefreshMviewTest.java, replace the HelloFunctionTest.java code with the following:

package com.example.fn;

import com.fnproject.fn.testing.FnResult;
import com.fnproject.fn.testing.FnTestingRule;
import org.junit.Rule;
import org.junit.Test;

import static org.junit.Assert.assertEquals;

public class RefreshMviewTest {

    @Rule
    public final FnTestingRule testing = FnTestingRule.createDefault();

    @Test
    public void shouldReturnUser() {
        testing.givenEvent().withBody("result").enqueue();
        testing.thenRun(RefreshMview.class, "handleRequest");

        FnResult fnresult = testing.getOnlyResult();

    assertEquals("{\"result\":\"Materialized Views Refreshed Successfully!\"}",fnresult.getBodyAsString());
       }
}

Update the Dockerfile

Use code such as the following to update the Dockerfile by first copying it:

cd fn
cp Dockerfile Dockerfile.orig

Update it with the following code:

FROM fnproject/fn-java-fdk-build:jre17-1.0.178 as build-stage
WORKDIR /function
ENV MAVEN_OPTS -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttps.proxyHost= -Dhttps.proxyPort= -Dhttp.nonProxyHosts= -Dmaven.repo.local=/usr/share/maven/ref/repository

ADD pom.xml /function/pom.xml
RUN ["mvn", "package", "dependency:copy-dependencies", "-DincludeScope=runtime", "-DskipTests=true", "-Dmdep.prependGroupId=true", "-DoutputDirectory=target", "--fail-never"]

ADD src /function/src

RUN ["mvn", "package", "-DskipTests=true"]
FROM fnproject/fn-java-fdk:jre17-1.0.178
WORKDIR /function
COPY --from=build-stage /function/target/*.jar /function/app/

CMD ["com.example.fn.RefreshMview::handleRequest"]

Add a Dependency Section to the pom.xml File

Use code such as the following to add the section:

       <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.9</version>
            <scope>compile</scope>
        </dependency>

Update the func.yaml File

Use code such as the following to update the file:

schema_version: 20180708
name: faw-mview-demo-fn
version: 0.0.39
runtime: java
build_image: fnproject/fn-java-fdk-build:jdk17-1.0.178
run_image: fnproject/fn-java-fdk:jre17-1.0.178
cmd: com.example.fn.RefreshMview::handleRequest

Test the Building of the Function

Use code such as the following to test the building of the function:

fn build -v faw-mview-demo-fn

Deploy the Function

Use code such as the following to deploy the function after building it:

fn invoke faw-mview-demo faw-mview-demo-fn

You see a message such as the following:

{"result":"Materialized Views Refreshed Successfully!"}

In the UI, Subscribe to the Topic with Action for your Function Name

When the function works properly from the command line, subscribe to the topic with action and your function name:

  1. Log in to the Fusion Analytics Console, select Enable Features, and enable Event Notification.
  2. Log in to the Oracle Cloud Infrastructure Console, select Developer Services, Notifications and create a topic for “Fusion Analytics Data Load Complete."
  3. Subscribe to the topic with your email address.
  4. Create an Event Service Matching Rule for the Oracle Analytics,  DataRefresh - Complete event.  Choose Attribute, resourceName: select your Fusion Analytics instance name.
  5. Create an Action, Action Type: Function, select your Function Application Name and Function Name.

For a detailed instructions see this blog.

Troubleshooting Tips

Verify the refresh time in the database, in SQL Developer as OAX_USER:

select sys_context('USERENV', 'SERVICE_NAME'),mview_name, to_char(last_refresh_end_time, 'dd-mon-yyyy hh24:mi:ss') from user_mviews;

Verify you can manually refresh them by executing the procedure and rechecking the refresh date and time:

set serveroutput on

DECLARE
    result VARCHAR2(200);
BEGIN
    refresh_mviews(result);
    dbms_output.put_line(result);
END;
/

select sys_context('USERENV', 'SERVICE_NAME'),mview_name, to_char(last_refresh_end_time, 'dd-mon-yyyy hh24:mi:ss') from user_mviews;

Test obtaining a token for your clientId and secret from ORDS:

Change the clientId and clientSecret to the value of your client_id and client_secret that was specified earlier in this article. Change the URL to your ORDS baseURL. Keep the oax_user/auth/token portion of the URL shown here..

curl -v \
--user "clientId:clientSecret" \
--data "grant_type=client_credentials" \
https://[random-chars]-oax[numbers].adb.us-ashburn-1.oraclecloudapps.com/ords/oax_user/oauth/token

This provides a bearer token that you can use to call the ORDS URL directly. 

{"access_token":"xeU123","token_type":"bearer","expires_in":3600}

Test the ORDS URL to refresh your materialized views using bearer token:

    curl -X 'POST' \
    'https://[random-chars]-oax[numbers].adb.us-ashburn-1.oraclecloudapps.com/ords/oax_user/mview/refresh/' \
    -H 'Authorization: Bearer xeU123' \
    -d '' | jq

You see a message such as the following:

{"result":"Materialized Views Refreshed Successfully!"}

Enable logging for the application

In the Oracle Cloud Console, navigate to Developer Services, Applications, Functions, Logs, Enable Logs.

Invoke the function with DEBUG option:

DEBUG=1;fn invoke faw-mview-demo faw-mview-demo-fn

Review the log to resolve errors.

Call to Action

Oracle solutions are designed to help you succeed. Now that you know more about them, visit the Oracle Analytics Community site to share your feedback, and let us know if you have questions or new ideas.

Lisa Garczynski

Consulting Solutions Architect, Oracle Analytics

Krishna Prasad Kotti

Senior Member of Technical Staff


Previous Post

Oracle Analytics Cloud January 2024 Update

Barry Mostert | 9 min read

Next Post


Enabling OCI Notifications for Data Flows in Oracle Analytics Cloud

Gabrielle Prichard | 6 min read