sql - View 中的 Hive 多个 CTE

Question

Hive 是否支持 VIEW 语句中的多个 CTE？我知道 Hive 支持 CTE (HIVE-1180)，但不确定它是否有任何限制——例如，在 VIEW 语句中支持多个 CTE:

CREATE OR REPLACE VIEW prdviews.temp
AS WITH set1 AS (SELECT 1 AS id FROM testdb.table1), 
        set2 AS (select 2 AS id FROM testdb.table2)
SELECT * FROM set1
UNION ALL
SELECT * FROM set2;

select * from prdviews.temp;

执行以上查询成功创建 Hive View 。但是当我运行 SELECT 查询时，它返回错误。还是我做错了？上面的查询或任何类似的查询返回类似这样的错误:

Error while compiling statement: FAILED: SemanticException Line 3:24 Table not found 'set1' in definition of VIEW temp [ WITH set1 AS (SELECT 1 AS `id` FROM `testdb`.`table1`), set2 AS (select 2 AS `id` FROM `testdb`.`table2`) SELECT `set1`.`id` FROM set1 UNION ALL SELECT `set2`.`id` FROM set2 ] used as temp at Line 1:14

Answer 1

下面是在 CDH 5x 中工作

with t1 as(select * from empinfo where empid=101),t2 as(select * from empinfo where empid=102) select * from t1 union all select * from t2;

还有下面的

select * from empinfo where empid=101 union all select * from empinfo where empid=102;