ContextQMD
Back to Clickhouse

mongodb

docs/en/sql-reference/table-functions/mongodb.md

26.4.1.1-new4.4 KB
Original Source

mongodb Table Function

Allows SELECT queries to be performed on data that is stored on a remote MongoDB server.

Syntax {#syntax}

sql
mongodb(host:port, database, collection, user, password, structure[, options[, oid_columns]]);
mongodb(uri, collection, structure[, oid_columns]);
mongodb(named_collection_name[, <arg>=<value>...]);

Arguments {#arguments}

ArgumentDescription
host:portMongoDB server address.
databaseRemote database name.
collectionRemote collection name.
userMongoDB user.
passwordUser password.
structureThe schema for the ClickHouse table returned from this function.
optionsMongoDB connection string options (optional parameter).
oid_columnsComma-separated list of columns that should be treated as oid in the WHERE clause. _id by default.

:::tip If you are using the MongoDB Atlas cloud offering please add these options:

ini
'connectTimeoutMS=10000&ssl=true&authSource=admin'

:::

You can also connect by URI:

sql
mongodb(uri, collection, structure[, oid_columns])
ArgumentDescription
uriConnection string.
collectionRemote collection name.
structureThe schema for the ClickHouse table returned from this function.
oid_columnsComma-separated list of columns that should be treated as oid in the WHERE clause. _id by default.
:::

You can pass the arguments using a named collection:

sql
mongodb(_named_collection_[, host][, port][, database][, collection][, user][, password][, structure][, options][, oid_columns])
-- or
mongodb(_named_collection_[, uri][, structure][, oid_columns])

Returned value {#returned_value}

A table object with the same columns as the original MongoDB table.

Examples {#examples}

Suppose we have a collection named my_collection defined in a MongoDB database named test, and we insert a couple of documents:

sql
db.createUser({user:"test_user",pwd:"password",roles:[{role:"readWrite",db:"test"}]})

db.createCollection("my_collection")

db.my_collection.insertOne(
    { log_type: "event", host: "120.5.33.9", command: "check-cpu-usage -w 75 -c 90" }
)

db.my_collection.insertOne(
    { log_type: "event", host: "120.5.33.4", command: "system-check"}
)

Let's query the collection using the mongodb table function:

sql
SELECT * FROM mongodb(
    '127.0.0.1:27017',
    'test',
    'my_collection',
    'test_user',
    'password',
    'log_type String, host String, command String',
    'connectTimeoutMS=10000'
)

or:

sql
SELECT * FROM mongodb(
    'mongodb://test_user:[email protected]:27017/test?connectionTimeoutMS=10000',
    'my_collection',
    'log_type String, host String, command String'
)

or:

sql
CREATE NAMED COLLECTION mongo_creds AS
       uri='mongodb://test_user:[email protected]:27017/test?connectionTimeoutMS=10000',
       collection='default_collection';

SELECT * FROM mongodb(
        mongo_creds,
        collection = 'my_collection',
        structure = 'log_type String, host String, command String'
)

Related {#related}