How to copy IoT Hub stored blobs to an Azure SQL using Data Factory

0
votes

We are using the IoT Hub routing feature to store messages into an Azure Blob container. By default it stores the messages in a hierarchical manner - creating a folder structure for year, month, day and so on. Within the folder for each day, it creates multiple block blob binary files. Each file may contain multiple JSON objects, each representing a unique IoT telemetry message.

How can I use Azure Data Factory to copy each of these messages into an Azure SQL database?

Screenshot from Azure Storage Explorer

A sample blob file containing multiple messages

1
azure-sql-databaseazure-data-factoryazure-iot-hubazure-blob-storage
what's the file format of the blob, txt, csv or others? – Leon Yue
They seem to be text files, please see the second image. – sacoder
The data in these files is 'josn ' format. You want to transfer these files data in the container into Azure SQL database, am I right? – Leon Yue
Correct, I want to get these into an Azure SQL database. While Azure Data Factory tutorials provide examples of how a text/csv file in an Azure blob container can be put into an Azure SQL table, not sure how to handle these files. – sacoder
The point is that these files may in different folders. – Leon Yue

1 Answers

2
votes

It seams that all the files have the same json schema. Then you could follow my steps.

I created an folder csv in my container and have several csv files with json data: enter image description here

Source Dataset: the data in csv file is json format, so I choose the json format file.

  1. choose the container: test
  2. import the schema(.json)

enter image description here

Source setting: using wildcard file path to choose all the folder and file in the container.

enter image description here

Sink setting: enter image description here

Mapping: enter image description here

Run the pipeline and check the result in sink table: enter image description here

enter image description here