MindsDB provides Machine Learning capabilities to enable predictive questions about your data. With MindsDB:
- Developers can quickly add AI capabilities to their applications.
- Data scientists can streamline MLOps by deploying ML models as AI Tables.
- Data analysts can easily make forecasts on complex data, such as multivariate time-series with high cardinality, and visualize these in BI tools like Grafana, and Tableau.
Combining both MindsDB and QuestDB provides unbound prediction ability with SQL.
This guide describes how to pre-process data in QuestDB and then access these data from MindsDB to produce powerful ML models.
- docker: To create an image and run the container.
- mysql: The client we use to interact with MindsDB
mysql -h 127.0.0.1 --port 47335 -u mindsdb -p). Alternatively, use MindsDB web console at
- Curl: To upload data to QuestDB from a local CSV file.
The following are the overall steps to connect MindsDB and QuestDB:
- Build a Docker image and spawn a container to run MindsDB and QuestDB together.
- Add QuestDB as a datasource to MindsDB using a SQL Statement.
- Create a table and add data for a simple ML use case using QuestDB's web console.
- Connect to MindsDB using
mysqlas a client and write some SQL.
We have put together all the files needed in GH.
#Running the Docker container
Clone the repository for this tutorial. The Dockerfile allows us to build an image with the following command:
Then, start the service container
qmdb with the following command:
The container is run as user
quest. It takes about 10 seconds to become
responsive, logs can be followed in the terminal:
The container has these mount points:
- /home/quest: User home directory.
- ~/questdb/: QuestDB's root directory.
- ~/questdb/db/: QuestDB's data root directory.
- ~/backups/: Directory for backups.
- ~/csv/: Directory for the
- ~/mindsdb/storage/: MindsDB's data root directory.
The container is running
Debian GNU/Linux 11 (bullseye) and exposes these
- 9000: QuestDB Web Console
- 8812: QuestDB pg-wire
- 9009: QuestDB ILP ingress line protocol
- 47334: MindsDB WebConsole
- 47335: MindsDB mysql API
- 47336: MindsDB mongodb API
#Adding data to QuestDB
There are different ways to insert data to QuestDB.
We can access QuestDB's web console at
Run the following SQL query to create a simple table:
We could populate table house_rentals_data with random data:
The data CSV file can be downloaded to a local folder and uploaded to QuestDB using the following command:
Either way, this gives us 100 data points, one every 4 hours, from
2021-01-16T12:00:00.000000Z (QuestDB's timestamps are UTC with microsecond
#Connect to MindsDB
We can connect to MindsDB with a standard mysql-wire-protocol compliant client (no password, hit ENTER):
Alternatively, we can use MindsDB web console at
From the terminal or the MindsDB web console, run the following command to check the available databases:
QuestDB is not shown in the result:
To see QuestDB as a database we need to add it to MindsDB:
SHOW DATABASES; should display both MindsDB and QuestDB:
This is a read-only view on our QuestDB instance. We can query it leveraging the full power of QuestDB's unique SQL syntax because statements are sent from MindsDB to QuestDB without interpreting them. It only works for SELECT statements:
The result should be something like this:
Beyond SELECT statements, for instance when we need to save the results of a
query into a new table, we need to use QuestDB's web console available at
Contains the metadata tables necessary to create ML models:
#Create a predictor model
We can create a predictor model
mindsdb.home_rentals_model_ts to predict the
rental_price for a neighborhood considering the past 20 days, and no
This triggers MindsDB to create/train the model based on the full data available
from QuestDB's table
house_rentals_data (100 rows) as a time series on the
When status is complete, the model is ready for use; otherwise, we simply wait while we observe MindsDB's logs. Creating/training a model will take time proportional to the number of features, i.e. cardinality of the source table as defined in the inner SELECT of the CREATE MODEL statement, and the size of the corpus, i.e. number of rows. The model is a table in MindsDB:
The new table is displayed:
#Describe the predictor model
We can get more information about the trained model, how was the accuracy
calculated or which columns are important for the model by executing the
DESCRIBE MODEL statement:
Or, to see how the model encoded the data prior to training we can execute:
Additional information about the models and how they can be customized can be found on the Lightwood docs.
#Query MindsDB for predictions
rental_price value per neighborhood in table
questdb.house_rentals_data can be obtained directly from QuestDB executing
To predict the next value:
#Stop the container
To terminate the container, run: