Note:

Stream Data to Oracle Autonomous Transaction Processing Database using OCI Functions

Introduction

In this tutorial, we will learn how to build and deploy a real-time data streaming pipeline using Oracle Cloud Infrastructure (OCI) services. The pipeline streams data from an OCI stream to an Oracle Autonomous Database by leveraging OCI Connector Hub and OCI Functions. The function is written in Java, employing the Resource Principal Authentication method for secure access to OCI services and a database wallet for establishing a connection with the Oracle Autonomous Database.

OCI Streaming: OCI has an array of tools for handling data in the cloud, with OCI Streaming being one such service tailored for high-throughput, real-time data streams. By leveraging OCI Streaming, developers can construct scalable and dependable data pipelines that efficiently ingest, process, and distribute data streams.

OCI Functions: OCI Functions is a fully managed, multi-tenant, highly scalable, on-demand, Functions-as-a-Service platform. It is built on enterprise-grade OCI and powered by the Fn Project open source engine. Use OCI Functions when you want to focus on writing code to meet business needs.

Resource Principal Authentication: You can use a resource principal to authenticate and access OCI resources. The resource principal consists of a temporary session token and secure credentials that enables the OCI Functions to authenticate itself to other OCI services, such as OCI Streaming.

OCI Connector Hub: OCI Connector Hub is a cloud message bus platform that offers a single pane of glass for describing, executing, and monitoring interactions when moving data between OCI services.

Note:

Objectives

Prerequisites

Task 1: Set up Dynamic Groups

Log in to the OCI Console, navigate to your domain, click Dynamic Groups and create a group with the following information.

Task 2: Create Policies

Go to the OCI Console, navigate to Policies and create the policies with the following information.

Task 3: Create an OCI Container Registry

  1. Go to the OCI Console, navigate to Developer Services, Containers & Artifacts, select Container registry and click Create repository to create a private repository for the function image.

    • Repository name: Enter lab/fn-java-event-to-atp.

      T3_1

  2. Check the repositories and take a note of the Namespace.

    T3_1

  3. Open the OCI Cloud Shell where you should have OCI CLI and Docker installed, and proceed with the log in on the registry. Check what is the correct URL for your region. In this tutorial, we are using Brazil East (Sao Paulo) where the registry URL is gru.ocir.io.

    Note: You will need the user auth token created in Prerequisites section.

    docker login gru.ocir.io
Username: <your container namespace>/youruser
Password: YOUR_AUTH_TOKEN_CREATED_EARLIER

    T3_3

Task 4: Create an OCI Object Storage Bucket to Store your Database Wallet

Go to the OCI Console, navigate to Storage, Object Storage & Archive Storage, Buckets and click Create Bucket to create a new bucket named Wallet to store your database wallet zip file.

T4_1

Task 5: Download the Database Wallet and Store it on OCI Object Storage Bucket

Note: This code example demonstrates how to securely connect to an Oracle Autonomous Database using wallet. To proceed, you will need to configure your wallet credentials and establish a password for use throughout this tutorial.

  1. Go to the OCI Console, navigate to Oracle Database, click Autonomous Database and select your ATP.

    T4_1

  2. Click Database Connection.

    T4_1

  3. Enter your wallet password and note down, you will need this password in your application.

    T4_1

  4. Go to your OCI Object Storage bucket created in Task 4 and click Upload to upload your downloaded wallet zip file.

    T4_1

Task 6: Securely Store your Database Credentials and Wallet Password within your Vault

Note: As a prerequisite, ensure you have already created a vault.

  1. Go to the OCI Console, navigate to Identity & Security, Vault and select your vault.

  2. Click Secrets, Create Secret and create secrets with the following information.

    Secret Name Value
    MYRDBMS_WALLET_PASSWORD Your wallet password in plain-text
    MYRDBMS_DB_PASSWORD Your database password in plain-text

    T5_1

    Note Ensure you select Plain-Text for Secret Type Template.

Repeat this process for the MYRDBMS_DB_PASSWORD as well and take note of both OCIDs that will be used for the function configuration.

T5_1

Task 7: Create the Java OCI Functions to Receive the Event Message and Insert to ATP Database

Note Make sure you select your private subnet, the same subnet of your stream pool.

  1. Go to the OCI Console, navigate to Developer Services, Functions, Applications and click Create application.

    T6_1

  2. Go to the OCI Cloud Shell where you have Docker, OCI CLI, Fn Project CLI installed and run the following command to initialize the function.

    Note: If you followed the tasks, your Docker log in command has already been executed by now, if not, proceed with the Docker log steps in Task 3.

  3. Check the current context, since you are using OCI Cloud Shell, it should be already set up in the current context.

    fn list context

    T6_2

  4. Run the following command to update your function settings to be able to pull images from your container registry created in Task 3.

    fn update context oracle.compartment-id PASTE_YOUR_COMPARTMENT_OCID
fn update context registry gru.ocir.io/PASTE_YOUR_REGISTRY_NAMESPACE/lab
fn list context

    T6_2

    Note: In this tutorial, we are using Brazil East(Sao Paulo) region, if you are using a different region, you need to change the API URL and REGISTRY locations.

  5. Run the following command to check if the application you created is listed on your OCI Cloud Shell.

    Note: Ensure your OCI Cloud Shell is using Network: Public

    fn list apps

    T6_2

  6. Get the Java function sample code from here: fn-java-event-to-atp.zip and upload it to your OCI Cloud Shell and then proceed to unzip the file.

  7. Click on the top right wheel menu and select Upload to upload the file.

    T6_2

    T6_2

  8. Run the following command to unzip the file.

    # check your file is there
ls -lrt
# unzip the file
unzip fn-java-event-to-atp.zip
# check again
ls -lrt

    T9_1

    Note: This concise Java project retrieves JSON data input from the OCI Connector Hub, parses it according to a predefined format, and seamlessly inserts the extracted information into your ATP database. To ensure proper data insertion, consult the following steps for guidance on the required message format and corresponding table within your ATP database.

  9. In your OCI Cloud Shell, run the following command to build the code and deploy the function.

    fn deploy --app MyApp
# After deploy complete, check the function is there:
fn list functions MyApp |grep fn-java-event-to-atp

    T9_1

    T9_1

  10. Go to the OCI Console, navigate to Developer Services, Functions, Applications, select your application (Myapp) and click your function.

    T9_1

    T9_1

    Note This tutorial’s Java sample code utilizes configuration variables to establish a database connection. Ensure you provide the appropriate values from your environment to guarantee successful execution.

  11. Create all the following configurations.

Secret Name/Key Value
WALLET_BUCKET Wallet
WALLET_OBJECT name of your zip file
BUCKET_NAMESPACE Your bucket namespace
DB_USER Your database username
DB_SERVICE_NAME the database service name you find in your database connection details
DB_PASSWORD_OCID_VAULT paste your secret OCID
DB_WALLET_PASSWORD_OCID_VAULT paste your secret OCID

Task 8: Create a New Table on ATP Database

Create a new table named MY_TABLE on the ATP database in order to receive data from the streaming.

  1. Select your Oracle Autonomous Database on which you downloaded the wallet, click Database actions drop-down menu and then click SQL.

    T9_1

  2. Run the following table creation DDL command.

    Note This lab uses the ADMIN user for database connections. Substitute your own username if it differs.

    CREATE TABLE "ADMIN"."MY_TABLE" 
( "CODE" VARCHAR2(50 BYTE), 
	"NAME" VARCHAR2(500 BYTE)
) ;

    T9_1

Task 9: Set up OCI Connector Hub to Invoke the Function when new Message Arrives on the Stream

Now that you have created the function and the new table to store the data, it is time to connect everything together! We will do this by setting up a pipeline that flows from OCI Streaming, through OCI Connector Hub, and finally to your OCI Functions.

  1. Go to the OCI Console, navigate to Analytics & AI, Messaging and click Connector Hub.

  2. Click Create connector and enter the following information.

    • Connector name: Enter StreamingToFN.
    • Source: Select Streaming.
    • Target: Select Functions.

    Note As a prerequisite, ensure you have already created your stream pool and stream named myfirststream.

    T9_1

  3. In Configure source, select your Stream pool and Stream.

    T9_1

  4. In Configure target, select MyApp as Function Application and fn-java-event-to-atp as Function.

    T9_1

Note You may need to create additional policies as suggested on Create connector page.

Task 10: Create a Message on OCI Streaming and Verify the Pipeline Working

Note The provided Java code sample is designed to process messages in a particular JSON structure. The code parses this JSON to extract the necessary data and then inserts it into the MY_TABLE table. Any deviation from the expected JSON format will result in parsing errors and prevent successful database insertion.

JSON Sample Message:

{"code": "001", "name":"Larry"}

Follow the steps:

  1. Go to the OCI Console, navigate to Analytics & AI, Messaging, Streaming, select your stream (myfirststream) and click Produce Test Message.

  2. In Data, enter the sample JSON message and click Produce.

    T9_1

    Note The stream pipeline may experience a cold start on its first execution, resulting in a noticeable delay. This is a consequence of the function initialization that occurs upon the connector hub’s first attempt to send a message.

Task 11: Validate the Data Arrived on the Database

  1. Select your Oracle Autonomous Database on which you downloaded the wallet, click Database actions drop-down menu and click SQL.

    T9_1

  2. Run the following query.

    select * from admin.my_table;

    T9_1

Troubleshooting and Tips

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.