如何用Hive SQL替代PostgreSQL的array_position实现数组对应取值？

问题背景 #

原PostgreSQL代码通过array_position函数找到word1数组中"location"的位置，再以此位置取出word2数组对应的值。Hive没有直接的array_position函数，但可以通过posexplode等函数实现相同逻辑。

步骤1：Hive中创建表并插入数据 #

首先适配Hive的数组语法（Hive数组类型为array<string>，用array()构造）：

CREATE TABLE logs (
  word1 array<string>,    
  word2 array<string>
);

INSERT INTO logs (word1, word2) VALUES 
  (array('location','title','value'), array('moskow','title353','34')),
  (array('title','value','location'), array('title5653','574584','sidney'));

步骤2：实现等效查询逻辑 #

利用posexplode拆分数组获取元素位置（Hive中位置从0开始，需加1转为PostgreSQL的1-based索引），再关联原表得到结果：

WITH location_pos AS (
    SELECT 
        row_number() OVER () AS rn,
        pos + 1 AS location_position,
        word2[pos] AS location_value
    FROM logs
    LATERAL VIEW posexplode(word1) exploded AS pos, val
    WHERE val = 'location'
)
SELECT 
    l.*, 
    lp.location_position, 
    lp.location_value
FROM logs l
JOIN location_pos lp 
    ON row_number() OVER () = lp.rn;

处理多匹配场景 #

如果word1数组中可能存在多个"location"，只需取第一个出现的位置，可调整CTE部分：

WITH location_pos AS (
    SELECT 
        rn,
        first_value(pos + 1) OVER (PARTITION BY rn ORDER BY pos) AS location_position,
        first_value(word2[pos]) OVER (PARTITION BY rn ORDER BY pos) AS location_value
    FROM (
        SELECT 
            row_number() OVER () AS rn,
            pos,
            val,
            word2
        FROM logs
        LATERAL VIEW posexplode(word1) exploded AS pos, val
    ) t
    WHERE val = 'location'
)
SELECT 
    l.*, 
    lp.location_position, 
    lp.location_value
FROM logs l
JOIN (SELECT DISTINCT rn, location_position, location_value FROM location_pos) lp 
    ON row_number() OVER () = lp.rn;

说明 #

• posexplode：将数组拆分为多行，每行包含元素的0-based位置和元素值。
• 索引转换：通过pos + 1对齐PostgreSQL的1-based索引规则。
• 关联优化：若表有唯一主键，建议用主键替代row_number()生成的行号，保证关联逻辑更可靠。

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