Spark 1.6中SQL子查询使用及不兼容查询转换方案咨询

针对你的两个问题，我来逐一解答：

1. Spark 1.6中SQL子查询的使用规则 #

Spark 1.6的HiveContext对SQL子查询的支持有明确限制，核心规则如下：

• 仅支持FROM子句中的非相关子查询：也就是子查询不依赖外部查询的字段，比如派生表的写法：

  SELECT * FROM (SELECT device_id FROM `table1`) AS sub_table
  

• 不支持WHERE/ON/HAVING子句中的相关子查询：如果子查询引用了外部查询的字段（比如你的原查询中ON子句里引用ne.device_id的子查询），Spark 1.6的优化器无法解析这类依赖关系，会抛出AnalysisException。
• 如果需要实现类似相关子查询的逻辑，通常可以用窗口函数、JOIN+聚合函数来替代，这也是Spark 1.6支持的特性。

2. 转换不支持子查询的查询为Spark 1.6兼容版本 #

你的原查询目标是：关联table1和table2，仅获取table2中匹配table1.device_id前缀、且按device_hostname降序排列的第一条记录。由于原查询使用了ON子句中的相关子查询，Spark 1.6无法运行，我们可以用**窗口函数ROW_NUMBER()**来改写，具体方案如下：

兼容版SQL语句 #

SELECT device_id, device_hostname
FROM (
    SELECT 
        ne.device_id,
        sp.device_hostname,
        -- 按device_id分组，对每组的device_hostname降序排序，标记行号
        ROW_NUMBER() OVER (PARTITION BY ne.device_id ORDER BY sp.device_hostname DESC) AS row_num
    FROM `table1` ne
    -- 先做模糊匹配关联，保留所有可能的匹配记录
    INNER JOIN `table2` sp 
        ON sp.device_hostname LIKE CONCAT(ne.device_id, '%')
) AS temp_table
-- 筛选每组的第一条记录（即降序后的最后一条匹配记录）
WHERE row_num = 1

逻辑说明 #

1. 先通过INNER JOIN将table1和table2按模糊匹配条件关联，得到所有符合前缀规则的记录对；
2. 使用ROW_NUMBER()窗口函数，以ne.device_id为分组依据，对每组内的sp.device_hostname降序排序，给每条记录分配一个行号；
3. 最后筛选出行号为1的记录，也就是每个device_id对应的table2中最后一条（降序第一条）匹配记录。

可选优化（针对性能） #

如果table2的device_hostname前缀格式固定（比如device_id是device_hostname的前N位），可以用SUBSTRING替代模糊匹配，减少计算开销：

-- 假设device_id的长度固定，或者用LENGTH(ne.device_id)动态获取长度
INNER JOIN `table2` sp 
    ON SUBSTRING(sp.device_hostname, 1, LENGTH(ne.device_id)) = ne.device_id

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