Reply
Highlighted
New Contributor
Posts: 1
Registered: ‎05-25-2018

How to rewrite hive sql from multiple read/write to one read/write for following sql ?

insert into otab select com, b1,b2,b1+b2 as a1 from (select com, c1+c2 as b1, c4+c4 as b2 from (select com, c1,c2,c3,c4 from itab where com='abc') as b) as a;
insert into otab select com, b1,b2,b1+b10+b20+c12 as a1 from (select com, c1+c2 as b1, c4+c4 as b10, c11+c12+c13 as b20, c12 from (select com, c11,c12,c13,c14 from itab where com='bcd') as b) as a;
insert into otab select com, b1,b2,b1+b2+b3 as a1 from (select com, c10+c20 as b1, c1+c2 as b2, c1+20 as b3 from (select com, c1,c10,c2,c20 from itab where com='cde') as b) as a;


this may go upto 100 statements


Here table: iTab has 300 columns to join/pull from 2 different tables based on common condition.
The above queries 2 level of transmittion to write to final table
but it acess the common table: itab for all those 100 statements
Here I want to read only one time from that table: itab to create one single insert statement to archieve instead of creating 100 different insert statement to access 100 times to table itab..

any other way to rewrite ths query to archeive this ?

Announcements