Developing a Deep Learning Model With SQL in Oracle Database: Predicting Boston House Prices

In this article, I will build a deep learning model and perform a simple regression analysis using Neural Networks, one of the new algorithms introduced by Oracle 18c for advanced analytical options. I hope it will be useful in the sense of awareness.

Topics such as Data Science and Machine Learning are among the most prominent topics today. Nowadays, we can implement the problems and methods addressed for these topics that require expertise to learn and practice with many products or software.

Oracle supports the methods and algorithms for solving the problems under these headings with the DBMS_DATA_MINING package. With this infrastructure, Oracle allows users to develop Machine Learning applications using SQL.

With the DBMS_DATA_MINING package, we can build models such as Custering, Classification, Regression, Anomaly Detection, Feature Extraction, and Association Rules, and then pass and interpret our data. The results obtained from these models can be used as input in our business scenarios.

The DBMS_DATA_MINING package is not installed by default on the Oracle database. Therefore, to benefit from this support, it is necessary to install the following package first. You can install Oracle Data Mining in your database by following the link below

https://docs.oracle.com/cd/E11882_01/datamine.112/e16807/install_odm.htm#DMADM117.

I don't need any additional installation because I will make this application in Autonomous Data Warehouse (DB) which is offered as a service in Oracle Cloud. I can use these algorithms in the Autonomous Data Warehouse. You can get Autonomous Data Warehouse service which is one of the services announced as Always Free in Oracle Open World 2019 for free and use it on the Cloud within minutes without any installation. You can follow the link for detailed information.

Deep Learning is one of the most talked-about artificial learning techniques in recent years. We closely follow the learning capacity and the limits of what they can do, and on the one hand, we want to benefit from our business scenarios.

Together with 18c, Oracle launched the infrastructure that allows us to build neural network models without the need to move data to a different environment within the database. Now let's look at an example of how to use this infrastructure and its details.

I need a data set and a problem to implement it. I choose Boston Housing Prices as a problem. To solve this problem, I will construct a regression model. I get the data set from Kaggle (Boston Housing).

Let's first examine the BOSTON_HOUSING dataset.

Now that we have reviewed the details with our dataset, let's load the BOSTON_HOUSING that we downloaded to our Oracle database.

First, create the Oracle table in which we will load the data set (train.csv) that we downloaded.

CREATE TABLE BOSTON_HOUSING
(
   ID        NUMBER,
   CRIM      NUMBER,
   ZN        NUMBER,
   INDUS     NUMBER,
   CHAS      NUMBER,
   NOX       NUMBER,
   RM        NUMBER,
   AGE       NUMBER,
   DIS       NUMBER,
   RAD       NUMBER,
   TAX       NUMBER,
   PTRATIO   NUMBER,
   BLACK     NUMBER,
   LSTAT     NUMBER,
   MEDV      NUMBER
);

Now that we have created our table, we will load the dataset we downloaded as CSV into the table; we have multiple methods to do this:

• Using Oracle External Table.
• Using Oracle SQL Loader.
• Using SQL-PL/SQL editors (Oracle SQL Developer, Toad, PL/SQL Developer, etc).

I will load the data set with the help of the editor I use. I use Oracle SQL Developer as an editor. With Oracle SQL Developer, you can load data as follows.

SELECT * FROM BOSTON_HOUSING;

We have completed the dataset loading process.

When we observe the data, we see the details according to the various characteristics of the houses. Each row contains information on the specific characteristics of the house. Our basic parameters for regression analysis are as presented in this table. In this table, we predict the result of the regression analysis. The MEDV column is the target variable that we will use in this analysis.

To build a Deep Learning model in the database, we have to use the Neural Network algorithm in the DBMS_DATA_MINING package. To use this algorithm, we need to understand some parameters and define them for later use. These parameters are as follows;

As you can see, there are many parameters that we can use to produce the model. We can use these parameters with different settings according to the network topology we want to set up.

First, we have to do; create and save insert statements to set these parameters in a way that we want to use. We just need to add the parameters we want to change to this table. Oracle will use the remaining parameters with their default values. We will then give this table as a parameter to read the algorithm settings to the function we will use to create our model.

CREATE TABLE neural_network_settings (
    setting_name    VARCHAR2(1000),
    setting_value   VARCHAR2(1000)
);

BEGIN
    INSERT INTO neural_network_settings (
        setting_name,
        setting_value
    ) VALUES (
        dbms_data_mining.prep_auto,
        dbms_data_mining.prep_auto_on
    );

    INSERT INTO neural_network_settings (
        setting_name,
        setting_value
    ) VALUES (
        dbms_data_mining.algo_name,
        dbms_data_mining.algo_neural_network
    );

    INSERT INTO neural_network_settings (
        setting_name,
        setting_value
    ) VALUES (
        dbms_data_mining.nnet_activations,
        '''NNET_ACTIVATIONS_LOG_SIG'',''NNET_ACTIVATIONS_LOG_SIG'',''NNET_ACTIVATIONS_LOG_SIG'''
    );

    INSERT INTO neural_network_settings (
        setting_name,
        setting_value
    ) VALUES (
        dbms_data_mining.nnet_nodes_per_layer,
        '512,250,100'
    );

    COMMIT;
END;

select * from neural_network_settings;

As we can see, we have added the parameters that we do not want to use with the default settings to the settings table with the values we want.

If we explain roughly the three parameters we insert;

• We selected the algorithm in line number two.
• In line number 4, we have determined how many layers the neural network will consist of and how many hidden units there will be. In this example, we are building a 3-tier network with 512 hidden units on the first layer, 250 hidden units on the second layer and 100 hidden units on the last layer.
• In line three, we have determined the activation function we want to use in each layer. In this example, we have declared our SIGMOID user from each layer.

Yes, we uploaded our training data. We have set the algorithm settings. Now it is time to create our model (training).

BEGIN
   DBMS_DATA_MINING.CREATE_MODEL(
      model_name          => 'DEEP_LEARNING_MODEL',
      mining_function     => dbms_data_mining.REGRESSION,
      data_table_name     => 'BOSTON_HOUSING',
      case_id_column_name => 'ID',
      target_column_name  => 'MEDV',
      settings_table_name => 'neural_network_settings');
END;

Yes, we have called the function we will create our model with the necessary parameters. If we examine what these parameters are.

• Model name: A unique model name that we will give to our model.

• Mining function: The parameter to which we report the type of problem to solve. Since we made a regression in this problem, we selected this parameter as regression. We could choose CLASSIFICATION or CLUSTERING etc. according to the type of problem.

• Data_table_name: The parameter to which the table is to be used in training.

• Case_id_column_name: The parameter that gives the key that separates the data we use in training. If there is a composite key, we must create a new attribute before creating the model. Since our ID column is unique in our table, we were able to give this column directly.

• Target_column_name: Where we tell which column our model should use as the target. The target variable of our model. In other words, the value the model predicts.

• Settings_table_name: Setting the table where the parameters that we want the neural network to use when writing the model.

Yes, we train our model. All parametric details about our model can be accessed by typing the following query in this step.

select * from all_mining_model_settings where model_name='DEEP_LEARNING_MODEL';

In the table, we see both the parameters we give from outside and the model parameters from which the assignments are made with default values.

Now let's look at the tables with detailed information about this Deep Learning model we created.

To reach the weight values produced in each layer;

select * from DM$VADEEP_LEARNING_MODEL;

For general information about the model;

select * from DM$VGDEEP_LEARNING_MODEL;

To reach the normalization values of the variables;

select * from DM$VNDEEP_LEARNING_MODEL;

Now let's see how we can make new predictions on our model. We can do this in two ways.

First, we can run our model for all of the aggregate values in a table. To do this, we will use the test.csv file in the data files we downloaded from Kaggle. Let's create the table where we will load this file and load the data inside.

CREATE TABLE BOSTON_HOUSING_TEST
(
   ID        NUMBER,
   CRIM      NUMBER,
   ZN        NUMBER,
   INDUS     NUMBER,
   CHAS      NUMBER,
   NOX       NUMBER,
   RM        NUMBER,
   AGE       NUMBER,
   DIS       NUMBER,
   RAD       NUMBER,
   TAX       NUMBER,
   PTRATIO   NUMBER,
   BLACK     NUMBER,
   LSTAT     NUMBER,
   MEDV      NUMBER
);

-- We can load the data with the method described above and query the table.

SELECT * FROM BOSTON_HOUSING_TEST;

Now let's input this data into our model and generate predictions.

SELECT T.*,
 PREDICTION (DEEP_LEARNING_MODEL USING *) NN_RES
FROM BOSTON_HOUSING_TEST T;

Now let's see how we can predict with a single record.

SELECT prediction(DEEP_LEARNING_MODEL USING 0.02 as crim,
                                            12.5 as zn,
                                            8.01 as indus,
                                            0    as chas,
                                            0.48 as nox,
                                            6.25 as rm,
                                            15.4 as age,
                                            4.92 as dis,
                                            4.00 as rad,
                                            242  as tax,
                                            15.3 as pratio,
                                            399  as balck,
                                            4.09 as lstat) pred
FROM dual;

As we have seen, we have been able to give the values to our model in a very simple way and get the prediction result in less than a second.