USQL提取CSV文件列：动态列数变更需频繁改代码求助

Got it, let's tackle this USQL dynamic CSV column issue—super common pain point when dealing with daily changing files! Here are a few solid approaches to avoid rewriting your code every time columns shift:

USQL supports wildcard extraction (*) to pull in all columns from your CSV automatically, regardless of how many new ones get added. You can then just select the specific columns you need, and your code stays unchanged even as the source file's schema grows.

Here's a quick example:

DECLARE @inputFile string = "/input/daily_file.csv";

// Grab every column from the CSV (handles new columns automatically)
@rawData =
    EXTRACT 
        *
    FROM @inputFile
    USING Extractors.Csv(skipFirstNRows: 1); // Adjust skipFirstNRows if your CSV doesn't have a header

// Filter down to only the columns you care about—this part never needs updating
@filteredData =
    SELECT
        CustomerID,
        OrderDate,
        TotalAmount // Replace with your target column names
    FROM @rawData;

OUTPUT @filteredData
TO "/output/processed_daily_data.csv"
USING Outputters.Csv();

This works because EXTRACT * dynamically reads all columns present in the CSV. As long as your target column names don't change, the SELECT clause will keep pulling them in, ignoring any new columns added later.

If you're worried about the rare case where a target column might get removed (not just added), you can add safeguards to avoid runtime errors. Use ISNULL to set default values for missing columns, or check for column existence upfront with GetColumnNames().

Example with error checking and defaults:

DECLARE @inputFile string = "/input/daily_file.csv";
DECLARE @requiredColumns = new SqlArray<string>{"CustomerID", "OrderDate"};

// First, verify all required columns exist in the CSV
@columnValidation =
    SELECT
        CASE WHEN COUNT(DISTINCT col) = @requiredColumns.Count THEN true ELSE false END AS AllColumnsPresent
    FROM GetColumnNames(@inputFile, Extractors.Csv(skipFirstNRows: 1)) AS col
    WHERE col IN @requiredColumns;

// Proceed only if all columns are present (or add logic to handle missing ones)
@rawData =
    EXTRACT *
    FROM @inputFile
    USING Extractors.Csv(skipFirstNRows: 1);

@safeFilteredData =
    SELECT
        ISNULL(CustomerID, "UNKNOWN") AS CustomerID, // Default if column is missing
        ISNULL(OrderDate, "1900-01-01") AS OrderDate,
        TotalAmount // Optional column—will just be null if missing
    FROM @rawData;

OUTPUT @safeFilteredData
TO "/output/safe_processed_data.csv"
USING Outputters.Csv();

For more complex scenarios (like CSVs with inconsistent formatting, quoted fields, or non-standard delimiters), you can create a custom C# extractor that dynamically parses the CSV and only pulls your target columns.

First, write the custom extractor (you can embed this in your USQL script or reference a compiled assembly):

public class TargetColumnExtractor : IExtractor
{
    private readonly string[] _targetColumns;
    private readonly bool _hasHeader;

    public TargetColumnExtractor(string[] targetColumns, bool hasHeader = true)
    {
        _targetColumns = targetColumns;
        _hasHeader = hasHeader;
    }

    public IEnumerable<IRow> Extract(IUnstructuredReader input, IUpdatableRow output)
    {
        using var reader = new StreamReader(input.BaseStream);
        string line;
        int[] targetIndices = null;

        // Parse header to find positions of target columns
        if (_hasHeader)
        {
            line = reader.ReadLine();
            var headers = line.Split(','); // Adjust delimiter if needed
            targetIndices = _targetColumns.Select(col => Array.IndexOf(headers, col)).ToArray();
        }

        // Process each data row
        while ((line = reader.ReadLine()) != null)
        {
            var values = line.Split(',');
            for (int i = 0; i < _targetColumns.Length; i++)
            {
                var colName = _targetColumns[i];
                int index = targetIndices[i];
                // Set value or null if column is missing/out of bounds
                output.Set<string>(colName, index >= 0 && index < values.Length ? values[index] : null);
            }
            yield return output.AsReadOnly();
        }
    }
}

Then use it in your USQL script:

// Reference your custom assembly if it's compiled separately
// REFERENCE ASSEMBLY YourCustomExtractorAssembly;

DECLARE @inputFile string = "/input/daily_file.csv";
DECLARE @targetCols = new string[]{"CustomerID", "OrderDate", "TotalAmount"};

@extractedData =
    EXTRACT 
        CustomerID string,
        OrderDate string,
        TotalAmount string
    FROM @inputFile
    USING new TargetColumnExtractor(@targetCols);

OUTPUT @extractedData
TO "/output/custom_processed_data.csv"
USING Outputters.Csv();

Quick Recommendation #

Start with the first method (EXTRACT * + targeted SELECT)—it's the simplest and works for 90% of cases where you just need to ignore new columns. Use the second method if you need to handle missing columns, and the third only when you need full control over parsing logic.

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