native-queries.mdx

title	Native Queries
sidebarTitle	Native Queries

The underlying database engine of MindsDB is MySQL. However, you can run queries native to your database engine within MindsDB.

Connect your Database to MindsDB

To run queries native to your database, you must first connect your database to MindsDB using the CREATE DATABASE statement.

CREATE DATABASE example_db
WITH ENGINE = "postgres",
PARAMETERS = {
    "user": "demo_user",
    "password": "demo_password",
    "host": "samples.mindsdb.com",
    "port": "5432",
    "database": "demo"
};

Here we connect the example_db database, which is a PostgreSQL database.

Run Queries Native to your Database

Once we have our PostgreSQL database connected, we can run PostgreSQL-native queries.

Querying

To run PostgreSQL-native code, we must nest it within the SELECT statement like this:

SELECT * FROM example_db (
    SELECT 
        model, 
        year, 
        price, 
        transmission, 
        mileage, 
        fueltype, 
        mpg, -- miles per galon
        ROUND(CAST((mpg / 2.3521458) AS numeric), 1) AS kml, -- kilometers per liter
        (date_part('year', CURRENT_DATE)-year) AS years_old, -- age of a car
        COUNT(*) OVER (PARTITION BY model, year) AS units_to_sell, -- how many units of a certain model are sold in a year
        ROUND((CAST(tax AS decimal) / price), 3) AS tax_div_price -- value of tax divided by price of a car
    FROM demo_data.used_car_price
);

On execution, we get:

+-----+----+-----+------------+-------+--------+----+----+---------+-------------+-------------+
|model|year|price|transmission|mileage|fueltype|mpg |kml |years_old|units_to_sell|tax_div_price|
+-----+----+-----+------------+-------+--------+----+----+---------+-------------+-------------+
| A1  |2010|9990 |Automatic   |38000  |Petrol  |53.3|22.7|12       |1            |0.013        |
| A1  |2011|6995 |Manual      |65000  |Petrol  |53.3|22.7|11       |5            |0.018        |
| A1  |2011|6295 |Manual      |107000 |Petrol  |53.3|22.7|11       |5            |0.02         |
| A1  |2011|4250 |Manual      |116000 |Diesel  |70.6|30  |11       |5            |0.005        |
| A1  |2011|6475 |Manual      |45000  |Diesel  |70.6|30  |11       |5            |0            |
+-----+----+-----+------------+-------+--------+----+----+---------+-------------+-------------+

The first line (SELECT * FROM example_db) informs MindsDB that we select from a PostgreSQL database. After that, we nest a PostgreSQL code within brackets.

Creating Views

We can create a view based on a native query.

CREATE VIEW cars FROM example_db (
        SELECT 
            model, 
            year, 
            price, 
            transmission, 
            mileage, 
            fueltype, 
            mpg, -- miles per galon
            ROUND(CAST((mpg / 2.3521458) AS numeric), 1) AS kml, -- kilometers per liter
            (date_part('year', CURRENT_DATE)-year) AS years_old, -- age of a car
            COUNT(*) OVER (PARTITION BY model, year) AS units_to_sell, -- how many units of a certain model are sold in a year
            ROUND((CAST(tax AS decimal) / price), 3) AS tax_div_price -- value of tax divided by price of a car
        FROM demo_data.used_car_price
);

On execution, we get:

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