MindsDB

https://docs.mindsdb.com/mindsdb
Admin
MindsDB is an AI data solution that enables humans, AI, agents, and applications to get highly...
Tokens:64,275
Snippets:581
Trust Score:10
Update:8 months ago
Show doc for...
Tokens:
Raw
### MariaDB SkySQL Data Source Example (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/connect-mariadb-skysql

An example SQL query demonstrating how to connect MindsDB to a MariaDB SkySQL service. This includes specific host, port, database, user, and password details, along with the necessary SSL certificate URL.

```sql
CREATE DATABASE skysql_datasource
WITH ENGINE = 'MariaDB',
PARAMETERS = {
  "host": "mindsdbtest.mdb0002956.db1.skysql.net",
  "port": 5001,
  "database": "mindsdb_data",
  "user": "DB00007539",
  "password": "password",
  -- here, the SSL certificate is required
  "ssl-ca": {
    "url": "https://mindsdb-web-builds.s3.amazonaws.com/aws_skysql_chain.pem"
  }
};
```

--------------------------------

### SQL Example for Batch Predictions

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/get-batch-predictions

An example demonstrating how to make bulk predictions using a SQL SELECT statement. This example joins a data source table ('home_rentals') with a MindsDB model ('home_rentals_model') to predict rental prices. The output shows original data alongside predicted values.

```sql
SELECT t.sqft, t.location, t.neighborhood, t.days_on_market, t.rental_price AS real_price,
       m.rental_price AS predicted_rental_price
FROM example_db.demo_data.home_rentals AS t
JOIN mindsdb.home_rentals_model AS m
LIMIT 5;
```

--------------------------------

### Start Ngrok Tunnel for TCP

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/database

This command initiates an Ngrok TCP tunnel, exposing a specified local port to the internet. It requires the Ngrok executable to be installed and configured with an auth token. The output provides a public forwarding address that can be used to access the local service.

```bash
ngrok tcp port-number
```

--------------------------------

### Example Requirements File with Packages

Source: https://docs.mindsdb.com/mindsdb/_sql/functions/custom_functions

An example of a requirements.txt file listing common Python packages like pandas and scikit-learn. This shows how to specify dependencies for custom functions.

```text
pandas
scikit-learn
```

--------------------------------

### Example: Query Home Rentals File (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/api/select-files

This example demonstrates querying a specific file named 'home_rentals' uploaded to MindsDB. It uses a LIMIT clause to retrieve only the first 10 rows, showcasing how to interact with file data as if it were a database table.

```sql
SELECT *
FROM files.home_rentals
LIMIT 10;
```

--------------------------------

### Create ML Engine (OpenAI Example)

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/ml-engine

This section demonstrates how to create a new Machine Learning engine using the OpenAI handler, providing the necessary API key.

```APIDOC
## CREATE ML_ENGINE my_openai_engine

### Description
Creates a new Machine Learning engine named 'my_openai_engine' using the 'openai' handler. The 'USING' clause specifies the OpenAI API key required for authentication.

### Method
SQL

### Endpoint
N/A (SQL Command)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
CREATE ML_ENGINE my_openai_engine
FROM openai
USING
    openai_api_key = '<your opanai api key>';
```

### Response
#### Success Response (200)
- **message** (string) - Indicates successful creation of the ML engine.

#### Response Example
```sql
Query successfully completed
```
```

--------------------------------

### MariaDB SkySQL Data Source Creation Template (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/connect-mariadb-skysql

This SQL template outlines the parameters required to create a data source connection for MariaDB SkySQL within MindsDB. It includes essential connection details and optional SSL configurations.

```sql
CREATE DATABASE maria_datasource            -- display name for the database
WITH ENGINE = 'MariaDB',                      -- name of the MindsDB handler
PARAMETERS = {
  "host": " ",                              -- host IP address or URL
  "port": ,                                 -- port used to make TCP/IP connection
  "database": " ",                          -- database name
  "user": " ",                              -- database user
  "password": " ",                          -- database password
  "ssl": True/False,                        -- optional, the `ssl` parameter value indicates whether SSL is enabled (`True`) or disabled (`False`)
  "ssl_ca": {
    "path": " "                                 -- either "path" or "url"
  },
  "ssl_cert": {
    "url": " "                                  -- either "path" or "url"
  },
  "ssl_key": {
    "path": " "                                 -- either "path" or "url"
  }
};
```

--------------------------------

### SELECT FROM KB Syntax

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/query

This section details the syntax for querying knowledge bases in MindsDB. It includes an example query, sample output, and a breakdown of the columns returned.

```APIDOC
## SELECT * FROM KB Syntax

Knowledge bases provide an abstraction that enables users to see the stored data.

### Description
This endpoint allows you to retrieve data stored within your MindsDB knowledge bases. You can select all columns or specific ones to view the stored information, including chunk content, metadata, and relevance scores.

### Method
SELECT

### Endpoint
`FROM <knowledge_base_name>`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT * 
FROM my_kb;
```

### Response
#### Success Response (200)
Returns a table with the following columns:

* `id` (string) - The source data IDs.
* `chunk_id` (string) - Unique identifier for each chunk of data.
* `chunk_content` (string) - The actual content of the data chunk.
* `metadata` (object) - Contains general and custom metadata associated with the chunk.
* `product` (string) - The product associated with the data chunk (if applicable).
* `distance` (float) - The calculated distance between the chunk's content and the search phrase.
* `relevance` (float) - The calculated relevance of the chunk compared to the search phrase (0 to 1).

#### Response Example
```sql
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+--------------------+--------------------+---
| id  | chunk_id             | chunk_content           | metadata                                                                                                                                                                                            | product               | distance           | relevance          |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+--------------------+--------------------+---
| A1B | A1B_notes:1of1:0to20 | Request color: black    | {"chunk_index":0,"content_column":"notes","end_char":20,"original_doc_id":"A1B_notes","original_row_id":"A1B","product":"Wireless Mouse","source":"TextChunkingPreprocessor","start_char":0}        | Wireless Mouse        | 0.5743341242061104 | 0.5093188026135379 |
| Q7P | Q7P_notes:1of1:0to22 | Prefer aluminum finish  | {"chunk_index":0,"content_column":"notes","end_char":22,"original_doc_id":"Q7P_notes","original_row_id":"Q7P","product":"Aluminum Laptop Stand","source":"TextChunkingPreprocessor","start_char":0} | Aluminum Laptop Stand | 0.7744703514692067 | 0.2502580835880018 |
| 3XZ | 3XZ_notes:1of1:0to19 | Gift wrap requested     | {"chunk_index":0,"content_column":"notes","end_char":19,"original_doc_id":"3XZ_notes","original_row_id":"3XZ","product":"Bluetooth Speaker","source":"TextChunkingPreprocessor","start_char":0}     | Bluetooth Speaker     | 0.8010851611432231 | 0.2500003885558766 |
+-----+----------------------+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+--------------------+--------------------+---
```

### Relevance Calculation Notes
* When a ranking model is provided, the default `relevance` is >= 0, unless specified otherwise in the `WHERE` clause.
* If no reranking model is provided and `relevance` is not defined in the query, all rows matching similarity and metadata search are included.
* If no reranking model is provided but `relevance` is defined in the query, relevance is calculated based on the `distance` column (`1/(1+ distance)`), and the output is filtered by comparing this value with the defined `relevance`.
```

--------------------------------

### SQL Example Output for Batch Predictions

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/get-batch-predictions

The result set from the SQL batch predictions example. It displays a table with columns from the original data source and the predicted values from the MindsDB model, showing a comparison of real and predicted rental prices.

```sql
+-------+----------+-----------------+----------------+--------------+-----------------------------+
| sqft  | location | neighborhood    | days_on_market | real_price   | predicted_rental_price      |
+-------+----------+-----------------+----------------+--------------+-----------------------------+
| 917   | great    | berkeley_hills  | 13             | 3901         | 3886                        |
| 194   | great    | berkeley_hills  | 10             | 2042         | 2007                        |
| 543   | poor     | westbrae        | 18             | 1871         | 1865                        |
| 503   | good     | downtown        | 10             | 3026         | 3020                        |
| 1066  | good     | thowsand_oaks   | 13             | 4774         | 4748                        |
+-------+----------+-----------------+----------------+--------------+-----------------------------+
```

--------------------------------

### Example Regression Model Creation

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/model

An example of creating a regression model in MindsDB using data from a database, specifying the engine and a tag.

```sql
CREATE MODEL mindsdb.home_rentals_model
FROM example_db
  (SELECT * FROM demo_data.home_rentals)
PREDICT rental_price
USING engine = 'lightwood',
      tag = 'my home rentals model';
```

--------------------------------

### Start MindsDB with Specific APIs (Bash)

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/mysql-client

This command starts the MindsDB application with specified APIs enabled. It allows users to control which services, like http, mysql, or postgres, are active.

```bash
python -m mindsdb --api http,mysql,postgres
```

--------------------------------

### Create and Query Sample Financial Database

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/examples

This snippet demonstrates how to create a database connection to sample financial data and then select all records from the 'financial_headlines' table. It requires the MindsDB SQL engine and specific connection parameters.

```sql
CREATE DATABASE sample_data
WITH ENGINE = 'postgres',
PARAMETERS = {
    "user": "demo_user",
    "password": "demo_password",
    "host": "samples.mindsdb.com",
    "port": "5432",
    "database": "demo",
    "schema": "demo_data"
};

SELECT * FROM sample_data.financial_headlines;
```

--------------------------------

### Create and Query MindsDB Knowledge Base

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/examples

Demonstrates the SQL syntax for creating a knowledge base, inserting data, and performing queries. It requires specifying embedding and reranking models, metadata, content, and ID columns. Queries can filter by product, content, and relevance.

```sql
CREATE DATABASE sample_data
WITH ENGINE = 'postgres',
PARAMETERS = {
    "user": "demo_user",
    "password": "demo_password",
    "host": "samples.mindsdb.com",
    "port": "5432",
    "database": "demo",
    "schema": "demo_data"
};

SELECT * FROM sample_data.orders;
```

```sql
CREATE KNOWLEDGE_BASE my_kb
USING
    embedding_model = {
        "provider": "openai",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-abc123"
    },
    reranking_model = {
        "provider": "openai",
        "model_name": "gpt-4o",
        "api_key": "sk-abc123"
    },
    metadata_columns = ['product'],
    content_columns = ['notes'],
    id_column = 'order_id';
```

```sql
INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders;
```

```sql
SELECT *
FROM my_kb
WHERE product = 'Wireless Mouse'
AND content = 'color'
AND relevance > 0.5;
```

--------------------------------

### SQL Joining Three Tables Example

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/api/join-on

Shows how to join more than two tables sequentially. This example uses a RIGHT JOIN followed by a standard JOIN to combine data from owners, pets, and animals tables.

```sql
SELECT *
FROM files.owners o
RIGHT JOIN files.pets p ON o.pet_id = p.pet_id
JOIN files.animals a ON p.animal_id = a.animal_id;
```

--------------------------------

### Create a MySQL Data Source Connection (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/database

An example of connecting to a MySQL database using the `CREATE DATABASE` command. It includes the engine type ('mariadb') and specific parameters like user, port, password, host, and database name.

```sql
CREATE DATABASE mysql_datasource
WITH ENGINE = 'mariadb',
PARAMETERS = {
  "user": "root",
  "port": 3307,
  "password": "password",
  "host": "127.0.0.1",
  "database": "my_database"
};
```

--------------------------------

### Raw SELECT from Mongo Integration (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/api/select

An example of using a raw SELECT statement to query a Mongo integration using Mongo-QL syntax for specific data retrieval.

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

--------------------------------

### Insert Financial Data into Knowledge Base

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/examples

This statement inserts data from the 'sample_data.financial_headlines' table into the 'my_kb' knowledge base. It utilizes batch processing with a specified batch size and number of threads for efficient data ingestion. Ensure the 'my_kb' knowledge base is already created.

```sql
INSERT INTO my_kb
SELECT * 
FROM sample_data.financial_headlines
USING
    batch_size = 500,
    threads = 10;
```

--------------------------------

### Example: Using OpenRouter with LLM()

Source: https://docs.mindsdb.com/mindsdb/_sql/functions/llm_function

Demonstrates how to configure environment variables to use an OpenAI-compatible model provider like OpenRouter with the LLM() function. Requires setting the model name, base URL, and API key.

```bash
LLM_FUNCTION_MODEL_NAME = "mistralai/devstral-small-2505"
LLM_FUNCTION_BASE_URL = "https://openrouter.ai/api/v1"
OPENAI_API_KEY = "openrouter-api-key"
```

--------------------------------

### Start MindsDB without GUI (Bash)

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/mysql-client

This command initiates the MindsDB application without launching its graphical user interface. This is useful for running MindsDB in environments where a GUI is not needed or available.

```bash
python -m mindsdb --no_studio
```

--------------------------------

### Query Knowledge Base for Content (Default Limit)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/examples

This SQL query retrieves all records from the 'my_kb' knowledge base where the 'content' column matches 'investors'. By default, it returns a maximum of 10 rows. This is useful for initial exploration of the data.

```sql
SELECT *
FROM my_kb
WHERE content = 'investors';
```

--------------------------------

### Create Database Connection in MindsDB (Fails in Metabase)

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/metabase

Attempts to create a database connection in MindsDB using the CREATE DATABASE statement. This example demonstrates a common issue where curly braces used for JDBC escape sequences cause the command to fail within Metabase.

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

--------------------------------

### Create Knowledge Base for Financial Data

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/examples

This SQL code defines a knowledge base named 'my_kb' using specified OpenAI embedding and reranking models. It designates 'sentiment_labelled' as metadata and 'headline' as content columns for the knowledge base. Ensure you have valid OpenAI API keys.

```sql
CREATE KNOWLEDGE_BASE my_kb
USING
    embedding_model = {
        "provider": "openai",
        "model_name" : "text-embedding-3-large",
        "api_key": "sk-xxx"
    },
    reranking_model = {
        "provider": "openai",
        "model_name": "gpt-4o",
        "api_key": "sk-xxx"
    },
    metadata_columns = ['sentiment_labelled'],
    content_columns = ['headline'];
```

--------------------------------

### Create LLM Model with OpenAI Engine in MindsDB

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/model

Demonstrates creating a Large Language Model (LLM) using the OpenAI engine in MindsDB. This example sets up a sentiment classifier by providing a prompt template that instructs the model on how to analyze customer reviews.

```sql
CREATE MODEL sentiment_classifier
PREDICT sentiment
USING
  engine = 'openai_engine',
  prompt_template = 'analyze customer reviews and assign sentiment as positive or negative or neutral: {{review}}';
```

--------------------------------

### Manage Agents with SQL in MindsDB

Source: https://docs.mindsdb.com/mindsdb/-objects

Provides examples of SQL syntax for creating, selecting from, and dropping agents in MindsDB. It explains the rules for agent naming, including case sensitivity and the use of backticks for names with spaces or specific casing.

```sql
CREATE AGENT my_agent USING ...;   -- creates "my_agent"
CREATE AGENT My_Agent USING ...;   -- also creates "my_agent"
CREATE AGENT `my agent 1` USING ...;   -- creates "my agent 1"
CREATE AGENT `My agent 1` USING ...;   -- error
```

```sql
SELECT * FROM `My agent 1`;   -- selects from “My agent 1”
DROP AGENT `My agent 1`;   -- deletes “My agent 1”
```

--------------------------------

### Query Knowledge Base with Defined Limit

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/examples

This SQL query retrieves records from 'my_kb' where the 'content' is 'investors', explicitly setting the maximum number of returned rows to 20 using the LIMIT clause. This allows for retrieving a specific number of results.

```sql
SELECT *
FROM my_kb
WHERE content = 'investors'
LIMIT 20;
```

--------------------------------

### Create Time Series Model from File in MindsDB

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/model

Shows how to create a time series forecasting model using data from a file in MindsDB. This example predicts house sales, ordering by date, grouping by bedrooms, and using a specified window and horizon for forecasting.

```sql
CREATE MODEL mindsdb.house_sales_predictor
FROM files
  (SELECT * FROM house_sales)
PREDICT MA
ORDER BY saledate
GROUP BY bedrooms
-- the target column to be predicted stores one row per quarter
WINDOW 8      -- using data from the last two years to make forecasts (last 8 rows)
HORIZON 4;    -- making forecasts for the next year (next 4 rows)
```

--------------------------------

### Insert Data with Partitions and Threads (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

This SQL statement demonstrates how to insert data into a knowledge base with optimized performance using partitions and threads. It allows for parallel data insertion, tracking, cancellation, and resumption. Key parameters include batch_size, track_column, threads, and error handling.

```sql
INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders
USING
    batch_size = 200,
    track_column = order_id,
    threads = 10,
    error = 'skip';
```

--------------------------------

### Insert Data using Partitions

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

Optimizes data insertion into the knowledge base by setting up partitions and threads for parallel data batches. Enables tracking, canceling, and resuming the insertion process.

```APIDOC
## INSERT INTO

### Description
Inserts data into the knowledge base using specified partitions and threads for optimized performance. Allows for tracking, canceling, and resuming the insertion process.

### Method
INSERT INTO ... USING ...

### Endpoint
N/A (SQL Statement)

### Parameters
#### Query Parameters
- **batch_size** (integer) - Optional - Defines the number of rows fetched per iteration. Defaults to 1000.
- **threads** (integer or boolean) - Optional - Defines threads for running partitions. Can be a number (e.g., `10`), `true` to enable, or `false` to disable. If ML task queue is enabled, threads are used automatically.
- **track_column** (string) - Optional - Defines the column used for sorting data before partitioning.
- **error** (string) - Optional - Defines error processing options. Available values: `raise` (default) or `skip`.

### Request Example
```sql
INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders
USING
    batch_size = 200,
    track_column = order_id,
    threads = 10,
    error = 'skip';
```

### Response
#### Success Response (200)
Data insertion process initiated. Progress can be tracked via `information_schema.queries`.

#### Response Example
N/A (SQL Statement Execution)
```

```APIDOC
## Track Data Insertion Progress

### Description
Queries the `information_schema.queries` table to view the progress of data insertion processes.

### Method
SELECT

### Endpoint
N/A (SQL Statement)

### Parameters
#### Query Parameters
- **table** (string) - Required - `information_schema.queries`

### Request Example
```sql
SELECT * FROM information_schema.queries;
```

### Response
#### Success Response (200)
Returns a table with details of ongoing and completed queries, including process IDs.

#### Response Example
```json
{
  "query_id": 1,
  "query": "INSERT INTO my_kb ...",
  "status": "running",
  "progress": 0.5
}
```
```

```APIDOC
## Cancel Data Insertion

### Description
Cancels an ongoing data insertion process using its process ID.

### Method
SELECT query_cancel(integer)

### Endpoint
N/A (SQL Function Call)

### Parameters
#### Path Parameters
- **process_id** (integer) - Required - The ID of the process to cancel, obtained from `information_schema.queries`.

### Request Example
```sql
SELECT query_cancel(1);
```

### Response
#### Success Response (200)
Indicates that the cancellation request has been processed. Note that already inserted data is not removed.

#### Response Example
```json
{
  "query_cancel": true
}
```
```

```APIDOC
## Resume Data Insertion

### Description
Resumes a previously canceled or paused data insertion process using its process ID.

### Method
SELECT query_resume(integer)

### Endpoint
N/A (SQL Function Call)

### Parameters
#### Path Parameters
- **process_id** (integer) - Required - The ID of the process to resume, obtained from `information_schema.queries`.

### Request Example
```sql
SELECT query_resume(1);
```

### Response
#### Success Response (200)
Indicates that the resume request has been processed.

#### Response Example
```json
{
  "query_resume": true
}
```
```

--------------------------------

### List Projects with SHOW DATABASES (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/list-projects

The `SHOW DATABASES` command lists all available data sources and projects. The `WHERE` clause can be used to filter projects by type. The `FULL` keyword provides additional information.

```sql
SHOW DATABASES
WHERE type = 'project';
```

```sql
SHOW FULL DATABASES
WHERE type = 'project';
```

--------------------------------

### Install and Load Jupysql Extension in Jupyter

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/jupysql

Installs the jupysql and pymysql libraries and loads the SQL extension in Jupyter Notebook. This is a prerequisite for using SQL magics with MindsDB.

```python
pip install jupysql
pip install pymysql

%load_ext sql

import pymysql
```

--------------------------------

### Create Index on Knowledge Base (PGVector)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

Creates an index on the knowledge base's underlying vector store to accelerate search operations. This feature is supported only when PGVector is used as the storage backend, as ChromaDB provides indexing by default. Note that indexing may reduce insert operation speed.

```sql
CREATE INDEX ON KNOWLEDGE_BASE my_kb;
```

--------------------------------

### Optional CREATE DATABASE Syntax Variations (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/database

Demonstrates equivalent `CREATE DATABASE` commands in SQL where optional keywords and default engine settings result in the same outcome. This highlights the flexibility of the MindsDB SQL API.

```sql
CREATE DATABASE db;
CREATE DATABASE db ENGINE 'mindsdb';
CREATE DATABASE db ENGINE = 'mindsdb';
CREATE DATABASE db WITH ENGINE 'mindsdb';
CREATE DATABASE db USING ENGINE = 'mindsdb';
```

--------------------------------

### Get a Single Prediction

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/get-single-prediction

Fetches predictions from a model table on the fly. Results are not persisted by default but can be saved using CREATE VIEW, CREATE TABLE, or INSERT INTO statements.

```APIDOC
## GET /api/predictions

### Description
Fetches predictions from a model table on the fly. The result set is not persisted by default. Predictions can be saved using `CREATE VIEW`, `CREATE TABLE`, or `INSERT INTO` statements.

### Method
GET

### Endpoint
`/api/predictions`

### Parameters
#### Query Parameters
- **target_name** (string) - Required - The name of the column to be predicted.
- **predictor_name** (string) - Required - The name of the model used to make the prediction.
- **column_name** (string) - Required - The name of the column in the WHERE clause.
- **value** (string) - Required - The value to filter by in the WHERE clause.

### Request Example
```sql
SELECT target_name, target_name_explain
FROM mindsdb.predictor_name
WHERE column_name = value 
AND column_name = value;
```

### Response
#### Success Response (200)
- **target_name** (string) - Name of the column to be predicted.
- **target_name_explain** (object) - JSON object containing prediction details like `predicted_value`, `confidence`, `anomaly`, `truth`, `confidence_lower_bound`, and `confidence_upper_bound`.

#### Response Example
```json
{
  "target_name": "predicted_value",
  "target_name_explain": {
    "predicted_value": 4394,
    "confidence": 0.99,
    "anomaly": null,
    "truth": null,
    "confidence_lower_bound": 4313,
    "confidence_upper_bound": 4475
  }
}
```
```

--------------------------------

### Example Python Function for Adding Integers

Source: https://docs.mindsdb.com/mindsdb/_sql/functions/custom_functions

A simple example of a custom Python function that adds two integers. This demonstrates the basic syntax and type hinting for function implementation in MindsDB.

```python
def add_integers(a:int, b:int) -> int:
    return a+b
```

--------------------------------

### Example: Save Prediction Results to a Table in MindsDB SQL

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/table

This example demonstrates how to use the CREATE OR REPLACE TABLE statement to save the results of a prediction query into a persistent table. It joins data from a source table with prediction results from a MindsDB predictor.

```sql
CREATE OR REPLACE TABLE int1.tbl1 (
    SELECT *
    FROM int2.tbl2 AS ta
    JOIN mindsdb.predictor_name AS tb
    WHERE ta.date > '2015-12-31'
);
```

--------------------------------

### SQL Common Table Expressions (CTEs) Example

Source: https://docs.mindsdb.com/mindsdb/_sql/sql_support/cte

Demonstrates the use of Common Table Expressions (CTEs) in SQL for MindsDB. CTEs define temporary, named result sets that can be referenced within a single query, improving readability and modularity. This example shows how to define two CTEs and then join their results.

```sql
WITH table_name1 AS (
    
    SELECT columns
    FROM table1 t1
    JOIN table2 t2
    ON t1.col = t2.col
),

table_name2 AS (
    
    SELECT columns
    FROM table1 t1
    JOIN table2 t2
    ON t1.col = t2.col
)

SELECT columns
FROM table_name1 t1
JOIN table_name2 t2
ON t1.col - t2.col;
```

--------------------------------

### Get Job History API

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/jobs

API endpoint to retrieve the execution history of a specific job.

```APIDOC
## SELECT FROM jobs_history

### Description
Retrieves the execution history for a specific job.

### Method
GET

### Endpoint
/api/jobs/history

### Parameters
#### Query Parameters
- **project** (string) - Required - The project name of the job.
- **name** (string) - Required - The name of the job.

### Response
#### Success Response (200 OK)
- **history** (array of objects) - A list of job execution records.
  - **job_name** (string) - The name of the job.
  - **project** (string) - The project the job belongs to.
  - **status** (string) - The execution status (e.g., 'completed', 'failed').
  - **start_time** (string) - The start time of the execution.
  - **end_time** (string) - The end time of the execution.
  - **error** (string) - Any error message if the execution failed.

#### Response Example
```json
{
  "history": [
    {
      "job_name": "daily_report_job",
      "project": "mindsdb",
      "status": "completed",
      "start_time": "2023-10-27 00:00:00",
      "end_time": "2023-10-27 00:00:05",
      "error": null
    }
  ]
}
```
```

--------------------------------

### View Data Insertion Progress (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

This SQL query allows users to monitor the progress of data insertion into the knowledge base by querying the `information_schema.queries` table. It provides details about ongoing and completed query operations.

```sql
SELECT * FROM information_schema.queries;
```

--------------------------------

### Configure Text Chunking Parameters (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

This SQL statement demonstrates how to configure text chunking parameters when creating a knowledge base. It allows users to define `chunk_size` and `chunk_overlap` to optimize data storage and search. `chunk_size` specifies the number of characters per chunk, and `chunk_overlap` defines the overlapping characters between consecutive chunks.

```sql
CREATE KNOWLEDGE_BASE my_kb
USING
    ...
    preprocessing = {
        "text_chunking_config" : {
            "chunk_size": 2000,
            "chunk_overlap": 200
        }
    },
    ...;
```

--------------------------------

### Insert Data into Knowledge Base (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

Inserts data into a knowledge base by selecting from another table. The data is embedded into vectors before being stored in a vector database. This is the standard method for populating a knowledge base.

```sql
INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders;
```

--------------------------------

### Allowed Characters for MindsDB Object Names

Source: https://docs.mindsdb.com/mindsdb/-objects

Shows an example of creating a MindsDB agent using allowed characters, which include lowercase letters, numbers, and underscores.

```sql
CREATE AGENT my_agent345 (...);   -- creates "my_agent345"
```

--------------------------------

### Skip Existing Items During Insertion (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

Improves insertion performance by skipping existing items using the `kb_skip_existing` flag. This method avoids embedding calculations for content that already exists, making it more efficient than a full upsert when only new data needs to be added.

```sql
INSERT INTO my_kb
SELECT *
FROM table_name
USING kb_skip_existing = true;
```

--------------------------------

### Available Providers and Models

Source: https://docs.mindsdb.com/mindsdb/_sql/agents/agent_syntax

Lists the supported model providers and their respective available models. Some providers like Bedrock have additional required parameters.

```APIDOC
## Available Providers and Models

### Description
This section details the various language model providers supported by MindsDB and the specific model names available under each provider. Note that some providers, like Bedrock, have additional required parameters for authentication and region specification.

### Method
N/A (Information Section)

### Endpoint
N/A (Information Section)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Available Providers:

*   **Anthropic**
    *   `claude-3-opus-20240229`
    *   `claude-3-sonnet-20240229`
    *   `claude-3-haiku-20240307`
    *   `claude-2.1`
    *   `claude-2.0`
    *   `claude-instant-1.2`

*   **Bedrock**
    *   Requires `aws_region_name`, `aws_access_key_id`, `aws_secret_access_key`.
    *   `aws_session_token` is optional.
    *   All models accessible from Bedrock are available.
    *   Requires `langchain_aws` and `transformers` packages to be installed.

*   **Google**
    *   `gemini-2.5-pro-preview-03-25`
    *   `gemini-2.0-flash`
    *   `gemini-2.0-flash-lite`
    *   `gemini-1.5-flash`
    *   `gemini-1.5-flash-8b`
    *   `gemini-1.5-pro`

*   **Ollama**
    *   `gemma`
    *   `llama2`
    *   `mistral`
    *   `mixtral`
    *   `llava`
    *   `neural-chat`
    *   `codellama`
    *   `dolphin-mixtral`
    *   `qwen`
    *   `llama2-uncensored`
    *   `mistral-openorca`
    *   `deepseek-coder`
    *   `nous-hermes2`
    *   `phi`
    *   `orca-mini`
    *   `dolphin-mistral`
    *   `wizard-vicuna-uncensored`
    *   `vicuna`
    *   `tinydolphin`
    *   `llama2-chinese`
    *   `openhermes`
    *   `zephyr`
    *   `nomic-embed-text`
    *   `tinyllama`
    *   `openchat`
    *   `wizardcoder`
    *   `phind-codellama`
    *   `starcoder`
    *   `yi`
    *   `orca2`
    *   `falcon`
    *   `starcoder2`
    *   `wizard-math`
    *   `dolphin-phi`
    *   `nous-hermes`
    *   `starling-lm`
    *   `stable-code`
    *   `medllama2`
    *   `bakllava`
    *   `codeup`
    *   `wizardlm-uncensored`
    *   `solar`
    *   `everythinglm`
    *   `sqlcoder`
    *   `nous-hermes2-mixtral`
    *   `stable-beluga`
    *   `yarn-mistral`
    *   `samantha-mistral`
    *   `stablelm2`
    *   `meditron`
    *   `stablelm-zephyr`
    *   `magicoder`
    *   `yarn-llama2`
    *   `wizard-vicuna`
    *   `llama-pro`
    *   `deepseek-llm`
    *   `codebooga`
    *   `mistrallite`
    *   `dolphincoder`
    *   `nexusraven`
    *   `open-orca-platypus2`
    *   `all-minilm`
    *   `goliath`
    *   `notux`
    *   `alfred`
    *   `megadolphin`
    *   `xwinlm`
    *   `wizardlm`
    *   `duckdb-nsql`
    *   `notus`

*   **OpenAI**
    *   `gpt-3.5-turbo`
    *   `gpt-3.5-turbo-16k`
    *   `gpt-3.5-turbo-instruct`
    *   `gpt-4`
    *   `gpt-4-32k`
    *   `gpt-4-1106-preview`
    *   `gpt-4-0125-preview`
    *   `gpt-4.1`
    *   `gpt-4.1-mini`
    *   `gpt-4o`
    *   `o4-mini`
    *   `o3-mini`
    *   `o1-mini`

*   **Nvidia NIM**
    *   `microsoft/phi-3-mini-4k-instruct`
    *   `mistralai/mistral-7b-instruct-v0.2`
    *   `writer/palmyra-med-70b`
    *   `mistralai/mistral-large`
    *   `mistralai/codestral-22b-instruct-v0.1`
    *   `nvidia/llama3-chatqa-1.5-70b`
    *   `upstage/solar-10.7b-instruct`
    *   `google/gemma-2-9b-it`
    *   `adept/fuyu-8b`
    *   `google/gemma-2b`
    *   `databricks/dbrx-instruct`
    *   `meta/llama-3_1-8b-instruct`
    *   `microsoft/phi-3-medium-128k-instruct`
    *   `01-ai/yi-large`
    *   `nvidia/neva-22b`
    *   `meta/llama-3_1-70b-instruct`
    *   `google/codegemma-7b`
    *   `google/recurrentgemma-2b`
    *   `google/gemma-2-27b-it`
    *   `deepseek-ai/deepseek-coder-6.7b-instruct`
    *   `mediatek/breeze-7b-instruct`
    *   `microsoft/kosmos-2`
    *   `microsoft/phi-3-mini-128k-instruct`
    *   `nvidia/llama3-chatqa-1.5-8b`
    *   `writer/palmyra-med-70b-32k`
    *   `google/deplot`
    *   `meta/llama-3_1-405b-instruct`
    *   `aisingapore/sea-lion-7b-instruct`
    *   `liuhaotian/llava-v1.6-mistral-7b`
    *   `microsoft/phi-3-small-8k-instruct`
    *   `meta/codellama-70b`
    *   `liuhaotian/llava-v1.6-34b`
    *   `nv-mistralai/mistral-nemo-12b-instruct`
    *   `microsoft/phi-3-medium-4k-instruct`
    *   `seallms/seallm-7b-v2.5`
    *   `mistralai/mixtral-8x7b-instruct-v0.1`
    *   `mistralai/mistral-7b-instruct-v0.3`
    *   `google/paligemma`
    *   `google/gemma-7b`
    *   `mistralai/mixtral-8x22b-instruct-v0.1`
    *   `google/codegemma-1.1-7b`
    *   `nvidia/nemotron-4-340b-instruct`
    *   `meta/llama3-70b-instruct`

### Request Example
```json
{
  "model": {
    "provider": "anthropic",
    "model_name": "claude-3-opus-20240229"
  }
}
```

### Response
#### Success Response (200)
N/A (This is informational content, not an endpoint response)

#### Response Example
N/A
```

--------------------------------

### Configure Ollama Provider in MindsDB

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/create

Example configuration for the Ollama provider in MindsDB, specifying the model name and the base URL of the Ollama instance. This allows MindsDB to utilize models served by Ollama.

```json
{
  "provider": "ollama",
  "model_name": "<model_name>",
  "base_url": "<ollama_base_url>"
}
```

--------------------------------

### Create and Query MySQL Database in MindsDB

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/grafana

Provides an example of creating a MySQL database connection within MindsDB and then querying data from it. This showcases the integration capabilities with external databases like MySQL.

```sql
CREATE DATABASE mysql_demo_db
WITH ENGINE = "mysql",
PARAMETERS = {
   "user": "user",
   "password": "MindsDBUser123!",
   "host": "samples.mindsdb.com",
   "port": "3306",
   "database": "public"
   };

SELECT * FROM mysql_demo_db.air_passengers;
```

--------------------------------

### Create a Project in MindsDB

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/project

Demonstrates the SQL syntax for creating a new project in MindsDB. Projects help organize artifacts like models and views based on predictive tasks. The 'IF NOT EXISTS' clause prevents errors if a project with the same name already exists.

```sql
CREATE PROJECT [IF NOT EXISTS] project_name;
```

--------------------------------

### Extract Content to Markdown using TO_MARKDOWN (SQL)

Source: https://docs.mindsdb.com/mindsdb/_sql/functions/to_markdown_function

This SQL snippet demonstrates how to use the TO_MARKDOWN function to extract content from a PDF file and store it in a knowledge base. It specifies the URL of the PDF and inserts the extracted content along with a unique ID into a table named 'my_kb'.

```sql
INSERT INTO my_kb (
  SELECT
    HASH('https://www.princexml.com/howcome/2016/samples/invoice/index.pdf') as id,
    TO_MARKDOWN('https://www.princexml.com/howcome/2016/samples/invoice/index.pdf') as content
)
```

--------------------------------

### Example Output of Custom Function Usage in MindsDB

Source: https://docs.mindsdb.com/mindsdb/_sql/functions/custom_functions

This is the expected output when the custom function `add_integers` is used in the provided SQL query. It shows the result of adding 1 to the `sqft` column.

```sql
+-----------+------+
| added_one | sqft |
+-----------+------+
| 918       | 917  |
+-----------+------+
```

--------------------------------

### Use Custom Functions in MindsDB SQL Queries

Source: https://docs.mindsdb.com/mindsdb/_sql/functions/custom_functions

After uploading custom functions, they can be called within SQL queries using the format `collection_name.function_name()`. This example demonstrates adding one to a column value.

```sql
SELECT functions.add_integers(sqft, 1) AS added_one, sqft
FROM example_db.home_rentals
LIMIT 1;
```

--------------------------------

### Insert Data into Knowledge Base

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/insert_data

Inserts data from a source table into a specified knowledge base. If the `content_columns` parameter was not defined during KB creation, the content column must be aliased as `content`.

```sql
INSERT INTO my_kb
SELECT order_id, product, notes
FROM sample_data.orders;
```

```sql
INSERT INTO my_kb
SELECT order_id, notes AS content
FROM sample_data.orders;
```

--------------------------------

### EVALUATE KNOWLEDGE_BASE Syntax with OpenAI

Source: https://docs.mindsdb.com/mindsdb/_sql/knowledge_bases/evaluate

This SQL snippet demonstrates the complete syntax for the `EVALUATE KNOWLEDGE_BASE` command, including required and optional parameters. It shows how to specify a test table, generate data, use an OpenAI LLM for evaluation, and save results to a table.

```sql
EVALUATE KNOWLEDGE_BASE my_kb
USING
    test_table = my_datasource.my_test_table,
    version = 'doc_id',
    generate_data = {
        'from_sql': 'SELECT id, content FROM my_datasource.my_table',
        'count': 100
    },
    evaluate = false,
    llm = {
        'provider': 'openai',
        'api_key':'sk-xxx',
        'model_name':'gpt-4'
    },
    save_to = my_datasource.my_result_table;
```

--------------------------------

### Error Example: Uppercase in Backticks for New Objects

Source: https://docs.mindsdb.com/mindsdb/-objects

Demonstrates an error scenario where attempting to create a new view with an uppercase letter inside backticks will fail, as all object names must be lowercase.

```sql
CREATE VIEW `My View` (...);   -- error
```

--------------------------------

### Connect MindsDB to SQL Alchemy using Python

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/sql-alchemy

Establishes a connection to MindsDB using SQL Alchemy in Python. Requires the 'pymysql' module to be installed. The function returns a SQLAlchemy engine object.

```python
from sqlalchemy import create_engine

user = 'mindsdb'
password = ''
host = '127.0.0.1'
port = 47335
database = ''

def get_connection():
    return create_engine(
        url="mysql+pymysql://{0}:{1}@{2}:{3}/{4}".format(user, password, host, port, database)
    )

if __name__ == '__main__':
    try:
        engine = get_connection()
        engine.connect()
        print(f"Connection to the {host} for user {user} created successfully.")
    except Exception as ex:
        print("Connection could not be made due to the following error: \n", ex)
```

--------------------------------

### Connect a Data Source

Source: https://docs.mindsdb.com/mindsdb/_sql/sql/create/database

Use the CREATE DATABASE command to establish a connection to a data source. You can specify the engine and connection parameters required for each integration.

```APIDOC
## POST /api/databases

### Description
Creates a new database connection to a specified data source using the MindsDB SQL API.

### Method
POST

### Endpoint
/api/databases

### Parameters
#### Request Body
- **name** (string) - Required - The identifier for the data source to be created.
- **engine** (string) - Optional - The engine to be selected depending on the database connection. Defaults to 'mindsdb' if not provided.
- **parameters** (object) - Required - A JSON object containing key-value pairs of connection parameters specific to the engine.

### Request Example
```json
{
  "name": "mysql_datasource",
  "engine": "mariadb",
  "parameters": {
    "user": "root",
    "port": 3307,
    "password": "password",
    "host": "127.0.0.1",
    "database": "my_database"
  }
}
```

### Response
#### Success Response (200)
- **status** (string) - Indicates the success of the operation (e.g., 'Query OK').
- **rows_affected** (integer) - The number of rows affected by the operation.
- **time_elapsed** (string) - The time taken for the operation to complete.

#### Response Example
```json
{
  "status": "Query OK",
  "rows_affected": 0,
  "time_elapsed": "8.878 sec"
}
```
```

--------------------------------

### Show Tables in MindsDB via Metabase SQL

Source: https://docs.mindsdb.com/mindsdb/_sql/connect/metabase

Executes the SHOW TABLES command in MindsDB through the Metabase SQL editor to list available tables. This is a basic query to verify the connection and understand the data structure.

```sql
SHOW TABLES;
```
Tokens:
Raw