Hive中RLIKE多条件匹配未返回预期结果的语法排查求助

嗨，我来帮你排查这个Hive SQL的问题！你遇到的情况大概率是RLIKE正则表达式里的两个小细节出错了，咱们一步步拆解：

• 多余空格干扰匹配：你写的正则表达式里，很多药物名称前后带了不必要的空格，比如' amoxicillin'（前面多空格）、'ampicillin-sulbactam '（后面多空格）。Hive的RLIKE是严格匹配正则模式的，这意味着只有当DESCRIPTION字段里的药物名称正好带着这些空格时才会被命中，而实际数据里的药物名称肯定是没有这些额外空格的，所以大部分药物都匹配不到。
• 大小写敏感问题：正则里的部分药物名称是大小写混合的（比如Amphotericin B、Cloxacillin），而Hive的RLIKE默认区分大小写。如果你的DESCRIPTION字段里的药物名称是全小写或其他大小写格式（比如amphotericin b），这部分就会匹配失败。

针对这两个问题，我们可以调整正则表达式，同时统一处理大小写来避免匹配遗漏：

CREATE TABLE tsri.antibiotics AS 
SELECT * 
FROM observation_fact_meds 
WHERE start_date > "2020-12-17" 
  AND encounter_num IN (
    SELECT DISTINCT encounter_num 
    FROM visit_dimension 
    WHERE patient_num IN ('000000', '000001', '000002', '000003', '000004', '000006', '000007') 
      AND INOUT_CD IN ('Inpatient')
  ) 
  -- 统一转小写后匹配，同时去掉正则里的多余空格
  AND LOWER(DESCRIPTION) RLIKE ('amikacin|amoxicillin|amoxicillin-clavulanate|amphotericin b|ampicillin|ampicillin-sulbactam|azithromycin|aztreonam|bacitracin|cefazolin|efepime|cefiderocol|cefotaxime|cefoxitin|ceftaroline|ceftazidime|ceftazidime-avibactam|ceftriaxone|cefuroxime|cephalexin|ciprofloxacin|clarithromycin|clindamycin|cloxacillin|cotrimoxazole|dapsone|erythromycin|gentamicin|imipenem|imipenem-cilastatin|isoniazid|lefamulin|levofloxacin|linezolid|meropenem|metronidazole|nafcillin|nystatin|penicillin|pentamidine|piperacillin-tazobactam|piperacillin|rifampin|sulfamethoxazole-trimethoprim|tnf antimicrobial med|tobramycin|vancomycin');

如果你的DESCRIPTION字段存储的是精确的药物名称（没有额外注释或后缀），其实用IN子句会比RLIKE更高效，还能避免模糊匹配带来的误判（比如避免匹配到包含目标药物字符串的其他无关记录）。示例如下：

CREATE TABLE tsri.antibiotics AS 
SELECT * 
FROM observation_fact_meds 
WHERE start_date > "2020-12-17" 
  AND encounter_num IN (
    SELECT DISTINCT encounter_num 
    FROM visit_dimension 
    WHERE patient_num IN ('000000', '000001', '000002', '000003', '000004', '000006', '000007') 
      AND INOUT_CD IN ('Inpatient')
  ) 
  AND LOWER(DESCRIPTION) IN (
    'amikacin', 'amoxicillin', 'amoxicillin-clavulanate', 'amphotericin b', 'ampicillin', 
    'ampicillin-sulbactam', 'azithromycin', 'aztreonam', 'bacitracin', 'cefazolin', 
    'efepime', 'cefiderocol', 'cefotaxime', 'cefoxitin', 'ceftaroline', 'ceftazidime', 
    'ceftazidime-avibactam', 'ceftriaxone', 'cefuroxime', 'cephalexin', 'ciprofloxacin', 
    'clarithromycin', 'clindamycin', 'cloxacillin', 'cotrimoxazole', 'dapsone', 'erythromycin', 
    'gentamicin', 'imipenem', 'imipenem-cilastatin', 'isoniazid', 'lefamulin', 'levofloxacin', 
    'linezolid', 'meropenem', 'metronidazole', 'nafcillin', 'nystatin', 'penicillin', 
    'pentamidine', 'piperacillin-tazobactam', 'piperacillin', 'rifampin', 'sulfamethoxazole-trimethoprim', 
    'tnf antimicrobial med', 'tobramycin', 'vancomycin'
  );

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