azure-data-factoryazure-logic-appspower-automateazure-automationazure-workflow-automation
ADF :: How to substitute files in Blob Storage
In the Azure Cost Management > Export, there is an export called Daily export of month-to-date:
Such export happens daily and it's exported to a Blob Storage.
PROBLEM: Because it's a month-to-day export, if today it's the 26 or April, today it will create a .csv file with all the Cost Management information from the 1th of April to the 26th.
But tomorrow another .csv file will be generated with all the costs from the 1th of April to the 27th.
This way I will have double informations!
GOAL: an ideal solution would be that as soon as a new file is exported to that Storage Account the old file is deleted.
So there is always only 1 .csv file that contains all the data from month-to-date.
SCOPE: Everything can be in scope:
- Azure Data Factory
- Logic Apps
- Automation Accounts
- Power Automate
...whatever works.
Solution
I have reproduced in my environment and got expected results as below:
Design:
Clearly:
You can reproduce the above design with below code:
Logic app code:
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Delete_blob_(V2)": {
"inputs": {
"headers": {
"SkipDeleteIfFileNotFoundOnServer": false
},
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "delete",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/files/@{encodeURIComponent(encodeURIComponent('/rithwik/',variables('ammo')))}"
},
"runAfter": {
"For_each": [
"Succeeded",
"Failed"
]
},
"type": "ApiConnection"
},
"For_each": {
"actions": {
"Append_to_array_variable": {
"inputs": {
"name": "emo",
"value": "@triggerBody()?['LastModified']"
},
"runAfter": {
"Compose": [
"Succeeded"
]
},
"type": "AppendToArrayVariable"
},
"Append_to_array_variable_2": {
"inputs": {
"name": "vammo",
"value": "@items('For_each')?['DisplayName']"
},
"runAfter": {
"Append_to_array_variable": [
"Succeeded"
]
},
"type": "AppendToArrayVariable"
},
"Compose": {
"inputs": "@items('For_each')?['LastModified']",
"runAfter": {},
"type": "Compose"
},
"Condition": {
"actions": {
"Append_to_string_variable": {
"inputs": {
"name": "ammo",
"value": "@variables('vammo')[0]"
},
"runAfter": {},
"type": "AppendToStringVariable"
}
},
"else": {
"actions": {
"Append_to_string_variable_2": {
"inputs": {
"name": "ammo",
"value": "@variables('vammo')[1]"
},
"runAfter": {},
"type": "AppendToStringVariable"
}
}
},
"expression": {
"and": [
{
"less": [
"@ticks(variables('emo')[0])",
"@ticks(variables('emo')[1])"
]
}
]
},
"runAfter": {
"Append_to_array_variable_2": [
"Succeeded"
]
},
"type": "If"
}
},
"foreach": "@body('Lists_blobs_(V2)')?['value']",
"runAfter": {
"Initialize_variable_3": [
"Succeeded"
]
},
"type": "Foreach"
},
"Initialize_variable": {
"inputs": {
"variables": [
{
"name": "emo",
"type": "array"
}
]
},
"runAfter": {
"Lists_blobs_(V2)": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_2": {
"inputs": {
"variables": [
{
"name": "vammo",
"type": "array"
}
]
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_3": {
"inputs": {
"variables": [
{
"name": "ammo",
"type": "string"
}
]
},
"runAfter": {
"Initialize_variable_2": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Lists_blobs_(V2)": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/foldersV2/@{encodeURIComponent(encodeURIComponent('JTJmcml0aHdpaw=='))}",
"queries": {
"nextPageMarker": "",
"useFlatListing": false
}
},
"metadata": {
"JTJmcml0aHdpaw==": "/rithwik"
},
"runAfter": {},
"type": "ApiConnection"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"When_a_blob_is_added_or_modified_(properties_only)_(V2)_2": {
"evaluatedRecurrence": {
"frequency": "Second",
"interval": 3
},
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/triggers/batch/onupdatedfile",
"queries": {
"checkBothCreatedAndModifiedDateTime": false,
"folderId": "JTJmcml0aHdpaw==",
"maxFileCount": 1
}
},
"metadata": {
"JTJmcml0aHdpaw==": "/rithwik"
},
"recurrence": {
"frequency": "Second",
"interval": 3
},
"splitOn": "@triggerBody()",
"type": "ApiConnection"
}
}
},
"parameters": {
"$connections": {
"value": {
"azureblob": {
"connectionId": "/subscriptions/b83c1ed3-c5b6-74c23f/resourceGroups/rbojja-/providers/Microsoft.Web/connections/azureblob",
"connectionName": "azureblob",
"id": "/subscriptions/b8b-b5ba-2074c23f/providers/Microsoft.Web/locations/eastus/managedApis/azureblob"
}
}
}
}
}
Firstly have 1 blob like below:
Blob can be of any type.
Then Uploaded new Blob:
Then blob got deleted:
Output:
Also added some other new steps to be more accurate:
Code view:
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Delete_blob_(V2)": {
"inputs": {
"headers": {
"SkipDeleteIfFileNotFoundOnServer": false
},
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "delete",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/files/@{encodeURIComponent(encodeURIComponent('/rithwik/',variables('xyz')))}"
},
"runAfter": {
"Initialize_variable_4": [
"Succeeded",
"Failed"
]
},
"type": "ApiConnection"
},
"For_each": {
"actions": {
"Append_to_array_variable": {
"inputs": {
"name": "emo",
"value": "@triggerBody()?['LastModified']"
},
"runAfter": {
"Compose": [
"Succeeded"
]
},
"type": "AppendToArrayVariable"
},
"Append_to_array_variable_2": {
"inputs": {
"name": "vammo",
"value": "@items('For_each')?['DisplayName']"
},
"runAfter": {
"Append_to_array_variable": [
"Succeeded"
]
},
"type": "AppendToArrayVariable"
},
"Compose": {
"inputs": "@items('For_each')?['LastModified']",
"runAfter": {},
"type": "Compose"
},
"Condition": {
"actions": {
"Append_to_string_variable": {
"inputs": {
"name": "ammo",
"value": "@variables('vammo')[0]"
},
"runAfter": {},
"type": "AppendToStringVariable"
}
},
"else": {
"actions": {
"Compose_2": {
"inputs": "@variables('vammo')[1]",
"runAfter": {},
"type": "Compose"
},
"Set_variable": {
"inputs": {
"name": "ammo",
"value": "@{outputs('Compose_2')}"
},
"runAfter": {
"Compose_2": [
"Succeeded"
]
},
"type": "SetVariable"
}
}
},
"expression": {
"and": [
{
"less": [
"@ticks(variables('emo')[0])",
"@ticks(variables('emo')[1])"
]
}
]
},
"runAfter": {
"Append_to_array_variable_2": [
"Succeeded"
]
},
"type": "If"
}
},
"foreach": "@body('Lists_blobs_(V2)')?['value']",
"runAfter": {
"Initialize_variable_3": [
"Succeeded"
]
},
"type": "Foreach"
},
"Initialize_variable": {
"inputs": {
"variables": [
{
"name": "emo",
"type": "array"
}
]
},
"runAfter": {
"Lists_blobs_(V2)": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_2": {
"inputs": {
"variables": [
{
"name": "vammo",
"type": "array"
}
]
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_3": {
"inputs": {
"variables": [
{
"name": "ammo",
"type": "string"
}
]
},
"runAfter": {
"Initialize_variable_2": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_4": {
"inputs": {
"variables": [
{
"name": "xyz",
"type": "string",
"value": "@variables('ammo')"
}
]
},
"runAfter": {
"For_each": [
"Succeeded",
"Failed"
]
},
"type": "InitializeVariable"
},
"Lists_blobs_(V2)": {
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/foldersV2/@{encodeURIComponent(encodeURIComponent('JTJmcml0aHdpaw=='))}",
"queries": {
"nextPageMarker": "",
"useFlatListing": false
}
},
"metadata": {
"JTJmcml0aHdpaw==": "/rithwik"
},
"runAfter": {},
"type": "ApiConnection"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"When_a_blob_is_added_or_modified_(properties_only)_(V2)_2": {
"evaluatedRecurrence": {
"frequency": "Second",
"interval": 3
},
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['azureblob']['connectionId']"
}
},
"method": "get",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/triggers/batch/onupdatedfile",
"queries": {
"checkBothCreatedAndModifiedDateTime": false,
"folderId": "JTJmcml0aHdpaw==",
"maxFileCount": 1
}
},
"metadata": {
"JTJmcml0aHdpaw==": "/rithwik"
},
"recurrence": {
"frequency": "Second",
"interval": 3
},
"splitOn": "@triggerBody()",
"type": "ApiConnection"
}
}
},
"parameters": {
"$connections": {
"value": {
"azureblob": {
"connectionId": "/subscriptions/b8074c23f/resourceGroups/bojja/providers/Microsoft.Web/connections/azureblob",
"connectionName": "azureblob",
"id": "/subscriptions/b83c1ed3-c574c23f/providers/Microsoft.Web/locations/eastus/managedApis/azureblob"
}
}
}
}
}
- Azure Data Factory CI npm validate step suddenly crashing
- Need to provide dynamic file from Azure blob to the any data activitiy in Azure Data Factory, please share some link
- How to take max value and replace it in drived column in data factory
- Synapse/ ADF - How to Truncate table if dynamic config column is True in pre-copy script
- Azure Cost Management - track costs associated with Databricks job
- Type conversion settings missed in Azure Data Factory in Copy activity
- New Linked Service (Azure SQL Database) Test connection failed
- Azure Data Factory - Unable to preview data
- Linked Service with self-hosted integration runtime is not supported in data flow in Azure Data Factory
- Pagination with oauth azure data factory
- Using Multiple Linked Services in a Data Flow, Azure Data Factory
- Azure Data Factory - Controlling order of transformations within a dataflow
- Azure Data Factory Failing with Bulk Load
- Mapping nested JSON Object to SQL table through Data flow in Azure Data Factory
- Copy Data From Azure Blob Storage to AWS S3
- Databricks job cluster per pipeline not per notebook activity
- How to orchestrate data when Im ingesting with azure data factory (external to fabric) to Lakhouse in fabric
- Azure Data Factory stored procedure activity times out
- How to use minimum number of web activities to send notifications in a ADF pipeline?
- unable to connect onpremisesql to adf
- Azure Data Factory: How to retain the file name in the sink through a copy activity
- ADF Monitor interface - run start and run end
- Azure Blob Failed to create Container container
- ADF Web Activity Error: The request content is not valid and could not be deserialized: 'After parsing a value an unexpected character was encountered
- How to handle csv file with same column name in Azure Data Factory?
- ADF Copy data activity pagination on a REST service POST request
- Azure Data Factory will not allow the Azure Function to initiate the run using managed ID
- Azure Data Factory: Exists transformation doesn't work as expected when comparing two tables
- Use Azure Data Factory to dynamically create new columns
- API working fine in Postman but not in Web Activity of ADF