SparkIllegalArgumentException正则组索引错误：Databricks SQL查询求助

在Databricks Notebook中运行包含复杂正则表达式的SQL查询时，抛出错误：SparkIllegalArgumentException: Regex group count is 0, but the specified group index is 1

原查询代码：

%sql
    SELECT * from (
    SELECT
        DISTINCT
        md.asset_identity_serial AS serial_number,
        md.pigeon_processed_timestamp AS ir_date,
        ir.pagecount AS lifetime_page_count,
        ir.recordid,
        TRIM(ir.serialnum) AS supply_serial,
        CASE
            WHEN panelmessage RLIKE '\\[.+\\]' AND REGEXP_EXTRACT(panelmessage, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) IS NOT NULL
                THEN CAST(REGEXP_EXTRACT(panelmessage, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) AS DECIMAL(10,2))
            WHEN panelmessage RLIKE '^\\d+\\.\\d+[[:alpha:]]*' THEN CAST(REGEXP_EXTRACT(panelmessage, '^\\d+\\.\\d+[[:alpha:]]*') AS DECIMAL(10,2))
            WHEN panelmessage RLIKE '\\d+\\.\\d+[[:alpha:]]*$' THEN CAST(REGEXP_EXTRACT(panelmessage, '\\d+\\.\\d+[[:alpha:]]*$') AS DECIMAL(10,2))
            WHEN (NOT panelmessage RLIKE '\\[.+\\]' OR panelmessage IS NULL) AND eventname RLIKE '\\[.+\\]' AND REGEXP_EXTRACT(eventname, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) IS NOT NULL
                THEN CAST(REGEXP_EXTRACT(eventname, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) AS DECIMAL(10,2))
            WHEN (NOT panelmessage RLIKE '^\\d+\\.\\d+[[:alpha:]]*' OR panelmessage IS NULL) AND eventname RLIKE '^\\d+\\.\\d+[[:alpha:]]*' THEN CAST(REGEXP_EXTRACT(eventname, '^\\d+\\.\\d+[[:alpha:]]*') AS DECIMAL(10,2))
            WHEN (NOT panelmessage RLIKE '\\d+\\.\\d+[[:alpha:]]*$' OR panelmessage IS NULL) AND eventname RLIKE '\\d+\\.\\d+[[:alpha:]]*$' THEN CAST(REGEXP_EXTRACT(eventname, '\\d+\\.\\d+[[:alpha:]]*$') AS DECIMAL(10,2))
            ELSE NULL
        END AS error_code,
        CASE
            WHEN error_code IS NULL THEN NULL
            WHEN error_code = '000.00' THEN error_code
            ELSE REPLACE(LTRIM(REPLACE(error_code, '0', ' ')), ' ', '0')
        END AS error_code_trimmed,
        CASE
            WHEN POSITION('.' IN error_code_trimmed) > 0 THEN REGEXP_EXTRACT(error_code_trimmed, '\\d+\\.\\d+[^[:alpha:]]*', 0)
            ELSE error_code_trimmed
        END AS error_code_simplified,
        CASE
            WHEN POSITION('.' IN error_code_simplified) > 0 THEN SUBSTRING(error_code_simplified, 1, POSITION('.' IN error_code_simplified) - 1)
            ELSE error_code_simplified
        END AS yyy,
        CASE
            WHEN LENGTH(yyy) = 3 THEN SUBSTRING(yyy, 1, 2)
            ELSE yyy
        END AS yy,
        CASE
            WHEN LENGTH(yy) = 2 THEN SUBSTRING(yy, 1, 1)
            ELSE yy
        END AS y
    FROM
        qa.pigeon_silver.ael_events_supply_ir_warning AS ir
    INNER JOIN
        qa.pigeon_silver.ael_events_device_collector_metadata AS md
    ON
        md.s3_file_location = ir.s3_file_location
    WHERE
        md.pigeon_processed_timestamp >= DATEADD(DAY, -365, CURRENT_DATE)
        AND TRIM(ir.serialnum) IS NOT NULL AND TRIM(ir.serialnum) <> ''
        AND SUBSTRING(TRIM(ir.serialnum), 1, 2) IN ('CA')
        AND LENGTH(ir.serialnum) = 12
        AND (
            panelmessage RLIKE '\\[\\d+.*]|^\\d+\\.\\d+[[:alpha:]]*|\\d+\\.\\d+[[:alpha:]]*$'
            OR eventname RLIKE '\\[\\d+.*]|^\\d+\\.\\d+[[:alpha:]]*|\\d+\\.\\d+[[:alpha:]]*$'
        )
        AND (
            (LENGTH(md.asset_identity_serial) = 7
            AND SUBSTRING(md.asset_identity_serial, 1, 2) IN ('01','02','04','05','06','07','08','09','11','23','24','25','26','27','32','35','41','58','59','62','67','68','69','72','79','89','94','98','99'))
            OR (LENGTH(md.asset_identity_serial) = 13 AND SUBSTRING(md.asset_identity_serial, 1, 2) IN ('14','15','34','35','40','44','46','50','70','71','74','75'))
        )) WHERE yyy in ('32')

错误原因 #

REGEXP_EXTRACT函数若未指定分组索引，Spark默认会使用索引1，但以下几个正则表达式未定义任何捕获分组（无()包裹的子表达式）：

• ^\\d+\\.\\d+[[:alpha:]]*
• \\d+\\.\\d+[[:alpha:]]*$

当Spark尝试提取索引1的分组时，因正则无分组导致报错。

修正方案 #

对这些无分组的正则匹配，明确指定分组索引为0（匹配整个正则表达式的结果），以下是修正后的完整查询：

%sql
    SELECT * from (
    SELECT
        DISTINCT
        md.asset_identity_serial AS serial_number,
        md.pigeon_processed_timestamp AS ir_date,
        ir.pagecount AS lifetime_page_count,
        ir.recordid,
        TRIM(ir.serialnum) AS supply_serial,
        CASE
            WHEN panelmessage RLIKE '\\[.+\\]' AND REGEXP_EXTRACT(panelmessage, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) IS NOT NULL
                THEN CAST(REGEXP_EXTRACT(panelmessage, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) AS DECIMAL(10,2))
            WHEN panelmessage RLIKE '^\\d+\\.\\d+[[:alpha:]]*' THEN CAST(REGEXP_EXTRACT(panelmessage, '^\\d+\\.\\d+[[:alpha:]]*', 0) AS DECIMAL(10,2))
            WHEN panelmessage RLIKE '\\d+\\.\\d+[[:alpha:]]*$' THEN CAST(REGEXP_EXTRACT(panelmessage, '\\d+\\.\\d+[[:alpha:]]*$', 0) AS DECIMAL(10,2))
            WHEN (NOT panelmessage RLIKE '\\[.+\\]' OR panelmessage IS NULL) AND eventname RLIKE '\\[.+\\]' AND REGEXP_EXTRACT(eventname, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) IS NOT NULL
                THEN CAST(REGEXP_EXTRACT(eventname, '\\[([0-9.]+)([A-Za-z]+)?\\]', 1) AS DECIMAL(10,2))
            WHEN (NOT panelmessage RLIKE '^\\d+\\.\\d+[[:alpha:]]*' OR panelmessage IS NULL) AND eventname RLIKE '^\\d+\\.\\d+[[:alpha:]]*' THEN CAST(REGEXP_EXTRACT(eventname, '^\\d+\\.\\d+[[:alpha:]]*', 0) AS DECIMAL(10,2))
            WHEN (NOT panelmessage RLIKE '\\d+\\.\\d+[[:alpha:]]*$' OR panelmessage IS NULL) AND eventname RLIKE '\\d+\\.\\d+[[:alpha:]]*$' THEN CAST(REGEXP_EXTRACT(eventname, '\\d+\\.\\d+[[:alpha:]]*$', 0) AS DECIMAL(10,2))
            ELSE NULL
        END AS error_code,
        CASE
            WHEN error_code IS NULL THEN NULL
            WHEN error_code = '000.00' THEN error_code
            ELSE REPLACE(LTRIM(REPLACE(error_code, '0', ' ')), ' ', '0')
        END AS error_code_trimmed,
        CASE
            WHEN POSITION('.' IN error_code_trimmed) > 0 THEN REGEXP_EXTRACT(error_code_trimmed, '\\d+\\.\\d+[^[:alpha:]]*', 0)
            ELSE error_code_trimmed
        END AS error_code_simplified,
        CASE
            WHEN POSITION('.' IN error_code_simplified) > 0 THEN SUBSTRING(error_code_simplified, 1, POSITION('.' IN error_code_simplified) - 1)
            ELSE error_code_simplified
        END AS yyy,
        CASE
            WHEN LENGTH(yyy) = 3 THEN SUBSTRING(yyy, 1, 2)
            ELSE yyy
        END AS yy,
        CASE
            WHEN LENGTH(yy) = 2 THEN SUBSTRING(yy, 1, 1)
            ELSE yy
        END AS y
    FROM
        qa.pigeon_silver.ael_events_supply_ir_warning AS ir
    INNER JOIN
        qa.pigeon_silver.ael_events_device_collector_metadata AS md
    ON
        md.s3_file_location = ir.s3_file_location
    WHERE
        md.pigeon_processed_timestamp >= DATEADD(DAY, -365, CURRENT_DATE)
        AND TRIM(ir.serialnum) IS NOT NULL AND TRIM(ir.serialnum) <> ''
        AND SUBSTRING(TRIM(ir.serialnum), 1, 2) IN ('CA')
        AND LENGTH(ir.serialnum) = 12
        AND (
            panelmessage RLIKE '\\[\\d+.*]|^\\d+\\.\\d+[[:alpha:]]*|\\d+\\.\\d+[[:alpha:]]*$'
            OR eventname RLIKE '\\[\\d+.*]|^\\d+\\.\\d+[[:alpha:]]*|\\d+\\.\\d+[[:alpha:]]*$'
        )
        AND (
            (LENGTH(md.asset_identity_serial) = 7
            AND SUBSTRING(md.asset_identity_serial, 1, 2) IN ('01','02','04','05','06','07','08','09','11','23','24','25','26','27','32','35','41','58','59','62','67','68','69','72','79','89','94','98','99'))
            OR (LENGTH(md.asset_identity_serial) = 13 AND SUBSTRING(md.asset_identity_serial, 1, 2) IN ('14','15','34','35','40','44','46','50','70','71','74','75'))
        )) WHERE yyy in ('32')

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