Oracle Data Science: Connecting to Oracle Autonomous Data Warehouse from a Notebook Session

The Oracle Accelerated Data Science (ADS) SDK is a Python library that is included as part of the Oracle Cloud Infrastructure Data Science service. ADS offers a friendly user interface, with objects and methods that cover all the steps involved in the lifecycle of machine learning models, from data acquisition to model evaluation and interpretation.

We will use ADS to create a connection to Oracle Autonomous Data Warehouse (ADW). Detailed explanation on how setup connection to ADW is also available in ADS documentation (click here for more: https://docs.cloud.oracle.com/en-us/iaas/tools/ads-sdk/latest/index.html.

If you haven't done so, you should look into getting-started.ipynb before you continue with this script.

Wallet: location & content

Wallet file which contains the credentials and other neccessary information can be downloaded from ADW instance. Wallet files are then uploaded to Data Science instance folder.
In [1]:
import os
import warnings
warnings.filterwarnings('ignore')
In our example, wallet files is located in "wallet" folder:
In [3]:
ls -l /home/datascience/block_storage/wallet
total 72
-rw-r--r--. 1 datascience users  6661 Mar 20 17:46 cwallet.sso
-rw-r--r--. 1 datascience users  6616 Mar 20 17:46 ewallet.p12
-rw-r--r--. 1 datascience users  3241 Mar 20 17:46 keystore.jks
-rw-r--r--. 1 datascience users   691 Mar 20 17:46 ojdbc.properties
-rw-r--r--. 1 datascience users   183 Mar 20 17:46 readme.md
-rw-r--r--. 1 datascience users   138 Mar 20 18:31 sqlnet.ora
-rw-r--r--. 1 datascience users  1160 Mar 20 17:46 tnsnames.ora
-rw-r--r--. 1 datascience users  3335 Mar 20 17:46 truststore.jks
drwxr-xr-x. 3 datascience users  4096 Mar 20 18:32 Wallet_OABOOTCAMPADW/
-rw-r--r--. 1 datascience users 25048 Mar 20 18:33 Wallet_OABOOTCAMPADW.zip
From all the files TNSNAMES.ORA contains the connection information which we will use:
In [4]:
cat /home/datascience/block_storage/wallet/tnsnames.ora
********_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=zepp9pq65uhav7e_********_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

********_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=zepp9pq65uhav7e_********_low.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

********_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=zepp9pq65uhav7e_********_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

We need to set 4 environment variables: TNS_ADMIN, ADW_SID, ADW_USER and ADW_PASSWORD.
In [11]:
%env TNS_ADMIN=/home/datascience/block_storage/wallet
%env ADW_SID=********_high
%env ADW_USER=********
%env ADW_PASSWORD="********"
env: TNS_ADMIN=/home/datascience/block_storage/wallet
env: ADW_SID=********_high
env: ADW_USER=********
env: ADW_PASSWORD="********"
We are now good to connect.
Let's import DatasetFactory library first. DatasetFactory allows datasets to be loaded into ADS.
In [12]:
from ads.dataset.factory import DatasetFactory
Construct uri as the connection source.
In [13]:
uri=f'oracle+cx_oracle://{os.environ["ADW_USER"]}:{os.environ["ADW_PASSWORD"]}@{os.environ["ADW_SID"]}'
Then specify the table and the target feature (let's assume that this is a classification example). Table can be specified as a SQL statement, but there are some other options too. Assumption here is that data resides in a single database table.
In [14]:
table = "BANK_DATA"
target = "y"
After you specify your query (in our case simply specify a table), then use ADS to query a table from ADW and load data to ADSDataset object using DatasetFactory.
In [7]:
if target != "BANK_DATA":
    ds = DatasetFactory.open(uri, format="sql", table=table, target=target).set_positive_class('yes')
else: 
    ds = DatasetFactory.open(uri, format="sql", table=table)

TIP

ADS now supports the ability to query based on a SQL expression.

For example,

ds = DatasetFactory.open(uri, format="sql", table="SELECT prod_id, cust_id, time_id FROM SH.sales")

Using entire dataset for graphing (4521 rows)
TIP:
  + Use show_in_notebook() to visualize the dataset.
  + Use get_recommendations() to view and apply recommendations for dataset optimization.

You can run the two functions: show_in_notebook() and get_recommendations() in order to review and analyze the data just read and perform some dataset transformations. Due to technical limitations (I can't export results of "show_in_notebook"), I am simply presenting the content of ADSDataset object called ds.
In [9]:
ds.head()
Out[9]:

age job marital education defaults balance housing loan contact day month duration campaign pdays previous poutcome y
0 45 management divorced tertiary no 0 no no unknown 1 jun 5 1 -1 0 unknown False
1 40 services married secondary no 0 yes no cellular 2 jun 297 1 28 3 failure False
2 28 blue-collar single primary no 0 yes yes unknown 3 jun 380 4 -1 0 unknown False
3 59 housemaid married secondary no 0 no no telephone 3 feb 722 1 99 4 failure False
4 28 services married secondary no 0 yes no cellular 5 may 335 2 281 8 failure False
This concludes this simple exercise. ds is a Dataset object which will be used later in forthcoming exercises/blog posts.
In [ ]:
 

Popular posts from this blog

Oracle Analytics 5.9: Using Web Map Service (WMS) and Tiled Maps (XYZ)

OBI 12c Series: Connecting OBI with Hyperion Planning

Having issue with opening RPD file after you've downloaded a snapshot?