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.
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.
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.
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.
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.
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.
Source: oracle.com
Posts
0 comments:
Post a Comment