Connect to Oracle ATP through Jupyter Notebook

Do you know Jupyter Notebook? The Jupyter Notebook is an open-source web application that allows you to create and share documents that contain live codes, equations, visualizations and narrative text. You can use it for data cleaning and transformation, numerical simulation, statistical modeling, data visualization, machine learning, and much more.

I like to use the Jupyter Notebook for my machine learning projects because it is a very useful tool. In one of my projects I needed to connect to the Oracle Autonomous Transaction Processing (ATP) Database for data visualization and it was great!

In this article we will connect to an Oracle Autonomous Transaction Processing Database instance through Jupyter Notebook.

You can download the notebook here: https://github.com/waslleysouza/oracle_autonomous_jupyter/blob/master/atp_connection.ipynb.

To create a new Oracle Autonomous Transaction Processing Database instance, read this article: Creating an Oracle Autonomous Transaction Processing (ATP) Database.

To access an Oracle Compute (Oracle Linux 7) instance, open a terminal emulator and configure a new SSH session to access it.

  • Remote host: The public IP address
  • Username = opc
  • Port = 22
  • Private key = The private key (e.g. id_rsa file)

Update the operating system packages.

$ sudo yum update -y

Install the Oracle Instant Client library.

$ sudo yum install oracle-instantclient18.3-basic.x86_64 -y

Add the Instant Client to the runtime link path.

$ sudo sh -c "echo /usr/lib/oracle/18.3/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"

$ sudo ldconfig

On the local computer, download the Client Credentials (Wallet) from the Oracle ATP Database instance, unzip it, and then copy it to the Oracle Compute instance.

Run the following command to change the location of the Client Credentials within the sqlnet.ora file.

  • <tns_path>: The location of the Client Credentials folder
  • <tns_folder>: The name of the Client Credentials folder
$ sed -i 's+?/network/admin+<tns_path>+g' <tns_folder>/sqlnet.ora

Start the Jupyter Notebook and create a new notebook.
First, install the required python libraries.

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

Import the required python libraries.

# Import required libraries
from sqlalchemy import create_engine
import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt

Create a new Oracle ATP Database connection.

  • <tns_path>: The location of the Client Credentials folder
  • <user>: The name of some created user (for example, admin)
  • <pass>: The user’s password (for example, WElcome_ # 123)
  • <tns_name>: The TNS name (for example, myatp_high)
# Create a new Oracle ATP or ADW connection
os.environ['TNS_ADMIN'] = '<tns_path>'
engine = create_engine('oracle://<user>:<pass>@<tns_name>')

On the local computer, download the iris.csv file (here) and copy it to the Oracle Compute instance. Create a new table in the database using the file.

# Create a new table using the Iris dataset
df = pd.read_csv('iris.csv')
df.to_sql('iris', engine, index=False, if_exists='replace')

Retrieve the data from the new table.

# Retrieve data from the new table
df = pd.read_sql("select * from iris", con=engine)
df.shape

Now you can explore the data.

# Explore the data
df.head(5)

Explore the data using Matplotlib.

# Explore the data using Matplotlib 
df.groupby('species')
plt.scatter(df.sepal_length, df.sepal_width)

Good job!
In this article, you learned how to connect to an Oracle Autonomous Transaction Processing Database instance through Jupyter Notebook.

Have a good time!

Author: Waslley Souza

Consultor Oracle com foco em tecnologias Oracle Fusion Middleware e SOA. Certificado Oracle WebCenter Portal, Oracle ADF e Java.

2 thoughts on “Connect to Oracle ATP through Jupyter Notebook”

  1. Hhm, I htink for anyone not familiar with Jupyter you are missing the main part!?
    “Start the Jupyter Notebook and create a new notebook.”

    How?

Leave a Reply

Your email address will not be published. Required fields are marked *