Manipulate Data in Tables, Views, and Files
Create a Table
Description
The CREATE TABLE
statement creates a table and optionally fills it with data from provided query. It may be used to materialize prediction results as tables.
Syntax
You can use the CREATE TABLE
statement to create an empty table:
CREATE TABLE integration_name.table_name (
column_name data_type,
...
);
You can use the CREATE TABLE
statement to create a table and fill it with data:
CREATE TABLE integration_name.table_name
(SELECT ...);
Or the CREATE OR REPLACE TABLE
statement:
CREATE OR REPLACE TABLE integration_name.table_name
(SELECT ...);
Note that the integration_name
connection must be created with the CREATE DATABASE
statement and the user with write access.
Here are the steps followed by the syntax:
- It executes a subselect query to get the output data.
- In the case of the
CREATE OR REPLACE TABLE
statement, theintegration_name.table_name
table is dropped before recreating it. - It (re)creates the
integration_name.table_name
table inside theintegration_name
integration. - It uses the
INSERT INTO
statement to insert the output of the(SELECT ...)
query into theintegration_name.table_name
.
On execution, we get:
Query OK, 0 rows affected (x.xxx sec)
Example
We want to save the prediction results into the int1.tbl1
table.
Here is the schema structure used throughout this example:
int1
└── tbl1
mindsdb
└── predictor_name
int2
└── tbl2
Where:
Name | Description |
---|---|
int1 | Integration where the table that stores prediction results resides. |
tbl1 | Table that stores prediction results. |
predictor_name | Name of the model. |
int2 | Integration where the data source table used in the inner SELECT statement resides. |
tbl2 | Data source table used in the inner SELECT statement. |
Let’s execute the query.
CREATE OR REPLACE TABLE int1.tbl1 (
SELECT *
FROM int2.tbl2 AS ta
JOIN mindsdb.predictor_name AS tb
WHERE ta.date > '2015-12-31'
);
On execution, we get:
Query OK, 0 rows affected (x.xxx sec)