Share

Using ADW Machine Learning Models in Oracle Analytics

Oracle Data Mining was an Oracle Database option for more than 2 decades now. With advancement of machine learning support in database, it became part of Oracle Advanced Analytics database option. Developers were able to use Oracle Data Mining by using Oracle Data Miner which was part of the Oracle SQL Developer.

Oracle Data Mining is PL/SQL based set of libraries which supported several supervised and unsupervised data mining functions such as classification, regression, clustering, time series analysis and other functions. These functions were support by a number of data mining algorithms such as decision trees, k-means, random forest, xboost, neural networks, ... just to name few.

With the latest releases of Oracle Database, Advanced Analytics option has been renamed into Machine Learning and it is now free to all Oracle Database users.

In Oracle Autonomous Data Warehouse, Zeppelin notebooks have been added for the interactive development of ML models.

Oracle Analytics can now connect to Oracle ADW and run ML model within the database, which is very interesting option, because no data is required to move from and to database. All operations like predictions happen within the database.

In our short exercise, we will take a look at Oracle ADW Machine Learning and we will create a simple prediction model. We will implement regression in order to predict housing prices in Boston. I have been using this exact same example with Oracle Analytics some time ago. We will then register ML model from ADW with Oracle Analytics and run the prediction from Oracle Analytics.

Machine Learning in ADW

We will use Zeppelin notebooks to explore data, prepare data and create a machine learning model. If you haven’t use Zeppelin notebooks, you will find it easy to use even without any previous experience. However good knowledge of SQL is desired. SQL is the language of choice here, so Python or R won’t help here. Maybe R in some case. This is obviously good news for Oracle database developers who are skilled in SQL and/or PL/SQL. You might also find out that using Machine Learning in Oracle database is pretty straight forward, but general understanding of ML functions and algorithms is definitely needed.

So, let’s start from the beginning.

To run this “tutorial”, Autonomous database is required. Just as a note, “on-premises” database, for example Oracle Database 19c, supports Oracle Data Miner and all its functions, which cannot be used with Autonomous.

Setting Machine Learning users

To begin, you must navigate to Oracle Cloud console and open Service Console.

ml01

Navigate to Administration and click on Manage Oracle ML Users.

ml02

Click on Create User and create one.

ml03

When user is created, you can then login into ML notebooks. In Service Console navigate to Development and select Oracle Machine Learning Notebooks.

ml04

Use just created user’s credentials to sign in.

ml05

Machine Learning Workspace opens:

ml06

From here you can navigate to Notebooks and open your projects, you can explore Examples (there is one example for every function that you can implement) and some other options which you are more than welcome to explore in detail.

We will create a new notebook. So, click on Notebooks. The list of all notebooks is presented and of course we need to create a new one. Click Create.

ml07

Enter a new notebook’s name, add comments, leave Connection set to Global and click OK.

ml08

An empty notebook is now created:

ml09

Let’s check connections first. You can do it simply by clicking the ml11 icon in top right corner.

Interpreter Building

If you don’t set these connections, then you will not able to connect to database and you will wonder why.

Exploratory Data Analysis and Data Preparation

Let’s check if our database table exist in my database schema. If you want to run a SQL statement in a notebook paragraph, type %sql before SQL statement you want to execute. If you are running a script, then type %script.

Run the following SQL statement: SELECT * FROM HOUSING; and observe the results.

ml20

We can run similar exploratory analysis as we did in my Housing Prediction exercise, which I prepared for Oracle Analytics and explore relationships between predictors and target attribute (column MV).

ml21

Then we can take a look at distribution for target attribute and explore correlations in dataset.

ml22

Mostly, finding are the same, as we don’t need to perform and addition data preparation. For example, there are no strong correlations which would be reasons for attribute removals.

Creating a machine learning model

In Oracle Machine Learning in a database everything is happening inside the database. This also means preparing and storing the models.

But before we do that, let’s just make a copy of our original dataset. The first script we will run is as follows:

%script

 

--- Drop HOUSING_ALL table if exists.

 

BEGIN

   EXECUTE IMMEDIATE 'DROP TABLE HOUSING_ALL';

EXCEPTION

   WHEN OTHERS THEN NULL;

END;

/

 

--- Create a new table HOUSING_ALL which is a copy of original HOUSING table and is used in this exercise.

 

CREATE TABLE HOUSING_ALL AS SELECT * FROM HOUSING;

 

In the next step we need to split the original data set into TRAIN and TEST dataset. Actually the later will act as a new dataset on which we will apply the model for prediction and we will compare predicted with actuals target values.

%script

 

-- Split the data into HOUSING_TRAIN and HOUSING_TEST. Split train vs. test ratio = 60%.

 

BEGIN

   EXECUTE IMMEDIATE 'DROP TABLE HOUSING_TRAIN';

EXCEPTION

   WHEN OTHERS THEN NULL;

END;

/

BEGIN

   EXECUTE IMMEDIATE 'DROP TABLE HOUSING_TEST';

EXCEPTION

   WHEN OTHERS THEN NULL;

END;

/

 

BEGIN

   EXECUTE IMMEDIATE 'CREATE TABLE HOUSING_TRAIN AS SELECT * FROM HOUSING_ALL SAMPLE (60) SEED (1)';

   DBMS_OUTPUT.PUT_LINE ('Created HOUSING_TRAIN');

   EXECUTE IMMEDIATE 'CREATE TABLE HOUSING_TEST AS SELECT * FROM HOUSING_ALL MINUS SELECT * FROM HOUSING_TRAIN';

   DBMS_OUTPUT.PUT_LINE ('Created HOUSING_TEST');

END;

/

For the prediction we are after “the big guns”, we will use Neural Networks in our prediction model.

First, model parameters table has to be created. These parameters will be later used for model creation.

As the first step a new settings table needs to be created and populated with parameters – check for more details in (1).

We will set parameters for:

  • algorithm name used,
  • auto data preparation,
  • hidden layers and number of nodes in each of layer.

%script

 

--- Drop NN_Model_settings table if exists.

 

BEGIN

   EXECUTE IMMEDIATE 'DROP TABLE NN_Model_settings';

EXCEPTION

   WHEN OTHERS THEN NULL;

END;

/

 

-- create settings table for Neural network model

 

CREATE TABLE NN_Model_settings

( setting_name VARCHAR2(30),

setting_value VARCHAR2(4000));

BEGIN

INSERT INTO NN_Model_settings (setting_name, setting_value)

values (dbms_data_mining.algo_name,

         dbms_data_mining.algo_neural_network);

 

-- Auto data preparation parameter is by default set to OFF

 

INSERT INTO NN_Model_settings (setting_name, setting_value)

VALUES (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);  

 

-- Let's define three hidden layes with respective number of nodes. Default activation function is "NNET_ACTIVATIONS_LOG_SIG".

INSERT INTO NN_Model_settings (setting_name, setting_value)

VALUES (dbms_data_mining.nnet_nodes_per_layer, '20, 20, 10');

END;

Let’s verify the settings above:

ml30

We are now ready to create a new ML model which will use parameters from the table above.

%script

 

-- Create a neural network ML model

 

BEGIN

   DBMS_DATA_MINING.CREATE_MODEL(

     model_name         => 'NN_MODEL_HOUSING',

     mining_function     => dbms_data_mining.REGRESSION,

     data_table_name     => 'HOUSING_TRAIN',

      case_id_column_name => 'IDX',

     target_column_name => 'MV',

     settings_table_name => 'NN_Model_settings');

END;

Once model is created, you can check which settings have been use. Observe that beside three settings we defined, there are also other default settings which can be amended and used in model creation.

ml31

 

%sql

select * from all_mining_model_settings where model_name='NN_MODEL_HOUSING';

 

We can now also explore what else has been generated, statistics for example. These are contained in DM$VGNN_MODEL_HOUSING view.

Model Housing View

%sql

SELECT * FROM DM$VGNN_MODEL_HOUSING;

Partition Name

Now it is time to test the prediction. Let’s run this SQL statement:

%sql

SELECT IDX, MV, prediction(NN_MODEL_HOUSING USING *) PredictedValue,

       prediction_details(NN_MODEL_HOUSING USING *) PredictionDetails

   FROM HOUSING;

For each instance in TEST dataset, we can check the predicted value and prediction details, explaining the prediction made. MV attribute is also presented for easier comparison.

ml32

We have now a new Neural Network machine learning model created in Oracle ADW. This model can now we registered with Oracle Analytics and used there.

Register ML model from ADW in Oracle Analytics

Let’s switch to Oracle Analytics now. We need release 5.7 and higher to register and use ML model from ADW.

The first step which we need to do is to register ML model from ADW with Oracle Analytics.

If there is no database connection, setup this first from the Create Connection menu from Home Page.

ml42

Navigate to Machine Learning and then from actions menu (top right corner) select Register ML Model:

ml40

If no connection is available, choose Create Connection. We have created our connection in the step before, therefore the list of connections is displayed. Choose the connection:

ml43

And then choose ML model to register:

ml44

Click on model name and click Register.

Model details are shown. Click Register once more.

ml45

Model now appears in the list of Machine Learning models in Oracle Analytics:

ml46

And finally, apply ML model from ADW

You can apply ML model using Data Flows. So let’s create one.

We need to start with TEST dataset. This dataset has also been created in my database schema which contains ML model.

ml47

The second step in data flow is Apply Model

ml48

Observe that output and input parameters have been read from the ML model:

ml49

As the last step, we need to save predicted dataset.

ml50

And run data flow:

Housing Predict Data Flow

Let’s check the results, create a new project with the following data:

Housing Prediction Results

This concludes our tutorial on how to use machine learning model created in Oracle ADW with Oracle Analytics.

Additional Information

(1) Brandan Tierney’s blog Understanding, Building and Using Neural Network Machine Leaning Models using Oracle 18c (https://developer.oracle.com/databases/neural-network-machine-learning.html),

(2) Oracle Machine Learning for SQL User’s Guide (https://docs.oracle.com/en/database/oracle/oracle-database/19/dmprg/index.html)or

(3) Oracle Machine Learning for SQL Concepts (https://docs.oracle.com/en/database/oracle/oracle-database/19/dmcon/index.html).

Become a member

Newsletter

Receive our newsletter to stay on top of the latest posts.


Ziga Vaupot

Author

Ziga Vaupot