Loading...

Azure Data Factory: How to split a file into multiple output files with Bicep

Azure Data Factory: How to split a file into multiple output files with Bicep

Introduction

 

In this article we will see how to split a csv file located in an Azure Storage Account through Azure Data Factory Data Flow.

 

We will do it through Azure Bicep in order to demonstrate the benefits of Infrastructure as Code (IaC) including:

  • Reviewing the planned infrastructure that will be deployed through the what-if feature.
  • Reproducible and testable infrastructures with templating deployments.

 

Jamesdld23_8-1706197123009.png

 

 

A complete procedure to deploy the following resources is available here: https://github.com/JamesDLD/bicep-data-factory-data-flow-split-file

  • Azure Storage Account
  • Uploading a test file that will be splitted
  • Azure Data Factory
  • Azure Data Factory Linked Service to connect Data Factory to our Storage Account
  • Azure Data Factory Data Flow that will split our file
  • Azure Data Factory Pipeline to trigger our Data Flow
  • Bonus: Azure Data Factory Pipeline to cleanup the output container for your demos

 

Bicep code to create our linked service

The following Bicep code demonstrates how to create a Storage Account Linked Service through Azure Bicep.

 

Every body might not be concerned by the following limitation but in order to make this demo accessible to everyone we will create the Storage Account linked service through a connection string instead of using a managed identity which is definitely what I usually recommend.

 

If your blob account enables soft delete, system-assigned/user-assigned managed identity authentication isn’t supported in Data Flow.
If you access the blob storage through private endpoint using Data Flow, note when system-assigned/user-assigned managed identity authentication is used Data Flow connects to the ADLS Gen2 endpoint instead of Blob endpoint. Make sure you create the corresponding private endpoint in ADF to enable access.

Source: Data Flow/User-assigned managed identity authentication

 

 

Let’s have a look at the Bicep code!

The only trick here is to grab an existing storage account and pass its connection string through Bicep without having any secret in your code.

 

 

 

@description('Name of the Azure storage account that will contain the file we will split.') param storageAccountName string = 'storage${uniqueString(resourceGroup().id)}' resource storageAccount 'Microsoft.Storage/storageAccounts@2021-08-01' existing = { name: storageAccountName } resource dataFactoryLinkedService 'Microsoft.DataFactory/factories/linkedservices@2018-06-01' = { parent: dataFactory name: dataFactoryLinkedServiceName properties: { type: 'AzureBlobStorage' typeProperties: { connectionString: 'DefaultEndpointsProtocol=https;AccountName=${storageAccount.name};AccountKey=${storageAccount.listKeys().keys[0].value}' } } }

 

 

Bicep code to create our Azure Data Factory Data Flow

 

Based on the following reference “Microsoft.DataFactory factories/linkedservices” we will create the Azure Data Factory Data Flow that will split our file into multiple files.

 

 

 

@description('The Blob s name that will be splitted') param blobNameToSplit string = 'file.csv' @description('The Blob s folder path that will be splitted') param blobFolderToSplit string = 'input' @description('The Blob s folder path that will be splitted') param blobOutputFolder string = 'output' resource dataFactoryLinkedService 'Microsoft.DataFactory/factories/linkedservices@2018-06-01' = { parent: dataFactory name: dataFactoryLinkedServiceName properties: { type: 'AzureBlobStorage' typeProperties: { connectionString: 'DefaultEndpointsProtocol=https;AccountName=${storageAccount.name};AccountKey=${storageAccount.listKeys().keys[0].value}' } } } resource dataFactoryDataFlow 'Microsoft.DataFactory/factories/dataflows@2018-06-01' = { parent: dataFactory name: dataFactoryDataFlowName properties: { type: 'MappingDataFlow' typeProperties: { sources: [ { linkedService: { referenceName: dataFactoryLinkedService.name type: 'LinkedServiceReference' } name: 'source' description: 'File to split' } ] sinks: [ { linkedService: { referenceName: dataFactoryLinkedService.name type: 'LinkedServiceReference' } name: 'sink' description: 'Splitted data' } ] transformations: [] scriptLines: [ 'source(useSchema: false,' ' allowSchemaDrift: true,' ' validateSchema: false,' ' ignoreNoFilesFound: false,' ' format: \'delimited\',' ' container: \'${blobContainerName}\',' ' folderPath: \'${blobFolderToSplit}\',' ' fileName: \'${blobNameToSplit}\',' ' columnDelimiter: \',\',' ' escapeChar: \'\\\\\',' ' quoteChar: \'\\\'\',' ' columnNamesAsHeader: true) ~> source' 'source sink(allowSchemaDrift: true,' ' validateSchema: false,' ' format: \'delimited\',' ' container: \'${blobContainerName}\',' ' folderPath: \'output\',' ' columnDelimiter: \',\',' ' escapeChar: \'\\\\\',' ' quoteChar: \'\\\'\',' ' columnNamesAsHeader: true,' ' filePattern:(concat(\'${blobNameToSplit}\', toString(currentTimestamp(),\'yyyyMMddHHmmss\'),\'-[n].csv\')),' ' skipDuplicateMapInputs: true,' ' skipDuplicateMapOutputs: true,' ' partitionBy(\'${partitionType}\', ${numberOfPartition})) ~> sink' ] } } }

 

 

 

When using the az deployment what-if option we can see the following changes. This is really convenient to see the asked changes before applying them.

 

 

 

numberOfSplittedFiles=3 blobFolderToSplit="input" blobNameToSplit="file.csv" blobOutputFolder="output" resourceGroupName=myDataFactoryResourceGroup dataFactoryName=myDataFactoryName storageAccountName=myStorageAccountName blobContainerName=myStorageAccountContainerName az deployment group what-if \ --resource-group $resourceGroupName \ --template-file data-factory-data-flow-split-file.bicep \ --parameters dataFactoryName=$dataFactoryName \ storageAccountName=$storageAccountName \ blobContainerName=$blobContainerName \ numberOfPartition=$numberOfSplittedFiles \ blobFolderToSplit=$blobFolderToSplit \ blobNameToSplit=$blobNameToSplit \ blobOutputFolder=$blobOutputFolder

 

 

 

Jamesdld23_7-1706196963855.png

 

 

The Data Flow looks like the following screenshot where we can see the number of partition that will be created. In our context it corresponds to the number of csv files that will be generated from our input csv file.

 

 

Jamesdld23_6-1706196915290.png

 

The other trick here is to play with a file name pattern to manage the target files names.

 

Jamesdld23_3-1706196727355.png

 

 

 

The output files in this sample will be set to fit with the input file name, the current date and the output file iteration.

 

 

 

concat('file.csv', toString(currentTimestamp(),'yyyyMMddHHmmss'),'-[n].csv')

 

 

 

 

Split the file through the Pipeline

 

Through the procedure located here https://github.com/JamesDLD/bicep-data-factory-data-flow-split-file we have created an Azure Data Factory pipeline named “ArmtemplateSampleSplitFilePipeline”, you can trigger it to launch the Data Flow that will split the file.

 

The following screenshot illustrates the split result done through Azure Data Factory Data Flow.


 

Jamesdld23_5-1706196812836.png

 

Conclusion

 

Considering Bicep or any other Infrastructure as Code (IaC) tool ensures to gain efficiency and agility, its a real ramp up when designing infrastructures and it makes them reproducible and testable.

 

See You in the Cloud

Jamesdld

Published on:

Learn more
Azure Developer Community Blog articles
Azure Developer Community Blog articles

Azure Developer Community Blog articles

Share post:

Related posts

Comparing feature sets for AKS enabled by Azure Arc deployment options

This article shows a comparison of features available for the different deployment options under AKS enabled by Azure Arc.    ...

2 hours ago

Azure Fluid Relay: Leveraging Azure Blob Storage to scale Git

Learn how to leverage Git as a storage mechanism behind the globally available Azure Fluid Relay (AFR) service. The post Azure Fluid Relay: Le...

8 hours ago

Verify the integrity of Azure Confidential Ledger transactions with receipts and application claims

In today's digital landscape, the integrity and confidentiality of transactional data are paramount. Microsoft’s Azure Confidential Ledger off...

9 hours ago

HTTP Trigger Azure Function Authorization Types simplified

Here' how you can quickly understand what are the different Authorization Levels to be set while working with HTTP Azure Functions.

1 day ago

Increasing Security for SQL Server Enabled by Azure Arc

Back in November 2023, the least privileges deployment model was introduced as a public preview. After thorough testing, we are excited to ann...

3 days ago

Govern your Azure Firewall configuration with Azure Policies

Introduction:  In the rapidly evolving digital landscape, securing cloud environments is more critical than ever. Azure Firewall emerges ...

3 days ago

Azure Verified Modules - Monthly Update [June]

AVM Module Summary The AVM team are excited that our community have been busy building AVM Modules. As of June 17th, the AVM Footprint curren...

3 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy