Search code examples
hivehql

Hive Loop Insert into select in a same table

I am currently working on Hive and I would like to insert in an existing table a number n times its contents.

To illustrate what I want :

I have a table test (var1 STRING, var2 STRING, var3 STRING) with for example 20 rows. I would like to insert in the table test the 20 rows n times (so for example 3 times, so as to have 80 lines at the end of my loop).

I tried this kind of request :

for n in 1 3;
do 
INSERT INTO test 
(var1 STRING, var2 STRING, var3 STRING)
SELECT var1 STRING, var2 STRING, var3 STRING
FROM test;
done;

But it didn't work.

Ant suggestions ? Thanks a lot

Solution

  • No need for loops.

    Demo

    create table t (i int);
insert into t values (1),(2);
select * from t;

    +----+
| i  |
+----+
| 1  |
| 2  |
+----+

    insert into t 
select  t.* 
from    t 
        lateral view explode(split(space(3-1),' ')) e
;

select * from t;

    +----+
| i  |
+----+
| 1  |
| 1  |
| 1  |
| 2  |
| 2  |
| 2  |
| 1  |
| 2  |
+----+