spark sql嵌套查询语法问题

Question

spark sql中运行报错

SQL：

SELECT his.name, his.oid FROM his_data_zadd AS his WHERE his.value=(SELECT MAX(temp_t.value) FROM his_data_zadd AS temp_t)

报错信息：

py4j.protocol.Py4JJavaError: An error occurred while calling o32.sql.
: java.lang.RuntimeException: [1.76] failure: ``)'' expected but identifier MAX found

SELECT his.name, his.oid FROM his_data_zadd AS his WHERE his.value=(SELECT MAX(temp_t.value) FROM his_data_zadd AS temp_t)

是spark sql的语法解析器不支持这种写法？还是我的sql语句原本就有问题？

Answer 1

试下把SQLContext换成HiveContext

Answer 2

spark2.2.1 spark sql支持hive语法，支持子查询
比如

SELECT aa.user_id,
       aa.buyTimes,
       aa.sumOrderAmount
FROM  (SELECT user_id,
              Count(1)          AS buyTimes,
              Sum(a.real_price) AS sumOrderAmount,
              Max(real_price)   AS maxPrice
       FROM   global_temp.order a
       WHERE  1 = 1
          AND a.user_id = (SELECT Max(temp.user_id)
                           FROM   global_temp.order AS temp)
          AND a.status != 0
       GROUP  BY a.user_id)aa
WHERE  1 = 1
LIMIT  50

能正确执行