DBT is a development framework that facilitates data transformation processes. You can read more about DBT here.

Usage

Installing Adapter for DBT

MindsDB provides an adapter to integrate your predictions into the DBT workflow.

To find out more about the dbt-mindsdb adapter, follow the instructions here.

You can install the dbt-mindsdb adapter by executing this command: pip install dbt-mindsdb.

Profile Setup

  1. Create a DBT project:

    dbt init [project_name]
    
  2. Configure your profiles.yml file:

    What MindsDB Supports” Currently, MindsDB supports only user/password authentication. Please see below ~/.dbt/profiles.yml file for details.

        mindsdb:
          outputs:
          dev:
            type: mindsdb
            database: "mindsdb"
            host: "127.0.0.1"
            port: 47335
            schema: "mindsdb"
            username: "mindsdb"
            password: ""
          target: dev
    

Let’s list the required data fields.

KeyRequiredDescriptionExample
type✔️The adapter namemindsdb
database✔️The database namemindsdb
host✔️The MindsDB (hostname) to connect tocloud.mindsdb.com
port✔️The port to be used47335 or 3306
schema✔️The schema (database) where models are builtThe MindsDB data source
username✔️The username to be used to connect to the servermindsdb or MindsDB Cloud user
password✔️The password to be used for authenticationlocal password or MindsDB Cloud password

Creating a Predictor

Create a table_name.sql file where table_name is the name of the predictor.

{{
    config(
        materialized='predictor',
        integration='photorep',
        predict='name',
        predict_alias='predicted_name',
        using={
            'encoders.location.module': 'CategoricalAutoEncoder',
            'encoders.rental_price.module': 'NumericEncoder'
        }
    )
}}
    SELECT *
    FROM stores;

Let’s list the required and optional data fields.

ParameterRequiredDescriptionExample
materialized✔️Use always the value predictorpredictor
integration✔️Create an integration in MindsDB that is used to get the data from and save the result tophotorep
predict✔️Field to be predictedname
predict_aliasAlias for predicted fieldpredicted_name
usingConfiguration options for trained model

Creating Predictions Table

Create a table_name.sql file where table_name is used as the name of the predictor. Or you can create a schema_name.table_name.sql file where schema_name is the name of the integration and table_name is the name of the predictor.

{{
    config(
        materialized='table',
        predictor_name='store_predictor',
        integration='photorep'
    )
}}
    SELECT a, bc
    FROM ddd
    WHERE name > latest;

Let’s list the required data fields.

ParameterRequiredDescriptionExample
materialized✔️Use always the value tabletable
predictor_name✔️Name of the predictor model from CREATE MODELstore_predictor
integration✔️Create an integration in MindsDB that is used to get the data from and save the result tophotorep

Note that each time DBT is run, the results table is overwritten.

Testing

  1. Install dev requirements:

    pip install -r dev_requirements.txt
    
  2. Run tests:

    python -m pytest tests/
    

What’s Next?

Now that you are all set, we recommend you check out our Tutorials and Community Tutorials sections, where you’ll find various examples of regression, classification, and time series predictions with MindsDB.

To learn more about MindsDB itself, follow the guide on MindsDB database structure. Also, don’t miss out on the remaining pages from the SQL API section, as they explain a common SQL syntax with examples.

Have fun!