Read MongoDB Data From ClickHouse

In this article, you will learn how to read your data from ClickHouse which is stored in MongoDB.

MongoDB engine is read-only table engine which allows to read data (SELECT queries) from remote MongoDB collection. Engine supports only non-nested data types. INSERT queries are not supported.

First, you have to create a table in ClickHouse which is related with the MongoDB parameters. Table engine in ClickHouse should be MongoDB and for the table parameter you should give your MongoDB connection informations.

CREATE TABLE mongo_table
 (
     key UInt64,
     data String
 ) ENGINE = MongoDB('host:port', 'dbName', 'collectionName', 'username', 'password');

Example;

I am going to read startup_log collection data located on local database in MongoDB. So, i am going to create a table in ClickHouse;

CREATE TABLE mongo_table
 (
     key UInt64,
     data String
 ) ENGINE = MongoDB('45.79.40.64:27017', 'local', 'startup_log', 'root', 'root');

Run the query in ClickHouse;

SELECT COUNT() FROM mongo_table;

Result;

 ┌─count()─┐
 │      47 │
 └─────────┘

To read from an SSL secured MongoDB server;

CREATE TABLE mongo_table_ssl
 (
     key UInt64,
     data String
 ) ENGINE = MongoDB('45.79.40.64:27017', 'local', 'startup_log', 'root', 'root', 'ssl=true');

You can also adjust connection timeout;

CREATE TABLE mongo_table_to
 (
     key UInt64,
     data String
 ) ENGINE = MongoDB('45.79.40.64:27017', 'local', 'startup_log', 'root', 'root', 'connectTimeoutMS=100000');