Oracle PL/SQL Packages to Automate Oracle Machine Learning Model Builds

There are several good examples and tutorials/workshops that show how to write sql and pl/sql that create machine learning models. To simplify the process this package was created as a stored procedure, that when invoked creates and populates a settings table, a configuration table, and then provides a second procedure to build the machine learning artifacts (models, lift tables, apply tables, and confusion matrix tables).

Once set up, you would just do the following for a new project:

• Update your model settings and specify the table, target column, algorithm, case_id, etc. and execute the build ("exec model_build_pkg.build_models"), which would generate the code and run the build. Note this assumes you create ML user "ml_user".

Step 1: Grant user privileges

• Normally dm_role (or oml_developer) roles are sufficient to build models. However when pl/sql is executed in stored procedures the grants must be direct and not through a role. Execute the following grants from admin (or sys in dbcs) to ml_user (or other ml user name).

<copy>alter user ml_user quota 100m on data;
grant execute on dbms_data_mining to ml_user;
grant create mining model to ml_user;
grant create table to ml_user;</copy>

Step 2: Compile the package.

• Copy/paste the code block below into sqlplus or sqldeveloper. The code is at the bottom of this page.

Step 3: Execute the create_config procedure.

• Execute the create_config procedure.

<copy>exec model_build_pkg.create_config;</copy>

• This creates and populates the MODEL_BUILD_SETTINGS table (normally used in ML builds) and a MODEL_CONFIG table. This procedure only needs to be run once. If it is re-run it drops and creates and re-populates the tables. You must review the contents and adjust to your particular case. The existing values are only used as a sample.

• The MODEL_BUILD_SETTINGS table has the following values. Note in this particular case a text index was used and therefore has text index settings - these would not normally be required and you can remove those rows.

• The MODEL_CONFIG table has the following values. The model build procedure (run next) loops through this table and builds a model for each row (in this case five models and related tables are built. You must update this table with your own values.

Step 4: Execute the build_models procedure.

• If you have not first run the create_config procedure you will be notified as such.

<copy>set serveroutput on
exec model_build_pkg.build_models;</copy>

• Now that you have created and updated the configuration you can build the models. This may take some time, depending on the number of models and the data volume.

<copy>exec model_build_pkg.build_models;</copy>

Code - Compile This

• Enter this in SQL Developer.

<copy>
CREATE OR REPLACE PACKAGE model_build_pkg IS
---------------------------------------------
	PROCEDURE create_config;
	PROCEDURE build_models;
END model_build_pkg;
/

CREATE OR REPLACE PACKAGE BODY model_build_pkg AS
---------------------------------------------
PROCEDURE create_config AS 
BEGIN
-- admin needs to: grant create table to ml_user

begin
execute immediate 'DROP table model_build_settings';
exception when others then null;
end;

execute immediate 'create table model_build_settings (setting_name varchar2(30),setting_value varchar2(4000))';

execute immediate 'insert into model_build_settings values (''ALGO_NAME'', ''ALGO_DECISION_TREE'')';
execute immediate 'insert into model_build_settings values (''PREP_AUTO'', ''ON'')';
execute immediate 'insert into model_build_settings values (''ODMS_TEXT_POLICY_NAME'',''MY_POLICY'')';
execute immediate 'insert into model_build_settings values (''TREE_TERM_MAX_DEPTH'', 7)';
execute immediate 'insert into model_build_settings values (''TREE_TERM_MINREC_SPLIT'', 20)';
execute immediate 'insert into model_build_settings values (''TREE_TERM_MINPCT_SPLIT'', .1)';
execute immediate 'insert into model_build_settings values (''TREE_TERM_MINREC_NODE'', 10)';
execute immediate 'insert into model_build_settings values (''TREE_TERM_MINPCT_NODE'', 0.05)';
commit;

begin
execute immediate 'DROP table model_config';
exception when others then null;
end;

execute immediate 'create table model_config ( '||
'model_name				varchar2(100) '||
', algorithm_name			varchar2(100) '||
', train_table_Name			varchar2(100) '||
', case_id				varchar2(100) '||
', target_column_name		VARCHAR2(100) '||
', mining_function			VARCHAR2(100) '||
', test_table_name			VARCHAR2(100) '||
', positive_target_value		VARCHAR2(100)) ';

execute immediate 'insert into model_config values(''INSURANCE_DT'',''ALGO_DECISION_TREE'',''CUST_INSUR_LTV'',''CUST_ID'',''LTV_BIN'',''CLASSIFICATION'',''CUST_INSUR_LTV_APPLY'',''1'')';
execute immediate 'insert into model_config values(''INSURANCE_SVM'',''ALGO_SUPPORT_VECTOR_MACHINES'',''CUST_INSUR_LTV'',''CUST_ID'',''LTV_BIN'',''CLASSIFICATION'',''CUST_INSUR_LTV_APPLY'',''1'')';
execute immediate 'insert into model_config values(''INSURANCE_RF'',''ALGO_RANDOM_FOREST'',''CUST_INSUR_LTV'',''CUST_ID'',''LTV_BIN'',''CLASSIFICATION'',''CUST_INSUR_LTV_APPLY'',''1'')';
execute immediate 'insert into model_config values(''INSURANCE_NN'',''ALGO_NEURAL_NETWORK'',''CUST_INSUR_LTV'',''CUST_ID'',''LTV_BIN'',''CLASSIFICATION'',''CUST_INSUR_LTV_APPLY'',''1'')';
execute immediate 'insert into model_config values(''INSURANCE_NB'',''ALGO_NAIVE_BAYES'',''CUST_INSUR_LTV'',''CUST_ID'',''LTV_BIN'',''CLASSIFICATION'',''CUST_INSUR_LTV_APPLY'',''1'')';
commit;

END create_config;

---------------------------------------------

PROCEDURE build_models IS 

TYPE model_config_rt IS RECORD (
model_name				varchar2(100)
, algorithm_name			varchar2(100)
, train_table_Name			varchar2(100)
, case_id					varchar2(100)
, target_column_name		VARCHAR2(100)
, mining_function			VARCHAR2(100)
, test_table_name			VARCHAR2(100)
, positive_target_value		VARCHAR2(100));

TYPE model_config_aat IS TABLE OF model_config_rt INDEX BY PLS_INTEGER;
 
l_model_config   model_config_aat;
   
v_accuracy number;

BEGIN

-- test to see if the config table has been created

BEGIN
EXECUTE IMMEDIATE 'select count(*) from model_config';

-- drop/create all_lift_data table

begin
execute immediate 'DROP TABLE all_lift_data PURGE';
exception when others then null;
end;

begin
execute immediate 'CREATE TABLE all_lift_data (algo_name VARCHAR2(50), QUANTILE_NUMBER NUMBER, GAIN_CUMULATIVE NUMBER)';
exception when others then null;
end;

EXECUTE IMMEDIATE q'[select * from model_config]' BULK COLLECT INTO l_model_config;
      
-- loop through algorithms

-- FOR i IN (select * from model_config) LOOP
FOR i IN 1 .. l_model_config.COUNT LOOP

execute immediate 'delete from model_build_settings where setting_name = ''ALGO_NAME''';
execute immediate 'insert into model_build_settings select ''ALGO_NAME'', '''||l_model_config (i).algorithm_name||''' from dual';

begin
dbms_data_mining.drop_model(l_model_config (i).model_name);
exception when others then null;
end;

begin
dbms_data_mining.create_model(
	model_name		=> l_model_config (i).model_name,
	mining_function	=> l_model_config (i).mining_function, 
	data_table_name	=> l_model_config (i).train_table_name, 
	case_id_column_name => l_model_config (i).case_id,
	target_column_name	=> l_model_config (i).target_column_name, 
	settings_table_name	=> 'model_build_settings');
end;

-- drop apply result

begin
execute immediate 'drop table '||l_model_config (i).model_name||'_apply purge';
execute immediate 'drop table '||l_model_config (i).model_name||'_lift purge';
exception when others then null;
end;

-- test the model by generating a apply result and then create a lift result

begin
dbms_data_mining.apply(
	model_name		=> l_model_config (i).model_name,
	data_table_name	=> l_model_config (i).test_table_name,
	case_id_column_name	=> l_model_config (i).case_id,
	result_table_name	=> l_model_config (i).model_name||'_apply');
exception when others then null;
end;

begin
dbms_data_mining.compute_lift(
	apply_result_table_name		=> l_model_config (i).model_name||'_apply',
	target_table_name			=> l_model_config (i).test_table_name,
	case_id_column_name			=> l_model_config (i).case_id,
	target_column_name			=> l_model_config (i).target_column_name,
	lift_table_name			=> l_model_config (i).model_name||'_lift',
	positive_target_value		=> l_model_config (i).positive_target_value,
	score_column_name			=> 'PREDICTION',
	score_criterion_column_name => 'PROBABILITY',
	num_quantiles				=> 100);
exception when others then null;
end;

begin
execute immediate 'insert into all_lift_data select '''||l_model_config (i).algorithm_name||''', QUANTILE_NUMBER, GAIN_CUMULATIVE from '||l_model_config (i).model_name||'_lift';
exception when others then null;
end;

-- build confusion matrix table

begin
EXECUTE IMMEDIATE 'drop table '||l_model_config (i).model_name||'_cmatrix';
exception when others then null;
end;

begin
DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
	accuracy                     => v_accuracy,
	apply_result_table_name      => l_model_config (i).model_name||'_apply',
	target_table_name            => l_model_config (i).test_table_name,
	case_id_column_name          => l_model_config (i).case_id,
	target_column_name           => l_model_config (i).target_column_name,
	confusion_matrix_table_name  => l_model_config (i).model_name||'_cmatrix',
	score_column_name            => 'PREDICTION',
	score_criterion_column_name  => 'PROBABILITY',
	score_criterion_type         => 'PROBABILITY');
DBMS_OUTPUT.PUT_LINE(l_model_config (i).model_name||' Accuracy: ' || ROUND(v_accuracy * 100,2));
end;

END LOOP;

EXCEPTION WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
      DBMS_OUTPUT.put_line('Table MODEL_CONFIG does not exist - first run model_build_pkg.create_config');
    ELSE RAISE;
    END IF;
END;

END build_models;
---------------------------------------------
END model_build_pkg;
---------------------------------------------
/</copy>