Skip to main content

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.

Creating a Tableโ€‹

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
name1 [type1],
name2 [type2],
...
) ENGINE = MongoDB(host:port, database, collection, user, password [, options]);

Engine Parameters

  • host:port โ€” MongoDB server address.

  • database โ€” Remote database name.

  • collection โ€” Remote collection name.

  • user โ€” MongoDB user.

  • password โ€” User password.

  • options โ€” MongoDB connection string options (optional parameter).

Usage Exampleโ€‹

Create a table in ClickHouse which allows to read data from MongoDB collection:

CREATE TABLE mongo_table
(
key UInt64,
data String
) ENGINE = MongoDB('mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse');

To read from an SSL secured MongoDB server:

CREATE TABLE mongo_table_ssl
(
key UInt64,
data String
) ENGINE = MongoDB('mongo2:27017', 'test', 'simple_table', 'testuser', 'clickhouse', 'ssl=true');

Query:

SELECT COUNT() FROM mongo_table;
โ”Œโ”€count()โ”€โ”
โ”‚ 4 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

You can also adjust connection timeout:

CREATE TABLE mongo_table
(
key UInt64,
data String
) ENGINE = MongoDB('mongo2:27017', 'test', 'simple_table', 'testuser', 'clickhouse', 'connectTimeoutMS=100000');

Original article