create a new hive table with values from two hive tables

0
votes

I want to create a hive table with 3 columns. Let's say, id, average,incharge. The values for id and average is in table 'first'. And for the incharge column, i need to execute a query on table 'second'. I tried this but failed

create table third(
 ID INT,
 AVERAGE INT,
 INCHARGE STRING
 )
from first f
insert into table third select
f.id,
f.average,
from ( select second.incharge where second.k_id == f.id limit 1)

Any clue will be really helpful

1
hiveql

1 Answers

1
votes
FROM (
SELECT
    f.id ,
    f.average
FROM
    FIRST f ) x JOIN
   (
SELECT
    s.incharge ,
    s.sid
FROM
    second s ) y ON y.sid = x.id
INSERT
OVERWRITE TABLE third
SELECT
x.id ,
x.average ,
y.incharge