Description

The SELECT statement fetches predictions from the model table. The data is returned on the fly and the result set is not persisted.

But there are ways to save predictions data! You can save your predictions as a view using the CREATE VIEW statement. Please note that a view is a saved query and does not store data like a table. Another way is to create a table using the CREATE TABLE statement or insert your predictions into an existing table using the INSERT INTO statement.

Syntax

Here is the syntax for making predictions in bulk by joining the data source table with the model table:

SELECT m.target_name, t.column_name, ...
FROM integration_name.table_name AS t
JOIN mindsdb.predictor_name AS m;

On execution, we get:

+----------------------+-------------+
| target_name          | column_name |
+----------------------+-------------+
| predicted_value1     | value1      |
| predicted_value2     | value2      |
| predicted_value3     | value3      |
| predicted_value4     | value4      |
| predicted_value5     | value5      |
+----------------------+-------------+

Where:

NameDescription
m.target_nameName of the column to be predicted. The m. in front indicates that this column comes from the mindsdb.predictor_name table.
t.column_name, ...Columns from the data source table (integration_name.table_name) that you want to see in the output.
integration_name.table_nameData source table that is joined with the model table (mindsdb.predictor_name).
predictor_nameName of the model used to make predictions.

Please note that in the case of bulk predictions, we do not pass the data values for which the prediction is made. It is because bulk predictions use all data available in the data source table.

Example

Let’s make bulk predictions to predict the rental_price value using the home_rentals_model model joined with the data source table.

SELECT t.sqft, t.location, t.neighborhood, t.days_on_market, t.rental_price AS real_price,
       m.rental_price AS predicted_rental_price
FROM example_db.demo_data.home_rentals AS t
JOIN mindsdb.home_rentals_model AS m
LIMIT 5;

On execution, we get:

+-------+----------+-----------------+----------------+--------------+-----------------------------+
| sqft  | location | neighborhood    | days_on_market | real_price   | predicted_rental_price      |
+-------+----------+-----------------+----------------+--------------+-----------------------------+
| 917   | great    | berkeley_hills  | 13             | 3901         | 3886                        |
| 194   | great    | berkeley_hills  | 10             | 2042         | 2007                        |
| 543   | poor     | westbrae        | 18             | 1871         | 1865                        |
| 503   | good     | downtown        | 10             | 3026         | 3020                        |
| 1066  | good     | thowsand_oaks   | 13             | 4774         | 4748                        |
+-------+----------+-----------------+----------------+--------------+-----------------------------+