Azure Data Lake中使用U-SQL提取路径参数与指定序号列并做聚合

It looks like you're working with U-SQL to process CSV files stored in Azure Data Lake Storage (ADLS) with the path structure /jobhistory/{AccountId}/{JobId}/*.csv. Below is a complete, adjusted version of your code to extract the required fields and perform aggregation:

Step 1: Define the File Set Path #

First, declare the file set path with the folder parameters (AccountId and JobId) that will be automatically extracted from the directory structure:

DECLARE @file_set_path string = "/jobhistory/{AccountId}/{JobId}/*.csv";

Step 2: Extract Data from CSV Files #

Use Extractors.Csv() to read the CSV files, and map the 6th column (0-based index 5) to the username field. The AccountId and JobId from the folder path will be included as columns in your dataset:

@extracted_data =
    EXTRACT 
        -- Fields pulled directly from the folder structure
        AccountId string,
        JobId string,
        -- 6th column from the CSV file (0-based index = 5)
        username string
    FROM @file_set_path
    USING Extractors.Csv(
        skipFirstNRows: 1, -- Set to 0 if your CSV has no header row
        delimiter: ',',
        silent: true -- Optional: Skip rows with parsing errors (remove for debugging)
    )
    WITH (
        COLUMN_MAPPING = "username: 5" -- Map username to the 6th CSV column
    );

Step 3: Perform Aggregation #

Now you can aggregate the data based on your needs. For example, here's how to count unique jobs and total records per AccountId and username:

@aggregated_results =
    SELECT 
        AccountId,
        username,
        COUNT(DISTINCT JobId) AS TotalUniqueJobs,
        COUNT(*) AS TotalRecords
    FROM @extracted_data
    GROUP BY AccountId, username;

Step 4: Output the Results #

Finally, write the aggregated data to an output file in ADLS:

OUTPUT @aggregated_results
TO "/output/account_user_job_summary.csv" -- Choose your desired output path
USING Outputters.Csv(quoting: false); -- Adjust quoting behavior as needed

Key Notes #

• Header Rows: If your CSV files don't have a header row, set skipFirstNRows: 0 in the extractor.
• Column Indices: Remember CSV column mapping uses 0-based indices—so the 6th column corresponds to index 5.
• Error Handling: Remove silent: true during testing to view parsing errors, which helps debug malformed CSV rows.
• Permissions: Ensure your Azure Data Lake Analytics account has read access to the input path and write access to the output path.

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