{{announcement.body}}
{{announcement.title}}

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

DZone 's Guide to

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

Learn how!

· AI Zone ·
Free Resource

Deep learning model with SQL.


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.

You may also like: What Is Deep Learning?

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.

Column Name Description Data Type
crim per capita crime rate by the town. Number
zn the proportion of residential land zoned for lots over 25,000 sq.ft. Number
indus the proportion of non-retail business acres per town. Number
chas Charles River dummy variable (= 1 if tract bounds river; 0 otherwise). Number
nox nitrogen oxides concentration (parts per 10 million). Number
rm average number of rooms per dwelling. Number
age the proportion of owner-occupied units built before 1940. Number
dis the weighted mean of distances to five Boston employment centers. Number
rad index of accessibility to radial highways. Number
tax full-value property-tax rate per $10,000. Number
ptratio the pupil-teacher ratio by the town. Number
black 1000(Bk – 0.63)^2 where Bk is the proportion of blacks by the town. Number
lstat lower status of the population (percent). Number
medv the median value of owner-occupied homes in $1000s.

Number

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;

SETTING NAME SETTING VALUE DESCRIPTION
NNET_HIDDEN_LAYERS
Non-negative integer Defines the topology by the number of hidden layers.
The default value is 1.
NNET_NODES_PER_LAYER A list of positive integers Defines the topology by the number of nodes per layer. Different layers can have different numbers of nodes.
The value should be non-negative integers and comma-separated. For example, ’10, 20, 5′. The setting values must be consistent with NNET_HIDDEN_LAYERS. The default number of nodes per layer is the number of attributes or 50 (if the number of attributes > 50).
NNET_ACTIVATIONS A list of the following strings: Defines the activation function for the hidden layers. For example, ”’NNET_ACTIVATIONS_BIPOLAR_SIG”, ”NNET_ACTIVATIONS_TANH”’.
”NNET_ACTIVATIONS_LOG_SIG” Different layers can have different activation functions.
”NNET_ACTIVATIONS_LINEAR” The default value is ”NNET_ACTIVATIONS_LOG_SIG”.
”NNET_ACTIVATIONS_TANH” The number of activation functions must be consistent with NNET_HIDDEN_LAYERS and NNET_NODES_PER_LAYER.
”NNET_ACTIVATIONS_ARCTAN” Note:
”NNET_ACTIVATIONS_BIPOLAR_SIG” All quotes are single and two single quotes are used to escape a single quote in SQL statements.
NNET_WEIGHT_LOWER_BOUND A real number The setting specifies the lower bound of the region where weights are randomly initialized. NNET_WEIGHT_LOWER_BOUND and NNET_WEIGHT_UPPER_BOUND must be set together. Setting one and not setting the other raises an error. NNET_WEIGHT_LOWER_BOUND must not be greater than NNET_WEIGHT_UPPER_BOUND. The default value is –sqrt(6/(l_nodes+r_nodes)). The value of l_nodes for:
input layer dense attributes are (1+number of dense attributes)
input layer sparse attributes are the number of sparse attributes
each hidden layer is (1+number of nodes in that hidden layer)
The value of r_nodes is the number of nodes in the layer that the weight is connecting to.
NNET_WEIGHT_UPPER_BOUND A real number This setting specifies the upper bound of the region where weights are initialized. It should be set in pairs with NNET_WEIGHT_LOWER_BOUND and its value must not be smaller than the value of NNET_WEIGHT_LOWER_BOUND. If not specified, the values of NNET_WEIGHT_LOWER_BOUND and NNET_WEIGHT_UPPER_BOUND are system determined.                                                                                                                   
The default value is sqrt(6/(l_nodes+r_nodes)). See NNET_WEIGHT_LOWER_BOUND.
NNET_ITERATIONS Positive integer This setting specifies the maximum number of iterations in the Neural Network algorithm.
The default value is 200
NNET_TOLERANCE TO_CHAR(0< numeric_expr <1) Defines the convergence tolerance setting of the Neural Network algorithm.
The default value is 0.000001.
NNET_REGULARIZER NNET_REGULARIZER_NONE Regularization setting for Neural Network algorithm. If the total number of training rows is greater than 50000, the default is NNET_REGULARIZER_HELDASIDE. If the total number of training rows is less than or equal to 50000, the default is NNET_REGULARIZER_NONE.
NNET_REGULARIZER_L2
NNET_REGULARIZER_HELDASIDE
NNET_HELDASIDE_RATIO 0 <= numeric_expr <=1 Define the held ratio for the held-aside method.
The default value is 0.25.
NNET_HELDASIDE_MAX_FAIL The value must be a positive integer. With NNET_REGULARIZER_HELDASIDE, the training process is stopped early if the network performance on the validation data fails to improve or remains the same for NNET_HELDASIDE_MAX_FAIL epochs in a row.
The default value is 6.
NNET_REG_LAMBDA TO_CHAR(numeric_expr >=0) Defines the L2 regularization parameter lambda. This can not be set together with NNET_REGULARIZER_HELDASIDE.

The default value is 1.

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.


Further Reading

Deep Learning and Machine Learning: Part I

Deep Learning With Python for Beginners

Deep Learning for Computer Vision: A Beginner's Guide

Topics:
oracle ,oracle cloud ,machine learning ,artificial inteligence ,deep leaerning ,deep learning ,neural nets ,oracle sql ,ai

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}