Hive SQL拆分竖线分隔列：提取HP1段指定元素方案

我的Hive表中有一个竖线分隔的高度非结构化列，需要拆分为多列，核心需求是：先提取以HP1开头的子串，再定位该子串中第30至40个竖线分隔的元素。尝试两种方法均报错：

• 第一种用CharIndex函数，但Hive不支持该函数；
• 第二种用SPLIT拆分后直接索引数组，执行失败。

请问Hive SQL中有可行方案吗？是否需要动态SQL？若需要该如何实现？

示例数据与尝试代码 #

示例数据 #

SSH|^~\&|EnsembleMK9|ISC|DELTA HIE^1.2.3.411593.135778^DEL|TESTMD|202209190035||ACK^A08|1214451793^137099156|P|2.5.1MSA|AA|1214451793^137099156
SSH|^~\&|DELTA HIE^1.2.3.411593.135778^DEL|PCCMM|LEAG^2.16.840.1.113883.3.2966.100.0.0.255.140^DEL|HIVEDBAKG^2.16.840.1.113883.3.2966.100.0.0.255.140^DEL|20220919053530.832||AKG^A08^AKG_A01|C1016708390|P|2.5.1|||NE||||||@SSH.3^NXG^2.16.840.1.113883.3.2966.1000.1005.152.4.264^DEL~@SSH.4^PCCMM^2.16.840.1.113883.3.2966.100.0.0.255.124^DELEVN||20220919013529PID|1||5624118^^^PCCMM&2.16.840.1.113883.3.8932.101.2&DEL^MR||Anderson^Donald||19440711|M||2106-3^White|30 Pine Woods Road^^Hyde Park^NY^12538^USA||^PRS^PH^^1^845^2292777||^English|M||||||^Non-Hispanic||||||||N|||||||||PD1||||AA48^Diminico^Carlo^F^^^^^&2.16.840.1.113883.3.8932.101.4&DEL^^^^^^^^^^^^^&&&&&&&&&PCP||||||||N|20220310ROL||AD|RCP|2022062001^^^^^^^^2.16.840.1.113883.4.6^^^^KOR^^^^^^^^HP|1|O|PKCL||||AA48^Diminico^Carlo^F^^^^^&2.16.840.1.113883.3.8932.101.4&DEL^^^^^^^^^^^^||||||||||||73828528~86654484|||||||||||||||||||||||||202209190135
SSH|^~\&|PC^1.2.3.411593.135778^DEL|PC|LEAG^2.16.840.1.113883.3.2966.100.0.0.255.140^DEL|HIVEDBAKG^2.16.840.1.113883.3.2966.100.0.0.255.140^DEL|20220919053530.832||AKG^A08^AKG_A01|C1016708390|P|2.5.1|||NE||||||@SSH.3^NXG^2.16.840.1.113883.3.2966.1000.1005.152.4.264^DEL~@SSH.4^PCCMM^2.16.840.1.113883.3.2966.100.0.0.255.124^DELEVN||20220919013529PID|1||5624118^^^PCCMM&2.16.840.1.113883.3.8932.101.2&DEL^MR||Anderson^Donald||19440711|M||2106-3^White|30 Pine Woods Road^^Hyde Park^NY^12538^USA||^PRS^PH^^1^845^2292777||^English|M||||||^Non-Hispanic||||||||N|||||||||PD1||||AA48^Diminico^Carlo^F^^^^^&2.16.840.1.113883.3.8932.101.4&DEL^^^^^^^^^^^^^&&&&&&&&&PCP||||||||N|20220310ROL||AD|RCP|2022062001^^^^^^^^2.16.840.1.113883.4.6^^^^KOR^^^^^^^^HP|1|O|PKCL||||AA48^Diminico^Carlo^F^^^^^&2.16.840.1.113883.3.8932.101.4&DEL^^^^^^^^^^^^||||||||||||73828528~86654484||||||||||||||||||||PCCMM|||||202209190135

第一次尝试代码 #

WITH x AS (
  SELECT colm
       , CharIndex('|', colm) As first_pipe
  FROM   mytbl
)
, y AS (
  SELECT colm
       , first_pipe
       , CharIndex('|', colm, first_pipe + 1) As second_pipe
       , SubString(colm, 0, first_pipe) As first_element
  FROM   x
)
, z AS (
  SELECT colm
       , first_pipe
       , second_pipe
       , first_element
       , SubString(colm, first_pipe  + 1, second_pipe - first_pipe - 1) As second_element
       , SubString(colm, second_pipe + 1, LENGTH(colm) - second_pipe) As third_element
  FROM   y
)
SELECT *
FROM z
limit 10

第二次尝试代码 #

SELECT
    colm,
    INSTR(colm, 'HP1') AS HP1_position,   -- Find the position of "HP1" in the string
    SUBSTR(colm, INSTR(colm, 'HP1')) AS substring_after_HP1,    -- Extract the substring after "HP1" until the end
    SPLIT(SUBSTR(colm, INSTR(colm, 'HP1')), '\\|') AS pipe_split,       -- Split the substring by the pipe "|" delimiter
    pipe_split[39] AS extracted_value       -- Extract the 40th element from the split data
FROM
    mytbl

不需要动态SQL，普通Hive SQL即可实现需求，以下是具体方案：

1. 替代CharIndex函数：用Hive原生的INSTR #

Hive不支持SQL Server的CharIndex，但可以用INSTR函数实现相同功能，语法为：

INSTR(str, substr[, start_pos[, occurrence]])

• str：目标字符串
• substr：要查找的子串
• start_pos：可选，开始查找的位置（默认1）
• occurrence：可选，要查找的第N次出现（默认1）

2. 正确拆分并索引数组 #

Hive中SPLIT函数返回的数组是0索引的，即第一个元素索引为0，第40个元素对应索引39。另外需要注意：

• 如果目标子串中竖线数量不足，直接索引会返回NULL，可以用COALESCE处理空值
• 示例数据中实际是HP|开头的子串，而非HP1，需根据实际数据调整匹配规则

完整实现SQL #

SELECT
    colm,
    -- 定位HP1开头的子串位置（如果是HP|则改为'HP|'）
    INSTR(colm, 'HP1') AS hp1_start_pos,
    -- 提取HP1开头的子串到末尾
    CASE WHEN INSTR(colm, 'HP1') > 0 THEN SUBSTR(colm, INSTR(colm, 'HP1')) ELSE NULL END AS hp1_substr,
    -- 拆分子串为数组
    CASE WHEN INSTR(colm, 'HP1') > 0 THEN SPLIT(SUBSTR(colm, INSTR(colm, 'HP1')), '\\|') ELSE NULL END AS hp1_split_arr,
    -- 提取第30到40个元素（对应索引29到39）
    COALESCE(SPLIT(SUBSTR(colm, INSTR(colm, 'HP1')), '\\|')[29], '') AS element_30,
    COALESCE(SPLIT(SUBSTR(colm, INSTR(colm, 'HP1')), '\\|')[30], '') AS element_31,
    -- 以此类推直到element_40
    COALESCE(SPLIT(SUBSTR(colm, INSTR(colm, 'HP1')), '\\|')[39], '') AS element_40
FROM
    mytbl;

优化：避免重复计算 #

如果数据量较大，重复调用INSTR和SPLIT会影响性能，可以用CTE提前计算中间结果：

WITH temp AS (
    SELECT
        colm,
        INSTR(colm, 'HP1') AS hp1_start_pos,
        CASE WHEN INSTR(colm, 'HP1') > 0 THEN SUBSTR(colm, INSTR(colm, 'HP1')) ELSE NULL END AS hp1_substr,
        CASE WHEN INSTR(colm, 'HP1') > 0 THEN SPLIT(SUBSTR(colm, INSTR(colm, 'HP1')), '\\|') ELSE NULL END AS hp1_split_arr
    FROM mytbl
)
SELECT
    colm,
    hp1_start_pos,
    hp1_substr,
    COALESCE(hp1_split_arr[29], '') AS element_30,
    COALESCE(hp1_split_arr[30], '') AS element_31,
    -- ...
    COALESCE(hp1_split_arr[39], '') AS element_40
FROM temp;

注意事项 #

• 如果实际数据中HP1后面可能还有其他分隔符（比如换行或其他标记），需要调整SUBSTR的结束位置，比如用INSTR找到下一个分隔符的位置来截断子串
• 若数组长度不足，COALESCE可以替换为你需要的默认值（比如空字符串或特定标记）

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