Transition from Atlas BI Connector to MongoSQL

MongoDB recommends migrating from the Atlas BI Connector to the newer SQL Interface.

Note

The Atlas BI Connector and the on-premises BI Connector are separate tools. This guide is about migrating from the Atlas BI Connector, which MongoDB is phasing out.

MongoSQL

Similar to the Atlas BI Connector, MongoSQL enables you to analyze data from Atlas clusters using a variety of SQL-based tools, such as Tableau and Power BI.

Compared to the Atlas BI Connector, MongoSQL offers the following advantages:

You can read data from sources other than Atlas clusters using the Atlas Data Federation infrastructure.
You can set your schema.
You pay only for your usage; you don't need a subscription.
You can use custom MongoDB connectors for Tableau and Power BI.

To learn more about MongoSQL, see Query with SQL Interface.

MongoSQL Limitations

MongoSQL is read-only.
MongoSQL is compatible only with the SQL 92 dialect; other SQL dialects are not supported.
All Atlas Data Federation limitations apply to MongoSQL because it's a feature of Atlas Data Federation.

MongoSQL Pricing

MongoSQL incurs only Atlas Data Federation query costs and AWS transfer costs; the SQL Interface itself is free to use. To learn more about the cost of querying your federated database instance, see Data Federation Costs.

Note

Costs incurred by MongoSQL queries appear on your invoice under the federated database instance that you queried, either as "Data Processed" or "Data Returned and Transferred" charges.

Prepare to Transition to MongoSQL

To transition from the Atlas BI Connector to MongoSQL, make sure your deployment meets the following prerequisites:

Additionally, MongoDB recommends that you generate a Transition Readiness Report to help plan your transition.

Prerequisites

An federated database instance containing queryable data.
A MongoDB database user to connect to your federated database instance.

Generate a Transition Readiness Report

MongoDB provides an MongoSQL Transition Readiness Tool to help you plan your move from the Atlas BI Connector to the SQL Interface. The tool generates a report based on your past Atlas BI Connector usage, providing real-time schema analysis and suggestions and highlighting queries that need syntax changes to run properly using MongoSQL.

To generate a report, you must provide the tool with at least one of the following details:

Your Atlas BI Connector logs, for query analysis.
Your cluster URI, for schema analysis.

You can analyze your queries, your schema, or both.

Download the MongoSQL Readiness Report Tool.

Select the tab for your operating system below and download the executable file.

Download the MongoDB MongoSQL Readiness Report Tool.

Grant execution permission to the Readiness Report Tool.

If the file doesn't have execution permission already, grant it.

 chmod +x <executable-filename>

 chmod +x <executable-filename>

 chmod +x <executable-filename>

(Optional) Download and decompress your Atlas BI Connector logs.

Providing your Atlas BI Connector logs enables the Readiness Report Tool to report on the following information:

Historical query data, such as volume and frequency.
Query syntax that will fail in MongoSQL.
Collection fields with data types unknown to relational databases.

To download your Atlas BI Connector logs:

In the Atlas UI, go to the Atlas cluster with the BI connection that you want to analyze.
From your cluster's options (), select Download Logs.
Download mongosql.gz.
Create a new directory, then decompress mongosql.gz into it.

(Optional) Copy your cluster URI.

Providing your Atlas cluster URI enables the Readiness Report Tool analyze your collection schemas and identify fields that contain data types unknown to SQL tools.

To find your cluster URI:

In the Atlas UI, go to the cluster with the collections that you want to analyze.
Click Connect.
Select Shell from the list of connection options.
Copy only your connection URI.
The connection URI resembles: mongodb+srv://bicluster.example.mongodb.net/. Exclude the shell executable, mongosh, and any shell-specific command line options.

Generate a report.

In a terminal, run the Readiness Report Tool executable, providing your downloaded logs or your cluster URI.

You must include your database username.
You must include either --input, --uri, or both. If you include your URI, the Readiness Report Tool prompts you for your database user password.
You can specify an --output destination for your generated report. If you don't, it's generated in your current directory.
You can specify a --resolver to choose a DNS resolver for network requests. Possible values are: cloudflare, google, and quad9.
You can use --include or --exclude to narrow your list of namespaces. Glob syntax is supported. By default, all namespaces are included.

The --help option returns the full list of Readiness Report Tool options:

<executable-filename> --help
Options:
     -i, --input <INPUT>        Sets the input file or directory to analyze BIC logs (optional). One of `--input` or `--uri` must be provided, or both
     -o, --output <OUTPUT>      Sets the output directory (optional). If not specified, the current directory is used
         --uri <URI>            The Atlas cluster URI to analyze schema (optional). One of `--input` or `--uri` must be provided, or both
     -u, --username <USERNAME>  Username for authentication (optional). This is required if the username and password is not provided in the URI
         --quiet                Enables quiet mode for less output
         --resolver <RESOLVER>  The specified resolver (optional) [possible values: cloudflare, google, quad9]
         --include <INCLUDE>    A list of namespaces to include (optional). If not provided, all namespaces are included. Glob syntax is supported
         --exclude <EXCLUDE>    A list of namespaces to exclude (optional). If not provided, no namespaces are excluded
     -h, --help                 Print help (see more with '--help')
     -V, --version              Print version

The Readiness Report Tool organizes the output and generates a clickable index file so you can easily navigate the report.

Transition to MongoSQL

The underlying architecture of MongoSQL is different from Atlas BI Connector and you might need to adapt your schema or your queries.

To transition to MongoSQL, identify existing Atlas BI Connector queries that fail on MongoSQL and update your schema or their syntax to fix them.

Warning

MongoDB recommends testing the full transition process in a sandbox environment before you make changes to your production environment. Transitioning from the Atlas BI Connector to MongoSQL without adapting your schema or your queries might cause breaking changes.

Enable MongoSQL for your federated database instance.

To learn more about enabling and using MongoSQL, see Enable the SQL Interface.

Connect to your data with the SQL Interface.

To learn more about connecting with MongoSQL, see Connect Using the Atlas SQL Interface.

Test your queries.

Test your queries with your new MongoSQL connection to ensure they run and return the results you expect.

To learn more about querying with MongoSQL, see Query with MongoSQL Statements.

View your schema and adapt it if necessary.

To learn more about schemas in MongoSQL, see Schema Management.

Adapt any failing queries.

Some query syntax might need to be changed when you transition from the Atlas BI Connector to MongoSQL.

To learn more about MongoSQL query syntax, see MongoSQL Language Reference.

Troubleshoot MongoSQL

The following MongoDB resources can help you troubleshoot your MongoSQL configuration:

The MongoDB communities on Reddit or Stack Overflow
Support
Professional Services
Your Customer Success team

Back

Enable SQL Access

Connect