What's the best way to write big datasets to several database tables?

Question

The data would be processed in my Alteryx workflow first and would then need to be written to the database.

I've been advised that a BULK INSERT stored procedure would be the most efficient approach.

Is it best to get Alteryx Server to create CSV files, save them to a network drive location, then trigger the stored procedure? Should I use a Python tool to save the CSV files then trigger the stored procedure to ensure the order of operations is correct (I've found control containers to be a bit buggy)?

Or is there a better way to do it using Alteryx?

apathetichell · Answer

your SQL server is onprem/bare metal too? what does large mean in datasets?

ALT_2358 · Answer

Thanks for the reply.

1) The data is uploaded to the server by the user (file browse).

2) The server is on-prem. It does technically have storage, but our Alteryx Server admin does not want us to store files there, even if temporary.

3) Alteryx takes the user's input, does some processing and creates some outputs which are then saved to the database at the end. Nothing needs to be done with the data after it's written to the db, that's the final step. Alteryx Designer is not required - it can all be done on Server.

So would you suggest getting Alteryx to simply create the CSV files and not do any of the db writing, then get the SQL server to ingest those CSV files and do all of the writing using stored procedures?

Thanks

apathetichell · Answer

There's a lot of unknowns here:

1) where is your data coming from (ie how does it get to Server)

2) is your server on a cloud vm (and if so - is your destination database on that cloud - or does it have storage in that cloud)

3) can you confirm that nothing needs to be done on Designer?

the best way to bulk load data to a cloud database is to use native features of that cloud platform and object storage. To anyone at home - that means that you should right raw csv or parquet files to s3/gcs/azure blob - and then set up internal systems on your cloud to process ingestion to your database. why? because that's what your cloud platform doe. For smaller loads (say sub 50mm rows) it probably doesn't matter if you write from server or upload as raw files and migrate to the db using internal cloud tools.