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

[#1]

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 
 

[#2]

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

[#3]

I believe I am on HDP 2.2

http://hortonworks.com/blog/microsoft-azure-hdinsight-on-linux-expands-application-platforms/

[#4 · 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.

[#5]

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

[#6]

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

[#7]

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')