This guide will walk you through integrating MongoDB with ClickHouse using Docker containers. This integration allows you to perform real-time analytics on data stored in MongoDB using ClickHouse, a columnar database management system. We will cover the setup and configuration steps for both MongoDB and ClickHouse.
Prerequisites
Before you start, make sure you have Docker installed on your system.
Step 1: Set up the ClickHouse Container
First, we will set up a ClickHouse container. Run the following command to create a ClickHouse container with the necessary configurations:
docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 -p 18123:8123 -p 19000:9000 clickhouse/clickhouse-server
Important note: Make sure you use the latest clickhouse version, i.e version => 23.6 , otherwise you may hit bug : OP_Query , described here
Step 2: Verify ClickHouse Setup
You can verify that ClickHouse is running correctly by executing the following command:
echo 'SELECT version()' | curl 'http://localhost:18123/' --data-binary @-
To access the ClickHouse client, use the following command:
docker exec -it some-clickhouse-server clickhouse-client
Step 3: Install ClickHouse Client (Optional)
If you prefer to use the ClickHouse client on your local machine, you can install it using the following command (on macOS) and then use the ClickHouse client to connect to your ClickHouse server:
brew install clickhouse-client clickhouse-client --host=clickhouse_server_ip --port=9000 --user=your_user --password=your_password
Step 4: Set Up the MongoDB Container
Now, let’s set up a MongoDB container:
docker run -d --name mongo-container -p 27027:27017 mongo:latest
Step 5: Access MongoDB Shell
You can access the MongoDB shell by running the following command:
docker exec -it mongo-container mongosh
Alternatively, you can use the MongoDB shell directly from your local machine:
mongosh --host localhost --port 27017
Step 6: Create a MongoDB Database and Collection
Inside the MongoDB shell, you can create a database collection and insert sample data:
use sampleDB db.createCollection("mongo_table") db.mongo_table.insertMany([ { key: NumberLong("12345678901234567890"), data: "Sample data 1" }, { key: NumberLong("98765432109876543210"), data: "Sample data 2" } ])
Step 7: Create a ClickHouse Table for MongoDB Data
In ClickHouse, you must create a table referencing the MongoDB data. Use the following SQL command:
CREATE TABLE mongo_table ( key UInt64, data String ) ENGINE = MongoDB('10.0.6.24:27027', 'sampleDB', 'mongo_table', '','','connectTimeoutMS=100000');
Step 8: Query MongoDB Data in ClickHouse
Now you can query the MongoDB data within ClickHouse:
SELECT * FROM mongo_table;
This will fetch and display the data from MongoDB in ClickHouse.
Step 9: MongoDB User Authentication (Optional)
If you want to set up user authentication in MongoDB, you can create a user with specific roles. Here’s an example of creating a user with read-write access to the sampleDB
database:
db.createUser({ user: "mongo", pwd: "password", roles: [ { role: "readWrite", db: "sampleDB" } ] })
Step 10: Query MongoDB Data with Authentication
If you’ve set up user authentication in MongoDB, use the following ClickHouse query to access the data:
SELECT * FROM mongodb( '10.0.6.24:27027', 'sampleDB', 'mongo_table', 'mongo', 'password', 'key UInt64, data String', 'connectTimeoutMS=10000' );
This query retrieves data from MongoDB using the specified credentials.
Now, you have successfully integrated MongoDB with ClickHouse using Docker containers. You can perform real-time analytics on MongoDB data using ClickHouse’s powerful querying capabilities.