U-SQL job to query multiple tables with dynamic names

Question

Our challenge is the following one :

• in an Azure SQL database, we have multiple tables with the following table names : table_num where num is just an integer. These tables are created dynamically so the number of tables can vary. (from table_1, table_2 to table_N) All tables have the same columns.

• As part of a U-SQL script file, we would like to execute the same query on all of these tables and generate an output csv file with the combined results of all these queries.

We tried several things :

• U-SQL does not allow looping so we were thinking creating a View in our Azure SQL database that would combine all the tables using a cursor of some sort. Then, the U-SQL file would query this View (using external source). However, a View in Azure SQL database can only be created via a function and a function cannot execute dynamic SQL or even call a stored procedure...

• We did not find a way to call a stored procedure of the external data source directly from U-SQL

• we dont want to update our U-SQL job each time a new table is added...

Is there a way to do that in U-SQL through a custom extractor for instance? Any other ideas?

Answer 1

One solution I can think of is to use Azure Data Factory (v2) to assist in this.

You could create a pipeline with the following activities:

1. Lookup activity configured to execute the stored procedure
2. For Each activity that uses the output of the lookup activity as a source
   1. As a child item use a U-Sql Activity that executes your U-Sql script which writes the output of a single table (the item of the For Each activity) to blob or datalake
3. Add a Copy Activity that merges the blobs from step 2.1 to one final blob.

If you have little or no experience working with ADF v2 do mind that it takes some time to get to know it but once you do, you won't regret it. Having a GUI to create the pipeline is a nice bonus.

Edit: as @wBob mentions another (far easier) solution is to somehow create a single table with all rows since all dynamically generated table have the same schema. You can create a stored procedure for populating this table for example.