<jupyter_start><jupyter_text>hana-ml Tutorial - Auto ML**Author: <NAME> DB HANA Core CN**In this tutorial, we will show you how to use AutoML(AutomaticClassification/AutomaticRegression) in hana-ml to train classification/regression model with public datasets.  Import the Necessary Libraries and Functions<jupyter_code>from hana_ml import dataframe
from hana_ml.dataframe import ConnectionContext
from hana_ml.algorithms.pal.utility import DataSets, Settings
from hana_ml.algorithms.pal.partition import train_test_val_split
from hana_ml.algorithms.pal.auto_ml import AutomaticClassification, AutomaticRegression
from hana_ml.visualizers.automl_progress import PipelineProgressStatusMonitor
from hana_ml.visualizers.automl_report import BestPipelineReport
from hana_ml.visualizers.unified_report import UnifiedReport
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import time
import json
import uuid<jupyter_output><empty_output><jupyter_text>Create a connection to a SAP HANA instanceFirst, you need to create a connetion to a SAP HANA instance. In the following cell, we use a config file, config/e2edata.ini to control the connection parameters. In your case, please update the following url, port, user, pwd with your HANA instance information for setting up the connection.<jupyter_code># Please replace url, port, user, pwd with your HANA instance information
connection_context = ConnectionContext(url, port, user, pwd)<jupyter_output><empty_output><jupyter_text>AutomaticClassificationDiabetes dataset is originally from the National Institute of Diabetes and Digestive and Kidney Diseases. The objective is to predict based on diagnostic measurements whether a patient has diabetes.1. **PREGNANCIES**: Number of times pregnant2. **GLUCOSE**: Plasma glucose concentration a 2 hours in an oral glucose tolerance test3. **BLOODPRESSURE**: Diastolic blood pressure (mm Hg)4. **SKINTHICKNESS**: Triceps skin fold thickness (mm)5. **INSULIN**: 2-Hour serum insulin (mu U/ml)6. **BMI**: Body mass index (weight in kg/(height in m)^2)7. **PEDIGREE**: Diabetes pedigree function8. **AGE**: Age (years)9. **CLASS**: Class variable (0 or 1),  **target varaible**.In hana-ml, we provide a class called DataSets which contains several public datasets. You could use load_diabetes_data to load the diabetes dataset.**Load the dataset**<jupyter_code># Load the data
diabetes_dataset, _, _, _ = DataSets.load_diabetes_data(connection_context)

# number of rows and number of columns
print("Shape of diabetes datset: {}".format(diabetes_dataset.shape))

# columns
print(diabetes_dataset.columns)

# cast the label to be NVARCHAR
diabetes_dataset = diabetes_dataset.cast('CLASS', 'VARCHAR')

# types of each column
print(diabetes_dataset.dtypes())<jupyter_output><empty_output><jupyter_text>Dataset report<jupyter_code># Generate a Dataset Report
UnifiedReport(diabetes_dataset).build().display()<jupyter_output><empty_output><jupyter_text>Split the dataset<jupyter_code># Split the dataset into a training and a test dataset
df_diabetes_train, df_diabetes_test, _ = train_test_val_split(data=diabetes_dataset, 
                                                              random_seed=2,
                                                              training_percentage=0.8,
                                                              testing_percentage=0.2,
                                                              validation_percentage=0,
                                                              id_column='ID',
                                                              partition_method='stratified',
                                                              stratified_column='CLASS')
print("Number of training samples: {}".format(df_diabetes_train.count()))
print("Number of test samples: {}".format(df_diabetes_test.count()))

# delete label column in the test dataset
df_diabetes_test = df_diabetes_test.deselect('CLASS')
# Look at the first three row of data
print(df_diabetes_train.head(3).collect())
print(df_diabetes_test.head(3).collect())<jupyter_output><empty_output><jupyter_text>Invoke AutomaticClassificationWhen you invoke AutomaticClassification, please use enable_workload_class() to manage workload in your SAP HANA instance. More detail could be see in the SAP help portal:https://help.sap.com/viewer/afa922439b204e9caf22c78b6b69e4f2/2.10.0.0/en-US/4499964b5ace432a80c572cc434240ab.htmlIn this example, we have configured a Workload Class in the SAP HANA database called "PAL_AUTOML_WORKLOAD".<jupyter_code># AutomaticClassification init 
progress_id = "automl_{}".format(uuid.uuid1())
auto_c = AutomaticClassification(generations=2, 
                                 population_size=5,
                                 offspring_size=5, 
                                 progress_indicator_id=progress_id)

# enable_workload_class
auto_c.enable_workload_class(workload_class_name="PAL_AUTOML_WORKLOAD")

# invoke a PipelineProgressStatusMonitor
progress_status_monitor = PipelineProgressStatusMonitor(connection_context=dataframe.ConnectionContext(url, port, user, pwd), 
                                                        automatic_obj=auto_c)

progress_status_monitor.start()

# training
try:
    auto_c.fit(data=df_diabetes_train)
except Exception as e:
    raise e<jupyter_output><empty_output><jupyter_text>Best pipeline plot<jupyter_code>BestPipelineReport(auto_c).generate_notebook_iframe()<jupyter_output><empty_output><jupyter_text>Make prediction<jupyter_code>res = auto_c.predict(df_diabetes_test)
print(res.collect())<jupyter_output><empty_output><jupyter_text>Use the existing pipeline to fit and predict<jupyter_code># The best pipeline after training
auto_c.best_pipeline_.collect().iat[0, 1]
pipeline = auto_c.best_pipeline_.collect().iat[0, 1]

auto_c.fit(df_diabetes_train, pipeline=pipeline)

res = auto_c.predict(df_diabetes_test)
print(res.collect())<jupyter_output><empty_output><jupyter_text>AutomaticRegression<jupyter_code># Load Dataset
bike_dataset = DataSets.load_bike_data(connection_context)

# number of rows and number of columns
print("Shape of datset: {}".format(bike_dataset.shape))

# columns
print(bike_dataset.columns)

# types of each column
print(bike_dataset.dtypes())

# print the first 3 rows of dataset
print(bike_dataset.head(3).collect())<jupyter_output><empty_output><jupyter_text>Dataset report<jupyter_code># Generate a Dataset Report
UnifiedReport(bike_dataset).build().display()<jupyter_output><empty_output><jupyter_text>Split the dataset<jupyter_code># Add a ID column for AutomaticRegression, the last column is the label
bike_dataset = bike_dataset.add_id('ID', ref_col='days_since_2011')

# Split the dataset into training and test dataset
cols = bike_dataset.columns
cols.remove('cnt')
bike_data = bike_dataset[cols + ['cnt']]

bike_train = bike_data.filter('ID <= 600')
bike_test = bike_data.filter('ID > 600')
print(bike_train.head(3).collect())
print(bike_test.head(3).collect())<jupyter_output><empty_output><jupyter_text>Invoke AutomaticRegression<jupyter_code># AutomaticRegression init 
progress_id = "automl_reg_{}".format(uuid.uuid1())
auto_r = AutomaticRegression(generations=2,
                             population_size=5,
                             offspring_size=5,                             
                             progress_indicator_id=progress_id)

# enable_workload_class
auto_r.enable_workload_class(workload_class_name="PAL_AUTOML_WORKLOAD")

# invoke a PipelineProgressStatusMonitor
progress_status_monitor = PipelineProgressStatusMonitor(connection_context=dataframe.ConnectionContext(url, port, user, pwd), 
                                                        automatic_obj=auto_r)

progress_status_monitor.start()
try:
    auto_r.fit(bike_train)
except Exception as e:
    raise e<jupyter_output><empty_output><jupyter_text>Best pipeline plot<jupyter_code>BestPipelineReport(auto_r).generate_notebook_iframe()<jupyter_output><empty_output><jupyter_text>Make prediction<jupyter_code>res = auto_r.predict(bike_test.deselect('cnt'))
print(res.collect())<jupyter_output><empty_output><jupyter_text>Use the existing pipeline to fit and predict<jupyter_code>print(auto_r.best_pipeline_.collect().iat[0, 1])
pipeline = auto_r.best_pipeline_.collect().iat[0, 1]

auto_r.fit(bike_train, pipeline=pipeline)

res = auto_r.predict(bike_test.deselect('cnt'))
print(res.collect())<jupyter_output><empty_output><jupyter_text>Close the connection<jupyter_code>connection_context.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08725616166335709, 'fraction_numerical': 0.011564069618035275, 'mean_word_length': 4.439644218551461, 'pattern_counts': {'":': 0, '<': 71, '<?xml version=': 0, '>': 71, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 21, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '523171', 'license': 'Apache-2.0', 'n_tokens_mistral': 2619, 'n_tokens_neox': 2511, 'n_words': 581, 'path': 'Python-API/pal/notebooks/tutorials/.ipynb_checkpoints/hana-ml_Tutorial_Auto_ML-checkpoint.ipynb', 'repo_name': 'xinchen510/hana-ml-samples', 'size': '8561'}