Query a Table

Description

The SELECT statement fetches data from a table and predictions from a model.

Here we go over example of selecting data from tables of connected data sources. To learn how to select predictions from a model, visit this page.

Syntax

Simple SELECT FROM an integration

In this example, query contains only tables from one integration. This query will be executed on this integration database (where integration name will be cut from the table name).

SELECT location, max(sqft)
FROM example_db.demo_data.home_rentals 
GROUP BY location
LIMIT 5;

Raw SELECT FROM an integration

It is also possible to send native queries to integration that use syntax native to a given integration. It is useful when a query can not be parsed as SQL.

SELECT ... FROM integration_name ( native query goes here );

Here is an example of selecting from a Mongo integration using Mongo-QL syntax:

SELECT * FROM mongo (
 db.house_sales2.find().limit(1) 
);

Complex queries

1. Subselect on data from integration.

It can be useful in cases when integration engine doesn’t support some functions, for example, grouping, as shown below. In this case, all data from raw select are passed to MindsDB and then subselect performs operations on them inside MindsDB.

SELECT type, max(bedrooms), last(MA)
FROM mongo (
 db.house_sales2.find().limit(300) 
) GROUP BY 1

2. Unions

It is possible to use UNION and UNION ALL operators. It this case, every subselect from union will be fetched and merged to one result-set on MindsDB side.

 SELECT data.time as date, data.target
 FROM datasource.table_name as data

UNION ALL

 SELECT model.time as date, model.target as target
 FROM mindsdb.model as model 
  JOIN datasource.table_name as t
 WHERE t.time > LATEST AND t.group = 'value';