Nixtla's NeuralForecast Integration with MindsDB

Nixtla’s NeuralForecast provides a diverse array of neural forecasting models, prioritizing their ease of use and resilience. These models encompass a spectrum of options, including traditional networks like MLP and RNNs, as well as cutting-edge innovations such as NBEATS, NHITS, TFT, and various other architectural approaches.

You can learn more about its features here.

​

How to bring NeuralForecast Models to MindsDB

Before creating a model, you will need to create an ML engine for NeuralForecast using the CREATE ML_ENGINE statement:

CREATE ML_ENGINE neuralforecast
FROM neuralforecast;

Once the ML engine is created, we use the CREATE MODEL statement to create the NeuralForecast model in MindsDB.

CREATE MODEL model_name
FROM data_source
  (SELECT * FROM table_name)
PREDICT column_to_be_predicted
GROUP BY column_name, column_name, ...
ORDER BY date_column
WINDOW 12 -- model looks back at sets of 12 rows each
HORIZON 3 -- model forecasts the next 3 rows
USING
    engine = 'neuralforecast'
    frequency = 'Q',
    train_time = 0.01,
    exogenous_vars = ['var_1', 'var_2'];

To ensure that the model is created based on the NeuralForecast engine, include the USING clause at the end.

The frequency parameter informs the model about the expected time difference between each measurement (supported values here). And the train_time parameter defines the training time - it defaults to 1, and lower values will reduce training time linearly by reducing the number of searches allowed for the best configuration by AutoNHITS. You can also define exogenous_vars as a parameter in the USING clause - these are complementary variables in the table that may improve forecast accuracy.

​

Example

Let’s go through an example of how to use Nixtla’s NeuralForecast with MindsDB to forecast monthly expenditures based on historical data.

Please note that before using the NeuralForecast engine, you should create it from the MindsDB editor, or other clients through which you interact with MindsDB, with the below command:

CREATE ML_ENGINE neuralforecast
FROM neuralforecast;

You can check the available engines with this command:

SHOW ML_ENGINES;

If you see the NeuralForecast engine on the list, you are ready to follow the tutorials.

We use a table from our MySQL public demo database, so let’s start by connecting MindsDB to it:

CREATE DATABASE mysql_demo_db
WITH ENGINE = 'mysql',
PARAMETERS = {
    "user": "user",
    "password": "MindsDBUser123!",
    "host": "samples.mindsdb.com",
    "port": "3306",
    "database": "public"
};

Now that we’ve connected our database to MindsDB, let’s query the data to be used in the example:

SELECT *
FROM mysql_demo_db.historical_expenditures
LIMIT 3;

Here is the output:

+------------+----------+-------------+
| month      | category | expenditure |
+------------+----------+-------------+
| 1982-04-01 | clothing | 359.9       |
| 1982-05-01 | clothing | 386.6       |
| 1982-06-01 | clothing | 350.5       |
+------------+----------+-------------+

The historical_expenditures table stores monthly expenditure data for various categories, such as food, clothing, industry, and more.

Let’s create a model table to predict the expenditures:

CREATE MODEL quarterly_expenditure_forecaster
FROM mysql_demo_db
  (SELECT * FROM historical_expenditures)
PREDICT expenditure
GROUP BY category
ORDER BY month
WINDOW 12
HORIZON 3
USING ENGINE = 'neuralforecast';

The CREATE MODEL statement creates, trains, and deploys the model. Here, we predict the expenditure column values. As it is a time series model, we order the data by the month column. Additionally, we group data by the category column - the predictions are made for each group independently (here, for each category).

Next, we define the WINDOW and HORIZON clauses. The WINDOW clause specifies the number of rows we look back at (here, we look back at sets of 12 rows). And the HORIZON clause defines for how many rows the predictions are made (here, for the next 3 rows).

The ENGINE parameter in the USING clause specifies the ML engine used to make predictions.

We can check the training status with the following query:

DESCRIBE quarterly_expenditure_forecaster;

Once the model status is complete, the behavior is the same as with any other AI table – you can query for batch predictions by joining it with a data table:

SELECT m.month as month, m.expenditure as forecasted
FROM mindsdb.quarterly_expenditure_forecaster as m
JOIN mysql_demo_db.historical_expenditures as t
WHERE t.month > LATEST
AND t.category = 'clothing';

Here is the output data:

+----------------------------+------------------+
| month                      | forecasted       |
+----------------------------+------------------+
| 2017-10-01 00:00:00.000000 | 10802.2109375    |
| 2017-11-01 00:00:00.000000 | 10749.2041015625 |
| 2017-12-01 00:00:00.000000 | 12423.849609375  |
+----------------------------+------------------+

The historical_expenditures table is used to make batch predictions. Upon joining the quarterly_expenditure_forecaster model with the historical_expenditures table, we get predictions for the next quarter as defined by the HORIZON 3 clause.

Please note that the output month column contains both the date and timestamp. This format is used by default, as the timestamp is required when dealing with the hourly frequency of data.

MindsDB provides the LATEST keyword that marks the latest training data point. In the WHERE clause, we specify the month > LATEST condition to ensure the predictions are made for data after the latest training data point.

Let’s consider our quarterly_expenditure_forecaster model. We train the model using data until the third quarter of 2017, and the predictions come for the fourth quarter of 2017 (as defined by HORIZON 3).

​

NeuralForecast + HierarchicalForecast

The NeuralForecast handler also supports hierarchical reconciliation via Nixtla’s HierarchicalForecast package. Hierarchical reconciliation may improve prediction accuracy when the data has a hierarchical structure.

In this example, there may be a hierarchy as total expenditure is comprised of 7 different categories.

SELECT DISTINCT category
FROM mysql_demo_db.historical_expenditures;

Here are the available categories:

+-------------------+
| category          |
+-------------------+
| food              |
| household_goods   |
| clothing          |
| department_stores |
| other             |
| cafes             |
| industry          |
+-------------------+

Spending in each category may be related over time. For example, if spending on food rises in October 2017, it may be more likely that spending on cafes also rises in October 2017. Hierarchical reconciliation can account for this shared information.

Here is how we can create a model:

CREATE MODEL hierarchical_expenditure_forecaster
FROM mysql_demo_db
  (SELECT * FROM historical_expenditures)
PREDICT expenditure
GROUP BY category
ORDER BY month
HORIZON 3
USING
  ENGINE = 'neuralforecast',
  HIERARCHY = [‘category’];

Predictions with this model account for the hierarchical structure. The output may differ from the default model, which does not assume any hierarchy.