How to import MongoDB Data to Azure Synapse?

Abhiram Patil 0 Reputation points
2023-07-25T16:01:00.62+00:00

Hello,

I have JSON data in MongoDB that I would like to transform into SQL tables in Azure Synapse. So far I have:

  1. Made a connection to MongoDB under Linked Services in the Manage tab of Synapse studio
  2. Clicked ingest on the home page of Synapse studio. Ingest options:
    1. Source Type: MongoDB
      1. Connection: Added the connection I made
        1. Selected all the files (There's only one)
          1. **I didn't check "**Export as-is to JSON files or Azure Cosmos DB collection"
            1. Destination Data Store: Azure Data Lake Storage Gen 2
              1. Name: data (I did this process a 2nd time and named the file data.json but ran into the same issues)
                1. File format: JSON
                  1. File pattern: didn't choose anything
                    1. Schema mapping: everything looked good:
                                          ![User's image](/api/attachments/466d0fa0-185e-4285-9d3b-c247181364c5?platform=QnA)
                      
                      1. Deployment Successful
  3. In the data tab, right clicked 'date' and selected new SQL query > bulk
    1. Here I get the following error: Failed to detect schema Please review and update the file format settings to allow file schema detection

I need help to fix this, thanks in advance.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,172 questions
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,602 Reputation points Microsoft Employee
    2023-07-26T22:23:04.5966667+00:00

    @Abhiram Patil Welcome to Microsoft Q&A forum and thanks for reaching out here.

    As per your description, while creating the copy pipeline for ingesting the data to your ADLS Gen2 location, in the destinations dataset configuration you have selected File format as JSON , which has resulted in the above error. This is because when you tried to select new SQL query > bulk then Synapse Analytics uses COPY statement for loading from external storage accounts. And this statement supports only the below file types:

    User's image

    Hence to overcome the issue, while creating the destination dataset, in the configuration section instead of JSON format try creating a DelimitedText. This should help overcome the problem.

    User's image

    Hope this helps.


    Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.