Azure SQL DB中基于正则快速拆分4亿条记录的编码列至多列

I'm working with an Azure SQL Database containing around 400 million records where I need to decode values from an encoded_val column into 6 target columns (de1 to de6). The encoding format isn't fixed—each segment can vary in length, and some segments might be missing, but I already have a regex pattern that can parse any of these strings correctly.

Example Data & Expected Output #

Raw data:

Expected parsed result:

What I've Tried So Far #

1. Computed Columns: These only let me construct new columns from existing values using basic functions—they don't support flexible regex pattern matching needed here.
2. User-Defined Functions (UDFs): I haven't fully figured out the implementation yet, but this seems like a possible path.
3. Complex Built-in Functions: I tried using nested SUBSTRING/PATINDEX calls like this, but it's extremely hard to maintain and the execution time is completely unacceptable for 400 million rows:

   UPDATE [bd].[table] 
   SET unitid = CAST(LEFT(SUBSTRING([colname], PATINDEX('%[0-9]%', [colname]), 100), 
                          PATINDEX('%[^0-9]%', SUBSTRING([colname], PATINDEX('%[0-9]%', [colname]), 100) + '*') - 1) 
                    AS SMALLINT);
   

My Question #

How can I efficiently implement this regex-based parsing for 400 million records in Azure SQL DB without sacrificing performance or maintainability?

Great question—handling 400 million rows requires balancing correctness, maintainability, and performance, especially with variable-format regex parsing. Here are the best approaches tailored for Azure SQL DB:

1. Use Native REGEXP_SUBSTR (Recommended for Azure SQL DB v12+) #

Azure SQL DB supports the REGEXP_SUBSTR function natively now, which lets you extract regex matches directly without messy nested string functions. This is far more maintainable and performs better than manual PATINDEX chains.

Example Implementation #

Assuming your regex pattern captures each segment into groups (adjust the pattern to match your full set of encoding rules), you can write a batch update to process rows in chunks (critical for large datasets):

-- Define your regex pattern with capture groups aligned to de1-de6
DECLARE @RegexPattern NVARCHAR(100) = '^([A-Z]+)([0-9]+)([A-Z0-9]+)_([0-9]+)$';

-- Batch update to avoid table locks and excessive log usage
WHILE 1 = 1
BEGIN
    UPDATE TOP (100000) [bd].[table]
    SET 
        de1 = REGEXP_SUBSTR(encoded_val, @RegexPattern, 1, 1, 'i'),
        de2 = REGEXP_SUBSTR(encoded_val, @RegexPattern, 1, 2, 'i'),
        de3 = REGEXP_SUBSTR(encoded_val, @RegexPattern, 1, 3, 'i'),
        de5 = REGEXP_SUBSTR(encoded_val, @RegexPattern, 1, 4, 'i')
        -- Set de4/de6 to NULL explicitly, or adjust the regex to capture those groups if needed
    WHERE de1 IS NULL; -- Only process unparsed rows

    IF @@ROWCOUNT = 0 BREAK;
END

Key Notes:

• Batch Size: Adjust TOP (100000) based on your database's resource limits—too large and you risk long transactions, too small and you'll add unnecessary overhead.
• Regex Flexibility: Use optional groups (e.g., ([A-Z]+)?) in your pattern to handle missing segments; REGEXP_SUBSTR will return NULL for unmatched groups.
• Performance: Native regex functions are optimized for SQL Server's execution engine, so they'll outperform custom scalar UDFs by a wide margin.

2. CLR Table-Valued Function (For Advanced Regex Scenarios) #

If your regex needs features not supported by REGEXP_SUBSTR (like lookarounds or complex backreferences), a CLR table-valued function (TVF) is your best bet. CLR code runs in managed .NET, which is faster than T-SQL for regex operations, and TVFs avoid the performance hit of scalar UDFs.

Steps to Implement: #

1. Write the CLR Function: Use C#/VB to create a function that parses the string with System.Text.RegularExpressions and returns structured values.

   using System.Data.SqlTypes;
   using System.Text.RegularExpressions;
   using Microsoft.SqlServer.Server;
   using System.Collections;
   
   public class EncodedParser
   {
       [SqlFunction(FillRowMethodName = "FillParsedColumns", 
                   TableDefinition = "de1 NVARCHAR(50), de2 NVARCHAR(50), de3 NVARCHAR(50), de4 NVARCHAR(50), de5 NVARCHAR(50), de6 NVARCHAR(50)")]
       public static IEnumerable ParseEncodedValue(SqlString encodedVal)
       {
           if (encodedVal.IsNull) yield break;
   
           var pattern = @"^([A-Z]+)([0-9]+)([A-Z0-9]+)_([0-9]+)$";
           var match = Regex.Match(encodedVal.Value, pattern, RegexOptions.IgnoreCase);
   
           yield return new ParsedResult(
               match.Groups[1].Success ? match.Groups[1].Value : null,
               match.Groups[2].Success ? match.Groups[2].Value : null,
               match.Groups[3].Success ? match.Groups[3].Value : null,
               null, // Assign de4 value if your regex captures it
               match.Groups[4].Success ? match.Groups[4].Value : null,
               null  // Assign de6 value if your regex captures it
           );
       }
   
       private class ParsedResult
       {
           public string De1 { get; }
           public string De2 { get; }
           public string De3 { get; }
           public string De4 { get; }
           public string De5 { get; }
           public string De6 { get; }
   
           public ParsedResult(string de1, string de2, string de3, string de4, string de5, string de6)
           {
               De1 = de1;
               De2 = de2;
               De3 = de3;
               De4 = de4;
               De5 = de5;
               De6 = de6;
           }
       }
   
       public static void FillParsedColumns(object obj, out SqlString de1, out SqlString de2, out SqlString de3, out SqlString de4, out SqlString de5, out SqlString de6)
       {
           var result = (ParsedResult)obj;
           de1 = result.De1 ?? SqlString.Null;
           de2 = result.De2 ?? SqlString.Null;
           de3 = result.De3 ?? SqlString.Null;
           de4 = result.De4 ?? SqlString.Null;
           de5 = result.De5 ?? SqlString.Null;
           de6 = result.De6 ?? SqlString.Null;
       }
   }
   

2. Deploy to Azure SQL DB: Enable CLR integration, deploy the compiled assembly, and create the TVF:

   sp_configure 'clr enabled', 1;
   RECONFIGURE;
   
   CREATE ASSEMBLY EncodedParserAssembly
   FROM 'C:\Path\To\Your\Compiled.dll'
   WITH PERMISSION_SET = SAFE;
   
   CREATE FUNCTION dbo.ParseEncodedValue(@encodedVal NVARCHAR(MAX))
   RETURNS TABLE (de1 NVARCHAR(50), de2 NVARCHAR(50), de3 NVARCHAR(50), de4 NVARCHAR(50), de5 NVARCHAR(50), de6 NVARCHAR(50))
   AS EXTERNAL NAME EncodedParserAssembly.EncodedParser.ParseEncodedValue;
   

3. Batch Update with the TVF:

   WHILE 1 = 1
   BEGIN
       UPDATE t
       SET 
           t.de1 = p.de1,
           t.de2 = p.de2,
           t.de3 = p.de3,
           t.de5 = p.de5
       FROM TOP (100000) [bd].[table] t
       CROSS APPLY dbo.ParseEncodedValue(t.encoded_val) p
       WHERE t.de1 IS NULL;
   
       IF @@ROWCOUNT = 0 BREAK;
   END
   

3. Pre-Processing with Distributed Tools (For Extreme Scale) #

If even native/CLR regex operations are too slow, consider offloading parsing to a distributed system:

1. Export the encoded_val column to Azure Blob Storage.
2. Use Azure Data Factory or Apache Spark to parse the data with regex transformations (Spark’s regex functions are highly optimized for large datasets).
3. Reload the parsed data back into your SQL DB.

Final Performance Tips #

• Indexing: Add a non-clustered index on de1 to speed up the WHERE de1 IS NULL filter in batch updates.
• Log Management: Temporarily switch your database to simple recovery mode during the update to minimize transaction log growth (remember to switch back afterward).
• Test First: Validate your regex pattern and update logic on a small subset of data before running against the full 400 million rows.

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