-1

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.

SDR
  • 361
  • 1
  • 4
  • 20
  • Get started with U-SQL in Azure Data Lake Analytics - https://learn.microsoft.com/en-us/azure/data-lake-analytics/data-lake-analytics-u-sql-get-started – Mike Ubezzi Oct 02 '18 at 23:33
  • Thanks @MikeUbezziMSFT but I already have already worked a bit on U-SQL . My challenge here is to dynamically generate the extract scripts and to know whether I am taking the right approach. – SDR Oct 03 '18 at 20:21

1 Answers1

2

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.

Community
  • 1
  • 1
Joel Cochran
  • 7,139
  • 2
  • 30
  • 43
  • Thanks Joel. I have actually used an ADF to call my stored procedure which reads this metadata json file and saves the column info into tables. But it looks like I need to look into ADF Custom Activity. Will search for some examples and explore that. – SDR Oct 03 '18 at 20:17