MindsDB
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.
Prerequisites
- 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 http://localhost:47334/ instead. - Curl: To upload data to QuestDB from a local CSV file.
Instructions
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
mysql
as 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:
docker build -t questdb/mindsdb:latest .
Then, start the service container qmdb
with the following command:
docker run --rm \
-p 8812:8812 \
-p 9009:9009 \
-p 9000:9000 \
-p 8888:8888 \
-p 47334:47334 \
-p 47335:47335 \
-d \
--name qmdb \
questdb/mindsdb:latest
The container is run as user quest
. It takes about 10 seconds to become
responsive, logs can be followed in the terminal:
docker logs -f qmdb
...
http API: starting...
mysql API: starting...
mongodb API: starting...
...
mongodb API: started on 47336
mysql API: started on 47335
http API: started on 47334
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
COPY
operation. - ~/mindsdb/storage/: MindsDB's data root directory.
The container is running Debian GNU/Linux 11 (bullseye)
and exposes these
ports:
- 9000: QuestDB Web Console
- 8812: QuestDB pg-wire
- 9009: QuestDB InfluxDB Line Protocol 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.
SQL
We can access QuestDB's web console at http://localhost:9000.
Run the following SQL query to create a simple table:
CREATE TABLE IF NOT EXISTS house_rentals_data (
number_of_rooms INT,
number_of_bathrooms INT,
sqft INT,
location SYMBOL,
days_on_market INT,
initial_price FLOAT,
neighborhood SYMBOL,
rental_price FLOAT,
ts TIMESTAMP
) TIMESTAMP(ts) PARTITION BY YEAR;
We could populate table house_rentals_data with random data:
INSERT INTO house_rentals_data SELECT * FROM (
SELECT
rnd_int(1,6,0),
rnd_int(1,3,0),
rnd_int(180,2000,0),
rnd_symbol('great', 'good', 'poor'),
rnd_int(1,20,0),
rnd_float(0) * 1000,
rnd_symbol('alcatraz_ave', 'berkeley_hills', 'downtown', 'south_side', 'thowsand_oaks', 'westbrae'),
rnd_float(0) * 1000 + 500,
timestamp_sequence(
to_timestamp('2021-01-01', 'yyyy-MM-dd'),
14400000000L
)
FROM long_sequence(100)
);
CURL command
The data CSV file can be downloaded to a local folder and uploaded to QuestDB using the following command:
curl -F data=@sample_house_rentals_data.csv "http://localhost:9000/imp?forceHeader=true&name=house_rentals_data"
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
precision).
Connect to MindsDB
We can connect to MindsDB with a standard mysql-wire-protocol compliant client (no password, hit ENTER):
mysql -h 127.0.0.1 --port 47335 -u mindsdb -p
Alternatively, we can use MindsDB web console at http://localhost:47334:
From the terminal or the MindsDB web console, run the following command to check the available databases:
SHOW DATABASES;
QuestDB is not shown in the result:
+--------------------+
| Database |
+--------------------+
| mindsdb |
| files |
| information_schema |
+--------------------+
To see QuestDB as a database we need to add it to MindsDB:
CREATE DATABASE questdb
WITH ENGINE = "questdb",
PARAMETERS = {
"user": "admin",
"password": "quest",
"host": "questdb",
"port": "8812",
"database": "questdb"
};
Then, run SHOW DATABASES;
should display both MindsDB and QuestDB:
+--------------------+
| Database |
+--------------------+
| mindsdb |
| files |
| questdb |
| information_schema |
+--------------------+