Apache Phoenix - Insert multiple rows with one upsert statement

Question

I am working with Apache Phoenix and I am trying to generate a bunch of test data so I can measure performance. I was wondering if there is a way to insert multiple rows using one UPSERT statement.

I want a Phoenix equivalent to this Oracle SQL statement:

insert into test_table (col_a,col_b) 
select dbms_random.value(1,10), dbms_random.value(1,10) 
from dual
connect by level <= 10;

If not I have a few more options like using a csv file or creating a program to do multiple UPSERTs for me.

If my answer solved your problem, click the big checkbox to accept it as the answer. — IgorekPotworek, Jan 13 '16 at 21:41

score 1 · Answer 1 · answered Jan 13 '16 at 17:45

1

Yes, there is Upsert Select statement.

Example:

UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM test.sourceTable WHERE col5 < 100
UPSERT INTO foo SELECT * FROM bar;

For more details please check Apache Phoenix official documentation link

answered Jan 13 '16 at 17:45

IgorekPotworek

1,317
13
33

This `UPSERT SELECT` only update values from phoenix/hbase but can't set multiple rows' field to same value. – Sinux Sep 01 '21 at 10:14

score 0 · Answer 2 · answered Apr 15 '16 at 06:49

i faced a similar issue while creating test data for my phoenix tables. Instead i used a JDBC connection to Phoenix and then upserted the rows in to my tables.Yes the option for insert in phoenix is upsert which basically updates if the row key exists else insert if the row key is not present.

Apache Phoenix - Insert multiple rows with one upsert statement

2 Answers2