topic Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric in Pipelines

Copying CosmosDB JSON data via Data Factory v2 to Fabric

BiJoe — Thu, 11 Jan 2024 09:09:33 GMT

We have a MongoDB database in Azure CosmosDB, and would like to copy that data into a Fabric workspace. However, we have run into trouble with Fabric connectors, which are in preview/ beta. What we do have working, is a linked service to Azure Cosmos DB for MongoDB in our Data Factory v2. What we are considering now, is to make a JSON dataset in DF v2, copy data to that dataset on a schedule. Next to copy that dataset into a Fabric workspace.

As long as we can get raw JSON data to Fabric, we could work it out from there. If we can use DataFlow gen 2 it would be great.

Specific storage options we are seeing when trying to creating the DFv2 dataset, is Fabric Lakehouse file storage, and ADLS gen 2. The first option does not seem to pick up the lakehouses in the workspace, and the latter option needs tenant admin involvement for creating the linked service. Given that we work out those obstacles, what would you choose as the data route?

Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric

Anonymous — Fri, 12 Jan 2024 06:47:46 GMT

Hi BiJoe
Thanks for using Fabric Community.

Please go ahead and raise a support ticket to reach our support team and they can guide you better.

https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.

Hope this is helpful. Please let me know incase of further queries.

Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric

Anonymous — Wed, 17 Jan 2024 03:44:55 GMT

Hi BiJoe
We haven’t heard from you on the last response and was just checking back to see if you got a chance to create a support ticket. If yes please share the details here.
Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric

BiJoe — Wed, 17 Jan 2024 07:53:08 GMT

hi and thanks for caring. We haven't decided yet whether to create a ticket. We have mixed experience with the support process.

Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric

NickA01 — Fri, 16 Feb 2024 16:11:14 GMT

BiJoe Greetings,
I have this working in one of my synapse workspaces.
Both the Cosmos MongoDB & the synapse workspace are in the same region.
I have both notebook & SQL connectivity.

Our issues were ensuring the Cosmos Mongo DB had the Analytics store enabled.
Next we have to set up the private access endpoint.
Finally, I had to get one of our DevOps team to run some powershell to do some dark magic with network isolation.
https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-private-endpoints#using-synapse-serverless-sql-pools

Here's the powershell
Update-AzCosmosDBAccount -Name <enter cosmosMongoDBName here> -ResourceGroupName <enter name of resourcegroup where your cosmosMongoDBName here> -NetworkAclBypass AzureServices
-NetworkAclBypassResourceId "/subscriptions/<enter subscriptionID here>/resourceGroups/<enter resourcegroup where synapse is >/providers/Microsoft.Synapse/workspaces/<enter synapse workspace name here>

Hope this is of use.

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric

BiJoe — Wed, 21 Feb 2024 11:22:35 GMT

Thanks, I will check out your suggestion when I get to prioritize this task. Have you tried the Cosmos DB Beta connector in Data Flow gen 2?

Re: Copying CosmosDB JSON data via Data Factory v2 to Fabric

NickA01 — Thu, 22 Feb 2024 08:02:56 GMT

Unfortunatly, Fabric Dataflow gen 2 has no support for Cosmos Mongo.
I've raised this on the ideas board --Microsoft Idea

Best I've been able to do is a nasty have witha gen 1 dataflow that I copied out of Advanced query in a power bi desktop file.

Here the start of my M query
Blank Query
let
Source = DocumentDB.Contents("https://<cosmosMongoAccountName>.documents.azure.com:443/", "<cosmosMongo DB Name>", "<collection name>"),
#"Expanded Document" = Table.ExpandRecordColumn(Source, "Document",<list out the fileds here>