Azure Data Factory Table to Table copy missing columns

Bryan 6

I am trying to triplicate the data from one Azure Table storage in a storage account to 2 other Azure Tables each in their own storage accounts, but when triggering the transfer of data, the output is missing columns. Likely because there are nulls for the missing columns on the first row being pulled.

How do make sure all the columns are being pulled?
Is there another solution besides Data Factory to making a complete copy of a Table Storage?

Worst case I can update my code to write to all three tables at the same time, but prefer that I didn't have to.

Is there a better way to handle the nulls when working with Azure Table storage? My columns are dynamically generated because the ecosystem where the data is initially being pulled is extremely dynamic and new columns could be added as the new configurations are deployed.

I thought I had this same issue with PowerBI/Excel connecting to the Table Storage, but there is an option option to load more content and then all the columns are loaded so I have hoped that something similar is done/available in Data Factory.

I've seen some instances where it's recommended to use "First Row as Header", but that option does not appear to be available for Azure Table Storage as a Source

Any other suggestions?

Bryan

svijay-MSFT 5,211 Reputation points Microsoft Employee

2021-10-14T12:23:07.337+00:00
Hello @Bryan ,

Thanks for the question and using MS Q&A platform.

From my understanding is correct, there are new columns and column values generated at later point of time. You'd like to import these new columns and column values. My assumption is that you're making use of the Copy Activity. Please let me know if this is not the case.

I did a similar testing at my end with the copy activity. But had the mapping cleared.

I had the below data in the same order :

{"Column1"="Value1";} {"Column1"="Value2";} {"Column1"="Value3";"Column2"="ValueA"} {"Column1"="Value3";"Column2"="ValueA";"Column3"="ValueX"}

The first row had 1 column and later rows had additional columns.

At the end of the copy activity, the below is the preview of the Target/destination table.

Alternatively, when I had the mapping in place

The output was as follows :

1 answer

Bryan 6 Reputation points

2021-10-15T16:28:41.41+00:00

Hi @svijay-MSFT

Thanks for responding.

Your three pictures at the end did not seem to make it through into the post that I can see.

The number of columns in my data right now including the partition key, row key and timestamp is 19 columns, with plans to add more columns in the future. The first row in my table is has data in the first 16 columns with the last three on the far right having a "null" value. The 33rd row in the table utilizes all 19 columns, but only the first 13 show up in the Preview of the Pipeline.

I cleared the mapping in the pipeline and then selected the Import Schemas button and get this

And then if I select the drop down menu to add a new column mapping and I only get the 16 columns that are populated by the first row and it's missing the last three

The last three would be these
Please sign in to rate this answer.
svijay-MSFT 5,211 Reputation points Microsoft Employee

2021-10-18T14:03:44.117+00:00

Hello @Bryan ,

Thanks for getting back to me. I m not sure why the screenshot did not come through. I'll see whether I can get back the screenshot.

I understood that you had cleared the mapping and imported the schema again to do the mapping again.

But what happens when you don't import the schema (after clearing the mapping)? Usually, when you don't have the explicit mapping - the activity tries to auto map. When I had the auto mapping, It seemed to work as expected at my end.

svijay-MSFT 5,211 Reputation points Microsoft Employee

2021-10-18T14:16:03.91+00:00

The below was the output when I did not have any explicit mapping :

Bryan 6 Reputation points

2021-10-18T15:30:59.823+00:00

@svijay-MSFT

If I clear out the mapping (tried resetting as well) and then preview the data in the Pipeline, the 3 missing columns are still missing. If I go back to the source and preview the data, the 3 extra columns are missing there as well.

I have tried recreating the Dataset, but it is still missing the 3 columns.

svijay-MSFT 5,211 Reputation points Microsoft Employee

2021-10-18T16:40:26.89+00:00

Hello @Bryan ,

Thanks for getting back. If I m not wrong, the Preview Data loads only the top ten rows. Whatever column comes in the top 10 rows, will be displayed. Now this is subjected only to the preview data.

Like you mentioned, you are having records only from 33rd row. You are less likely see the (3) columns in the preview data. If this is your case.

You can view the actual Az Storage Table and see whether you are having the necessary column. At my end,I encountered the issue only in the preview data, new columns added after 10th row did not appear in the preview data. However, when I queried the table data using the below script :

Connect-AzAccount -Subscription <SUBSCRIPTION> $storageaccount = Get-AzStorageAccount -ResourceGroupName <RESOURCE GROUP NAME> -Name <STORAGE ACCOUNT NAME> $table = Get-AzStorageTable –Name <TABLENAME> -Context $storageAccount.Context $cloudTable = $table.CloudTable $rows = Get-AzTableRow -Table $table.CloudTable

`

#Copies the output to the clipboard $rows | fl | clip

I pasted the output in a Notepad - And I saw the all the columns to be present in the output.

Bryan 6 Reputation points

2021-10-21T17:53:17.607+00:00

@svijay-MSFT

I opened a Microsoft Support case and with their help, we determined that adjusting the code to include the extra columns was the best route. Even though the data didn't appear in the "preview data" output, adjusting the mapping explicitly the code brought over the "missing" columns and the data.

I also had to adjust the code on the Sink for the Partition Key and Row Key values (which I wanted to preserve) because the Drop Down menu, while having an option to "Select" a Column from the Source, there was nothing to select.

"sink": { "type": "AzureTableSink", "azureTableInsertType": "replace", "azureTablePartitionKeyName": "PartitionKey", "azureTableRowKeyName": "RowKey", "writeBatchSize": 10000
Sign in to comment

Share via

Azure Data Factory Table to Table copy missing columns

1 answer