MongoDB Data Connector

MongoDB Data Connector Documentation

MongoDB is an open-source NoSQL database that stores data in flexible, JSON-like documents, providing dynamic schemas and easy scalability.

The MongoDB Data Connector enables federated/accelerated SQL queries on data stored in MongoDB databases.

datasets:
  - from: mongodb:mytable
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_pool_min: 0
      mongodb_pool_max: 10

Configuration

`from`

The from field takes the form mongodb:{table_name} where table_name is the table identifer in the MongoDB server to read from.

datasets:
  - from: mongodb:mytable
    name: my_dataset
    params:
      mongodb_db: my_database
      ...

`name`

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: mongodb:my_dataset
    name: cool_dataset
    params: ...

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

`params`

The MongoDB data connector can be configured by providing the following params. Use the secret replacement syntax to load the secret from a secret store, e.g. ${secrets:my_mongodb_conn_string}.

Parameter Name	Description
`mongodb_connection_string`	The connection string to use to connect to the MongoDB server. This can be used instead of providing individual connection parameters.
`mongodb_user`	The MongoDB username.
`mongodb_pass`	The password to connect with.
`mongodb_host`	The hostname of the MongoDB server.
`mongodb_port`	The port of the MongoDB server.
`mongodb_db`	The name of the database to connect to.
`mongodb_sslmode`	Optional. Specifies the SSL/TLS behavior for the connection, supported values: `required` (default) requires an SSL connection, `preferred` tries SSL but falls back to insecure if unavailable, `disabled` does not use SSL.
`mongodb_sslrootcert`	Optional parameter specifying the path to a custom PEM certificate that the connector will trust.
`mongodb_time_zone`	Optional. Specifies connection time zone. Default is `UTC`. Accepts fixed offsets (e.g., `+02:00`) or IANA time zone names (e.g., `America/Los_Angeles`).
`mongodb_auth_source`	Optional. Authentication source database. Overrides the default auth source in the connection string.
`mongodb_unnest_depth`	Optional. Maximum nesting depth for unnesting embedded documents into a flattened structure. Higher values expand deeper nested fields. Default: `0`
`mongodb_num_docs_to_infer_schema`	Optional. Number of documents to use to infer the schema. Defaults to 400.
`mongodb_pool_min`	The minimum number of connections to keep open in the pool, lazily created when requested. Default: `0`
`mongodb_pool_max`	The maximum number of connections in the pool. Default: `10`

Types

The table below shows the MongoDB data types supported, along with the type mapping to Apache Arrow types in Spice.

MongoDB Type	Arrow Type
`String`	`Utf8`
`Boolean`	`Boolean`
`Int32`	`Int32`
`Int64`	`Int64`
`Double`	`Float64`
`Decimal128`	`Decimal128`
`Binary`	`Binary`
`Datetime` without time	`Date32`
`Datetime` with time	`Timestamp(Millisecond, <Timezone>)`
`Timestamp`	`Timestamp(Millisecond, None)`
`Array`	`List<Utf8>`
`Null`	`Null`
`Undefined`	`Null`
`RegularExpression`	`Utf8`
`JavaScriptCode`	`Utf8`
`JavaScriptCodeWithScope`	`Utf8`
`Symbol`	`Utf8`
`MaxKey`	`Utf8`
`MinKey`	`Utf8`
`DbPointer`	`Utf8`
`ObjectId`	`Utf8`
`Document`	See unnesting section

Unnesting

Consider the following document:

{
  "a": 1,
  "b": {
    "x": 2,
    "y": {
      "z": 3
    }
  }
}

Using mongodb_unnest_depth you can control the unnesting behavior. Here are the examples:

mongodb_unnest_depth: 0

sql> select * from test_table;
+-----------+---------------------+
| a (Int32) | b (Utf8)            |
+-----------+---------------------+
| 1         | {"x":2,"y":{"z":3}} |
+---+-----------------------------+

mongodb_unnest_depth: 1

sql> select * from test_table;
+-----------+-------------+------------+
| a (Int32) | b.x (Int32) | b.y (Utf8) |
+-----------+-------------+------------+
| 1         | 2           | {"z":3}    | 
+-----------+-------------+------------+

mongodb_unnest_depth: 2

sql> select * from test_table;
+-----------+-------------+---------------+
| a (Int32) | b.x (Int32) | b.y.z (Int32) |
+-----------+-------------+---------------+
| 1         | 2           | 3             |
+-----------+-------------+---------------+

Examples

Connecting using username and password and custom auth table

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_auth_source: admin

Connecting using SSL

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_sslmode: preferred
      mongodb_sslrootcert: ./custom_cert.pem

Connecting using a Connection String

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_connection_string: mongodb://${secrets:my_user}:${secrets:my_password}@localhost:27017/my_db?authSource=admin

With custom connection pool settings

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_pool_min: 5
      mongodb_pool_max: 10