22

I'm new to Hive and I wanted to know if insert overwrite will overwrite an existing table I have created. I want to filter an already created table, let's call it TableA, to only select the rows where age is greater than 18. Can I achieve this using insert overwrite table?

I'm thinking of writing something like:

INSERT OVERWRITE TABLE TableA SELECT a.Age FROM TableA WHERE a.Age > = 18

there are NA entries in the table I created, but I assume that after I filter this table there will be no NAs in the Age column, right?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Anna Mai
  • 233
  • 1
  • 2
  • 7

1 Answers1

39

Self filtering and insertion is not support , yet in hive.

I would suggest the following steps in your case :

1.Create a similar table , say tabB , with same structure.

create table tabB like tableA;

2.Then you could apply your filter and insert into this new table.

INSERT OVERWRITE TABLE tabB SELECT a.Age FROM TableA WHERE a.Age > = 18

Hope this helps.

iPrince
  • 115
  • 10
K S Nidhin
  • 2,622
  • 2
  • 22
  • 44
  • Yeah it definitely helps. Also, if I have multiple Age columns (representing different members in a family) how would I select multiple columns while doing INSERT OVERWRITE TABLE? – Anna Mai Oct 03 '14 at 13:34
  • you add whatever logic , a big subquery too after insert overwrite table – K S Nidhin Oct 03 '14 at 13:51