Azure Storage Account: Move Tables data to Containers storage

Question

Azure Storage Account: Move Tables data to Containers storage

Sryn 21

I have data in my work's Azure Storage Account - Tables Data Storage. It is from a Stream Analytics Job which was routed from IoT Hub. I want to setup a POC DashBoard and one way I researched is using Azure Data Explorer (ADE). ADE has a tutorial to Ingest Data from a Blob container, but not Blob tables. I understand that Azure Storage Account (ASA) uses Blobs. So, I need the data in ASA Tables Data Storage to be in the ASA Containers Data Storage.

Any way I can get the data in my ASA Tables transferred/copied to my ASA Containers?

Ultimately, any suggestion to get a DashBoard from my ASA Tables data is greatly appreciated.

Thank you in advance.

Accepted answer

0 additional answers

Your answer

Answer 1

Ravi Kanth Koppala 3,391 Microsoft Employee Moderator

@Sryn

It is possible to copy data from Azure Storage Tables to Azure Blob storage. One way to do this is to use Azure Data Factory to create a pipeline that copies the data from the Azure Storage Tables to Azure Blob storage.

Here are the steps to set this up:

Create an Azure Storage Account and an Azure Blob storage container in the Azure portal.
In the Azure portal, create an Azure Data Factory instance.
In the Azure Data Factory instance, create a new pipeline.
In the pipeline, add a copy activity that copies data from the Azure Storage Tables to the Azure Blob storage container.
Configure the source and sink for the copy activity. For the source, select the Azure Storage Tables. For the sink, select the Azure Blob storage container.
Set up the copy activity to specify the mapping between the source and sink.
Test the pipeline by executing a pipeline run.

After the data has been copied to the Azure Blob storage container, you can use Azure Data Explorer to ingest the data and create a dashboard.

Alternatively, you can use Azure Stream Analytics to read data from the Azure Storage Tables and send the data to Azure Event Hubs. Then, you can use Azure Data Explorer to ingest data from the Azure Event Hubs and create a dashboard.

I hope this helps! Let me know if you have any questions. If my answer didn't help, please be more specific on the question so I can assist you.

----------

Please "Accept as Answer" and Upvote if any of the above helped so that it can help others in the community looking for remediation for similar issues.

Sryn 21 Reputation points

2022-12-20T05:19:03.24+00:00

Hi Ravi,

Thank you for the suggestion. I think I followed your instructions successfully but I have trouble at the mapping.

That last 'Value' row maps automatically to a Boolean type. I don't know how to change its mapping type. Pipeline validation is OK but on Debug, it fails due to this mapping. I want it just to be a String type.

Can you teach me how to fix this here in this thread?
Ravi Kanth Koppala 3,391 Reputation points Microsoft Employee Moderator

2022-12-20T07:34:26.227+00:00
@Sryn ,

I think we can use the derived column transformation to generate new columns in your data flow or to modify existing fields - https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column. To change the mapping type of a column from Boolean to String, you can use the "Derived Column" transformation in the Mapping section of the Copy Data activity.

Here is an example of how to do this:

In the Mapping section of the Copy Data activity, click on the "Add Derived Column" button.

In the Derived Column transformation, create a new column with the desired name and data type (e.g., "ValueString" with data type "String").

Use an expression to convert the value of the "Value" column to a string and assign it to the new "ValueString" column. For example, you can use the following expression: string(Value)

In the Sink section of the Copy Data activity, specify the new "ValueString" column to be mapped to the target container.

Save and run the pipeline to copy the data from the Storage tables to the container using the modified mapping.

I hope this helps! Let me know if you have any other questions.
Sryn 21 Reputation points

2022-12-20T07:56:39.62+00:00

Thanks Ravi for the suggestion.

Haven't tried that yet. I just got a hack working for this. In the original Storage Account Table, I changed the first row Value type from Boolean to String. The following rows I left as is. That hack seemed to make that pipeline auto-mapping thing to map Value as a string type. Both pipeline Validate and Debug works now. I also see the expected JSON file + values in the Storage Account Blob Container.

I'll mark this thread as Answered now.

Again, thank you.

Share via

Azure Storage Account: Move Tables data to Containers storage

0 additional answers

Your answer