<jupyter_start><jupyter_text>Hybrid Gradient Boosting Trees  Example via Unified ClassificationA data set that identifies whether or not a pentient has diabetes is used to demonstrate the use of hybrid graident boosting classifier in SAP HANA.  Pima Indians Diabetes DatasetOriginal data comes from National Institute of Diabetes and Digestive and Kidney Diseases. The collected dataset is aiming at, based on certain diagnostic measurements, diagnostically predicting whether or not a patient has diabetes. In particular, patients contained in the dataset are females of Pima Indian heritage, all above the age of 20. Dataset is form Kaggle, for tutorials use only.The dataset contains the following diagnositic attributes:$\rhd$ "PREGNANCIES" - Number of times pregnant,$\rhd$ "GLUCOSE" - Plasma glucose concentration a 2 hours in an oral glucose tolerance test,$\rhd$ "BLOODPRESSURE" -  Diastolic blood pressure (mm Hg),$\rhd$ "SKINTHICKNESS" -  Triceps skin fold thickness (mm),$\rhd$ "INSULIN" - 2-Hour serum insulin (mu U/ml),$\rhd$ "BMI" - Body mass index $(\text{weight in kg})/(\text{height in m})^2$,$\rhd$ "PEDIGREE" - Diabetes pedigree function,$\rhd$ "AGE" -  Age (years),$\rhd$ "CLASS" - Class variable (0 or 1) 268 of 768 are 1(diabetes), the others are 0(non-diabetes).<jupyter_code>import hana_ml
from hana_ml import dataframe
from hana_ml.algorithms.pal import metrics
from hana_ml.algorithms.pal.unified_classification import UnifiedClassification<jupyter_output><empty_output><jupyter_text>Load DataThe data is loaded into 3 tables - full set, training-validation set, and test set as follows: PIMA_INDIANS_DIABETES_TBL PIMA_INDIANS_DIABETES_TRAIN_VALID_TBL PIMA_INDIANS_DIABETES_TEST_TBLTo do that, a connection is created and passed to the loader.There is a config file, config/e2edata.ini that controls the connection parameters and whether or not to reload the data from scratch. In case the data is already loaded, there would be no need to load the data. A sample section is below. If the config parameter, reload_data is true then the tables for test, training and validation are (re-)created and data inserted into them.[hana]url=host.sjc.sap.corpuser=usernamepasswd=<PASSWORD>port=3xx15<jupyter_code>from data_load_utils import DataSets, Settings
import plotting_utils
url, port, user, pwd = Settings.load_config("../../config/e2edata.ini")
connection_context = dataframe.ConnectionContext(url, port, user, pwd)
full_tbl, train_tbl, test_tbl, _ = DataSets.load_diabetes_data(connection_context)
from hana_ml.algorithms.pal.utility import Settings

Settings.set_log_level()<jupyter_output><empty_output><jupyter_text>Create Data FramesCreate the data frames for the full, test, training, and validation sets.Let us also do some data exploration.  Define Datasets - training set and testing setData frames are used keep references to data so computation on large data sets in HANA can happen in HANA. Trying to bring the entire data set into the client will likely result in out of memory exceptions.<jupyter_code>diabetes_train = connection_context.table(train_tbl)
#diabetes_train = diabetes_train.cast('CLASS', 'VARCHAR(10)')
diabetes_test = connection_context.table(test_tbl)
#diabetes_test = diabetes_test.cast('CLASS', 'VARCHAR(10)')<jupyter_output><empty_output><jupyter_text>Simple ExplorationLet us look at the number of rows in each dataset:<jupyter_code>print('Number of rows in training set: {}'.format(diabetes_train.count()))
print('Number of rows in testing set: {}'.format(diabetes_test.count()))<jupyter_output>Number of rows in training set: 614
Number of rows in testing set: 76<jupyter_text>Let us look at columns of the dataset:<jupyter_code>print(diabetes_train.columns)<jupyter_output>['ID', 'PREGNANCIES', 'GLUCOSE', 'BLOODPRESSURE', 'SKINTHICKNESS', 'INSULIN', 'BMI', 'PEDIGREE', 'AGE', 'CLASS']<jupyter_text>Let us also look some (in this example, the top 6) rows of the dataset:<jupyter_code>diabetes_train.head(6).collect()<jupyter_output><empty_output><jupyter_text>We can also check the data type of all columns:<jupyter_code>diabetes_train.dtypes()<jupyter_output><empty_output><jupyter_text>We have a 'CLASS' column in the dataset, let us check how many classes are contained in this dataset:<jupyter_code>diabetes_train.distinct('CLASS').collect()<jupyter_output><empty_output><jupyter_text>Two classes are available, assuring that this is a binary classification problem.   Model Creation & Model SelectionThe lines below show the ease with which classification can be done.Set up the label column, use default feature set and create the model:<jupyter_code>cv_values = {}
cv_values['learning_rate'] = [0.1, 0.4, 0.7, 1.0]
cv_values['n_estimators'] = [4, 6, 8, 10]
cv_values['split_threshold'] = [0.1, 0.4, 0.7, 1.0]
hgc = UnifiedClassification(func='HybridGradientBoostingTree',
                            param_search_strategy='grid',
                            resampling_method='cv',
                            evaluation_metric='error_rate',
                            ref_metric=['auc'],
                            fold_num=5,
                            random_state=1,
                            param_values=cv_values)
hgc.fit(diabetes_train, key='ID', label='CLASS',
        partition_method='stratified',
        partition_random_state=1,
        stratified_column='CLASS')
from hana_ml.model_storage import ModelStorage
#from hana_ml.model_storage_services import ModelSavingServices


# Creates an object called model_storage which must use the same connection with the model
model_storage = ModelStorage(connection_context=connection_context)
model_storage.clean_up()
# Saves the model for the first time
hgc.name = 'Model A'  # The model name is mandatory
hgc.version = 1
model_storage.save_model(model=hgc)

# Lists models
model_storage.list_models()
# plan the schedule

model_storage.set_schedule(name='Model A',
                           version=1,
                           schedule_time='every 3 minutes',
                           connection_userkey='raymondyao',
                           init_params={"func" : 'HybridGradientBoostingTree',
                                        "param_search_strategy" : 'grid',
                                        "resampling_method" : 'cv',
                                        "evaluation_metric" : 'error_rate',
                                        "ref_metric" : ['auc'],
                                        "fold_num" : 5,
                                        "random_state" : 1,
                                        "param_values" : {'learning_rate': [0.1, 0.4, 0.7, 1],
                                                          'n_estimators': [4, 6, 8, 10],
                                                          'split_threshold': [0.1, 0.4, 0.7, 1]}},
                           fit_params={"key" : 'ID',
                                       "label" : 'CLASS',
                                       "partition_method" : 'stratified',
                                       "partition_random_state" : 1,
                                       "stratified_column" : 'CLASS'},
                           training_dataset_select_statement="SELECT * FROM DIABETES_TRAIN"
                          )
model_storage.start_schedule('Model A', 1)
model_storage.list_models().iat[0, 7]
model_storage.terminate_schedule('Model A', 1)
model_storage.list_models().iat[0, 7]
from hana_ml.algorithms.pal.model_selection import GridSearchCV
from hana_ml.algorithms.pal.model_selection import RandomSearchCV
hgc2 = UnifiedClassification('HybridGradientBoostingTree')

gscv = GridSearchCV(estimator=hgc2, 
                    param_grid={'learning_rate': [0.1, 0.4, 0.7, 1],
                                'n_estimators': [4, 6, 8, 10],
                                'split_threshold': [0.1, 0.4, 0.7, 1]},
                    train_control=dict(fold_num=5,
                                       resampling_method='cv',
                                       random_state=1,
                                       ref_metric=['auc']),
                    scoring='error_rate')
gscv.fit(data=diabetes_train, key= 'ID',
         label='CLASS',
         partition_method='stratified',
         partition_random_state=1,
         stratified_column='CLASS',
         build_report=True)<jupyter_output>INFO:hana_ml.ml_base:Executing SQL: DO
BEGIN
DECLARE param_name VARCHAR(5000) ARRAY;
DECLARE int_value INTEGER ARRAY;
DECLARE double_value DOUBLE ARRAY;
DECLARE string_value VARCHAR(5000) ARRAY;
param_name[1] := N'FUNCTION';
int_value[1] := NULL;
double_value[1] := NULL;
string_value[1] := N'HGBT';
param_name[2] := N'KEY';
int_value[2] := 1;
double_value[2] := NULL;
string_value[2] := NULL;
param_name[3] := N'PARTITION_METHOD';
int_value[3] := 2;
double_value[3] := NULL;
string_value[3] := NULL;
param_name[4] := N'PARTITION_RANDOM_SEED';
int_value[4] := 1;
double_value[4] := NULL;
string_value[4] := NULL;
param_name[5] := N'PARTITION_STRATIFIED_VARIABLE';
int_value[5] := NULL;
double_value[5] := NULL;
string_value[5] := N'CLASS';
param_name[6] := N'PARAM_SEARCH_STRATEGY';
int_value[6] := NULL;
double_value[6] := NULL;
string_value[6] := N'grid';
param_name[7] := N'EVALUATION_METRIC';
int_value[7] := NULL;
double_value[7] := NULL;
string_value[7] := N'ERROR_RATE';
param_name[8] := N'LEA[...]<jupyter_text>EvaluationLet us compare cross-validation accuracy and test accuracy:<jupyter_code>cm = hgc.confusion_matrix_.collect()
cm
gscv.estimator.confusion_matrix_.collect()
train_accuracy = float(cm['COUNT'][cm['ACTUAL_CLASS']==cm['PREDICTED_CLASS']].sum())/cm['COUNT'].sum()
train_accuracy
features = diabetes_train.columns
features.remove('CLASS')
features.remove('ID')
print(features)
pred_res = hgc.predict(diabetes_test, key='ID', features=features)
pred_res.head(10).collect()
pred_res.dtypes()
ts = diabetes_test.rename_columns({'ID': 'TID'}) .cast('CLASS', 'NVARCHAR(256)')
jsql = '{}."{}"={}."{}"'.format(pred_res.quoted_name, 'ID', ts.quoted_name, 'TID')
results_df = pred_res.join(ts, jsql, how='inner')
cm_df, classification_report_df = metrics.confusion_matrix(results_df, key='ID', label_true='CLASS', label_pred='SCORE') 
import matplotlib.pyplot as plt
from hana_ml.visualizers.metrics import MetricsVisualizer
f, ax1 = plt.subplots(1,1)
mv1 = MetricsVisualizer(ax1)
ax1 = mv1.plot_confusion_matrix(cm_df, normalize=False)
print("Recall, Precision and F_measures.")
classification_report_df.collect()
_,_,_,metrics_res = hgc.score(data=diabetes_test, key='ID', label='CLASS')
metrics_res.collect()
from hana_ml.model_storage import ModelStorage

model_storage = ModelStorage(connection_context=connection_context)
gscv.estimator.name = 'HGBT' 
gscv.estimator.version = 1
model_storage.save_model(model=gscv.estimator)
# Lists models
model_storage.list_models()
from hana_ml.visualizers.unified_report import UnifiedReport

mymodel = model_storage.load_model('HGBT', 1)
mymodel.fit(data=diabetes_train, key= 'ID',
            label='CLASS',
            partition_method='stratified',
            partition_random_state=1,
            stratified_column='CLASS',
            build_report=True)
UnifiedReport(mymodel).display()
model_storage.delete_model('HGBT', 1)
connection_context.close()<jupyter_output>INFO:hana_ml.ml_base:Executing SQL: SELECT 1 FROM "PAL_TEST"."HANAML_MODEL_STORAGE" LIMIT 1
INFO:hana_ml.ml_base:Executing SQL: SELECT 1 FROM "PAL_TEST"."HANAML_MODEL_STORAGE" LIMIT 1
INFO:hana_ml.ml_base:Executing SQL: SELECT 1 FROM "PAL_TEST"."HANAML_MODEL_STORAGE" LIMIT 1
INFO:hana_ml.ml_base:Executing SQL: SELECT 1 FROM "PAL_TEST"."HANAML_MODEL_STORAGE" LIMIT 1
INFO:hana_ml.ml_base:Executing SQL: DROP TABLE "PAL_TEST"."HANAML_HGBT_1_CLASS_HANA_ML_ALGORITHMS_PAL_UNIFIED_CLASSIFICATION_UNIFIEDCLASSIFICATION_MODELS_0"
INFO:hana_ml.ml_base:Executing SQL: DROP TABLE "PAL_TEST"."HANAML_HGBT_1_CLASS_HANA_ML_ALGORITHMS_PAL_UNIFIED_CLASSIFICATION_UNIFIEDCLASSIFICATION_MODELS_1"
INFO:hana_ml.ml_base:Executing SQL: DROP TABLE "PAL_TEST"."HANAML_HGBT_1_CLASS_HANA_ML_ALGORITHMS_PAL_UNIFIED_CLASSIFICATION_UNIFIEDCLASSIFICATION_MODELS_2"
INFO:hana_ml.ml_base:Executing SQL: DROP TABLE "PAL_TEST"."HANAML_HGBT_1_CLASS_HANA_ML_ALGORITHMS_PAL_UNIFIED_CLASSIFICATION_UNIFIEDCLASSIFICATION_MODELS_3"
INFO[...]
{'fraction_non_alphanumeric': 0.10525030525030525, 'fraction_numerical': 0.014896214896214897, 'mean_word_length': 3.8446372239747633, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '47900', 'license': 'Apache-2.0', 'n_tokens_mistral': 4066, 'n_tokens_neox': 3868, 'n_words': 915, 'path': 'Python-API/pal/notebooks/DiabetesUnifiedClassificationHGBT.ipynb', 'repo_name': 'SAP-samples/hana-ml-samples', 'size': '12287'}