CODEWITHSUNDEEP
sqlhadoophive
aviad
aviad

Reputation: 8278

Is there sql WITH clause equivalent in hive?

Failed to find the answer in the specs.

So, I wonder: Can I do something like that in 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);

Upvotes: 7

Views: 29031

Answers (3)

Ram Ghadiyaram
Ram Ghadiyaram

Reputation: 29165

Hadoop Hive WITH Clause Syntax and Examples With the Help of Hive WITH clause you can reuse piece of query result in same query construct. You can also improve the Hadoop Hive query using WITH clause. You can simplify the query by moving complex, complicated repetitive code to the WITH clause and refer the logical table created in your SELECT statements.

Hive WITH clause example with the SELECT statement

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 Clause in INSERT Statements You can use the WITH clause while inserting data to table. For example:

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;

Upvotes: 2

John McDonnell
John McDonnell

Reputation: 1490

With is available in Hive as of version 0.13.0. Usage documented here.

Upvotes: 10

Santiago Cepas
Santiago Cepas

Reputation: 4094

I guess you could always use subqueries:

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;

Upvotes: 2

Related Questions