Azure logic apps: Exporting large data from database into CSV file

0
votes

I am working on Azure Logic App based solution to export the data from Database to CSV file.

So far, I am able to do the following:

  1. Use SQL database connector and execute the stored procedure to retrieve data
  2. Create a CSV file
  3. Use FTP connector to upload the file to an FTP server

With this approach, the issue is going to be data size. I am trying to figure out a way(s) to handle large data export. Here are the different approaches I can think of:

Option 1: Use pagination in SP, and iteratively collect the data.

Question 1.1: In each loop in the Logic App, multiple CSV files will be generated. How to combine the data from each iteration into a single CSV file?

Question 1.2: I am thinking of using a variable array to collect the data from each iteration, and then create CSV file from the variable array. Will I run into any issue if the size of the variable array becomes too large?

Option 2: Somewhere I read that you can overcome the data size issue with chunking.

Question: I am not even sure if the database connector supports chunking and will I able export the data into a single CSV file?

Option 3: Create multiple CSV files and then merge them into one CSV file?

Question: Is that possible to do it in Logic apps? Or will I need to implement a Logic function to handle the merging of CSV files?

1
azure-logic-apps
Have you looked at Azure Data Factory instead of Logic Apps? This is exactly the kind of workflow it is designed to handle. - Joel Cochran
@JoelCochran, you are right, DF is more suitable than Logic Apps. But was exploring the Logic apps for this as it can be easily configured by the Business analyst. - Amey

1 Answers

0
votes

Its hard to say different options without knowing the amount of data you are talking about.

1- Yes you can use pagination, i would recommend using a SP in your sql server to return the data and also the page size and number: https://social.technet.microsoft.com/wiki/contents/articles/40060.sql-pagination-for-bulk-data-transfer-with-logic-apps.aspx

1.1 By default for-each in LogicApps is executed in parallel, check the For each settings and change the concurrency control: https://docs.microsoft.com/sv-se/azure/logic-apps/logic-apps-control-flow-loops#foreach-loop-sequential

The data returned from the SP can be converted to csv by using "create csv table" https://docs.microsoft.com/sv-se/azure/logic-apps/logic-apps-perform-data-operations#create-csv-table-action