how to insert multiple rows of data into a hive(0.13.1) table?

Question

I am looking for an equivalent of bellow query for Hive version 0.13.1.

INSERT INTO TABLE table1 VALUES 
(151, 'cash', 'lunch'), 
(152, 'credit', 'lunch'), 
(153, 'cash', 'dinner');

from this answer it is clear "INSERT.... VALUES" query available after version 0.14.
so what is equivalent of above query for given hive version?

@Jared - I've asked the OP to open a new question for multiple values insert. — David דודו Markovitz, May 05 '17 at 20:26
I have no clue regarding it. Better person will be someone in my management, who provides me this hive version, to answer it :) . — John, May 06 '17 at 02:22

score 7 · Accepted Answer · answered May 05 '17 at 19:50

7

If you want to insert multiple values then you can union selects

INSERT INTO TABLE table1 
    select 151, 'cash', 'lunch'
    union all
    select 152, 'credit', 'lunch'
    union all
    select 153, 'cash', 'dinner';

answered May 05 '17 at 19:50

Jared

2,904
6
33
37

David דודו Markovitz · Answer 2 · 2019-12-26T11:30:09.847

3

When using the "stack" function, the first number represents the number of rows

INSERT INTO TABLE table1 
select stack
       (
           3
          ,151 ,'cash'   ,'lunch'
          ,152 ,'credit' ,'lunch'
          ,153 ,'cash'   ,'dinner'
       )

or

INSERT INTO TABLE table1 
select inline(array
       (
           struct (151 ,'cash'   ,'lunch')
          ,struct (152 ,'credit' ,'lunch')
          ,struct (153 ,'cash'   ,'dinner')
       ))

edited Dec 26 '19 at 11:30

answered May 05 '17 at 17:05

David דודו Markovitz

42,900
6
64
88

I don't have Hive 0.13 but `inline` was introduce on hive 0.10 so it should work. It does work on newer versions. – David דודו Markovitz May 05 '17 at 20:30
1

i am getting this error while running above command `FAILED: NullPointerException null` – John May 10 '17 at 11:40
Hi @Sanjay, please check the edited answer for an additional option (stack) – David דודו Markovitz Dec 26 '19 at 11:30

how to insert multiple rows of data into a hive(0.13.1) table?

2 Answers2