How to convert the MySql structured data into NoSql hierarchical data using Azure DataFlows

Question

Hi Team,

Actually trying to convert the MySql data set into NoSql Hierarchical data structure format using dataflows in azure data factory, but currently not able to find the right option to configure it.

Please find the below snippets for input/output sources.

output :

[
  {
    "company" : "company1",
    "projects" : [
      {
        "projectId" : 100,
        "projectSize" :2,
        "employees" : ["M101", "M102"]
      },
      {
        "projectId" : 200,
        "projectSize" :1,
        "employees" : ["M201"]
      }
    ]
  },
  {
    "company" : "company2",
    "projects" : [
      {
        "projectId" : 300,
        "projectSize" :1,
        "employees" : ["M301"]
      }
    ]
  }
]

Answer 1

Hi

Thanks for reaching out to Microsoft Q&A.

While this may be done for small datasets, I am not sure how successful and easy this would be to implement when it comes to converting huge amount of data. I have found some links that had discussed about such requirements, you can go through and see if it works. I know these might not be a ready to implement solution but can guide you to the right direction.

[https://stackoverflow.com/questions/8849571/relational-to-nosql-database

[https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-cosmos-db?tabs=data-factory

Same requirement was discussed by mongodb in a webinar.

[https://www.mongodb.com/presentations/webinar-transitioning-from-sql-to-mongodb

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.

Answer 2

Hi Nalagatla Mourya ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

I understand you are trying to transform your data from relational dataset to non relational dataset.

Here I am trying to achieve the same keeping the input data into .csv file. However, you can try to point the input dataset to MySQL database instead and follow the below steps:

1. Add Aggregate transformation and Group by 'companyName' and 'projectID' and in aggregate tab , provide expression as 'collect(employeeID)'
2. Add another Aggregate transformation to group by using 'companyName' and aggregate with expression as: collect(@(projectID=projectID,projectSIZE=projectSIZE,employees=employees))

3. Add sink transformation to load the data into json file .

---

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well