1) Can i use storage table for capture log details of pipeline via datafactory. will it allow insert new values in table? 2) I want to update specific column in storage table. how do i update the column which activity I should use? like I want to create table "control_table" with columns having "table_name" and "last_update" columns. every time I load data , i want to update the "last update column".

Hi @THIMMAIAH GARI,PRASHANTH,, , Welcome to Microsoft Q&A Platform. Thanks for posting the query. Yes, Azure table storage can be used to log the details from Azure data factory. Both insert and update are possible. I have used copy activity to implement the above requirement with dummy source file whereas additional columns will be used for populating table name and date that can come from pipeline variables. This activity can be used after table is loaded in ADF pipeline to record the load into the Azure table. Replace will replace entire property of previous entity with new entity if the entity exists. If the new entity doesn't define property or having property value null then that property will be removed on updating whereas in merge, old properties will be retained even if the new entity didn't define new properties in new entity and it updates if new values are defined for old properties if entity exists. Hence, I have used merge in this scenario. Partition key + Row key combination has to be unique in a table and hence, partition key can be used appropriately from your scenario. I have used static value of 1 for partition key and used table name as row key in this scenario. So that whenever date property changes for same table name under 1 partition id. Please find below GIF and JSON attached of the pipeline. 32392-tablestorageupsertjson.txt Ref: difference-between-insert-or-merge-entity-and-insert-or-replace-entity Hope this helps! Please let us know if it does not align with requirement or for further queries and we will be glad to assist.

How to update data into Azure storage table from datafactory?

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-14T15:33:41.837+00:00
Hi @THIMMAIAH GARI,PRASHANTH,, ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Yes, Azure table storage can be used to log the details from Azure data factory. Both insert and update are possible.

I have used copy activity to implement the above requirement with dummy source file whereas additional columns will be used for populating table name and date that can come from pipeline variables. This activity can be used after table is loaded in ADF pipeline to record the load into the Azure table.

Replace will replace entire property of previous entity with new entity if the entity exists. If the new entity doesn't define property or having property value null then that property will be removed on updating whereas in merge, old properties will be retained even if the new entity didn't define new properties in new entity and it updates if new values are defined for old properties if entity exists. Hence, I have used merge in this scenario.

Partition key + Row key combination has to be unique in a table and hence, partition key can be used appropriately from your scenario. I have used static value of 1 for partition key and used table name as row key in this scenario. So that whenever date property changes for same table name under 1 partition id.

Please find below GIF and JSON attached of the pipeline.

32392-tablestorageupsertjson.txt

Ref: difference-between-insert-or-merge-entity-and-insert-or-replace-entity

Hope this helps! Please let us know if it does not align with requirement or for further queries and we will be glad to assist.
Please sign in to rate this answer.

1 person found this answer helpful.
THIMMAIAH GARI,PRASHANTH,, 201 Reputation points

2020-10-15T08:33:30.707+00:00

Currently I am lookuping storage table and filtering data using filter activity.

In above your answer...I have to use one file in datalake as source with one row and one column...otherwise i cant update directly with additional columns

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-15T09:24:37.95+00:00

Hi @THIMMAIAH GARI,PRASHANTH,, ,

Thanks for sharing additional details. Yes, Lookup can be used on table storage dataset as well. And source can also be selected as Azure Table storage and then pass additional columns as below. In this case, no need to use any file from data lake as source. If you want to read single table record from Azure table , please use syntax as in this document and also as in below GIF.

Please let us know for additional queries on this and we will be glad to assist further.

THIMMAIAH GARI,PRASHANTH,, 201 Reputation points

2020-10-16T12:37:10.54+00:00

In Your video you added code like @markus.bohland@hotmail.de ('RowKey eq ''',item().ROWKEY,'''') in variable

But I couldn't find in the document you referred above. Please share the link for those query's.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T13:13:41.833+00:00

Hi @THIMMAIAH GARI,PRASHANTH,, ,

Thanks for reaching out. The queries are written are based on below section in the document where they used filter on LastModifiedTime and I have used query on RowKey value in the table.

Hope this helps! Please let us know for further queries and we will be glad to assist.

THIMMAIAH GARI,PRASHANTH,, 201 Reputation points

2020-10-16T13:18:28.627+00:00

here in the document the example it's having "gt" and "le" as code..but in your example Rowkey having "eq" as code...this is something different if we get more details, it will be helpful.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T13:41:45.327+00:00

@THIMMAIAH GARI,PRASHANTH,, - As per my understanding, this is bash commands syntax and hence used eq similar to gt command that is same as equals. I will check with Product team for confirmation and check with them on updating the documentation with more details on the same. I will get back to you with more details.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-20T13:29:45.547+00:00

@THIMMAIAH GARI,PRASHANTH,, - Thanks for your patience. I got update from Product team that Azure Table Storage query is a direct map to Azure Table Storage Entities Query Option $filter as specified in this document. Product team also will be working on updating this info in the document related to loading from Azure Table Storage in data factory.

Thanks for your valuable contribution.

Mehdi Nikkhah 0 Reputation points

2023-01-27T17:43:38.41+00:00

Hello @THIMMAIAH GARI,PRASHANTH,,

I have hard time deploy above approach using bicep.

Unfortunately, the latest dataset version supported by Bicep is 'datasets@2018-06-01', which does not support "Additional Column". What do you recommend?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to update data into Azure storage table from datafactory?

0 additional answers

Your answer