Summary
Linear regression models are a type of supervised learning algorithm that predict a continuous target variable by fitting a straight line to the input features. They are commonly used for predicting numeric outcomes based on one or more input variables. Advantages of linear regression include simplicity, interpretability, and fast computation time. However, disadvantages include the assumption of a linear relationship between variables, which may not always hold, and sensitivity to both outliers and collinear features, which can affect the model's performance and reported accuracy.
Note: If you are not sure that your data meet these requirements, you may want to first do some exploratory visualizations and transformations using scatter plots to visualize pairwise relationships. Altneratively, HeavyML makes it simple with to try first or compare a regression model with fewer assumptions, such as Random Forests.
Example
Linear Regression Options
With the exception of the general options listed above, the linear regression model type accepts no options.
Model Evaluation
Like all other regression model types, the model r2 can be obtained via the EVALUATE MODEL command. If the model was created with a specified EVAL_FRACTION, the model r2 score can be obtained on that test holdout set via the following:
If no EVAL_FRACTION was specified in the CREATE MODEL command, or if EVAL_FRACTION was specified but you wish to evaluate the model on a different dataset, you can specify the evaluation query explicitly as follows:
The relatively low R2 scores obtained for the linear regression model are not atypical for complex multi-variate relations. As noted above, in such cases, it will likely be worth trying a random forest or Gradient-Boosted Tree (GBT) regression model, as the accuracy of these models can be dramatically higher in many cases (in the example above, a simple random forest model achieved an R2 score above 0.87).
Model Prediction/Inference
Once a linear regression model is created, it can, like all other regression model types, be used for prediction via the row-wise ML_PREDICT operator, which takes the model name (in quotes) and a list of independent predictor variables semantically matching the ordered list of variables the model was trained on.
Related Methods
A list of predictors for a trained linear regression model, along with their associated coefficients, can be obtained by executing the linear_reg_coefs table function, as shown in the following example;
A simple, interpretable model
In general, we recommend starting with either Linear or Random Forests Regressions depending on data complexity. A single decision tree regression option is available for narrower use cases. One advantage of decision tree regression over random forest regression is its interpretability. A decision tree is an easy-to-understand model that can be easily visualized and interpreted. The tree structure allows one to see the sequence of decisions and criteria used to make predictions. This interpretability can be especially valuable in applications where the decision-making process needs to be transparent and understandable, such as in healthcare or finance.
Another advantage of decision tree regression is that it is computationally efficient compared to random forest regression. Because a decision tree is a single tree, it can be built and trained more quickly than an ensemble of trees in a random forest model. This efficiency can be important when dealing with real-time applications or large data sets where computational resources are limited.
Additionally, decision tree regression can be more appropriate for certain types of data sets or problems. For example, if the target variable has a strong linear relationship with a small subset of the features, a decision tree model might be able to capture this relationship more accurately than a random forest model. In such a case, a random forest model might overfit the data and produce less accurate predictions.
It is important to note, however, that decision tree regression models can also suffer from overfitting and may not perform as well as random forest models in many scenarios.
The model syntax follows general HeavyML conventions. You need to specify a SQL-legal model name, specify the type DECISION_TREE_REG, and provide a SELECT statement indicating the columns to use. The column projection statement must have the predicted variable first, then categorical columns and then continuous ones. Optionally, you can add a WITH statement to adjust categorical column processing using the same CAT_TOP_K and EVAL_FRACTION parameters discussed above.
This model trains in approximately 240ms. This is significantly faster than a full random forest regression on the same hardware which took several seconds. It produces a single tree model with r2 of 85% which is both useful and simple. While you would not expect this model to be as robust as random forests for application outside of its initial training domain, it might be acceptable or even preferred in terms of speed and explicability within its training domain.
To make a prediction using your decision tree model, use the ML_PREDICT function. This can be used on-the-fly, or as part of an UPDATE or CTAS if you want to persist your predictions. The general form is:
So in our example above, you'd use:
Note that we did not use the name of the variable to be predicted - that comes from the model itself.
Gradient boosting is a machine learning technique that combines weak learners, here decision trees, to create a strong predictor by iteratively minimizing the loss function. The main difference between random forests and gradient boosting lies in how the decision trees are created and aggregated. Unlike random forests, the decision trees in gradient boosting are built additively; in other words, each decision tree is built one after another.
Gradient boosting models have several advantages over random forest regression models:
Gradient boosting models can often achieve higher accuracy than random forests: Gradient boosting models are designed to minimize errors and can learn complex relationships between the target variable and predictors, which can lead to higher accuracy compared to random forest models.
Gradient boosting can handle missing data: Gradient boosting models can handle missing data by imputing missing values using the best split values during the tree-building process. Random forest models require imputation of missing data prior to training.
Gradient boosting is less prone to overfitting: Gradient boosting models are designed to reduce overfitting through techniques like early stopping and regularization. Random forest models are prone to overfitting, especially with noisy data or high-dimensional data.
However, gradient boosting models also have some disadvantages compared to random forest models, such as being more computationally expensive and having more hyperparameters to tune. The choice between gradient boosting and random forest models depends on the specific problem and data set, and should be determined through experimentation and cross-validation.
The model syntax follows HeavyML conventions. You need to specify a SQL-legal model name, specify the type GBT_REG, and provide a SELECT statement indicating the columns to use. The statement must have the predicted variable first, then categorical columns and then continuous ones. Optionally, you can add a WITH statement to adjust categorical column processing using the same CAT_TOP_K and EVAL_FRACTION parameters discussed above.
For our example dataset, the r2 obtained was about 5% less than random forests regression, while model building was faster. Your mileage may vary. In general, gradient boosted regression models may perform better than random forests regression in domains where the data has a high degree of complexity, nonlinearity, and noise, such as in image and speech recognition, natural language processing, and financial forecasting. Additionally, gradient boosted models may be more effective when there is a large number of features, and when the target variable is highly imbalanced.
To make a prediction using your gradient boosting tree model, use the ML_PREDICT function. This can be used on-the-fly, or as part of an UPDATE or CTAS if you want to persist your predictions. The general form is:
So in our example above, you'd use:
Note that we did not use the name of the variable to be predicted - that comes from the model itself.
Robust Predictive Modeling of Non-Linear Phenomena
Random forests regression is a machine learning technique used for regression tasks. In a random forest model, a large number of decision trees are constructed using randomly selected subsets of the training data and features. The individual trees are then combined to form a consensus prediction, which tends to be more accurate than any individual tree. This approach also helps to reduce overfitting, a common problem in machine learning where a model is too closely tailored to the training data and performs poorly on new data.
Compared to linear regression, which is a simple and interpretable method for modeling linear relationships between variables, random forests are more flexible and can model nonlinear relationships between variables. Additionally, random forests can handle a large number of features and can identify important features for prediction. Overall, random forests are a powerful and accessible machine learning tool, even for users without prior background in machine learning.
The model syntax follows general HeavyML conventions. You need to specify a SQL-legal model name, specify the type RANDOM_FOREST_REG, and provide a SELECT statement indicating the columns to use. The column projection statement must have the predicted variable first, then categorical columns and then continuous ones. Optionally, you can add a WITH statement to adjust categorical column processing using the same CAT_TOP_K and EVAL_FRACTION parameters discussed above.
For example, to predict Florida real estate price given parcels data:
In this example,
SALEPRC1 is the sales price to be predicted.
PARUSEDESC is a categorical column describing Florida land use types such as single family versus condominium. CNTYNAME is a categorical column of county names. These two columns are specified first among the predictors because they are categorical.
Four continuous value columns are provided: ACRES, TOTLVGAREA, EFFYRBUILT and SALEYR1. These indicate the parcel size in acres, its total living area, effective year built (reset for major remodeling) and sale year.
There are 70 counties in Florida, and we want this to be a statewide model. County is always a potentially-significant variable in price predictions, so we set the CAT_TOP_K to 70, well above its default of 10. If we want to increase initial model creation speed, we could also keep the default initially while experimenting with variables used, however this would come at the risk of fitting the model only on the most-populated counties, whose price prediction properties presumably vary.
In addition to the general SQL modeling parameters demonstrated above, random forest regressions allow control of several other optional parameters. These are also provided at model creation as a comma separated list of parameter value options in the WITH statement. We recommend starting with the defaults, and then adjusting incrementally. For example, you could try increasing the number of trees and see if a higher number improves model accuracy without a large decrease in performance.
It is often useful to understand which specific features are most important in a prediction. While future enhancements may make the syntax for this even easier, a UDTF version of feature importance evaluation is available today.
The table function is called random_forest_reg_var_importance
It takes a single parameter, which is the random forests model name. Because it is a table function, it must be wrapped within a TABLE() expression in SQL when used within a FROM clause:
This returns a table with rows for each feature used in the model. Categorical features are broken into multiple rows, with one row per sub-feature. The importance score units are based on the leaf-level tree construction metric set by var_importance_metric_str. While the MDA_Scaled metric takes a bit longer to run, you may find its values to be more interpretable.
You can also CTAS the results of the function above:
If you create and update a new percent_importance column in the resulting table, you can normalize and sort the feature importance scores by the maximum importance. For example when we do this with the example parcel data above, we find the most important model features are total living area, parcel areas and effective year built.
To make a prediction using your model, use the ML_PREDICT function. This can be used on-the-fly, or as part of an UPDATE or CTAS if you want to persist your predictions. The general form is:
So in our example above, you'd use:
Note that we did not use the name of the variable to be predicted - that comes from the model itself.
The Florida real estate price prediction model above returns a reasonably-high r2 with simply the default random forest regression parameters. About 87% of the data variance can be explained with just the 6 variables above from the public parcels data available from the Florida Department of Revenue.
The model can be improved by adding auxiliary datasets, such as distance to the coastline, school district quality, or density of high-quality local amenities like parks and restaurants.
Another use case for random forest regression is in customer segmentation, for example predicting customer lifetime value (CLV) for a business. CLV is a measure of the total amount of money a customer is expected to spend on a business's products or services over their entire lifetime as a customer. By segmenting customers into groups with similar CLV predictions, a business can tailor marketing and customer retention strategies to maximize revenue and profitability.
In this use case, the random forest regression model would be trained on historical customer data, such as purchase history, demographic information, and website activity. The model would then be used to predict the CLV for new and existing customers. The model could also identify the most important factors that contribute to CLV, such as customer age, purchase frequency, and product categories.
The random forest model can handle large datasets and complex relationships between variables, making it well-suited for customer segmentation tasks. Additionally, the model can automatically select the most relevant features for prediction, which is especially useful when dealing with high-dimensional data. By using a random forest model for customer segmentation, a business can gain valuable insights into customer behavior and preferences, and make data-driven decisions to improve customer satisfaction and increase revenue.
| Parameter | Description | Default Value |
|---|
num_trees | The number of decision trees to include in the ensemble | 10 |
obs_per_tree_fraction | The proportion of observations (or samples) that are randomly sampled and used to train each decision tree in the forest. Valid range 0 to 1 | 1 |
max_tree_depth | Maximum tree depth. Zero value means unlimited depth. Can be any non-negative number. | 0 |
features_per_node | The number of features available for each node split in the decision trees. | square root of the total number of features in the dataset. |
impurity_threshold | Valid range >= 0 | 0 |
bootstrap | Boolean | True |
min_obs_per_leaf_node | Minimum number of observations in the leaf node. Can be any positive number. | 5 |
min_obs_per_split_node | The minimum number of observations required for a node to be split during the construction of a decision tree/ Valid range is any positive number. | 2 |
min_weight_fraction_in_leaf_node | The minimum fraction of the sum of instance weights required in a leaf node | 0.0 |
min_impurity_decrease_in_split_node | The minimum impurity decrease required to split an internal node | 0.0 |
max_leaf_nodes | Valid range >= 0 | 0 |
use_histogram | Use histograms to speed computations. Boolean | True |
var_importance_metric | Options: mean decrease in impurity ('MDI'), mean decrease in accuracy ('MDA'), or 'MDA_scaled' - the MDA raw value scaled by its standard deviation | MDI |
Overview of HeavyML regression algorithms
HEAVY.AI supports four algorithms: linear regression, random forest regression, gradient boosted tree (GBT) regression, and decision tree regression. Creation and training of the models can be accomplished both via a CREATE MODEL statement as well as via invocation of dedicated table functions. Inference using the models can be accomplished via a row-wise ML_PREDICT operator, or via dedicated table functions.
Creating a regression model is accomplished via the CREATE MODEL statement.
We will step through the various parts and options of the statement above, giving concrete examples along the way.
Like CREATE TABLE, CREATE MODEL allows for:
CREATE MODEL (without qualifiers): Creates a named model. If a model already exists by the same name, the statement will throw an error message to that effect.
CREATE MODEL IF NOT EXISTS will only create a model if no model already exists with the same name, except unlike #1, it will not throw an error if there is a naming collision.
CREATE OR REPLACE MODEL: If a model with the same name already exists, it will overwrite it. This supports rapid iteration on models, but there is no 'undo' so you may wish to combine this with puposeful model name versioning.
Other things to take note of when using CREATE MODEL include:
<model_name> must be a valid SQL identifier, i.e. my_model_123, just like a SQL table or column name.
The model type must be one of the four following values: LINEAR_REG, RANDOM_FOREST_REG, DECISION_TREE_REG, or GBT_REG.
<query_statement> must be a valid SQL query. Unlike general SQL, column order is significant here.
The first column should represent the variable you wish to predict (aka the independent variable). For currently supported regressions, this can be any continuous column type, including integers or floating columns.
One or more predictor (aka dependent) valiables can follow in arbitrary order with the following limits
Categorical predictor variables must currently be of type TEXT ENCODED DICT (i.e. unencoded text is not supported).
Continuous predictor variables should be of type integer or float.
In addition, note that all projected expressions (i.e. revenue * 2.0 ) that are not column references (i.e. revenue) must be aliased, i.e. revenue * 2.0 AS revenue2x
When training a model, HeavyML automatically ignores any rows that contain NULL values in any of the input predictor columns.
In some cases it could be of modeling advantage to impute values for NULLs in advance of regression. This is case-specific and thus not automated, but most simply mean and mode values can be substituted where appropriate using standard SQL UPDATE commands.
Because several of these techniques are sensitive to outliers, it is best practice to review data extremes before modeling, and especially to ensure that any sentinel values for "no data" are removed beforehand. For example, the presence of -999 values instead of nulls will invalidate linear regression results.
Other column types can be CAST or EXTRACTED to valid types within the <query_statement>
Datetimes and timestamp values can be extracted to either continuous or categorical values if needed:
extract(month from timestamp_col)
extract(epoch from timestamp_col)
Geo columns are not directly supported, but operators returning types above from a geo column or from columnar longitude and latitude work as expected
ST_AREA(polygon_column)
GeoToH3(longitude, latitude, scale)
Standard Options
Many of the options that can be specified in the WITH options list are model-dependent and will be detailed in the relevant section, however the following options apply to all model types
EVAL_FRACTION: (optionally aliased as DATA_SPLIT_EVAL_FRACTION): Specifies the proportion of the dataset to be withheld from the training data, and allows the EVALUATE MODEL command to be run on the evaluation set without explicit specification (see the EVALUATE MODEL section below for more details). Note that EVAL_FRACTION must be >= 0.0 and < 1.0. Default value is 0.0 (no evaluation hold-out set).
TRAIN_FRACTION: (optionally aliased as DATA_SPLIT_TRAIN_FRACTION): Specifies the proportion of the dataset to be used for training. The most common use case for specifying TRAIN_FRACTION is when training a model over a large amount of data, such that specifying a TRAIN_FRACTION of less than 1 will speed up training, at some cost in model accuracy. Note that TRAIN_FRACTION must be >= 0.0 and <= 1.0.
CAT_TOP_K: For models with categorical predictors, this option specifies the top-k number of attributes that will be one-hot encoded from each, based on the attribute's frequency of occurrence in the training dataset. Note that the default value for CAT_TOP_K is 10, so only the 10 most-frequent categorical values are considered in modeling unless this is adjusted.
For example, if CAT_TOP_K is set to 3, and a categorical predictor column of us_state has 20 rows for 'CA', 15 rows for 'TX', 12 rows for 'NY', 10 rows for 'WA', and 8 rows for 'FL", then one-hot encoded columns will be generated for 'CA', 'TX', and 'NY'.
This option works in combination with the CAT_MIN_FRACTION described immediately below. For a categorical attribute to be one-hot encoded, the attribute must also have a column frequency greater or equal to CAT_MIN_FRACTION.
CAT_MIN_FRACTION: For models with categorical predictors, this option specifies the minimum frequency an attribute must be represented in a categorical column to be one-hot encoded as a predictor.
It is computed based on the number of rows in the column with the attribute value divided by the total number of rows.
This option works in conjunction with CAT_TOP_K, such that for a categorical attribute to be one-hot encoded, it must be both among the top-k attributes for a column, as well as have a frequency of occurrence >= CAT_MIN_FRACTION.
The default value for CAT_MIN_FRACTION is 0.01, meaning that only categorical attributes that make up at least 1% of their input column will be one-hot encoded (assuming they are also among the top CAT_TOP_K attributes of that column in terms of frequency).
Currently registered model names can be accessed via the SHOW MODELS command. If the configuration flag --restrict-ml-model-metadata-to-superusers is set to false (the default), any user can execute this command, otherwise it is restricted to superusers only.
SHOW MODEL DETAILS
Metadata for currently registered models can be accessed via the SHOW MODEL DETAILS command.
If SHOW MODEL DETAILS is run without a list of table names, metadata for all registered models will be returned.
If SHOW MODEL DETAILS is executed with a list of one or more model names, then just the metadata for those model names will be returned.
If the configuration flag --restrict-ml-model-metadata-to-superusers is set to false (the default), any user can execute this command, otherwise it is restricted to superusers only.
Metadata for model features, including regression coefficients for linear regression models and feature importance scores for random forest regression models, can be displayed by executing SHOW MODEL FEATURE DETAILS <model_name>;
If you have superuser access, you can view relevant model metadata by querying the information_schema.ml_models table. The table schema is as follows:
The ml_models system table can be queried as follows:
Evaluating a trained model can be done with the EVALUATE MODEL statement, which returns the model's R-squared (or R2) score. R-squared is a goodness-of-fit measure for regression models. This statistic indicates the percentage of the variance in the dependent variable that the independent variables explain collectively.
In the future, other additional metrics, such as Mean Squared Error (MSE), and Mean Absolute Error (MAE) may also be returned. Note: Additional metrics can be computed today in SQL from the values returned by ML_PREDICT described below if desired.
If CREATE MODEL was executed with a specified EVAL_FRACTION, then EVALUATE MODEL can be run without a specified query (i.e. simply EVALUATE MODEL <model_name>), which will run the model on the specified proportion of the training dataset held out for evaluation (i.e. the "test set").
If EVAL_FRACTION was not specified in the CREATE MODEL statement, or if you wish to test the model's performance on a different evaluation set, a specific evaluation query can be provided. It is expected that the order of the columns should be <predicted_col>, <categorical predictor cols>, <continuous predictor cols> , and that the arguments specified should semantically match the variables the model was trained on.
For example, the following workflow shows training a random forest regression model to predict prices of Florida real estate parcels for single-family homes with a defined 20% test set, and then evaluating the model performance on that held-out test dataset, and then separately on a dataset of Georgia real estate parcels of single-family homes.
In this example, 88% of the variance in the Florida dataset can be explained by the model (based on 20% hold-out values). The same model applied entirely outside of its training domain explains 75% of the sale price variance in Georgia.
Model inference, or using the model to predict values, can be performed by using the ML_PREDICT operator, which can be run anywhere a normal SQL operator is expected (i.e. it is executed row-wise and is not a table function). Syntax is as follows:
Note the following:
The model_name is always the first argument to ML_PREDICT, and should be enclosed in single quotes.
The number of predictors must match the number of logical predictors (i.e. number of column expression inputs, not accounting for the splitting of categorical predictors into multiple one-hot encoded columns) that the model was trained on.
The predictors should be specified in the same order as that provided at model creation (with categorical predictors always coming first), otherwise you will receive erroneous results.
The variable to be predicted, which was the first column input to CREATE MODE, should never be provided to ML_PREDICT.
Example