CREATE VIEW Statement

Description

In MindsDB, an AI Table is a virtual table based on the result-set of the SQL Statement that JOINs a table data with the predictions of a model. An AI Table can be created using the CREATE VIEW mindsdb.[ai_table_name] statement.

Syntax

CREATE VIEW mindsdb.[ai_table_name] as (
    SELECT
        a.[column_name1],
        a.[column_name2],
        a.[column_name3],
        p.[model_column] as model_column
    FROM [integration_name].[table_name] as a
    JOIN mindsdb.[predictor_name] as p
);

Where:

Expressions	Description
[ai_table_name]	Name of the view to be created
[column_name1], [column_name2] ...	Name of the columns to be joined, input for the model to make a prediction
[model_column]	name of the target column for the predictions
[integration_name].[table_name]	where integration_name is the linked database and has it's table_name
[predictor_name]	Name of the model to be used to generate the predictions

Example

We will use the Home Rentals dataset to create an AI Table.

number_of_rooms	number_of_bathrooms	sqft	location	days_on_market	initial_price	neighborhood	rental_price
0	1	484	great	10	2271	south_side	2271
1	1	674	good	1	2167	downtown	2167

The first step is to execute a SQL query for creating a home_rentals_model that learns to predict the rental_price value given other features of a real estate listing:

CREATE PREDICTOR mindsdb.home_rentals_model
FROM integration_name (SELECT * FROM house_rentals_data) as rentals
PREDICT rental_price as price;

Once trained, we can JOIN any input data with the trained model and store the results as an AI Table.

Let's pass some of the expected input columns (in this case, sqft, number_of_bathrooms, location) to the model and join the predicted rental_price values:

CREATE VIEW mindsdb.home_rentals as (
    SELECT
        a.sqft,
        a.number_of_bathrooms,
        a.location,
        p.rental_price as price
    FROM mysql_db.home_rentals as a
    JOIN mindsdb.home_rentals_model as p
);

Note that in this example, we pass part of the same data that was used to train as a test query, but usually you would create an AI table to store predictions for new data.