How do you use the Atlas SQL Interface with MongoDB?


The Atlas SQL Interface is a powerful feature that allows you to query MongoDB data using SQL syntax, bridging the gap between traditional SQL databases and MongoDB’s document-based structure.

Enabling Atlas SQL

To start using Atlas SQL, you need to enable it for your MongoDB Atlas cluster:

  1. Ensure your Atlas cluster is running MongoDB version 5.0 or higher
  2. Navigate to your Atlas cluster and click on “Connect” under the “ATLAS SQL” section
  3. Create a new connection string by clicking “Create”
  4. Select the appropriate driver for your use case (e.g., Tableau Connector, JDBC, or ODBC)

Connecting to Atlas SQL

Once enabled, you can connect to Atlas SQL using various methods:

BI Tools

  1. Use custom connectors for supported BI tools like Tableau or Power BI
  2. For Power BI:
    • Select “Get Data” and choose “MongoDB Atlas SQL”
    • Enter your MongoDB URI and database name
    • Provide your Atlas MongoDB Database access credentials

JDBC or ODBC Drivers

  1. Download the appropriate driver (JDBC or ODBC) from the MongoDB download center
  2. Use the driver to connect from your preferred SQL client or BI tool

MongoDB Shell

You can also connect using the MongoDB Shell for quick queries.

Querying with Atlas SQL

Once connected, you can start querying your MongoDB data using SQL syntax:

Basic Queries

 

Working with Nested Data

Atlas SQL provides special functions like FLATTEN and UNWIND to work with nested document structures.

Benefits of Atlas SQL

  • SQL Compatibility: Allows SQL users to query MongoDB data without learning a new query language
  • Schema Flexibility: Retains MongoDB’s schema-less structure while enabling SQL queries
  • Integration: Seamlessly works with popular BI tools for data visualization and analysis

Considerations

  • Atlas SQL only supports read operations; you cannot write data using SQL syntax
  • Querying with Atlas SQL incurs data transfer charges, so be mindful of costs

By leveraging Atlas SQL, you can combine the flexibility of MongoDB’s document model with the familiarity of SQL, making it easier to perform analytics and generate reports on your MongoDB data.

Additional Resources

How to tune Linux Threads for MongoDB IOPS Performance?