在Hive中是否有与SQL WITH子句等效的语法？

Question

在规范中找不到答案。

因此，我想知道：我是否可以在Hive中做类似的事情？

insert into table my_table
with a as
(
    select *
        from ...
        where ...  
),

b as
(
    select *
        from ...
        where ...  
)
select 
    a.a, 
    a.b, 
    a.c, 
    b.a, 
    b.b, 
    b.c 
from a join b on (a.a=b.a);

- aviad

Answer 1

从Hive版本 0.13.0 开始提供了 With 语句。这里有使用文档。

- John McDonnell

Answer 2

我猜你可以总是使用子查询：

insert into table my_table
select 
    a.a, 
    a.b, 
    a.c, 
    b.a, 
    b.b, 
    b.c 
from
(
    select *
        from ...
        where ...  
) a
join 
(
    select *
        from ...
        where ...  
) b
on a.a = b.a;

- Santiago Cepas

Answer 3

Hadoop Hive WITH Clause Syntax and Examples 使用Hive WITH语句，您可以在同一查询中重复使用查询结果的部分。您还可以使用WITH子句来改进Hadoop Hive查询。通过将复杂、重复的代码移至WITH子句并引用在SELECT语句中创建的逻辑表，可以简化查询。

---

使用SELECT语句的Hive WITH子句示例

WITH t1 as (SELECT 1), 
t2 as (SELECT 2),
t3 as (SELECT 3)
SELECT * from t1 
UNION ALL
SELECT * from t2
UNION ALL 
SELECT * from t3;

Hive中的WITH子句在INSERT语句中的应用 您可以在向表中插入数据时使用WITH子句。例如：

WITH t11 as (SELECT 10),
t12 as (SELECT 20),
t13 as (SELECT 3) 
INSERT INTO t1 
SELECT * from t11 
UNION ALL 
SELECT * from t12 
UNION ALL 
SELECT * from t13;

- Ram Ghadiyaram