Create a View

​

Description

The CREATE VIEW statement creates a view, which is a great way to do data preparation in MindsDB. A VIEW is a saved SELECT statement, which is executed every time we call this view.

​

Syntax

Here is the syntax:

CREATE VIEW [IF NOT EXISTS] project_name.view_name AS (
    SELECT a.column_name, ...,
           p.model_column AS model_column
    FROM integration_name.table_name AS a
    JOIN mindsdb.predictor_name AS p
);

On execution, we get:

Query OK, 0 rows affected (x.xxx sec)

Where:

​

Example

Below is the query that creates and trains the home_rentals_model model to predict the rental_price value. The inner SELECT statement provides all real estate listing data used to train the model.

CREATE MODEL mindsdb.home_rentals_model
FROM integration
    (SELECT * FROM house_rentals_data)
PREDICT rental_price;

On execution, we get:

Query OK, 0 rows affected (x.xxx sec)

Now, we can JOIN the home_rentals_data table with the home_rentals_model model to make predictions. By creating a view (using the CREATE VIEW statement) that is based on the SELECT statement joining the data and model tables, we create an AI Table.

Here, the SELECT statement joins the data source table and the model table. The input data for making predictions consists of the sqft, number_of_bathrooms, and location columns. These are joined with the rental_price column that stores predicted values.

CREATE VIEW mindsdb.home_rentals_predictions AS (
    SELECT
        a.sqft,
        a.number_of_bathrooms,
        a.location,
        p.rental_price AS price
    FROM integration.home_rentals_data AS a
    JOIN mindsdb.home_rentals_model AS p
);

On execution, we get:

Query OK, 0 rows affected (x.xxx sec)

CREATE MODEL mindsdb.data_model
FROM integration
    (SELECT * FROM old_data)
PREDICT column;

CREATE VIEW mindsdb.data_predictions AS (
    SELECT
        a.column1,
        a.column2,
        a.column3,
        p.column AS predicted_column
    FROM integration.new_data AS a
    JOIN mindsdb.data_model AS p
);

​

USING VIEW

Examples to use view

1. Complex select on view (it is grouping in this example).

SELECT type, last(bedrooms) 
FROM mindsdb.house_v
GROUP BY 1

2. Creating predictor from view

CREATE MODEL house_sales_model
FROM mindsdb (
  SELECT * FROM house_v
) PREDICT ma
ORDER BY saledate
GROUP BY bedrooms, type
WINDOW 1 HORIZON 4

3. Using predictor with view

SELECT * FROM mindsdb.house_v
JOIN mindsdb.house_sales_model
WHERE house_v.saledate > latest