Dynamically generate Extract scripts from metadata in USQL

Question

I have a requirement to read metadata information that comes in json format and dynamically generate extract statements to further transform data for that table.

I have currently loaded metadata information in Azure SQL DB. So, I would need to read this data and create extract statements on the fly and pass them to the USQL as a parameter.

Need some help in how to proceed with this and also whether this is the correct approach that I am following.

Thanks in advance.

Answer 1

Don't equate executing U-SQL to something like Stored Procedures in SQL Server: the two are quite different under the covers. For instance, passing parameters is kinda supported, but not like you may think, and [to the best of my knowledge] dynamic script elements aren't supported.

I do, however, think you could accomplish this with Azure Data Factory (ADF) and some custom code.

1. ADF executes U-SQL scripts by referencing a blob in Blob Storage, so you could have an ADF custom activity (Azure Batch) that reads your metadata and dynamically generates the U-SQL script to an Azure Blob.
2. Once available, the Data Factory can execute the generated script based on a pipeline parameter that holds the script name.

Doing this in ADF allows you to perform this complex operation dynamically. If you go this route, be sure to use ADF V2.