Multi-insert statement in Hive

Question

I am coming across a problem with multi-insert in Hive

FROM staged_employees se
INSERT INTO TABLE us_employees
    AS SELECT * WHERE se.cnty = 'US'
INSERT INTO TABLE ca_employees
    AS SELECT * WHERE se.cnty = 'CA'
...

As far as I know, the multi-inserts are not IF ... ELSE ... construct, however, is it possible to make it into IF ... ELSE ... construct?

Answer 1

HQL has no support for conditional logic. A literal answer to your question about splitting apart the insert is:

INSERT INTO TABLE us_employees
SELECT * 
FROM staged_employees se
WHERE se.cnty = 'US'

INSERT INTO TABLE ca_employees
SELECT *
FROM staged_employees se
WHERE se.cnty = 'CA'

All you will accomplish by splitting out your queries is reading all of the data from the staged_employees table twice. And, potentially, making other users of your cluster incredibly annoyed.