Managing U-SQL scripts in multiple files and run it as a single job

Question

We wrote a big U-SQL script that consists of several units of code dependent on each other. It could run as a single U-SQL job. BTW, we are running this job as Azure Data Factory activity.

We want to divide this script into multiple U-SQL scripts each containing single unit for better maintenance and testing. In this way, we will be able to do unit testing on the individual unit of the script. But we faced another issue if we do this. In the case of multiple scripts, we have to combine all of these scripts into the single script before deploying data factory pipeline.

Is there a way to manage U-SQL scripts in multiple files and submit it as a single job in Azure Data Factory?

Answer 1

At this point a script has to be a self-contained whole. You can split parts of scripts into procedures and table-valued functions for easier reuse and maintainability and sharing in the U-SQL catalog. Then your script to submit would compose these artefacts.

If you could elaborate on how you would want to split a script at the file level, please let me know. Some of the issues I see is that you will need to define the order of the files so name resolutions are done in the right order, and that the extract will occur before the output :).

Answer 2

You can split your task in multiple usql stored procs or TVFs and then create a master stored proc to call all the procs as per the desired sequence. For eg: Proc1 - Has logic to insert a customer record Proc2 - Add a column consolidating all the orders of the customer TVF1 - Passing a customer id returning you some details about the customer Proc3 - Use the details and output them in a file MasterProc - Call Proc1, Proc2, TVF1 and Proc3

Thanks Ankit