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

Question

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 send in multiple upsert statements with different values in one json. I looked at the avatica roadmap and its mentioned that we can call composite RPC's by Execute-Fetch.

http://calcite.apache.org/docs/avatica_roadmap.html

I was hoping to get an example on how to do this as there is no execute-fetch example in the documentation.

Answer 1

https://calcite.apache.org/docs/avatica_json_reference.html

https://calcite.apache.org/docs/avatica_protobuf_reference.html

I recommend Protocol Buffers (Phoenix 4.7.+ uses Calcite 1.5 which uses Protocol Buffers by default) over JSON because the generated classes are much cleaner to work with than JSON libraries. Protocol Buffers are also fairly language agnostic.

For either JSON or Protocol Buffers, the basic procedure is as follows:

Send a PrepareRequest

Receive PrepareResponse

Send ExecuteRequest with TypedValue for each parameter

Receive ExecuteResponse

If you have more data frames, send FetchRequest and receive FetchResponse for all subsequent frames.

Answer 2

maybe this is a bit off your theme regarding using the query server.

my experience with HBase Phoenix Batch Insert is using JDBC driver,

• turn off the auto commit on connection

• execute a series of insert statements

• commit manually