Develop and leverage AI models in ClickHouse®

Learn how to train AI models and perform live scoring with a set of SQL statements and Aiven for ClickHouse®

ClickHouse® is a well known analytical database, but one of its less known feature is the ability to train AI models and perform live scoring with a set of SQL statements. In this tutorial we'll showcase how, starting from a taxi dataset, we can train a Logistic Regression model and use it for predicting the future cost of a ride.

Prerequisites

An Aiven account. Sign up for a free trial today
Docker to interact with the ClickHouse® service to upload the data and query it

Start with Aiven for ClickHouse

We can create an ClickHouse service with the following process:

Access the Aiven Console
Create an Aiven for ClickHouse® service and specify:
- The cloud provider and region
- The service plan, defining the size of the service. We can use the free tier for the purpose of this tutorial
- The additional storage disk size
- The service name
Click on Create

Wait a couple of minutes until the service is in RUNNING state.

Load the data

For this tutorial, we'll use a dataset containing New York taxi trip data from Kaggle. Navigate to the Kaggle website, download the file and unzip it. The folder contains a set of files with the naming pattern yellow_tripdata_YYYY-MM.csv. Each one includes a month of taxi rides in the years 2019 and 2020.

To load it in Aiven for ClickHouse, connect to the database with the clickhouse-client Docker image:

Loading code...

You can find the USERNAME, PASSWORD, HOST and PORT information in the Aiven Console, under the Aiven for ClickHouse service overview page in the ClickHouse Native tab.

Create a taxi_trips_train table for our training dataset:

Loading code...

Create another table, named taxi_trips_test for our testing dataset:

Loading code...

Then exit from the docker container:

Loading code...

Next, we need to clean our data source a little. Remove the Windows end of line symbols from yellow_tripdata_2019-01.csv, representing our training dataset, with:

Loading code...

Then load the data from the yellow_tripdata_2019-01-polished.csv file to the taxi_trips_train table with:

Loading code...

Load the yellow_tripdata_2019-02.csv data in the taxi_trips_test, this represents our testing dataset

Loading code...

Train a stochastic linear regression model

Once the data is uploaded, we can focus on the main task of training an AI model. Checking the dataset, we can notice the presence of a column called fare_amount representing the fare as calculated by the meter based on time and distance. In the next section we'll build a model to predict the fare_amount based on other columns in the dataset and we'll use a stochastic linear regression model built in ClickHouse to achieve it.

To train the model, connect to ClickHouse again:

Loading code...

Then execute the following SQL to train the stochastic linear regression model:

Loading code...

The above statement trains a stochastic linear regression model with the following parameters:

taxi_trips_model as name
0.001 as learning rate
0.1 as l2 regularization coefficient
15 as mini-batch size
Adam as method for updating weights
fare_amount as target column
passenger_count, trip_distance, RatecodeID, PULocationID, and DOLocationID as training parameters

Test the stochastic linear regression model

After building the model, let's test it against our taxi_trips_test dataset. We can compare the actual fare_amount in our test dataset with the prediction from the taxi_trips_model with:

Loading code...

The above SQL:

Retrieves the state from the taxi_trips_model model
Includes the row number with the rowNumberInAllBlocks() function
Selects the fare_amount from the test dataset
includes the prediction from the model using the evalMLMethod function, passing the same columns used for training, but coming from the test dataset

The results showcases predictions not too far from the actual data

Loading code...

Measure the stochastic linear regression model performance

How does the model perform? In the AI/ML field there are a set of different KPIs to understand the quality of a model. We are going to use the mean absolute error (MAE) in this example. The MAE calculates the average difference between the prediction and the actual value in the testing dataset. We can calculate the overall MAE with:

Loading code...

The above query calculates:

The MAE as the average of the absolute difference between the actual total_amount and the prediction
The average total_amount
The weight of the MAE on the total_amount

The results say that our model is off on 2.03 dollars per trip, the error is around 15.97%. Not bad for a single SQL query.

Loading code...

Improve the model with feature engineering in ClickHouse

Can we improve the performance? Yes with some feature engineering! Feature engineering is the practice of creating a new set of parameters for the model to be more accurate. You can derive these parameters from existing parameters, enriched with external sources, or aggregated from various fields. All the above actions are native to ClickHouse that allows us to explore the data and reshape it as needed.

First step is to check the data quality; let's investigate the presence of outliers in the fare_amount column by bucketing the data in 10$ buckets:

Loading code...

Notice that there are a lot of trips with negative fare_amount as well as amounts greater than 500$. This is probably mistakes in the data collection and outliers we might want to take into account when creating the model.

Let's create a new model that:

Excludes the outliers identified above with cost greater than 500$
Predicts the absolute value of fare_amount avoiding the negative numbers
Includes, as additional parameters:
- The pickup hour
- The pickup day of the week
- The time from pickup to dropoff in buckets of 10 minutes

First, drop the existing taxi_trips_model_enhanced table:

Loading code...

Then re-create it with our new model:

Loading code...

The pickup hour and day of the week are added to the new taxi_trips_model_enhanced model by the toDayOfWeek(tpep_pickup_datetime) and toHour(tpep_pickup_datetime) columns. The calculation age('minute', tpep_pickup_datetime, tpep_dropoff_datetime)/10 provides the time in minutes between pickup and dropoff in buckets of 10 minutes.

We can test the new model with:

Loading code...

The result shows an improvement, compared to the previous model, of more than 1%.

Loading code...

You can build in further improvements to the model parameters to raise its precision. This tutorial demonstrates how, with just SQL statements, you can train and test a stochastic linear regression model all within ClickHouse.

Conclusion

Adding artificial intelligence in your application doesn't always require external tools. This removes the security barriers you set up in your database and exposing you to the risk of accidental data access or usage of stale data. Modern databases like ClickHouse exposes interesting functionality that allows you to implement model training and prediction directly where your data resides, enabling you to keep your data secure and performant.

Table of contents

Prerequisites
Start with Aiven for ClickHouse
Load the data
Train a stochastic linear regression model
Test the stochastic linear regression model
Measure the stochastic linear regression model performance
Improve the model with feature engineering in ClickHouse
Conclusion