Live Feed of Data from AWS S3 to OCI Autonomous Database

Photo by Campaign Creators on Unsplash

There is increasingly growing demand of integrating various data sources from other cloud services to OCI Autonomous Database (ADB). Pertinently, in Oracle Cloud, Autonomous Database is one-stop data store supporting diverse data types and automating the continuous optimization of database. It also provides a smart built in self service application called Data Studio which empowers the users to perform data ingestion to insights from one single interface.

Today we are going to discuss a common scenario our customers often ask about.

Do we have any simple way to load the data from AWS S3 to Autonomous database? Sometimes a customer asks how this can be achieved without writing any code.

If you would like to know the answer, please continue reading!

Solution:

This can be done by live feeding of data from AWS S3 to OCI Autonomous database using AWS SNS and ADB Data Studio.

Now we will test this scenario with a sample data!

Prerequisite:

1. An AWS cloud account with necessary access to create S3 bucket and SNS topic with subscription.
2. An OCI cloud account with necessary access to create Autonomous Database.

Step-by-Step guide:

Step 1: Create Amazon S3 bucket

Step 2: Create Access Keys for user in AWS IAM console and keep a copy of Access key ID and Secret access key

Step 3: Create a database user in OCI Autonomous database actions page

Step 4: Add an amazon S3 cloud store from OCI database actions page (Login using the user created in previous step)

Step 5: Create and configure a live table feed to use notifications, and copy the notification URL

Take a note of notification URL.

Step 6: Create a notifications service subscription topic in Amazon Simple Notification Service (SNS) console

Login using IAM user and Create Topic

{
  "Version": "2012-10-17",
  "Id": "example-ID",
  "Statement": [
    {
      "Sid": "Example SNS topic policy",
      "Effect": "Allow",
      "Principal": {
        "Service": "s3.amazonaws.com"
      },
      "Action": "SNS:Publish",
      "Resource": "arn:aws:sns:ap-south-1:**********:notify-topic",
      "Condition": {
        "StringEquals": {
          "aws:SourceAccount": "***********"
        },
        "ArnLike": {
          "aws:SourceArn": "arn:aws:s3:*:*:<bucket_name>"
        }
      }
    }
  ]
}

Create topic and copy the topic ARN

Step 7: Enable and configure event notifications using the Amazon S3 console

S3 Management console as IAM user

Step 8: Create a notifications service subscription from AWS SNS console

Paste the notification link collected from live table feed (step 5).

Step 9: Test the setup

Upload a csv file in s3 bucket and check if it is loaded in ADB.

Similar integrations are possible with Azure Blob storage as well.

References:

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/adp-data-studio-overview-page.html

https://www.oracle.com/in/cloud/free/

Hope this blog would be helpful for OCI users!

Happy Reading!