U SQL动态查询生成：基于文件元数据动态定义Extract语句求助

Hey there! I’ve tackled dynamic U-SQL Extract generation using external metadata a few times, so let’s walk through a practical, actionable approach to make this work:

First, you need to pull in the metadata that defines each file’s schema. The method depends on where your metadata lives:

• From a Relational Metadata Store
  If your file schemas are stored in a SQL Server/Azure SQL DB, use U-SQL’s ADO.NET extractor to query that metadata directly:

  DECLARE @metadataConnString string = "Server=tcp:your-sql-server.database.windows.net,1433;Initial Catalog=your-metadata-db;User ID=your-user;Password=your-pass;Encrypt=True;";
  DECLARE @metadataQuery string = "SELECT FilePath, ColumnName, UsqlDataType FROM dbo.FileSchemaMetadata WHERE Dataset = 'CustomerTransactions'";
  
  @metadata =
      EXTRACT FilePath string,
              ColumnName string,
              UsqlDataType string
      FROM @metadataQuery
      USING new Microsoft.Analytics.Samples.Formats.AdoNet.AdoNetSqlExtractor(@metadataConnString);
  

• From a File-Based Metadata Source
  If you store metadata in JSON/CSV files (e.g., in Azure Blob Storage), use the appropriate extractor to load it:

  @metadata =
      EXTRACT FilePath string,
              SchemaColumns array<struct<ColumnName:string, UsqlType:string>>
      FROM "/metadata/customer_transactions_schema.json"
      USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();
  

Next, you’ll transform that metadata into valid U-SQL Extract syntax. The key here is aggregating columns per file:

• For Row-Based Metadata (One Row Per Column)
  Group by file path, then concatenate columns into a single Extract column definition:

  @dynamicExtracts =
      SELECT FilePath,
             String.Concat(
                 "EXTRACT ",
                 String.Join(", ", GROUP BY ColumnName, UsqlDataType INTO @cols.Select(c => $"{c.ColumnName} {c.UsqlDataType}")),
                 " FROM \"", FilePath, "\" USING Extractors.Csv(skipFirstNRows: 1);"
             ) AS ExtractScript
      FROM @metadata
      GROUP BY FilePath;
  

• For Structured Metadata (One Row Per File)
  If your metadata already has a nested list of columns, just unpack and join:

  @dynamicExtracts =
      SELECT FilePath,
             String.Concat(
                 "EXTRACT ",
                 String.Join(", ", SchemaColumns.Select(c => $"{c.ColumnName} {c.UsqlType}")),
                 " FROM \"", FilePath, "\" USING Extractors.Csv(skipFirstNRows: 1);"
             ) AS ExtractScript
      FROM @metadata;
  

Important note: U-SQL is statically compiled, so you can’t run dynamically generated Extract statements directly within a single U-SQL script. Instead, you’ll need to:

1. Run the metadata fetch/aggregation script first to generate the Extract statements.
2. Extract those statements (e.g., write them to a file or query the job output).
3. Use the Azure SDK or Azure Data Factory (ADF) to build a full U-SQL script and submit it.

Example Using Azure .NET SDK #

// 1. First, retrieve the generated Extract statements from your initial U-SQL job output
var extractStatements = new List<string>();
// (Code to pull @dynamicExtracts results into this list)

// 2. Build the full U-SQL script
var fullUsqlScript = string.Join("\n\n", extractStatements) + @"

// Add your downstream processing logic here, e.g.:
@combinedData =
    SELECT * FROM [FirstFileExtract]
    UNION ALL
    SELECT * FROM [SecondFileExtract];

OUTPUT @combinedData
TO "/output/combined_customer_data.csv"
USING Outputters.Csv();
";

// 3. Submit the script to your ADLA account
var adlaClient = new AdlaAccountClient(new AdlaCredentials("your-adla-account-name"), new Uri("https://your-adla-account.usgovcloudapi.net/"));
var jobSubmissionResult = adlaClient.Job.SubmitJob("Dynamic Customer Data Extract", fullUsqlScript);

Don’t forget these common scenarios:

• Different File Types: Add an ExtractorType field to your metadata, then dynamically swap in Extractors.Tsv(), Extractors.Text(), or custom extractors.
• Schema Evolution: Include a flag in metadata to enable allowExtraColumns: true in CSV extractors if files might have unexpected columns.
• Type Mapping: Ensure your metadata uses valid U-SQL data types (e.g., string instead of VARCHAR, DateTime instead of DATETIME2).

内容的提问来源于stack exchange，提问作者Swati Gutgutia