SAP HANA Cloud is enriched with an Automated Machine Learning (AutoML) approach. AutoML can be helpful, for example, to enable data scientists to quickly prototype an initial machine learning model.

A machine learning model is a program that finds patterns as well as recommends decisions based on patterns. This intelligence is made possible by first ‘training’ the model with a large dataset. During training, the machine learning algorithm is optimized to find certain patterns or outputs from the dataset, depending on the task. The output of this process - often a computer program with specific rules and data structures - is called a machine learning model.

AutoML with SAP HANA Cloud is a great tool to see what is possible with a dataset, and if it is worth investing more time into a use case.

PAL (Predictive Analytics Library) AutoML enables:

Improved PAL models and enhanced business impact
Composite pipeline models using an optimal combination of multiple PAL algorithms
Automated algorithm comparison and selection
Parameter search with optimal selection

Productivity Uplift and Expert Experience

Expert Data Scientists can derive optimal models in less time and with better utilization of compute time
Comparable AutoML expertise that addresses trending and competitive capability gaps
ML predictions with higher accuracy and value
Less time to value

For this lesson, a data set containing customer transactions as a table has already been loaded into the SAP HANA Cloud Database (GX_TRANSACTIONS).

The business problem is to predict whether a transaction is fraudulent or not. Such use cases are often quite challenging and thus require different techniques before implementing a machine learning model.

Try it out!

Select the following link to access the HANA Cloud Database Explorer.

Note

All of the following SQL statements should be executed in the same SQL console window within DB Explorer.

Step 1

For this scenario, the first step is to prepare the data using the table GX_TRANSACTIONS. A couple of the columns need different data types for use with the AutoML functions. This step converts measures from integers to doubles along with casting the FRAUD column as NVARCHAR. In addition, the step creates the view TRANSACTIONSV to access the changes in the original dataset.

Copy the following statement and run it in DB Explorer to create the TRANSACTIONSV View with the new data types:

Code Snippet

1234567891011121314
CREATE OR REPLACE VIEW "TRANSACTIONSV" AS
	SELECT 
		"TRANSACTION_ID",
		"ORIGIN",
		"CLASS",
		CAST("AMOUNT" AS DOUBLE) AS "AMOUNT",
		CAST("OLD_BALANCE_ORIGIN" AS DOUBLE) AS "OLD_BALANCE_ORIGIN",
		CAST("NEW_BALANCE_ORIGIN" AS DOUBLE) AS "NEW_BALANCE_ORIGIN",
		"DESTINATION",
		CAST("OLD_BALANCE_DEST" AS DOUBLE) AS "OLD_BALANCE_DEST",
		CAST("NEW_BALANCE_DEST" AS DOUBLE) AS "NEW_BALANCE_DEST",
		CAST("FRAUD" AS NVARCHAR(20)) AS "FRAUD"
	FROM "GX_TRANSACTIONS";
SELECT TOP 10 * FROM "TRANSACTIONSV";

Step 2

For the next step we seek to downsample the training dataset and partition it for training and validation.

Downsampling helps address class imbalance by reducing the number of samples in the majority class, making the dataset more balanced for training.

Partitioning splits the data into training and testing sets to evaluate the model’s performance on unseen data.

This preparation involves four different stages:

(i) Parameter Initialization

A temporary table #PAL_PARAMETER_TBL is created to store procedure parameters

(ii) Downsampling

Calls the PAL_SAMPLING procedure to downsample the TRANSACTIONSV table using the specified parameters, and stores the downsampled data in the downsample_out variable.

(iii) Partitioning

Sets parameters for partitioning and then calls the PAL_PARTITION procedure to partition the downsampled data using the specified parameters before storing it in the out_0 variable.

(iv) Result Output

Creates a temporary table #PAL_PARTITION_RESULT_TBL to store the partitioned data and then selects and displays the first 10 rows from this data.

Note

As these queries create temporary tables for result storage, please ensure that

Copy the following query and run it the same SQL console as the previous query in DB Explorer:

Code Snippet

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
DO BEGIN
	/*** Parameter Initialization ***/
	IF EXISTS (SELECT *
		FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_PARAMETER_TBL'
			AND SCHEMA_NAME = CURRENT_SCHEMA
			AND CONNECTION_ID = CURRENT_CONNECTION) THEN
		DROP TABLE "#PAL_PARAMETER_TBL";
	END IF;
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_PARAMETER_TBL"(
		"PARAM_NAME" VARCHAR(256),
		"INT_VALUE" INTEGER,
		"DOUBLE_VALUE" DOUBLE,
		"STRING_VALUE" VARCHAR(5000)
	);
	
	/*** Downsampling ***/
	TRUNCATE TABLE "#PAL_PARAMETER_TBL";
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('SAMPLING_METHOD', 8, null, null); 
   -- stratified_wor;
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('RANDOM_SEED', 1234, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('COLUMN_CHOOSE', null, NULL, 'FRAUD');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('PERCENTAGE', null, 0.05, null);
	
	/*** Table variables *****/
	lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
	lt_inputdata = SELECT * FROM "TRANSACTIONSV";
	
	/*** PAL function call *****/
	CALL "_SYS_AFL"."PAL_SAMPLING"( :lt_inputdata, :lt_parms, downsample_out );
	
	/*** Outsampling train and testdata ***/
	truncate TABLE "#PAL_PARAMETER_TBL";
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('PARTITION_METHOD', 1, null, null);
	-- stratified;
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('STRATIFIED_COLUMN', null, NULL, 'FRAUD');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('RANDOM_SEED', 1234, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('TRAINING_PERCENT', null, 0.7, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('TESTING_PERCENT', null, 0.3, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('VALIDATION_PERCENT', null, 0.0, null);
	
	/*** Table variables *****/
	lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
	lt_inputdata = SELECT * FROM :downsample_out;
	CALL _SYS_AFL.PAL_PARTITION( :lt_inputdata, :lt_parms, out_0 );
	
	/*** results *****/
	IF EXISTS (SELECT * FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_PARTITION_RESULT_TBL'
			AND SCHEMA_NAME = CURRENT_SCHEMA
			AND CONNECTION_ID = CURRENT_CONNECTION) THEN
		DROP TABLE "#PAL_PARTITION_RESULT_TBL";
	END IF;
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_PARTITION_RESULT_TBL" AS (
		SELECT *
		FROM :out_0
	);
END;
SELECT COUNT(*), PARTITION_TYPE
    	FROM "#PAL_PARTITION_RESULT_TBL" GROUP BY PARTITION_TYPE;

Result Output:

Overall, this procedure prepares the data for a machine learning model by ensuring a balanced dataset and creating appropriate training and validation sets.

Step 3

The following SQL procedure defines an AutoML scenario configuration for a classification task. Here’s a breakdown of its functionality:

(i) AutoML Configuration

Calls the PAL_AUTOML_CONFIG procedure to create an AutoML configuration using the specified parameters in the #PAL_PARAMETER_TBL table. The lt_config and lt_info variables together store the information about the AutoML configuration.

(ii) Result Output

Creates temporary tables #PAL_AUTOML_CONFIG_TAB and #PAL_AUTOML_CONFIGINFO_TAB to store the configuration and information, respectively.

Overall, this procedure configures an AutoML pipeline for a classification task, specifying the pipeline type, removing certain operators, and modifying parameters for selected operators.

Execute the following query in the currently open SQL console in DB Explorer:

Code Snippet

1234567891011121314151617181920212223242526272829303132333435363738394041
DO BEGIN
	TRUNCATE TABLE "#PAL_PARAMETER_TBL";

	/*** Parameter initialization and automl config ***/
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('PIPELINE_TYPE', null, NULL, 'classifier');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('VERIFY_CONFIG', 1, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_DICT', null, NULL, 'light');
	-- Uses a lightweight DEFAULT AutoML classifier operator dictionary;

	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_REMOVE', null, null, 'NB_Classifier');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_REMOVE', null, null, 'MLP_M_TASK_Classifier');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_REMOVE', null, null, 'TomekLinks');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_REMOVE', null, null, 'SMOTETomek');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_MODIFY', null, null, '{"RDT_Classifier":{"TREES_NUM":[101]}}');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONFIG_MODIFY', null, null, '{"HGBT_Classifier": {"ITER_NUM": [50, 100], "MAX_DEPTH": {"range": [1, 1, 6]}, "ETA": [0.1, 0.5, 1.0]}}');

	lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
	CALL _SYS_AFL.PAL_AUTOML_CONFIG( :lt_parms, lt_config, lt_info );
	IF EXISTS (SELECT *
		FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_AUTOML_CONFIG_TAB'
			AND SCHEMA_NAME = CURRENT_SCHEMA
			AND CONNECTION_ID = CURRENT_CONNECTION) THEN
		DROP TABLE #PAL_AUTOML_CONFIG_TAB;
	END IF;
	
	/*** Create temporary tables to store config and info data ***/
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_CONFIG_TAB" AS (
		SELECT * FROM :lt_config);
	IF EXISTS (SELECT *
		FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_AUTOML_CONFIGINFO_TAB'
			AND SCHEMA_NAME = CURRENT_SCHEMA
			AND CONNECTION_ID = CURRENT_CONNECTION) THEN
		DROP TABLE #PAL_AUTOML_CONFIGINFO_TAB;
	END IF;
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_CONFIGINFO_TAB" AS (
		SELECT * FROM :lt_info);
END;
/*** Result Output ***/
SELECT * FROM "#PAL_AUTOML_CONFIGINFO_TAB";

Result Output:

Key Points

This procedure provides a foundation for building an AutoML pipeline for classification tasks. Enhance or customize the pipeline by adding or removing operators and modifying parameters as required.

Step 4

The following procedure performs the actual AutoML classification task that automatically determines the best pipeline models. Using this procedure involves four steps:

(i) Parameter Initialization

Populates the temporary table #PAL_PARAMETER_TBL with the required procedure parameters.

(ii) Data Preparation

Retrieves the training data from the TRANSACTIONSV and #PAL_PARTITION_RESULT_TBL tables.

(iii) AutoML Fit

Calls the PAL_AUTOML_FIT procedure to train an AutoML model using the specified parameters and training data and then stores the best pipeline, model and information in the out_0, out_1 and out_2 variables, respectively.

(iv) Result Output

Creates temporary tables to store the best pipeline, model and information.

Overall, this procedure performs an AutoML classification task, including data preparation, model training, and result storage.

Execute the following query in the SQL console window as the previous query in DB Explorer:

Code Snippet

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374
DO BEGIN
	/*** Parameters ***/
	DECLARE tvar TIMESTAMP;
	DECLARE myuser NVARCHAR(12);

	SELECT CURRENT_TIMESTAMP INTO tvar FROM dummy;
	SELECT CURRENT_USER INTO myuser FROM dummy;

	TRUNCATE TABLE "#PAL_PARAMETER_TBL";
	/*** Populate table with procedure parameters ***/
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('PIPELINE_TYPE', null, NULL, 'classifier');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('HAS_ID', 1, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('DEPENDENT_VARIABLE', null, null, 'FRAUD');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('FOLD_NUM', 5, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('RESAMPLING_METHOD', null, null, 'stratified_cv');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('THREAD_RATIO', null, 0.8, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('RANDOM_SEED', 1234, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('GENERATIONS', 2, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('POPULATION_SIZE', 5, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('OFFSPRING_SIZE', 5, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('SUCCESIVE_HALVING', 1, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('CONNECTIONS', null, NULL, 'default');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('MAX_EVAL_TIME_MINS', null, 1.0, NULL);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('EARLY_STOP', 3, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('SCORINGS', null, NULL, '{"F1_SCORE_1": 1.0}');
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('ELITE_NUMBER', 5, null, null);
	
	/*** Retrieve AutoML configuration prepared before, 
	or start with a clean system configuration; ***/
	-- INSERT INTO "#PAL_PARAMETER_TBL" VALUES ('CONFIG_DICT',null,NULL,'light');  -- 'default';
	INSERT INTO "#PAL_PARAMETER_TBL"
	SELECT 'CONFIG_DICT', NULL, NULL, CONTENT
	  FROM #PAL_AUTOML_CONFIG_TAB WHERE ROW_INDEX = 0;
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('VERIFY_CONFIG', 1, null, null);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('EXECUTION_ID', null, NULL, 'AutoMLFit-Fraud_ClassModel-' || :tvar || '-' || :myuser);
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES('RETENTION_PERIOD', 1, null, null);
	
	/*** Table variables *****/
	lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
	lt_fitdata = SELECT D.* FROM "TRANSACTIONSV" AS D, 
   "#PAL_PARTITION_RESULT_TBL" AS P
		WHERE D.TRANSACTION_ID = P.TRANSACTION_ID AND P.PARTITION_TYPE = 1;
	
	/*** PAL function call *****/
	CALL _SYS_AFL.PAL_AUTOML_FIT( :lt_fitdata, :lt_parms, out_0, out_1, out_2 );
	
	/*** Results *****/
	IF EXISTS (SELECT * FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_AUTOML_BEST_PIPELINE_TBL'
		AND SCHEMA_NAME = CURRENT_SCHEMA
		AND CONNECTION_ID = CURRENT_CONNECTION) 
      THEN DROP TABLE #PAL_AUTOML_BEST_PIPELINE_TBL;
	END IF;
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_BEST_PIPELINE_TBL" AS (
		SELECT * FROM :out_0
	);
	IF EXISTS (SELECT * FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_AUTOML_MODEL_TBL'
		AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION) 
      THEN DROP TABLE #PAL_AUTOML_MODEL_TBL;
	END IF;
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_MODEL_TBL" AS (
	SELECT * FROM :out_1 );
	IF EXISTS (SELECT * FROM M_TEMPORARY_TABLES
		WHERE TABLE_NAME = '#PAL_AUTOML_INFO'
		AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION) 
      THEN DROP TABLE #PAL_AUTOML_INFO;
	END IF;
	CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_INFO" AS (
	SELECT * FROM :out_2 );
END;
SELECT * FROM "#PAL_AUTOML_BEST_PIPELINE_TBL";
SELECT * FROM "#PAL_AUTOML_MODEL_TBL";
SELECT * FROM "#PAL_AUTOML_INFO";

Result Output:

Key Points

Step 5

The following SQL procedure evaluates the performance of the best AutoML pipeline model on the hold-out test dataset.

The procedure utilizes the temporary table #PAL_PARAMETER_TBL to store procedure parameters and sets the thread ratio to 0.8.
The test data from the TRANSACTIONSV and #PAL_PARTITION_RESULT_TBL tables provides training for the AutoML model in the IN_MODELT table.
The PAL_PIPELINE_SCORE procedure evaluates the model’s performance on the test data using the specified parameters.
Temporary tables store the evaluation results, statistics, and predictions.

Overall, this procedure evaluates the performance of a trained AutoML model on a test dataset, providing insights into its accuracy and generalization capabilities.

Copy the following query and run it the same SQL console as the previous query in DB Explorer:

Code Snippet

1234567891011121314151617181920212223242526272829303132333435
DO (IN IN_MODELT TABLE ("ROW_INDEX" INT, "MODEL_CONTENT" NVARCHAR(5000)) 
=> "#PAL_AUTOML_MODEL_TBL")
BEGIN
	/*** Parameters *****/
    TRUNCATE TABLE "#PAL_PARAMETER_TBL";
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES ('THREAD_RATIO',null,0.8,null);

    /*** Table variables *****/
    lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
    lt_testdata = SELECT D.* FROM "TRANSACTIONSV" as D, "#PAL_PARTITION_RESULT_TBL" AS P  
    WHERE D.TRANSACTION_ID = P.TRANSACTION_ID and P.PARTITION_TYPE = 2;
    lt_model = SELECT * FROM :IN_MODELT;
   
   /*** PAL function call *****/
   CALL _SYS_AFL.PAL_PIPELINE_SCORE(:lt_testdata, :lt_model, :lt_parms, out_0, out_1, out_2, out_3);
  
   /*** Results *****/
  	IF EXISTS
  	(SELECT * FROM M_TEMPORARY_TABLES WHERE TABLE_NAME = '#PAL_PIPELINE_SCORE_RESULT_TBL' 
  	AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION) 
	      THEN DROP TABLE #PAL_PIPELINE_SCORE_RESULT_TBL;
	   END IF;
       CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_PIPELINE_SCORE_RESULT_TBL" 
       AS (SELECT * FROM :out_0);
    IF EXISTS(SELECT * FROM M_TEMPORARY_TABLES 
    WHERE TABLE_NAME = '#PAL_PIPELINE_SCORE_STATS_TBL' 
    AND SCHEMA_NAME = CURRENT_SCHEMA 
    AND CONNECTION_ID = CURRENT_CONNECTION) 
	      THEN DROP TABLE #PAL_PIPELINE_SCORE_STATS_TBL;
	   END IF;
       CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_PIPELINE_SCORE_STATS_TBL" 
       AS (SELECT * FROM :out_1);
END;
SELECT * FROM "#PAL_PIPELINE_SCORE_STATS_TBL";
SELECT * FROM "#PAL_PIPELINE_SCORE_RESULT_TBL";

Result Output:

Key Points

Step 6

The next SQL procedure uses a trained AutoML model to make predictions on a new dataset. Here’s a breakdown of its functionality:

(i) Parameter Initialization

Modifies the temporary table #PAL_PARAMETER_TBL to set the thread ratio to 0.8.

(ii) Data Preparation

Retrieves the prediction data from the TRANSACTIONSV and #PAL_PARTITION_RESULT_TBL tables, selecting only relevant columns.
Retrieves the trained AutoML model from the IN_MODELT table.

(iii) Model Prediction

Calls the PAL_PIPELINE_PREDICT procedure to make predictions using the trained model and prediction data.
Stores the prediction results and information in the out_0 and out_1 variables, respectively.

(iv) Result Output

Creates temporary tables to store the prediction results and information.

Overall, this procedure uses a trained AutoML model to make predictions on new data, providing insights into the model’s ability to generalize to unseen examples.

Execute the following query and run it the same SQL console as the previous query in DB Explorer:

Code Snippet

12345678910111213141516171819202122232425262728293031323334353637
DO (IN IN_MODELT TABLE ("ROW_INDEX" INT, "MODEL_CONTENT" NVARCHAR(5000)) 
=> "#PAL_AUTOML_MODEL_TBL")
BEGIN
/*** Parameters *****/
    TRUNCATE TABLE "#PAL_PARAMETER_TBL";
	INSERT INTO "#PAL_PARAMETER_TBL" VALUES ('THREAD_RATIO',null,0.8,null);

    /*** Table variables *****/
    lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
    lt_predictdata = SELECT D."TRANSACTION_ID", "ORIGIN", "CLASS", 
    "AMOUNT", "OLD_BALANCE_ORIGIN", "NEW_BALANCE_ORIGIN", "DESTINATION", 
    "OLD_BALANCE_DEST", "NEW_BALANCE_DEST"
               FROM "TRANSACTIONSV" as D,  "#PAL_PARTITION_RESULT_TBL" AS P  
               WHERE D.TRANSACTION_ID = P.TRANSACTION_ID and P.PARTITION_TYPE = 2;
    lt_model = SELECT * FROM :IN_MODELT;
   
   /*** PAL function call *****/
   CALL _SYS_AFL.PAL_PIPELINE_PREDICT(:lt_predictdata, :lt_model, :lt_parms, out_0, out_1);
   
    /*** Results *****/
  	IF EXISTS(SELECT * FROM M_TEMPORARY_TABLES 
  	WHERE TABLE_NAME = '#PAL_AUTOML_PREDICT_RESULT_TBL' 
  	AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION) 
	      THEN DROP TABLE "#PAL_AUTOML_PREDICT_RESULT_TBL";
	   END IF;
       CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_PREDICT_RESULT_TBL" 
       AS (SELECT * FROM :out_0);
    IF EXISTS(SELECT * FROM M_TEMPORARY_TABLES 
    WHERE TABLE_NAME = '#PAL_AUTOML_INFO_RESULT_TBL' 
    AND SCHEMA_NAME = CURRENT_SCHEMA AND CONNECTION_ID = CURRENT_CONNECTION) 
	      THEN DROP TABLE #PAL_AUTOML_INFO_RESULT_TBL;
	   END IF;
       CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_INFO_RESULT_TBL" 
       AS (SELECT * FROM :out_1);

END;
SELECT * FROM "#PAL_AUTOML_PREDICT_RESULT_TBL";

Result Output:

Key Points

Step 7

This last SQL procedure illustrates refitting a previously trained AutoML pipeline model based on new data or, in this case, on the entire training dataset. Instead of re-running the complete AutoML classification scenario task, which involves evaluating multiple algorithms and pipelines, this step only retrains the final and best AutoML pipeline model.

(i) Parameter Initialization

Retrieves the best pipeline from the #PAL_AUTOML_BEST_PIPELINE_TBL table and stores it as the PIPELINE parameter in the temporary table #PAL_PARAMETER_TBL.

(ii) Data Preparation

Retrieves the entire training data from the TRANSACTIONSV and #PAL_PARTITION_RESULT_TBL tables.

(iii) Pipeline Refitting

Calls the PAL_PIPELINE_FIT procedure to refit the pipeline on the entire training data using the specified parameters.
Stores the refitted model and pipeline information in the lt_model and lt_pinfo variables, respectively.

(iv) Result Output

Creates a temporary table #PAL_AUTOML_REFIT_MODEL_TBL to store the refitted model.

Overall, this procedure refits a previously trained AutoML pipeline on the entire training dataset, with the goal to improve performance.

Execute the following query and run it the same SQL console as the previous query in DB Explorer:

Code Snippet

12345678910111213141516171819202122232425
DO BEGIN
	/*** Parameter Initialization ***/
    truncate TABLE "#PAL_PARAMETER_TBL";
    INSERT INTO "#PAL_PARAMETER_TBL" 
    SELECT 'PIPELINE', NULL, NULL, PIPELINE FROM  #PAL_AUTOML_BEST_PIPELINE_TBL 
    WHERE ID = 0;

    /*** Data Preparation ****/
    lt_parms = SELECT * FROM "#PAL_PARAMETER_TBL";
    lt_fitdata = SELECT D.* FROM "TRANSACTIONSV" as D,  "#PAL_PARTITION_RESULT_TBL" AS P
     where D.TRANSACTION_ID = P.TRANSACTION_ID and P.PARTITION_TYPE = 1;

	/*** Pipeline Refitting ***/
	CALL _SYS_AFL.PAL_PIPELINE_FIT(:lt_fitdata, :lt_parms, lt_model, lt_pinfo);
    IF EXISTS(SELECT * FROM M_TEMPORARY_TABLES 
    WHERE TABLE_NAME = '#PAL_AUTOML_REFIT_MODEL_TBL' 
    AND SCHEMA_NAME = CURRENT_SCHEMA 
    AND CONNECTION_ID = CURRENT_CONNECTION) 
	    THEN DROP TABLE #PAL_AUTOML_REFIT_MODEL_TBL;
        END IF;
       CREATE LOCAL TEMPORARY COLUMN TABLE "#PAL_AUTOML_REFIT_MODEL_TBL" AS 
       (SELECT * FROM :lt_model);
      
END;
SELECT * FROM "#PAL_AUTOML_REFIT_MODEL_TBL";

Result Output:

Key Points

Congratulations! The analysis is now complete. By using the AutoML capabilities within SAP HANA Cloud, the fraudulent transactions hidden in the dataset are uncovered. The AutoML analysis identifies many records as fraudulent:

Code Snippet

12
SELECT COUNT(*) AS NO_OF_FRAUDULENT_TX FROM "#PAL_AUTOML_PREDICT_RESULT_TBL"
WHERE SCORES = '1';

One important note is that a model like this becomes more effective with more data. SAP HANA Cloud’s in-memory processing applies this approach to other data-intensive business problems such as demand forecasting, customer segmentation and pricing optimization. Additionally, the machine learning capabilities work with relational data as well as the multi-model engines within SAP HANA Cloud. Developers can also apply advanced processing features to spatial data, graph data, or even discover patterns within semi-structured data stored in the JSON document store.

Congratulations on completing this lesson on AutoML. SAP HANA Cloud’s machine learning capabilities can provide critical insight into hidden patterns within a dataset. When ready, click on the blue button below to experience additional features.

Next unit