Accessing Object Storage Via Oracle Analytics Cloud

With the advent of Data Lakehouse concept, the need is more than ever in trying to analyze & visualize data that is in raw format present in a Data Lake. Oracle Analytics Cloud is a single and complete platform that empowers your entire organization to ask any question of any data—across any environment, on any device. It fits into your ecosystem, enabling analysis in the cloud while also providing easy access to any data source.

However, currently there is no direct connectivity between Oracle Analytics Cloud & the Object Storage. I am sure this will be addressed in the future releases but the only way, as of now, to overcome this limitation is to have external tables created in ADW that points to the objects in the Object Storage bucket and then connect the OAC to ADW and have the visualizations created on these external tables.

In this blog, I am going to explain the steps to implement this. The architecture diagram would look like below:

As prerequisites, you need to have an ADW provisioned, an object storage bucket created, have a file containing the data uploaded (I am using the file Employee_Info.parquet) into that bucket and an OAC instance provisioned.

Note: Copy and save the object’s URL as highlighted in the third screenshot below.

Now, let us start the steps to get the connection created.

Step 1:

Firstly, you need to create an Auth Token.

Go to OCI Console à Profile (Top right corner)

Scroll down and click on Auth Tokens and then on Generate Token and give a description

Note: Save the resulting Auth Token to a notepad as this needs to be used at the next stage.

STEP 2:

Go to OCI Console à Oracle Database à Autonomous Datawarehouse à Database Actions à A new tab will open up

STEP 3: (On the newly opened tab)

Go to Data Tools à Data Load à Cloud Storage à Next

Step 4:

The newly opened page will ask you to set up a cloud storage. Once you click on the '+' icon, fill up the details as given below:

Click on NEXT.

Note: The Oracle Cloud Infrastructure User Name is your profile name. Auth Token is the token you had saved in the Step 1 and Bucket URL is the one you had saved in the Step 2.

Step 5:

Now, go back to Database Actions home page and select as below:

Select the connection name that you have used while configuring and you will be able to see the file name on the left hand side. Drag and drop the same to the pane on the right hand.

Click on the big friendly green play button and once the run finishes, this will set up an external table with the name EMPLOYEE_INFO in my case. Click Done and come back to the Database Actions page.

Now, establish a connection from SQL developer to the ADW and let us try to access this table created in the above step.

Voila! We can now access the data present in the parquet file on object storage via ADW just like we do any table in present in a Database. How cool is that!!!

However, our job isn’t done yet, is it?

Step 6:

Let us head to our OAC instance as below:

OCI Console à Analytics & AI à Analytics Cloud

Click on the Analytics instance and then on Analytics Home Page

Create a connection to your ADW instance, if not already done

Step 7:

Now, we need to create a dataset using the connection we created in the above step & find the dataset you had created earlier, EMPLOYEE_INFO in my case.

Step 8:

Drag and drop the dataset to the pane on the right side and save the same.

Now you can easily use this in your Workbooks to create visualizations or in the Data Flows as below: