Support Questions

Find answers, ask questions, and share your expertise

Insert multiple rows with one request using phoenix query server on Hbase

New Contributor

I am looking to combine multiple upsert requests into one request and pass it to the phoenix query server.

I am sending the following json to upsert one record

POST https://tishihdiphoenix.azurehdinsight.net/hbasephoenix/ HTTP/1.1
request:{"request":"prepareAndExecute","connectionId":"000000-0000-0000-00000001","sql":""UPSERT INTO Table( col1 ) VALUES ( value1 )":100}
Authorization: Basic YWRtaW46Tm9tb3JlTm9tb3JlIT0x
Host: tishihdiphoenix.azurehdinsight.net
Content-Length: 0
Connection: Keep-Alive
I want to be able to upsert multiple statements with different values in one json. 
I tried changing the sql statement to something like
UPSERT INTO Table( col1 ) VALUES ( value1 ), ( value2 ) but that didn't work. I followed the steps to prepare request and then created statement. I got a create response but I couldn't get either the fetch or the execute to work. 

Fetch response > Statement not found, potentially expired. 000000-0000-0000-00000001::1453337888</pre></p>

Execute response > com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field "statementHandle" (class org.apache.calcite.avatica.remote.Service$SchemasRequest), not marked as ignorable (2 known properties: , "catalog", "schemaPattern"])
 at [Source: java.io.StringReader@204e1f9c; line: 1, column: 312] (through reference chain: org.apache.calcite.avatica.remote.SchemasRequest["statementHandle"])


Any ideas on what I might need to do after create statement 
 


1 ACCEPTED SOLUTION

You cannot currently perform multiple upserts within a single HTTP call to the Phoenix QueryServer.

Support for commit/rollback was recently added to Calcite (the technology behind the Phoenix QueryServer) in https://issues.apache.org/jira/browse/CALCITE-767 which is relevant for what you're trying to do. However, this was not yet released in a version of HDP. The ability to use commit and rollback lets you batch multiple calls inside of the PQS instance before committing them to HBase.

View solution in original post

6 REPLIES 6

What version of HDP are you using? It seems like there is a mismatch between your client and server libraries.

New Contributor

You cannot currently perform multiple upserts within a single HTTP call to the Phoenix QueryServer.

Support for commit/rollback was recently added to Calcite (the technology behind the Phoenix QueryServer) in https://issues.apache.org/jira/browse/CALCITE-767 which is relevant for what you're trying to do. However, this was not yet released in a version of HDP. The ability to use commit and rollback lets you batch multiple calls inside of the PQS instance before committing them to HBase.

New Contributor

Thanks for the quick answer, would you happen to know when it would become available?

I am not sure, but I can see if we have any public roadmap posted for you.

New Contributor

Yes There is a way.....

Take a look at following working query and modify your code accordingly to generate like queries.

Consider a table XYZ with columns ID,CF.RISK :

UPSERT INTO XYZ(ID,CF.RISK SELECT ID,(CASE ID WHEN '12341e65-a68b-4f88-93ce-33dd94497a6d' THEN 'MEDIUM_RISK' WHEN '73f41e65-a68b-4f88-93ce-33dd94497a6d' THEN 'HIGH_RISK' ELSE 'LOW_RISK' END) FROM ACCOUNTS WHERE ID IN('12341e65-a68b-4f88-93ce-33dd94497a6d','73f41e65-a68b-4f88-93ce-33dd94497a6d')

It updates column CF.RISK for all ID's in WHERE clause list.

,

Yes there is a way;

Take a look at following working query and modify your code accordingly: Consider a table XYZ with columns ID,CF.RISK;

UPSERT INTO XYZ(ID,CF.RISK SELECT ID,(CASE ID WHEN '12341e65-a68b-4f88-93ce-33dd94497a6d' THEN 'MEDIUM_RISK' WHEN '73f41e65-a68b-4f88-93ce-33dd94497a6d' THEN 'HIGH_RISK' ELSE 'LOW_RISK' END) FROM ACCOUNTS WHERE ID IN('12341e65-a68b-4f88-93ce-33dd94497a6d','73f41e65-a68b-4f88-93ce-33dd94497a6d')

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.