Connecting Jupyter Notebook to Oracle Autonomous Database

Jupyter Notebook is an open source web application for Machine Learning and Data Exploration.

In this post I will show you how to connect a Jupyter Notebook to Oracle Autonomous Database and explore the data using Python.

The assumption is we already have a Jupyter notebook sandbox running on Oracle Cloud compute instance.

Prerequisites:

Connect to your OCI compute instance via SSH.

Start your Jupyter notebook environment and create a new notebook.

$jupyter notebook

or if running as root

#jupyter notebook --allow-root

Access your notebook using the browser > http://<your public IP>:8888

Install the required Python libraries.

# Install required libraries
!pip install sqlalchemy pandas numpy cx_oracle matplotlib

Import the libraries

import sqlalchemy as db
import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt

Create a new connection with Oracle Autonomous Database:

os.environ['TNS_ADMIN']='<path to your Oracle db wallet on OCI instance>'

engine = db.create_engine('oracle://<user>:<password>@<service type>')

ie.

os.environ['TNS_ADMIN'] = '/opt/oracle/instantclient_19_3/network/admin/'

engine = db.create_engine('oracle://ADMIN:Welcome!@smatATP_TP')

Import this data set to your Oracle Autonomous Database using the Jupyter notebook:

Since you are using OCI compute, you will have to upload the dataset on your compute instance. Then using the Jupyter notebook, load the data to your Database.

i.e

df = pd.read_csv('/home/opc/applications/Data/iris.csv')
 df.to_sql('iris', engine, index=False, if_exists='replace')

Retrieve data from the table you just created.

df = pd.read_sql("select * from iris", con=engine)

df.head(10)

 

 

total = pd.read_sql("select count(*) from iris", con=engine)
print(total)

 

Use Matplotlib to visualize the data.

df.groupby('species')
plt.scatter(df.sepal_length, df.sepal_width)

 

Happy Coding!

References:

Build a machine learning sandbox on Oracle Cloud

Connect to Oracle ATP through Jupyter Notebook

Python, Oracle ADWC and Machine Learning

Oracle Integration Cloud Autonomous Transaction Processing Adapter Configuration !!!

Oracle’s two major ground breaking innovation last year were Autonomous Data warehouse (ADW) and Autonomous Database Transaction processing (ATP) both are database offering suitable for different workload and are self-driving, self-securing, and self-repairing in nature. If you want to read more about these services then please go through above links.

ADW/ATP both can be quickly provisioned on Oracle Cloud Infrastructure, it’s take less than 5 minute to spin ADW/ATP instance and database is ready to connect.

User can use Oracle SQL Developer to connect to ADW/ATP database as long as they are supported version. These DBaaS services also offers out-of-box browser based SQL Developer tool which can be used to run any kind of SQL statements.

Here is sample snap of browser based SQL Developer capabilities –

8.png

 

Once user has Database ready, obviously there could be requirement to access data residing inside ADW/ATP instances.

Fortunately, Oracle Integration Cloud provide Adapter for connecting ADW/ATP instance, click here to know more about ATP Adapter capabilities –

In this blog I will be covering simple steps how you can connect to ADW/ATP instances using OIC Autonomous Transaction Processing Adapter (ATP) Adapter.

I made assumption that ADW/ATP instance already exists. if you not sure how to create ADW/ATP instance then refer this blog which was written by my colleague who already explained how to create ADW/ATP database instance and connect from SQL developer.

So, let move forward. Login to your Oracle Integration Cloud (OIC) home page >> Integration >> Connection >> Create >> search for “Oracle ATP” >> select the same

01

Continue reading “Oracle Integration Cloud Autonomous Transaction Processing Adapter Configuration !!!”

Enabling REST APIs to consume data from Oracle Autonomous Databases (ADW/ATP)

In a previous blog, I showed how to develop microservices to connect to Autonomous Databases and consume data (read/write) via REST APIs. Although I still highly recommend that approach, the reality is that there is an easier way to do it using Oracle REST Data Services modules that come included with any Oracle Autonomous Database (ADW/ATP).

This way you simply have to:

  1. Configure the API endpoint (Method + URL) that you want to expose as a REST API
  2. Define the underlying SQL statement that will serve to your endpoint (i.e. SELECT, INSERT, UPDATE, DELETE statements or a more complex PL/SQL script)
  3. Define the security mechanism to properly protect your APIs

That’s pretty much it, this should be a very quick, yet powerful alternative to building your own microservices to consume data from Autonomous DBs.

Continue reading “Enabling REST APIs to consume data from Oracle Autonomous Databases (ADW/ATP)”

Why we must cede to our robot overlords (to do the dull stuff)

Can I get a show of hands – whose spine shudders at the sound of their own phone ringing? If your hand is up, chances are a component of your role (or role in days gone by… the scarring can be permanent) involves operations. Day or night, it’s that dread associated with wondering “What now?”. A few years back, enterprise started outsourcing the problem of supporting key business systems to 3rd party services, and while this reduced the quantityof calls, it only served to increase the quality – now when the phone rings at 3am, you know things are bad. Real bad.

Continue reading “Why we must cede to our robot overlords (to do the dull stuff)”

AUSOUG Connect 2018 – Talking Dev

ausoug-title-01.pngIn November 2018, I had the privilege to attend the Australian Oracle User Group national conference “#AUSOUG Connect” in Melbourne. My role was to have video interviews with as many of the speakers and exhibitors at the conference. Overall, 10 interviews over the course of the day, 90 mins of real footage, 34 short clips to share and plenty of hours reviewing and post-editing to capture the best parts.

Continue reading “AUSOUG Connect 2018 – Talking Dev”

Teaching How to Invoke REST APIs from Oracle Visual Builder Web/Mobile Apps

In this blog, I am going to show you how to build a nice and simple UI with data coming from invoking REST APIs. All without code, but in just a few clicks.

I consider myself a good backend developer, good at making things functional, but I really struggle every time I need to produce nice UIs. However, using Oracle Visual Builder, I feel like I don’t have to be a UI developer or designer, I can very easily produce nice and friendly mobile UIs that consume my backend REST APIs. If you are like me, a backend programmer who loves API-first design approach, I’m sure that you will find this blog not only informative, but also refreshing.

This is a quick view of what we are going to achieve in this article:

  1. First, we are going to auto-create Service controls in Oracle Visual Builder by pointing to existing REST APIs.
  2. Then, we are going to use the out-of-the box widgets and components to build a simple, yet powerful UI that consumes such APIs.
  3. Finally, we are going to publish the UI and test it across different media, e.g. Web on a laptop, mobile, tablets, etc.

Continue reading “Teaching How to Invoke REST APIs from Oracle Visual Builder Web/Mobile Apps”

Teaching How to Invoke Gen2 Oracle Cloud Infrastructure (OCI) resources via REST APIs

I am thrilled with the Oracle’s Gen2 Cloud Infrastructure architecture, where Oracle completely separates the Cloud Control Computers from the User Code, so that no threats can enter from outside the cloud and no threats can spread from within tenants.

Obviously with more security, there comes more coordination, especially at the moment of invoking OCI resources APIs. Luckily, Oracle did a good job at providing a simple to use CLI and SDK (see here for more information).

For the purpose of this blog, I built a simple NodeJS application that helps demystify the security aspect of invoking OCI APIs. Check this link for examples of running similar code across other Programming Languages.

My NodeJS application manages OCI resources in order to:

  • List ADW instances
  • Stop an ADW instance
  • Start an ADW instance

I started this NodeJS application to list, start and stop ADW resources. However, I designed this application to easily extend it to invoke any other type of OCI resources.

I containerised this application with Docker, to make it easier to ship and run.

This is a picture of the moving parts:

Continue reading “Teaching How to Invoke Gen2 Oracle Cloud Infrastructure (OCI) resources via REST APIs”