hive不支持列子查询

需求：hivesql中有一列动态查询数量

实现：

SELECT
(select  count(*) as cnt from dept)  as  num,
id,
name
from 
users;

报错：

Error while compiling statement: FAILED: ParseException line 29:1 cannot recognize input near '(' 'select' 'count' in expression specification

原因：

​ hive不支持列的子查询，上面写法mysql、oracle是支持的，子查询必须在from之后，当然from之后有些特殊语法也不支持，需要使用 jion或union 语法改写，当然根据hive版本的不同可能写法不同；可自行研究。我遇到下面情况：

-- 可执行
SELECT
(select  count(*) as cnt from dept)  as  num,
id,
name
from 
users;

-- 不可执行
insert overwrite table app.user
SELECT
(select  count(*) as cnt from dept)  as  num,
id,
name
from 
users;

改写后：

SELECT
cnts.cnt as  num,
u.id,
u.name
from 
(select  count(*) as cnt from dept) cnts
join
users u

参考文档：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries