Looking for technical architecture suggestions. I need to run a SQL query that will return around 36000 rows of data; I need to write the results into a single CSV file. I first thought an M/R script would work but I'd need to pass the entire result set as a single key (from the Map() function) which makes M/R pointless. Maybe I could save each row to a custom record then use another M/R to create the CSV and write as many rows until governance is exceeded then it can reschedule itself and append the subsequent records data to the CSV until all records are processed. Or maybe forget the custom record and segregate each result row from the SQL query into it's own key; the first key will create the CSV file and subsequent keys can append to the CSV. Now sure how to know when the CSV has finished being created though. Could always store the number of rows in N/cache and decrement it for each row saved maybe. All above solutions sucked - thanks for the help!

You absolutely need a query? Can’t be done with a search?

I'm trying to get the quantity available for a list of locations

If you are able to create a search that gives you you the data you need, then you can use the task module to do a searchTask and export that to CSV, everything is native and asynchronous, meaning it does not matter how many results and you don’t need to worry about handling CSV creation or parsing or anything like that… Basically the same as exporting to CSV via the UI click but only better because it is a background asynchronous process.

I'm familar with task.create and passing in the fieldId

This is not exactly task.create(), give it a try, you can do a lot with searches…

I've got a functional consultant telling me there's no way to do this with one search,

In my experience if you don’t need 3 levels joins it most likely can be done with a search… But I’m not familiar enough with your case and I’m not fighting your consultant lol 😂 . That would be the cleanest possible design to handle a large dataset… Going back to use a query, you don’t need to pass the entire data set as a key… In you map stage you can use a fixed key and pass each map result into values, then group them in reduce and create your CSV file. That dataset is not large enough to break that…

I thought reduce could fully execute before receiving the next key from the map?

No, the reduce stage purpose is to aggregate data it does not run until map is completed.

If reduce aggregates all data with the same key, regardless of when it's received, this becomes easy.

Correct, the key you write in map to reduce could be ‘CSV_DATA’ and your value is each organized data row. You group in reduce, build your CSV header from the first grouped result and add your data to the CSV. My recommendation here is to use papaparse, that way you don’t need to worry about any parsing it can create a valid CSV content with one line of code.

you can use the task module with SQL?

@Craig If you can use that which is basically the same as searchTask, you should go with that, what @Anthony OConnor mentioned ^^^…

yeah you guys are massively overcomplicating this 😛

Well that makes it even easier!

I did not know the query module had a similar method, maybe new addition?

yeah idk they seem to quietly add sql support in a lot of places so always worth double checking

No very new, it looks like it was added in 2021

Everyday's a school day Thanks for the help

wait. There’s a built in “run sql and export it to csv” thing? Why did I write my own, lol.

I was on the same path!