Azure architecture question for transfer data to Azure SQL Database

user_15183037 0
Viewed 90

I want to switch my on premise data collection to Azure environment.

At the moment I`m saving up to 500k (messages) on data a day.
Before storing the data in the database, i doing some data preparation in a C# application like getting/setting relation ids.

Now for my question:
I read a lot of azure documentation and come to the conclusion that i have two ways.

1. IOT Hub -> Azure Stream Analytics -> Azure SQL Database
My understanding is that if i use Azure Functions, i could just use my C# application for data preparation and insert.

2. IOT Hub -> Azure Functions -> Azure SQL Database
With Stream Analytics i could use reference data for data preparation before insert to the database but not sure of the capabilities of this service.

Can anyone with experience give me tips on how to best implement the use case in azure.
Is there maybe even a better solution as the two i wrote above?

azure-stream-analytics azure-iot-hub azure-functions azure-sql-database azure
2 Answers

You haven't mentioned if the 500K messages are sent at one go or distributed across the day? How are the messages being sent to your on prem env currently?

If they are sent to an API endpoint then definitely IOT/Eventhub is an option. If you want to reuse your C# application then I would say Azure databricks is not an option and Functions should be considered.

If you can do away with the C# application, you can write the data to a SQLDB AND do some joins with other datasets using pyspark and databricks handles this very efficiently. You should compare the costs between Functions and Azure databricks though. If you are using the Consumption plan, functions will be quite cheap.

I would say skip stream analytics if you end destination doesn't mandate data to be streamed to it. This is great for a streaming real time PowerBi dashboard.

user_143586770

Azure Stream Analytics really shines as a Streaming ETL from an event/iot hub to a SQL database.

You can manage all your types with TRY_CAST and following that approach make sure that you won't get any issue when inserting into a table.

There are advanced JSON parsing capabilities for nested records and arrays, so you can flatten your fields in the query. If a specific function in missing in SQL, you can use JavaScript user defined functions as a workaround.

You can JOIN on a reference dataset from SQL or Blob to enrich your data. And that data is cached (with an option to refresh), so you don't kill your source.

And you can use VSCode + ASA Tools extension to do local runs from live input to live output to debug your query and check your integration.

Function is awesome too, and it may be cheaper in certain cases. But it's a general purpose service, so it will take you more development to get there, where ASA is built for Streaming ETL scenarios.

user_6339250
Related